Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2005
    Posts
    47

    Unanswered: Altering a Table Column with data from not null to null

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Any chance you know the DB2 Version and platform ..

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2005
    Posts
    47
    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.

  4. #4
    Join Date
    Mar 2005
    Posts
    108
    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.

  5. #5
    Join Date
    Jul 2005
    Posts
    47
    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

  6. #6
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338

    Cool

    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

  7. #7
    Join Date
    Jul 2005
    Posts
    47
    Hi Jayan,

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

  8. #8
    Join Date
    Mar 2005
    Posts
    108
    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

  9. #9
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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

  10. #10
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    is there any way to do it opposite: from 'null' to 'not null'?
    Thanks,
    Grofaty

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    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

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    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

  14. #14
    Join Date
    Jan 2003
    Posts
    1,605
    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:
    Code:
    SQL0443N  Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE
    with diagnostic text "SQL1227  Reason code or token: 5|AVGCOLLEN|8
    ".  SQLSTATE=38553
    ... 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
    Last edited by grofaty; 05-31-06 at 05:25.

  15. #15
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 05-31-06 at 06:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •