Estas instruções presumem que você esteja utilizando o DB2 Universal Database e orientam sobre as seguintes tarefas:
As seguintes tarefas são opcionais:
Conclua as seguintes etapas:
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
Próximas etapas: Execute uma das seguintes etapas para criar as tabelas e acionadores de banco de dados:
Nota. Quando você cria e acessa as tabelas de banco de dados, fique atento como os seguintes problemas:
-- 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
Aguarde a conclusão do script. Se estiver executando o script pela primeira vez, a mensagem a seguir é exibida novamente:
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 essas mensagens. As mensagens são exibidas porque o script tenta remover as tabelas denominadas DBINPUT_CUSTOMER e DBINPUT_EVENTS antes de criar novas tabelas. Se você não executou o script antes, o script não poderá localizar essas tabelas, porque elas não existem.
Se você não estiver executando o script pela primeira vez, poderá visualizar um aviso de cada um dos acionadores:
SQL0280W View, trigger or materialized query table "DB2ADMIN.DBIN_CUST_UPDATE" has replaced an existing inoperative view, trigger or materialized query table. SQLSTATE=01595Essas mensagens também podem ser ignoradas.