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 !
