Altering table Flavours

Yesterday doing alter column for an ecommerce client of mine I was asked to remove the generated always IDENTITY key from a table. Secondly asked to add a column for a name column where the added column  UCASE_NAME will always have alphabets translated to upper case. Third to alter an identity column as default by to generated always.

The structure of the table was :

CREATE TABLE “DB2INST3″.”EMPLOYEE_UPPER” (
“EMPNO” SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY ,
“FIRSTNME” VARCHAR(12) NOT NULL ,
“MIDINIT” CHAR(1) ,
“LASTNAME” VARCHAR(15) NOT NULL ,
“WORKDEPT” CHAR(3) ,
“PHONENO” CHAR(4) ,
“HIREDATE” DATE ,
“JOB” CHAR(8) ,
“EDLEVEL” SMALLINT NOT NULL ,
“SEX” CHAR(1) ,
“BIRTHDATE” DATE ,
“SALARY” DECIMAL(9,2) ,
“BONUS” DECIMAL(9,2) ,
“COMM” DECIMAL(9,2) )
IN “USERSPACE1” ;
To drop the identity column the command was easy

db2 “ALTER TABLE employee_upper alter column EMPNO drop IDENTITY”

I thought it would be easy and simple for the second request which was to add a column , always getting  generated as UPPER Name. The first alter gave me error as :

[db2 “alter table EMPLOYEE_UPPER  add column UCASE_NAME GENERATED ALWAYS AS (lower(FIRSTNME))”
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL20054N  The table “DB2INST3.EMPLOYEE_UPPER” is in an invalid state for the
operation. Reason code=”22″.  SQLSTATE=55019

After checking the table state it was in normal state. So while checking the error code  I was able to realize the table might get into check pending state so db2 was not running the alter table correctly.  I ran the command to set integrity off :

db2 “set integrity for  EMPLOYEE_UPPER off”
DB20000I  The SQL command completed successfully.

The alter table command worked smoothly after this

db2 “alter table EMPLOYEE_UPPER  add column UCASE_NAME GENERATED ALWAYS AS (lower(FIRSTNME))”
DB20000I  The SQL command completed successfully.

Since I had set integrity off I had to  bring the table out of check pending state.

db2 “set integrity for  EMPLOYEE_UPPER immediate checked force generated”
DB20000I  The SQL command completed successfully.

Now the third case to  drop identity statement for default  was the same which I used first time but to alter the column for changing default always again I had to start with the next sequential value in the alter statement  :

db2 “alter table EMPLOYEE_UPPER  alter column EMPNO set generated always as identity (start with 19)”

That worked like charm , thanks Rupen Shah and Dmitriy  Mechsheryakoy working with me for this.

Now ON IDUG SPAIN BARCELONA I had the pleasure of winning APPLE TV  gifted BY BMC Software . Thanks BMC software  for that  . I am going to say thanks to   Guy Przytula for teaching   advanced certification  db2 (614) so nicely .

TIP For DBA’s: Check your backup and restore policies every six month.