Obtain SQL Statements from the jobs

joycerecacho · Post by **joycerecacho** » Tue Sep 10, 2013 2:00 pm

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,

ray.wurlod · Post by **ray.wurlod** » Tue Sep 10, 2013 4:14 pm

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").

ray.wurlod · Post by **ray.wurlod** » Wed Sep 11, 2013 1:19 am

There may be other OLETYPE values. Try

Code: Select all

WHERE OLETYPE LIKE '%Input' OR OLETYPE LIKE '%Output'

ray.wurlod · Post by **ray.wurlod** » Wed Sep 11, 2013 1:22 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 11, 2013 4:30 pm

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.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 12, 2013 4:21 pm

Code: Select all

EVAL "@RECORD<17>"

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)