StarQuest Technical Documents

SQDR Plus V4 Documentation Updates

Last Update: 4 February 2015
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL009

Abstract

This technical document contains documentation updates for the SQDR Plus v4 software. Information will be removed when it is migrated to other areas of the documentation (e.g. the SQDR Control Center Online Help).

The following topics are addressed:

• Pausing SQDR Plus during host maintenance (DB2 for i)
• Password Changes (DB2 for i host schema owner)
• Password Changes (local or domain Windows or Linux user for access to DB2 LUW control database)
• Password Changes (Derby database used by Launch Agent)
• Upgrading from SQDR Plus Classic to SQDR Plus v4

Pausing SQDR Plus during host maintenance (DB2 for i)

To instruct SQDR Plus to stop checking for updates during host maintenance (e.g. nightly backups), issue the following AS/400 commands as a privileged user account:

/* at start of maintenance window */
SNDMSG MSG(PAUSE) TOMSGQ(SQDR/CAQ) MSGTYPE(*INQ) RPYMSGQ(*LIBL/QSYSOPR)

/* at end of maintenance window */
SNDMSG MSG(RESTART) TOMSGQ(SQDR/CAQ) MSGTYPE(*INQ) RPYMSGQ(*LIBL/QSYSOPR)

Password Changes (DB2 for i host schema owner)

The wizard for creating a Staging Agent for a DB2 for i source system ("Add Databases" operation) prompts for a schema name (default SQDR) and password. A schema (collection) and a user are created on the source system, and the credentials are stored (encrypted) in the agent's configuration (sqagent.properties file) . If the password for the DB2 for i user changes, the Staging Agent will be unable to start. The simplest solution is to make sure that the user's password is never expired or changed, but some host systems enforce periodic password changes.

If the agent is already running when the password change occurs, you can use SQDR Control Center to modify the password:

1. Select the Agent from the Databases list
2. Open the Configuration Settings panel
3. Select the text field sourcePassword and enter the new password - it will be displayed in plain text.
4. Select the Save button at the top of the Configuration Settings panel; this will encrypt the new password, save the configuration, and restart the Agent.

If the agent is unable to start because of the password change:

1. Locate the sqagent.properties file for the agent - it will be located in a directory agtN (where N is a numeric value) in

• C:\ProgramData\StarQuest\sqdrplus\conf (Windows).
• /var/sqdrplus/conf (Linux)

2. Edit sqagent.properties with notepad, vi, gedit or other text editor and change the sourcePassword value to the new password (plain text). Save sqagent.properties.
3. You should now be able to start the agent; if necessary, use the Services control panel to restart the SQDR Plus Launch Agent (Windows) or run /etc/init.d/sqdr-capagent restart (Linux).
4. After the agent is running, use SQDR Control Center as described above to change the value again so that the value is encrypted.

Password Changes (local or domain Windows or Linux user for access to DB2 LUW control database)

When using a local DB2 LUW control and staging database for SQDR Plus, the prerequisites include the creation of a local or domain user ID named "sqdr". The user and password information is collected during creation of a Staging Agent are stored (encrypted) in the agent's configuration (sqagent.properties file) and in a table (SQ_PROPERTIES) on the source system that is used by SQDR clients in order to connect to the SQDR Plus staging database. If the password for this user changes, SQDR will be unable to retrieve data from the SQDR Plus staging database and the Staging Agent will be unable to start.

The procedure for changing SQDR Plus to use the new password is identical to the process above for source password changes, except that you should modify the password field rather than the sourcePassword field. Saving the change restarts the Staging Agent, which in turn updates the SQ_PROPERTIES table on the source system.

After the change has been made to SQDR Plus, refresh the connection information (to the staging database) stored on the SQDR client:

1. Open Data Replicator Manager
2. Right-click on the Source and select Properties
3. Select the Advanced tab
4. Select OK

Password Changes (Derby database used by Launch Agent)

The SQDR Plus Launch Agent uses a local Derby database as a control database. You are prompted to supply a password during installation. We recommend using a non-sensitive password for this function as it is stored in plain text in several locations. This is the password that is supplied for the user SQDR when starting SQDR Control Center.

Contact StarQuest Support for details on changing the Derby password.

Upgrading from SQDR Plus Classic to SQDR Plus v4

The following upgrade procedure will run new baselines for all incremental subscriptions. If running new baselines is undesirable because of the size of the tables involved or speed of network connectivity, contact StarQuest support for assistance.

On the SQDR client (tier 3) system:

1. Update SQDR to 4.01 or later. Be sure to run SQDR Configuration to update the SQDR control database. SQDR 4.x is compatible with SQDR Plus Classic, so this step can be done in advance.
2. In Data Replicator Manager, pause the I/R groups.

On the source system (tier 1) running SQDR Plus Classic :

3. Save a copy of sqagent.properties being used by SQDR Plus Classic for reference.
4. If you are using the Publish function, use CAMAINT to note the published table configuration. Then “unpublish”
   and delete remaining subscribers and subscriptions.
5. Remove SQDR Plus Classic from source system. See the SQDR Plus User's Guide for instructions. On a DB2 for i system, log on as the original installing user and run the command like SBMJOB CMD(CALL PGM(SQDR/RMVSQDR) PARM(SQDR)) JOB(RMV_CA). On a DB2 for Windows system, use the Add/Remove Programs control panel. On a DB2 for Linux or UNIX system, use the uninstall.sh shell script.
6. Confirm that SQDR Plus Classic is completely removed. On a DB2 for i source system, confirm that the users (SQDR and SQDRADMIN) and SQDR collection have been removed, auto-journaled files reverted to being non-journaled, and the exit program is unregistered.
7. If any user files are still configured as auto-journaled or published when the SQDR Plus product remove job is run, the SQDR library containing the SQDR Journal and any Receiver libraries associated with User ASP(s) may remain. The associated objects will be owned by QDFTUSR. In this case, use ENDJRNPF to stop journaling the user files, and then delete the unused SQDR Journal and Receiver objects.
   

On the SQDR Plus v4 (tier 2) system:

8. Install SQDR Plus v4. See the Quick Start Guide to Using SQDR Plus v4 for details.
9. Configure a connection to the source system, using the same or different schema as originally used by SQDR Plus Classic. For simplicity, we recommend using the same schema name.
10. Examine the saved SQDR Plus Classic configuration (sqagent.properties) and restore any special settings - e.g. mail notification parameters.
11. Restore any publication settings.

On the SQDR client (tier 3) system:

12. Refresh the Source by displaying advanced properties to “find” the new Staging Agent
13. Reset I/R groups. This will recreate the SQDR Plus configuration, including auto-journaling any non-journaled files.
14. Resume updates. A new baseline will be run.

Hints:

• If the destination is DB2, refer to Using Truncate to Minimize Logging Activity for DB2 Targets for instructions on truncating the destination tables before resetting the I/R groups.