list SQL-statements send to DB2

Wim · #1 (**permalink**) 02-28-05, 09:41

I have to find out what SQL statement a proprietary application sends to our DB2 database to generate a certain report. I used ODBC-trace to find out, but I can't find an SQL statement in the ODBC-log. I think the application uses precompiled/static SQL.

How should I proceed ?

With kind regards
Wim

UDB 8.1 on AIX

J Petruk · #2 (**permalink**) 02-28-05, 10:10

You can do a snapshot for dynamic SQL statements on the database, if it's dynamic SQL.

Is it ODBC, or a a static SQL app? If the later, you would have bind files that have the static statements. There's a command "db2bfd" that could dump them for you.

The ODBC trace should show it too.

Wim · #3 (**permalink**) 02-28-05, 11:47

If I do an ODBC-trace while using our development environment (PowerBuilder), and I run an SQL statement (dynamic), I can find the SQL back in the trace log.

Code:

 pb60            b3c-ca0	ENTER SQLExecDirect 
		HSTMT               01CF16F0
		UCHAR *             0x02970418 [      -3] "select DISTINCT
"SYSCAT"."COLUMNS"."TABNAME" 
FROM "SYSCAT"."COLUMNS"
WHERE "SYSCAT"."COLUMNS"."TABSCHEMA" = 'PRAGA'\ 0"
		SDWORD                    -3

pb60            b3c-ca0	EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)
		HSTMT               01CF16F0
		UCHAR *             0x02970418 [      -3] "select DISTINCT
"SYSCAT"."COLUMNS"."TABNAME" 
FROM "SYSCAT"."COLUMNS"
WHERE "SYSCAT"."COLUMNS"."TABSCHEMA" = 'PRAGA'\ 0"
		SDWORD                    -3

In the ODBC-trace taken while this application was running, there is no SQL statement. I think that's because it's static SQL.

Code:

PRAGA           b40-e30	ENTER SQLDataSourcesW 
		HENV                01B61540
		UWORD                        1 <SQL_FETCH_NEXT>
		WCHAR *             0x01B61B28 
		SWORD                      127 
		SWORD *             0x01D2EB4C
		WCHAR *             0x01B616A8 
		SWORD                      127 
		SWORD *             0x01D2EABC

PRAGA           b40-e30	EXIT  SQLDataSourcesW  with return code 0 (SQL_SUCCESS)
		HENV                01B61540
		UWORD                        1 <SQL_FETCH_NEXT>
		WCHAR *             0x01B61B28 [       7] "praga"
		SWORD                      127 
		SWORD *             0x01D2EB4C (7)
		WCHAR *             0x01B616A8 [      19] "IBM DB2 O"
		SWORD                      127 
		SWORD *             0x01D2EABC (19)

sathyaram_s · #4 (**permalink**) 02-28-05, 11:56

SQL event monitor is the best bet ...
It captures all dynamic SQL statements and stores the text
If it is static, then you will be able to get the section number within a package , which using the syscat views can be resolved to SQL ..

Cheers
Sathyaram

Wim · #5 (**permalink**) 02-28-05, 12:44

Thank you for your comment.

I'm new to this SYSCAT.PACKAGES view.
I couln't find SYSCAT.SECTIONS or something similar. In what system table can I find the "sections" (and the original SQL) ?

Wim

sathyaram_s · #6 (**permalink**) 02-28-05, 13:18

syscat.statements

Internet Services

Web Development

Digital Marketing

Consumer Tech