StarQuest Technical Documents

Change Data Processing: Java example

Last Update: 13 Sept 2019
Product: StarQuest Data Replicator
Version: SQDR 4.5 or later
Article ID: SQV00DR032

Abstract

SQDR can be configured to invoke a user-supplied stored procedure to process the Change Data information for purposes of auditing or "push" notifications in addition to, or instead of, updating a destination table. This function is described in detail in the technical document Using SQDR Change Data Processing.

This technical document provides a sample Java stored procedure that logs the Change Data information to a local text file. This sample can be used as a template for performing other tasks such as writing to a message system such as RabbitMQ, IBM WebSphere MQ or Microsoft Message Queuing (MSMQ).

The process illustrated below uses IBM Data Studio to create the Java stored procedure in the DB2 for LUW database being used by SQDR for its control database. In the simplest scenario, use the same credentials (local Windows or UNIX user sqdr) to connect Data Studio to the database (i.e. that user will be used to create the stored procedure), and place the stored procedure in the SQDR schema, alongside the SQDR control tables and the StarQuest-supplied stored procedure SQLOGGER. However, Data Studio also allows you to easily create a Java stored procedure in a destination DB2 database, to use different credentials, and to specify a different target schema. You can also create the stored procedure with Java tools like javac and jar and register it in DB2 with CREATE PROCEDURE and CALL SQLJ.INSTALL_JAR.

This sample creates uniquely-named text files in C:\temp (Windows) or /tmp (UNIX). Make sure the target directory exists.

Solution

• Download and install IBM Data Studio Client. In our example, Data Studio is installed on the same machine as SQDR, SQDR Plus, and the DB2 LUW control databases.
• Start Data Studio and choose a workspace.
• In the Data Source Explorer perspective, create a connection to the database of interest (the control database or the target database) if necessary. If Data Studio is running on the same machine as SQDR and DB2 for LUW, there may be an existing connection.
  • Select Database Connections and right click/New..
  • In the Connection Parameters dialog, select DB2 for Linux, UNIX, & Windows in the left column.
  • For a local control database, enter SQDRC for the Database name, 127.0.0.1 for the host and 50000 for the port. Enter sqdr as the user, enter the password, select the Save Password checkbox, and select Test the Connection. Click OK.
  • OR To use a destination database instead of the control database, enter the appropriate host address, port, and the credentials of a user with authority to create a procedure, and the default schema if you wish to use a schema that differs from the user name
    

In the Data Project Explorer perspective:

• Select File/New.. from the menu.
• Select Data Development Project.
• Enter a meaningful name - e.g. SQLOGTOFILE-ControlDB and select Next.
• Choose the connection and select Next.
• Verify the default schema and finish creating the project.
• After creating the new project, expand it.
  
• Right click on Stored Procedures, select New/Stored Procedure and enter the following information:

Name e.g. SQLOGTOFILE
Language: Java
Java package: e.g. com.starquest.logtofile
Jar Name: e.g. SQLOGTOFILE

• Under Template, choose Deploy & Run (Dynamic JDBC) IN/OUT Parameters.
• Click Finish.

To open a stored procedure, click on the stored procedure name, not the Java source file name. You will see two panels - DDL and Java.

• Paste the following into the DDL panel, overwriting the CREATE PROCEDURE statement created by Data Studio:

CREATE PROCEDURE SQLOGTOFILE ( IN groupId CHAR (16) FOR BIT DATA,
IN subscriptionId CHAR (16) FOR BIT DATA,
IN resyncState CHAR(1),
IN transaction_id CHAR (10) FOR BIT DATA,
IN changeRowCounter BIGINT,
IN changeRowSequence BIGINT,
IN changeRowLogDetail VARCHAR(512),
IN change_row_member VARCHAR(10),
IN changeRowTimestamp TIMESTAMP,
IN changeRowNanosecond INTEGER,
IN changeRowType CHAR(1),
IN actionTaken CHAR(1),
IN resultRowCount INTEGER,
IN flaggedCount BIGINT,
IN keylogTs TIMESTAMP,
IN resultError CLOB(64000),
IN beforeKey CLOB(64000),
IN beforeKeyEdited CLOB(64000),
IN afterKey CLOB(64000),
IN afterKeyEdited CLOB(64000),
IN afterImage CLOB(64000) )

NOT DETERMINISTIC CALLED ON NULL INPUT
LANGUAGE Java
EXTERNAL NAME 'SQLOGTOFILE:com.starquest.logtofile.SQLOGTOFILE.x_SQLOGTOFILE'
FENCED
THREADSAFE
PARAMETER STYLE JAVA

 

• Paste the following into the Java panel, overwriting the Java program created by Data Studio:

package com.starquest.logtofile;

import java.sql.Clob;
import java.net.URI;
import java.sql.Timestamp;
import java.io.IOException;
import java.io.PrintStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.OpenOption;
import java.nio.file.StandardOpenOption;
import java.math.BigInteger;

public class SQLOGTOFILE{

public static void x_SQLOGTOFILE (byte[] groupId, byte[] subscriptionId,String resyncState, byte[] transaction_id, long changeRowCounter, long changeRowSequence, String changeRowLogDetail, String change_row_member,
Timestamp changeRowTimestamp, int changeRowNanosecond, String changeRowType, String actionTaken,
int resultRowCount, long flaggedCount,
Timestamp keylogTs, Clob resultError, Clob beforeKey,
Clob beforeKeyEdited, Clob afterKey, Clob afterKeyEdited, Clob afterImage) {

try {
OpenOption[] options = {StandardOpenOption.CREATE,
StandardOpenOption.APPEND};

String filename = "log" + (new BigInteger(groupId)).toString(16) + ".txt";

String fileSeparator = System.getProperty("file.separator");
URI uri;
if (fileSeparator.equals("\\"))
uri = new URI("file:///C:/temp/" + filename);
else
uri = new URI("file:///tmp/" + filename);

Path p = Paths.get(uri);

PrintStream out = new PrintStream(

Files.newOutputStream(p, options));

out.println("<dml_type>" + changeRowType + "</dml_type>" +
"<beforekey>" + (beforeKey != null ? beforeKey.getSubString(1, (int) beforeKey.length()) : "NULL") + "</beforekey>" +
"<afterkey>" + (afterKey != null ? afterKey.getSubString(1, (int) afterKey.length()) : "NULL") + "</afterkey>" +
"<afterimage>" + (afterImage != null ? afterImage.getSubString(1, (int) afterImage.length()) : "NULL") + "</afterimage>" );

out.close();

} catch (Exception e) {
e.printStackTrace();
}
}
}

• Save the stored procedure. If you get a warning dialog Unable to locate a suitable static public void method in procedure .., select Save rather than Sync.
• Right click on the stored procedure and select Deploy.
• Review the output in the SQL Results panel to confirm that the stored procedure was deployed successfully.

If you get the error

"YOURUSER" does not have the privilege to perform operation "CREATE PROCEDURE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.18.60

then ask a DBA Admin to issue the following grant:
grant create_external_routine on database to YOURUSER

You are now ready to invoke the stored procedure from SQDR:

• Open Data Replicator Manager.
• Right-click on the incremental group and select Properties.
• Go to the Advanced panel.
• In the Change Data section:
  • Select the desired logging level from the dropdown list (e.g. Data-Always).
  • Select the appropriate database (Control DBMS or Destination DBMS).
  • Enter the name of the stored procedure (e.g. SQLOGTOFILE).
• Click OK.

The stored procedure will be invoked whenever change data is received by SQDR. Verify that the text file gets created and contains the desired information.