MVSFORUMS.com

NASCAR9 · Posted: Tue Apr 19, 2005 10:37 am Post subject: Creating Index Columns

Is the Index on SSNO redundant?

kolusu · Posted: Tue Apr 19, 2005 12:49 pm Post subject:

nascar9,

I don't think the index will reduntant. But change the definitions with ASC clause and type2 as shown below.

NASCAR9 · Posted: Tue Apr 19, 2005 2:04 pm Post subject:

Koluso,
I'm not familiar with 'TYPE 2' so I checked the SQL Reference and it said,
"TYPE 2 specifies a type 2 index. The TYPE 2 clause is not required. A type 2 index is always created."

Also, you coded 'UNIQUE' on both indexes, index XPRMLT4 is a secondary index on PREMLETTER, and XPRMLT1 is the UNIQUE index. Am I missing something?

Thanks,
NASCAR9

kolusu · Posted: Tue Apr 19, 2005 2:36 pm Post subject:

NASCAR9 · Posted: Tue Apr 19, 2005 2:54 pm Post subject:

Kolusu, The combination of SSNO, MONTH_CNT, and MAILDATE make the key. A SSNO can be in this table more than once with MONTH_CNT or MAILDATE set to a different value.
SSNO is indexed for faster retrieval. That was the reason for my post. I

bauer · Posted: Wed Apr 20, 2005 6:32 am Post subject:

Hi all,

index HOURS.XPRMLT4 doen't make sense for me.

If only SSNO is available in the where clause of your select DB2 uses index HOURS.XPRMLT1 with matchcols = 1. So HOURS.XPRMLT4 is not necessary.

BUT: The situation depends on the statistic data, cardinality, number of Rows matching SSNO only vs. number of Rows matching SSNO and the other fields.

So is can make sense for Db2 to uses index HOURS.XPRMLT4.

Check the getpagerate, statistic data, use explain command !

regards,
bauer

NASCAR9 · Posted: Wed Apr 20, 2005 6:42 pm Post subject:

Thanks bauer! I think what yourself and Kolusu have explained has helped. I'm not a DBA but seem to have many DBA responsibilities. We