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 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168
22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168
22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199
188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52
184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124 ,126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168
22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124
126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52 , 184.108.40.206, 220.127.116.11
18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124
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.
To Restrict External Connections
Complete the following procedure to restrict external connections.
- On the target host running the DB2 instance for the VDB, create a procedure similar to the following. Your DBA can customize this to suit their needs:
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
Any CLIENT_IPADDR that is not the local host will be subject to receiving the signal.
- To restrict the external connections you can add a command like this to a pre-refresh hook on the VDB:
db2 -td@ -v -f /<Path on your staging>/<VDB Name>.CONNECT_PROC.sql
- When you are finished, you can unset the DB2 property with these commands on a post-refresh hook.
db2 "connect to <VDB Name>" db2 "update db cfg for <VDB Name> using CONNECT_PROC NULL"
- During a Provision job, you will likely not encounter this situation, but you can restrict the connections on a Configure_Clone hook and then open up the VDB to connections on a Post_Start hook.
The following articles may provide more information or related information to this article: