REXX exec to copy part of code to a file

alamak · New User Joined: 06 May 2009 Posts: 5 Location: india

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

superk · Posted: Tue Oct 20, 2009 12:54 pm

dbzTHEdinosauer · Posted: Tue Oct 20, 2009 1:36 pm

why don't you just access the DBRM's?

alamak · New User Joined: 06 May 2009 Posts: 5 Location: india

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.

expat · Posted: Tue Oct 20, 2009 1:51 pm

stem_name.number = variable

alamak · New User Joined: 06 May 2009 Posts: 5 Location: india

Iam sorry - but can you please be a bit elobarate?

expat · Posted: Tue Oct 20, 2009 2:03 pm

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.

dbzTHEdinosauer · Posted: Tue Oct 20, 2009 3:13 pm

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?

dbzTHEdinosauer · Posted: Tue Oct 20, 2009 3:37 pm

also,
instead of having multiple stems,
your logic could be to write the stem each time you have found the END-EXEC.

alamak · New User Joined: 06 May 2009 Posts: 5 Location: india

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.

expat · Posted: Tue Oct 20, 2009 4:15 pm

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.

dbzTHEdinosauer · Posted: Tue Oct 20, 2009 5:09 pm

GuyC · Posted: Wed Oct 21, 2009 4:00 pm

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

alamak · New User Joined: 06 May 2009 Posts: 5 Location: india

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