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

Rahul B

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

Aug 29 '07 #1

Dave Hughes

On Wed, 29 Aug 2007 12:17:57 +0000, Rahul B scribbled:

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.

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/infoce...2luw/v9/topic/
com.ibm.db2.udb.admin.doc/doc/r0001980.htm
Cheers,

Dave.

Aug 29 '07 #2

Rahul B

On Aug 29, 6:37 pm, Dave Hughes <d...@waveform.plus.comwrote:

On Wed, 29 Aug 2007 12:17:57 +0000, Rahul B scribbled:

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.

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.

Thanks a ton, Dave.

That has indeed worked.

Rahul

Aug 29 '07 #3