Disengaging DB2 Transactional Logging on a VDB (KBA6205)

Issue

If an application is producing a large amount of transactional processing on a DB2 VDB it might be useful to turn the logging off to reduce overhead and keep disk usage to manageable level. Here are some techniques to use to turn the DB2 transactional logging off.

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

Resolution

Here are the steps to take to enable this ability

Pre-existing VDB

• Run a snapshot of the VDB before this procedure.  This provides a safety measure in case something goes wrong in the procedure and the VDB needs to be rolled back.

• Change cfg for LOGARCHMETH1 - turn it off

• db2 update db cfg for VDB2 using LOGARCHMETH1 OFF

• Next, issue this command from command line as the VDB instance user:

  • db2 deactivate db VDB2

• Create an offline backup of the VDB:

  • db2 backup db VDB2

• Activate the VDB: 

  • db2 activate db VDB2

Take snapshot of VDB2. 

Provisioning VDB (from dSource or another VDB):

• During the Provision you can add database configuration parameters under the Target Configuration page in the GUI. After providing the VDB name click the "+ Add" button under the "Virtual Database Configuration Setting" section and enter a Property Name (LOGARCHMETH1) and Value (OFF) to the fields.  If you are not adding more parameters just proceed to the next page, clicking "Next".
• The VDB will provision and not use transactional logging.

Troubleshooting

• If you attempt to provision from the VDB after you turn off logging, when creating a snapshot you might see this error:

2020-07-21 Tue 09:49:45 PDT recoverDB.sh: Info: Activating database VDB4
2020-07-21 Tue 09:49:45 PDT recoverDB.sh: Debug: db2 -x activate db VDB4
2020-07-21 Tue 09:49:47 PDT recoverDB.sh: Debug: SQL1116N A connection to or activation of database "VDB4" failed because the 
database is in BACKUP PENDING state. SQLSTATE=57019
2020-07-21 Tue 09:49:47 PDT recoverDB.sh: Error: Unable to activate db VDB4
2020-07-21 Tue 09:49:47 PDT recoverDB.sh: Error: SQL1116N A connection to or activation of database "VDB4" failed because the 
database is in BACKUP PENDING state. SQLSTATE=57019
2020-07-21 Tue 09:49:47 PDT recoverDB.sh: Info: Uncataloged the database on exit

You can resolve the error by configuring the child VDB utilizing the Virtual Database Configuration Settings described in the Resolution section above, under "Provisioning VDB (from dSource or another VDB)"