Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186

    Unanswered: Adding columns with functions in table : Need help

    Hi All,
    Here's a interesting situation. We have a table defined as below:
    create table CONTACT(
    OFFICEID INTEGER NOT NULL,
    CONTACTID INTEGER NOT NULL,
    PRIVATE INTEGER NOT NULL DEFAULT 0,
    RECORDTYPE INTEGER NOT NULL DEFAULT 1,
    CONTACTTYPE INTEGER DEFAULT 0,
    SUBTYPE VARCHAR(15) ,
    LASTNAME VARCHAR(128) NOT NULL DEFAULT '',
    FIRSTNAME VARCHAR(64) DEFAULT '',
    PRIMARY KEY (OFFICEID,CONTACTID)
    ) ;
    GRANT INSERT, UPDATE, DELETE, SELECT, REFERENCES ON CONTACT TO PUBLIC;

    Now say this table has 10,000 records and many tables referencing to it.
    I have to add two new columns which are as follows:

    UCLASTNAME VARCHAR(128) GENERATED ALWAYS AS ( UCASE ( LASTNAME ) )
    UCFIRSTNAME VARCHAR(64) GENERATED ALWAYS AS ( UCASE ( FIRSTNAME ) )

    If i add them when the DB is in use, i get the error, that function based columns can not be added, so i do following,
    steps followed were:

    1.set integrity for contact off.
    2.added the required columns.
    3.set integrity for contact immediate checked on (all the three steps executed successfully.)

    Still we are getting the error SQL0668N Operation not allowed when the underlying table (or a dependent table) is in the Check Pending state.

    Are the above steps enough or any other steps need to be followed??

    Please help us.

    -Prashant
    Prashant

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Adding columns with functions in table : Need help

    after step2 , but before step 3 , try doing

    db2 set integrity for <tablename> immediate checked force generated



    HTH

    Cheers

    Sathyaram


    Originally posted by dahalkar_p
    Hi All,
    Here's a interesting situation. We have a table defined as below:
    create table CONTACT(
    OFFICEID INTEGER NOT NULL,
    CONTACTID INTEGER NOT NULL,
    PRIVATE INTEGER NOT NULL DEFAULT 0,
    RECORDTYPE INTEGER NOT NULL DEFAULT 1,
    CONTACTTYPE INTEGER DEFAULT 0,
    SUBTYPE VARCHAR(15) ,
    LASTNAME VARCHAR(128) NOT NULL DEFAULT '',
    FIRSTNAME VARCHAR(64) DEFAULT '',
    PRIMARY KEY (OFFICEID,CONTACTID)
    ) ;
    GRANT INSERT, UPDATE, DELETE, SELECT, REFERENCES ON CONTACT TO PUBLIC;

    Now say this table has 10,000 records and many tables referencing to it.
    I have to add two new columns which are as follows:

    UCLASTNAME VARCHAR(128) GENERATED ALWAYS AS ( UCASE ( LASTNAME ) )
    UCFIRSTNAME VARCHAR(64) GENERATED ALWAYS AS ( UCASE ( FIRSTNAME ) )

    If i add them when the DB is in use, i get the error, that function based columns can not be added, so i do following,
    steps followed were:

    1.set integrity for contact off.
    2.added the required columns.
    3.set integrity for contact immediate checked on (all the three steps executed successfully.)

    Still we are getting the error SQL0668N Operation not allowed when the underlying table (or a dependent table) is in the Check Pending state.

    Are the above steps enough or any other steps need to be followed??

    Please help us.

    -Prashant
    Last edited by sathyaram_s; 11-12-02 at 11:51.

  3. #3
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi Sathyaram,

    Do we need to restart the db server in between. I have to avoid this if possible.

    -Prashant
    Prashant

  4. #4
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    dependent tables are in CHKP state

    do step
    4. Set Integrity for Dep1, Dep2... Immediate Checked

    You don't need to restart the db server after these.

    HTH,
    Oliver

Posting Permissions

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