Results 1 to 5 of 5

Thread: db2 tables

  1. #1
    Join Date
    Nov 2007
    Posts
    72

    Unanswered: db2 tables

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    Last edited by Marcus_A; 02-03-08 at 10:00.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2007
    Posts
    72
    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,

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Posts
    113
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •