Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: list SQL-statements send to DB2

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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)

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    syscat.statements
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •