1

A have a db2 table with a column col1 varchar(2000). I want to reduce length from 2000 to 1600 byte. It is possible to do this with ALTER? Or in this case, the only valid solution is DROP - CREATE?

ALTER COLUMN col1 SET DATA TYPE VARCHAR(1600) -- doesn't work for reduce size, just for increase

Thanks for any idea!!!

neo
  • 33
  • 1
  • 8
  • Don't know about DB2 z/os rules, but with DB2 LUW this can end SQLCODE=-190 when at least one of this conditions is true : 1-the default value is longer than 1600, 2-at least one row has a value that is longer than 1600. – nfgl May 31 '21 at 14:02

3 Answers3

2

For Db2-for-Z/OS at v11 or V12 the rules for ALTER TABLE...ALTER COLUMN ...SET DATA TYPE ... include this rule:

"If the data type is a character or graphic string, the new length attribute must be at least as large as the existing length attribute of the column. "

You have not explained the motivation for reducing the column length, maybe double check this, and what assumptions you are making as they might not be true.

mao
  • 11,321
  • 2
  • 13
  • 29
0

This is what I would try:

  1. ALTER TABLE thetable ADD COLUMN newcol1 VARCHAR(1600);
  2. UPDATE thetable SET newcol1 = col1;
  3. ALTER TABLE thetable DROP COLUMN col1;
  4. ALTER TABLE RENAME COLUMN newcol1 TO col1;
  5. REORG the tablespace.
piet.t
  • 11,718
  • 21
  • 43
  • 52
0

ALTER TABLE ALTRE COLUMN SET DATA TYPE doesn't work in this case, since you are trying to reduce the size of the VARCHAR column.

piet.t's solution may work under certain circumstances, when you could DROP COLUMN from the table. There are many cases in which you can NOT drop a column from a table. It is good to check IBM Doc to find out the detail and check whether it works in your case or not -> https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-alter-table

Also, step 2 needs to consider the length of existing value, some existing values may exceed 1600 characters, you need to decide how you want to truncate the values.

On the other hand, you should check your application or database physical design to see if you really need to reduce the size, for example, you can normally tolerate that in the application code or from the SQL statements.

kendrick
  • 26
  • 2