StarQuest Technical Documents

Creating a Custom Db2 LUW Staging Database

Last Update: 18 March 2022
Product: SQDR & SQDR Plus
Version: 5.18 and later
Article ID: SQV00PL067

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.

In a typical scenario, the staging database (named SQDRP0, SQDRP1, etc) is created by the Add Agent Wizard by performing a RESTORE DATABASE operation from a template backup image supplied with SQDR Plus. This database uses a code set of UTF-8, which is the default for all recent versions of Db2 LUW and offers compatibility with most databases, both IBM and non-IBM.

However, in certain cases it may be desirable to create the staging database manually. For example, when replicating from an older Db2 LUW source system that uses a code set other than UTF-8, it may be desirable to create a staging database with a matching code set.

This technical document describes a technique for a creating and using a custom staging database, leveraging the support (introduced in SQDR Plus 5.18) for agents to share a staging database by using multiple schemas.

This is an advanced technique and we recommend contacting StarQuest support before using this scenario.

Solution

Summary

• Use the Create Agent Wizard to create a place-holder Agent and its database.
  
• Do not start this Agent; disable it immediately
  
• Drop the database
  
• Create a new database with the same name and desired characteristics
  
• Perform some grants and other operations in the new database
  
• Create a new Agent, selecting the existing database
  
• Start the new Agent and verify operation
  
• Delete the place-holder Agent

For this example, we are going to assume that there is an existing Agent and existing database SQDRP0 that we do not want to disturb. So will be creating a new database SQDRP1.

These steps will be performed using SQDR Control Center or Db2 command line.

Procedure

Create a new place-holder Agent to any host database. It does not need to be the same type of database as the actual host. On the second panel, the dropdown for Control Database should be <New Control DB>.

On completion of creating the Agent, do not start it. Immediately disable it.

Viewing the summary of Agents (selecting hostname displayed at the top of the tree in the left window), verify that the new database is SQDRP1.

Using the Services control panel, restart the SQDR Plus Jetty service (otherwise jetty retains a connection to SQDRP1).

Deactivate & drop SQDRP1:
db2 deactivate db SQDRP1
db2 drop db SQDRP1

Recreate the database with desired characteristics and perform various grants and manipulations:

as user db2admin:
db2 create db SQDRP1 on E: dbpath on L: USING CODESET IBM-1252 TERRITORY US

db2 connect to SQDRP1 user db2admin using <password>
db2 grant secadm on database to SQDR
DB2 GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER SQDR
DB2 GRANT IMPLICIT_SCHEMA ON DATABASE TO USER SQDR
DB2 GRANT BINDADD ON DATABASE TO USER SQDR
db2 commit
db2 disconnect SQDRP1

db2 connect to SQDRP1 user SQDR using <password>
-- instantiate JAR file used by Java stored procedures
db2 CALL sqlj.install_jar( 'file:C:\Program Files\StarQuest\sqdrplus\capagent\UDBProcs.jar', 'CAPTUREPROCS_JAR' )
-- Create a dummy table in the SQDR schema to make sure that the new Agent will use schema SQDR0:
db2 create table SQDR.TAB1 (fld1 int)
db2 commit
db2 disconnect all

Hopefully the place-holder Agent was never started, but if it was, issue the following SQL in the Derby database to ensure that StarSQL packages will be bound when the new Agent first starts:

delete from SQPKGINFO where name=’SQDRP1’;

Create the Agent of interest using the Add Agent Wizard. On the second panel, select the already existing database (SQDRP1) from the dropdown for Control Database.

Complete creation of the Agent.

Examine the Agents summary listing and verify that the new Agent is using SQDRP1(SQDR0).

Start the new Agent.

Examine diagnostics for the Agent and confirm that the necessary tables and stored procedures are created in schema SQDR0.

Examine diagnostics for the Launch Agent and confirm that StarSQL packages were bound in collection STARSQL.

Create some subscriptions.

After verifying operation, you can now delete the place-holder Agent. Because of the existence of the SQDR0 schema, the database will not be dropped.