StarQuest Technical Documents

SQDR Plus IBM i (OS/400) Resource Utilization

Last Update: 28 January 2021
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL008

Abstract

SQDR utilizes several IBM i (OS/400) features in order to remotely acquire copies of IBM DB2 for i database files. In addition, SQDR Plus accesses the IBM i Journal-based mechanisms to obtain change data and schema change information. Access is achieved using TCP/IP based network services supplied with the IBM i operating system. This technical note describes the specific usage of the network services and other services.

Prerequisites

Knowledge of IBM i job management fundamentals, working knowledge of the IBM Navigator application, and some knowledge of IBM i commands is required.

Additional Resources

IBM i Access port usage details:
Port numbers for host servers and server mapper
TCP/IP Ports Required for IBM i Access and Related Functions
IBM i network server jobs: Server table
IBM i User Authorities used by SQDR Plus

Solution

SQDR Plus technology is designed to have a minimal impact on the iSeries operations. To accomplish real time replication, the data layout (schema) and image of the data (copy) must first be acquired. Subsequently, the underlying changes to the database are obtained by monitoring the journal receiver objects associated with the file.

Schema and images are acquired using remote access technologies to the iSeries host from the SQDR client called ODBC Providers. ODBC providers include the built-in open-standards based DRDA protocol of StarQuest’s StarSQL driver (bundled in SQDR) or the IBM i Access (iSeries Access) driver, which uses an IBM proprietary protocol and may be installed separately as part of the SQDR installation. In the case of StarSQL, access normally uses port 446 on the iSeries (the DDM/DRDA server, QRWTSRVR) and in the case of IBM i Access, port 8471 (the Database server, QZDASOINIT). Both protocols support encryption: port 448 is customarily used by DDM/DRDA and ports 9471 is used by i Access. Encryption may impact host performance, but is otherwise immaterial to this discussion, so the standard ports will be assumed. Regardless of the protocol use, both server jobs end up using the same database engine – QSQSRVR.

Change data is accessed by SQDR Plus via a combination of IBM i remote services exposed by the IBM Java Tool Box (JTB) (also delivered as the open source project JTOpen). The JTB uses TCP/IP to access the File server ( QPWFSSERVO) on the IBM i system which normally operates on port 8473. One connection is made per journal per SQDR Plus instance. In order to access the data managed by a journal, a StarQuest supplied service program RJRNLAPI is invoked to select journal records to be parsed by SQDR Plus. The service program is invoked using the Remote Command server (QZRCSRVS) on port 8475.

The various server jobs are normally configured as pre-start jobs and the current user must be referenced (default SQDR) instead of the job’s user (default QUSER) to differentiate usage between SQDR Plus & SQDR and other unrelated software.
This process is termed “subscribing” and involves interrogating the IBM i system catalogs to discover the file and column attributes. Supplemental metadata is acquired as part of the subscription process including the applicable journal information associated with the file. Standard calls are made using system supplied commands and programmatic interfaces (“API”.) The information is similar to what may be obtained using the DSPFD and DSPFFD commands.

Because SQDR Plus uses journal objects, the subscription process verifies that the table is journaled. If the table is not already journaled, the product may optionally commence journaling the table automatically. As part of journaling a table, an exclusive lock must be obtained to change the physical file attributes. This lock may not be readily obtained during normal operations, so the product provides for deferred operation though a “publish” function available to SQDR Plus administrators, which periodically retries the change operation. This is the only operation which depends upon an exclusive lock being available; the duration is usually on the order of a second or two.

Once a table has been validated for change data replication, the subscription is “run” to obtain an initial copy of the data. SQDR performs this step by issuing a SELECT operation on the table to retrieve the appropriate content. This SELECT SQL statement runs using the database server – either the job QRWTSRVR (initiated on port 446) or the IBM i database server running QZDASOINIT.

Two additional QZRCSRVS jobs are used per SQDR Plus process: one to read the message queue CALOGQ which is used to place diagnostic messages into the SQDR Plus diagnostic facility originating from IBM i processes, and the second to read the message queue CAQ which is used to request services of SQDR Plus from IBM i jobs (such as the VRYSUB command used to suspend or resume change data processing of a subscription, from an IBM i CLP.) These jobs involve calls to QMHRCVM every ten seconds to check for activity.

SQDR and SQDR Plus do not use any IFS shares. However, SQDR Plus does access the IFS through Java Tool Box (JTB) and the i Access host servers for the following operations. StarQuest Support can supply information about alternate (but less efficient) methods for both operations that avoid the use of the IFS.

• Reading the User Space to retrieve change data
  
• Uploading the SAVF file used when updating host components

The following table summarizes the port usage, job name and underlying service program:

Service Name	Server	Common Port (SSL Port)	Job Name	Related
DDM	Database server	446 (448)	QRWTSRVR	QSQSRVR
as-srvmap	Port Mapper	449		Used to look up service by name and return the port number.
as-database	Database server	8471 (9471)	QZDASOINIT	QSQSRVR
as-file	File server	8473 (9473)	QPWFSERVSO
as-rmtcmd	Remote command and program call server	8475 (9475)	QZRCSRVS	RJRNLAPI, QMHRCVM
as-signon	Signon server	8476 (9476)

Users and Collections

During creation of an SQDR Plus Staging Agent for the DB2 for i host, a collection (library) is created on the host, and two user profiles are created:

• a non-privileged user that is the owner of the collection (e.g. SQDR)
• a privileged user that is the owner of a service program used for reading journal receivers (e.g. SQDRADM)

See SQDR Plus IBM i User Authorities for a detailed description of the authorities needed by the two users.

The initial total size of the collection is about 25mb.

The following objects are created in the collection:

Control Tables:	approximate size 200-300kb each
SQ_BASELINES SQ_CATALOG SQ_CQUEUE SQ_IXLIST SQ_JOURNALS SQ_PROPERTIES SQ_READERS SQ_SUBFFD SQ_WORKERS	Output file for DSPFF
Message Queues:
CALOGQ	used to place diagnostic messages (originating from IBM i processes) into the SQDR Plus diagnostic facility
CAQ	used to request services of SQDR Plus from IBM i jobs
Stored Procedures:
TABLEINFO	used by SQDR to list tables available for subscriptions
Utility Programs:
VRYSUB	VRYSUB (Vary Subscription) is used to suspend or resume change data processing of a subscription from an IBM i CLP.
Service Programs:
JRNLINFO
RJRNLAPI	Retrieve journal records to be parsed by SQDR Plus. The service program is invoked using the Remote Command server (QZRCSRVS) on port 8475. Owned by privileged user
SQL Functions:
CYMD MDY YMD	Used by SQDR for date conversion
Exit Program:
CALOGMGMT CADLTRCV CADLTRCV1 (owned by privileged user)	An exit program is installed for QIBM_QJO_DLT_JRNRCV to delay deletion of journal receivers until SQDR Plus has scanned for changes to tables of interest. This can be viewed with WRKREGINF.
Message File:
MSG001	Application messages
User Spaces:
SQSPC0 *USRSPC	One 16mb user space is created for each journal being monitored for changes