StarQuest Technical Documents

SQDR Plus V4 IBM i (OS/400) Resource Utilization

Last Update: 21 September 2016
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
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 port 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.

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

Service Name

Server

Common Port

Job Name

Related

DDM

Database server

446

QRWTSRVR

QSQSRVR

as-database

Database server

8471

QZDASOINIT

QSQSRVR

as-file

File server

8473

QPWFSERVSO

 

as-rmtcmd

Remote command and program call server

8475

QZRCSRVS

RJRNLAPI, QMHRCVM

as-signon

Signon server

8476

 

 

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 spaces for each journal being monitored for changes

 


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.