Tuesday 22 August 2017

Doh, IBM DB2 on the IBM Container Service on IBM Bluemix, remember persistence :-)

Having got DB2 running on the IBM Container Service on IBM Bluemix: -


I was a little perturbed when my Java code failed to work, when connecting from my Mac to the DB2 container: -

java -cp db2jcc4.jar:. JdbcTestDB2 54.23.128.93 50000 SAMPLE db2inst1 Qp455w0rd!

com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-1031, SQLSTATE=58031, SQLERRMC=null, DRIVER=4.23.42

I checked the SQL code: -

db2 ? sql1031

SQL1031N  The database directory cannot be found on the indicated file
      system.


Explanation: 

The system database directory or local database directory could not be
found. A database has not been created or it was not cataloged
correctly.

The command cannot be processed.

User response: 

Verify that the database is created with the correct path specification.
The Catalog Database command has a path parameter which specifies the
directory where the database resides.

sqlcode: -1031

sqlstate: 58031


which made me think: -

db2 list db directory

SQL1031N  The database directory cannot be found on the indicated file system. 
SQLSTATE=58031


and then it hit me ….

I'd previously dropped the running container: -

cf ic stop db2

removed it: -

cf ic rm db2

and started a new one: -

cf ic run --name db2 -p 50000:50000 -e DB2INST1_PASSWORD=Qp455w0rd! -e LICENSE=accept -d registry.eu-gb.bluemix.net/david_hay/db2expressc:pamfixed db2start

and validated it: -

cf ic ps -a

CONTAINER ID        IMAGE                                                       COMMAND             CREATED             STATUS              PORTS               NAMES
3cf533af-c95        registry.eu-gb.bluemix.net/david_hay/db2expressc:pamfixed   "db2start "         50 seconds ago      Running             50000/tcp           db2


and requested an IP address: -

cf ic ip request

OK
IP address "54.23.128.93" was obtained.


and bound the IP address to the container: -

cf ic ip bind 54.23.128.93 db2

OK
The IP address was bound successfully.


So, of course, I no longer had any databases :-)

To mitigate this, I started a shell to the running container: -

cf ic exec -i -t db2 /bin/bash

switched to the db2inst1 user: -

su - db2inst1

and created the SAMPLE database: -

db2sampl

  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "DB2INST1"...
  Creating tables with XML columns and XML data in schema "DB2INST1"...

  'db2sampl' processing complete.


and validated that all was well: -

db2 connect to SAMPLE

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.5
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE


db2 select "EMPNO,FIRSTNME,LASTNAME from DB2INST1.EMPLOYEE"

EMPNO  FIRSTNME     LASTNAME       
------ ------------ ---------------
000010 CHRISTINE    HAAS           
000020 MICHAEL      THOMPSON       
000030 SALLY        KWAN           
000050 JOHN         GEYER          
000060 IRVING       STERN          
000070 EVA          PULASKI        
000090 EILEEN       HENDERSON      
000100 THEODORE     SPENSER        
000110 VINCENZO     LUCCHESSI      
000120 SEAN         O'CONNELL      
000130 DELORES      QUINTANA       
000140 HEATHER      NICHOLLS       
000150 BRUCE        ADAMSON        
000160 ELIZABETH    PIANKA         
000170 MASATOSHI    YOSHIMURA      
000180 MARILYN      SCOUTTEN       
000190 JAMES        WALKER         
000200 DAVID        BROWN          
000210 WILLIAM      JONES          
000220 JENNIFER     LUTZ           
000230 JAMES        JEFFERSON      
000240 SALVATORE    MARINO         
000250 DANIEL       SMITH          
000260 SYBIL        JOHNSON        
000270 MARIA        PEREZ          
000280 ETHEL        SCHNEIDER      
000290 JOHN         PARKER         
000300 PHILIP       SMITH          
000310 MAUDE        SETRIGHT       
000320 RAMLAL       MEHTA          
000330 WING         LEE            
000340 JASON        GOUNOT         
200010 DIAN         HEMMINGER      
200120 GREG         ORLANDO        
200140 KIM          NATZ           
200170 KIYOSHI      YAMAMOTO       
200220 REBA         JOHN           
200240 ROBERT       MONTEVERDE     
200280 EILEEN       SCHWARTZ       
200310 MICHELLE     SPRINGER       
200330 HELENA       WONG           
200340 ROY          ALONZO         

  42 record(s) selected.

I then re-ran my Java code: -

java -cp db2jcc4.jar:. JdbcTestDB2 54.23.128.93 50000 SAMPLE db2inst1 Qp455w0rd!

000010 CHRISTINE HAAS
000020 MICHAEL THOMPSON
000030 SALLY KWAN
000050 JOHN GEYER
000060 IRVING STERN
000070 EVA PULASKI
000090 EILEEN HENDERSON
000100 THEODORE SPENSER
000110 VINCENZO LUCCHESSI
000120 SEAN O'CONNELL
000130 DELORES QUINTANA
000140 HEATHER NICHOLLS
000150 BRUCE ADAMSON
000160 ELIZABETH PIANKA
000170 MASATOSHI YOSHIMURA
000180 MARILYN SCOUTTEN
000190 JAMES WALKER
000200 DAVID BROWN
000210 WILLIAM JONES
000220 JENNIFER LUTZ
000230 JAMES JEFFERSON
000240 SALVATORE MARINO
000250 DANIEL SMITH
000260 SYBIL JOHNSON
000270 MARIA PEREZ
000280 ETHEL SCHNEIDER
000290 JOHN PARKER
000300 PHILIP SMITH
000310 MAUDE SETRIGHT
000320 RAMLAL MEHTA
000330 WING LEE
000340 JASON GOUNOT
200010 DIAN HEMMINGER
200120 GREG ORLANDO
200140 KIM NATZ
200170 KIYOSHI YAMAMOTO
200220 REBA JOHN
200240 ROBERT MONTEVERDE
200280 EILEEN SCHWARTZ
200310 MICHELLE SPRINGER
200330 HELENA WONG
200340 ROY ALONZO


So that's all good then :-)

The moral of the story - when you drop and recreate a container, don't assume that the "local" storage is still there; next time around, I'll mount some external storage to my container ….

No comments:

Visual Studio Code - Wow 🙀

Why did I not know that I can merely hit [cmd] [p]  to bring up a search box allowing me to search my project e.g. a repo cloned from GitHub...