update insert
Posted: Wed Sep 21, 2005 4:55 pm
Hello:
Background:- Trying to do update / insert into DB2 directly using DB2 Plugin.
- Need to update based on a column called LAST_MOD_DTS. If on DB2 the date of LAST_MOD_DTS is LESS then the input column's LAST_MOD_DTS then go ahead and update.
- The input file's LAST_MOD_DTS column is a Char 26 and on DB2 the LAST_MOD_DTS is a TimeStamp.
- Input file: Char 26 :LAST_MOD_DTS : 2005-11-17-00.00.00.000000
Target DB2 table: Timestamp: LAST_MOD_DTS: 2005-08-17 00:00:00.000000
I want to convert the date in the input file's LAST_MOD_DTS column to timestamp so I can compare timestamp to timestamp for the update. This is my user generated Query on DB2 Plugin in SQL.
UPDATE EDBCUST.ACCT_LEDGER SET AMOUNT=?,QUANTITY=?,LEDGER_RSN_CDE=?,EFFECTIVE_DATE=?,ISPENDING=?,ISVOID=?,PCRD_ID=?,AUTH_CDE=?,AUTH_DTE=?,DOCUMENT_TYPE_CDE=?,NOTE_NBR=?,CREATE_DTS=?,MODIFIED_BY=?,LOAD_SEQ_ID=?,DIGEST=? WHERE ACCT_NBR=? AND ITEM_DETAIL_ID=? AND LEDGER_DETAIL_TYPE=? AND LEDGER_AMT_TYPE=? AND LAST_MOD_DTS < timestamp(?);
? is the Value on DB2 and LAST_MOD_DTS is the input file's data
Problem:
Some how Datastage seems to strip of Timestamp parameter. In Director it shows that the job aborted and following is the error:
"TSXfm_ACCT_LEDGER_xfmACCT_LEDGER_Video..Xfm: [IBM][CLI Driver][DB2/AIX64] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610"
TSXfm_ACCT_LEDGER_xfmACCT_LEDGER_Video..Xfm: ACCT_NBR = 50007018760 ITEM_DETAIL_ID = 3779 AMOUNT = 1.50 QUANTITY = NULL LEDGER_DETAIL_TYPE = DADJ LEDGER_AMT_TYPE = ADJ LEDGER_RSN_CDE = MC EFFECTIVE_DATE = 2004-01-23 ISPENDING = 0 ISVOID = 0 PCRD_ID = NULL AUTH_CDE = NULL AUTH_DTE = NULL DOCUMENT_TYPE_CDE = NULL NOTE_NBR = 0 CREATE_DTS = 2005-08-17-00.00.00.000000 LAST_MOD_DTS = 2005-11-17 00:00:00.000000 MODIFIED_BY = CONVERT LOAD_SEQ_ID = 17 DIGEST = NULL
See it has stripped of timestamp and shows it only as LAST_MOD_DTS = 2005-11-17 00:00:00.000000 .
Can you please help .....?
Thanks,
Background:- Trying to do update / insert into DB2 directly using DB2 Plugin.
- Need to update based on a column called LAST_MOD_DTS. If on DB2 the date of LAST_MOD_DTS is LESS then the input column's LAST_MOD_DTS then go ahead and update.
- The input file's LAST_MOD_DTS column is a Char 26 and on DB2 the LAST_MOD_DTS is a TimeStamp.
- Input file: Char 26 :LAST_MOD_DTS : 2005-11-17-00.00.00.000000
Target DB2 table: Timestamp: LAST_MOD_DTS: 2005-08-17 00:00:00.000000
I want to convert the date in the input file's LAST_MOD_DTS column to timestamp so I can compare timestamp to timestamp for the update. This is my user generated Query on DB2 Plugin in SQL.
UPDATE EDBCUST.ACCT_LEDGER SET AMOUNT=?,QUANTITY=?,LEDGER_RSN_CDE=?,EFFECTIVE_DATE=?,ISPENDING=?,ISVOID=?,PCRD_ID=?,AUTH_CDE=?,AUTH_DTE=?,DOCUMENT_TYPE_CDE=?,NOTE_NBR=?,CREATE_DTS=?,MODIFIED_BY=?,LOAD_SEQ_ID=?,DIGEST=? WHERE ACCT_NBR=? AND ITEM_DETAIL_ID=? AND LEDGER_DETAIL_TYPE=? AND LEDGER_AMT_TYPE=? AND LAST_MOD_DTS < timestamp(?);
? is the Value on DB2 and LAST_MOD_DTS is the input file's data
Problem:
Some how Datastage seems to strip of Timestamp parameter. In Director it shows that the job aborted and following is the error:
"TSXfm_ACCT_LEDGER_xfmACCT_LEDGER_Video..Xfm: [IBM][CLI Driver][DB2/AIX64] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610"
TSXfm_ACCT_LEDGER_xfmACCT_LEDGER_Video..Xfm: ACCT_NBR = 50007018760 ITEM_DETAIL_ID = 3779 AMOUNT = 1.50 QUANTITY = NULL LEDGER_DETAIL_TYPE = DADJ LEDGER_AMT_TYPE = ADJ LEDGER_RSN_CDE = MC EFFECTIVE_DATE = 2004-01-23 ISPENDING = 0 ISVOID = 0 PCRD_ID = NULL AUTH_CDE = NULL AUTH_DTE = NULL DOCUMENT_TYPE_CDE = NULL NOTE_NBR = 0 CREATE_DTS = 2005-08-17-00.00.00.000000 LAST_MOD_DTS = 2005-11-17 00:00:00.000000 MODIFIED_BY = CONVERT LOAD_SEQ_ID = 17 DIGEST = NULL
See it has stripped of timestamp and shows it only as LAST_MOD_DTS = 2005-11-17 00:00:00.000000 .
Can you please help .....?
Thanks,