IBM AppConnect and DB2 and SQL0530N

I'm fiddling about with IBM AppConnect Professional ( formerly known as CastIron ), looking at the integration between a flow running on AppConnect, hosted on the IBM Cloud ( nee Bluemix ) and a DB2 database running on a VM on my Mac.

I'll be writing another blog post about the actual integration, including the Secure Gateway later.

Meantime, I wanted to test my flow, which should be monitoring a table for changes.

I did this by inserting a new row into the EMPLOYEE table of the SAMPLE database ( which has been around since I worked on DB2/400 in the mid-90s ).

This is what that table looks like: -

db2 describe "select * from DB2INST1.EMPLOYEE"

 Column Information

 Number of columns: 14

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 452   CHARACTER                 6  EMPNO                                     5
 448   VARCHAR                  12  FIRSTNME                                  8
 453   CHARACTER                 1  MIDINIT                                   7
 448   VARCHAR                  15  LASTNAME                                  8
 453   CHARACTER                 3  WORKDEPT                                  8
 453   CHARACTER                 4  PHONENO                                   7
 385   DATE                     10  HIREDATE                                  8
 453   CHARACTER                 8  JOB                                       3
 500   SMALLINT                  2  EDLEVEL                                   7
 453   CHARACTER                 1  SEX                                       3
 385   DATE                     10  BIRTHDATE                                 9
 485   DECIMAL                9, 2  SALARY                                    6
 485   DECIMAL                9, 2  BONUS                                     5
 485   DECIMAL                9, 2  COMM                                      4

This is what I ran: -

db2 "INSERT INTO DB2INST1.EMPLOYEE   VALUES('000001','Dave','M','Hay','ABC','2122','30/10/1999','Guru',18,'F','30/10/1973',1234.89,1234.89,1221.89)"

which returned: -

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0530N  The insert or update value of the FOREIGN KEY 
"DB2INST1.EMPLOYEE.RED" is not equal to any value of the parent key of the 
parent table.  SQLSTATE=23503

which baffled me somewhat.

I dug into the database further: -

db2look -d sample -e -t db2inst1.employee

…

CREATE TABLE "DB2INST1"."EMPLOYEE"  (
  "EMPNO" CHAR(6 OCTETS) NOT NULL , 
  "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL , 
  "MIDINIT" CHAR(1 OCTETS) , 
  "LASTNAME" VARCHAR(15 OCTETS) NOT NULL , 
  "WORKDEPT" CHAR(3 OCTETS) , 
  "PHONENO" CHAR(4 OCTETS) , 
  "HIREDATE" DATE , 
  "JOB" CHAR(8 OCTETS) , 
  "EDLEVEL" SMALLINT NOT NULL , 
  "SEX" CHAR(1 OCTETS) , 
  "BIRTHDATE" DATE , 
  "SALARY" DECIMAL(9,2) , 
  "BONUS" DECIMAL(9,2) , 
  "COMM" DECIMAL(9,2) )   
 IN "USERSPACE1"  
 ORGANIZE BY ROW; 


-- DDL Statements for Primary Key on Table "DB2INST1"."EMPLOYEE"

ALTER TABLE "DB2INST1"."EMPLOYEE" 
ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY
("EMPNO");



-- DDL Statements for Indexes on Table "DB2INST1"."EMPLOYEE"

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "DB2INST1"."XEMP2" ON "DB2INST1"."EMPLOYEE" 
("WORKDEPT" ASC)

COMPRESS NO 
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
-- DDL Statements for Aliases based on Table "DB2INST1"."EMPLOYEE"

CREATE ALIAS "DB2INST1"."EMP" FOR TABLE "DB2INST1"."EMPLOYEE";


-- DDL Statements for Foreign Keys on Table "DB2INST1"."EMPLOYEE"

ALTER TABLE "DB2INST1"."EMPLOYEE" 
ADD CONSTRAINT "RED" FOREIGN KEY
("WORKDEPT")
REFERENCES "DB2INST1"."DEPARTMENT"
("DEPTNO")
ON DELETE SET NULL
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
...

which showed me the error of my way.

In essence, the WORKDEPT column is actually keyed against a different table: -

db2 describe "select * from DB2INST1.DEPARTMENT"

 Column Information

 Number of columns: 5

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 452   CHARACTER                 3  DEPTNO                                    6
 448   VARCHAR                  36  DEPTNAME                                  8
 453   CHARACTER                 6  MGRNO                                     5
 452   CHARACTER                 3  ADMRDEPT                                  8
 453   CHARACTER                16  LOCATION                                  8

db2 "select * from DB2INST1.DEPARTMENT"

DEPTNO DEPTNAME                             MGRNO  ADMRDEPT LOCATION        
------ ------------------------------------ ------ -------- ----------------
A00    SPIFFY COMPUTER SERVICE DIV.         000010 A00      -               
B01    PLANNING                             000020 A00      -               
C01    INFORMATION CENTER                   000030 A00      -               
D01    DEVELOPMENT CENTER                   -      A00      -               
D11    MANUFACTURING SYSTEMS                000060 D01      -               
D21    ADMINISTRATION SYSTEMS               000070 D01      -               
E01    SUPPORT SERVICES                     000050 A00      -               
E11    OPERATIONS                           000090 E01      -               
E21    SOFTWARE SUPPORT                     000100 E01      -               
F22    BRANCH OFFICE F2                     -      E01      -               
G22    BRANCH OFFICE G2                     -      E01      -               
H22    BRANCH OFFICE H2                     -      E01      -               
I22    BRANCH OFFICE I2                     -      E01      -               
J22    BRANCH OFFICE J2                     -      E01      -               

  14 record(s) selected.

My insert: -

db2 "INSERT INTO DB2INST1.EMPLOYEE   VALUES('000001','Dave','M','Hay','ABC','2122','30/10/1999','Guru',18,'F','30/10/1973',1234.89,1234.89,1221.89)"

is using a DIFFERENT and NON-EXISTENT code ( ABC ) for WORKDEPT.

I changed my insert to: -

db2 "INSERT INTO DB2INST1.EMPLOYEE   VALUES('000001','Dave','M','Hay','A00','2122','30/10/1999','Guru',18,'F','30/10/1973',1234.89,1234.89,1221.89)"

and it all worked: _

DB20000I  The SQL command completed successfully.

Yay !