Page 1 of 1
Obtain SQL Statements from the jobs
Posted: Tue Sep 10, 2013 2:00 pm
by joycerecacho
Hi everybody.
How do I obtain a list of all SQL statements from my jobs?
I mean, the SQL statements that are inside "DB2 stages".
I need to list them.
Thank you guys!
Best Regards,
Posted: Tue Sep 10, 2013 4:14 pm
by ray.wurlod
They will be in the link properties, and therefore in those entries in DS_JOBOBJECTS for which the OLETYPE suggests a link (for example contains the word "Input" or "Output", such as "CCustomInput").
Posted: Wed Sep 11, 2013 1:19 am
by ray.wurlod
There may be other OLETYPE values. Try
Code: Select all
WHERE OLETYPE LIKE '%Input' OR OLETYPE LIKE '%Output'
Posted: Wed Sep 11, 2013 1:22 am
by ray.wurlod
If that doesn't work, and you know a fairly unique fragment of one of the SQL statements, try these commands in the Command window:
Code: Select all
SEARCH DS_JOBOBJECTS
(enter the fragment of SQL)(press Enter)
(press Enter)
LIST DS_JOBOBJECTS ID.SUP @ID FMT '40L' OLETYPE
Post your results.
Posted: Wed Sep 11, 2013 4:30 pm
by ray.wurlod
Do the same again, but use LIST.ITEM rather than LIST.
Code: Select all
SEARCH DS_JOBOBJECTS
LIST.ITEM DS_JOBOBJECTS
From this you will learn the field number in which the SQL statement resides, and which you can use in an EVAL expression to extract it.
Posted: Thu Sep 12, 2013 4:21 pm
by ray.wurlod
included in a query will extract field number 17.
I leave it up to you how to parse out the SQL statement. Something like
Code: Select all
Field(string[Index(string, "[CDATA", 1)+6, 9999], "]", 1)