Refreshing or Deleting a DB2 VDB after a SQL5187N error (KBA9628)

Issue

When attempting to perform VDB operations such as Refresh or Delete on a DB2 VDB, the following error may be reported:

Failed to execute the DB2 script /opt/delphix/toolkit/.../plugin/DB2_.../logs/myinstance/MYVDB.CONNECT_PROC.sql.

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL5187N The "CREATE OR REPLACE PROCEDURE" operation is not allowed for
procedure "CP.CONNECT_PROCEDURE" because a connection procedure with the same
name is defined for the database. LINE NUMBER=8. SQLSTATE=429C8

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Background

In order to ensure that a DB2 VDB cannot be accessed while provisioning processes are still running, a "connection procedure" is temporarily defined to restrict connections.

In cases when the provisioning process is aborted or fails, this connection procedure may remain in place and need to be removed manually.

Resolution

To resolve this issue and prevent the SQL5187N error, connect to the server running the affected VDB and remove the connection procedure from the VDB by modifying the CONNECT_PROC database configuration parameter:

db2 "connect to <VDB Name>"
db2 "update db cfg for <VDB Name> using CONNECT_PROC NULL"

Once this change is made, the failing VDB operations should run successfully to completion.

Delphix Engineering is aware of this behavior, and considering improvements for a future release.