Introduction

The cdr sync command provides a convenient way to perform a complete synchronization of replicated tables as a foreground process that can be run within a script. The cdr check command determines which rows are out of sync, and can perform a targeted repair of only those rows.

To use the cdr sync and cdr check commands, you must first compile, install, and register the checksum function. The zip file that accompanies this article contains the necessary checksum source code and registration statements. The cdr sync and cdr check commands are described in the IBM Informix Server Enterprise Replication Guide, version 10.00.xC4, accessible from the Resources section of this article.

The cdr check command uses a 32-bit CRC checksum as part of its algorithm to determine which rows need synchronization. The checksum routines are implemented in a checksum() user defined routine (UDR) that you must install prior to using the cdr check command. This UDR must be installed on all nodes participating in the check.

What is a UDR?

A UDR (user defined routine) is nothing more than a function call. UDRs are registered with the database through the "CREATE FUNCTION" SQL statement. Informix Dynamic Server has supported overloading of functions since version 9.0. That means that multiple procedures can have the same name as long as they have different signatures. (By signature, I mean the parameter types passed to them.)

IDS supports functions written either in stored procedure language (SPL), or in C or Java™. In our case, we will use C to write the stored procedure.

When you write a UDR in C, you use the normal compiler to compile the UDR, but do not compile to an executable binary. Instead, you compile to a shared object. Then when you register the UDR with the "CREATE FUNCTION" SQL statement, you identify the location where the shared object will be placed, and the function within the shared object that is to be executed. The UDR must be registered in any of the databases that will utilize it. For more information on the writing of UDRs, see DataBlade API Function Reference, Version10.0 and DataBlade API Programmer's Guide Version 10.0, accessible from the Resources section.

Enabling cdr check with the checksum UDR

To use the cdr sync and cdr check commands, you need the checksum UDR that is included with this article.

To compile, install, and register the checksum UDR:

1. Compile the checksum UDR once for each operating system in your replication domain.
2. Install the checksum UDR on every computer with the IDS database server running Enterprise Replication (ER).
3. Register the checksum UDR in every database server running ER.

Compiling the checksum UDR

The requirements for compiling the UDR source code is described in the IBM Informix DataBlade API Programmer’s Guide, chapter 12, "Developing a User-Defined Routine" (see Resources).

Since the output of the compilation is a shared object, and the rules for generating a shared object are platform specific, you must examine the documentation as well as the compiler documentation for your server. This article describes how to compile on Solaris, Linux®, and Windows® XP.

Linux 32-bit

Compiling the UDR on Linux using gcc version 3.2.3 20030502 (Red Hat Linux 3.2.3-53) is fairly simple. For example, the following script could be used:

gcc -shared \
    -I${INFORMIXDIR}/incl/public -I${INFORMIXDIR}/incl \
    -c checksum.c
ld -shared  checksum.o -o checksum.so

Solaris 32-bit

Compiling on Solaris is very similar to compiling on Linux. The following script was tested on Sun Workshop 6, C 5.1:

cc -G \
   -I${INFORMIXDIR}/incl/public -I${INFORMIXDIR}/incl \
   -c checksum.c
ld -G  checksum.o -o checksum.so

AIX® 64-bit

The following makefile was used to build the checksum UDR on an AIX 64-bit system.:

#
# Compiler/Linker flags specific to AIX
#

CC = cc
LD = ld
NM = nm
CFLAGS = -q64 -shared -qchars=signed -D_H_LOCALEDEF -DINFX_ANSI -D_LARGE_FILES
PICFLAGS = -lm
SOFLAGS = -G -b64 -bnoentry

LIBSO = checksum.so

TARGETS =${LIBSO}

.SUFFIXES: .c .o


all: $(TARGETS)

checksum.so: checksum.c
        $(CC) $(CFLAGS) $(PICFLAGS) -I${INFORMIXDIR}/incl/public -I${INFORMIXDIR}/incl/ -c checksum.c
        $(NM) -X64 -g checksum.o | sed "/ U /d" | cut -f1 -d" " | sed "/.o:/d" | sed -e "s/^\.//" | sort -u > checksum.exp
        $(LD) $(SOFLAGS) -bE:checksum.exp -o checksum.so checksum.o -lm -lc
        chmod 755 checksum.so

clean::
        rm -f ${LIBSO} *.o

Windows XP

Compiling on Windows XP was tested on Visual Studio version 6 with the compiler version 12.00.8168 for 80x86 and on .NET with the compiler version 13.10.3077 for 80x86. The Windows shared object is normally referred to as a DLL file (dynamic link library). The most significant difference with the Windows compilation is that you need to include a .def file into the link edit containing all of the entry points that will be externalized. Otherwise, the server cannot resolve the function names when the DLL is loaded into the server. The DEF file is include in the accompanying zip file. The DEF file has the following content:

;  checksum.def  : Declares the module parameters for the DLL.

LIBRARY     "CHECKSUM"
DESCRIPTION 'CHECKSUM Windows Dynamic Link Library'

EXPORTS
    ; Explicit exports can go here

    lvarchar_checksum       @1
    integer_checksum        @2
    short_checksum          @3
    int8_checksum           @4
    decimal_checksum        @5
    money_checksum          @6
    real_checksum           @7
    double_checksum         @8
    date_checksum           @9
    datetime_checksum       @10
    interval_checksum       @11
    collection_checksum     @12
    row_checksum            @13
    sblob_checksum          @14
    classicBlob_checksum    @15

The compilation needs to be run in both the Informix environment and in the Windows environment for the C compiler. This means that you will need to have run the vcvars32.bat file from the Visual C installation as well as having set up the Informix environment. For example, the environmental variable %MSDevDir% should be set to the location of the C compiler include files. This is set in the vcvars32.bat file which is used to establish the compiler’s environment. The script to compile on Windows is:

cl /DNT_MI_SAPI /DMI_SERVBUILD –I%MSDevDir$\include 
   -I%INFORMIXDIR%\incl\public –I%INFORMIXDIR%\incl –c checksum.c
link /DLL /OUT:checksum.dll /DEF:checksum.def checksum:obj
    %INFORMIXDIR%\lib\SAPI.LIB

Installing the UDR

After you compile and link the UDR into a shared object, you must install it into a secured location that is accessible from the server, such as, under the $INFORMIXDIR/extend directory. For instance, the checksum UDR shared object file would be placed in $INFORMIXDIR/extend/checksum.

On UNIX® and Linux computers, the directory must be owned by user informix, placed in the informix group, and be non-writable by anyone except user informix (drwxr-xr-x). Similarly, the shared object file must be owned by user informix, in the informix group, and have (drwxrwxr-x) permissions.

On Windows computers, the DDL must be read-only. This can be set by running "attrib +r".

Registering the UDR

The zip file contains an SQL script called "checksum.sql". This SQL script contains the necessary SQL statements to register the UDR, as well as to load the shared object into the server. You must modify the script, however, if you put the shared object file in a directory other than $INFORMIXDIR/extend/checksum.

For example, to run this script against the stores database on the server node1, use the following command:

dbaccess –e stores@node1 checksum

The script uses the following SQL statements to register the UDR.

create function checksum(p1 lvarchar, p2 integer)
    RETURNS integer
    with (NOT VARIANT, HANDLESNULLS, PARALLELIZABLE)
    EXTERNAL NAME "$INFORMIXDIR/extend/checksum/checksum.so(lvarchar_checksum)"
    LANGUAGE C;

create function checksum(p1 integer, p2 integer)
    RETURNS integer
    with (NOT VARIANT, HANDLESNULLS, PARALLELIZABLE)
    EXTERNAL NAME "$INFORMIXDIR/extend/checksum/checksum.so(integer_checksum)"
    LANGUAGE C;

create function checksum(p1 references byte, p2 integer)
    RETURNS integer
    with (NOT VARIANT, HANDLESNULLS, PARALLELIZABLE)
    EXTERNAL NAME "$INFORMIXDIR/extend/checksum/checksum.so(classicBlob_checksum)"

The first CREATE statement creates the entry for the function to generate the checksum for a variable length field. The function returns an integer that is the 32-bit CRC checksum.

The WITH clause contains a list of characteristics of the checksum UDR:

• It will always return the same results if it is passed the same parameters (NOT VARIANT).
• It is able to handle null parameters (HANDLESNULLS).
• It is thread safe (PARALLELIZABLE).

This is followed by the location of the shared object ($INFORMIXDIR/extend/checksum/checksum.so). The value in parentheses (lvarchar_checksum) is the function to execute within the shared object when the UDR is invoked.

The UDR can be tested by running a query against the database in which it was registered. For instance, if you have registered the UDR to the stores database, then you could run the following commands:

Dbaccess stores_demo <<!EOF
Select checksum(lname, 0), checksum(fname, 0) from customers;
!EOF

Alternatively, you could run the following equivalent commands:

Dbaccess stores_demo <<!EOF
Select (checksum(lname, checksum(fname,0)) from customers;
!EOF

You are now ready to use cdr check and cdr sync.

Back to top

Download

Information about download methods

Resources

Learn

• Learn more in the Informix documentation. This page includes links to the Enterprise Replication Guide, the Datablade API Programmer's Guide, and the Datablade API Function Reference.
  
  
• The Informix Dynamic Server V10.0 Informaiton Center is a resource for all kinds of information about IDS.
  
  
• The artilce "Schema evolution in an Enterprise Replication environment" (developerWorks, June 2005) provides more information about Enterprise Replication.
  
  
• Visit the developerWorks Informix zone to expand your Informix skills.
  
  
• Stay current with developerWorks technical events and Webcasts.
  
  

Get products and technologies

• Download a free trial version of Informix Dynamic Server Version 10.0.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  

About the author

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• What is a UDR?
• Enabling cdr check with the checksum UDR
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Informix Dynamic Server Enterprise Edition
• Demo: Use DB2 First Steps and the DB2 Control Center
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: Informix Dynamic Server Enterprise Edition

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers