StarQuest: keeping data in motion

 

Stelo Technical Documents

Tips for the SQDR Plus Db2 LUW Staging Database

Last Update:19 March 2024
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. For information about using Db2 LUW as an SQDR source or destination, see

Additional information can be found in these technical notes

Topics

Default directories for Db2 program files and data

 
Windows
Linux
Program files C:\Program Files\IBM\SQLLIB /opt/ibm/db2/V11.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 as as an admin (see below) by issuing DB2 UPDATE DBM USING SYSADM_GROUP DB2ADMNS (optional), create a Windows group named DB2ADMNS, add the user to the group and to the local Administrators 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.

If the SQDR Plus services are being run as a domain user (e.g. in order to use Integrated Security with a SQL Server source) and you desire to create new staging databases (when creating a new agent), that user should be added to the Windows group named DB2ADMNS (or whatever the DBM configuration parameter is set to; if it is blank, then DB2ADMNS is used) and to the local Administrators group. You can verify the ability of that user to create new databases by opening a db2cmd window as that user (right-click and select Run as a different user and enter the user credentials) and executing db2 create database temp (followed by db2 drop database temp if it succeeds). You may also need to run db2 services as a domain user and/or use db2set to set the Db2 registry setting DB2_GRP_LOOKUP (e.g. DB2_GRP_LOOKUP=LOCAL).

For details see Troubleshooting Flow for DB2 Security authentication issues on Windows platforms.

When the goal is to use Integrated Security with a SQL Server source, you can either

  • Run SQDR Plus services as a domain user (as above).
  • Run SQDR Plus services as Local System Account and add the machine account e.g. MyDomain\MySQDRMachine$ to SQL Server; see SQL Server Authentication.Considerations.

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

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

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.

8.3 Naming Considerations

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 may have problems updating 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

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

Here is another example response file, created on Windows Server 2016, installing Db2 11.5, placing both Program Files and ProgramData on the D: drive, including both English and German languages, and running the Db2 services as a local user db2admin.

Finally, here is another example response file created on Windows Server 2019, installing Db2 11.5.5fp1, and running the Db2 services as a local user db2admin.

Note that the response file includes the password (encrypted) for user db2admin that was specified during the GUI installation. If you want to use a different password for another machine, edit the RSP file and remove the line

ENCRYPTED = DB2.PASSWORD

and replace the encrypted password with a password in plain text:

DB2.PASSWORD = mypassword

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

Choosing the language for installation GUI

to run the Db2 Setup wizard in a language different from the default language on your computer:

On Windows, run setup.exe with the -i parameter, specifying the two-letter language code. e.g.

C> setup -i en

On UNIX, set the LANG environment variable e.g.

# LANG=en_US.UTF-8; export LANG
# ./db2setup

error 2896 executing action EnableTextSearchCA or ValidatePanelCA failed

Symptom: On rare occasions, we have encountered a problem installing Db2 where the following error appears in the Db2 installation log:

error 2896 executing action EnableTextSearchCA

or the installation of Db2 fails with the error ValidatePanelCA failed.

Solution: Create a response file on another system and perform a silent install.

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 and schema being used by a particular Staging Agent, use SQDR Control Center and select the hostname that appears at the top of the tree. A list of Agents and their associated Control DB will appear in the right panel.

In the case of multiple agents sharing a control database, the name of the schema will appear in parentheses e.g. SQDRP1(SQDR2). If no schema name appears, then that agent was the first created for that database and is using the default schema name (SQDR).

You can also 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

Interpreting Db2 error messages

Issue: The agent diagnostics displays Db2 LUW error messages as received from the IBM JCC4 JDBC driver, typically showing SQLCODE and SQLSTATE rather than the actual error text. Here is an example of how to interpret these messages.

Solution: The following error appeared in the diagnostics:

Failed to add incremental subscription at Capture Agent for source table '"MYSCHEMA"."MYTABLE"'.
Snapshot could not be started.
Error: Stored procedure SQDR.ADDSUBSCRIPTION 05.24.20210827 returned error 11.
RemoteException occurred in server thread; nested exception is:
java.rmi.RemoteException: Failed to add subscription:
DB2 SQL Error: SQLCODE=-289, SQLSTATE=57011, SQLERRMC=USERSPACE1, DRIVER=4.28.11 SQLSTATE=57011

  1. Make a note the SQLCODE, SQLSTATE, and SQLERRMC values.
  2. Go to the IBM doc for Db2 LUW https://www.ibm.com/docs/en/db2/11.5
  3. search for 57011
  4. Within the results, look for an entry that mentions SQL0289N:

SQL0289N Unable to allocate new pages in table space tablespace-name.

So we can conclude that Db2 has a problem growing the tablespace USERSPACE1; this is typical of a disk full condition.

Disabling DB2DETAILDEADLOCK event monitor

If you are using an older Agent and db2diag.log contains messages that indicate the DB2EVENT\db2detaildeadlock queue is full, we recommend disabling the DB2DETAILDEADLOCK event monitor. This is already disabled in agents created with SQDR Plus 4.96 (January 2018) and later.

Issue the following Db2 commands to modify the database (SQDRPn) indicated in the db2diag.log:

db2 connect to SQDRPn user <user> password <password>
db2 "set event monitor DB2DETAILDEADLOCK state 0"
db2 "drop event monitor DB2DETAILDEADLOCK"

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.

Data Studio Web Console has been deprecated; some of its functionality is now part of Db2 Management Console (the successor to IBM Data Server Manager).

 

(Deprecated) 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.

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 reinstalling Db2:

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 situation may be 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, but the database was not re-cataloged because it was not required by SQDR Plus.

Solution:

Catalog the database and then drop it:

First determine the database directory (i.e. where logs are stored - this is the drive that is displayed for other cataloged databases when you run db2 list db directory). In the following example, the database directory is E:

db2 list db directory on E:
Note that the database in question appears in the output.

Database alias = SQDRP9
Database name = SQDRP9
Database directory = SQL00019
Database release level = 15.00
Comment =
Directory entry type = Home
Catalog database partition number = 0
Database member number = 0

Then catalog and drop the database:
db2 catalog db SQDRP9 on E:
db2 terminate
db2 drop database SQDRP9

If the drop database command fails with the error
SQL1031N The database directory cannot be found on the indicated file system. SQLSTATE=58031

and attempts to connect to the database fail with:
db2 connect to SQDRP19
SQL1042C An unexpected system error occurred. SQLSTATE=58004

then an admin may have deleted the database directory from the filesystem (SQL00019 in the above example). In this case, recreate it by copying the database directory of another database (e.g. copy SQL00018 and name the copy SQL0019) - since our goal is dropping the database, we don't care about the contents. At this point, you should be able to drop the database.

 

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

Unable to create new agent "SQDRPn not found" error

When creating a new agent on a test system where agents had been frequently created and deleted, the following error was encountered, even though SQDRP0 did not exist and was not cataloged on the system. The command db2 create database SQDRP0 (followed by db2 drop database SQDRP0) runs successfully.

addAgent Failed to add Agent
Cannot enumerate existing databases: java.rmi.RemoteException
Cannot create database : SQDRP0. SQL30061N The database alias or database name "SQDRP0 " was not found at the remote node.

Solution: Restart the SQDR Launch Agent service.

Unable to create agent due to Security Policy (ERRORCODE=-4214; SQLSTATE=28000 - "UserID Revoked")

Symptom

When creating an agent on a new installation of SQDR Plus, the following error appeared:

Request failed.
An error occurred. Unable to validate connection parameters.
...
java.rmi.RemoteExceptionjava.rmi.RemoteException
Cannot update database procedure Registration:
com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException[jcc]
....
Connection authorization failure occurred.
Reason: User ID revoked. ERRORCODE=-4214; SQLSTATE=28000

This error refers to using the local Windows user sqdr to connect to the local Db2 LUW staging database (note the mention of the IBM JCC JDBC driver). However, when we examined the user in Computer Management, it does not show as disabled and the userID appears functional.

A slightly different error occurred when trying to connect from a Db2 command window:

C> db2 connect to SQDRP0 user sqdr
Enter current password for sqdr:
SQL30082N Security processing failed with reason "19" ("USERID DISABLED or RESTRICTED"). SQLSTATE=08001

The same error occurred when using the local user db2admin.

However, connecting from a Db2 command window running as the user sqdr (but not specifying user on the command line) did succeed.

Solution:

The IBM notes referenced below led us to examining the Windows Local Policies:

  1. Click on Start->Run from the Windows start menu
  2. Type secpol.msc
  3. Go to Local Policies->User Rights Assignments.
  4. Double-click on Access this computer from network

The contents of this value looked OK.

However, when we examined Deny Access to this computer from the network. In most cases, this property is empty, but here it contained Local account and member of Administrators group

SQDR uses DRDA (network connections) to communicate with Db2.

The Windows user sqdr does not typically need to be a member of the local Administrators group (unless it is being used for other purposes such as running services), but in this case it had been added to the Administrators group.

After removing sqdr from the local Administrators group, connectivity to Db2 began working as expected.

Note that db2admin needs to be a member of the local Administrators group, so on this system it may experience problems connecting to Db2.

Another option wouild be to use domain users rather than local users.

IBM References

ValidatePanelCA failed Error

In rare cases installation of Db2 will fail with the error ValidatePanelCA failed. Workaround: perform a silent install with a response file.

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

Failure to update Db2 with the new system name may result in a misleading error message "Db2 detected a multiple partition instance" when attempting to update Db2 to a new release or fixpack level.

Transaction log for the database is full

Symptom

The following error occurs for a control database used by SQDR or SQDR Plus:

ODBC message: SQLSTATE 57011, native error -964, [StarSQL][StarSQL ODBC Driver][DB2 Universal DataBase]The transaction log for the database is full.

For example, this error might appear in the Application Event log for the SQDRC database when starting the SQDR service if IR_KEYLOG logging has been enabled for a period of time (we recommend enabling IR_KEYLOG logging only for debugging purposes; it should not be left running):

Initialization of incremental support failed (retrying). Error: ODBC message: SQLSTATE 57011, native error -964, [StarSQL][StarSQL ODBC Driver][DB2 Universal DataBase]The transaction log for the database is full.

Service interface EnumerateGroups failed. Initialization of incremental support failed.

A similar error may occur for the SQDRPn staging databases used by SQDR Plus agents.

Solution

In a db2cmd window, Issue the command

db2 get db cfg for <database>

and examine the following values:

Log file size (4KB) (LOGFILSIZ) = 4096
Number of primary log files (LOGPRIMARY) = 56
Number of secondary log files (LOGSECOND) = 200

You can either increase the number of log files, or increase the Log file size.

In the above case, the number of logs (56 primary + 200 secondary) is already at the maximum of 256, so we chose to double the size of the logs from 4096 to 8192. Either method will increase the disk space usage.

open a db2cmd window as user .\db2admin

for SQDRC:
DB2 UPDATE DB CFG FOR SQDRC USING LOGFILSIZ 8192
Stop the SQDRSVC service (and disconnect from Data Studio) and confirm there are no active connections to SQDRC.
DB2 DEACTIVATE DB SQDRC
DB2 ACTIVATE DB SQDRC

for SQDRPn, the actions are similar, but stop the relevant agent instead of stopping SQDRSVC.

SQL20249N (package needs rebinding) or SQL206/SQL207 when creating a new agent database

Symptom:

One or more of the following errors appeared in the Agent Diagnostics or the Capture Agent wrapper.log when creating or starting a new agent (with a new staging database)

SEVERE
DB2 SQL Error: SQLCODE=-206, SQLSTATE=-42703, SQLERRMC=RD.JRNTS

SEVERE
DB2 SQL Error: SQLCODE=-204, SQLSTATE=-42704, SQLERRMC=SQDR.SQ_LOGMAP

Catalog.initialize: Error in monitor thread, will retry
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-20249, SQLSTATE= , SQLERRMC=SQDR.P1522331423, DRIVER=4.27.25

And a similar error appeared when attempting to create an incremental subscription:

SQL20249N The statement was not processed because the package named SQDR.P1522331423 needs to be explicitly rebound.

Explanation:

This error is due to the staging database being at a newer level than that of the installed version of Db2 LUW. In this case, the new agent was created using SQDR Plus 5.24, whose database templates were created with Db2 11.5.5fp1, but the system was running Db2 11.5.4.

Solution:

Either:

  • Update Db2 to the recommended version (11.5.5fp1 at the time of this writing)

or

  • Delete and recreate the new agent, choosing an existing control database from the dropdown on the second panel, instead of creating a <New Control DB>. SQDR Plus will create a new schema in the existing database.

SQLCODE=-443, SQLSTATE=38553 Db2 LUW Error after a Db2 update

Symptom:

After updating Db2 LUW to certain versions (e.g. from 11.5.4 to 11.5.5fp1 or from 11.5.7 to 11.5.9 CSB 38303), some agents were failing with the error on a catalog call:

"SQLCODE=-443, SQLSTATE=38553, SQLERRMC=GETTYPEINFO;SYSIBM:CLI:-805"

Contact Stelo support for the current recommended version of Db2 LUW.

Solution:

Re-bind the db2schema.bnd file on the Db2 LUW staging database:

  1. Identify the staging database of interest in SQDR Manager by selecting the top level - i.e. select the hostname at the top of the tree - and examine the ControlDB column in the list of Agents.
  2. From the Start Menu, launch the DB2 program "Command Window - Administrator"
  3. Navigate to the BND folder e.g. C:\Program Files\IBM\SQLLIB\bnd
  4. Type the following commands, replacing SQDRPn with the name of your database and (if necessary) db2admin with the name of a user with sufficient Db2 authorities to rebind packages:

    db2 connect to SQDRPn
    or
    db2 connect to SQDRPn user db2admin
    db2 bind db2schema.bnd blocking all grant public sqlerror continue
    db2 terminate

See the IBM tech doc for a slightly different issue but with the same solution:
SQLCODE=-443, SQLSTATE=38553, SQLERRMC=SYSIBM.SQLPRIMARYKEYS;PRIMARYKEYS;SYSIBM:CLI:-727" when using database conversion


Backup of Control Database (SQDRC)

We recommend backing up the control database used by the SQDR service (typically called SQDRC on Db2 or ControlDB on SQL Server) to preserve information configured by Data Replicator Manager, including subscriptions and column mapping information.

  • Be sure that Data Replicator Manager is not running.
  • Stop the SQDR service (SQDRSVC).
  • Backup the database.

SQL Server:
In SQL Server Management Studio, expand databases, right-click on ControlDB, and select Tasks/Backup.

Db2:

From a Db2 command prompt:

  1. Make sure that are no active connections to SQDRC
    db2 list database application
  2. Deactivate SQDRC
    db2 deactivate database SQDRC
  3. Backup the database:
    db2 backup db SQDRC to C:\temp compress

If you fail to deactivate SQDRC, you will get the following error:

SQL1035N The operation failed because the specified database cannot be connected to in the mode requested. SQLSTATE=57019

These operations can also be performed with IBM Data Studio.

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.