Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Question Unanswered: How to add 5000 columns in db2?

    Hi,
    I must handle 5000 columns in db2. But I have found out that depending on the size of columns, it only can handle about 500 columns in one table. So have should I do? There is only one primary key and the rest is vargraphic columns (as numbers) ...

    * Have 10 different tables and join them together?

    * Have 1 table but add another key and make join within the table?

    * Combine the above options?

    * Or something different you will propose...

    Thanks,
    Marko
    Last edited by Marko_Swe; 02-05-09 at 03:38.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'd go as far as to say that you should rethink your data model.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    I can't, this is what old systems sometimes gives you....but I know...the data model is bad.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I am not sure if views would be a work-around. If so, you could split up the data into several tables (and use a reasonable data model), then define views on top of the base tables to provide the 5000-column view on the data that the application expects.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    5000-column view on the data that the application expects.
    This may not work in all cases. For example, you won't be able to select all 5K columns from the view, due to the select list limitation. If a query against the view results in a temporary table being created, that table will also be subject to the limit for the number of columns, I think.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marko_Swe
    Hi,
    I must handle 5000 columns in db2. But I have found out that depending on the size of columns, it only can handle about 500 columns in one table. So have should I do? There is only one primary key and the rest is vargraphic columns (as numbers) ...

    * Have 10 different tables and join them together?

    * Have 1 table but add another key and make join within the table?

    * Combine the above options?

    * Or something different you will propose...

    Thanks,
    Marko
    You can create/use a partitioned table. But again it depends on your applications requirements.

    Sorry, i take it back. It will not work in this case... I agree with reconsidering existing db model
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I can't, this is what old systems sometimes gives you....but I know...the data model is bad.


    If the above is true then what is the need for a new table that contains 5000 columns? I would rethink your data model as was suggested. Will all 5000 of these columns always have data? that could be your deciding factor on separating into other tables. Also, can you stack these columns vertically, instead of horizontally? Meaning you have your key and a type column to determine which of your thousands of vargrphic columns this one is.

  8. #8
    Join Date
    Feb 2009
    Posts
    4

    Post

    It's to tricky to separate the data into more specific tables and so on...so the situation is still to handle 5000 columns. The columns will not always have data, exactly how much is difficult to estimate but maybe most data is between 500-3000 columns of data. I can only stack the columns horizontally cause further sql require it...

    Thanks all of you for your reflections and plz post more!

    Well, one more thing...Java is the backend language here...if it gives you any idea

    Marko

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Wide tables (lots of columns) are surely not rare in relational databases. However, more than 200 or 500 columns typically never used. So if you tell me that it would not be possible to use a better schema for the data that has less than 500 columns, I begin to doubt it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2009
    Posts
    4
    Of course it's possible to use a better schema, but as often, there is no time or money to do it. This is the area I have to work within....

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Ok, then you can choose between not doing it because it is possible technically or not doing it because you don't have the necessary resources.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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