He aquí algunos ejemplos de las maneras en que puede ampliarse el ejemplo:
Este escenario presenta los siguientes desafíos:
El siguiente código SQL es para DB2 Universal Database. Si ejecuta Oracle, es posible que tenga que modificarlo en consecuencia (y recuerde establecer @ como el carácter finalizador de la sentencia):
CREATE TABLE "ADAPTER_ITEM" ( "PKEY" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE), "NAME" VARCHAR(20) NOT NULL, "INVOICE_KEY" VARCHAR(10) NOT NULL, "PRICE" FLOAT(53) DEFAULT 0 ) DATA CAPTURE NONE@ ALTER TABLE "ADAPTER_ITEM" ADD CONSTRAINT "ADAPTER_ITEM_PK" PRIMARY KEY ("PKEY")@
El SQL crea una tabla nueva, ADAPTER_ITEM, con cuatro columnas: una clave primaria generada automáticamente, el nombre del artículo, una referencia a la factura a la que pertenece y el precio del artículo. La nueva tabla utiliza INVOICE_KEY como clave foránea para la tabla ADAPTER_ITEM.
Sólo debe actualizar una parte del flujo que crea los mensajes ADAPTERINVOICE. Con el botón derecho del ratón, pulse sobre el nodo DatabaseInput en el flujo MigratedAdapter para abrir el ESQL, navegue hasta la vista Origen y desplácese hacia abajo hasta la parte que crea el mensaje:
SET Root.MRM = THE( SELECT DispatchedEvent.Usr.OBJECT_VERB AS verb, INVOICE.PKEY AS Inv:PKEY, INVOICE.ITEM AS Inv:"Item", INVOICE.QUANTITY AS Inv:QUANTITY, INVOICE.CUSTOMER AS Inv:CUSTOMER, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.verb, CUSTOMER.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:PKEY, CUSTOMER.FIRSTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:FIRSTNAME, CUSTOMER.LASTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:LASTNAME, CUSTOMER.ADDRESS AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADDRESS, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.verb, ADDRESS.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:PKEY, ADDRESS.FIRSTLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:FIRSTLINE, ADDRESS.SECONDLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:SECONDLINE, ADDRESS.CODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CODE, ADDRESS.CITY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CITY, ADDRESS.COUNTRY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:COUNTRY, CUSTOMER.CCODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:CCODE , 'JDBCConnector_' ||CAST(DispatchedEvent.Usr.EVENT_ID AS CHARACTER) ||'ibm_events' ||CAST(DispatchedEvent.Usr.EVENT_TIME AS CHARACTER FORMAT 'yyyyMMddhhmmss.SSS') AS Inv:ObjectEventId FROM Database.ADAPTER_INVOICE AS INVOICE, Database.ADAPTER_CUSTOMER AS CUSTOMER, Database.ADAPTER_ADDRESS AS ADDRESS WHERE INVOICE.PKEY = DispatchedEvent.Usr.OBJECT_KEY AND CUSTOMER.PKEY = INVOICE.CUSTOMER AND ADDRESS.PKEY = CUSTOMER.ADDRESS);
Añada los elementos anidados y establezca el atributo de tamaño en el número de artículos recuperados.
(SELECT ADAPTER_ITEM.PKEY, ADAPTER_ITEM.NAME, ADAPTER_ITEM.INVOICE_KEY, ADAPTER_ITEM.PRICE FROM Database.ADAPTER_ITEM WHERE ADAPTER_ITEM.INVOICE_KEY = INVOICE.PKEY) AS Inv:ITEMS.Ite:ADAPTERITEM[],
Ahora inserte datos en el nuevo atributo con el número de filas ADAPTERITEM recuperadas. Añada lo siguiente después de SELECT:
CREATE FIRSTCHILD OF Root.MRM.Inv:ITEMS TYPE NameValue NAME 'size' VALUE CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[]);
Generalmente podría escribir:
SET Root.MRM.Inv:ITEMS.size = CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[])Pero, como el mensaje es una secuencia desordenada, debe asegurarse de que el atributo es el primer hijo.
SET Root.MRM = THE( SELECT DispatchedEvent.Usr.OBJECT_VERB AS verb, INVOICE.PKEY AS Inv:PKEY, (SELECT ADAPTER_ITEM.PKEY, ADAPTER_ITEM.NAME, ADAPTER_ITEM.INVOICE_KEY, ADAPTER_ITEM.PRICE FROM Database.ADAPTER_ITEM WHERE ADAPTER_ITEM.INVOICE_KEY = INVOICE.PKEY) AS Inv:ITEMS.Ite:ADAPTERITEM[], INVOICE.QUANTITY AS Inv:QUANTITY, INVOICE.CUSTOMER AS Inv:CUSTOMER, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.verb, CUSTOMER.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:PKEY, CUSTOMER.FIRSTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:FIRSTNAME, CUSTOMER.LASTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:LASTNAME, CUSTOMER.ADDRESS AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADDRESS, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.verb, ADDRESS.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:PKEY, ADDRESS.FIRSTLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:FIRSTLINE, ADDRESS.SECONDLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:SECONDLINE, ADDRESS.CODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CODE, ADDRESS.CITY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CITY, ADDRESS.COUNTRY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:COUNTRY, CUSTOMER.CCODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:CCODE , 'JDBCConnector_' ||CAST(DispatchedEvent.Usr.EVENT_ID AS CHARACTER) ||'ibm_events' ||CAST(DispatchedEvent.Usr.EVENT_TIME AS CHARACTER FORMAT 'yyyyMMddhhmmss.SSS') AS Inv:ObjectEventId FROM Database.ADAPTER_INVOICE AS INVOICE, Database.ADAPTER_CUSTOMER AS CUSTOMER, Database.ADAPTER_ADDRESS AS ADDRESS WHERE INVOICE.PKEY = DispatchedEvent.Usr.OBJECT_KEY AND CUSTOMER.PKEY = INVOICE.CUSTOMER AND ADDRESS.PKEY = CUSTOMER.ADDRESS); CREATE FIRSTCHILD OF Root.MRM.Inv:ITEMS TYPE NameValue NAME 'size' VALUE CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[]);
Pruebe la nueva funcionalidad añadiendo un par de elementos a la tabla ITEM o bien añada o modifique una factura que utilice estos artículos:
--<ScriptOptions statementTerminator="@"/> INSERT INTO ADAPTER_ITEM (NAME,INVOICE_KEY,PRICE) VALUES ('Trousers', 'inv10', 2.17)@ INSERT INTO ADAPTER_ITEM (NAME,INVOICE_KEY,PRICE) VALUES ('Jumpers', 'inv10', 12.00)@ INSERT INTO ADAPTER_INVOICE VALUES ('inv10', '', 10, 'cust1')@
Inserte el SQL anterior en un nuevo script SQL en el proyecto de diseño de datos AdapterMigration en el Explorador de proyectos de datos. A continuación, pulse el botón derecho del ratón en el proyecto dentro de la vista Explorador de proyectos de datos y seleccione "Ejecutar SQL". Esto hará que el flujo se ejecute y que aparezca el siguiente mensaje en la cola ADAPTERMIGRATION.INVOICE:
<?xml version="1.0"?> <Q1:ADAPTERINVOICE xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERINVOICE" xmlns:Q4="http://dbinput/amartens" xmlns:Q3="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERADDRESS" xmlns:Q2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERCUSTOMER" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" verb="Create"> <Q1:PKEY>inv10</Q1:PKEY> <Q1:ITEMS size="2"> <Q4:ADAPTERITEM> <PKEY>9</PKEY> <NAME>Trousers</NAME> <INVOICE_KEY>inv10</INVOICE_KEY> <PRICE>2.17E+0</PRICE> </Q4:ADAPTERITEM> <Q4:ADAPTERITEM> <PKEY>10</PKEY> <NAME>Jumpers</NAME> <INVOICE_KEY>inv10</INVOICE_KEY> <PRICE>1.2E+1</PRICE> </Q4:ADAPTERITEM> </Q1:ITEMS> <Q1:QUANTITY>10</Q1:QUANTITY> <Q1:CUSTOMER>cust1</Q1:CUSTOMER> <Q1:ADAPTERCUSTOMER> <Q2:ADAPTERCUSTOMER verb="Retrieve"> <Q2:PKEY>cust1</Q2:PKEY> <Q2:FIRSTNAME>Barney</Q2:FIRSTNAME> <Q2:LASTNAME>Rubble</Q2:LASTNAME> <Q2:ADDRESS>add1</Q2:ADDRESS> <Q2:ADAPTERADDRESS> <Q3:ADAPTERADDRESS verb="Retrieve"> <Q3:PKEY>add1</Q3:PKEY> <Q3:FIRSTLINE>MP211</Q3:FIRSTLINE> <Q3:SECONDLINE>IBM Hursley Park</Q3:SECONDLINE> <Q3:CODE>SO21 2JN</Q3:CODE> <Q3:CITY>Winchester</Q3:CITY> <Q3:COUNTRY>GB</Q3:COUNTRY> </Q3:ADAPTERADDRESS> </Q2:ADAPTERADDRESS> <Q2:CCODE>Dev</Q2:CCODE> </Q2:ADAPTERCUSTOMER> </Q1:ADAPTERCUSTOMER> <Q1:ObjectEventId>JDBCConnector_160ibm_events20101108020022.046</Q1:ObjectEventId> </Q1:ADAPTERINVOICE>