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

I'd go as far as to say that you should rethink your data model.

I can't, this is what old systems sometimes gives you....but I know...the data model is bad.

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.

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.

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

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.

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

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.

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....

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.