StarQuest Technical Documents

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

Last Update: 14 November 2019
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.

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. Examine the expert settings of the StarSQL data source used by the SQDR client to connect to the Tier 1 source system and note the value of BinaryCCSID; if the setting is configured, note its value and continue.
  2. 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. In Data Replicator Manager on the tier 3 SQDR system, select the Source and right-click to select Properties.
  4. Click OK (not Cancel). This will update the connection information that the SQDR client uses to connect to the Tier 2 staging system.

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: [email protected] 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.

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


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.