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

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

Cheers
Sathyaram

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

select from syscat.functions view

Cheers
Sathyaram

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

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

SET INTEGRITY FOR <tablename> IMMEDIATE CHECKED FORCE GENERATED