IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

REXX exec to copy part of code to a file


IBM Mainframe Forums -> CLIST & REXX
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
alamak

New User


Joined: 06 May 2009
Posts: 5
Location: india

PostPosted: Tue Oct 20, 2009 12:29 pm
Reply with quote

Hi,

I have the following requirement:

1. Search all the members of a given pds to extract sql statements from the members and write just the sql statements to a new file.

step 1 : open the pds.
step 2: search each memeber in pds.
step 3: for each memeber search for 'exec and end-exec' and copy the code between these two statement to stem.

there can be multiple sql execs in a single module.

Can someone please help me on this...

Thanks
Back to top
View user's profile Send private message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4651
Location: Raleigh, NC, USA

PostPosted: Tue Oct 20, 2009 12:54 pm
Reply with quote

alamak wrote:
Can someone please help me on this...


Maybe. Show us what you've done so far, what you need help on, where you're stuck. Doesn't sounds like anything very difficult, but you obviously know more about what you want to do than us.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 20, 2009 1:36 pm
Reply with quote

why don't you just access the DBRM's?
Back to top
View user's profile Send private message
alamak

New User


Joined: 06 May 2009
Posts: 5
Location: india

PostPosted: Tue Oct 20, 2009 1:43 pm
Reply with quote

I havent stated as of yet... but off the three steps - the first two are fairly simple.

in reg to the step 3., consider ofr e.g.

code line 1
code line 2

exec sql
select a
from b
where c=d
order by crt_ts desc
end-exec

code line 3
code line 4

my output should contain..only the following:
exec sql
select a
from b
where c=d
order by crt_ts desc
end-exec

also., if there are multiple exec statements in a single memebr., I need to get all those statments.

so., how to keep the mentioned part of code into stem.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Oct 20, 2009 1:51 pm
Reply with quote

stem_name.number = variable
Back to top
View user's profile Send private message
alamak

New User


Joined: 06 May 2009
Posts: 5
Location: india

PostPosted: Tue Oct 20, 2009 1:57 pm
Reply with quote

Iam sorry - but can you please be a bit elobarate?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Oct 20, 2009 2:03 pm
Reply with quote

There is a sticky at the top of the CLIST/REXX forum which links to the manuals. These will explain stem variables better than I will.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 20, 2009 3:13 pm
Reply with quote

alamak,

what exactly will this 'extracted sql' be used for?
Is this just an exercise in REXX?
or is the output file (containing the SQL) going to be analyized for some purpose?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 20, 2009 3:37 pm
Reply with quote

also,
instead of having multiple stems,
your logic could be to write the stem each time you have found the END-EXEC.
Back to top
View user's profile Send private message
alamak

New User


Joined: 06 May 2009
Posts: 5
Location: india

PostPosted: Tue Oct 20, 2009 4:06 pm
Reply with quote

the output will be used for assesing sqls.

in regard to the manuals., i have cheked them but could not get what iam exactly looking for.

Each time i encounter a EXEC SQL., i can capture this line to stem., but next part is to capture everything in the module till i encounter an en-exec statment. once i encouter an end-exec i can write the whole info into stem.

And further the code should then look for thenext exec statment in the same module.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Oct 20, 2009 4:15 pm
Reply with quote

Well for me it is just common sense.

Having read the data/program/module/whatever into a stem you process the stem using a flag set to either 1 or 0 to determine if the record is written or not.

Code:

Read program into Stem.

DO aa = 1 to Stem.0
  If POS('EXEC SQL',Stem.aa) > 0
     then flagwrt = 1
  If flagwrt = 1
     then QUEUE Stem.aa
  If POS('END-EXEC',Stem.aa) > 0
     then flagwrt = 0
END

Write Queued records to output dataset or whatever
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 20, 2009 5:09 pm
Reply with quote

Quote:
the output will be used for assesing(sic) sqls.


That is the purpose of the output of EXPLAIN: Plan table.

If the bind option is EXPLAIN(YES) you have all the SQL,
in the plan table.

So instead of learning REXX to generate something that will not help you in your ultimate goal,
why don't you learn a little DB2 and dump the plan tables for your modules?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Oct 21, 2009 4:00 pm
Reply with quote

It all depends of what the "assessment" is

If the sources have been bound :

even without explain(yes) the statements are in some form in sysibm.syspackstmt.
dependencies can be found in SYSIBM.SYSPACKDEP

If you do have explain-info
A lot of parsed material is in several tables :
DSN_PREDICATE_TABLE
DSN_SORTKEY_TABLE

and even a completely to XML parsed statement can be found in
DSN_QUERY_TABLE
Back to top
View user's profile Send private message
alamak

New User


Joined: 06 May 2009
Posts: 5
Location: india

PostPosted: Wed Oct 21, 2009 5:00 pm
Reply with quote

Well.. thanks for the replies...

getting plan_table alone wont suffice my requirement. the requirement involves column definitions, key words and etc.., i was able to get a solution using rexx. which basically strips jsut the sql code into a new file.

appreciate all your inputs..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> CLIST & REXX

 


Similar Topics
Topic Forum Replies
No new posts Copy few lines from SYSOUT of 10 mill... All Other Mainframe Topics 5
No new posts Selecting the max value from a file. DFSORT/ICETOOL 3
No new posts Access library name in REXX CLIST & REXX 6
No new posts Change history of vsam file. JCL & VSAM 3
No new posts access library name in REXX CLIST & REXX 2
Search our Forums:

Back to Top