An Example of Changing the Data Type of a DB2 Column

I don’t often make changes to existing tables. The WebSphere Commerce database comes with defintions in place. We make customizations that are largely adding columns or adding tables or adding indexes. It is pretty rare that we have to alter one of the handful of custom tables in some way.

What this means is that I tend to learn a LOT whenever I have to do it. And if you’re a regular reader, you know that one of my inspirations for blog topics is things I learn that I think others could benefit from.

Problem Statement

In this case, WebSphere Commerce requires that primary keys of any custom tables that you would like to add to stagingprop consist of no more than three INTEGER(also SMALLINT or BIGINT) columns and two VARCHAR (or CHAR) columns of 254 characters or less. Many times when developers request a new table, they don’t yet know if a table will be a part of stagingprop.

In this particular case, one of the three columns that make up the primary key was defined with the DOUBLE data type, which is CAST-able to INTEGER, but to avoid future potential problems, I wanted to change it to a true INTEGER data type. I have an active imagination, and could imagine a couple of scenarios where having it as a DOUBLE could lead to stagingprop problems down the road. The developers assured me that the data is all compatible with INTEGER anyway.

There are two main solutions that came to mind:

1. Alter the table to change the data type of the column
2. Use ADMIN_MOVE_TABLE to change the data type of the column

I tested both solutions on a dummy copy of the table. It was a small table at less than 1,000 rows, and I wanted to be able to experience and decide based on having done both on a test table.

The table structure looked like this:

The three columns in red above are the primary key for the table, and the first two columns are also children in foreign key relationships.

Solution #1 – Alter the Table to Change the Data Type of the Column

Description

The steps for #1 are essentially:

1. Ensure users are not accessing the table
2. Drop primary key
3. Alter data type
4. Reorg table
5. Add primary key
6. Do runstats on the table

Considerations

Be aware of having no primary key on the table – if un-controlled inserts are done on the table while the primary key is not there, you could get problematic data in the table.

The type of reorg needed is a classic or offline reorg. An online/inplace reorg will not work.

Detailed Walkthrough

$ db2 "alter table DBATEST.XCONFIGSHPCHARGE drop PRIMARY KEY"
DB20000I  The SQL command completed successfully.

$ db2 "alter table DBATEST.XCONFIGSHPCHARGE alter TAMANIO set data type INTEGER"
DB20000I  The SQL command completed successfully.

$ db2 "select substr(TABSCHEMA,1,8) as tabschema, substr(TABNAME,1,18) as tabname, AVAILABLE, REORG_PENDING, NUM_REORG_REC_ALTERS from sysibmadm.admintabinfo where tabschema='DBATEST' and tabname='XCONFIGSHPCHARGE' with ur"

TABSCHEMA TABNAME            AVAILABLE REORG_PENDING NUM_REORG_REC_ALTERS 
--------- ------------------ --------- ------------- -------------------- 
DBATEST   XCONFIGSHPCHARGE   Y         Y                                1 

  1 record(s) selected.
$ db2 "reorg table DBATEST.XCONFIGSHPCHARGE"
DB20000I  The REORG command completed successfully.

$ db2 "select substr(TABSCHEMA,1,8) as tabschema, substr(TABNAME,1,18) as tabname, AVAILABLE, REORG_PENDING, NUM_REORG_REC_ALTERS from sysibmadm.admintabinfo where tabschema='DBATEST' and tabname='XCONFIGSHPCHARGE' with ur"

TABSCHEMA TABNAME            AVAILABLE REORG_PENDING NUM_REORG_REC_ALTERS 
--------- ------------------ --------- ------------- -------------------- 
DBATEST   XCONFIGSHPCHARGE   Y         N                                0 

  1 record(s) selected.

$ db2 "ALTER TABLE DBATEST.XCONFIGSHPCHARGE ADD CONSTRAINT PK_XCONFIGSHPCHAR PRIMARY KEY (SHIPMODE_ID, JURSTGROUP_ID, TAMANIO)"
DB20000I  The SQL command completed successfully.

$ db2 runstats on table DBATEST.XCONFIGSHPCHARGE with distribution and detailed indexes all
DB20000I  The RUNSTATS command completed successfully.

Table after the change:

Solution #2

Description

The steps for #2 are essentially:

1. Ensure users are not accessing the table
2. Drop foreign keys
3. Use ADMIN_MOVE_TABLE to change the column data type
4. Add foreign keys
5. Do runstats on the table

I think that it is possible with the foreign key removal/addition that you might end up needing to set integrity on the table – I did not have to in my situation.

Considerations

Be aware of having no foreign keys on the table – if un-controlled inserts are done on the table while the foreign keys are not there, you could get problematic data in the table.

Since there must be no foreign keys on the table to run this option, it becomes a choice only if the table in question has few or relativley uncomplicated RI relationships to other tables.

Assuming you do not have to SET INTEGRITY, the outage with this method may be less than with the other method.

In my case, I’m using methods of keeping users from accessing the table simply of telling my developers to stay out.

Detailed Walkthrough

$ db2 "alter table dbatest.XCONFIGSHPCHARGE drop FOREIGN KEY FK_CFGSHPC_JRSGRP drop FOREIGN KEY FK_CFGSHPC_SHPMDE"
DB20000I  The SQL command completed successfully.

$ db2 "call ADMIN_MOVE_TABLE( 
'DBATEST',
'XCONFIGSHPCHARGE',
'','','','','','',
'SHIPMODE_ID INTEGER NOT NULL, 
    JURSTGROUP_ID INTEGER NOT NULL, 
    MONTO DECIMAL(20,5), 
    TAMANIO INTEGER NOT NULL, 
    DESCRIPTION VARCHAR(50), 
    DIASADICIONALES INTEGER, 
    FIELD1 INTEGER, 
    FIELD2 VARCHAR(254), 
    FIELD3 DOUBLE, 
    OPTCOUNTER SMALLINT',
'',
'MOVE')"
Result set 1
  --------------
  KEY                              VALUE
  -------------------------------- ----------------------------------------------------
  AUTHID                           DB2INST1
  CLEANUP_END                      2013-03-09-18.00.39.229239
  CLEANUP_START                    2013-03-09-18.00.39.178340
  COPY_END                         2013-03-09-18.00.38.982713
  COPY_OPTS                        OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
  COPY_START                       2013-03-09-18.00.38.856133
  COPY_TOTAL_ROWS                  825
  INDEXNAME                        PK_XCONFIGSHPCHAR
  INDEXSCHEMA                      DBATEST
  INDEX_CREATION_TOTAL_TIME        0
  INIT_END                         2013-03-09-18.00.38.828100
  INIT_START                       2013-03-09-18.00.38.638682
  PAR_COLDEF                       SHIPMODE_ID INTEGER NOT NULL, 
                            JURSTGROUP_ID INTEGER NOT NULL,     
                            MONTO DECIMAL(20,5), 
                            TAMANIO INTEGER NOT NULL, DESCRIPTION VARCHAR
  REPLAY_END                       2013-03-09-18.00.39.109882
  REPLAY_START                     2013-03-09-18.00.38.983153
  REPLAY_TOTAL_ROWS                0
  REPLAY_TOTAL_TIME                0
  STATUS                           COMPLETE
  SWAP_END                         2013-03-09-18.00.39.170664
  SWAP_RETRIES                     0
  SWAP_START                       2013-03-09-18.00.39.115338
  VERSION                          09.07.0006

  22 record(s) selected.

  Return Status = 0

$ db2 "ALTER TABLE DBATEST.XCONFIGSHPCHARGE ADD CONSTRAINT FK_CFGSHPC_JRSGRP FOREIGN KEY (JURSTGROUP_ID) REFERENCES WSCOMUSR.JURSTGROUP (JURSTGROUP_ID) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION“
DB20000I  The SQL command completed successfully.

$ db2 "ALTER TABLE DBATEST.XCONFIGSHPCHARGE ADD CONSTRAINT FK_CFGSHPC_SHPMDE FOREIGN KEY (SHIPMODE_ID) REFERENCES WSCOMUSR.SHIPMODE (SHIPMODE_ID) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION“
DB20000I  The SQL command completed successfully.

$ db2 runstats on table DBATEST.XCONFIGSHPCHARGE with distribution and detailed indexes all
DB20000I  The RUNSTATS command completed successfully.

Table after the change:

Summary

So there you have it – two different methods of changing the data type for a column, both with the same end result, but with some different considerations as to which might be appropriate.