How to Restrict External Connections to DB2 VDBs (KBA6512)

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.3.0, 6.0.3.1, 6.0.4.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
4.3	4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2	4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1	4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

How to Restrict External Connections on DB2 Database

 A VDB refresh may fail due to an obscure error during provisioning types of jobs (provisions, refresh, rollback).  The following error is from the DB2 plugin diagnostic log for a  VDB displaying this behavior (locate plugin log on the target host under <Plugin directory>/DB2/logs/<DB2 instance name>/<VDN Name>.diag.log):

2020-09-10 Thu 08:41:24 CDT        recoverDB.sh: DB2 error getting count of BLU tables.  RC=4   SQL Resutl: SQL1655C  The operation could not be completed due to an error accessing data
on disk.  SQLSTATE=58030

This could be a mount issue, but if you look further up in the log and see a pattern like the following you may be experiencing a situation where DB2 external connections from active applications are connecting to the VDB, disrupting the maintenance phase of the job where the engine performs recovery and activates the VDB after this has been accomplished.  DB2 has no real built-in safeguards to protect a database from being accessed during this phase, and these connections can cause the VDB to enter a bad state. Here is a snippet from the same diagnostic log.  This particular job is a VDB refresh and the diagnostics snippet starts where original VDB is deleted:

2020-09-10 Thu 08:38:53 CDT      uncatalogDB.sh: Request to delete virtual database DB2VDB has been accepted.
2020-09-10 Thu 08:38:54 CDT      uncatalogDB.sh: Forcing all connections to DB2VDB
2020-09-10 Thu 08:38:56 CDT      uncatalogDB.sh: db2 "force application (20217, 20197, 20210, 8348, 19874, 20216, 20091, 19880, 20189, 20090, 20215, 20011, 20182, 20208, 20188, 20201, 20247, 20082, 19878, 20187, 19871, 20009, 20193, 20094, 20199, 20087, 19883, 20271, 20113, 19876, 8264, 8369, 20086, 8066, 20079)"
2020-09-10 Thu 08:38:57 CDT      uncatalogDB.sh: Applications successfully disconnected from the database
2020-09-10 Thu 08:39:01 CDT      uncatalogDB.sh: Deactivation of db DB2VDB is successful
2020-09-10 Thu 08:39:01 CDT      uncatalogDB.sh: Uncataloging database DB2VDB.
2020-09-10 Thu 08:39:19 CDT          cleanup.sh: Value of 35bf2354-9aae-4989-abdf-9db5d8fb47d4 and /opt/delphix/DB2/logs/db2instance/dup.DB2VDB.db2cmd.out.35bf2354-9aae-4989-abdf-9db5d8fb47d4
2020-09-10 Thu 08:39:19 CDT          cleanup.sh: Removing file /opt/delphix/DB2/logs/db2instance/DB2VDB.relocateDB.config.
2020-09-10 Thu 08:39:19 CDT          cleanup.sh: Removing file /opt/delphix/DB2/logs/db2instance/DB2VDB.system.info.
2020-09-10 Thu 08:39:19 CDT          cleanup.sh: value of partialCleanFlag : false
2020-09-10 Thu 08:39:19 CDT          cleanup.sh: Uncataloging database aliases if any exist.
2020-09-10 Thu 08:39:19 CDT          cleanup.sh: Completed deletion of database DB2VDB.
2020-09-10 Thu 08:40:38 CDT  validateInstall.sh: Value of additional parameters Linking :  and Provisioning :
2020-09-10 Thu 08:40:38 CDT  validateInstall.sh: Validating already existing database name on instance for provisioning
2020-09-10 Thu 08:40:38 CDT  validateInstall.sh: Check if there is an existing database in the instance with the same name as the VDB we are trying to create : DB2VDB
2020-09-10 Thu 08:40:54 CDT        recoverDB.sh: Starting provisioning of database DB2VDB from source_host:source_instance:source_dataset
2020-09-10 Thu 08:41:13 CDT        recoverDB.sh: Running db2relocatedb for DB2VDB
2020-09-10 Thu 08:41:19 CDT        recoverDB.sh: Activating database DB2VDB
2020-09-10 Thu 08:41:23 CDT        recoverDB.sh: Checking if there is a requirment to perform rbind operation
2020-09-10 Thu 08:41:24 CDT        recoverDB.sh: DB2 error getting count of BLU tables.  RC=4   SQL Resutl: SQL1655C  The operation could not be completed due to an error accessing data
on disk.  SQLSTATE=58030

The command db2 "force application...." is used to kill all the active connections on the VDB.  However, after the kill, there are a few minutes where active connections can re-establish a session and disrupt the recovery phase. During this VDB provisioning activity a restriction of these connections is desired to prevent a failure.

The process below offers a way to create a DB2 procedure that a DB2 DBA can customize to their own environments, depending on the source of the applications, and effectively prevent the disruption. 

cat <<-EOF > /<Path on your target host>/<VDB Name>.CONNECT_PROC.sql
connect to <VDB Name>@
CREATE OR REPLACE PROCEDURE CP.CONNECT_PROCEDURE ()
LANGUAGE SQL
BEGIN
IF ( CLIENT_IPADDR <> '<VDB host name>' )
THEN
SIGNAL SQLSTATE '42502' SET MESSAGE_TEXT='Connection refused !';
END IF;
END
@
update db cfg for <VDB Name> using CONNECT_PROC "CP.CONNECT_PROCEDURE"@
terminate@
EOF

db2 -td@ -v -f  /<Path on your staging>/<VDB Name>.CONNECT_PROC.sql

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