IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2019-03-15T03:39:44Z by stiruvee
dlstrawn
dlstrawn
6 Posts

Pinned topic Dropping a column from a table

‏2019-03-14T14:59:57Z |

I have a table that has a column defined as char(2), default '  '...  I want to change the default to NULL, but nothing I am doing is working...  I used: "alter table pubwrksf.Material alter column InvnType set not null" which gives the meaningless to me -  SQL0952 reason code 10...  I have tried to drop existing default first, change the default to something else, and other options, but nothing allows me to use set not null (which is how the DB2 for i documentation says its done) on that column...  Any ideas???

 

  • B.Hauser
    B.Hauser
    320 Posts

    Re: Dropping a column from a table

    ‏2019-03-14T15:24:12Z  

    If you want to remove the NOT NULL default, try the following statement:

    Alter Table YourSchema.YourTable
       Alter Column YourCoumn Drop Not Null;
    

     

    Or do a reverse engineering and retrieve the CREATE TABLE Statement.

    Convert the CREATE TABLE statement into a CREATE OR REPLACE TABLE statement, i.e. add OR REPLACE.

    Modify the SQL script with the of the CREATE OR REPLACE TABLE statement, i.e. just remove NOT NULL DEFAUT and (re)run the CREATE OR REPLACE TABLE Statement:

    1. Original CREATE Statement:

    Create table YourSchema/YourTable
    (YourCol1  VarChar(10) Not NULL Default '',
     YourCol2  integer     Not NULL Default 0,
     YourCol3  VarChar(10) Not NULL Default '' ,
     YourCol4  Date        not NULL Default Current Date);
    

     

    2. Modified CREATE OR REPLACE Statement:

    Create Or replace table YourSchema/YourTable

    (YourCol1  VarChar(10) Not NULL Default '',
     YourCol2  integer     Not NULL Default 0,
     YourCol3  VarChar(10),
     YourCol4  Date        not NULL Default Current Date);
    

     

    Birgitta

  • dlstrawn
    dlstrawn
    6 Posts

    Re: Dropping a column from a table

    ‏2019-03-14T15:55:07Z  
    • B.Hauser
    • ‏2019-03-14T15:24:12Z

    If you want to remove the NOT NULL default, try the following statement:

    <pre dir="ltr">Alter Table YourSchema.YourTable Alter Column YourCoumn Drop Not Null; </pre>

     

    Or do a reverse engineering and retrieve the CREATE TABLE Statement.

    Convert the CREATE TABLE statement into a CREATE OR REPLACE TABLE statement, i.e. add OR REPLACE.

    Modify the SQL script with the of the CREATE OR REPLACE TABLE statement, i.e. just remove NOT NULL DEFAUT and (re)run the CREATE OR REPLACE TABLE Statement:

    1. Original CREATE Statement:

    <pre dir="ltr">Create table YourSchema/YourTable (YourCol1 VarChar(10) Not NULL Default '', YourCol2 integer Not NULL Default 0, YourCol3 VarChar(10) Not NULL Default '' , YourCol4 Date not NULL Default Current Date); </pre>

     

    2. Modified CREATE OR REPLACE Statement:

    Create Or replace table YourSchema/YourTable

    <pre dir="ltr">(YourCol1 VarChar(10) Not NULL Default '', YourCol2 integer Not NULL Default 0, YourCol3 VarChar(10), YourCol4 Date not NULL Default Current Date); </pre>

     

    Birgitta

    So, I made a critical error in my problem above - I said: "I want to change the default to NULL", I should have said: "I want to change the default to NOT NULL"....

    However, I have the original SQL create statement, and when I modify it to include the new column and run it, it throws an error saying the table already exists...  When I change the create to replace, it doesn't recognize the statement replace...  here is my Create Table:

    create table pubwrksf.Material (
        MatrNmbr integer as identity, 
        MatrDesc char(40) not null, 
        ManuNmbr char(20) not null, 
        MatrUntM char(10) not null default ' ', 
        MatrUntC numeric(9,2) not null, 
        RvFundNo numeric(3,0) not null,  
        RvFuncNo numeric(3,0) not null,  
        RvAcctNo numeric(6,0) not null,  
        RvSubANo numeric(4,0) not null,  
        RvProjNo numeric(4,0) not null,  
        ExFundNo numeric(3,0) not null,  
        ExFuncNo numeric(3,0) not null,  
        ExDeptNo numeric(3,0) not null,  
        ExDivnNo numeric(3,0) not null,  
        ExLItmNo numeric(4,0) not null,  
        ExProjNo numeric(4,0) not null,  
        CatgCode char(2) not null, 
        CurBgBal numeric(9,2) not null,
        BgBalDte date not null,
        StatusCd char(10) not null implicitly hidden default 'Active', 
        CreateBy char(64) not null implicitly hidden, 
        CreateOn timestamp not null implicitly hidden default current timestamp, 
        LstChgBy char(64) not null implicitly hidden, 
        LstChgOn for each row on update as row change timestamp 
            not null implicitly hidden,
        constraint pubwrksf.Material_MatrNmbr_pk 
            primary key (MatrNmbr),
        constraint pubwrksf.Material_CatgCode_fk 
            foreign key (CatgCode)
            references pubwrksf.Category (CatgCode)
            on delete cascade, 
        constraint pubwrksf.Material_StatusCd_ck 
            check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
    );

    Here is what I changed it to (i tried create and replace):

    create or replace table pubwrksf.Material (
        MatrNmbr integer as identity, 
        MatrDesc char(40) not null, 
        ManuNmbr char(20) not null, 
        InvnType char(2) not null, 
        MatrUntM char(10) not null default ' ', 
        MatrUntC numeric(9,2) not null, 
        RvFundNo numeric(3,0) not null,  
        RvFuncNo numeric(3,0) not null,  
        RvAcctNo numeric(6,0) not null,  
        RvSubANo numeric(4,0) not null,  
        RvProjNo numeric(4,0) not null,  
        ExFundNo numeric(3,0) not null,  
        ExFuncNo numeric(3,0) not null,  
        ExDeptNo numeric(3,0) not null,  
        ExDivnNo numeric(3,0) not null,  
        ExLItmNo numeric(4,0) not null,  
        ExProjNo numeric(4,0) not null,  
        CatgCode char(2) not null, 
        CurBgBal numeric(9,2) not null,
        BgBalDte date not null,
        StatusCd char(10) not null implicitly hidden default 'Active', 
        CreateBy char(64) not null implicitly hidden, 
        CreateOn timestamp not null implicitly hidden default current timestamp, 
        LstChgBy char(64) not null implicitly hidden, 
        LstChgOn for each row on update as row change timestamp 
            not null implicitly hidden,
        constraint pubwrksf.Material_MatrNmbr_pk 
            primary key (MatrNmbr),
        constraint pubwrksf.Material_CatgCode_fk 
            foreign key (CatgCode)
            references pubwrksf.Category (CatgCode)
            on delete cascade, 
        constraint pubwrksf.Material_StatusCd_ck 
            check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
    );

    the modified statement gave:

    [SQL0952] Processing of the SQL statement ended. Reason code 10. Cause . . . . . : The SQL operation was ended before normal completion. The reason code is 10. Reason codes and their meanings are: 1 -- An SQLCancel API request has been processed, for example from ODBC. 2 -- SQL processing was ended by sending an exception. 3 -- Abnormal termination. 4 -- Activation group termination. 5 -- Reclaim activation group or reclaim resources. 6 -- Process termination. 7 -- An EXIT function was called. 8 -- Unhandled exception. 9 -- A Long Jump was processed. 10 -- A cancel reply to an inquiry message was received. 11 -- Open Database File Exit Program (QIBM_QDB_OPEN). 0 -- Unknown cause. Recovery . . . : If the reason code is 1, a client request was made to cancel SQL processing. For all other reason codes, see previous messages to determine why SQL processing was ended.

    What I'm I doing wrong???

     

    Updated on 2019-03-14T15:58:13Z at 2019-03-14T15:58:13Z by dlstrawn
  • stiruvee
    stiruvee
    48 Posts

    Re: Dropping a column from a table

    ‏2019-03-15T03:39:44Z  
    • dlstrawn
    • ‏2019-03-14T15:55:07Z

    So, I made a critical error in my problem above - I said: "I want to change the default to NULL", I should have said: "I want to change the default to NOT NULL"....

    However, I have the original SQL create statement, and when I modify it to include the new column and run it, it throws an error saying the table already exists...  When I change the create to replace, it doesn't recognize the statement replace...  here is my Create Table:

    create table pubwrksf.Material (
        MatrNmbr integer as identity, 
        MatrDesc char(40) not null, 
        ManuNmbr char(20) not null, 
        MatrUntM char(10) not null default ' ', 
        MatrUntC numeric(9,2) not null, 
        RvFundNo numeric(3,0) not null,  
        RvFuncNo numeric(3,0) not null,  
        RvAcctNo numeric(6,0) not null,  
        RvSubANo numeric(4,0) not null,  
        RvProjNo numeric(4,0) not null,  
        ExFundNo numeric(3,0) not null,  
        ExFuncNo numeric(3,0) not null,  
        ExDeptNo numeric(3,0) not null,  
        ExDivnNo numeric(3,0) not null,  
        ExLItmNo numeric(4,0) not null,  
        ExProjNo numeric(4,0) not null,  
        CatgCode char(2) not null, 
        CurBgBal numeric(9,2) not null,
        BgBalDte date not null,
        StatusCd char(10) not null implicitly hidden default 'Active', 
        CreateBy char(64) not null implicitly hidden, 
        CreateOn timestamp not null implicitly hidden default current timestamp, 
        LstChgBy char(64) not null implicitly hidden, 
        LstChgOn for each row on update as row change timestamp 
            not null implicitly hidden,
        constraint pubwrksf.Material_MatrNmbr_pk 
            primary key (MatrNmbr),
        constraint pubwrksf.Material_CatgCode_fk 
            foreign key (CatgCode)
            references pubwrksf.Category (CatgCode)
            on delete cascade, 
        constraint pubwrksf.Material_StatusCd_ck 
            check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
    );

    Here is what I changed it to (i tried create and replace):

    create or replace table pubwrksf.Material (
        MatrNmbr integer as identity, 
        MatrDesc char(40) not null, 
        ManuNmbr char(20) not null, 
        InvnType char(2) not null, 
        MatrUntM char(10) not null default ' ', 
        MatrUntC numeric(9,2) not null, 
        RvFundNo numeric(3,0) not null,  
        RvFuncNo numeric(3,0) not null,  
        RvAcctNo numeric(6,0) not null,  
        RvSubANo numeric(4,0) not null,  
        RvProjNo numeric(4,0) not null,  
        ExFundNo numeric(3,0) not null,  
        ExFuncNo numeric(3,0) not null,  
        ExDeptNo numeric(3,0) not null,  
        ExDivnNo numeric(3,0) not null,  
        ExLItmNo numeric(4,0) not null,  
        ExProjNo numeric(4,0) not null,  
        CatgCode char(2) not null, 
        CurBgBal numeric(9,2) not null,
        BgBalDte date not null,
        StatusCd char(10) not null implicitly hidden default 'Active', 
        CreateBy char(64) not null implicitly hidden, 
        CreateOn timestamp not null implicitly hidden default current timestamp, 
        LstChgBy char(64) not null implicitly hidden, 
        LstChgOn for each row on update as row change timestamp 
            not null implicitly hidden,
        constraint pubwrksf.Material_MatrNmbr_pk 
            primary key (MatrNmbr),
        constraint pubwrksf.Material_CatgCode_fk 
            foreign key (CatgCode)
            references pubwrksf.Category (CatgCode)
            on delete cascade, 
        constraint pubwrksf.Material_StatusCd_ck 
            check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
    );

    the modified statement gave:

    [SQL0952] Processing of the SQL statement ended. Reason code 10. Cause . . . . . : The SQL operation was ended before normal completion. The reason code is 10. Reason codes and their meanings are: 1 -- An SQLCancel API request has been processed, for example from ODBC. 2 -- SQL processing was ended by sending an exception. 3 -- Abnormal termination. 4 -- Activation group termination. 5 -- Reclaim activation group or reclaim resources. 6 -- Process termination. 7 -- An EXIT function was called. 8 -- Unhandled exception. 9 -- A Long Jump was processed. 10 -- A cancel reply to an inquiry message was received. 11 -- Open Database File Exit Program (QIBM_QDB_OPEN). 0 -- Unknown cause. Recovery . . . : If the reason code is 1, a client request was made to cancel SQL processing. For all other reason codes, see previous messages to determine why SQL processing was ended.

    What I'm I doing wrong???

     

     

    * Check following link for workaround to fix SQL0952 (reason code 10) error when using JDBC/ODBC application to alter table definition.

    https://www-01.ibm.com/support/docview.wss?uid=nas8N1000052

     

    *  Also, you can use STRSQL  to  alter table definition and reply to inquiry message manually