StarQuest Technical Documents

Tips for the SQDR Plus Derby Control Database

Last Update: 20 February 2024
Product: SQDR Plus
Version: 4.2 & later
Article ID: SQV00PL020

Abstract

In the four tier StarQuest Data Replication architecture, SQDR Plus runs on Tier 2 and uses a local Apache Derby database as the control database for the SQDR Plus Launch Agent, which registers and controls individual Staging Agents for each source database.

Derby is designed to be installed and run with minimal administration. In normal operation, the SQDR Plus user has no direct interaction with the Derby database software, which should be considered as embedded and for exclusive use of the replication software. However, conditions may occur that require some interaction with Derby. This technical document provides guidance in working with Derby as used by SQDR Plus.

Topics

• Using ij to perform SQL operations on the Derby control database used by Launch Agent
• Recovering the Derby control database after disk full condition
• Backing up the Derby Control Database
• Incorrect Agent Name after Network Reconfiguration
• Using SQL Server Linked Server to connect to the Derby control database
• Using IBM Data Studio to connect to the Derby control database
• Changing SQDR Plus ProgramData directory
• Configuring Non-standard RMI Ports
• Enabling Statement Logging

Using ij to perform SQL operations on the Derby control database used by Launch Agent

The Launch Agent uses an Apache Derby database as its control database. ij is an interactive SQL scripting tool that is included with Derby. Under direction of StarQuest support, you can use the ij utility to perform tasks such as examining control tables.

On Windows, start ij from the Tools subdirectory of the SQDR Plus program group (see below if you are using an older version of SQDR Plus):

On UNIX, run /opt/StarQuest/sqdrplus/ij.

At the ij prompt, enter the following to connect to the Launch Agent control database:

ij> connect 'jdbc:derby://localhost:1527/SQDRPLUS;user=SQDR;password=mypassword';

where mypassword is the Derby password that you supplied during installation of SQDR Plus.

Once connected, you can perform SQL operations by entering semicolon-terminated statements such such as SELECT * FROM SQ_AGENTS;.or SHOW TABLES; (to view a list of tables).

If you are using an older version of SQDR Plus, create a batch file ij.bat containing:

set SQDRPLUS=C:\Program Files\StarQuest\sqdrplus
set DERBYPROG=%SQDRPLUS%\derby
set DERBY_HOME=C:\ProgramData\StarQuest\sqdrplus\derby
set JAVA=%SQDRPLUS%\jre7\bin\java
"%JAVA%" -jar "%DERBYPROG%\lib\derbyrun.jar" ij

Recovering the Derby control database after disk full condition

In this scenario, SQDR Plus was unable to function after the system experienced a disk full condition because connections to the Derby control database used by the Launch Agent failed with the following error:

Recovery failed unexpected problem log record is Not first but transaction is not in transaction table : 1740392.

If there have been no recent changes to the Launch Agent's configuration (e.g. no new databases added recently), removing the Derby log files will allow the Derby database to recover to a consistent state:

1. Make sure that the SQDR Derby service is stopped.
2. Locate the log directory for the Derby database SQDRPLUS (C:\ProgramData\StarQuest\sqdrplus\derby\SQDRPLUS\log for Windows; /var/sqdrplus/derby/SQDRPLUS/log for Linux).

C:\ProgramData is normally a hidden directory on Windows, if necessary make it visible:

1. Select Folder and Search Options from the Organize menu.
   
2. Select the View panel.
   
3. Select Show hidden files, folders, and drives.
3. Move the files in the log directory to another directory; these can be deleted after you are confident that the issue is resolved.
4. Start the SQDR Derby service.
5. You can verify connectivity to the Derby database using ij - see Using ij to perform SQL operations on the Derby control database used by Launch Agent.

If recent changes to the Launch Agent's configuration (e.g. recently-added databases) were lost, contact StarQuest Support for further assistance.

Backing up the Derby Control Database

1. Stop all three SQDR Plus services (Agent, Jetty, and Derby).
2. Locate the directory for the Derby database SQDRPLUS (C:\ProgramData\StarQuest\sqdrplus\derby\SQDRPLUS for Windows; /var/sqdrplus/derby/SQDRPLUS for Linux). C:\ProgramData is normally a hidden directory on Windows, if necessary make it visible (see above).
3. Copy the entire SQDRPLUS directory.
4. Restart the three SQDR Plus services

Incorrect Agent Name after Network Reconfiguration

If you make network-related changes to the configuration of an existing agent, the name of the agent is not changed. In some cases, this is only a cosmetic issue, but there are several scenarios (pausing a DB2 for i agent or updating the Host Components on an IBM host) where functionality is affected.

One customer moved its SQL Server source database to a new server using backup and restore. The agent configuration was modified, changing the hostname in the sourceDbHost and sourceDbUrl properties. The name displayed at the top of the right panel (Summary) and in the list of agents (displayed when you select the system name at the top of the tree in the left panel) displays the old name, but the agent is functioning as expected.

In another case, a customer had configured a DB2 for i agent using the IP address rather than the hostname. Later, the IBM i server was moved to a different network and the agent was reconfigured to use the new IP address. However, trying to update host components failed, eventually timing out and displaying the error Unable to retrieve admin user. In examining the SQ_AGENTS table in the Derby control database, it was discovered that the HOSTS field still contained the original IP address.

In a third case, a company acquisition required the customer to modify the sourceDbHost and sourceDbUrl properties to reflect a new domain name. Here the source system was running DB2 for i and the customer was using SNDMSG to pause the agent during the nightly maintenance on the source system.

The agent's request to the Launch Agent to pause itself was failing and the following error appeared in sq_launcher0.log:

WARNING: CaptureAgentLog: stopAgent Failed to stop agent.
java.rmi.RemoteException: Agent: MYRDB@myas400.newdomain.com does not exist.
at com.starquest.sqdr.capture.LaunchAgent.stopAgent

Solution:

Use ij (interactive SQL scripting tool for Derby) to modify the SQDR.SQ_AGENTS table in the Launch Agent's control database (Derby database SQDRPLUS). We recommend Backing up the Derby Control Database before making any changes with the ij tool.

1. Start ij from the Tools subdirectory of the SQDR Plus program group (see Using ij to perform SQL operations on the Derby control database used by Launch Agent for alternate ways to start ij).
2. Enter the following at the ij> prompt:

connect 'jdbc:derby://localhost:1527/SQDRPLUS;user=SQDR;password=mypassword';

where mypassword is the Derby password that you supplied during installation of SQDR Plus, used to connect to the SQDR Control Center.

3. After connecting, display the current contents of the SQ_AGENTS table:

SELECT NAME,HOST FROM SQ_AGENTS where NAME like '%MYRDB%';

4. Modify the NAME and HOST fields:

update SQ_AGENTS set NAME='MYRDB@myas400.newdomain.com' where NAME like '%MYRDB%';
update SQ_AGENTS set HOST='myas400.newdomain.com' where NAME like '%MYRDB%';

The name change will appear immediately in the Control Center.

5. Restart the SQDR Launch Agent service in the Services control panel (Windows) or run /etc/init.d/sqdr-agent restart (Linux) so that the change gets propagated to the functional side.

Using SQL Server Linked Server to connect to the Derby control database

If you have SQL Server installed, you can use the StarSQL ODBC driver bundled with SQDR to create a Linked Server to the Derby control database, as described in How to Use a Microsoft SQL Server Linked Server to Access DB2 with StarSQL. The connection can be defined with either an ODBC data source or a connection string. A typical connection string is

Driver={StarSQL (64-bit)}; Server=SQDRPLUS;HostName=127.0.0.1;Port=1527

On the Security panel, enter user=SQDR and the Derby password that you supplied during installation of SQDR Plus.

You can view a list of tables in SQL Server Management Studio by expanding the Linked Server object.

To issue a SQL statement, use the following format:

SELECT * FROM OPENQUERY (DERBY, 'SELECT * FROM SQ_AGENTS')

Using IBM Data Studio to connect to the Derby control database

If you have IBM Data Studio installed, you can use it to examine and modify the Derby control database, using either the StarSQL for Java JDBC driver or the Derby JDBC driver; both drivers are included in the SQDR Plus distribution. You will need to configure the location of the .jar file in Data Studio preferences.

For using the StarSQL for Java JDBC driver, follow the instructions in the technical document Using StarSQL for Java with IBM Data Studio. The location of StarSQL_JDBC.jar is in the directory \Program Files\StarQuest\sqdrplus\StarAdmin. A typical connection URL is JDBC://127.0.0.1:1527/SQDRPLUS;decimalDelimiter=host; sendUnicode=True

For using the Derby JDBC driver:

1. In the Data Source Explorer, right-click on Database Connections and select New...
2. Select Derby from the list of database types in the left panel.
3. In the right panel, select Derby 10.8 Derby Client JDBC Driver Default from the dropdown list for JDBC driver.
4. Select the ... button next to that list.
5. Remove the line that shows derbyclient.jar as unqualfied.
6. Select Add JAR/.zip and navigate to the location of derbyclient.jar (typically \Program Files\StarQuest\sqdrplus\derby\lib), select derbyclient.jar, and click OK.
7. Enter the following values:
   

Database SQDRPLUS
Host: localhost
Port: 1527
User name: SQDR
Password: enter the Derby password

The resulting URL will be jdbc:derby://localhost:1527/SQDRPLUS

For both JDBC drivers: The new connection will now appear under Database Connections and is ready to use. The connection can now be used to browse and administer the database.

Changing SQDR Plus ProgramData directory

When migrating SQDR Plus to a new server, if the location of the StarQuest ProgamData has changed - for example, when migrating to a clustered configuration where ProgramData is located on the shared S: drive rather than C: - then it is necessary to update the DIRECTORY column of the Derby SQ_AGENTS table.

For each agent, issue SQL similar to:

UPDATE SQ_AGENTS values(' S:\ProgramData\StarQuest\sqdrplus\conf\agt0) where CONTROLDB='SQDRP0'

Configuring Non-standard RMI Ports

The SQDR Plus Launch Agent and its agents communicate with each other using RMI (Remote Method Invocation). The Launch Agent uses port 50005 and each agent a succeeding port - i.e. the first agent will use 50006, the second agent 50007, etc.

If these ports are in use by another application, you can configure SQDR Plus to use a different range of ports by doing the following. This is most easily done after installing SQDR Plus, but before creating an agent.

modify the Derby SQ_AGENTS control table using ij or any ad-hoc SQL tool

ij> connect 'jdbc:derby://localhost:1527/SQDRPLUS;user=SQDR;password=mypassword';
ij> SELECT NAME, AGENTPORT FROM SQ_AGENTS;
ij> UPDATE SQ_AGENTS SET AGENTPORT=60005 WHERE AGENTPORT=50005;

If any agents have been created, update the corresponding row in SQ_AGENTS.

Then restart the SQDR Plus Launch Agent service.

Enabling Statement Logging

Under direction of Stelo Support, you may be asked to enable Derby statement logging for troubleshooting.

1. Identify the ProgramData directory for Derby. This is typically C:\ProgramData\StarQuest\sqrdplus\derby (Windows) or /var/sqdrplus/derby (Linux).
2. Clear or rename derby.log.
3. Add the following to derby.properties and restart the Derby service:
   derby.language.logStatementText=true
4. When the information has been captured, disable statement logging by removing or commenting out the above statement or setting it to false.

Statement logging information is written to derby.log.