473,385 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

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

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
2 4834
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
3
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it...
11
by: Al Murry | last post by:
A Java Batch job running on AIX 4.3.3 talking to another RS6000 running DB2 6.1 was taking 15 min last week. This week it is taking over 6 hours! The job uses 4 tables that have over 5,000,000...
12
by: Philip Sherman | last post by:
I'm trying to copy production statistics to a test database and can't set the column statistics for a VARGRAPHIC column to match what RUNSTATS generated on production. The reason code and some...
2
by: mike.usenet | last post by:
Hi, I'm new to DB2, but am relatively familiar with databases, and I've run into something I don't fully understand. I created a table tab4, then, after the fact, altered the table so that one...
3
by: Rajesh Kumar Mallah | last post by:
Hi, Looks like alter table does not tells about the indexes it dropped PG version: 7.4.3 Regds mallah.
7
by: am72de | last post by:
Hi all, I've posted this problem some weeks ago, but noone had a solution. Perhaps now someone could help me. I have the following tables: Create Table T1 ( ID1 int Not Null , ID2 int Not...
0
by: Rahul B | last post by:
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" ...
5
by: Rahul Babbar | last post by:
Hi, I am facing an issue here. I had to drop a number of columns (around 20) from a table, which I tried to do all at the same time using the following command. Alter table table_name drop...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.