I am using db2look to get a bunch of related table statistics from a production databaes, and update a test database using the generated statements. Both the production database and test database don't have detailed index statistics.

During this process, only one update statement failed, which is to update SYSSTAT.TABLES for a specific table X. It failed with the message:
The catalog statistis "963" for column "CARD" is out of its target column,has an invalid format or is inconsistent in relation to some other statistics. (23521, -1227)

This is strange, becaues:
1) The update statements for other tables and their columns statistis succeeded. (i.e. the statements that update SYSSTAT.TABLES and SYSSTAT.COLUMNS)
2) The update statements for table X's column statistis (SYSSTAT.COLUMNS) succeeded. So I know that "CARD" value 963 of the table is greater than any column's COLCARD.

I suspect the relationship between "CARD" and "NPAGE", "FPAGE" might be off. Another posibility is that the some configuration of prod and test is different. However, i don't know how to confirm this. Could you please help?


I am using db2 8.

Out of service. But I would apply FP17 if you haven't already.

If by any chance your production database is partitioned and the test one is not, keep in mind that statistics are collected per partition.

If this is not the case, I would first try making sure that the statistics in production is fully up to date.

Thanks, the production database is fully up to date, and it is not partitioned (neither is the test database).

There should be a relationship between CARD/NPAGES/FPAGES, such as a page can contain # of records, so CARD records should at least occupy #NPAGES, etc? I am just guessing.

Anyone can help me on this?

Do you have a reason code for -1227 (sql1227n)?

SQL1227N The catalog statistic "<value>" for column
"<column>" is out of range for its target column, has
an invalid format, or is inconsistent in relation to
some other statistic. Reason Code = "<code>"


Can you execute the update statement from the command line to see if it gives you the RC.

I am discovering something insteresting here. The prod database's statistics is updated with runstats command without "WITH DISTRIBUTION ON COLUMNS" clause, this can be proved by running the blow command on the prod database:

select * from SYSSTAT.COLUMNS where TABNAME = '***' AND TABSCHEMA = '***'
(*** is a placehold for real values, don't want to disclose my company's information :-))

This sql returns empty.

The db2look however returns update statements to update SYSSTAT.COLUMNS with valid values.

So it seems, db2look executes runstats on the prod database but doesn't write them into the prod tables.

But this doens't make sense, after all, i want to simulate the prod database.

1) is there a way to tell db2look to not do more than it should?
2) is there a way to delete information from SYSSTAT.COLUMNS?

Thank you for your help.

select * from SYSSTAT.COLUMNS

should not return empty if table is there. Regardless how stats are collected.

But how can I explain the empty result? I fired the sql statement via CLP, it shows "0 record(s) selected."

If I select from SYSCAT.COLUMNS, the result is not empty.

Could you expand on this?

Thanks.

WOW buddy. you are confusing me. does select * from SYSCAT.COLUMNS gives you data or not. Show the command and its output.

select * from SYSCAT.COLUMNS where TABNAME = 'XXXX' AND TABSCHEMA = 'XXXX'

The result set is not empty.

select * from SYSSTAT.COLUMNS where TABNAME = 'XXXX' AND TABSCHEMA = 'XXXX'
The result set is empty, even after I have executed RUNSTATS ON TABLE "XXXX"."XXXX"
WITH DISTRIBUTION ON ALL COLUMNS
on this table, the result set is still empty!

But the db2look on this table gives update statements, such as:
UPDATE SYSSTAT.COLUMNS
SET COLCARD=45056,
NUMNULLS=0,
SUB_COUNT=-1,
SUB_DELIM_LENGTH=-1,
AVGCOLLENCHAR=-1,
HIGH2KEY='338960',
LOW2KEY='10987',
AVGCOLLEN=4
WHERE COLNAME = 'XXXX' AND TABNAME = 'XXXX' AND TABSCHEMA = 'XXXX';

So it seems that the db2look command first does a runstats command on the table but without writing the statistics to the related system tables and output them.

Have I made myself clear? Please continue to help me. Thank you very much.

db2look does not update statistics. I'd suggest you take a closer look at how you spell 'XXXX' and 'XXXX' in each of the statements.

As Nick said db2look does nothing to change stats. Check your name.

btw. I just realized that you were talking about two diff schemas SYSCAT and SYSSTAT. but that does not changes anything. SYSSTAT should have info on the table and its columns regardles if you have indexes or has collected statistics.

I run the sql "select * from SYSSTAT.COLUMNS" and grepped the result, there is no record for the XXXX table!

SYSSTAT should contain the records even for an empty table. I've proved that by creating an empty table and search for it in the SYSSTAT.COLUMNS.

Who can solve the mystery?

Are you sure that your XXXX is a table??? Please run

select type from syscat.tables where tabschema = 'XXX' and tabname = 'XXXX'

and show the output.