webMethods Enterprise Server 5.0.1
webMethods JDBC Adapters 4.2.1
webMethods Adapter Config Tool 4.1.1
WM JDBC adapter was configured and integration components were built. Whenever a new record is entered on an incoming table, WM trigger (WMT_A4_INO) creates temp tables WMB_A4_INO and WMS_A4_INO in DB2 system. However one of the fields created by WM in temp table WMS_A4_INO is table name created as “WMB_A4_INO”. Since this does not include library name, WMT trigger fails. Here is the SQL that WM generates to create the tables and the trigger -
INSERT INTO LIB1.WMS_A4_INO VALUES( ‘WMB_A4_INO’, 0)
CREATE TRIGGER WMT_A4_INO AFTER INSERT ON DB2.LIB1.INCOMING REFERENCING new as new FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE oldNum INT; SET oldNum = (SELECT lastNum FROM LIB1.WMS_A4_INO WHERE tableName = ‘LIB1.WMB_A4_INO’);UPDATE LIB1.WMS_A4_INO SET lastNum = oldNum + 1;INSERT INTO LIB1.WMB_A4_INO (TRANSACTION_ID, wm_rowid) VALUES (new.TRANSACTION_ID, oldNum);
In the above SQL, only “WMB_A4_INO” is inserted but the query uses “LIB1.WMB_A4_INO”; it appends the library name. So, the trigger fails. Has anyone faced this problem before. Is there a resolution.
Thanks for your help.