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
 

 

Performance by DB2 with non-unique indexes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Wed Mar 14, 2012 12:58 pm    Post subject: Performance by DB2 with non-unique indexes
Reply with quote

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.
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Mar 14, 2012 5:53 pm    Post subject:
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Mar 15, 2012 4:45 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Thu Mar 15, 2012 11:10 pm    Post subject: Reply to: Performance by DB2 with non-unique indexes
Reply with quote

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

Global Moderator


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

PostPosted: Thu Mar 15, 2012 11:24 pm    Post subject:
Reply with quote

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
Quote:
get your story straight!
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Mar 15, 2012 11:25 pm    Post subject: Re: Reply to: Performance by DB2 with non-unique indexes
Reply with quote

rakesh1155 wrote:
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.

Dear me, don't tell me some incompetent moron coded FOR SELECT * in a cursor!
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Fri Mar 16, 2012 10:11 am    Post subject: Reply to: Performance by DB2 with non-unique indexes
Reply with quote

Quote:
Dear me, don't tell me some incompetent moron coded FOR SELECT * in a cursor
Hello Akatsukami, I cant be sure of that! And there are hundreds of modules using this table, so cannot be sure if someone has coded a SELECT *.

Hello dbzTHEdinosauer,

Wrong choice of word 'restructure' instead of 'alter'. Apologies.
I will surely need to modify some existing modules, but it will only be a few which are processing these corresponding set of rows which will have this combo.

We are stuffing them into one column because we need to use this table which doesnt have enough free columns for this corresponding set of data.

The value of these 3 variables is currently not stored in columnX.


I am still waiting for an answer to my original query.
i.e. Does uniqueness in a non-unique indexed column help with performance? If yes, how does it help?

And in such a scenario, looking for comments on performance of DB2 on using SUBSTR().
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Mar 16, 2012 12:42 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Fri Mar 16, 2012 3:20 pm    Post subject: Reply to: Performance by DB2 with non-unique indexes
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 16, 2012 3:44 pm    Post subject:
Reply with quote

GuyC wrote:
string var1 ,var2 and '%%%%%%%%%' into a hostvariable VarX
and use ColumnX like VarX


it has the same result as substr(), but is indexable
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Fri Mar 16, 2012 4:18 pm    Post subject: Reply to: Performance by DB2 with non-unique indexes
Reply with quote

Thanks GuyC.

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


Thanks everyone once again for helping me clarify my doubt and learning a few new things in DB2.
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Unique sort situation.... dbecker SYNCSORT 6 Wed Apr 27, 2016 8:53 pm
No new posts Unique Record Count anandhan87 DFSORT/ICETOOL 1 Tue Feb 09, 2016 4:06 pm
No new posts DISP=(SHR,PASS) performance opinion steve-myers JCL & VSAM 1 Wed Dec 02, 2015 11:53 pm


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