StarQuest Technical Documents

Tips for the SQDR Plus DB2 LUW Staging Database

Last Update: 1 April 2019
Product: SQDR Plus
Version: 4.2 & later
Article ID: SQV00PL015

 

Abstract

In the four tier StarQuest Data Replication architecture, SQDR Plus runs on Tier 2 and uses a local IBM DB2 for Linux, UNIX & Windows (DB2 LUW) database as the control and staging database for each Staging Agent.

DB2 LUW is designed to be installed and run with minimal administration. In normal operation, the SQDR Plus user has no direct interaction with the DB2 LUW database software, which should be considered as embedded and for exclusive use of the replication software. However, conditions may occur that require some interaction with DB2. This technical document provides guidance in working with DB2 LUW as used by SQDR Plus.

Additional information can be found in the technical note Updating from IBM DB2 Express-C to DB2 Express.

Topics

Default directories for DB2 program files and data

 
Windows
Linux
Program files C:\Program Files\IBM\SQLLIB /opt/ibm/db2/V10.5
Volatile files C:\ProgramData\IBM\DB2COPY1 /home/db2inst1/sqllib
db2diag.log C:\ProgramData\IBM\DB2\DB2COPY1\DB2 /home/db2inst1/sqllib/db2dump
Table spaces C:\DB2\NODE0000\database /home/db2inst1/NODE0000/database
DB2 logs and other files C:\DB2\NODE0000\SQL000n /home/db2inst1/NODE0000/SQL000n

Notes:

  • The directory C:\ProgramData may be hidden on Windows; use Tools (or Organize)/Folder Options/View/Hidden files and select Folders/Show hidden files and folders to view these folders in Explorer.
  • During installation of SQDR Plus, the user is prompted to select different drives for the location of DB2 data files (aka table spaces) and DB2 logs; this will affect the locations of the last two rows of this table.

DB2 command environments

Windows:

Run db2cmd or select DB2 Command Window from the DB2 Program Group. Some operations such as db2stop/db2start need to run from an elevated command window; select DB2 Command Window - Administrator from the DB2 Program Group..

Note that if you get a message that a user is not authorized to perform a DB2 operation, that usually refers to DB2 authorization, and running from an elevated command window will not help. Instead, either

  • Configure DB2 to recognize the user by issuing DB2 UPDATE DBM USING SYSADM_GROUP DB2SYSADM, create a Windows group named DB2SYSADM, add the user to the group and restart DB2. Note that this method of authorization is complicated by the use of domain users and may affect the ability of SQDR Plus to create new agents unless you add the Local System Account (NT AUTHORITY\SYSTEM) to the group.
  • (recommended) Run the db2cmd environment as another user (e.g. db2admin) by holding down the Shift key when you select DB2 Command Window from the DB2 Program Group and choosing Run as a different user.

Linux:

Set up the DB2 command environment by one of the following methods.

  • Log on as the DB2 instance owner (db2inst1)
  • su - db2inst1
  • Set up the command environment by sourcing the file appropriate for your shell environment:
    $ . /home/db2inst1/sqllib/db2profile (sh/ksh/bash)
    % source ~db2inst1/sqllib/db2cshrc (csh)

If you choose the latter method, you may encounter authorization issues as described above in the Windows section.

Installation issues

General Help

If you encounter problems installing DB2, the DB2 installation log can be found in the DB2LOG subdirectory of your Documents folder (Windows) or in /tmp (Linux) - e.g.

Windows: C:\Users\MYID\Documents\DB2LOG\DB2-EXP-<date>.log
Linux: /tmp/db2setup.log, db2setup.err, etc.

The computer name of the Windows workstation on which the DB2 installation files are located must be 15 characters or less in length.

 

Symptom: DB2 Launchpad will not show Install options in the left column.

This occurs when the DB2 installer is located in a folder whose name includes spaces, on a drive where 8.3 file names are disabled.

We recommend enabling 8.3 short file names on all volumes before installing DB2, both because of the inability to run the DB2 installer in such a situation, and because if DB2 is installed on a drive where 8.3 file names are disabled, you should use a directory name without spaces (e.g. E:\DB2\SQLLIB rather than E:\Program Files\IBM\SQLLIB) and you will not be able to update it.

If DB2 is installed to a drive where 8.3 file names are disabled, in a directory name with spaces (e.g. E:\Program Files\IBM\SQLLIB), you will see the following symptom when starting a DB2 command window:

'E:\Program' is not recognized as an internal or external command, operable program or batch file.

To determine if 8.3 file names are enabled on a particular volume, enter the following from an elevated command window:

fsutil 8dot3name query D:

To enable 8.3 file names on a global basis, enter the following from an elevated command window, and recreate any folder names that contain spaces. You may need to uninstall all programs that use that drive and recreate the <drive>:\Program Files\ directory.

fsutil.exe behavior set disable8dot3 0

To enable 8.3 file names on a single disk volume, enter the following from an elevated command window, and recreate any folder names that contain spaces:

fsutil 8dot3name set E: 1

You may be able to avoid the need to reinstall DB2 and other programs using the drive by adding the short name to Program Files::

fsutil file setshortname "E:\Program Files" PROGRA~1

For more information, see

DB2 Authority Issues on Windows

In a default installation, the DB2 services run as a local user, usually called db2admin, and the SQDR Plus services run as Local System Account. No additional configuration is required, as the Local System Account has db2adm authority in the DB2 instance; in particular, the SQDR Agent service, running as Local System Account, has the necessary authority to create a Db2 staging database when creating a new Agent.

However, if you are using Integrated Security and a domain user to access a SQL Server source, additional considerations apply. See SQL Server Integrated Security (Windows Authentication) Issues for details.

Limiting the size of db2diag.log

The DB2 log file db2diag.log can grow to a large size if it is not actively managed; we recommend configuring automated rotation and size limitation of the db2diag.log:

db2 UPDATE DBM CFG USING DIAGSIZE 2
db2stop
db2start

Troubleshooting the DB2 control database used by the Staging Agent

Each Staging Agent uses a local IBM DB2 LUW database as its control database. We recommend examining the DB2 log file db2diag.log for errors and tuning suggestions.

To determine the local IBM DB2 LUW database being used by a particular Staging Agent, use SQDR Control Center to examine the Configuration Settings for the agent of interest. Examine the controlDbUrl property: the database name will be SQDRPn, where n is a number beginning with 0 - e.g. SQDRP0, SQDRP1.

The db2diag.log file is typically located in C:\ProgramData\IBM\DB2\DB2COPY1\DB2 (Windows), and /home/db2inst1/sqllib/db2dump (Linux). If the db2diag.log file has grown too large to be easily examined, use db2diag -A to rotate the log.

Examine the db2diag.log for errors and tuning suggestions related to the control database and follow the recommended solution.

For example, an SQDR Plus installation was experiencing unusual problems with SQDR Plus. The following error was observed in db2diag.log:

2013-05-15-23.28.46.463001 Instance:DB2 Node:000
PID:2128(db2syscs.exe) TID:1772 Appid:127.0.0.1.55299.130515015127
catcache support sqlrlc_check_available_memory Probe:100 Database:SQDRP0

ADM4000W A catalog cache overflow condition has occurred. There is no error but this indicates that the catalog cache has exceeded the configured maximum size. If this condition persists, you may want to adjust the CATALOGCACHE_SZ DB configuration parameter.

Adjusting the value of CATALOGCACHE_SZ resolved the SQDR Plus problem:

DB2 CONNECT TO SQDRP0;
DB2 UPDATE DB CFG USING CATALOGCACHE_SZ 350 ;
DB2 CONNECT RESET;

Another example of an error revealed by db2diag.log was an incorrect value for the JDK_PATH DBM configuration parameter, resulting in calls to Java stored procedures failing because DB2 was unable to start a JVM:

2014-01-30-13.19.08.514000-300 I796850F515 LEVEL: Warning
PID : 5272 TID : 5276 PROC : db2fmp64.exe
INSTANCE: DB2 NODE : 000
HOSTNAME: HOSQL
EDUID : 5276
FUNCTION: DB2 UDB, oper system services, sqloJVMstart, probe:15
MESSAGE : jdk_path dbm cfg parameter not a directory
DATA #1 : Hexdump, 18 bytes
0x000000A3DF5F7940 : 443A 5C53 514C 4C49 425C 6A61 7661 5C6A
D:\SQLLIB\java\j
0x000000A3DF5F7950 : 646B dk

This was resolved by setting the correct value for JDK_PATH:

db2 update dbm cfg using JDK_PATH C:\PROGRA~1\IBM\SQLLIB\java\jdk
db2stop
db2start

Moving the DB2 control database used by the Staging Agent

We recommend that the local IBM DB2 LUW database used as a control database by the Staging Agent be located on a high-speed disk drive (e.g. SSD) with sufficient storage to handle the staged data. The Quick Start Guide for SQDR Plus includes instructions for configuring SQDR Plus so that the local DB2 control database will be created on the disk of your choice when you create an agent.

Use the following procedure to move an existing control database to another disk (either for speed or capacity):

  1. On the Tier 3 system (running the SQDR service), Use Data Replicator Manager to pause the I/R group and delete any connections to the staging system.

On the Tier 2 system (running SQDR Plus):

  1. Verify that you have adequate available disk space on both the target location and the temporary directory where you will store a backup file. Be sure that there are no older backup images of your database in the temporary directory. In our example, we are using the D: drive for both.
  2. Identify the DB2 database to be moved (e.g. SQDRP0) by examining the controlDbUrl keyword in the Agent configuration.
  3. Stop the SQDR Plus jetty & launch agent (capagent) services from the Services control panel (Windows) or /etc/init.d (Linux).
  4. Use the db2 list applications command to verify that there are no active connections to DB2.
  5. Back up the db2 database to a temporary location (the compress argument is optional), drop the original database, and restore it using the ON keyword:

db2 backup database SQDRP0 to D:\temp compress
db2 drop database SQDRP0
db2 restore database SQDRP0 from D:\temp ON D:

  1. Connect to the database to verify that it is functional:

db2 connect to SQDRPO user SQDR

  1. Start the SQDR Plus jetty & launch agent (capagent) services from the Services control panel (Windows) or /etc/init.d (Linux).
  2. Use a browser to connect to SQDR Control Center and verify that the agent is running; its icon should be green.
  3. On the Tier 3 system (running the SQDR service), use Data Replicator Manager to resume the I/R group.

Error restoring the control database

Symptom: While performing the procedure described above (Moving the DB2 control database used by the Staging Agent), a user encountered the following error when restoring the database to the new location:

SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress, or has terminated the specified request because of an error or a forced interrupt. SQLSTATE=55032

Solution: The database was still cataloged on the LDAP server. After uncataloging the database, the restore command succeeded.

Specifying the location of DB2 database files

We recommend that the database file of the local IBM DB2 LUW database used as a control database by the Staging Agent be located on a drive other than the one use for the DB2 log files and isolated from other major disk activity. You can configure this during installation.

To change the location of the databases files for local DB2 LUW databases to be created in the future (i.e. after installation of SQDR Plus but before creation of the control database):

  1. Edit the Launch Agent configuration: On Windows, selecting the Launch Agent configuration from the Program group shortcut and right-click/Run as Administrator. On Linux, enter vi /var/sqdrplus/conf/sqagent.properties.
  2. Modify the value for DB2iTemplateDB_DB2 (IBM i source), UDBTemplateDB_DB2 (DB2 LUW source), MSSQLTemplateDB_DB2 (SQL Server source), or ORCLTemplateDB_DB2 (Oracle source) by appending the clause ON <target-location>. IMPORTANT: use single quotes around the path to the template file if needed. Example: DB2iTemplateDB_DB2='C:/Program Files/StarQuest/sqdrplus/db2' ON D: (Windows) or DB2iTemplateDB_DB2='/opt/StarQuest/sqdrplus/db2' on /disk2/DB2 (Linux). On Linux be sure the instance owner (e.g. db2inst1) has write authority to this directory.
  3. Save the configuration and restart Launch Agent service from the Services control panel (Windows) or by entering /etc/init.d/sqdr-capagent restart (Linux).

 

Specifying the location of DB2 logfiles

We recommend that the DB2 logfiles of the local IBM DB2 LUW database used as a control database by the Staging Agent be located on a drive other than the one use for the database files. You can configure this during installation.

To change the location of the DB2 logfiles for local DB2 LUW databases to be created in the future (i.e. after installation of SQDR Plus but before creation of the control database):

  1. Edit the Launch Agent configuration: On Windows, select Launch Agent configuration from the Program group shortcut, and right-click/Run as Administrator. On Linux, enter vi /var/sqdrplus/conf/sqagent.properties.
  2. Modify the value for DB2iTemplateDB_DB2 (IBM i source), UDBTemplateDB_DB2 (DB2 LUW source), MSSQLTemplateDB_DB2 (SQL Server source), or ORCLTemplateDB_DB2 (Oracle source) by appending the clause DBPATH ON <target-location>. IMPORTANT: use single quotes around the path to the template file if needed. Example: DB2iTemplateDB_DB2='C:/Program Files/StarQuest/sqdrplus/db2' DBPATH ON D: (Windows) or DB2iTemplateDB_DB2='/opt/StarQuest/sqdrplus/db2' dbpath on /disk2/DB2 (Linux). On Linux be sure the instance owner (e.g. db2inst1) has write authority to this directory.
  3. Save the configuration and restart Launch Agent service from the Services control panel (Windows) or by entering /etc/init.d/sqdr-capagent restart (Linux).

To change the location of the DB2 logfiles after the database has been created, ensure that there are no active connections to the database and issue the command:

db2 update db cfg for <database> using newlogpath <full path>

e.g. db2 update db cfg for SQDRP0 using newlogpath D:\DB2Logs

When the next connection is made to the database, the database manager will move the logs to the new location specified by logpath.

Configuring Notification Alerts

If the disk drive containing the staging database runs out of space, SQDR Plus will stop operating. This may occur for a number of reasons:

  • The Tier 3 SQDR client has been halted or is otherwise unable to retrieve changes from the staging database, and backlog of changes has accumulated.
  • The staging agent has been configured with a very large value for pruneRetention. This value specifies the minimum time (specified in hours) that data should be retained in the staging database for use by the Replay function of SQDR. Default value is 0 (do not retain staging data after it has been retrieved by the SQDR client).
  • The staging agent has been configured with a very large value for pruneInterval (default value is 30 minutes).
  • Another application is consuming disk space.

The Health Monitor function of the IBM Data Studio Web Console can be used to monitor disk space utilization and other conditions that might affect DB2 operation. You can download Data Studio Web Console from http://www.ibm.com/developerworks/downloads/im/data/. It can be installed either on the machine running DB2, or another machine with access to the machine running DB2. If you are using another machine, you may need to allow exceptions for ports 50000 and 22 in the firewall of the DB2 machine. The Web Console is accessed with a browser, so it in turn can be accessed by remote machines that have access to the machine running the Web Console, which should have a firewall exception for the listening port (default 11086).

  1. Install Data Studio Web Console.
  2. Specify parameters such as the listening port (default 11086) and an administrator user and password using the configuration application that runs at the end of installation.
  3. Be sure that the Data Studio web console service (IBMDSServer41) is running; you can control it with either the Services control panel or with the Start/Stop Data Studio web console server shortcuts in the Data Studio program group.
  4. Connect to the appropriate URL (e.g. http://myhost.isqv.com:11086/datatools) using a Flash-enabled browser (for example, Google Chrome). You can also use a shortcut in the Data Studio program group on the machine with Data Studio is installed.
  5. Add a database connection to the staging database (e.g. SQDRP0) by either selecting Add database connections from Task Launcher or selecting Databases from the Open button and select the Plus sign. Enter the following (this assumes that Data Studio Web Console is installed on the same machine as DB2):

    Database connection name: SQDRP0 (or a name of your choosing)
    Database name: SQDRP0
    Host name: 127.0.0.1
    Port number: 50000
    User ID & Password with sufficient authority to the database

  6. Open Alert Notification from the Open button.
  7. Select the database from the dropdown.
  8. Select the Configure Email and SNMP services button.
  9. Select Email service and enter your SMTP host name and port and (if necessary) SMTP authentication.
  10. Exit back to the Alert Notification screen.
  11. Select Add Alert Notification.
  12. Next to Alert Types: choose the Select.. button, and select File System Utilization from the Available Alert Types.
  13. For severity, choose Warning or critical alerts.
  14. For Email addresses: select the Add button and enter an email address.
  15. Review the other values (e.g. repeat every 15 minutes, start & end times).
  16. Click OK.

This will result in an email sent every fifteen minutes that the file system storing the database files is over 90% full. These are default values and can be modified.

You can also use Data Studio Web Console to configure automatic database maintenance, such as automatic table REORG operations.

Reducing Table Space Storage

The size of the DB2 staging database is not related to the size of any source table; it is related to the number of tables to be monitored and the number of changes to be staged at any given time. However, a backlog caused by the tier 3 SQDR client not fetching updates may cause the database to grow to a large size. The disk space usage will not shrink, even after the data has been retrieved from the staging database and SQDR Plus pruning run. In that case, you may need to do the following tasks:

  1. REORG the staging tables - see REORG Hints below for details.
  2. After the REORG is complete, alter the the table space. You can also specify reduction by a percentage.

ALTER TABLESPACE USERSPACE1 REDUCE MAX

Example: The following Windows batch file will reorganize all the tables in the SQDR schema in all the SQDR* databases and issue the ALTER TABLESPACE command:

@ECHO OFF

setlocal enableDelayedExpansion

for /f "tokens=4" %%x in ('db2 list database directory ^| find "Database name" ^| find "SQDR"') do (

db2 connect to %%x >> reorg.log
db2 -x "SELECT 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' LONGLOBDATA;' FROM SYSCAT.TABLES WHERE (type= 'T') AND (tabschema = 'SQDR')" > reorg_sqdr.sql
db2 -tvf reorg_sqdr.sql >> reorg.log
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 disconnect all
)

REORG Hints

You can REORG individual tables using the GUI interface of Data Studio Client or by issuing the REORG command.

REORG SQDR.xxxxxxxx

If you see an Error -289, then there is insufficient space in the table space to do the REORG; specify another table space to use as a temporary location or add storage to the table space:

REORG SQDR.xxxxxxx USE TEMPSPACE1

To view the size of all tables in the SQDR schema, issue the following SQL:

SELECT tabname,TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+
SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+
SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM
SYSIBMADM.ADMINTABINFO where TABSCHEMA='SQDR' group by tabname,tabschema

The following SQL displays the largest tables (those > 10000 pages) from SQDR in descending order

SELECT tabname,TABSCHEMA, DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE as TOTAL_SIZE
FROM SYSIBMADM.ADMINTABINFO where ((TABSCHEMA='SQDR') AND (DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE > 10000)) ORDER BY TOTAL_SIZE DESC

To reorganize all the tables in the SQDR schema, issue the following from the DB2 command line environment:

db2 -x "SELECT 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' LONGLOBDATA;' FROM SYSCAT.TABLES WHERE (type= 'T') AND (tabschema = 'SQDR')" > reorg_sqdr.sql
db2 -tvf reorg_sqdr.sql

You can enable automatic REORG using Data Studio Web Console or by issuing the following commands; however, be aware of maintenance window times, as a system-initiated REORG could affect replications in progress - i.e. the maintenance window time should be configured for a period of low or no replication activity.

db2 update db cfg using AUTO_MAINT ON
db2 update db cfg using AUTO_TBL_MAINT ON
db2 update db cfg using AUTO_RUNSTATS ON
db2 update db cfg using AUTO_REORG ON

Adding Storage to a Table Space

The SQDR Plus staging tables are stored in a table space named USERSPACE1, which uses Automatic Storage (note that the DB2 utilities show the type of storage as DMS: Database managed space). The table space will automatically grow to use as much disk space as is available on the assigned disk. If that is inadequate, you have several options:

  • Use the above procedure Moving the DB2 control database used by the Staging Agent to move the entire database to a larger disk.
  • Depending on your environment, it may be possible to expand the disk (e.g. by increasing the size of a virtual disk in a VMWare environment, or adding additional drives to a Windows dynamic disk).
  • Add additional containers to the storage group (and thus the table space)

This section describes the last option, which is likely to be the quickest and least disruptive method, especially if the additional storage is only needed temporarily.

  1. Determine the name of the storage group (default IBMSTOGROUP) by displaying the storage options of the table space in Data Studio Client or issue the following SQL:

    SELECT SGNAME FROM SYSCAT.STOGROUPS

  2. Issue the following SQL statements to add another disk to the storage group and rebalance the contents of the table space:

    ALTER STOGROUP IBMSTOGROUP ADD 'E:'

    ALTER TABLESPACE USERSPACE1 REBALANCE

You may need to stop and start DB2 If the REBALANCE command fails with this error:

SQL2094W The rebalance of table space "USERSPACE1" either did not add or drop containers, or there was insufficient disk space to create all of the containers. Reason code: "1". SQLSTATE=01690

If you wish to remove the second disk from the STOGROUP (for example, the extra storage was needed temporarily in order to perform maintenance and the above REORG's), issue the following SQL:

ALTER STOGROUP IBMSTOGROUP DROP 'C:'
ALTER TABLESPACE USERSPACE1 REBALANCE

For additional information, refer to the IBM documentation for Altering Automatic Storage Table Spaces.

Performance Suggestions

We recommend configuring DB2 and SQDR Plus so that the DB2 staging database resides on a high performance disk such as SSD or IBM FlashSystems. The IBM redtips paper Faster DB2 Performance with IBM FlashSystem contains details about the performance and the benefits gained from running DB2 on a fast disk, and describes tools that can be used to monitoring latency and I /O wait time such Perfmon (Windows Performance Monitor) and Db2top (Linux).

Performance can often be improved by activating all databases at DB2 startup time rather than waiting for an application to connect. See DB2 Activate Scripts for details.

Scripting Changes

If you have a large number of staging agents (and hence a large number of DB2 LUW staging databases), making changes to the database with Data Studio or ad-hoc SQL tools such as the DB2 command line or odbctest may not be practical.

Below is an example of using a Windows batch file to perform a GRANT on all the staging databases.

In this case, the DB2 service was running as Local System Account, rather than the more typical usage of a local db2admin user, and the 13 databases (SQDRP0 through SQDRP12) were configured for automatic maintenance. Errors in db2diag.log indicated that SYSTEM lacked the authority to perform automatic maintenance. Invoke the batch file with the user (e.g. db2admin) and password information appropriate to your environment - e.g. db2grant db2admin mypassword.

@ECHO OFF
if [%1]==[] goto usage
if [%2]==[] goto usage

setlocal enableDelayedExpansion
for /f "tokens=4" %%x in ('db2 list database directory ^| find "Database name" ^| find "SQDR"') do (
set DB=%%x
 db2 connect to !DB! user %1 using %2
 db2 grant ALL on SYSTOOLS.HMON_ATM_INFO to SYSTEM
 db2 grant ALL on SYSTOOLS.POLICY to SYSTEM
 db2 grant ALL on SYSTOOLS.HMON_COLLECTION to SYSTEM
 db2 disconnect !DB!
)
goto end

:usage
 echo * *
 echo * usage: db2grant.bat [userid] [password] *
 echo * *
 echo * example: C:\temp\db2grant.bat db2admin mypass *
 echo * *
 goto end

:end

Similarly, here is a Linux shell script to recatalog and update a number of staging databases (stored on /disk2/DB2) after uninstalling DB2 Express-C 10.1 and replacing it with DB2 Express 10.5:

for i in C P0 P1 P2 P3
do
 db2 catalog db SQDR${i} on /disk2/DB2
 db2 UPGRADE DATABASE SQDR${i}
done

Unable to create new agent due to uncataloged database

When creating a new agent on a test system where agents had been frequently created and deleted, the following error was encountered:

An error occurred. Unable to validate connection parameters
remoteException Cannot create database: SQDRP9.
SQL1005N The database SQDRP9 already exists in either the local database directory or system database directory.

The commands db2 list db directory and db2 drop database SQDRP9 failed to recognize the existence of SQDRP9. However, it was discovered that SQDRP9 did exist in the database directory (E:\DB2\NODE0000\) but was uncataloged.

This was the result of the following sequence of events: The user deleted an agent but SQDR Plus was not able to drop the database because it was in use by another application. Later, DB2 was removed and reinstalled (to perform an ugrade from DB2 Express-C to DB2 Express), but the database was not re-cataloged because it was not required by SQDR Plus.

Solution:

Catalog the database and then drop it:

db2 list db directory on E:
db2 catalog db SQDRP9 on E:
db2 terminate
db2 drop database SQDRP9

For more details, see the IBM tech note Restore database error with SQL1005N.

Installation on Windows Server 2012R2 and Windows 8.1

DB2 LUW 10.5 fixpak 4 or later is required for Windows Server 2012R2 and 8.1; attempting to install an earlier version of DB2 will fail with the error ValidatePanelCA failed unless you perform a silent install with a response file.

Silent Installation of DB2 with a response file

To run a silent installation, create a response file by performing a normal user-driven installation. For example, this response file was created on Windows Server 2012 (non-R2), installing DB2 Express-C 10.5fp1 to the E: drive and running the DB2 services as Local System Account. The response file is a plain text file and can be customized - for example, to install to a different location. To invoke the installation, place the response file in the DB2 installer directory and run:

C> setup -u PROD_EXPC.rsp -l setup.log

Contact StarQuest Support for a response file for the current recommended version of DB2.

Changing the hostname

If you need to change the name of the system hosting SQDR Plus, see the IBM technical document Changing hostname of the DB2 server for details on reconfiguring DB2 LUW.

When the Agent restarts after the hostname change, it will update the ODBC connection string in the SQDR.SQ_PROPERTIES control table on the source.

In Data Replicator Manager, refresh the Source (i.e. retrieve the new ODBC connection string from SQ_PROPERTIES) by viewing its properties and making any change (e.g. change the notification address, click OK, change it back, and click OK).

DB2 Memory Exhaustion when using Microsoft Hyper-V Dynamic Memory

Symptom: Dynamic Memory is a feature of the Microsoft Hyper-V Hypervisor (Windows Server 2008 R2 SP1 and later) that uses physical memory more efficiently by treating memory as a shared resource that can be reallocated automatically among running virtual machines. Dynamic Memory adjusts the amount of memory available to a virtual machine, based on changes in memory demand and values that you specify.

However, DB2 for LUW (used as the SQDR Plus staging database) configures some of its parameters based on the amount of memory available at startup and may experience memory exhaustion in this environment. If SQDR Plus is running on a Hyper-V virtual machine that has been configured with an inadequate minimum starting memory, the SQDR client may receive the following error when it attempts to fetch staged data from SQDR Plus:

Stored procedure SQDR.GETCHANGE2 04.12.20130905 returned error 11.
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-101,
SQLSTATE=54001, SQLERRMC=null, DRIVER=4.16.53

SQLCODE=-101 and SQLSTATE=54001 indicates that the statement was not processed because a limit such as a memory limit, an SQL limit, or a database limit was reached.

In addition, error messages related to memory exhaustion may appear in db2diag.log.

Solution: Increase the minimum starting memory of the virtual machine. For a 64-bit Windows system, we recommend a minimum of 4gb.

Increasing the Number of Packages in the DB2 for LUW Control Database

Symptom: A customer experienced a large number of rollbacks related to the DB2 for LUW control database used by the tier 2 SQDR Plus system, resulting in a large number of email notifications. This is a rare situation.

The following error appeared in the agent's Diagnostics:

SEVERE: [sqv][Thread-1769][Oct 21, 2013 11:31:09 AM] CaptureAgentLog: ReplicationWorker.run:
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH103 0X5359534C564C3031, DRIVER=3.66.46
...
at com.ibm.db2.jcc.am.Connection.prepareStatementX(Connection.java:2379)
at com.ibm.db2.jcc.am.Connection.prepareStatementX(Connection.java:2326)
at com.ibm.db2.jcc.am.Connection.prepareStatement(Connection.java:909)
at com.starquest.sqdr.capture.as400.RemoteChangePropagator.initialize (RemoteChangePropagator.java:2133)
at com.starquest.sqdr.capture.as400.RemoteChangePropagator.restoreSavedLogPosition (RemoteChangePropagator.java:2104)
at com.starquest.sqdr.capture.ChangePropagator.isIgnoredRollbackTransaction (ChangePropagator.java:760)
at com.starquest.sqdr.capture.as400.RemoteChangePropagator.processOneLogRecord (RemoteChangePropagator.java:542)
at com.starquest.sqdr.capture.ReplicationWorker.run(ReplicationWorker.java:343)

SQLCODE=-805, SQLSTATE=51002 indicates "Package NULLID.SYSLH103 was not found".

Solution:
It was determined that Java garbage collection was failing to close prepared statement handles quickly enough. The customer's issue was resolved by rebinding the DB2 for LUW packages, specifying a larger number of packages (5 packages for each isolation level, instead of the default of 3):

cd C:\Program Files\IBM\SQLLIB\bnd

db2 connect to SQDRP0 user <ADMINUSER> (where ADMINUSER is a userID with sufficient authority to bind packages)

db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 5

In addition, SQDR Plus has been modified to reduce the possibility of encountering this issue again.


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.