Hi All !!

I am working in datawarehousing environment ..where we lot of fact tables base tables and dimension tables ...
Q1. which the best way to optimize the table i.e should there be any limit on number columns per table ?
Q2.Is it something like integer column must be ahead of varchar column ?
Q3.Should be the length of varchar columns should be same across the database or in a table say if i have 2 varchar columns they should have same length or they should be placed adjacent to each other ?
Q4.Any best practices specifically for fact, dimension tables ?

If somebody could forward me any document or a URL that would be really nice .
There are few DB2 Champs here i would request them to answer my questions ! !

Thanks & Regards

Q2. Unless you are updating the rows, it makes no difference in the order of the columns (varchar vs integer). In DB2 for z/OS, you can decrease the amount of logging if you place varchar columns at the end of the row, but only in situations where you update the columns after an insert. This does not apply to DB2 for Linux, UNIX, Windows since it autmatically places varchar columns at the end of the row regardless of the order of the column in your create table.

Q3. It makes no difference.

Thanks Marcus for your reply !! but i havent got all the answer till yet ! !
Q1.how many columns should be there in base tables dimension table,summary tables ? for optimization purposes .how should be the keys and constraints should be placed ?

Q2.number of columns per table ?

Q3. please send me all best practices for db2 and datastage ? we don't have enough exposure to documents and if you champions dont reply where else should we ask from ...i posted the same question there on ibm website forum nobody bothered to reply ! this forum seems to be active ..request you kindly guide me ...

Thanks & Regards,

There is no way to answer that without knowing your business and your application. The size of the fact table depends on how many facts you have.

Sorry, but there are no easy answers or best practices that will tell you the answers you want. If there were easy answers, then most of the databases in the world would be well-designed, which is not the case in my experience.

Q2.number of columns per table ?
750 or fewer (including hidden columns), 749 if the table is a dependent


Agree with Marcus_A. Those DBA or Database consultants receive pretty high and stable pay for a good reason.