Topic
  • 5 replies
  • Latest Post - ‏2013-02-26T06:58:46Z by lianggz1010
SystemAdmin
SystemAdmin
1632 Posts

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

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

    ‏2013-02-22T07:48:00Z  
    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

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

    ‏2013-02-22T16:08:48Z  
    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
    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

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

    ‏2013-02-25T08:14:24Z  
    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:

    <pre class="jive-pre"> 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. </pre>
    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

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

    ‏2013-02-25T16:07:45Z  
    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
    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

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

    ‏2013-02-26T06:58:46Z  
    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?
    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