Topic
  • No replies
NourOssama
NourOssama
1 Post

Pinned topic Multiple Queries sent as one parameter to User defined SQL DB2 Connector Stage

‏2014-01-05T21:50:03Z |

Hello,

We have a transaction that consists of more than one sql statement.

For Example: INSERT INTO TEST_TAB1 (COL1) VALUES (1);INSERT INTO TEST_TAB1 (COL1) VALUES ('DDD');INSERT INTO TEST_TAB1 (COL1) VALUES (3);INSERT INTO TEST_TAB1 (COL1) VALUES (4);

We tried assigning these statements to a parameter and passing it to DB2 Connector Stage with the "Write Mode" set to "Insert". Datastage seems to read the first statement only "INSERT INTO TEST_TAB1 (COL1) VALUES (1);",and  ignores the rest.

The second option was sending the parameter as "User-defined SQL", this worked however, although there is a reject link where the second statement should be sent "INSERT INTO TEST_TAB1 (COL1) VALUES ('DDD');" (as we are trying to insert a string into a column of type int), we cannot catch the error and the whole job aborts. We only need to catch the error that occurred though the reject link, and the statement that caused this error.

Is this the only way this could be done? If anyone has ever tried implementing this differently or if there is something we're missing, please let  us know.

Thanks.