Bei den folgenden Anweisungen wird davon ausgegangen, dass Sie DB2 Universal Database verwenden. Sie werden durch die folgenden Tasks geführt:
Die folgenden Tasks sind optional:
Gehen Sie wie folgt vor:
CREATE DB DBINPUT CONNECT TO DBINPUT BIND 'Installationsverzeichnis\bnd\@db2cli.lst' blocking all grant public TERMINATE
CREATE DB DBINPUT CONNECT TO DBINPUT BIND Installationsverzeichnis/bnd/@db2cli.lst grant public CLIPKG 5 TERMINATE
db2start
db2 -vf dbinput.sql
Nächste Schritte: Führen Sie einen der folgenden Schritte aus, um die Datenbanktabellen und Auslöser zu erstellen:
Hinweis: Berücksichtigen Sie Folgendes beim Erstellen der Datenbanktabellen und beim Zugriff darauf:
-- 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
Warten Sie das Ende des Scripts ab. Wenn Sie das Script zum ersten Mal ausführen, wird die folgende Nachricht zweimal angezeigt:
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=42704Ignorieren Sie diese Nachrichten. Sie werden angezeigt, da das Script versucht, die Tabellen DBINPUT_CUSTOMER und DBINPUT_EVENTS zu entfernen, bevor neue Tabellen erstellt werden. Wenn das Script zum ersten Mal ausgeführt wird, kann das Script diese Tabellen nicht finden, da sie nicht existieren.
Falls Sie das Script nicht zum ersten Mal ausführen, wird möglicherweise für jeden der Auslöser eine Warnung angezeigt:
SQL0280W View, trigger or materialized query table "DB2ADMIN.DBIN_CUST_UPDATE" has replaced an existing inoperative view, trigger or materialized query table. SQLSTATE=01595Diese Nachrichten können ebenfalls ignoriert werden.