Stelo Technical Documents

Deleting an SQDR Plus Staging Agent

Last Update: 22 March 2024
Product: SQDR Plus
Version: 4.50 & later
Article ID: SQV00PL011

Abstract

When you create an SQDR Plus Agent for a Db2 or other source system, file directories and a local Db2 LUW database are created on the Tier 2 (SQDR Plus) system and some set of objects (often a database schema, stored procedures, and userID's) are created on the Tier 1 (source) system. If you delete the Agent using SQDR Control Center, the agent is removed from the Launch Agent's control database, but some of the other objects are not removed. This technical document describes the recommended procedure for deleting an SQDR Plus Agent.

This document covers the following topics:

• PREPARATION AND GATHERING OF INFORMATION
  
• AGENT DELETION ON THE TIER 2 (SQDR PLUS) SYSTEM
  
• CLEANUP OF A DB2 FOR I SOURCE SYSTEM
  
• CLEANUP OF A DB2 FOR LUW SOURCE SYSTEM
  
• CLEANUP OF A MICROSOFT SQL SERVER SOURCE SYSTEM
  
• CLEANUP OF AN ORACLE SOURCE SYSTEM
• CLEANUP OF AN INFORMIX SOURCE SYSTEM
• CLEANUP OF A MYSQL/MARIADB/AURORA SOURCE SYSTEM
• CLEANUP OF A POSTGRESQL SOURCE SYSTEM
• Considerations for Autojournaling and Publication (Db2 for i source)

Solution

PREPARATION AND GATHERING OF INFORMATION

• Use SQDR Control Center to determine if there are any existing SQDR subscriptions. If so, delete them using Data Replicator Manager on the (Tier 3) SQDR client and kill any existing ODBC connections to the source and the staging database, either by selecting the connections in Data Replicator Manager and right-click/Kill, or by (restarting the SQDR Service.
• Use SQDR Control Center to gather the following information; select the system name in the left panel and look at the list of Agents in the right panel:
  • the Agent Home Directory. This will look like one of the following:
    C:\ProgramData\StarQuest\sqdrplus\conf\agt0 (Windows)
    /var/sqdrplus/conf/agt0 (Linux)
• Use SQDR Control Center to gather the following information from the Staging Agent's Configuration Settings; select the agent you want to delete in the left panel and select Configuration Settings in the right panel:
• Identify the local Db2 for LUW (or Derby) control database (e.g. SQDRP0), which is part of the controlDbUrl:

jdbc:db2://localhost:50000/SQDRP0:driverType=4;deferPrepares=false;

• Identify the sourceDbHost and sourceDbUrl for the source database
  i Host: jdbc:as400://myas400;prompt=false
  LUW host: jdbc:db2://myhost:50000/MYRDB:driverType=4;deferPrepares=false;
• Identify the sourceDbSchema (e.g. SQDR)
  
• Identify the sourceUserId (e.g. SQDR for Db2 for i; db2inst1 for Db2 for LUW)
  

• (Db2 for i source only): log on with a terminal session to determine privileged source user ID (e.g. SQDRADM) by examining the owner of the journal reader service program:

  DSPOBJAUT SQDR/RJRNLAPI *SRVPGM

AGENT DELETION ON THE TIER 2 (SQDR PLUS) SYSTEM

• Using SQDR Control Center, select the Agent and select Stop Agent from the Database dropdown menu
• From a Db2 command line, use LIST APPLICATION to verify that there no connections to the local control database (e.g. SQDRP0). It may be necessary to stop and restart the SQDR Jetty service from the Services control panel (Windows) or /etc/init.d (Linux) to terminate Db2 connections from the SQDR Control Center.
• Using SQDR Control Center, select the Agent and select Delete Database ; this removes the Agent from the Launch Agent's control database, and will attempt to delete the local control database and agent Home Directory.
• Delete the local control database if necessary:

For Db2 LUW control database:

• Close the browser connection to SQDR Control Center
• From the Services control panel (Windows) or /etc/init.d (Linux), stop the SQDR Jetty service.
• In a db2cmd window as an authorized user, drop the local control database identified above - e.g.:

  db2 DROP DATABASE SQDRP0

• If the database does not exist, then it has already been dropped when you selected Delete Database above.
• From the Services control panel or /etc/init.d, start the SQDR Jetty service.
  

For Derby control database:

• From the Services control panel (Windows) or /etc/init.d (Linux), stop all three SQDR Plus services (Agent, Jetty, and Derby)
• Navigate to the Derby working directory e.g.
  

C:\ProgramData\StarQuest\sqdrplus\derby (Windows)
/var/sqdrplus/derby (Linux)

• Delete the directory that represents the database (e.g. SQDRP0).
• Restart the three SQDR Plus service

• If it hasn't already been deleted by Delete Database, delete the agent Home Directory identified above (e.g. C:\ProgramData\StarQuest\sqdrplus\conf\agt0). Note that 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.

CLEANUP OF A DB2 FOR I SOURCE SYSTEM

• Log on with a terminal session as a user with SYSADM or SECOFR authority.
• If necessary, unregister the exit program:
  • Enter the command WRKREGINF.
  • Locate the entry for QIBM_QJO_DLT_JRNRCV (usually on the 2nd page) and select 8=Work with exit programs.
  • Locate the entry related to your agent and remove it with option 4.
• Issue the following command to modify your current job to send a default response in response to confirmation inquiries regarding deletion of unsaved journal receivers:

  CHGJOB INQMSGRPY(*DFT)

• If you configured Publications in SQDR Plus or subscribed to tables that required autojournaling, review Considerations for Autojournaling and Publication before continuing to the next step.
  
• If userIDs ( the sourceUserId and the privileged source userID) were created specifically for SQDR Plus, either manually or automatically when the agent was added in SQDR Plus, delete those userIDs:

  DLTUSRPRF USRPRF(SQDRJ) OWNOBJOPT(*DLT)
  DLTUSRPRF USRPRF(SQDR) OWNOBJOPT(*DLT)

Specifying OWNOBJOPT(*DLT) deletes all objects owned by these users, including the schema on the source. If the schema has not been deleted, examine the remaining contents and handle those objects - e.g. a journal receiver that is still in use by the journal for an autojournaled table, or a view that needs to be deleted before removing a table. After handling the remaining objects, delete the library with DLTLIB SQDR.

• If you wish to preserve the user ID's (i.e. they were not created specifically for SQDR Plus), delete only the schema, preserving the userID's.

  DLTLIB SQDR

• Using any ad-hoc SQL tool (e.g. STRSQL, odbctest), issue the following SQL:

  DROP PROCEDURE SQDR.TABLEINFO
  DROP PROCEDURE SQDR.QCMDEXC

• If SQDR Plus was configured to use remote journaling, clean up the userID and schema (e.g. SQDR) that was created on the secondary IBM i system.

CLEANUP OF A DB2 FOR LUW SOURCE SYSTEM

• If the Stelo exit program is being used (not typical), decide whether to leave the exit program in place, revert to circular logging, use LOGRETAIN to retain archived logs, or use another mechanism for archive logging.
• If you are using the Log Reader Stored Procedure (recommended), delete the binary from the function directory and drop the stored procedure.
• Delete the SQDR schema in the source database by creating an SQL script cleanup.sql containing the following. Substitute your sourceDbSchema for SQDR and configure the connect statement for your environment:

  connect to <RDB> user <instance-owner> using <password> ;
  drop table sqdr.sq_baselines ;
  drop table sqdr.sq_catalog ;
  drop table sqdr.sq_properties ;
  drop table sqdr.sq_readers ;
  drop procedure sqdr.tableinfo ;
  drop table sqdr.sq4711temp ;
  drop schema sqdr restrict ;

• Enter the following in a db2cmd window on the source system to run the SQL script:

  db2 -tf cleanup.sql

• If the DROP SCHEMA statement fails because it is not empty, handle the remaining contents of the schema.

CLEANUP OF A MICROSOFT SQL SERVER SOURCE SYSTEM

Using Change Tracking (CT):

During the setup and operation of SQDR Plus, the database and all subscribed tables are altered to use CHANGE TRACKING. If these objects were previously not being tracked, you can disable CHANGE TRACKING with:

ALTER DATABASE MYDATABASE SET CHANGE_TRACKING = OFF
ALTER TABLE MySchema.MyTable DISABLE CHANGE_TRACKING;

Using Change Data Capture (CDC):

See SQL Server & Change Data Capture (CDC): Reverting CDC properties.

For both CT & CDC:

Drop the tables and schema used by SQDR:
drop table sqdr.sq_baselines ;
drop table sqdr.sq_catalog ;
drop table sqdr.sq_properties ;
drop table sqdr.sq_readers ;
drop procedure sqdr.tableinfo ;
drop table sqdr.sq4711temp ;
drop schema sqdr restrict ;

CLEANUP OF AN ORACLE SOURCE SYSTEM

If you are using the XStream support of SQDR Plus, stop and drop the outbound server and delete the xstream_tbs tablespace and XSTRMADMIN user created as part of SQDR configuration. Refer to the cleanup scripts included in the XStream setup scripts. Rever the system parameter enable_goldengate_replication if this was set as part of SQDR configuration (i.e. you have no other applications using this function).

Delete the SQDR user and its tables and schema:

DROP USER SQDR CASCADE;

If you enabled supplemental logging and archive logging for the purpose of using SQDR Plus, you may want to disable these settings:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
ALTER DATABASE NOARCHIVELOG;

CLEANUP OF AN INFORMIX SOURCE SYSTEM

Delete the SQDR schema and its contents.

If you enabled logging of the database for the purpose of using SQDR Plus, you may want to disable logging with the ontape command.

If you created the syscdcv1 Change Data Capture database for the purpose of using SQDR Plus, you may want to drop it.

CLEANUP OF A MYSQL/MARIADB/AURORA SOURCE SYSTEM

Contact Stelo support for advice.

CLEANUP OF A POSTGRESQL SOURCE SYSTEM

• Drop inactive replication slots. Failure to do so could result in a storage shortage on the source system.
• Delete the schema created by SQDR.
• if a user was created for the purpose of SQDR, you may delete it.
• If you enabled wal_level = logical for the purpose of SQDR, you may want to revert the change; the mechanism will differ for cloud databases compared to on-prem systems.

See the Quick Start Guide and the PostgreSQL Resource Utilization tech doc for details of objects created on the source system for the purpose of SQDR.

Considerations for Autojournaling and Publication (Db2 for i source)

If you configured Publications in SQDR Plus or subscribed to tables that required autojournaling, SQDR Plus issued a GRANT *USE ON <table> to the sourceUserId (e.g. SQDR). If you choose to not remove this user, review those grants and revoke authorities if desired.

When a non-journaled physical file is selected for incremental replication, the SQDR Plus Staging Agent creates a new library in the same ASP (auxiliary storage pool) as the physical file. This library is named <agentSchema><NN>, where agentSchema is the name of the Agent schema and userid (default SQDR) and NN is the ASP number. For example, if the Capture Agent schema is SQDR and the physical file is located in ASP 3, a library named SQDR03 will be created in ASP 3. This library is used to store journals. Journal receivers for the journal will be created in the Agent schema (e.g. SQDR).

If the subscriptions are removed using SQDR and SQDR Plus, SQDR Plus modifies the table to not be journaled, and you may now delete the journal receivers (in the agent schema) and the journal library (<agentSchema><ASP#>). However, if you are unable to unsubscribe cleanly, you may need to perform additional steps to complete the cleanup of autojournaling.

To determine if a journal is associated with a table:

WRKJRNA JRN(SQDR01/SQ01)
F13=Display Journaled Files

To display the journal receiver attached to the journal:

WRKJRNA JRN(SQDR01/SQ01)
F17=Display attached receiver attributes

The journal receiver will be located in the agent schema, and should be detached from the journal before the schema can be deleted.

To determine if a table is being journaled and to identify the journal being used, use DSPFD FILE(MYLIB/MYTAB) and look for:

File is currently journaled . . . . . . . . : No
Current or last journal . . . . . . . . . . : SQ01
Library . . . . . . . . . . . . . . . . . : SQDR01