StarQuest Technical Documents

SQDR Plus: Detecting and Cleaning up Unused Db2 Databases

Last Update: 8 June 2020
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL037

Abstract

In the four tier StarQuest Data Replication architecture, SQDR Plus runs on Tier 2 and uses a local Db2 (IBM DB2 for Linux, UNIX & Windows aka DB2 LUW) database as the control and staging database for each Staging Agent; i.e. a new local Db2 database with the name SQDRPn (where n is a digit) is created for each source system that you will be replicating from.

When an agent is deleted (see Deleting an SQDR Plus Staging Agent), the SQDR Control Center will attempt to drop the associated Db2 database; however this operation may fail if the database is in use. In addition, sometimes creation of a new agent fails after the creation of the associated Db2 database. Over the course of time, a number of unused databases may accumulate.

This technical document describes a script that can be used to detect unused databases and generate a script to drop these databases. The script is provided as a Windows batch file; a similar shell script can be created to perform the same function on Linux.

Solution

1. Download the SQDRPn cleanup script.
2. Unzip the zip archive into a directory in which you have write access, as the script creates temporary files.
3. Edit SQDRPn_cleanup.bat. You may need to change the locations of SQDR Plus, DB2, or StarQuest ProgramData or the Derby password.
4. Run SQDRPn_cleanup.bat from a db2cmd DB2 command window. This will display a list of databases that do not appear to be in use by SQDR Plus, and generate a batch file dropdb.bat that can be run to drop the listed databases. If no unused databases are detected, the output will be blank.

If there is a problem connecting to the Derby database - e.g. bad user/password, or Derby service not running -, an error message will be displayed. The rest of the output (listing all databases) should be ignored.

Before running dropdb.bat, take the following precautions, as dropping a database of an existing agent will make that agent unusable.

The script looks for any database that contains the string SQDRP; if you have a database with such a name used for a different purpose (e.g. MYSQDRPHL), be sure to remove it from the dropdb.bat script.

Be sure that the database is indeed unused:

• Use DB2 LIST APPLICATIONS to make sure there are no active connections to the database.
• Use a query tool to execute the SQL statement SELECT * FROM SQDR.SQ_PROPERTIES from the SQDRPn database of interest and examine the following values:
• home - The name of a directory in C:\ProgramData\StarQuest\sqdrplus\conf\ that may or may not exist. If it exists, examine its contents and make sure it refers to an unrelated, newer agent.
• Version - the version of SQDR Plus when the database was last used. If this version is much older than the currently installed version of SQDR Plus, it's a sign that the database hasn't been used in a while, unless the agent has been disabled.
• sourceUrl & sourceDriver - the connection information to the source system will provide information as to what agent the database was originally used for.
  

Once you are confident that the databases can be safely dropped, run dropdb.bat from a DB2 command window as a user with sufficient authority to drop databases (SYSADM or SYSCTL). If necessary, deactivate the database with DB2 DEACTIVATE DB SQDRPn, although we suggest further examination to understand why the database was activated before deactivating and dropping it.