sysproc.explain_sql and data studio

Justin

unread,

Oct 3, 2012, 11:01:53 AM10/3/12

to

When using data studio 3.1.1, and running query advisor. I get an error with sysproc.explain_sql (no authorized routine).

Where can I get the DDL to create the SP?

Details:

Exception Message:
The following error occurred when running stored procedure SYSPROC.EXPLAIN_SQL. SQLCODE: -440; SQLSTATE: 42884. Resolve this error and do operation again.
No authorized routine named "SYSPROC.EXPLAIN_SQL" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.63.108

Exception Stack Trace:
com.ibm.datatools.dsoe.explain.common.exception.ExplainException: com.ibm.datatools.dsoe.common.da.exception.OSCSQLException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: No authorized routine named "SYSPROC.EXPLAIN_SQL" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.63.108

at com.ibm.datatools.dsoe.explain.luw.impl.ExplainThread.callSP(Unknown Source)

at com.ibm.datatools.dsoe.explain.luw.impl.ExplainThread.explain(Unknown Source)

at com.ibm.datatools.dsoe.explain.luw.impl.ExplainThread.buildLUWExplainModelNeedSync(Unknown Source)

at com.ibm.datatools.dsoe.explain.luw.impl.ExplainThread.commonProcess(Unknown Source)

at com.ibm.datatools.dsoe.explain.luw.impl.ExplainThread.run(Unknown Source)

Caused by: com.ibm.datatools.dsoe.common.da.exception.OSCSQLException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: No authorized routine named "SYSPROC.EXPLAIN_SQL" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.63.108

... 5 more

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: No authorized routine named "SYSPROC.EXPLAIN_SQL" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.63.108

at com.ibm.db2.jcc.am.fd.a(Unknown Source)

at com.ibm.db2.jcc.am.fd.a(Unknown Source)

at com.ibm.db2.jcc.am.fd.a(Unknown Source)

at com.ibm.db2.jcc.am.yn.c(Unknown Source)

at com.ibm.db2.jcc.am.yn.d(Unknown Source)

at com.ibm.db2.jcc.am.yn.a(Unknown Source)

at com.ibm.db2.jcc.am.zn.a(Unknown Source)

at com.ibm.db2.jcc.t4.cb.h(Unknown Source)

at com.ibm.db2.jcc.t4.cb.e(Unknown Source)

at com.ibm.db2.jcc.t4.q.d(Unknown Source)

at com.ibm.db2.jcc.t4.sb.l(Unknown Source)

at com.ibm.db2.jcc.am.yn.hb(Unknown Source)

at com.ibm.db2.jcc.am.zn.rc(Unknown Source)

at com.ibm.db2.jcc.am.zn.b(Unknown Source)

at com.ibm.db2.jcc.am.CallableStatement.fc(Unknown Source)

at com.ibm.db2.jcc.am.CallableStatement.execute(Unknown Source)

... 5 more

Lennart Jonsson

unread,

Oct 4, 2012, 3:23:22 AM10/4/12

to

On 10/03/2012 05:01 PM, Justin wrote:
>
> When using data studio 3.1.1, and running query advisor. I get an error with sysproc.explain_sql (no authorized routine).
>
> Where can I get the DDL to create the SP?

Which version of db2 are you running this against?

/Lennart

[...]

Justin

unread,

Oct 10, 2012, 1:19:28 PM10/10/12

to

DB2 version 9.1 fix pack 9.

Justin

unread,

Oct 10, 2012, 2:39:10 PM10/10/12

to

Here is a helpful link -

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0025000.htm

Lennart Jonsson

unread,

Oct 11, 2012, 2:09:46 AM10/11/12

to

On 10/10/2012 08:39 PM, Justin wrote:
>
> Here is a helpful link -
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0025000.htm
>
> On Wednesday, October 10, 2012 1:19:28 PM UTC-4, Justin wrote:
>> DB2 version 9.1 fix pack 9.
>>

The link can't be reached for the moment. Some thoughts:

Is this an database that has been created in an older version (idea, the
db has not been migrated to a current version, i.e. new functionality
have not been enabled)?

Have you checked syscat.routines / syscat.procedures for existence of sp
(idea, data studio calls the procedure with wrong parameters, type and
or number of parameters)

/Lennart

[...]

Justin

unread,

Oct 12, 2012, 9:50:35 AM10/12/12

to

Your internets must be broken :)

The link works for me: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0025000.htm

As far as the database, I am not 100% sure, but it was moved to an older version and the SP does not exist when checking syscat.routines.

For clarity, here is a query:
select routinename from sysibm.sysroutines where routinename='EXPLAIN_SQL'