Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

REXX exec to copy part of code to a file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX
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    Post subject: REXX exec to copy part of code to a file
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

Moderator Team Head


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

PostPosted: Tue Oct 20, 2009 12:54 pm    Post subject: Re: REXX exec to copy part of code to a file
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    Post subject:
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    Post subject: Reply to: REXX exec to copy part of code to a file
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: 8593
Location: Back in jolly old England

PostPosted: Tue Oct 20, 2009 1:51 pm    Post subject:
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    Post subject:
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: 8593
Location: Back in jolly old England

PostPosted: Tue Oct 20, 2009 2:03 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: REXX exec to copy part of code to a file
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: 8593
Location: Back in jolly old England

PostPosted: Tue Oct 20, 2009 4:15 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Oct 21, 2009 4:00 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> CLIST & REXX All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us