DB2:How to alter not logged column

How to alter not logged column

You can't alter a table column from not logged to logged using the ALTER TABLE statement. You'll have to use the stored procedure SYSPROC.ALTOBJ

You can check if a table has a not logged column using db2look. 


Example: 

database = ERIC 
table = EXAMPLE 

$ db2look -d eric -e -t example 

------------------------------------------------ 
-- DDL Statements for table "DB2INST1"."EXAMPLE" 
------------------------------------------------ 

CREATE TABLE "DB2INST1"."EXAMPLE" ( 
"KEYID" INTEGER , 
"DATA" BLOB(2147483647) NOT LOGGED NOT COMPACT ) 
IN "USERSPACE1" ; 

You can't alter the column to set LOGGED using the ALTER TABLE statement. 

Let's use the stored procedure SYSPROC.ALTOBJ (this procedure will drop and recreate again the table with all the data and metadata). 

You can check the parameters at information center. 

The second parameter is the new create table DDL. 

Our example: 

$ db2 "call SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR','CREATE TABLE DB2INST1.EXAMPLE(KEYID INTEGER,DATA BLOB(2147483647) LOGGED NOT COMPACT) IN USERSPACE1',-1,?)" 

Value of output parameters 
-------------------------- 
Parameter Name : ALTER_ID 
Parameter Value : 2 

Parameter Name : MSG 
Parameter Value : SELECT OBJ_TYPE, OBJ_SCHEMA, OBJ_NAME, SQL_OPERATION, SQL_STMT, EXEC_MODE, EXEC_SEQ FROM SYSTOOLS.ALTOBJ_INFO_V WHERE ALTER_ID=2 AND EXEC_MODE LIKE '_1______' ORDER BY EXEC_SEQ 

Return Status = 0 

Check again with db2look 

$ db2look -d eric -e -t example 

CREATE TABLE "DB2INST1"."EXAMPLE" ( 
"KEYID" INTEGER , 
"DATA" BLOB(2147483647) LOGGED NOT COMPACT ) 
IN "USERSPACE1" ;