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

Rexx tool to extract table names from DBRMLIB


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

New User


Joined: 20 Nov 2008
Posts: 13
Location: bangalore

PostPosted: Mon Jul 19, 2010 2:34 pm
Reply with quote

Hi,
I am completely new to rexx. I have a requirement of extracting all table names from DBRMLIB which is the output of precompilation stage of a Cobol db2 program. DBRMLIB would contain all the sql statements used in a program. It would not be in proper format.
The following are the issues/requirements for the rexx search tool in DBRMLIB.
• The table names spans across more than one line.
• DBRMLIB would contain the select query or insert query or update or declare cursor.
For all of these, we need to extract the table names between the keywords as mentioned below

So the approach we can think of is as follows

For Select Query
All the words between the keywords “FROM” and “WHERE” or only “from”

For declaration of cursor

• All the words between “Declare” and “table”
Example:
DECLARE table1 TABLE (col1,col2)

• Similar to select query: All the words between “from” and “where”

For update query (if any)
All the words between the keywords “ update” and “set”

For insert query(if any)
• All the words between keywords “into” and where”
• All the words between keywords “into and “select”
Example: Insert into tablename (Select * from tablename)
• All the words between keywords “into and “values”

For join query (if any)
For the queries which involve joins, we need to take the table names before and after the word “left outer join” or “right outer join”
“Inner Join” or “outer join” and so on

Can anyone please help me on this.Is multiple line parsing available in REXX?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 19, 2010 2:54 pm
Reply with quote

well, between your lack of experience with REXX and the obvious ignorance of DB2 on the part of the requirement maker,
you have a real task ahead of you.

does anyone at your organization realize that as of DB2 version 8, all DBRM's are written in UTF - which means this idiot REXX tool won't work?

also, there are much better ways to extract the information that you are looking for. Such as interrogating the db2 system tables for plans/packages that refer to Tables.
I will leave this to someone else to provide proper guidance,
since it is obvious you do not want to perform searches either here in this forum
(this subject has been repeatedly posted)
or in google.

as far as multiline parse - what you have to do it combine the lines (concatenate) so they become one string, instead of two.
Then you can parse your information.

also, CREATE Table is DDL not DML,
and as such,
you will not find a CREATE table in your DBRMLIB.

you would be better off spending your time learning what is contained in the IBM systems tables and extracting this information from there.
which would tell you the plans (programs)/packages that affect tables.
There was a post recently about CRUT matrix which would also provide this info.
Back to top
View user's profile Send private message
Neena John

New User


Joined: 20 Nov 2008
Posts: 13
Location: bangalore

PostPosted: Mon Jul 19, 2010 3:48 pm
Reply with quote

My basic requirement is to map each table to an application ie say table1 is used in aaa application.

Since there are no naming convention followed for the table(in our system) which indicates the application, i can t go by creator in sysibm tables.

So we thought we would go for DBRMLIB say the pds/library "ndvr.aaa.prod.dbrmlib"
which gives the list of tables used by the application.

I checked in various sysibm tables but could not get this mapping anywhere.
Thats y we thought of going for rexx tool for parsing..
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 19, 2010 4:34 pm
Reply with quote

please do not waste our time reiterating your reasons for doing something.

there are several suggestions in this thread,
titled, interestingly enough: finding the list of programs using db2 tables using rexx

and stop using the word applications when you mean programs/modules.
you will only derive programs/modules from dbrms, not applications,
though you can derive the application name (somehow) from the pds name.

you could always build a separate cross-reference of application to program.
Back to top
View user's profile Send private message
Neena John

New User


Joined: 20 Nov 2008
Posts: 13
Location: bangalore

PostPosted: Mon Jul 19, 2010 5:07 pm
Reply with quote

Pls dont over react.
When I meant applications,it is application and not programs.
I know to differentiate an application and programs
I dont want to continue this discussion any more
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Jul 19, 2010 5:17 pm
Reply with quote

Quote:
My basic requirement is to map each table to an application ie say table1 is used in aaa application.


Quote:
I know to differentiate an application and programs ...


looks like not really

application is an abstract concept,

while You might be able to find automatically relations between tables, plans, programs, modules

the only place to find such info for applications would be the documentation
( naturally if such thing exists in Your organization )

Quote:

I dont want to continue this discussion any more


thanks &deity, given Your attitude it would be a waste of time for everybody icon_evil.gif
Back to top
View user's profile Send private message
prasadchikane

New User


Joined: 18 Feb 2014
Posts: 9
Location: India

PostPosted: Mon Jul 13, 2015 12:55 pm
Reply with quote

Hello Neena,

I am not sure if if my post is helpful now.
DBRMLIB might not be helpful since it wont be in a usable format.
You can try to use the actual queries written in your programs and execute EXPLAIN query on them.

The EXPLAIN query would make an entry into the PLAN_TABLE where you have a TNAME coulmn which provides you all the tables accessed in the query.

Hope this helps.

Thanks,
Prasad
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Jul 13, 2015 2:12 pm
Reply with quote

Quote:
I am not sure if if my post is helpful now.


just answer the question Yourself ...
FIVE Years have gone by icon_cool.gif
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 Running REXX through JOB CLIST & REXX 13
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Load new table with Old unload - DB2 DB2 6
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts run rexx code with jcl CLIST & REXX 15
Search our Forums:

Back to Top