DB2 STAGE - Difference between user defined sql and insert

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
wblack
Premium Member
Premium Member
Posts: 30
Joined: Thu Sep 23, 2010 7:55 am

DB2 STAGE - Difference between user defined sql and insert

Post by wblack »

What is the main difference in using the the user defined sql and a regular insert statement. I was debugging a job that wasn't working as expected and noticed that the developer had the write mode set to 'USER DEFINED SQL' for a very basic insert using job parameters. While using the same insert sql I changed the write mode to insert and noticed that the sql started acting as we though it should. Are there any under-lying differences between the two write modes?

SQL
*************************************************************
INSERT INTO #DATAREP_PARAMS.DATA_SCHEMA#.#TABLE_ROOT#_NORM_BLOCKED
(INSERTED, LSSI_RECID, LAST_REFRESHED,SUPPLIER_REGION_NUM, REASON) VALUES
('#PROCESS_START#', ORCHESTRATE.LSSI_RECID, '#PROCESS_START#', #REGION_NUM#, ORCHESTRATE.MATCH_CODE)
*************************************************************
William Black
BI-RMA
Premium Member
Premium Member
Posts: 463
Joined: Sun Nov 01, 2009 3:55 pm
Location: Hamburg

Post by BI-RMA »

Hi wblack,

By using a user defined SQL-Statement you can enter some additional logic into update-statements if needed.

For inserts I can't think of any reason to use user-defined SQL. I would personally prefer to use a transformer to add the Job parameters and let the database-stage generate the insert statement itself. It makes the job easier to maintain, because you don't have to change the SQL to add additional columns to the target-table later.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
Post Reply