How to drop dependent object

[Former Member]

Hi Experts,

I am uninstalling SAP instance now and get stuck in the drop schema phase due to the following error:

drop schema DB2BJ1 restrict

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA"

cannot be processed because there is an object

"DB2BJ1.EVMON_LOCKING_SCHEMA_SQL09070", of type "XSROBJECT", which depends on

it. SQLSTATE=42893

Please provide me the db2 command to remove the dependent object.

Thanks and Regards,

Rudi

[former_member189725 · Accepted solution]

Run the SQL

drop XSROBJECT DB2BJ1.EVMON_LOCKING_SCHEMA_SQL09070.

Then try to drop the schema.

You can also use the ADMIN_DROP_SCHEMA procedure to drop a schema.

[Former Member · Accepted solution]

Hi Ratnajit,

Thanks for your help, rewards will be given

I can remove the first object after running the command you have given, however there's still dependent object as follows:

db2 drop XSROBJECT DB2BJ1.EVMON_LOCKING_SCHEMA_SQL09070

DB20000I The SQL command completed successfully.

db2 drop schema DB2BJ1 restrict

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA"

cannot be processed because there is an object "DB2BJ1.SQL08104503375514", of

type "PLAN", which depends on it. SQLSTATE=42893

db2 drop PLAN DB2BJ1.SQL08104503375514

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0104N An unexpected token "DB2BJ1" was found following "drop PLAN ".

Expected tokens may include: "JOIN". SQLSTATE=42601

Please guide me how to remove DB2BJ1.SQL08104503375514 object?

Regards,

Rudi

[former_member189725 · Accepted solution]

try -- > db2 drop plan SQL08104503375514

Check if this works.

[Former Member · Accepted solution]

db2 drop plan SQL08104503375514

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0104N An unexpected token "plan" was found following "drop ". Expected

tokens may include: "JOIN <joined_table>". SQLSTATE=42601

[Former Member · Accepted solution]

Hi Ratnajit,

Is there a command to remove all dependent object which connect to the schema?

Regards,

Rudi

[Former Member · Accepted solution]

Any suggestion?

[former_member189725 · Accepted solution]

Try to check this

select name from sysibm.sysplan where default_schema='DB2BJ1' and name like '%SQL%'

if the object exists.

Is it an MCOD database where you have schema for other systems . IF not directly drop the database for uninstalling.

db2 drop db <SID>.

Edited by: Ratnajit Dey on Dec 8, 2011 5:45 PM

[Former Member · Accepted solution]

HI Ratnajit,

I have deleted BJ1 database after running db2 terminate and db2 force application all, however the uninstallation from sapinst can not be performed since the DB <BJ1> has been removed. I am now re-installing the SAP instance after removing old folder under /usr/sap/BJ1 and /db2/db2bj1 manually.

sapinst is stopped at phase 'update database registry' due to:

folder or file /db2/db2bj1/sqllib/adm/db2set does not exist. I checked that folder sqllib and all the subfolder does not exist, and also db2set file.

I wonder why sqllib folder is not yet created by sapinst every time i did re-install sap instance. It is different when i perform fresh install, update database registry does not have problem at all, since sapinst created sqllib folder beforehand.

Regards,

Rudi

[Former Member · Accepted solution]

the manual run for db2 set returns error as follow:

./db2set DB2_WORKLOAD=SAP

DB2SET processing complete, rc = -10013, SQLCODE = -10013

Please advise.

Regards,

Rudi

[former_member189725 · Accepted solution]

You should never delete the instance and the db2 software manually. Please stop the sapinst.

Go to the directory /db2/db2bj1/db2_software/instance

db2idrop db2bj1

Then delete the db2 software copy .. /db2/db2bj1/db2_software/install

./db2_deinstall -b /db2/db2bj1/db2_software

Start the sapinst from scratch.

[Former Member · Accepted solution]

Hi Ratnajit,

Thanks for your feedback, however it's too late, i've reinstalled the instance from scratch after doing manual deletion, yet i appreciate your effort and i reward point for you.

The system is now completely finished after struggling with some errors.

Thank you and Regards,

Rudi