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,
Obtain SQL Statements from the jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
Obtain SQL Statements from the jobs
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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").
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There may be other OLETYPE values. Try
Code: Select all
WHERE OLETYPE LIKE '%Input' OR OLETYPE LIKE '%Output'
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
Post your results.
Code: Select all
SEARCH DS_JOBOBJECTS
(enter the fragment of SQL)(press Enter)
(press Enter)
LIST DS_JOBOBJECTS ID.SUP @ID FMT '40L' OLETYPE
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Do the same again, but use LIST.ITEM rather than LIST.
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.
Code: Select all
SEARCH DS_JOBOBJECTS
LIST.ITEM DS_JOBOBJECTS
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
EVAL "@RECORD<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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.