Z/os DB2 Version 7 Index Problem

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Dear All,

FULLKEYCARD of index does not match CARD of table.

This is happening some times on partitioned table spaces after performing the following steps:

1- Running online reorg on some parts of a table space.

2- Running online reorg on all of indexes related to table( none partition and partition indexes).

3- Running Runstat utility after completion of the above steps.

Note : We get no error messages on the JCL outputs and DB2 log.

Anybody more experienced, please help.

Best regards
Manshadi

enrico-sorichetti · Posted: Tue Jan 19, 2010 2:45 pm

it' s odd to receive a <any IBM product message>(*) without a message ID/NUMBER

repost the full message text including the message ID DSN<something>

(*) what I mean is that all the software providers prefix the messages with something that makes easy to search for them in the manuals
for db2 the message prefix is DSN

enrico-sorichetti · Posted: Tue Jan 19, 2010 3:19 pm

search and read the manuals about CARDINALITY issues
and work with Your support in order to solve them

a quick and dirty google search with FULLKEYCARD of index does not match CARD of table gave many useful links on the issue
refining further with index table cardinality even more

sushanth bobby · Posted: Tue Jan 19, 2010 4:53 pm

Manshadi,

Second link in the top of the page. CLICK IT & search for this text "DB2/UDB V7 SQL Reference". & Click it. Now, go to Appendix D.

FULLKEYCARDF of SYSIBM.SYSINDEXES
Number of distinct values of the key. The value is -1 if statistics have not been gathered. This is an updatable column.

CARDF of SYSIBM.SYSTABLES table
Total number of rows in the table or total number of LOBs in an auxiliary table. The value is -1 if statistics have not been gathered or the row describes a view, alias, or created temporary table.

First sentence of above definations is your answer.

Sushanth

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

[B]Dear All,

After completion of Runstat job, the statistics is gathered and we expect FULLKEYCARD of index be equal to CARD of table but it is not.

This is happening some times on partitioned table spaces after performing the following steps:

1- Running online reorg on some parts of a table space.

2- Running online reorg on all of indexes related to table( none partition and partition indexes).

3- Running Runstat utility after completion of the above steps.

Note : We get no error messages on the JCL outputs and DB2 log.

Anybody more experienced, please help.

Best regards
Manshadi

enrico-sorichetti · Posted: Tue Jan 19, 2010 6:31 pm

since You keep refusing to post the info we asked, no reason to waste our time with You

does the message have a standard DB2 message identifier,
option 1) yes... read the manuals for the message explanation and the action to be taken to solve the issue

option 2) no... find who is the issuer of the message and behave accordingly

reposting the same question.. ( aka pestering ) will not be useful for getting help

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Dear enrico sorichetti

Thanks for your reply.

I have 12 years of experience on DB2 and I know how to search the google but the problem is that I don't get any error messages.

All the jobs are completed with Zero return codes and I check the value of FULLKEYCARD and the value of CARD in SYSTABLE and I see that does are deferent.

And I am looking for some one experienced this problem and his solution.

Thanks
Manshadi

enrico-sorichetti · Posted: Tue Jan 19, 2010 7:08 pm

Please stop the condescending attitude.
and... I am not Your Dear
the 12 years of experience do not make any difference about the manual reading capabilities

review the definition of FULLKEYCARDinality and CARDinality,
depending on the table and index definitions it might be normal for the two to differ

if You / Your colleagues have very strong evidence that they should match it' s about time to open a PMR with IBM support

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Mr Enrico,

Whereas we are using unique index and this problem occurs, as far as I know the values must match.

Anyway thanks for your help and suggestions.

sushanth bobby · Posted: Tue Jan 19, 2010 8:17 pm

Manshadi,

Can you execute an SQL query and tell us the difference,

To identify total number of rows,
SELECT COUNT(*)
FROM hlq.tablename;

To identify number of distinct rows,
SELECT COUNT(DISTINCT ID)
FROM hlq.tablename;

Sushanth

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Sushanth,

The count(*) on the table is not correct for example :

# Rows in the table :850000000
# The result of the COUNT(*) is 195000000 which it is same as FULLKEYCARD of the unique index.

Thanks
Manshadi

enrico-sorichetti · Posted: Tue Jan 19, 2010 9:41 pm

is the index partitioned also ??

dick scherrer · Posted: Tue Jan 19, 2010 9:42 pm

Hello,

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Mr.Enrico

We have 2 unique indexes one partition and one none partition, sometimes the problem is happened on partition index and sometimes on none partition index.

We run reorganize job and runstat in production system periodically every week and sometimes we reached this problem.

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Mr. Dick

Before running reorganize and runstat the number of records in the table is 850000000 and the count(*) result is correct.
The FULLKEYCARD OF THE INDEX and CARD of the table is also same.

But when we reached the problem they are not same.

enrico-sorichetti · Posted: Tue Jan 19, 2010 10:13 pm

I would say it is time to open a PMR with IBM

dick scherrer · Posted: Tue Jan 19, 2010 10:19 pm

Hello,

How long has this been happening? Was any maintenance applied just before this started happening? Was the process changed?

Has the volume of data changed?

What else might have changed (immediately before this began)?

As was mentioned before, it may be time to open an issue with IBM support. . .

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Mr.Dick,

We have this problem around 6 mount ago.

The process is not changed.
The volumes is not changed.

Anyway thanks for your suggestion.

Thanks
Manshadi

sushanth bobby · Posted: Wed Jan 20, 2010 12:16 pm

Manshadi,

Can you query and tell us the sum of CARDF for partition index and CARDF for non partition index. Table to be queried is SYSIBM.SYSINDEXPART.

This is an updatable column, so, i hope after running your RUNSTATS, no other job in schedule is updating these columns.

Sushanth

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Hi,
Sushanth

The CARDF for partition index and none partition index was deferent.

Partition index CARDF=850000000

None partition index CARDF =195000000

After we encountered this problem we run REBILD index and runstat and everything will be correct.

But as you know for big tables in online production environment rebuild index is not good idea, because during rebuild time the table is not available .

dbzTHEdinosauer · Posted: Wed Jan 20, 2010 2:16 pm

according to posts in the other site (url from my earlier post),
you should runstat first then reorg.

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Hi,
Dick Brenholtz

Thanks for your suggestion, but it is opposite of IBM recommendation.

Regards
Manshadi

sushanth bobby · Posted: Wed Jan 20, 2010 2:44 pm

Manshadi,

You can take STATISTICS while doing REORG itself, by doing this you can eliminate your step (3) process.

What version of DB2 you are using ?

Sushanth

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

Hi,
Sushanth

We are using DB2 Version 7.

Yes I can take statistics in Reorg jobs but I am not sure it will solve our problem.

Thanks
Manshadi

GuyC · Posted: Wed Jan 20, 2010 10:22 pm

If a unique index exists, select count(*) probably uses that index rather than the tablespace/parts

a way to force a TS-Scan is adding a where clause on a non-index column where all rows qualify for example datelupd < '3000-01-01'

But all this doesn't answer the question.
DB2 z/OS V7 is OOS more than a year go.

Do you ever check the table or always force it ?