IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Z/os DB2 Version 7 Index Problem


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 2:34 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jan 19, 2010 2:45 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jan 19, 2010 3:19 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Jan 19, 2010 4:53 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 6:07 pm
Reply with quote

[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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jan 19, 2010 6:31 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 6:50 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jan 19, 2010 7:08 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 7:24 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Jan 19, 2010 8:17 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 9:28 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jan 19, 2010 9:41 pm
Reply with quote

is the index partitioned also ??
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Jan 19, 2010 9:42 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 9:54 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 10:00 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jan 19, 2010 10:13 pm
Reply with quote

I would say it is time to open a PMR with IBM
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Jan 19, 2010 10:19 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 10:40 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Jan 20, 2010 12:16 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Wed Jan 20, 2010 1:49 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 20, 2010 2:16 pm
Reply with quote

according to posts in the other site (url from my earlier post),
you should runstat first then reorg.
Back to top
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Wed Jan 20, 2010 2:30 pm
Reply with quote

Hi,
Dick Brenholtz

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

Regards
Manshadi
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Jan 20, 2010 2:44 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Wed Jan 20, 2010 3:46 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jan 20, 2010 10:22 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts Cobol file using index COBOL Programming 2
No new posts How to copy the -1 version of a membe... TSO/ISPF 4
No new posts z/vm installation problem All Other Mainframe Topics 0
Search our Forums:

Back to Top