Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / December 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
marklawford - 28 Nov 2005 15:55 GMT
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 - 28 Nov 2005 23:31 GMT
> Not having earned my DBA badge from the scouts just yet I'm a little
> lost with an error I'm getting.
[quoted text clipped - 36 lines]
>
> 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
Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

marklawford - 30 Nov 2005 09:20 GMT
> Try dropping and recreating the tables in misc\EXPLAIN.DDL. Maybe they
> didn't migrate properly.
[quoted text clipped - 5 lines]
> 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 - 30 Nov 2005 09:28 GMT
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

marklawford - 16 Dec 2005 12:30 GMT
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

> Not having earned my DBA badge from the scouts just yet I'm a little
> lost with an error I'm getting.
[quoted text clipped - 36 lines]
>
> ROLLBACK;
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.