Hi,

Can anyone please tell me the best,simple and fastest method to change a table column which has data in it from not null to null in DB2.

I have worked on it by exporting ,dropping recreating and importing. But i feel there is another simple way to do it.

Thanks,
Venky

Any chance you know the DB2 Version and platform ..

Sathyaram

Thanks for replying to my post as I have seen people viewing it but no replies.
The version I am working on is DB2/AIX64 8.2.2.

There is no better way when you have to stick to DB2. Even next version of DB2 does not let you alter the column directly to NULL.

Thanks for the reply. I just wanted to clear my doubt. I thought DB2 allows you to make it null from not null but not Null to Not Null.

Venky

If you are on Version 8.2 you can try the following: It worked for me,

Step1> In this table COL1 isnot null.
db2 "describe table tb_test1"

Column Type Type
name schema name Length Scale Nulls

------------------------------ --------- ------------------ -------- ----- -COL1 SYSIBM SMALLINT 2 0 No

COL2 SYSIBM SMALLINT 2 0 Yes

You have some records loaded in this table.

Step2> Run the following Stored Proc:

db2 "CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE SCHEM1.TB_TEST1 ( COL1 SMALLINT , col2 smallint ) IN USERSPACE1 PARTITIONING KEY ( COL1) ', -1, ? )"

(Note:If you have two not null cols which you want to make null then (COL1,COL2)',-1.....)

Step3>
See your desired result.

db2 "describe table tb_test1"

Column Type Type
name schema name Length Scale Nulls

------------------------------ --------- ------------------ -------- ----- -COL1 SYSIBM SMALLINT 2 0 Yes

COL2 SYSIBM SMALLINT 2 0 Yes


Thanks,
Jay
New Delhi, India

Hi Jayan,

Thanks for the solution ,looks like this will be helpful to many people.Will try this SP and check out.

Hi Jay,
This SP is very helpful. Could you please share a little more, where is this SP documented? Can you post a link please? I would like to learn about some other SP's, too.

Thanks,
Gary

Hi,

You can see the list of all the available procs by running the following. Regarding the arguments you need to pass you can search the same at IBM Developers works:

db2 "select PROCNAME from syscat.procedures where PROCSCHEMA = 'SYSPROC' "

Thanks,
Jayanta Datta
New Delhi, India

Hi,

You can see the list of all such available system procs by running the following. Regarding the arguments you need to pass you can search the same at IBM Developers works:

db2 "select PROCNAME from syscat.procedures where PROCSCHEMA = 'SYSPROC' "

Thanks,
Jayanta Datta
New Delhi, India

Hi,
is there any way to do it opposite: from 'null' to 'not null'?
Thanks,
Grofaty

Hi,

I have figured out:

CALL SYSPROC.ALTOBJ ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL, COL2 SMALLINT NOT NULL) IN USERSPACE1 PARTITIONING KEY (COL1)', -1, ? );

This is excelent news!!!
Grofaty

Hi,
this also works:

CALL SYSPROC.ALTOBJ ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL, COL2 SMALLINT NOT NULL) IN USERSPACE1 PARTITIONING KEY (COL1,COL2)', -1, ? );

and this also works:

CALL SYSPROC.ALTOBJ ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL, COL2 SMALLINT NOT NULL)', -1, ? );

Why is PARTITIONING KEY cause important?

Thanks,
Grofaty

Hi,
it is interesting if primary key is created on table (but I am not changing the primary key columns definitions) the the following error is returned:
... but table definitions are changed anyway. What does this error means?

My system: db2 v8.1 fixpack 9 (which is equal to v8.2.2) on Windows.

Thanks,
Grofaty

Hi,
I have found out that some statistics are changed before and after the SP if runstat is run.

I did:
1. runstats on table SCHEM1.TB_TEST1 with distribution and detailed indexes all
2. select * from sysibm.sysindexes where tbname='TB_TEST1'
3. execute SP
4. repeat step 1
5. repeat step 2

Statistic from step 2 and step 5 are changed. Why? Is that the reason of error code posted in previous post?

Thanks,
Grofaty