update insert

[DS_MJ]

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,

[rleishman]

I'm not a DB2 user, but I'll have a go...

I think you're barking up the wrong tree. The DS log message you pasted in is just showing you the columns in your plug-in (look at the Columns tab of the DB2 plug-in stage) - this is before it even gets to the SQL.

The question marks in your SQL are known as placeholders, or bind variables, or (as in your log) parameters. The question mark itself is not intelligent - it does not know which incoming value should be used to replace it. Eg. How does it know to use the Amount to replace the first question-mark in your SQL? Answer: DS supplies the bind variables in sequence - the first variable passed replaces the first question mark, the second variable replaces the second mark, etc.

What I think you've done is supplied more question-marks than there are columns

Take a look at the auto-generated SQL (keep a copy of your override SQL elsewhere). It should be of the form:

Code: Select all

UPDATE EDBCUST.ACCT_LEDGER 
SET AMOUNT=?,QUANTITY=?,...
WHERE ACCT_NBR=?
AND ITEM_DETAIL_ID=? 
AND LEDGER_DETAIL_TYPE=? 
AND LEDGER_AMT_TYPE=?

Note that every column is used only once. The non-key columns are used in the SET clause, and the Primary Key columns are used in the WHERE clause.

For your SQL to work, you need to use the columns only once, and in exactly the same order they are used in the auto-generated SQL.

OK, on to the solution...

What you are trying to do is to conditionally update the table. ie. Only update the table if a certain condition holds. The condition is dynamic (it depends on the incoming data) rather than static (eg. WHERE status = 'X').

A user-defined SQL could be used to employ static conditions, but I cannot think of a way to make it perform dynamic conditions.

What you need to do is to determine whether you want to perform the update BEFORE you get to the DB2 stage, ie. in a transformer. To do this, you need to get the date of the pre-existing row to compare to the incoming row.

Here's where it gets thorny:
- If EDBCUST.ACCT_LEDGER is a small(ish) table (say, <100K rows) you could drop it down to a hash file and perform a hash-file lookup. Compare the old date to the incoming date in the transformer constraint and voila - only the newest rows will go to the DB2 stage.
- If EDBCUST.ACCT_LEDGER is large or will become so, you will be copying EVERY ROW of the table down to a hash file. If the job runs regularly then this would have tragic performance. Some people advocate keeping the hash table permanently and updating it with the same changes that go to the table. I'm not a fan of this method because you just can't tell if they ever get out of synch.

My preferred solution to conditional updates to large tables is to let the database do the work (that's what they're good at). Either:
- Load the incoming data file into the database (as a temp table) before you start and left-join the target table to it in the source DB2 stage. Then you have both the old and new values from the get-go. Or...
- Instead of writing inserts and updates to the real target table, write them to an empty temp table with the same columns instead (you can use a plug-in bulk loader). Then perform an INSERT and an UPDATE statement to merge the enitre contents of the temp table to the real target.

Good luck,
Ross.

[DS_MJ]

Hello rleishman:

Thanks for your prompt reply.

What I think you've done is supplied more question-marks than there are columns

I had used the SQL generated query and then copied it and pasted that query in the user defined query and then modifed to compare.
However, there was definitely something wrong with the query.

Using Hash's not fesiable since the table is very big and keeps growing .

- Instead of writing inserts and updates to the real target table, write them to an empty temp table with the same columns instead (you can use a plug-in bulk loader). Then perform an INSERT and an UPDATE statement to merge the enitre contents of the temp table to the real target.

We are counting each second for each job to finish hence looked for something else.


But came up with the Solution:
What I did wrong was name the column that needed to be updated as a key so it did not update that column. Hence the compare did not work.

However there was another column that had the same date as LAST_MOD_DTS so made that other column as the key column and got LAST_MOD_DTS updated and then do the comparison.

So:


UPDATE TABLE_NAME SET
COL1=?,COL2=?,COL3=?,...........,LAST_MOD_DTS=?, WHERE COL1=?,COL2=?,COL3=?,..........., AND LAST_MOD_DTS < ?;
This works.

[rleishman]

Nice.

Was the spare column you used already in the table? Or did you just pass an extra column with the same value to the DB2 plugin?

If the column already existed, then it is no longer being maintined by your job and will quickly get out of date.

If the latter; clever! I had not thought of passing columns that dont exist to the plugin and then just leveraging the user defined SQL. If you didn't do it this way and have some free time then try it out and tell me if it works.

[DS_MJ]

Was the spare column you used already in the table? Or did you just pass an extra column with the same value to the DB2 plugin?

Yes, the spare column I used is already in the table.

If the column already existed, then it is no longer being maintined by your job and will quickly get out of date.

True. But I am told that this existing column is being maintained and will always be maintained.

If the latter; clever! I had not thought of passing columns that dont exist to the plugin and then just leveraging the user defined SQL. If you didn't do it this way and have some free time then try it out and tell me if it works.

I started out with this thought but they told me they were also fighting for space and they could not add an extra column hence the idea of using the existing column.

[rleishman]

I started out with this thought but they told me they were also fighting for space and they could not add an extra column hence the idea of using the existing column.

Actually, I wasn't thinking of creating a new column is DB2, just passing an extra data item (that does not exist in the table) to the plugin.

[DS_MJ]

Actually, I wasn't thinking of creating a new column is DB2, just passing an extra data item (that does not exist in the table) to the plugin.

Cool. Yes, that is a great idea. Thanks.