Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
General Query on DB2 table design

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

Active User


Joined: 22 Dec 2007
Posts: 108
Location: Bangalore

PostPosted: Thu Feb 14, 2019 12:49 pm    Post subject: General Query on DB2 table design
Reply with quote

Hi,

I need one small suggestion. It is related to designing a new DB2 table. In current assignment we need to create 2 tables. Now sometime back someone had told me that while designing a table we need to keep all frequently used columns in the beginning of the table and it is good for query performance.

Now in our current table we have one column (it's kind of IND_STATUS column) we are assuming that we gonna use frequently in our WHERE clause (that column will be updated also couple of times with certain values). Now in the original design they kept the column towards end of the table and so I proposed to move that column towards the beginning (just after our primary key column). I got below reply...

"I do not believe that physical column order makes any difference to a table, except for a primary key consisting of multiple fields."

So I just wanted to know if my understanding is wrong. Please share your valuable thought so I can either go with them or I should reply with correct info.

Thanks
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


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

PostPosted: Thu Feb 14, 2019 1:17 pm    Post subject:
Reply with quote

We always add new columns at the end of the existing Table and as long as it has a index on it (IND_STATUS), you are good to go. Once you are done with the creation just run your sql query in BMC or Run a EXplain to confirm the required index is used and no table scan is performed.

What you heared might probably for clustering indexes to have the columns together otherwise I don’t see why that would impact the performance.
Back to top
View user's profile Send private message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 108
Location: Bangalore

PostPosted: Thu Feb 14, 2019 1:44 pm    Post subject:
Reply with quote

Hi Rohit,

Thanks! for the reply. It's a new table and I am thinking to have Index on this IND_STATUS column. So I guess I can leave the column where it is now.

Thanks
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Thu Feb 14, 2019 10:02 pm    Post subject:
Reply with quote

Yes, that should not harm you at all when comes to performance. Once it’s done make sure the query picks up the index that’s all.
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 DB2 Query runs very long John F Dutcher DB2 21 Wed Aug 21, 2019 11:44 pm
No new posts Query to Truncate value in table bhaskar_kanteti DB2 2 Mon Aug 12, 2019 3:26 pm
No new posts RACF RANGE TABLE Martin Wickenden All Other Mainframe Topics 1 Mon Jul 08, 2019 9:06 pm
No new posts Verify WITH UR present inside program... priyankakir CLIST & REXX 23 Wed Jun 12, 2019 3:37 pm
No new posts DB2 table quality rizwanaccy DB2 6 Mon Apr 01, 2019 1:28 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us