Db2 – Problem deleting detached partition in DB2 LUW V9.7

db2db2-luwpartitioning

Recently in prod environment we are having problems while dropping the detached partitions its giving this error

""" DB21034E The command was processed as an SQL statement because it
was not a valid Command Line Processor command. During SQL processing
it returned: SQL20285N The statement or command is not allowed because
the table named "schemaname.tablename_JAN1" has detached dependents or
the asynchronous partition detach operation is not complete. Reason
code = "2". SQLSTATE=55057"""

I did some research and could find this from IBM saying we need to restart the instance to solve the issue. Link below I think you could have seen many of such incidents of what i described, please help me with this issue thanks in advance

http://www-01.ibm.com/support/docview.wss?uid=swg21515721

Best Answer

When you detach a partition from a table, the detach operation returns quickly, but DB2 then starts a background process to clean up any global indexes. You can't drop the table containing the detached partition until this process completes.

So, have you verified that the asynchronous cleanup that occurs after you issue the ALTER TABLE ... DETACH is actually finished?

Related Solutions

DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.

db2 grant setsessionuser on public to group DB2ADMNS

Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:

db2 grant setsessionuser on user bobabob97 to group DB2ADMNS

So now I can perform

db2 set session_user=bobabob97

and it works.

To switch back I only need to run

db2 set session_user=system_user

Db2 – create a stored procedure if it doesnt exist using a ddl in db2

IBM Added The OR REPLACE option in version 7.1

So your code should work like this :

CREATE OR REPLACE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL 
BEGIN 

......
......
update/select statements
......

END

Best Answer

Related Solutions

DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

Db2 – create a stored procedure if it doesnt exist using a ddl in db2

Related Question