Getting SQL0901N error with SQL State: 58004 on running explain plans

[marklawford]

Not having earned my DBA badge from the scouts just yet I'm a little
lost with an error I'm getting.

We've just upgraded our development database from 7.2 to 8.2 as the
first step in upgrading our wider environment. Of course, development
doesn't stop so I'm running some explain plans over some new views. The
problem is, when the view is accessed as part of the explain plan
script, the following error is returned.

[Error Code: -901, SQL State: 58004] [IBM][CLI Driver][DB2/6000]
SQL0901N The SQL statement failed because of a non-severe system
error. Subsequent SQL statements can be processed. (Reason "Error in
getting coldist_data from stats profile".) SQLSTATE=58004
The explain tables are in the MYSCHEMA schema and the target view is in
the "MAINSCH" schema. Of course, all this used to work before the
upgrade.

Can anyone shed any light on what could be the cause of the problem?
Running select over the view in isolation returns the data I expect.

The script I'm using (running over the standard explain plan tables):

DELETE FROM MYSCHEMA.EXPLAIN_INSTANCE;

EXPLAIN PLAN FOR
SELECT * FROM MAINSCH.VW_ACCOUNTTYPE_B;

SELECT O.operator_id, S2.target_id, O.operator_type, S.object_name,
CAST(O.total_cost AS INTEGER) cost
FROM MYSCHEMA.EXPLAIN_OPERATOR O
LEFT OUTER JOIN MYSCHEMA.EXPLAIN_STREAM S2
ON O.operator_id = S2.source_id
LEFT OUTER JOIN MYSCHEMA.EXPLAIN_STREAM S
ON O.operator_id = S.target_id
AND O.explain_time = S.explain_time
AND S.object_name IS NOT NULL
ORDER BY O.explain_time ASC, operator_id ASC;

ROLLBACK;

[Serge Rielau]

marklawford wrote:

Not having earned my DBA badge from the scouts just yet I'm a little
lost with an error I'm getting.

We've just upgraded our development database from 7.2 to 8.2 as the
first step in upgrading our wider environment. Of course, development
doesn't stop so I'm running some explain plans over some new views. The
problem is, when the view is accessed as part of the explain plan
script, the following error is returned.

[Error Code: -901, SQL State: 58004] [IBM][CLI Driver][DB2/6000]
SQL0901N The SQL statement failed because of a non-severe system
error. Subsequent SQL statements can be processed. (Reason "Error in
getting coldist_data from stats profile".) SQLSTATE=58004
The explain tables are in the MYSCHEMA schema and the target view is in
the "MAINSCH" schema. Of course, all this used to work before the
upgrade.

Can anyone shed any light on what could be the cause of the problem?
Running select over the view in isolation returns the data I expect.

The script I'm using (running over the standard explain plan tables):

DELETE FROM MYSCHEMA.EXPLAIN_INSTANCE;

EXPLAIN PLAN FOR
SELECT * FROM MAINSCH.VW_ACCOUNTTYPE_B;

SELECT O.operator_id, S2.target_id, O.operator_type, S.object_name,
CAST(O.total_cost AS INTEGER) cost
FROM MYSCHEMA.EXPLAIN_OPERATOR O
LEFT OUTER JOIN MYSCHEMA.EXPLAIN_STREAM S2
ON O.operator_id = S2.source_id
LEFT OUTER JOIN MYSCHEMA.EXPLAIN_STREAM S
ON O.operator_id = S.target_id
AND O.explain_time = S.explain_time
AND S.object_name IS NOT NULL
ORDER BY O.explain_time ASC, operator_id ASC;

ROLLBACK;

Try dropping and recreating the tables in misc\EXPLAIN.DDL. Maybe they
didn't migrate properly.
I recall some issue with db2exfmt in that area which I belive to be
fixed in FP10.
If that fails... support.
I see at least one PMR on the same topic (unfortunately without resolution)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[marklawford]

Serge Rielau wrote:

Try dropping and recreating the tables in misc\EXPLAIN.DDL. Maybe they
didn't migrate properly.
I recall some issue with db2exfmt in that area which I belive to be
fixed in FP10.
If that fails... support.
I see at least one PMR on the same topic (unfortunately without resolution)

Cheers
Serge

Thanks for the reply, Serge. The explain tables were rebuilt after the
upgrade but thanks for the suggestion.

I'm a little puzzled by the message "Error in getting coldist_data from
stats profile". I've located the SYSSTAT and SYSCAT.COLDIST views but I
don't understand the implication from the message.

I've yet to establish whether we have applied FP10 to be honest so I
might be chasing my own tail a little but I'm interested to learn why
this error comes up, what it means and what the resolution (ultimately)
is.

Cheers,

Mark

[Knut Stolze]

marklawford wrote:

I'm a little puzzled by the message "Error in getting coldist_data from
stats profile". I've located the SYSSTAT and SYSCAT.COLDIST views but I
don't understand the implication from the message.

A SQL0901 is something like an assertion in the DB2 code. Ideally, I'd
never hit one of those. And the result is that you usually have to contact
IBM support to get such issues resolved.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[marklawford]

Our instance of this problem has been solved.

It seems that the runstats were not computed after the upgrade and
restore that was performed. After computing these stats the explain
plans completed as expected.

Thanks to those that responded.

Mark

marklawford wrote:

Not having earned my DBA badge from the scouts just yet I'm a little
lost with an error I'm getting.

We've just upgraded our development database from 7.2 to 8.2 as the
first step in upgrading our wider environment. Of course, development
doesn't stop so I'm running some explain plans over some new views. The
problem is, when the view is accessed as part of the explain plan
script, the following error is returned.

[Error Code: -901, SQL State: 58004] [IBM][CLI Driver][DB2/6000]
SQL0901N The SQL statement failed because of a non-severe system
error. Subsequent SQL statements can be processed. (Reason "Error in
getting coldist_data from stats profile".) SQLSTATE=58004
The explain tables are in the MYSCHEMA schema and the target view is in
the "MAINSCH" schema. Of course, all this used to work before the
upgrade.

Can anyone shed any light on what could be the cause of the problem?
Running select over the view in isolation returns the data I expect.

The script I'm using (running over the standard explain plan tables):

DELETE FROM MYSCHEMA.EXPLAIN_INSTANCE;

EXPLAIN PLAN FOR
SELECT * FROM MAINSCH.VW_ACCOUNTTYPE_B;

SELECT O.operator_id, S2.target_id, O.operator_type, S.object_name,
CAST(O.total_cost AS INTEGER) cost
FROM MYSCHEMA.EXPLAIN_OPERATOR O
LEFT OUTER JOIN MYSCHEMA.EXPLAIN_STREAM S2
ON O.operator_id = S2.source_id
LEFT OUTER JOIN MYSCHEMA.EXPLAIN_STREAM S
ON O.operator_id = S.target_id
AND O.explain_time = S.explain_time
AND S.object_name IS NOT NULL
ORDER BY O.explain_time ASC, operator_id ASC;

ROLLBACK;