Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Alter decimal precision

    Hi,

    I am using DB2 vers 7 and I have made a stupid mistake I need to rectify. I created a column called repair_approval to store costs. However, in my haste I set it to precision 5, scale 0 when in fact it needed to be precision 8, scale 5. Now, I know there are issues with altering table columns with this version of db2 and I was wondering if it is possible to alter the scale and precision of columns. I tried:

    alter table subdiv alter column repair_approval SET DATA TYPE DECIMAL (8,2)

    but predictably it didn't work. Does anyone know of any other way this can be done without going through the rigmarole or dropping and recreating the table.

    Thanks

    Richard

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

    Re: Alter decimal precision

    Unfortunatley, you will have to take the export-drop-create-load route , unless you have some change management tools

    Cheers
    Sathyaram

    Originally posted by Rdean
    Hi,

    I am using DB2 vers 7 and I have made a stupid mistake I need to rectify. I created a column called repair_approval to store costs. However, in my haste I set it to precision 5, scale 0 when in fact it needed to be precision 8, scale 5. Now, I know there are issues with altering table columns with this version of db2 and I was wondering if it is possible to alter the scale and precision of columns. I tried:

    alter table subdiv alter column repair_approval SET DATA TYPE DECIMAL (8,2)

    but predictably it didn't work. Does anyone know of any other way this can be done without going through the rigmarole or dropping and recreating the table.

    Thanks

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

  3. #3
    Join Date
    Nov 2003
    Posts
    17

    Re: Alter decimal precision

    Hi again,

    Thanks for the quick reply, I figured as much! I bowed to the inevitability and have tried to drop the table but I get the following message:

    [IBM][CLI Driver][DB2/NT] SQL0478N The object type "TABLE" cannot be dropped because there is an object "LU.SQL030218155415477", of type "FUNCTION", which depends on it. SQLSTATE=42893

    When I look in the user defined functions area I only see functions with schema Sysfun, and not Lu (which is our schema). Now I know my predecessor who created the database, also created a bunch of functions (create function ....) but I cannot find a way of viewing these. I assume it must be one of these that it is holding up the drop table but I, at the very least, need to know its name so that I can drop it as well!

    So, to recap, do you know how to find a list of functions created by the user if they are not appearing in the user defined functions list in control center?

    Grr, is going to be a day of running in circles I feel!

    Thanks

    Richard



    Originally posted by sathyaram_s
    Unfortunatley, you will have to take the export-drop-create-load route , unless you have some change management tools

    Cheers
    Sathyaram

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

    Re: Alter decimal precision

    select from syscat.functions view

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

  5. #5
    Join Date
    Nov 2003
    Posts
    17

    Check pending table problem

    Hello

    I managed to finally drop the table (having previously exported the contents to an ixf file). I then recreated the table and reinserted the data from this file. I got the expected check pending problems and tried to solve it by doing the following:

    BACKUP DB dbname TABLESPACE tablespacename online to ...

    QUIESCE TABLESPACES FOR TABLE schema.tablename RESET

    So now if I list tablespaces all appears to be normal. Both functions above complete without error. However, when I then go on to try and access the table data I get:

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL0668N Operation not allowed when the underlying table (or a dependent table) is in the Check Pending state. SQLSTATE=57016

    I thought that it may be because of the way I had reinserted the data so tried to drop the table again - but it won't let me! I also checked any dependent tables but they all allow access. So, does anyone know if there is another way of removing the check pending state or is there away I can at least suppress it so that I can drop the table?

    Thanks

    Richard

  6. #6
    Join Date
    Nov 2003
    Posts
    17

    Re: Check pending table problem

    In case anyone is interested this got it working...

    SET INTEGRITY FOR <tablename> IMMEDIATE CHECKED FORCE GENERATED



    Originally posted by Rdean
    Hello

    I managed to finally drop the table (having previously exported the contents to an ixf file). I then recreated the table and reinserted the data from this file. I got the expected check pending problems and tried to solve it by doing the following:

    BACKUP DB dbname TABLESPACE tablespacename online to ...

    QUIESCE TABLESPACES FOR TABLE schema.tablename RESET

    So now if I list tablespaces all appears to be normal. Both functions above complete without error. However, when I then go on to try and access the table data I get:

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL0668N Operation not allowed when the underlying table (or a dependent table) is in the Check Pending state. SQLSTATE=57016

    I thought that it may be because of the way I had reinserted the data so tried to drop the table again - but it won't let me! I also checked any dependent tables but they all allow access. So, does anyone know if there is another way of removing the check pending state or is there away I can at least suppress it so that I can drop the table?

    Thanks

    Richard

Posting Permissions

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