StarQuest: keeping data in motion

 

Stelo Technical Documents

SQDR Plus: Tips for Db2 for i (iSeries) Replication

Last Update: 28 February 2024
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL032

Abstract

This technical document contains tips and troubleshooting hints when using SQDR Plus for incremental data replication from a Db2 for i (IBM iSeries) database.

The following technical documents provide additional information:

Contents:

Unable to create schema/collection when creating an agent

Symptom: The following error appeared when creating an agent for a Db2 for i source:

Unable to create or verify schema/collection SQDR. [SQL0552] Not authorized to CREATE DATABASE

This error can result if the newly-created user SQDR does not have authority to the CRTLIB command. To confirm this, Issue the command DSPOBJAUT OBJ(CRTLIB) OBJTYPE(*CMD) and observe If *PUBLIC is configured as *EXCLUDE.

Solution:

Either:

  • Grant *USE to the CRTLIB command to the user SQDR
  • Create the collection (schema) as a user that does have sufficient authority:
    e.g.
    STRSQL
    CREATE COLLECTION SQDR
    or
    QSH
    db2 'CREATE COLLECTION SQDR'

You will now be able to use SQDR Control Center to complete creation of the agent, which will populate the collection and assign expected ownership and authorities to the objects in the collection.

Note: If SQDR lacks the authority to create a library, SQDR Plus autojournaling will not function. In this case, we recommend editing the agent's configuration and setting autoJournal=false. Attempts to subscribe to an unjournaled table will then fail with appropriate error, and an AS/400 administrator with sufficient authority can change the journaling properties of the table.

Verifying Connectivity to Host Servers on Db2 for i Source System

SQDR Plus uses the IBM Java Toolbox to communicate with the Db2 for i source system. To verify that the host servers are running and accessible, you can use the JPing utility included in Java Toolbox.

On Windows, start jping from the SQDR Plus program group; it is located under Tools.

On UNIX, run /opt/StarQuest/sqdrplus/capagent/jping.

When you run jping, you will be prompted for the hostname or IP address of the IBM i system; the output should like this:

Verifying connections to system mysystem...

Successfully connected to server application: as-file
Successfully connected to server application: as-netprt
Successfully connected to server application: as-rmtcmd
Successfully connected to server application: as-dtaq
Successfully connected to server application: as-database
Successfully connected to server application: as-ddm
Successfully connected to server application: as-central
Successfully connected to server application: as-signon
Connection verified

For example, a customer with an existing agent to an IBM i source began experiencing a failure (the icon of the agent was cycling between yellow and green), and the following error appeared in the Diagnostics log:

com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Cannot read user space: Connection refused
at com.starquest.sqdr.capture.as400.RemoteJournalReader.readUserSpace(RemoteJournalReader.java:695)

Using jping as described above revealed that the as-file service was not running on the IBM i server. This was confirmed by using WRKTCPSTS, selecting option 3. Work with IPv4 connection status and observing that as-file (port 8473) was not active. After issuing the command STRHOSTSVR SERVER(*FILE), the agent recovered and normal functions resumed.

[SQL0443] SYSIBM:CLI:-514 error when creating agent

Symptom: The following error was encountered after entering connection information for a new Db2 for i Agent (i.e.after the first panel of the Add Agent Wizard)

Unable to validate connection parameters [SQL0443] SYSIBM:CLI:-514

Solution: Catalog calls were failing because the SYSIBM library on the IBM i server was incomplete and needed to rebuilt.

Verify that the SYSIBM library is complete by running the command CALL QSYS/QSQIBMCHK to invoke the SYSIBM object verification tool. Use DSPJOBLOG and F10 to view the results.

If any objects are missing, run the command CALL QSYS/QSQSYSIBM to create any objects missing from the SYSIBM library.

In addition, the catalog cross-reference tables can become corrupted during operations such as a failed release upgrade or a sudden power failure (no UPS). Use RCLDBXREF *CHECK to verify the state of the cross-reference information.

To attempt to correct the cross-reference information for a specific library without taking the system to a restricted state, use the RCLDBXREF *FIX MYLIB command where MYLIB represents the specific library to correct.

To rebuild all the system-cross reference files, run RCLSTG *DBXREF from a restricted state. On large systems, the RCLSTG operation can take long periods of time. It is recommended that this operation be done only if necessary.

See Verifying System Catalog Information for ODBC Use for details.

Tables must be journaled

To use incremental replication, the source table must be journaled, either as *BOTH (preferred) or *AFTER. The following message will appear in the Agent diagnostics if the table is not journaled:

CaptureAgent.addSubscription: File Not Journaled (MYSCHEMA.MYTABLE)
Catalog.addsubscription: Failed to add catalog subscription, errcode=12. Table/File not journaled.

Limitations of *AFTER journaling

For best results, we recommend that tables be journaled for Record Images=*BOTH (the system writes both before and after images to the journal for changes to records in the table).

For information on how to view or change the journaling characteristics of a table, see SQDR Plus Tips for Db2 for i (iSeries) Journaling.

If a table is journaled as *AFTER, the following limitations apply:

  • The subscription must use RRN (Relative Record Number)
  • The subscription cannot have criteria

If a table is journaled as *AFTER and you have not forced the use of RRN, the subscription will fail with this error:

Failed to add incremental subscription at Capture Agent for source table '"MYLIB"."MYTABLE"'. Snapshot could not be started. Error: Stored procedure SQDR.ADDSUBSCRIPTION 05.23.20210710 returned error 18. Invalid journaling/logging options

and a similar error will appear in the Agent diagnostics:

CaptureAgent.addSubscription:MYLIB.MYTABLE MYIRGROUP, System=MYSYS id=8C85846DE301B34AAFB69D810E895DC9
com.starquest.sqdr.capture.ReplicationException: Invalid journaling/logging options

To force the use of RRN, you can either

  • In Data Replicator Manager, select the checkbox Force Use of RRN on the General panel of the incremental group and recreate the failing subscriptions, as this setting.affects only new subscriptions.

or

  • In Control Center, edit the Agent configuration and add the property alwaysUseRowId with a value of True. This affects all subscriptions of that agent, and should only be used if all tables of interest are journaled as *AFTER.

Specifying a primary key with SQ_IXLIST (primary key advice table)

Incremental replication requires that the source table must have a primary key or a unique index. In the case of a Db2 for i source, a table lacking a primary key can be successfully replicated using the Relative Row Number (RRN). However, this method has the disadvantage that a REORG of the table will result in the need to perform a new baseline, since the row numbers will change.

An alternative is to use special table on the source system (SQDR.SQ_IXLIST) to define the columns that comprise the primary key - i.e. the content of these columns provide a unique value, even if the source table is not defined with a primary key or unique index.

Note that SQDR.SQ_IXLIST resides on the source system, not the staging database. The table must be journaled as *BOTH. Do not use the RRN column - i.e. uncheck "force RRN" on the column page. All columns that provide the key should be replicated.

Layout:

  • table schema
  • table name
  • column name
  • sequence

Example:

INSERT INTO SQDR.SQ_IXLIST ("TABLE_SCHEM","TABLE_NAME","COLUMN_NAME","KEY_SEQ") VALUES('QAUSER','APPS','ID',10)

The DML may be issued using i Access Client Solutions, 5250/STRSQL command, or another ad-hoc SQL tool. The user must use a sufficiently privileged credential (such as “SQDR”) to modify the table.

If the subscription is defined to “Use Unique constraints”, then a primary key constraint is added to the destination table

Logging IBM Java Toolbox Activity

The IBM Java Toolbox is used by the Launch Agent and Jetty (SQDR Control Center) components of SQDR Plus to communicate with the Db2 for i source system.. You can produce Java Toolbox diagnostic information by adding com.ibm.as400.access.Trace definitions to the Java invocation of those services.

For instance, to trace Java Toolbox usage by Jetty:

  1. Locate the appropriate wrapper.conf. The jetty wrapper configuration file is located in the jetty/wrapper/conf subdirectory of the Programs directory (e.g. C:\Program Files\StarQuest\sqdrplus on Windows and /opt/StarQuest/sqdrplus on UNIX).
  2. Make a backup copy of wrapper.conf.
  3. Examine wrapper.conf and determine the number of the highest wrapper.java.additional.N parameter. For instance, if you see wrapper.java.additional.4 defined, then you will use wrapper.java.additional.5 in the next step.
  4. Create a text file named wrapper-local.conf in C:\ProgramData\StarQuest\sqdrplus\jetty\wrapper-local.conf
    (/var/sqdrplus/jetty/wrapper-local.conf on Linux) containing the following contents:

wrapper.java.additional.5=-Dcom.ibm.as400.access.Trace.category=error,warning,information,diagnostic

  1. Stop and restart the SQDR Plus jetty service.

This will produce a large amount of diagnostic information in wrapper.log for the jetty service. This log file resides in the jetty/logs subdirectory of the Application Data directory (C:\ProgramData\StarQuest\sqdrplus on Windows; /var/sqdrplus on UNIX). The location of this log file is defined in wrapper.conf.

If you would prefer to write the diagnostic information to a different file, add the following definition:

wrapper.java.additional.6=-Dcom.ibm.as400.access.Trace.file=c:\\temp\\trace.out

For details on Java Toolbox tracing options, refer to the IBM documentation.

Be sure to disable tracing when you have finished collecting diagnostic information by deleting or renaming wrapper-local.conf and restarting the service.

Data Conversion Issues with Binary Columns

Symptom: Some Db2 for i tables, especially in older databases, may have columns lacking a CCSID attribute. The StarSQL ODBC driver includes an expert setting BinaryCCSID that instructs StarSQL to treat binary data as character data using the specified CCSID. If this setting is configured in the StarSQL ODBC data source used by the SQDR client to connect to the Tier 1 source system, you also need to configure it in the ClientODBCString used by the SQDR client system when it connects to the Tier 2 staging system. Failure to do so will result in incorrect data in the target database.

Solution:

  1. From a terminal session, use DSPFD (display file description) to identify the CCSID (Coded character set identifier) of the table. If it is 65535, then the BinaryCCSID parameter must be specified on all StarSQL connections: from Tier 3 to Tier 1 (as defined in the connection string or data source of the source) and from Tier 3 to Tier 2 (as defined in the ClientODBCString in the agent configuration). Note that it is also possible to configure the CCSID for individual columns, but that is not common.
  2. Examine the expert settings of the StarSQL data source or DSN-less connection string used by the SQDR client to connect to the Tier 1 source system and note the value of BinaryCCSID. Set it if it is not already set (e.g. BinaryCCSID=37 - the value may differ for non-US databases)
  3. Using any query tool, examine the current value of agentODBCString in SQDR.SQ_PROPERTIES on the source system; this table is created and populated by SQDR Plus.

select * from SQDR.SQ_PROPERTIES
...
"agentODBCString", "DRIVER={StarSQL 32}; HostName=myTier2Sys; Port=50000; Server=SQDRP0; PkgColId=STARSQL"
....

  1. In SQDR Control Center, select the agent (in the left window) and select the Configuration Settings panel (in the right window).
  2. Select the Plus symbol at the top and add a new parameter clientODBCString. Enter the value of agentODBCString (without the double-quotes) and append the BinaryCCSID parameter - e.g.

DRIVER={StarSQL 32};HostName=myTier2Sys;Port=50000;Server=SQDRP0;PkgColId=STARSQL;BinaryCCSID=37

  1. Select the Save icon at the top. The parameter will be saved and the agent restarted.
  2. Examine SQDR.SQ_PROPERTIES on the source to confirm that the change has been conveyed to the source system.
  3. Update the connection information that the SQDR client uses to connect to the Tier 2 staging system: In Data Replicator Manager on the tier 3 SQDR system, select the Source and right-click to select Properties. On the Advanced Panel of the Source, do one of the following:
  • SQDR v5: Change the value of the dropdown for notification address, click OK, and then change it back.
  • SQDR v6: Select the Refresh button. Examine the Agent connection string to confirm the change.

Excessive character count error

Symptom:

An incremental subscription from a Db2 for i system configured with CCSID 65535 completed its baseline but then failed when applying incremental data with the general error message:

Subscription is active, but has had unexpected errors or row counts for INSERT, UPDATE, or DELETE on the target table

A more descriptive error message was found using Event Viewer to view the Windows/Application event log

Subscription flagged due to SQL error encountered while applying changes for group 'MYGROUP', target table '"mydb"."myschema"."mytable"'.

Error message: Error applying change data for subscription MYSUB:
ODBC message: SQLSTATE 22001, native error 0,[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation
Analysis:
Error: Excessive character count( 10) for column "COL1"SQL_CHAR(5)
Error: Excessive character count( 4) for column "COL2"SQL_CHAR(2)
etc.

Note that the excessive character count is double that of the actual column width.

Solution:
Configure binaryCCSD=37 in the connection strings for SQDR source (Tier 3 to Tier 1) and in ClientODBCString in the agent configuration (defining Tier 3 to Tier 2 communication) as described in Conversion Issues with Binary Columns above.

CPF3C48 Error

The following error appears in SQDR Plus diagnostics when you run the baseline for a new subscription, or attempt to publish a table:

getJournalName: Call to QUSPTRUS(return -1) returned CPF3C48, Message Text=Operation not valid on system domain object

This error was encountered when the system value QALWUSRDMN was set to a list of libraries rather than the default value of *ALL, and is related to the user space object created in the SQDR library.

  1. Using SQDR Control Center, stop the SQDR Plus agent
  2. In a terminal session, enter DSPSYSVAL QALWUSRDMN to verify that the current value is something other than *ALL.
  3. Append SQDR to the list of libraries. Note that QTEMP is required. This change is immediate.

CHGSYSVAL SYSVAL(QALWUSRDMN) VALUE('QBRM QTEMP QUSRBRM SQDR')

  1. Delete the user space object that has already created in the SQDR library.

DLTOBJ OBJ(SQDR/*ALL) OBJTYPE(*USRSPC)

  1. Using SQDR Control Center, start the SQDR Plus agent; this will recreate the user space object.

 

Agent fails to start with "Cannot initialize service program"

Symptom: After updating SQDR Plus to v4.74 or later, a Db2 for i agent fails to start and an error like this appears in the Diagostics:

com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Cannot initialize service program: Value for a parameter was not valid.
at com.starquest.sqdr.capture.as400.RemoteJournalReader.initializeServiceProgram(RemoteJournalReader.java:485)

Solution: Select the Agent in SQDR Control Center and select Update Host Components from the Database menu.

Agent fails to start, attempting to recreate tables that already exist

Symptom: After an IBM i system update from i 6.1 to 7.1, the agent failed to start, and the following error appeared in the diagnostics, indicating that the agent was trying to recreate a control table that already exists:

SEVERE: [sqv][main][Oct 16, 2016 8:00:43 AM] CaptureAgentLog: CaptureAgent.main: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException:
[SQL0601] SQ_IXLIST in SQDR type *FILE already exists.

Solution: The system cross-reference tables were corrupt and needed to be rebuilt (in restricted state) with RCLSTG *DBXREF. See the IBM technical document Verifying System Catalog Information for ODBC Use for details.


Issues due to QALWOBJRST system value

If the system value QALWOBJRST is set to something other than *ALL (default) or *ALWPGMADP (Objects that adopt authority can be restored to the system), creation of an agent or update of host components will fail.

Symptoms: The following symptoms will occur on creation of an agent:

In SQDR Manager:
Request failed.
Unable to copy resources to SQDR.
Object RJRNLAPI in library SQDR type *SRVPGM not found.

Jetty wrapper log:
INFO: ManagementServiceImpl.runCmd:7 security or data format changes occurred.
7 objects restored. 6 not restored to SQDR.

QZRCSRVS job log:
CADLTRCV in library SQDR with adopt authority attribute not restored.
CALOGMGMT in library SQDR with adopt authority attribute not restored.
TABLEINFO in library SQDR with adopt authority attribute not restored.
VRYSUB1 in library SQDR with adopt authority attribute not restored.
CADLTRCV1 in library SQDR with adopt authority attribute not restored.
RJRNLAPI in library SQDR with adopt authority attribute not restored.

Solution:

  1. Change QALWOBJRST to *ALL or *ALWPGMADP.
  2. Remove the library (SQDR) and users (SQDR and SQDRADM) created during the failed attempt. If you do not remove the library, the Agent Creation Wizard will think that the library already exists and will not attempt to recreate it.
  3. Create the agent using the wizard.

Symptoms: The following symptoms will occur during Update Host Components:

in SQDR Manager, the operation will appear to succeed; "Save file on <system>" will appear in the history panel.

but the jetty wrapper log will display:
7 objects restored. 6 not restored to SQDR.

and the objects will not have been updated.

Solution:

  1. Change QALWOBJRST to *ALL or *ALWPGMADP.
  2. Run Update Host Components.

Unable to update host components or pause agent after host IP address or hostname change

Symptoms: After a network reconfiguration involving a change to IP address or hostname of the Db2 for i source system, an attempt to update host components times out, displaying the error Unable to retrieve admin user.

Using SNDMSG to pause the agent fails with the error

stopAgent Failed to stop agent.
java.rmi.RemoteException: Agent: MYRDB@myas400.newdomain.com does not exist.

Solution: Update to SQDR Plus 4.77 or later, or update the NAME and HOST fields in the SQ_AGENTS table of the Derby control database. see Tips for the SQDR Plus Derby Control Database for details.

User not authorized to User Space

Symptom:

the following messages appear in the Diagnostics during agent startup
(rather than the expected message of
RemoteJournalReader.initializeServiceProgram:MYLIB.QSQJRN MYLIB/QSQJRN0001 00000000000000111399 User space: SQSPC0 SQDR )

SEVERE
RemoteJournalReader.fillBuffer:
com.ibm.as400.access.AS400SecurityException: /QSYS.LIB/SQDR.LIB/SQSPC0.USRSPC: User is not authorized to object.

WARNING
ReplicationWorker.run:
com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Cannot read user space: /QSYS.LIB/SQDR.LIB/SQSPC0.USRSPC: User is not authorized to object.

This error may occur if the QPWFSERVER authorization list is configured as *PUBLIC *EXCLUDE (restricting access to QSYS.LIB objects from remote clients) rather than the default of *PUBLIC *USE.

Solution:

Add the agent user to the authorization list:

ADDAUTLE AUTL(QPWFSERVER) USER(SQDR) AUT(*USE)

Agent fails to start - Unable to allocate user space

Symptom: Agent fails to start, and logs indicate a problem allocating the user space object used by SQDR Plus.

From SQDR Plus diagnostics:

SEVERE: [sqv][main][Aug 23, 2018 2:54:50 PM] CaptureAgentLog: RemoteJournalReader.initializeUserSpace:
java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
...
at com.ibm.as400.access.UserSpaceImplRemote.retrieveAttributes(UserSpaceImplRemote.java:240)
at com.ibm.as400.access.UserSpaceImplRemote.getLength(UserSpaceImplRemote.java:201)
at com.ibm.as400.access.UserSpace.exists(UserSpace.java:422)
at com.starquest.sqdr.capture.as400.RemoteJournalReader.initializeUserSpace(RemoteJournalReader.java:1002)

From joblog for the IBM i host server:

SEVERE: [sqv][main][Aug 23, 2018 4:29:58 PM] CaptureAgentLog: RemoteJournalReader.initializeUserSpace:
com.ibm.as400.access.AS400Exception: CPF9803 Cannot allocate object SQSPC0 in library SQDR4.
at com.ibm.as400.access.UserSpaceImplRemote.buildException(UserSpaceImplRemote.java:86)

The symptoms appear as if a lock exists on the user space, but no locks were displayed by WRKOBJLCK. The SQDR Plus Capture Agent is stopped - i.e. it should not be holding any locks. There are no issues with user authority.

Solution: When this situation was encountered, it was resolved by restarting the Windows system running SQDR Plus.

Additional analysis

To check if there are existing locks held by an ILE program that are not displayed by WRKOBJLCK, issue the following SQL and examine the columns MODULE_LIBRARY & MODULE_NAME (the module containing the ILE procedure)

select * from QSYS2.OBJECT_LOCK_INFO where object_schema='SQDR' and object_name like 'SQSP%' with nc

QSYS2.OBJECT_LOCK_INFO is available in:

  • IBM i 7.3 base
  • IBM i 7.2 with DB2 group SF99702 Level 9
  • IBM i 7.1 with DB2 group SF99701 Level 38

Also

  • Use NETSTAT on the IBM i host to determine if there are any residual connections from the Windows system that haven't been disconnected, even though the agent is stopped.
  • Review the TCP/IP keepalive settings on the IBM i host

Agent fails to start after role swap - Journal not found

Symptom: After testing a role swap in a disaster recovery scenario, the agent failed to start, indicating that it is looking for a non-existent journal (the journal actually resides in a different library):

java.rmi.RemoteException: Replication Worker failed to initialize

INFO initialize: Call to QjoRetrieveJournalInformation(return -1) returned CPF9801, Message Text=Object MYJRN in library MYLIB not found.
SEVERE LogReader.createLogReader:RuntimeException in creation for (MYLIB.MYJRN)

Solution: Delete all rows in the SQDR.SQ_MDCACHE (metadata cache) table in the local Db2 LUW control database used by the agent and restart the agent. Note that the rebuild of SQ_MDCACHE may take a very long time, and should be done with the guidance of StarQuest support.

Examine the controlDbUrl property of the Configuration to determine the database being used e.g. SQDRP0

DB2 CONNECT TO SQDRP0
DB2 DELETE FROM SQDR.SQ_MDCACHE
DB2 DISCONNECT SQDRP0


Performance: Increased Latency Due to Journal Caching

Symptom

After updating to a new Db2 for i server, a customer with a low latency requirement noticed that replication latency increased from 200ms to 5000ms (5 seconds).

It was discovered that the optional licensed program 5770SS1 options 42 HA Journal Performance was installed on the new system, and that journal caching with a CACHEWAIT setting 5 (seconds) was in affect for the journals being accessed by SQDR Plus.

Solution

One of the following:

  • Use CHGJRNA to reduce the CACHEWAIT value (the maximum time that the system waits before writing journal entries to disk) for the journals of interest. Setting this to *SYSDFT sets the cache wait time to the system default of 30 seconds.
  • Use CHGJRN to disable journal caching (JRNCACHE parameter) for the journals of interest.

References:

 

Creating agent fails with access denied to library QGY

Symptom

Creating a new agent failed with the error
attempt to use permanent system object QGY without authority

Solution

The user had changed permission on the library QGY from *PUBLIC *USE to *PUBLIC *EXCLUDE. The issue was resolved by restoring *USE access to at least the library QGY and the objects QGY/QGYOLOBJ, QGY/QGYOLAUS & QGY/QGYCLIST.

Explanation

Prior to v5r3, the library QGY provided the Open List APIs (part of Host Servers, used by iAccess and Java Toolbox). These API's are now implemented by objects in QSYS, but the QGY library provides compatibility with existing applications, and transfers control to the corresponding APIs in QSYS. SQDR Plus uses JTOpen (Java Toolbox), which as of v10.7 (September 2021) continues to access the QGY library.

Starting agent fails with Not authorized to CREATE PROCEDURE

Symptom

On the initial start of a new agent, the following error appeared in Diagnostics

com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException:
[SQL0552] Not authorized to CREATE PROCEDURE.

Solution

The user had restricted access to the table QSYS2/SYSROUTINE (default is *PUBLIC *CHANGE).

At a minimum, *OBJOPR & *ADD for this table is needed by the user SQDR to create procedures.

Because QSYS2/SYSROUTINE was in use and locked, we were not able to change it directly; instead we added user SQDR (with *OBJOPR & *ADD permissions) to an authorization list QSYS2 that the user had previously created for controlling access to QSYS2/SYSROUTINE.

Minimized Entry Data Journal (MINENTDTA) Considerations

When using a journal defined as MINENTDTA(*FLDBDY), using RRN is required, and criteria should be avoided.

The following error will occur if the journal is defined as MINENTDTA(*FILE) or MINENTDTA(*DTAARA):

Failed to add incremental subsicription. Snapshot could not be started.
Error: Stored procedure SQDR.ADDSUBSCRIPTION returned error 11.
Unexpected native error: Journaled for Minimized Journal Entries, but not on Field Boundaries. Not supported.

Tables using *AFTER journaling must use RRN

Issue: Creating an incremental subscription from an IBM i source failed with an error

Stored procedure SQDR.ADDSUBSCRIPTION returned error 18: invalid journaling/logging options.

Solution:

First determine if the table is journaled, and if so, whether it is journaled as *BOTH or *AFTER. See SQDR Plus Tips for Db2 for i (iSeries) Journaling for details.

If the table is journaled as *AFTER, be sure to use RRN (select Force Use of RRN on the IR group properties). The checkbox for Create target unique index using source table RRN on the Columns tab of the subscription should appear as enabled, and the destination name for the RRN column should appear. Note that if that space is blank, then the RRN column will not be replicated.

 

Performance: Using the filterAtSource agent property

The filterAtSource property controls whether or not all journal entries are forwarded to Tier 2.  If the value is true (default) and the number of subscribed tables for a journal is less than or equal to 300, only activity for "matched" names are forwarded to Tier 2.  Filtering at the source reduces the amount of traffic on the network, at the expense of increased processing on Tier 1, and extra activity at agent startup.

If SQDR is replicating all or almost all of the tables of a journal, you may want to set filterAtSource to false. This is especially true in the case of an unreliable connection between Tier 2 and Tier 1, as the filters are added individually every time the worker is started or restarted.

If the number of subscribed tables for a journal is over 300, then no filtering can be performed at the source; this is a limitation of the logreader API. A workaround is to create additional agents, each subscribed to less than 300 tables. For example, consider the case of a journal being used for 2000 tables, of which SQDR is replicating 400 tables. In the default scenario, all journal entries are sent to Tier 2, which will then scan the data for the 400 tables of interest. If instead 2 agents, each with 200 subscribed tables, are configured, then filterAtSource is in affect and only journal entries of the tables of interest are sent to Tier2.

Managing Exit Program Registration

If SQDR Plus agents have been deleted and recreated multiple times (e.g. in a test environment), and exit programs were not properly unregistered with WRKREGINF/QIBM_QJO_DLT_JRNRCV/option 8/option 4 to delete (see Deleting an SQDR Plus Staging Agent: CLEANUP OF A DB2 FOR I SOURCE SYSTEM), it is possible for multiple exit program registration entries to exist.

At best, this may result in confusion and interfere with troubleshooting; at worse, it may intefere with cleanup of journal receivers that are no longer needed or result in jobs causing large number of output files in QPRINT containing the following error

CALog: Exception ID: CPF2469, Exception Data:

There should be (at most) only one exit program registered for each SQDR Plus agent; there may be none if managedLogs=false.

If there is more than one entry, determine the Exit Program Number of the entry to retain by executing the following SQL on the IBM i source (replacing SQDR with the name of the agent library if necessary) and delete the others with WRKREGINF.

select PROPERTYVALUE from SQDR.SQ_PROPERTIES where PROPERTYNAME='ExitProgram'

Alternatively, you can delete all entries for an agent with WRKREGINF, execute the SQL

delete from SQDR.SQ_PROPERTIES where PROPERTYNAME='ExitProgram',

and restart the agent, which will recreate the exit program.

If the table SQDR.SQ_PROPERTIES does not exist (but the library exists, with other typical content), this indicates an agent that was deleted using SQDR Control Center but not fully cleaned up on the IBM i system.

 


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.