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 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.