I am getting the above message while creating a table with200 columns with VARCHAR(256) in DB2 9.5 LUW. I have overcome this by changing the data type to LONG VARCHAR.
But the problem is the literature in fixpack 1 for 9.5 says that the datatype LONG VARCHAR is deprecated. What is the way out of this problem.
Please help folks.

Right, LONG VARCHAR because it is deprecated (for quite a while already). One alternative is to use CLOBs instead.

However, I would like to understand why you need 200 VARCHAR(256) columns. What kind of information is stored there? I am asking because it rather looks like a data model issue.

The replacement for LONG VARCHAR is CLOB. Both are stored in a separate area (not in the row on the data page with other columns) which is why it does not impact the 32677 page size limit (not counting page overhead). But performance will suffer since neither of these data types uses the bufferpool to cache pages, and all read and write I/O is synchronous to disk. Make sure you have file caching on for any tablespace with a LOB column (best to have a separate tablespace for LONG objects).

You would be better off performance-wise if you created two tables without LOB’s or LONG VARCHAR and linked them together, but you will need more bufferpool space for the 32K page sizes and real memory to support it (do not use virtual memory for bufferpools).

Thanks for revert. Well, i may not be able to answer your question as this db is getting migrated from Oracle. Model questions are beyond me as this is a product in use for while supported on Oracle. Recently there is a move to enhance this product for support on db2 as well.

Well, coming to CLOBs.. i am still getting the failure message.

I think you need to post the create table DDL and the exact error message. I would put the LONG data in a separate tablespace.

Well i could overcome the error by replacing CLOB with CLOB(256). I am not sure if this would call for any code changes in the application. Also the requirement is not to alter tables structure.
Can somebody point out any trivial issues associated with this. Like support of CLI Drivers and dynamic sql etc.

In DB2 you must specify the maximum length, even in a CLOB. That is probably what the error is saying. It will not affect the code.

You need to make sure that you specify that the data on the CLOB is logged, because I think LOB's are not logged by default (not sure). Please refer to the SQL Reference manual Vol 2 for details on create table.

CLOB and VARCHAR data types are treated differently when the database is accessed via JDBC. I suspect the same may be true with CLI access as well. Before you do any further changes try to test the application with the modified table.

One workaround would be to create a view that casts CLOB columns to VARCHAR "on the fly". Obviously, there will be some performance overhead.

I would myself indeed also go for the "split in two tables" solution.

Let's say you need
Instead, create the following three objects:
Reading applications will not see the difference. For writing applications you'll need INSTEAD OF triggers.

Alternatively (if that's an option), reduce the max. size of some of the columns such that the sum of the sizes is small enough. (Account for 1 additional byte per column without NOT NULL, and 2 additional bytes per VARCHAR column.)

I think it could be a problem if the result set is wider than fits on a 32K page. But that would depend on the actual length of the data stored in the VARCHAR's.