View previous topic :: View next topic
|
Author |
Message |
Neena John
New User
Joined: 20 Nov 2008 Posts: 13 Location: bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Neena John
New User
Joined: 20 Nov 2008 Posts: 13 Location: bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Neena John
New User
Joined: 20 Nov 2008 Posts: 13 Location: bangalore
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
prasadchikane
New User
Joined: 18 Feb 2014 Posts: 9 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Quote: |
I am not sure if if my post is helpful now. |
just answer the question Yourself ...
FIVE Years have gone by |
|
Back to top |
|
|
|