ALTER COLUMN

Toad for IBM DB2
1

Hi,

I’m on DB2 LUW V9.7 and Toad for DB2 v5.2.

I’m trying to increase the column size of a table from char(18) and char(30). As per DB2 manuals, I should be able to do it by just doing ALTER TABLE ALTER COLUMN statement (http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fc0023297.htm). However, when I use the alter table wizard in Toad, it shows me the script that exports data, drops & recreates the table and imports the data back into the table. Is there a way to force it to do an ALTER and reorg (if needed)?

Thanks,

Bob

2

Hi Bob,

Please post the DDL for the table. Toad should be using an alter if it’s allowed by DB2.

Adam

3

Hi Adam,

Here is the DDL:

CREATE TABLE DCS.AP_ADM_ASC (

AP_ADM_ASC_I INTEGER NOT NULL,

ADM_TB_NM CHARACTER(30) NOT NULL,

ADM_COL_NM CHARACTER(18),

CTL_LVL_TB_NM CHARACTER(30),

CTL_LVL_TB_I INTEGER,

CDN_TYP CHARACTER(10) NOT NULL,

AP_VAL_I INTEGER,

AP_VAL_CH CHARACTER(30),

EFF_S TIMESTAMP NOT NULL,

END_S TIMESTAMP NOT NULL,

REC_STT CHARACTER(1) NOT NULL DEFAULT ‘A’,

CRT_S TIMESTAMP NOT NULL,

CRT_UID CHARACTER(8) NOT NULL,

CRT_PGM_C CHARACTER(30) NOT NULL,

LST_UPD_S TIMESTAMP NOT NULL,

LST_UPD_UID CHARACTER(8) NOT NULL,

LST_UPD_PGM_C CHARACTER(30) NOT NULL,

)

IN tablespace;

The column that I’m trying to alter is ADM_COL_NM.

Thanks,

Bob

4

Hi Bob,

I tried altering ADM_COL_NM to 30 in Toad for DB2 5.2 and I got an ALTER COLUMN statement. The DDL you sent is invalid without removing the comma after the last column. Is there more to the create statement that you left out?

Adam

5

Sorry, the last comma should not be there, it was a typo.

I don’t know if this helps, but the version I’m on is 5.2.0.884 - Are you on the same version/build?

I tried to do the same on a different table on a different database and it doesn’t work there either.

6

I’m also using 5.2.0.884. Can you post the script that Toad created for the change?

7

I created a new table similar to the one above, with a different schema and tried to alter it. Here is the script that Toad generated:

– Step 1. Export of data for preserve

EXPORT TO ‘%DATA_PATH%\THIMMA_AP_ADM_ASC_1048690281.ixf’

OF IXF

SELECT AP_ADM_ASC_I, ADM_TB_NM, ADM_COL_NM, CTL_LVL_TB_NM, CTL_LVL_TB_I, CDN_TYP, AP_VAL_I, AP_VAL_CH, EFF_S, END_S, REC_STT, CRT_S, CRT_UID, CRT_PGM_C, LST_UPD_S, LST_UPD_UID, LST_UPD_PGM_C FROM “THIMMA”.“AP_ADM_ASC”;

– Step 2. Drop

DROP TABLE THIMMA.AP_ADM_ASC;

COMMIT;

– Step 3. Sync

SET CURRENT SCHEMA = DCS;

CREATE TABLE THIMMA.AP_ADM_ASC (

AP_ADM_ASC_I INTEGER NOT NULL,

ADM_TB_NM CHARACTER(30) NOT NULL,

ADM_COL_NM CHARACTER(30),

CTL_LVL_TB_NM CHARACTER(30),

CTL_LVL_TB_I INTEGER,

CDN_TYP CHARACTER(10) NOT NULL,

AP_VAL_I INTEGER,

AP_VAL_CH CHARACTER(30),

EFF_S TIMESTAMP NOT NULL,

END_S TIMESTAMP NOT NULL,

REC_STT CHARACTER(1) NOT NULL DEFAULT ‘A’,

CRT_S TIMESTAMP NOT NULL,

CRT_UID CHARACTER(8) NOT NULL,

CRT_PGM_C CHARACTER(30) NOT NULL,

LST_UPD_S TIMESTAMP NOT NULL,

LST_UPD_UID CHARACTER(8) NOT NULL,

LST_UPD_PGM_C CHARACTER(30) NOT NULL

)

IN THIMMA;

ALTER TABLE THIMMA.AP_ADM_ASC

DATA CAPTURE NONE

PCTFREE 0

LOCKSIZE ROW

APPEND OFF

NOT VOLATILE;

CREATE UNIQUE INDEX THIMMA.IDCS011901

ON THIMMA.AP_ADM_ASC

( AP_ADM_ASC_I ASC )

CLUSTER

ALLOW REVERSE SCANS

COMPRESS NO;

CREATE INDEX THIMMA.IDCS011902

ON THIMMA.AP_ADM_ASC

( ADM_TB_NM ASC, CDN_TYP ASC, ADM_COL_NM ASC, CTL_LVL_TB_NM ASC, CTL_LVL_TB_I ASC )

ALLOW REVERSE SCANS

COMPRESS NO;

– Step 4. Import of data for preserve

LOAD CLIENT FROM ‘%DATA_PATH%\THIMMA_AP_ADM_ASC_1048690281.ixf’

OF IXF

METHOD N

(AP_ADM_ASC_I,

ADM_TB_NM,

ADM_COL_NM,

CTL_LVL_TB_NM,

CTL_LVL_TB_I,

CDN_TYP,

AP_VAL_I,

AP_VAL_CH,

EFF_S,

END_S,

REC_STT,

CRT_S,

CRT_UID,

CRT_PGM_C,

LST_UPD_S,

LST_UPD_UID,

LST_UPD_PGM_C)

INSERT INTO THIMMA.AP_ADM_ASC

(AP_ADM_ASC_I,

ADM_TB_NM,

ADM_COL_NM,

CTL_LVL_TB_NM,

CTL_LVL_TB_I,

CDN_TYP,

AP_VAL_I,

AP_VAL_CH,

EFF_S,

END_S,

REC_STT,

CRT_S,

CRT_UID,

CRT_PGM_C,

LST_UPD_S,

LST_UPD_UID,

LST_UPD_PGM_C)

INDEXING MODE REBUILD

ALLOW NO ACCESS;

– Step 5. Restoring constraints and indexes

SET CURRENT SCHEMA = DCS;

SET CURRENT PATH = “SYSIBM”,“SYSFUN”,“SYSPROC”,“SYSIBMADM”,“PIDDBA1”;

ALTER TABLE THIMMA.AP_ADM_ASC

ADD CONSTRAINT REC_STT CHECK

(REC_STT IN (‘A’, ‘I’))

ENFORCED

ENABLE QUERY OPTIMIZATION;

COMMIT;

– Step 6. Runstats

RUNSTATS ON TABLE THIMMA.AP_ADM_ASC

ALLOW WRITE ACCESS;

COMMIT;

8

I still can’t recreate the error. I must not have the all the properties of your object.

Please try recreating the error using the table from the DDL below.

CREATE TABLE XXX(

AP_ADM_ASC_I INTEGER NOT NULL,

ADM_TB_NM CHARACTER(30) NOT NULL,

ADM_COL_NM CHARACTER(18),

CTL_LVL_TB_NM CHARACTER(30),

CTL_LVL_TB_I INTEGER,

CDN_TYP CHARACTER(10) NOT NULL,

AP_VAL_I INTEGER,

AP_VAL_CH CHARACTER(30),

EFF_S TIMESTAMP NOT NULL,

END_S TIMESTAMP NOT NULL,

REC_STT CHARACTER(1) NOT NULL DEFAULT ‘A’,

CRT_S TIMESTAMP NOT NULL,

CRT_UID CHARACTER(8) NOT NULL,

CRT_PGM_C CHARACTER(30) NOT NULL,

LST_UPD_S TIMESTAMP NOT NULL,

LST_UPD_UID CHARACTER(8) NOT NULL,

LST_UPD_PGM_C CHARACTER(30) NOT NULL

)

IN ???

9

Okay, with just a table without indexes and constraints, like the one you mentioned, it worked. It generated this script.

ALTER TABLE THIMMA.AP_ADM_ASC

ALTER COLUMN ADM_COL_NM SET DATA TYPE CHARACTER(30);

REORG TABLE THIMMA.AP_ADM_ASC;

COMMIT;

It also worked with an unique index and a non-unique index in the mix.

However, when I added a constraint (with no indexes) and tried, it generated a script similar to the previous one (drop & recreate).

The problem seems to be with tables having constraints.

10

Please post your constraint.

11

Here is the DDL for the constraint:

ALTER TABLE THIMMA.AP_ADM_ASC

ADD CONSTRAINT REC_STT CHECK

(REC_STT IN (‘A’, ‘I’))

ENFORCED

ENABLE QUERY OPTIMIZATION;

12

Hi Bob,

This is a known issue that was resolved in the 5.5 release. Please give it a try.

Regards,

Adam

13

Okay, Thanks for your help Adam. I’ll try to upgrade to 5.5.