この説明では、DB2 Universal Database を使用していることを前提とし、以下のタスクを順番に説明していきます。
以下のタスクはオプションです。
以下のステップを実行します。
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
次のステップ: 以下のいずれかのステップを実行して、データベース表とトリガーを作成します。
注 データベース表を作成してそれにアクセスする際には、以下の問題に注意してください。
-- 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
スクリプトの実行が終了するまで待ちます。 スクリプトを初めて実行する場合は、次のようなメッセージが 2 回表示されます。
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、SQL ステートメントとして処理されました。 SQL 処理中に、以下のコマンドが返されました。SQL0204N "DB2ADMIN.DBINPUT_EVENTS" は未定義の名前です。SQLSTATE=42704これらのメッセージは無視してください。 メッセージが表示される理由は、スクリプトが、DBINPUT_CUSTOMER および DBINPUT_EVENTS という名前の表を除去してから、新規表を作成しようとするからです。 以前にスクリプトを実行したことがなければ、これらの表は存在していないので、スクリプトが見つけることはできません。
スクリプトを実行するのが初めてではない場合は、トリガーのそれぞれで次のような警告が表示されることがあります。
SQL0280W ビュー、トリガー、またはマテリアライズ照会表「DB2ADMIN.DBIN_CUST_UPDATE」が、既存の作動不能なビュー、トリガー、またはマテリアライズ照会表を置換しました。 SQLSTATE=01595これらのメッセージも無視できます。