These instructions assume that you are using DB2 Universal Database and guide you through the following tasks:
The following tasks are optional:
Complete the following steps:
CREATE DB DBINPUT CONNECT TO DBINPUT BIND 'install_dir\bnd\@db2cli.lst' blocking all grant public TERMINATE
CREATE DB DBINPUT CONNECT TO DBINPUT BIND install_dir/bnd/@db2cli.lst grant public CLIPKG 5 TERMINATE
db2start
db2 -vf dbinput.sql
Next steps: Take one of the following steps to create the database tables and triggers:
Note. When you create and access the database tables, be aware of the following issues:
-- First create the tables needed for Application Data DROP TABLE "ADAPTER_CUSTOMER" @ CREATE TABLE "ADAPTER_CUSTOMER" ( "PKEY" VARCHAR(10) NOT NULL, "FIRSTNAME" VARCHAR(20), "LASTNAME" VARCHAR(20), "ADDRESS" VARCHAR(10), "CCODE" VARCHAR(10) ) DATA CAPTURE NONE @ ALTER TABLE "ADAPTER_CUSTOMER" ADD CONSTRAINT "ADAPTERCUSTOMERPK" PRIMARY KEY ("PKEY") @ ALTER TABLE "ADAPTER_CUSTOMER" ADD CONSTRAINT "CUST_ADDR_FK" FOREIGN KEY ("ADDRESS") REFERENCES "ADAPTER_ADDRESS" ("PKEY") ON DELETE CASCADE@ DROP TABLE "ADAPTER_ADDRESS" @ CREATE TABLE "ADAPTER_ADDRESS" ( "PKEY" VARCHAR(10) NOT NULL, "FIRSTLINE" VARCHAR(20), "SECONDLINE" VARCHAR(20), "CODE" VARCHAR(10), "CITY" VARCHAR(20), "COUNTRY" VARCHAR(10)) DATA CAPTURE NONE @ ALTER TABLE "ADAPTER_ADDRESS" ADD CONSTRAINT "ADAPTERADDRESSPK" PRIMARY KEY ("PKEY")@ DROP TABLE "ADAPTER_INVOICE" @ CREATE TABLE "ADAPTER_INVOICE" ( "PKEY" VARCHAR(10) NOT NULL, "ITEM" VARCHAR(10) NOT NULL, "QUANTITY" INTEGER NOT NULL, "CUSTOMER" VARCHAR(10) NOT NULL) DATA CAPTURE NONE @ ALTER TABLE "ADAPTER_INVOICE" ADD CONSTRAINT "ADAPTERINVOICEPK" PRIMARY KEY ("PKEY")@ ALTER TABLE "ADAPTER_INVOICE" ADD CONSTRAINT "INV_CUST_FK" FOREIGN KEY ("CUSTOMER") REFERENCES "ADAPTER_CUSTOMER" ("PKEY") ON DELETE CASCADE@ -- Then create the tables needed for the event store DROP TABLE "IBM_EVENTS" @ CREATE TABLE "IBM_EVENTS" ( "EVENT_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE), "CONNECTOR_ID" VARCHAR(40), "OBJECT_KEY" VARCHAR(10) NOT NULL, "OBJECT_NAME" VARCHAR(40) NOT NULL, "OBJECT_VERB" VARCHAR(40) NOT NULL, "EVENT_PRIORITY" INTEGER NOT NULL, "EVENT_TIME" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, "EVENT_STATUS" INTEGER NOT NULL, "EVENT_COMMENT" VARCHAR(100) ) DATA CAPTURE NONE @ ALTER TABLE "IBM_EVENTS" ADD CONSTRAINT "EVENTKEY" PRIMARY KEY ("EVENT_ID") @ DROP TABLE "IBM_UID" @ CREATE TABLE "IBM_UID" ( "ID" INTEGER NOT NULL ) DATA CAPTURE NONE @ ALTER TABLE "IBM_UID" ADD CONSTRAINT "UIDKEY" PRIMARY KEY ("ID") @ -- Triggers for the Customer application data table DROP TRIGGER "ADAP_ADDR_CREATE"@ DROP TRIGGER "ADAP_ADDR_DELETE"@ DROP TRIGGER "ADAP_ADDR_UPDATE"@ DROP TRIGGER "ADAP_CUST_CREATE"@ DROP TRIGGER "ADAP_CUST_DELETE"@ DROP TRIGGER "ADAP_CUST_UPDATE"@ DROP TRIGGER "ADAP_INV_CREATE"@ DROP TRIGGER "ADAP_INV_DELETE"@ DROP TRIGGER "ADAP_INV_UPDATE"@ CREATE TRIGGER "ADAP_CUST_CREATE" AFTER INSERT ON "ADAPTER_CUSTOMER" REFERENCING NEW AS N FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (N.PKEY, 'ADAPTER_CUSTOMER', 'Create', 1, 0)@ CREATE TRIGGER "ADAP_CUST_DELETE" AFTER DELETE ON "ADAPTER_CUSTOMER" REFERENCING OLD AS O FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (O.PKEY, 'ADAPTER_CUSTOMER', 'Delete', 1, 0)@ CREATE TRIGGER "ADAP_CUST_UPDATE" AFTER UPDATE ON "ADAPTER_CUSTOMER" REFERENCING NEW AS N FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (N.PKEY, 'ADAPTER_CUSTOMER', 'Update', 1, 0)@ -- Triggers for the Address application data table CREATE TRIGGER "ADAP_ADDR_CREATE" AFTER INSERT ON "ADAPTER_ADDRESS" REFERENCING NEW AS N FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (N.PKEY, 'ADAPTER_ADDRESS', 'Create', 1, 0)@ CREATE TRIGGER "ADAP_ADDR_DELETE" AFTER DELETE ON "ADAPTER_ADDRESS" REFERENCING OLD AS O FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (O.PKEY, 'ADAPTER_ADDRESS', 'Delete', 1, 0)@ CREATE TRIGGER "ADAP_ADDR_UPDATE" AFTER UPDATE ON "ADAPTER_ADDRESS" REFERENCING NEW AS N FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (N.PKEY, 'ADAPTER_ADDRESS', 'Update', 1, 0)@ -- Triggers for the Invoice application data table CREATE TRIGGER "ADAP_INV_CREATE" AFTER INSERT ON "ADAPTER_INVOICE" REFERENCING NEW AS N FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (N.PKEY, 'ADAPTER_INVOICE', 'Create', 1, 0)@ CREATE TRIGGER "ADAP_INV_DELETE" AFTER DELETE ON "ADAPTER_INVOICE" REFERENCING OLD AS O FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (O.PKEY, 'ADAPTER_INVOICE', 'Delete', 1, 0)@ CREATE TRIGGER "ADAP_INV_UPDATE" AFTER UPDATE ON "ADAPTER_INVOICE" REFERENCING NEW AS N FOR EACH ROW INSERT INTO IBM_EVENTS (OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES (N.PKEY, 'ADAPTER_INVOICE', 'Update', 1, 0)@ TERMINATE@
db2 -td@ -vf dbinput_create_tables_db2.sql
Wait for the script to finish running. If you are running the script for the first time, the following message is displayed twice:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "DB2ADMIN.DBINPUT_EVENTS" is an undefined name. SQLSTATE=42704Ignore these messages. The messages are displayed because the script attempts to remove tables called DBINPUT_CUSTOMER and DBINPUT_EVENTS before it creates the new tables. If you have not run the script before, the script cannot find these tables, as they do not exist.
If you are not running the script for the first time you might see a warning for each of the triggers:
SQL0280W View, trigger or materialized query table "DB2ADMIN.DBIN_CUST_UPDATE" has replaced an existing inoperative view, trigger or materialized query table. SQLSTATE=01595You can also ignore these messages.