Adding varchar of 32k

[prk1]

I have a need to add a varchar column of 32k size in addition to an existing varchar column of similar size in the same table.
I want to know, how would it impact the overall performance of the application accessing the table for select/update/insert?

Additional Info:
I am currently using DB2 UDB v8
Columns are used to store XML data
I do not want to use CLOB/BLOB, since I cannot afford a performance impact

Thanks,
Prk1

[Parnamwulan]

For select statements it will have quite normal performance as have selecting from standard tables

For insert - it depends on the method and task - if there will be the possibility to count the column, it will be the best
If not, then it depends on what you need to store in the table - if you would like to add a column to the table with existing data, do not use the import/insert update command because it will take serious time .... better will be to use load command an load it to pre-made table and set up trigger fixed to a fictive import (you can load huge table and data in short time and setup the trigger after the import to another table) which will count the column for you
Or, you can export the two tables with export to ..... select and joined statement and then load to newly created table

update - as insert in fact .)

[cburnett]

prk1,
Ah. A common problem and one I've been benchmarking for a customer at the moment. First up you cannot have a row span a page and biggest supported pagesize is 32K. Therefore you cannot have two VARCHAR(32K) columns in the same table.

Options are:

• LONG VARCHAR - these are stored outside the table row and these you can have more than one in a table but watch the restrictions
• CLOB - Also stored outside the table.
• XML - (with V9). Also kept outside the table.

Of these LONG VARCHAR and CLOB have similar performance impacts (50% elapsed and CPU) with XML having the most impact (100% elasped, 12 times more CPU).

Naturally these results are very dependent on how you use the data, but the relative impacts may be of interest.