dropped a not null column from a table, error while running stats

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rahul B - 29 Aug 2007 13:17 GMT

Hi,

I dropped a not null column from a table.
After that i reorganized the table.
However, an update statement is giving error like
"(Reason "column number out of range".) SQLSTATE=58004"

So i ran stats on the table like
"RUNSTATS ON TABLE <SCHEMA.TABNAME>"

However, it gives error(warning) like
SQL2314W Some statistics are in an inconsistent state. The newly
collected
"TABLE" statistics are inconsistent with the existing "INDEX"
statistics.
SQLSTATE=01650

and i cannot find out how to run stats on indexes.
"RUNSTATS ON INDEX <INDEX_NAME. is not the syntax.

Further, is the problem coming due to this or their could be some
other problem.

Please help as it is urgent.

Thanks

Rahul

Reply to this Message

Dave Hughes - 29 Aug 2007 14:37 GMT

> However, it gives error(warning) like SQL2314W Some statistics are in
> an inconsistent state. The newly collected
[quoted text clipped - 4 lines]
> and i cannot find out how to run stats on indexes. "RUNSTATS ON INDEX
> <INDEX_NAME. is not the syntax.

Indeed. A simplified version of the RUNSTATS syntax might look something
like this:

RUNSTATS ON TABLE [schname.tbname] [WITH DISTRIBUTION]
[AND [DETAILED] INDEXES ALL]
[SET PROFILE ONLY]

See the RUNSTATS reference [1] for the full syntax, but what's above is
all I find I need to remember on a daily basis. So, in your case
something like:

RUNSTATS ON TABLE schname.tbname AND INDEXES ALL

ought to do the trick.

[1] http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/
com.ibm.db2.udb.admin.doc/doc/r0001980.htm

Cheers,

Dave.

Reply to this Message

Rahul B - 29 Aug 2007 15:30 GMT

> > However, it gives error(warning) like SQL2314W Some statistics are in
> > an inconsistent state. The newly collected
[quoted text clipped - 26 lines]
>
> Dave.

Thanks a ton, Dave.

That has indeed worked.

Rahul

Reply to this Message