Performance by DB2 with non-unique indexes

rakesh1155 · New User Joined: 21 Jan 2009 Posts: 84 Location: India

Hello People,

I had a query regarding use of indexes for DB2 table in COBOL.
Table1 has some 15 columns with one of the column as columnX [CHAR(48)] which is a non-unique index.

For a business requirement, we need to store values of 3 variables in the table. [VAR1 - X(20), VAR2 - X(3) , VAR3 - X(20)] (Note: This combo will be unique)

But we need to fetch one row in most cases using VAR1 & VAR2 only. Sometimes we need to fetch the row using all the 3 variable values.

Solution1:
Store the combination of (VAR1, VAR2 & VAR3) in the columnX.
In the cobol program, string (VAR1 & VAR2)
and query by passing this value and using the substring option SUBSTR(columnX,1,23)

Solution2: Store the combination of (VAR1 & VAR2) in the columnX and accomodate VAR3 in some other column.
In the cobol program, string (VAR1 & VAR2)
and query by passing exact value to the columnX.

My doubt here is:
1. As in solution1, Will there be any performance advantage of storing a "unique" combo (i.e. VAR1,VAR2,VAR3) in columnX instead of the exact search combo (i.e. VAR1 &VAR2) ?

2. Which one of the above solutions would give a better performance?
I am against the solution1 because i think the SUBSTR will degrade the performance more than the uniqueness of this field will improve. But this was suggested by one of my seniors so looking for more views.

I am hoping I have put across my query clearly. Please let me know if needed more info.

Rohit Umarjikar · Posted: Wed Mar 14, 2012 5:53 pm

Solution1 : you can go for redefine clause
If DBA is ready to have one more new coulmn added then better go for additional coulmn with index.

So your second solution would be better.

GuyC · Posted: Thu Mar 15, 2012 4:45 pm

why not store VAR1, VAR2 and VAR3 in 3 different columns (as it should be according to CODD) and create a index on (var1,var2,var3).

substr() will indeed make the index unusable

otherwise string var1 ,var2 and '%%%%%%%%%' into a hostvariable VarX
and use ColumnX like VarX

rakesh1155 · New User Joined: 21 Jan 2009 Posts: 84 Location: India

The reason behind not storing these 3 values into 3 different columns is because we are using an existing table and restructuring the table is not an option considering the cost and impact on existing modules.

dbzTHEdinosauer · Posted: Thu Mar 15, 2012 11:24 pm

lthese 3 var are new, did you not say.
why stuff them in one column?
sounds as if you need to modify existing modules anyway.

adding 3 columns is not a restucture, it is an ALTER.

or are these 3 var already (and have been in the 48 char column-x)

as quoted from the movie Basic

Akatsukami · Posted: Thu Mar 15, 2012 11:25 pm

rakesh1155 · New User Joined: 21 Jan 2009 Posts: 84 Location: India

dbzTHEdinosauer · Posted: Fri Mar 16, 2012 12:42 pm

you are missing the point.

you alter a table and add columns. you don't normally have 'free' columns in a row.

uniqueness in non-unique index,
apples and oranges. how many rows in table?
what kind of activity occurs with table?

a substr function is yet something else that must be performed by db2.
will basically disqualify use of index.

you have basically decided that you are going to 'stuff' 3 variables worth of data into one column,
and then use substr to access.

well, that's your decision, don't expect us to say that it is the best way.

rakesh1155 · New User Joined: 21 Jan 2009 Posts: 84 Location: India

Hello dbzTHEdinosauer,

I think I am not able to very clearly explain the scenario or the way this table has been used.

But, on continuing my search over the internet and discussing with people around and also, as you suggested, the use of substr function or use of functions will disqualify the use of index.
And that to a huge extent resolves my query.

I didn't have the authority to decide whether or not to stuff 3 variables into one column. But I thought and suggested to my seniors that it was not right to use substr()/functions if there are possibilities to avoid them so came to this forum to confirm it.

Thanking you for spending your precious time to look into post.

GuyC · Posted: Fri Mar 16, 2012 3:44 pm

rakesh1155 · New User Joined: 21 Jan 2009 Posts: 84 Location: India

Thanks GuyC.

I surely didn't miss that part in your reply.

Thanks everyone once again for helping me clarify my doubt and learning a few new things in DB2.