I'm trying to drop the audit policy on a DB2 12.1 system on AIX.

db2 drop audit policy SCHEMAPOLICY

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  The statement failed because one or more dependencies exist on the 
target object.  Target object type: "AUDIT POLICY". Name of an object that is 
dependent on the target object: "CURRENT SERVER". Type of object that is 
dependent on the target object: "DATABASE".  SQLSTATE=42893

The syntax is all over the place but here's what appears to be the hindrance
From the DB2 command prompt: 

SELECT substr(AUDITPOLICYNAME,1,12) as "Policy          ", \
OBJECTTYPE as "Obj Type", \
SUBOBJECTTYPE as "Sub Object Type", \
substr(OBJECTSCHEMA,1, 10) as "Schema", \
substr(OBJECTNAME,1,19) as "Object Name" \
FROM SYSCAT.AUDITUSE

Policy           Obj Type Sub Object Type Schema     Object Name        
---------------- -------- --------------- ---------- -------------------
SCHEMAPOLICY                              -          CURRENT SERVER   ** 

So how the heck do I get rid of this object so I can drop the policy? It's not needed just a proof of concept.

This is db2 LUW 12.1 on AIX (not mainframe/ZOS, the syntax differs wildly in some cases)

SUBOBJECTTYPE as "Sub Object Type", \
substr(OBJECTSCHEMA,1, 10) as "Schema", \
substr(OBJECTNAME,1,19) as "Object Name" \
FROM SYSCAT.AUDITUSE`

Policy           Obj Type Sub Object Type Schema     Object Name        
---------------- -------- --------------- ---------- -------------------
SCHEMAPOLICY                              -          CURRENT SERVER   ** 

So how the heck do I get rid of this object so I can drop the policy? It's not needed just a proof of concept.

This is db2 LUW 12.1 on AIX (not mainframe/ZOS, the syntax differs wildly in some cases)

I'm trying to drop the audit policy on a DB2 12.1 system on AIX.

db2 drop audit policy SCHEMAPOLICY

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  The statement failed because one or more dependencies exist on the 
target object.  Target object type: "AUDIT POLICY". Name of an object that is 
dependent on the target object: "CURRENT SERVER". Type of object that is 
dependent on the target object: "DATABASE".  SQLSTATE=42893

The syntax is all over the place but here's what appears to be the hindrance
From the DB2 command prompt: 

SELECT substr(AUDITPOLICYNAME,1,12) as "Policy          ", \
OBJECTTYPE as "Obj Type", \
SUBOBJECTTYPE as "Sub Object Type", \
substr(OBJECTSCHEMA,1, 10) as "Schema", \
substr(OBJECTNAME,1,19) as "Object Name" \
FROM SYSCAT.AUDITUSE

Policy           Obj Type Sub Object Type Schema     Object Name        
---------------- -------- --------------- ---------- -------------------
SCHEMAPOLICY                              -          CURRENT SERVER   ** 

So how the heck do I get rid of this object so I can drop the policy? It's not needed just a proof of concept.

This is db2 LUW 12.1 on AIX (not mainframe/ZOS, the syntax differs wildly in some cases)

SUBOBJECTTYPE as "Sub Object Type", \
substr(OBJECTSCHEMA,1, 10) as "Schema", \
substr(OBJECTNAME,1,19) as "Object Name" \
FROM SYSCAT.AUDITUSE`

Policy           Obj Type Sub Object Type Schema     Object Name        
---------------- -------- --------------- ---------- -------------------
SCHEMAPOLICY                              -          CURRENT SERVER   ** 

So how the heck do I get rid of this object so I can drop the policy? It's not needed just a proof of concept.

This is db2 LUW 12.1 on AIX (not mainframe/ZOS, the syntax differs wildly in some cases)

• db2
• audit

• Have you tried AUDIT DATABASE REMOVE POLICY before your DROP AUDIT POLICY statement? – Mark Barinstein Commented Feb 4 at 21:47
• That worked. Trying to read their output and their documentation isn't easy. I still think their syntax diagrams were created by Korean interrogators. Thank you for that advice, it's kind of daunting to see the keyword "database" and not have some trepidation. – Kevin Fries Commented Feb 5 at 12:56
• Not sure how to make the answer as accepted. db2 => AUDIT DATABASE REMOVE POLICY DB20000I The SQL command completed successfully. db2 => commit db2 => drop audit policy SCHEMAPOLICY DB20000I The SQL command completed successfully. db2 => commit – Kevin Fries Commented Feb 5 at 13:04
• I've added an answer, which you may mark as accepted. BTW, there is a small article How to read the syntax diagrams, which may help you understand the "language" used there... – Mark Barinstein Commented Feb 5 at 14:29

1 Answer 1

DB2 audit policy can't be dropped, if it's used to audit some database object. The error message shows the reason of inability to drop this policy: the database is audited with it. The audit policy use can be checked with the SYSCAT.AUDITUSE system view containing the corresponding row(s) with this policy name.
The same AUDIT statement is used to start and stop the audit policy use for some database object.

So, we have to issue the AUDIT DATABASE REMOVE POLICY statement prior to the DROP AUDIT POLICY one to drop this policy.