Stelo Technical Documents

SQDR Plus IBM i CL setup scripts

Last Update: 21 January 2022
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL029

Abstract

The Quick Start Guide to Using SQDR Plus describes the typical method of creating an SQDR Plus Agent for an IBM Db2 for i host using the Add Agent Wizard of SQDR Manager. This GUI wizard performs all the necessary setup on the IBM i host, including creating two new userID's with appropriate authorities, and creating and populating the agent schema (collection). The Add Agent Wizard prompts for the user name and password of an existing user with SECOFR authority (e.g. QSECOFR) or, at a minimum, *SECADM and *ALLOBJ authorities. All of the actions (creating users, transferring the SAVF, RSTLIB, grants, etc) are performed using a Java Toolbox connection from the system running SQDR Plus. This method is designed for quick and trouble-free installation, without the need to log on to the IBM i server through a terminal session.

See SQDR Plus IBM i User Authorities for detailed information about the roles and tasks performed by each of the three user ID's used by SQDR Plus (installing user, agent user, and privileged (journal reader) user) and the grants necessary to enable non-privileged userID's to perform those tasks.

For situations where data center security policies restrict the use of privileged user accounts, StarQuest provides a set of CL programs that can be used by an IBM i system administrator to perform the IBM i host setup tasks prior to creating the agent, including creating the userID's and grants described in SQDR Plus IBM i User Authorities. After performing the host setup tasks, use the SQDR Manager Add Agent Wizard to create the agent, supplying the credentials of the newly-created userID's.

This document describes the use of these programs.

Solution

The CL programs are delivered as a SAVF named SQDRINST, containing a SAVF named SQDRP (containing objects used by SQDR Plus), and the following *PGM & *CMD objects:

• ADDAGENT - add the agent
  
• UPDTHOSTC - update host components (used by ADDAGENT during the initial setup and invoked directly to install later updates)
  
• EXITPGM - install the exit program used to prevent premature deletion of journal receivers (before the SQDR Plus journal reader has scanned them)
  
• SQDRAUTL - create authority lists and grant minimal access necessary to user libraries, tables, journals, and journal receivers

The source for all the programs are supplied in the source physical files QCLSRC & QCMDSRC.

In addition to details about the programs listed above, information is provided on the following topics:

• Prerequisites
• Preparation
  
• Creating the Agent in SQDR Manager
  
• Creating Incremental Subscriptions in Data Replicator Manager
  
• Deleting an Agent

Prerequisites

The programs use the RUNSQL command, which is available in

• i 7.2 and later
• i 7.1 with DB2 PTF Group SF99701 level 14 (TR4)
• i 6.1 with DB2 PTF Group SF99601 level 25

If you are using an older version of IBM i that does not include the RUNSQL command, edit the CL programs and change all instances of RUNSQL to QSH('db2...'); however, this has not been tested.

The SAVF and the objects are supplied in i 7.1 format. Contact StarQuest support if you need a version for i 6.1.

Preparation

1. Create an empty SAVF on the IBM i host: CRTSAVF QGPL/SQDRINST
   
2. Transfer the supplied SAVF (SQDRINST.SAVF) to the IBM i host using ftp binary transfer mode or iAccess Navigator
   
   C:\>ftp myas400
   User: myuser
   Enter password: mypassword
   ftp> cd QGPL
   ftp> bin
   ftp> put SQDRINST.SAVF
3. Restore the SAVF to a library named SQDRINST:

RSTLIB SAVLIB(SQDRINST) DEV(*SAVF) +
SAVF(QGPL/SQDRINST) RSTLIB(SQDRINST) OPTION(*ALL)+ MBROPT(*ALL) ALWOBJDIF(*ALL)

3. If desired, examine the source files in QCLSRC & QCMDSRC.
4. Add SQDRINST to your library list:

ADDLIBLE SQDRINST

ADDAGENT - add agent

For initial installation, enter ADDAGENT and hit F4 for prompting of the parameters:

SQDR user/schema - default SQDR
Password for SQDR user
SQDRADM user - default SQDRADM
Password for SQDRADM user

ADDAGENT will create the userID's and call UPDTHOSTC (to restore the SAVF) and EXITPGM (to install the exit program)

After completion of the command, examine the joblog for errors by entering DSPJOBLOG, then F10=Display detailed messages and then Page Up.

ADDAGENT is typically used only once. Note that if the SQDR and SQDRADM users already exist, the passwords supplied on the command may not the actual passwords associated with the users.

SQDRAUTL - create authorization lists

To grant access to a library that will be replicated, use SQDRAUTL to create authorization lists & grant authorities for an SQDR Plus Agent

Enter SQDRAUTL and hit F4 for prompting of the parameters:

SQDR user/schema - default SQDR
SQDRADM user - default SQDRADM
SQDR authorization list - default SQDRL
SQDRADM authorization list - default SQDRADML
Name of a library to enable for replication
Name of the journal - default QSQJRN
Library where journal is located - this is often, but not always, the same as the library to be enabled for replication.

After completion of the command, examine the joblog for errors by entering DSPJOBLOG, then F10=Display detailed messages and then Page Up.

You may run this multiple times if you intend to replicate from multiple libraries; ignore errors that may occur when it attempts to create an authorization list that already exists.

UPDTHOSTC - update host components

This command is invoked by ADDAGENT on initial setup; it can also be used directly when StarQuest delivers future updates to the host components, and is equivalent to the Update Host components function of SQDR Control Center (which requires supplying the credentials of a user with SECOFR authority).

To update host components, stop the agent using SQDR Control Center, then run UPDTHOSTC and hit F4 for prompting of the parameters:

SQDR user/schema - default SQDR
SQDRADM user - default SQDRADM

After completion of the command, examine the joblog for errors by entering DSPJOBLOG, then F10=Display detailed messages and then Page Up.

EXITPGM - install the exit program

Parameters:

SQDR user/schema - default SQDR

This command is invoked by ADDAGENT on initial setup; it is typically not called directly.

Creating the Agent in SQDR Manager

After running ADDAGENT to create the host environment, use the SQDR Control Center to create the agent, specifying SQDRADM as the installing user (the userID specified on the first panel), SQDR for the Control Schema, and (on the second panel), user and password credentials for the Agent User (SQDR) and Privileged Owner (SQDRADM).

Refer to Quick Start Guide to Using SQDR Plus on Windows with IBM Db2 for i (Part 1) for details.

Creating Incremental Subscriptions in Data Replicator Manager

Before creating incremental subscription in Data Replicator Manager, be sure to run SQDRAUTL for the libraries containing the tables that you plan to replicate.

Deleting an Agent

For details on unregistering the exit program and deleting users and the SQDR schema (collection), refer to the section Cleanup of a DB2 for i Source System of the technical document Deleting an SQDR Plus Staging Agent.

To delete the authorization lists, first revoke access from the objects to which the authorization lists have been granted access, then delete the authorization lists themselves.

RVKOBJAUT OBJ(MYLIB/*ALL) OBJTYPE(*ALL) AUTL(SQDRL)
RVKOBJAUT OBJ(MYLIB/*ALL) OBJTYPE(*ALL) AUTL(SQDRADML)
RVKOBJAUT OBJ(MYLIB) OBJTYPE(*LIB) AUTL(SQDRL)

DLTAUTL AUTL(SQDRADML)
DLTAUTL AUTL(SQDRADM)