need REXX code to extract all the SQLs in COBOL pgm

suresh1624 · New User Joined: 21 Nov 2007 Posts: 28 Location: chennai

Hi All,
My requirement is as follows

1) Process each member from the PDS given and extract all the SQL statements
into another PDS with the same member name. I need to skip the SQLs with FETCH
or CLOSE or OPEN statements.
2) All the i/p members contain only COBOL pgms.If no SQLs found in the member skip it.
3) All SQL statements are coded between EXEC and END-EXEC.

Can any one help me please with the source code ? I'm very new to REXX.

ofer71 · Posted: Sat Feb 09, 2008 3:37 pm

I can't help you "with the source code", but is is pretty simple mission:
1. Loop through all members using LMMLIST or DSLIST.
2. For each member, use the ISPF service VIEW with the MACRO option.
3. Write a macro that use the FIND command to find every EXEC SQL which is not commented.
4. Use EXECIO to create the new members.

O.

expat · Posted: Sat Feb 09, 2008 3:54 pm

Wouldn't it be easier to take a look through the DB2 tables, PACKSTMT or something like that, or the DBRM library to find the SQL code.

I believe that this is already extracted by DB2 at pre compile time.

Why extract what is already extracted ?

suresh1624 · New User Joined: 21 Nov 2007 Posts: 28 Location: chennai

What is PACKSTMT? The queries in the DBRM are written in a coded format.

ofer71 · Posted: Sat Feb 09, 2008 4:17 pm

But you can get the list of members that contains SQL from the DBRM library..... it will cost you some search time...

O.

suresh1624 · New User Joined: 21 Nov 2007 Posts: 28 Location: chennai

ofer61,
Thanks for your help, but i'm new to rexx and it take time to go through the manual and understand what you said; Step by step procedure ll be helpful if had a little knowledge in REXX;any way thanks for your time;

suresh1624 · New User Joined: 21 Nov 2007 Posts: 28 Location: chennai

Can any one of u help me with the source code please. I think the logic is already given by ofer71 but to implement it takes time for me as i m new to rexx. Pls help me

Thanks,
Suresh.

ofer71 · Posted: Mon Feb 11, 2008 11:41 am

Well, I can do the work for you in about 6-8 hours. My rate is $100 per hour + tax. You will get a tested and working code.

O.

suresh1624 · New User Joined: 21 Nov 2007 Posts: 28 Location: chennai

ofer71,
its k; i understand that i shouldn't expect source code;

stodolas · Posted: Mon Feb 11, 2008 6:26 pm

$100/hour is pretty cheap for freelance consulting. You should take him up on that

superk · Posted: Mon Feb 11, 2008 6:42 pm

suresh1624 · New User Joined: 21 Nov 2007 Posts: 28 Location: chennai

Hi All,
Here is the code which i asked. I need not pay anyone now :lol:

diwa_thilak · Posted: Fri Feb 29, 2008 6:44 am

Good Job techies,

You have introduced one new rexx programmer to the mainframe world !!!

( Suresh i beleive you have written the code).

anyway thanks for sharing the code in forum.

Arunraj Murugaiyan · New User Joined: 27 Nov 2015 Posts: 5 Location: INDIA

Thanks Suresh n team,

I am working on same kind of requirement and this code make easy my work.
Thank you all once again

.

-Arun

Rohit Umarjikar · Posted: Mon Nov 30, 2015 9:13 pm

Welcome!!
JACKPOT 777 for you