Querying SQL using SYSPACKSTMT (Important, production fix)
Kevin
looking for the string " AS " to identify where that syntax is used
for correlation names in the FROM statement.
The best way (I hope) that I've found is to query the STMT column of
the SYSPACKSTMT catalog table. I am hoping that some others here have
some experience doing this and can point me in the right direction.
Questions:
- Am I correct that all the embedded SQL in COBOL programs would be
stored in the SYSIBM.SYSPACKSTMT catalog table?
- The information in the STMT column does not contain only SQL text.
It seems like there are some packed fields too. Any info. about how
the info is organized would help, or where I can find this
information.
More Questions of less importance:
- I'd like to combine the SQL which is broken up into 254-byte blocks
into one long SQL statement prior to searching for the " AS ". That
way, I won't have to search for different combinations of " AS " if
that string is split between the end of one row and the beginning of
another row. Initially I was going to use SAS, with some control
break logic concatenating the SQL for a statement number to a long
string. However, SASV6 limits strings to 200 characters (ugh). Any
suggestions on how to do this (without resorting to COBOL?).
- Using Visual Basic / ADO, or MS Access / Jet ODBC I can connect to
the SYSIBM.SYSPACKSTMT table, however if the STMT row does not begin
with a character value (i.e. if it begins with a packed field for
example), then the readable text in the remainder of that row is not
shown. Any way to be able to examine the STMT fields for readable
text even if they start with packed info. would help a lot. I don't
know if I should write a separate query, use some conversion function,
use a DB2 utility to create a flat file and attach to it... lots of
options to try, but not sure which is the best.
Thanks for any help, or for any of your experiences or knowledge
querying the SYSPACKSTMT table!
Kevin Fosler
David Sharpe
Try querying SYSCAT.STATEMENTS.TEXT.
Hope this helps,
David.
Kevin
This sounds very promising!
I tried:
Select *
from SYSCAT.STATEMENTS.TEXT;
and received an error message that the statement is too long.
The FROM statement isn't correct (I can see that it needs something
like XXXX.XXXX_XXXX; instead of two "."'s.
Any help on how to query this table is appreciated. We use DB2 MVS
V6.
Usually I query SYSIBM.tablename for the catalog.
Thank you!
Kevin
On Thu, 14 Dec 2000 08:59:10 -0500, David Sharpe <dav...@ca.ibm.com>
wrote:
Sathyaram S
David had specified about UDB on Unix, NT.
Sathyaram
In article <rpdk3t87nnroo1lsr...@4ax.com>,
--
Cheers,
Sathyaram S
Sent via Deja.com
http://www.deja.com/
Serge Rielau
select TEXT from SYSCAT.STATEMENTS
Cheers
Serge
--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support
Serge Rielau
Kevin
I haven't found a good solution, so I'm going to query the
SYSIBM.SYSPACKSTMT catalog table.
Unfortunately, there is some packed information in the STMT field
which I don't understand, and isn't explained in the documentation
I've looked at so far.
I hope, at least, that by querying for the string I'm looking for, I
will be querying a complete set of the SQL in all the COBOL programs.
If this isn't true, this assumption could come back to haunt me.
Again, thanks for all your help,
Kevin
James Campbell
Kevin wrote in message ...
>I haven't found a good solution, so I'm going to query the
>SYSIBM.SYSPACKSTMT catalog table.
Good idea, SYSPACKSTMT is the place, in a DB2 for OS/390 system, where this
information is stored.
>
>Unfortunately, there is some packed information in the STMT field
>which I don't understand, and isn't explained in the documentation
>I've looked at so far.
Conceptually, a DBRM consists of
- a logical header record
- followed by a logical record for each statement.
In the output from the precompiler, each logical record is prefixed by the
characters 'DBRM' and the result split into 80 byte records. The format of
these logical records are described in SDSNMACS(DSNXDBRM), for the header,
and (DSNXNBRM) for each statement.
In SYSPACKSTMT this same information is split into 254 byte records without
the 'DBRM' prefix.
James Campbell
Hansen Corporation
P.A.
SELECT PROGNAME
, QUERYNO AS STMT
, TNAME AS TABLE
, CORRELATION_NAME
FROM PLAN_TABLE
WHERE CORRELATION_NAME <> ' '
But this requires that all your packages/plans are bound using EXPLAIN(YES)
- which probably true in production systems.
HTH
Paresh Adhia
BM...@insituform.com
Kevin : First question what OS and Platform are you on? I think it would
be very helpful if we could many of the messages in here to have that near
the top of the message.
Thanks,
Bill
Insituform Technologies, Inc.
Saint Louis, MO USA
kevin.fosler@cuna
mutual.com To: comp.databa...@list.deja.com
cc:
12/13/2000 02:28 Subject: Querying SQL using SYSPACKSTMT (Important, production
PM fix)
Please respond to
comp.databases.ib
m-db2
I have a need to query all SQL for every COBOL program in my company,
looking for the string " AS " to identify where that syntax is used
for correlation names in the FROM statement.
....
Sent via Deja.com http://www.deja.com/
Before you buy.
pm3...@attglobal.net
(extact sql from them) .
Under MVS/OS-390, see chapter 6
"Application plans panel"
"DBRM..."
"Package..."
"collection"
These pannels have either S or SQ commands that query the catalog tables
that you can use
to extract sql statements contained in the objects.
There may be other ways to get what you want via other panels/programs,
...
PM
Kevin
>On platforms like nt, there are tools (DB2BFD) to format bind files
>(extact sql from them) .
>
>Under MVS/OS-390, see chapter 6
>"Application plans panel"
>"DBRM..."
>"Package..."
>"collection"
>
>These pannels have either S or SQ commands that query the catalog tables
>that you can use
>to extract sql statements contained in the objects.
>
>There may be other ways to get what you want via other panels/programs,
>...
>
>PM
Thanks everyone for the info. About the panel info. in this response,
it sounds promising. I forwarded it to the DBA here to explain
exactly what it means :-)
In the future, I'll make it clear what version of DB2 and Operating
System my question refers to. It was my first post here and I assumed
(incorrectly) this was a mainframe DB2 newsgroup, but DB2 has grown up
a lot since I was a DB2 DBA (prior to version 3!).
Kevin