Estas instrucciones dan por supuesto que se está utilizando DB2 Universal Database y le guían por las tareas siguientes:
Las tareas siguientes son opcionales:
Lleve a cabo los siguientes pasos:
CREATE DB DBINPUT CONNECT TO DBINPUT BIND 'dir_instalación\bnd\@db2cli.lst' blocking all grant public TERMINATE
CREATE DB DBINPUT CONNECT TO DBINPUT BIND dir_instalación/bnd/@db2cli.lst grant public CLIPKG 5 TERMINATE
db2start
db2 -vf dbinput.sql
Siguientes pasos: realice uno de los siguientes pasos para crear las tablas de base de datos y los desencadenantes:
Nota. Cuando cree y acceda a las tablas de la base de datos, tenga en cuenta los problemas siguientes:
-- Primero cree las tablas necesarias para 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@ -- A continuación, cree las tablas necesarias para el almacén de sucesos 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") @ -- Desencadenantes de la tabla de datos de aplicación Cliente 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)@ -- Desencadenantes de la tabla de datos de aplicación Dirección 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)@ -- Desencadenantes de la tabla de datos de aplicación Factura 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
Espere a que el script termine de ejecutarse. Si está ejecutando el script por primera vez, aparece el siguiente mensaje dos veces:
DB21034E El mandato se ha procesado como una sentencia SQL porque no era un mandato de Procesador de línea de mandatos válido. Durante el proceso SQL ha devuelto: SQL0204N "DB2ADMIN.DBINPUT_EVENTS" es un nombre no definido. SQLSTATE=42704Haga caso omiso de estos mensajes. Los mensajes se visualizan porque el script intenta eliminar las tablas llamadas DBINPUT_CUSTOMER y DBINPUT_EVENTS antes de crear las tablas nuevas. Si no ha ejecutado el script con anterioridad, el script no puede encontrar estas tablas, ya que no existen.
Si no es la primera vez que ejecuta el script, es posible que aparezca un aviso para cada desencadenante:
SQL0280W La tabla de vista, desencadenante o consulta materializada "DB2ADMIN.DBIN_CUST_UPDATE" ha sustituido una tabla de vista no operativa existente, desencadenante consulta materializada. SQLSTATE=01595También puede ignorar estos mensajes.