Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2013-02-26T06:58:46Z by lianggz1010
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

Pinned topic Data Studio 3.2 - Collect Diagnostic Data - Bad SQL Query support file

‏2013-02-21T20:34:07Z |
When using Data Studio "Capture Query Environment" - Collect Diagnostic Data about the Query the support file that SHOULD capture the Query - named: "11111.111.111.D130221.V9111.FB80.sql" does not contain the query or usefull support information:

In Data Studio v 3.1.1 the contents of the *.sql file would appear simmilar to:


************************************************************************ **From Query Tuner Query Environment Capture                          ** **DB2 Version 10 NFM                                                  ** ************************************************************************ ************************************************************************ **PMR number :11111,111,111   Documentation ID: 2222                  ** ************************************************************************ ************************************************************************ **Sql statements                                                      ** ************************************************************************ EXPLAIN ALL SET QUERYNO = 1 FOR SELECT 
"S71".
"/B28/S_WWD58VI" AS 
"/CPMB/WWD58VI" ,
"S72".
"/B28/S_WWDHNQR" AS 
"/C PMB/WWDHNQR
" ,"S73
"."/B28/S_WWDYUH0
" AS           
"/CPMB/WWDYUH0" ,
"S74".
"/B28/S_WWDQK8W" AS 
"/CPMB/WWDQK8W" ,
"S75".
"/B28/ S_WWD92ZW
" AS "/CPMB/WWD92ZW
" ,"S76
"."/B28/S_WWDKPUW
" AS 
"/CPMB/WWDKPUW" ,
"S77".
"/B28/S_WWD2TDK" AS 
"/CPMB/WWD2TDK" ,
"S78".
"/B28/ S_WWD2T03
" AS "/CPMB/WWD2T03
" ,"S79
"."/B28/S_WWDPGBT
" AS 
"/CPMB/WWDPGBT" ,
"S80".
"/B28/S_WWDMUX3" AS 
"/CPMB/WWDMUX3" ,
"S81".
"/B28/ S_WWDVHW9
" AS "/CPMB/WWDVHW9
" ,"S82
"."/B28/S_WWDEYEE
" AS 
"/CPMB/WWDEYEE" , SUM ( 
"F".
"/B28/S_SDATA" ) AS 
"/CPMB/SDATA" FROM


In Data Studio v 3.2 the contents now include this useless Explain Table Insert SQL.. Not the problematic SQL support needs:


********************************************* ** ADMIN_INFO_SQL V1.4.0 2011/10/12 PM49717** ** Plan, Profile and Accelerator Inserts   ** ********************************************* ************************* **  Start Plan Insert  ** ************************* INSERT INTO SAPR3.PLAN_TABLE(QUERYNO,QBLOCKNO,APPLNAME,PROGNAME,PLANNO,M ETHOD,CREATOR,TNAME,TABNO,ACCESSTYPE,MATCHCOLS,ACCESSCREATOR,ACCESSNAME, INDEXONLY,SORTN_UNIQ,SORTN_JOIN,SORTN_ORDERBY,SORTN_GROUPBY,SORTC_UNIQ,S ORTC_JOIN,SORTC_ORDERBY,SORTC_GROUPBY,TSLOCKMODE,TIMESTAMP,REMARKS,PREFE TCH,COLUMN_FN_EVAL,MIXOPSEQ,VERSION,COLLID,ACCESS_DEGREE,ACCESS_PGROUP_I D,JOIN_DEGREE,JOIN_PGROUP_ID,SORTC_PGROUP_ID,SORTN_PGROUP_ID,PARALLELISM _MODE,MERGE_JOIN_COLS,CORRELATION_NAME,PAGE_RANGE,JOIN_TYPE,GROUP_MEMBER ,IBM_SERVICE_DATA,WHEN_OPTIMIZE,QBLOCK_TYPE,BIND_TIME,OPTHINT,HINT_USED, PRIMARY_ACCESSTYPE,PARENT_QBLOCKNO,TABLE_TYPE,TABLE_ENCODE,TABLE_SCCSID, TABLE_MCCSID,TABLE_DCCSID,ROUTINE_ID,CTEREF,STMTTOKEN,PARENT_PLANNO,BIND
Updated on 2013-02-26T06:58:46Z at 2013-02-26T06:58:46Z by lianggz1010
  • lianggz1010
    lianggz1010
    12 Posts
    ACCEPTED ANSWER

    Re: Data Studio 3.2 - Collect Diagnostic Data - Bad SQL Query support file

    ‏2013-02-22T07:48:00Z  in response to SystemAdmin
    Hi Rod,

    For DB2 for z/OS v10, Data Studio v3.2 began to depend on the stored procedure ADMIN_INFO_SQL to collect diagnostic data in Capture Query environment. The new *.sql file you saw is the output from this stored procedure. This is a bit different from that generated from previous version of Data Studio. For a given SQL statement, The new PLAN_TABLE insert SQL in *.sql file is more helpful for IBM service to exactly know what kind of problem query's access plan is on the target subsystem.

    Any further concern about it, kindly please let me know. Thanks a lot.

    Thanks and Best Regards

    Liang Gaozhong
    • SystemAdmin
      SystemAdmin
      1632 Posts
      ACCEPTED ANSWER

      Re: Data Studio 3.2 - Collect Diagnostic Data - Bad SQL Query support file

      ‏2013-02-22T16:08:48Z  in response to lianggz1010
      Is support aware of this? On a PMR I opened the other day and sent the 'Service SQL' for the query and recieved the following response:

      
      IBM_SUPPORT. A2/21/13 12:26 PM action taken:  Hello Rod, For some reason the file D130221.V9001.FB80.sql  only has INSERTs into PLAN_TABLE, Etc. Can you send us the actual problem SQL ? action planned: wait 
      
      for cust DOC.
      
      • lianggz1010
        lianggz1010
        12 Posts
        ACCEPTED ANSWER

        Re: Data Studio 3.2 - Collect Diagnostic Data - Bad SQL Query support file

        ‏2013-02-25T08:14:24Z  in response to SystemAdmin
        Hi Rod,

        Thanks so much for pointing out this. We will report this issue to the ADMIN_INFO_SQL development team to be aware of, so that the problem SQL can be considered to be included in future release. Thanks again.

        Thanks and Best Regards

        Liang Gaozhong
        • SystemAdmin
          SystemAdmin
          1632 Posts
          ACCEPTED ANSWER

          Re: Data Studio 3.2 - Collect Diagnostic Data - Bad SQL Query support file

          ‏2013-02-25T16:07:45Z  in response to lianggz1010
          Am I understanding that Data Studio release 3.2.0.0 does not provide IBM support the neecessary 'Service SQL' query to allow them to diagnose the problem?

          Given that our primary use of Data Studio is to send Service SQL to support is the reccommendation that we have all our users; back off this release of Data Studio and revert to v3.1.1 if they will be sending 'Collect Diagnostic Data' into support for analysis?
          • lianggz1010
            lianggz1010
            12 Posts
            ACCEPTED ANSWER

            Re: Data Studio 3.2 - Collect Diagnostic Data - Bad SQL Query support file

            ‏2013-02-26T06:58:46Z  in response to SystemAdmin
            Hi Rod,

            For Data Studio 3.2, actually, the problem query is already included in the collected data, but not in the *.sql file like before. We will broadcast it to our IBM support people ,and let them know how to extract the query from uploaded files. It is not required to revert to v3.1.1, and no any extra action required for you.

            Any further concern about it, kindly please let me know. Thanks a lot.

            Thanks and Best Regards

            Liang Gaozhong