DB21034E - Alter Table problem

20 December,2008 by Jack Vamvas

Red Hat Linux DB2 8.2

Enterprise Server Edition

db2 'ALTER TABLE APPL_LOG ALTER COLUMN DETAIL SET DATA TYPE VARCHAR(30000)'

this returned a error message : DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0670N The row length of the table exceeded a limit of "4005" bytes. (Table space "USERSPACE1".) SQLSTATE=54010 The normal solution would be to expand the Tablespace. Given the allocations already on this server this would be problematic. Given that the data length for this column was potentially large , a CLOB was appropriate . It has got disadvantages related to the way it can be maniipulated in sql statements Initially I needed to view the table definition , this was done through "db2 list tablespaces show detail" There was no data in the table , therefore there was no requirement to export the data . The following statement recreated the table

CREATE TABLE "MyDB"."MY_TABLE" ( "LOG_DATE" TIMESTAMP , "SERVER_NAME" VARCHAR(16) , "SYSTEM" VARCHAR(16) , "APPLICATION_CODE" VARCHAR(16) , "SEVERITY" VARCHAR(16) , "LOGGER_NAME" VARCHAR(128) , "MESSAGE" VARCHAR(512) , "DETAIL" CLOB , "UNIQUE_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) ) IN "USERSPACE1" ;

 Ref:Jack Vamvas(http://www.dba-db2.com)

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB21034E - Alter Table problem

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright