Topic
  • 3 replies
  • Latest Post - ‏2013-11-22T05:32:34Z by lianggz1010
mor
mor
11 Posts

Pinned topic DS 2.2.1.0 visual explain unknown error -99999

‏2012-11-05T14:24:23Z |
DS 2.2.1.0 visual explain unknown error -99999

Using Data Studio 2.2.1.0 (32bit) on Win7 (64bit) connecting to a Linux-x64 DB2 v9.7 DPF database.

Problem is that the creation of a visual-explan plan fails for one environment (production) , but works fine for development.

I know it is not an access-rights issue because I'm able to see explain plans on production when using a different client tool (non-IBM) that also uses jdbc. The problem only lies in the DS 2.2.1 client tool.

The symptom is "Failed to collect explain data for the query access plan from the target data server with RC= -99999.
Explanation: unknown
User Response: Check Error Log and turn on Visual Explain trace in the 'Collect Explain Data' dialog for more information.

I enabled the 'Trace the collection of explain data' on the 'Collect Explain Data' General Settings page, and find the "sp.trc" file in a sub-directory of the %temp% tree, and that file shows the text below.
IBM Data Studio 2.2.1 Visual Explain stored procedure trace starts at 2012-11-05 14:20:09.692

Database Info: EXPPRD01 http://V9.7 jdbc:db2://myhost.idx.corpdmz.com:50001/EXPPRD01:retrieveMessagesFromServerOnGetMessage=true;.

Input parms:

Retain explain data in db=FALSE

PATH=C:\Users\vuser~1\AppData\Local\Temp\

PROPS={luwCurrentSchemaPreference=MDW, luwCurrentOptProfPreference=}

XMLIN={luwColumnGroupStatPerference=Y, sqlStmtDelimiterPreference=;}

API IS SUPPORT=FALSE

OTHER PARMS={}

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>db2luw20459E</key><string>SQLCODE: 20459, SQLSTATE: 01H55 ,(Extra message: <![CDATAError: Bind failure. Pos=0; Line=28712;]>)</string></dict></plist>

xmlInput

========

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>MAJOR_VERSION</key>
<integer>1</integer>
<key>MINOR_VERSION</key>
<integer>0</integer>
<key>REQUESTED_LOCALE</key>
<string>en_GB</string>
<key>RETAIN</key>
<string>N</string>
<key>TRACE</key>
<string>Y</string>
<key>SQL_TEXT</key>
<string>select * from syscat.tables where type=&apos;T&apos; and tabschema=&apos;ODM&apos;</string>
<key>CURRENT SCHEMA</key>
<string>EDW</string>
<key>STMT_DELIM</key>
<string>;</string>
<key>RETURN_COLUMN_STATS</key>
<string>Y</string>
</dict>
</plist>
Special Registers

=================

{CURRENT SCHEMA=MDW}

xmlinProps

==========

{STMT_DELIM=;, RETURN_COLUMN_STATS=Y}

xmlMessage

==========

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>db2luw20459E</key><string>SQLCODE: 20459, SQLSTATE: 01H55 ,(Extra message: <![CDATAError: Bind failure. Pos=0; Line=28712;]>)</string></dict></plist>

xmlOutput

=========

IBM Data Studio 2.2.1 Visual Explain stored procedure trace finishes at 2012-11-05 14:20:10.583.
Updated on 2012-12-12T05:14:11Z at 2012-12-12T05:14:11Z by lianggz1010
  • lianggz1010
    lianggz1010
    12 Posts

    Re: DS 2.2.1.0 visual explain unknown error -99999

    ‏2012-12-12T05:14:11Z  
    This is a known issue. The following link contains the detail about it:
    http://www-01.ibm.com/support/docview.wss?uid=swg1IC61569

    The local fix is:
    Have only one set of explain tables, either the session user schema or the SYSTOOLS schema.

    Any further problem with it, kindly please let me know. Thanks a lot.

    Thanks and Best Regards
  • Thisisvijju
    Thisisvijju
    1 Post

    Re: DS 2.2.1.0 visual explain unknown error -99999

    ‏2013-11-20T06:51:42Z  
    This is a known issue. The following link contains the detail about it:
    http://www-01.ibm.com/support/docview.wss?uid=swg1IC61569

    The local fix is:
    Have only one set of explain tables, either the session user schema or the SYSTOOLS schema.

    Any further problem with it, kindly please let me know. Thanks a lot.

    Thanks and Best Regards

    I have tables only under session user schema, but I still face the same problem. Any help regarding this is very much appreciated.

    Failed to collect explain data for the query access plan from the target data server with RC= -99999

    from the trace file

    xmlMessage
    ==========
    <?xml version="1.0" encoding="UTF-8"?>
    <plist version="1.0">
    <dict>
    <key>db2luw20459E</key><string>SQLCODE: 20459, SQLSTATE: 01H55 ,(Extra message: <![CDATA[Error: Bind failure. Pos=0; Line=28712;]]>)</string></dict></plist>

    xmlOutput
    =========

  • lianggz1010
    lianggz1010
    12 Posts

    Re: DS 2.2.1.0 visual explain unknown error -99999

    ‏2013-11-22T05:32:34Z  

    I have tables only under session user schema, but I still face the same problem. Any help regarding this is very much appreciated.

    Failed to collect explain data for the query access plan from the target data server with RC= -99999

    from the trace file

    xmlMessage
    ==========
    <?xml version="1.0" encoding="UTF-8"?>
    <plist version="1.0">
    <dict>
    <key>db2luw20459E</key><string>SQLCODE: 20459, SQLSTATE: 01H55 ,(Extra message: <![CDATA[Error: Bind failure. Pos=0; Line=28712;]]>)</string></dict></plist>

    xmlOutput
    =========

    Data Studio is currently depending on  EXPLAIN_SQL stored procedure to generate access plan graph. The possible reason for this issue can be the EXPLAIN_SQL procedure package needs to be rebound (maybe the database was moved up a fixpack without running db2updv9 for example) and the user currently calling EXPLAIN_SQL doesn't have bind authority. 

    Could you have a try to rebind sqllib/bnd/db2ExplainRtns.bnd and things should be fine. The command is "db2 connect to SAMPLE; db2 bind sqllib/bnd/db2ExplainRtns.bnd". If a user with bind authority calls the procedure it should also automatically rebind itself. 

     

     

    Thanks and Best Regards
    ------------------------------------------
    Liang Gaozhong