Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Z/os DB2 Version 7 Index Problem
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Z/os DB2 Version 7 Index Problem
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Jan 19, 2010 2:45 pm    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Jan 19, 2010 3:19 pm    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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: 1013
Location: India

PostPosted: Tue Jan 19, 2010 4:53 pm    Post subject:
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    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Jan 19, 2010 6:31 pm    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Jan 19, 2010 7:08 pm    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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    Post subject:
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: 1013
Location: India

PostPosted: Tue Jan 19, 2010 8:17 pm    Post subject:
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    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Jan 19, 2010 9:41 pm    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
Reply with quote

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

Site Director


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

PostPosted: Tue Jan 19, 2010 9:42 pm    Post subject:
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    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Jan 19, 2010 10:13 pm    Post subject: Reply to: Z/os DB2 Version 7 Index Problem
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

Site Director


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

PostPosted: Tue Jan 19, 2010 10:19 pm    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Wed Jan 20, 2010 12:16 pm    Post subject:
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    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts COBOL Version 6.1 Virendra Shambharkar COBOL Programming 5 Tue Nov 01, 2016 11:24 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us