View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
is the index partitioned also ?? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
# Rows in the table :850000000 |
How was this determined?
Why do you believe the count(*) is incorrect?
Could the count be using a view that has limited values returned?
How sure are you that the exact same table in the same subsystem was used to get both values? |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
I would say it is time to open a PMR with IBM |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. . . |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
Back to top |
|
|
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 . |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
according to posts in the other site (url from my earlier post),
you should runstat first then reorg. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 ? |
|
Back to top |
|
|
|