Setting up DB2

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:

  1. Open a new document in a text editor. Into this document copy and paste the following script, which creates the DBINPUT database and runs the BIND utility. Name the file dbinput.sql and save the file.

  2. In a DB2 command window, to ensure that DB2 is started, enter the following command:
    db2start
  3. In a DB2 command window, navigate to the folder that contains dbinput.sql and enter the following command:
    db2 -vf dbinput.sql

Next steps: Take one of the following steps to create the database tables and triggers:

Manually create the database tables and triggers

Note. When you create and access the database tables, be aware of the following issues:

  1. Open a new document in a text editor. Into this document copy and paste the following script, which creates the ADAPTER_ADDRESS, ADAPTER_CUSTOMER, ADAPTER_INVOICE and IBM_EVENTS tables. Name the file adapter_create_tables_db2.sql and save the file.
     
    -- 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@
          
  2. Ensure that the database is connected and running. In a command window that you have opened to access your database, move to the folder that contains dbinput_create_tables_db2.sql and enter the following command:
    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=42704
          
    Ignore 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=01595
          
    You can also ignore these messages.

Back to Setting up the database

Back to sample home