Quick start for Q replication to Oracle and Sybase

Document options Document options requiring JavaScript are not displayed New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

Level: Intermediate

Dell Burner (dellb@us.ibm.com), Technical writer, IBM

12 May 2005

Find out about the newly released federated capability in WebSphere Information Integrator Q replication, and walk through the process of setting up and using WebSphere MQ, WebSphere Information Integrator Replication Edition, and DB2 Universal Database to replicate to Oracle and Sybase targets.

Introduction

IBM® WebSphere® Information Integrator Q replication now lets you replicate committed transactional data from IBM DB2® Universal Database™ sources to targets in Oracle and Sybase.

This new feature combines Q replication with the federated server capabilities of WebSphere Information Integrator to provide a low-latency, high-throughput solution for non-DB2 targets.

As Figure 1 shows, this feature works much like a Q replication scenario where both source and target are DB2 Universal Database servers. WebSphere MQ is configured as it would be between two DB2 databases. Transactions are replicated from DB2 Universal Database to Oracle or Sybase using the existing Q Capture and Q Apply programs. The Q Capture program runs on the source system, reading DB2 recovery logs for changed source data, and writing it to WebSphere MQ queues.

The primary difference is on the Q Apply side. With an Oracle or Sybase target, the Q Apply program runs on a WebSphere Information Integrator federated server, retrieving captured changes from queues and writing them to Oracle and Sybase targets by using federated server nicknames. (A nickname is a pointer to the non-DB2 target rather than a physical table.)

Q replication's federated capability provides the sophisticated Q Apply engine that determines transaction dependencies and replays transactions on the target system via nicknames to maximize parallelism and minimize latency.

Federated Q replication also features the same wizard-driven graphical user interface (GUI), command-line processor, and script-driven processes to configure the replication environment. Integrated monitoring and statistics make it easier to react to problems and maintain system health.

The following federated Q replication support is available in WebSphere Information Integrator Version 8.2.2 (Version 8.1 Fix Pack 9):

• Sources and targets: Unidirectional replication is supported from DB2 Universal Database for z/OS®, Linux™, UNIX®, and Windows™ to Oracle and Sybase targets using the federated wrappers that are defined specifically for Oracle (NET8 and SQLNET) and Sybase (CTLIB). You can transform data by replicating to DB2 stored procedures that write to nicknames. The Q Apply program can load source data into one or more non-DB2 target tables in parallel by using the EXPORT and IMPORT utilities.
• Infrastructure: When you configure Q replication for non-DB2 targets, several Q Apply control tables are created on the target system and accessed through nicknames just as target tables are. This is because Q Apply writes to these tables during the same unit of work as the changes to target tables. One set of control tables is needed for each federated target database.
• Application of data to targets: Replication requires columns of like attributes (for example, INTEGER NOT NULL from the source table to INTEGER NOT NULL at the nickname). Q replication can also replicate source columns to nickname columns with different but compatible attributes. For example, you can replicate from small column data lengths at the source to larger data lengths at the target.
• Utilities: The Replication Alert Monitor and table differencing and reconciliation utilities (asntdiff and asntrep) are supported for non-DB2 targets. The asntdiff utility compares the source table with the nickname. The asntrep utility updates the nickname to bring the source and target into synch.

Both of the WebSphere Information Integrator products now provide access to non-DB2 platforms. Table 1 compares supported source and target platforms for SQL replication and Q replication.

Table 1. Supported source and target platforms for SQL and Q replication

You can learn more about Q replication and SQL replication in the Introduction to Replication and Event Publishing (GC18-7567-00), available in softcopy at the DB2 UDB, DB2 Connect and DB2 Information Integrator Version 8 product manuals page.

Software prerequisites

Required software on the system where the Q Capture program runs remains the same as for version 8.2. The system where the Q Apply program runs must be updated to WebSphere Information Integrator Version 8.2.2 (Version 8.1 Fix Pack 9).

The following software is required on the system where the Q Apply program runs, in addition to the Oracle or Sybase client software:

• WebSphere Information Integrator Version 8.2 (with Relational Wrappers component) with Fix Pack 9 installed
• DB2 Universal Database Version 8.2
• WebSphere MQ Version 5.3 (this software is bundled with the WebSphere Information Integrator package)

Related references

For more detail on planning, configuring, administering, monitoring, maintaining, and tuning a Q replication environment as well as the federated objects that support it, see the DB2 Information Center and Tuning for Replication and Event Publishing Performance (SC18-9289-00), available in softcopy at the DB2 UDB, DB2 Connect and DB2 Information Integrator Version 8 product manuals page.

Back to top

Overview of setup tasks for federated Q replication

Setting up Q replication to Oracle or Sybase targets involves the following steps:

1. Create WebSphere MQ objects.
2. Configure the federated Q Apply server and the non-DB2 target server and create federated objects.
3. Create Q replication objects.
4. Start replication.

Figure 2 summarizes the steps.

Note about terminology

The terms "source" and "target" can be somewhat confusing in the world of federated replication. Throughout this article, the term "target" is used to refer to both the WebSphere Information Integrator federated server, where the Q Apply program applies transactions to nicknames, and to the non-DB2 database that contains the corresponding target tables. In other WebSphere federated documentation, the non-DB2 targets are referred to as "data sources." Figure 3 depicts these relationships.

Back to top

Creating WebSphere MQ queue managers

You create WebSphere MQ queue managers to handle queues, channels, and messages that provide the pathway for replicated data and communication between the Q Capture program at the source database and the federated Q Apply server.

About this task

This procedure creates queue managers on the systems where the Q Capture and Q Apply programs run. Q replication also supports WebSphere MQ client-server configurations where the Q Capture and Q Apply programs run on a system with an MQ client installed and connect to a remote queue manager. For more information about WebSphere MQ client-server support, see the Technote Q replication supports the WebSphere MQ Client - new functionality on the WebSphere Information Integrator support Web site.

Recommendation: Use queue managers on the same systems as the Q Capture and Q Apply programs for better replication performance.

Procedure

To create WebSphere MQ queue managers:

1. On the system where the Q Capture program runs, issue the following command from an operating system prompt:

   crtmqm queue_manager_name

   
   
2. Issue the crtmqm command in the same form as above on the system where the Q Apply program runs.

Back to top

Creating the source and target queues

You can use WebSphere MQ script (MQSC) commands to create the queues that the Q Capture program uses at the source and the Q Apply program uses at the federated Q Apply server.

Before you begin

Create queue managers for the Q Capture and Q Apply programs.

About this task

Q replication supports one-way replication from DB2 sources to Oracle and Sybase targets, so the required queues are the same as for unidirectional replication. See WebSphere MQ objects required for unidirectional replication (remote) in the DB2 Information Center for more detail.

Procedure

To create the source and target queues:

1. Start the queue manager at the source system by issuing the following command:

   strmqm queue_manager_name

   
   
2. Start an interactive MQSC session with the source queue manager by issuing the following command:

   runmqsc queue_manager_name

   
   
3. Create the source queues by issuing the commands in Table 2.

   Queue	Purpose	MQSC command
   Send queue	Directs transaction and control messages from Q Capture to Q Apply	DEFINE QREMOTE('send_queue_name') RNAME('receive_queue_name') RQMNAME('remote_queue_manager_name') XMITQ('transmit_queue_name')
   Administration queue	Receives control messages from Q Apply to Q Capture	DEFINE QLOCAL('Q_Capture_admin_queue_name')
   Restart queue	Holds one message that tells Q Capture where to start reading in the DB2 recovery log after a restart	DEFINE QLOCAL('restart_queue_name')
   Transmission queue	Holds transaction and informational messages from Q Capture that are bound for remote Q Apply	DEFINE QLOCAL('transmit_queue_name') USAGE(XMITQ)

4. Stop the interactive MQSC session with the source queue manager by issuing the end command.
5. Start the queue manager at the target system by issuing the following command:

   strmqm queue_manager_name

   
   
6. Start an interactive MQSC session with the target queue manager by issuing the following command:

   runmqsc queue_manager_name

   
   
7. Create the target queues by issuing the commands in Table 3.

   Queue	Purpose	MQSC command
   Receive queue	Receives transaction and control messages from Q Capture to Q Apply	DEFINE QLOCAL('receive_queue_name')
   Administration queue	Directs control messages from Q Apply to Q Capture	DEFINE QREMOTE('Q_Apply_admin_queue_name') RNAME('Q_Capture_admin_queue_name') RQMNAME('remote_queue_manager_name') XMITQ('transmit_queue_name')
   Transmission queue	Holds control messages from Q Apply that are bound for remote Q Capture	DEFINE QLOCAL('transmit_queue_name') USAGE(XMITQ)

Back to top

Creating WebSphere MQ channels between the source and target

You create WebSphere MQ channels between the source and target queue managers to transmit messages between the Q Capture program at the source database and the Q Apply program at the federated Q Apply server.

Before you begin

• Create queue managers for the Q Capture and Q Apply programs.
• Create the source and target queues.

About this task

Each channel has two ends: a sender channel that is defined within the originating queue manager, and a receiver channel that is defined within the destination queue manager, as shown in Figure 4.

Procedure

To create channels between the source and target:

1. Ensure that the queue manager for the source system is running by issuing the following command:

   strmqm queue_manager_name

   
   
2. Start an interactive MQSC session with the source queue manager by issuing the following command:

   runmqsc queue_manager_name

   
   
3. Define the sender channel from the source queue manager to the target queue manager (using the TCP/IP transmission protocol) by entering the following command:

   DEFINE CHL ('source_sender_channel_name') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('target_IP_address(port)') XMITQ('source_transmit_queue_name') DISCINT(0)

   
   

   If you omit the port value the default WebSphere MQ port number of 1414 is used. You can check the /etc/services file on Linux or UNIX or the \etc\services file on Windows at the source system to verify that this port is unused.

4. Define a receiver channel at the source queue manager from the target queue manager by entering this command:

   DEFINE CHL ('source_receiver_channel_name') CHLTYPE(RCVR) TRPTYPE(TCP)

   
   
5. Stop the interactive MQSC session with the source queue manager by issuing the end command.
6. Start the queue manager at the target system by issuing the following command:

   strmqm queue_manager_name

   
   
7. Start an interactive MQSC session with the target queue manager by issuing the following command:

   runmqsc queue_manager_name

   
   
8. Define the sender channel from the target queue manager to the source queue manager (using the TCP/IP transmission protocol) by entering the following command:

   DEFINE CHL ('target_sender_channel_name') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('source_IP_address(port)') XMITQ('target_transmit_queue_name') DISCINT(0)

   
   
9. Define a receiver channel at the target queue manager from the source queue manager by entering this command:

   DEFINE CHL ('target_receiver_channel_name') CHLTYPE(RCVR) TRPTYPE(TCP)

   
   

Back to top

Configuring the source and target systems for federated Q replication

Before you can replicate to a non-DB2 target, you need to configure the DB2 source database where the Q Capture program runs and configure the DB2 instance and database on the system where the Q Apply program runs.

Procedure

To configure the source and target systems for federated Q replication:

1. Linux, UNIX, Windows: Enable archival logging at the source database by using one of the following methods:

   Method	Description
   Replication Center	Use the Turn on Archive Logging window. To open the window, open the Q Capture Servers folder, right-click the server that you want to configure, and click Enable Database for Q Replication.

   
   Figure 5. Opening the Turn on Archive Logging window
   
   

   Method

   Description

   UPDATE DB CFG command

   Issue the following command: UPDATE DB CFG FOR database USING LOGRETAIN RECOVERY Where database is the source database. Tip: After you issue this command, the database is placed in a BACKUP PENDING state, which requires a full offline backup of the database. You can use the DB2 Control Center or the BACKUP DATABASE command, where path is the location where you want the backup image to be stored: BACKUP DATABASE database_name TO path

2. Turn on federated support for the Q Apply server by using one of the following methods:

   Method	Description
   Control Center	Use the DBM Configuration window. To open the window, right-click the DB2 instance in the object tree that contains the Q Apply database and click Configure Parameters. Under the Environment heading, click Federated and click to turn on federated support.

   
   Figure 6. DBM Configuration window in the Control Center
   
   

   Method	Description
   UPDATE DBM CFG command	Ensure that you are attached to the instance that contains the Q Apply server, and issue the following command: UPDATE DBM CFG USING FEDERATED YES

   You must stop and restart the instance for the change to take effect.

3. Set the Oracle or Sybase environment variables in the db2dj.ini file on the federated Q Apply server.

   If you installed the Oracle or Sybase client software before you installed WebSphere Information Integrator, the required environment variables are set in the db2dj.ini file. If not, use one of the following methods:

   Method	Description
   Automatic	Run the WebSphere Information Integrator installation program again and specify the Typical installation option. Follow the instructions in the wizard. Tip: Running the WebSphere Information Integrator installation will set only the required environment variables. The optional environment variables must be set manually.
   Manual	Edit the db2dj.ini file. On federated servers that run Linux or UNIX, this file is in the sqllib/cfg directory. On federated servers that run Windows, this file is in the sqllib\cfg or %DB2PATH%\cfg directory.

   The db2dj.ini file contains configuration information about the Oracle or Sybase client software that is installed on your federated server. If the file does not exist, you can create a file with the name db2dj.ini by using any text editor. In the db2dj.ini file, you must specify the fully qualified path for the environment variables; otherwise you will receive errors.

   For more information about required and optional environment variables, see Setting the Oracle environment variables or Setting the Sybase environment variables in the DB2 Information Center.

4. Linux or UNIX: Add the environment variables for your target database to the .profile file of the DB2 instance:
   • Oracle: For an Oracle database, run the following commands where oracle_home_directory is the directory where the Oracle client software is installed:

     export ORACLE_HOME=oracle_home_directory export PATH=$ORACLE_HOME/bin:$PATH

     
     
   • Sybase: For a Sybase database, run the following commands where sybase_home_directory is the directory where the Sybase client software is installed and OCS-version_release is the version and release of the Sybase Open Client that is installed:

     export SYBASE=sybase_home_directory export SYBASE_OCS=OCS-version_release export PATH=$SYBASE/bin:$PATH

     
     
5. Linux or UNIX: Execute the DB2 instance .profile file by entering:

   . $HOME/.profile

   
   

   Verify that the environment variables are part of the db2dj.ini file. Also, to ensure that the federated Q Apply server reads the new values of the variables, stop and restart the DB2 instance.

6. Set up the client configuration file for your target database on the system where Q Apply runs:
   • Oracle: Specify the TCP/IP address, port number, service name, and other information for connecting to the Oracle database in the tnsnames.ora file in the following path:
     • Linux or UNIX: $ORACLE_HOME/network/admin
     • Windows: %ORACLE_HOME%\NETWORK\ADMIN
     You can use the Oracle Net Configuration Assistant utility that comes with the Oracle client software to create and configure the tnsnames.ora file. See the installation documentation from Oracle for more information about using this utility. Within the tnsnames.ora file, the SID (or SERVICE_NAME) is the name of the Oracle instance, and the HOST is the host name where the Oracle server is located.

     See Setting up and testing the Oracle client configuration file in the DB2 Information Center for more details.

   • Sybase: Specify the location of the Sybase SQL Server or Adaptive Server Enterprise instance and the type of connection (protocol) for the database server in the interfaces file in the $SYBASE directory.

     Copy the interfaces file to the $HOME/sqllib directory of the federated Q Apply server's DB2 instance.

     You can also create a link to the interfaces file or use the IFILE server option in the CREATE SERVER statement to specify the full path to the file. See Setting up and testing the Sybase client configuration file in the DB2 Information Center for more details.

7. Test the client configuration:
   • Oracle: Use the Oracle sqlplus utility to test the connection.
   • Sybase: Use an appropriate Sybase query utility, such as isql, to test the connection.

For more information about optional configuration parameters that affect replication, see Configuring the source database to work with the Q Capture program and Configuring the target database to work with the Q Apply program in the DB2 Information Center. You can get more detailed tuning information from Tuning for Replication and Event Publishing Performance (SC18-9289-00), available in softcopy at http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html.

Back to top

Registering the Oracle or Sybase wrapper

The federated Q Apply server uses wrappers to communicate and exchange data with the non-DB2 target server. The action of "creating" a wrapper actually registers the appropriate existing wrapper for your chosen target.

Before you begin

• The DB2 instance that contains the Q Apply server must be configured for federated support.
• The wrapper must allow write access to the non-DB2 target.

Restrictions

Q replication does not support replication to non-DB2 targets through a generic wrapper such as the ODBC wrapper.

Procedure

To create an Oracle or Sybase wrapper, use one of the following methods:

CREATE WRAPPER wrapper_name

Back to top

Creating server definitions for Oracle or Sybase targets

You must create a server definition in the federated Q Apply server for each Oracle or Sybase database that will be a replication target.

Before you begin

• The DB2 Universal Database instance that contains the Q Apply server must be configured for federated support.
• You must create a wrapper for the non-DB2 target.

Procedure

To create a server definition for an Oracle or Sybase data source:

1. Locate the node name (in federated terminology, a node is a server instance):
   • Oracle: The node name is located in the tnsnames.ora file above the Definition line.
   • Sybase: The node name is located in the interfaces file in the sqllib directory.
2. Create the server definition by using one of the following methods:

   Method	Description
   Control Center	Use the Create Server Definitions window. To open the window, expand the federated Q Apply database in the object tree, expand the wrapper icon, right click the Server Definitions folder, and click Create. On the Settings page of the window, you must specify the NODE for Oracle and the NODE and DBNAME for Sybase.

   
   Figure 8. The Create Server Definition window
   
   

   Method

   Description

   CREATE SERVER statement

   Use the CREATE SERVER statement: CREATE SERVER server_definition_name TYPE target_type VERSION version_number WRAPPER wrapper OPTIONS (NODE 'node_name', DBNAME 'database_name') ; target_type: Specify Oracle or Sybase. version_number: Specify a version of the non-DB2 target database. For Oracle, versions 8i, 9i, 10, and 10g are supported. For Sybase, versions 11.0, 11.5, 11.9, 12.0, and 12.5 are supported. wrapper: For Oracle, specify NET8 or SQLNET. For Sybase, specify CTLIB. 'node_name': You can find the node name in the tnsnames.ora file for Oracle and the interfaces file for Sybase. The NODE is required for these targets. 'database_name': The DBNAME is required for Sybase targets.

Back to top

Creating user mappings for Oracle and Sybase targets

To allow the Q Apply program to connect to the Oracle or Sybase target, you must create a user mapping to associate the user ID and password for the federated Q Apply server with a user ID and password for the non-DB2 target database.

Procedure

To create a user mapping for Oracle and Sybase targets, use one of the following methods:

CREATE USER MAPPING FOR QApply_userID
SERVER server_definition_name
OPTIONS (REMOTE_AUTHID 'remote_userID', 
REMOTE_PASSWORD 'remote_password') ;

Back to top

Creating control tables for federated Q replication

Before you can replicate data to the non-DB2 target, you must create control tables to store information about Q subscriptions, message queues, operational parameters, and user preferences.

Before you begin

• The Replication Center must be able to connect to the source server and the federated server. The Replication Center will work with the non-DB2 target server using the federated server's pass-through and nickname facilities.
• You must set up federated access by creating wrappers, server definitions, and user mappings for the non-DB2 target server.
• By default, the remote authorization ID is used as the schema for the Q Apply control tables that are created in the non-DB2 target database. The user ID must have the authority to create objects using this schema.
• For the Q Capture control tables, you need the name of the WebSphere MQ queue manager that the Q Capture program uses, and the names of a local queue to use as the administration queue and a local queue to use as the restart queue.
• For the Q Apply control tables, you need the name of the WebSphere MQ queue manager that the Q Apply program uses.

  Tip: The Replication Center does not validate the WebSphere MQ queue manager and queue names. Make sure that the names that you specify when creating control tables match the WebSphere MQ object names. Otherwise, the Q Capture or Q Apply program will not run. WebSphere MQ object names are case sensitive.

About this task

For non-DB2 targets, some Q Apply control tables are created on the target system and accessed through nicknames just as target tables are. The rest of the control tables are created in the federated Q Apply server. Table 4 shows the location of the control tables.

Table 4. Location of control tables for federated Q replication

Procedure

To create control tables for the Q Capture program and Q Apply program in the Replication Center:

1. Use the Create Q Capture Control Tables wizard. To open the wizard, right-click the Q Capture Servers folder and click Create Q Capture Control Tables.
2. Use the Create Q Apply Control Tables wizard. To open the wizard, right-click the Q Apply Servers folder and click Create Q Apply Control Tables.
   1. Optional: On the Start page, if you want to specify the location of the control tables on the Q Apply server or non-DB2 server, click Custom.
      • For the Q Apply server, the control tables are created in one tablespace. You can specify an existing table space or create a new table space.
      • For the non-DB2 target server, the control tables are created in the default table space (Oracle) or default segment (Sybase), or you can specify an existing table space or segment.
   2. On the Server page, select a federated server.
      Figure 10. Server page of Create Q Apply Control Tables wizard
      
      
   3. On the Target Tables page, specify that the target tables are in a non-DB2 relational database that is mapped to the Q Apply server, and verify:
      • The server name (this is the server definition for the non-DB2 database).
      • The remote schema that the Q Apply control tables will be created under in Oracle or Sybase (the Schema field is prefilled with the remote authorization ID for the non-DB2 database, or you can change the value).
      
      Figure 11. Target tables page of the Create Q Apply Control Tables wizard
      
      
   4. On the Queue manager page, specify the queue manager that the Q Apply program uses.
   5. On the Summary page, review your choices and click Finish to generate the SQL script for creating the control tables. You can run the script, schedule it as a task in the Task Center, or save it to a file.

Creating control tables by using the ASNCLP

You can also use the ASNCLP command-line program to create Q Capture and Q Apply control tables.

Use the CREATE CONTROL TABLES FOR command. For the Q Apply control tables, specify the FEDERATED keyword. You can optionally use the RMT SCHEMA keyword to specify a schema for the control tables on the non-DB2 database. The default value is the remote authorization ID. You can also optionally specify the tablespace (Oracle) or segment (Sybase) where these remote control tables will be created.

For example, to create Q Apply control tables for replication to an Oracle target ORACLE_TARGET through a federated Q Apply server FED_DB with a remote authorization ID of ORACLE_ID:

ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER TARGET TO DB FED_DB NONIBM SERVER ORACLE_TARGET;
SET QMANAGER QM2 FOR APPLY SCHEMA;
SET APPLY SCHEMA ASN;
CREATE CONTROL TABLES FOR APPLY SERVER IN FEDERATED RMT SCHEMA ORACLE_ID;

The command creates a script that is run automatically and stored in the directory from which the command was issued. For full syntax and parameters, see Chapter 10, "Control table definition commands for Q replication" in the ASNCLP Program Reference for Replication and Event Publishing.

Back to top

Creating a replication queue map

A replication queue map links the send queue at the source and the receive queue at the target to define a pathway for replicating data. You also specify the administration queue that the Q Apply program uses to send control messages to the Q Capture program.

Before you begin

• Create the Q Capture and Q Apply control tables.
• Have the name of the send queue, receive queue, and Q Apply administration queue.

Procedure

To create a replication queue map from the Replication Center:

1. Open the Create Replication Queue Map window:
   1. Expand the Q Capture schema that identifies the Q Capture program that you want to use the queue map.
   2. Right-click the Replication Queue Maps folder and click Create.
   
   Figure 12. Create Replication Queue map window
   
   
2. On the Options page, specify the following options:
   • Maximum message length: The maximum size (in kilobytes) of a message that the Q Capture program can put on the send queue.
   • Queue error action: What the replication programs do if an error occurs at one of the queues.
   • Number of Q Apply agents: The number of agent threads that the Q Apply program uses for concurrently applying transactions from the receive queue.
   • Maximum Q Apply memory usage: The maximum amount of memory (in megabytes) that the Q Apply program uses as a buffer for messages from this receive queue.
   • Heartbeat interval: How often, in seconds, that the Q Capture program sends messages on this queue to indicate that it is still running when there are no transactions to replicate.

Creating a queue map by using the ASNCLP

In the ASNCLP, use the CREATE REPLQMAP command to create a replication queue map. For example, to create a queue map SAMPLE_ASN_TO_FED_DB_ASN that uses a send and receive queue that are both named ASN.QM1_TO_QM2.DATAQ, an administration queue ASN.QM1.ADMINQ, and the default options:

ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER CAPTURE TO DB SAMPLE;
SET SERVER TARGET TO DB FED_DB NONIBM SERVER ORACLE_TARGET;
SET QMANAGER QM2 FOR APPLY SCHEMA;
SET APPLY SCHEMA ASN;
CREATE REPLQMAP SAMPLE_ASN_TO_FED_DB_ASN USING ADMINQ ASN.QM1.ADMINQ 
RECVQ ASN.QM1_TO_QM2.DATAQ SENDQ ASN.QM1_TO_QM2.DATAQ

For full syntax and parameters, see Chapter 12, "Replication queue map definition commands for Q replication" in the ASNCLP Program Reference for Replication and Event Publishing.

Back to top

Creating Q subscriptions for federated Q replication

A Q subscription maps the DB2 table that contains your source data to a copy of that table at the non-DB2 target database. You specify a queue map, target table options, and other preferences. You create a Q subscription for each table that you want to replicate.

Before you begin

• Create the Q Capture and Q Apply control tables.
• Create a replication queue map.

Restrictions

• Multidirectional replication is not supported for non-DB2 targets.
• Views or stored procedures in the non-DB2 database are not supported as targets.
• If you want the Q Apply program to perform a load by using the EXPORT and IMPORT utilities, the target table that is referred to by the nickname must be empty.
• Replication to non-DB2 target tables with referential integrity constraints is supported only if you manually define the constraints on the corresponding nicknames. The Replication Center does not automatically create these constraints. Also, the Q Apply program does not drop referential integrity constraints on nicknames during the loading process and then restore them. Recommendation: Use the "no load" option for nicknames with referential integrity constraints and load the target table outside of the replication administration tools.
• For target nicknames with multiple indexes, the BEFORE_VALUES attribute for the Q subscription must be Y and the CHANGED_COLS_ONLY value must be N in the IBMQREP_SUBS table.

Procedure

To create Q subscriptions for federated Q replication:

1. Open the Create Q Subscriptions wizard in the Replication Center by expanding the appropriate Q Capture schema or Q Apply schema in the object tree, right-clicking the Q Subscriptions folder, and clicking Create.
   Figure 13. Opening the Create Q Subscriptions wizard
   
   
2. On the Replication page, accept the default of Unidirectional.
3. On the Servers page:
   1. Specify a source server.
   2. Specify the federated Q Apply server as the target server. The Q Apply program updates a nickname at this server that is mapped to a table at the non-DB2 relational database.
   3. Specify a replication queue map.
4. On the Source Tables page, select the source table that you want to replicate from.
5. On the Target page, specify the type of target that you want to replicate to:
   • A table in the non-DB2 database, which will be updated by using a nickname. You can let the Replication Center create a new table, or specify an existing table.
   • A DB2 stored procedure, which allows you to manipulate source data before it is applied to the nickname. The stored procedure must already exist on the Q Apply server and should only write to nicknames.

   Tip: The Replication Center always creates a new nickname for a non-DB2 target table even if one or more nicknames already exist for the table. A new nickname gives the Replication Center control over nickname alterations so that it can resolve data type differences between DB2 Universal Database and the non-DB2 target without changing an existing nickname.

   
   Figure 14. Target page of the Create Q Subscriptions wizard
   
   
6. For multiple Q subscriptions: If you specified more than one source table, the Target Tables page shows the profile to be used for non-DB2 target tables, indexes, and table spaces or segments, and for target nicknames at the federated Q Apply server. Click Change to open the Manage Target Object Profile window and change the names.
7. On the Rows and Columns page:
   1. Use the Source changes controls if you want to replicate a subset of the source table columns or rows.
   2. Use the Column mapping field to change the default mapping of source columns to columns in the non-DB2 target table. The Column Mapping window shows the default data type mappings between the nickname at the federated Q Apply server and the non-DB2 target table and validates whether a source column can be mapped to a target column.
   3. Use the Index or primary key field to select the key columns that the Q Apply program uses to identify replicated rows and correctly order transactions.
8. On the Unexpected Conditions page, specify how the Q Apply program responds to errors.
9. On the Loading the Target Table page:
   1. Specify a load option.

      Tip: For automatic loading of the target table by the Q Apply program, federated Q replication supports only the EXPORT and IMPORT utilities. See Options for loading target tables for Q replication--Overview in the DB2 Information Center for details on the loading process.

   2. Specify whether the Q subscription is active as soon as it is created. With this option, replication begins for the Q subscription when the Q Capture and Q Apply programs start.
10. On the Review Q Subscriptions page, confirm that the Q subscription is valid. If you want to change anything about the Q subscription, or if it is missing information, highlight the Q subscription and click Properties.
11. On the Summary page, click Finish to generate the SQL script for creating the Q subscription. You can run the script, schedule it as a task in the Task Center, or save it to a file.

Creating a Q subscription by using the ASNCLP

In the ASNCLP, use the CREATE QSUB command to create a Q subscription. For TARGET NAME specify the non-DB2 target table. You also specify the FEDERATED keyword and can specify a nickname name and owner if you want to change the default.

For example, the following series of commands creates a Q subscription with these characteristics:

• The source server is SAMPLE.
• The federated Q Apply server (TARGET keyword) is FED_DB.
• The non-DB2 target server (NONIBM SERVER keyword) is ORACLE_TARGET.
• The replication queue map (REPLQMAP keyword) is SAMPLE_ASN_TO_FED_DB_ASN.
• The Q subscription name (SUBNAME keyword) is FEDSUB.
• The target table on the Oracle database is EMPLOYEE.
• The nickname on the federated Q Apply server that points to the EMPLOYEE table is EMPNICKNAME.
• The Q subscription specifies a manual (E) load phase (HAS LOAD PHASE keyword).

ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER CAPTURE TO DB SAMPLE;
SET SERVER TARGET TO DB FED_DB NONIBM SERVER ORACLE_TARGET;
SET CAPTURE SCHEMA ASN;
SET APPLY SCHEMA ASN;
SET QMANAGER QM1 FOR CAPTURE SCHEMA;
SET QMANAGER QM2 FOR APPLY SCHEMA;
CREATE QSUB USING REPLQMAP SAMPLE_ASN_TO_FED_DB_ASN (SUBNAME FEDSUB EMPLOYEE
OPTIONS HAS LOAD PHASE E TARGET NAME EMPLOYEE FEDERATED EMPNICKNAME);

For full syntax and parameters, see Chapter 14, "Q subscription definition commands for Q replication" in the ASNCLP Program Reference for Replication and Event Publishing.

Back to top

Starting Q replication to non-DB2 targets

Starting Q replication to non-DB2 targets is the same as starting replication to DB2 targets. This topic shows you how to start WebSphere MQ channels and listeners, and then links to detailed information on starting the Q Capture and Q Apply programs.

Before you begin

• Create WebSphere MQ queue managers, queues, and channels.
• Configure the Q Capture server, non-DB2 target, and federated Q Apply server.
• Create wrappers, server definitions, and user mappings.
• Create control tables, replication queue maps, and Q subscriptions.

Procedure

To start Q replication to non-DB2 targets:

1. Start the WebSphere MQ channels from Q Capture to Q Apply, and from Q Apply to Q Capture:
   1. Ensure that the queue managers for the source and target are running by issuing these commands:

      strmqm source_queue_manager_name strmqm target_queue_manager_name

      
      
   2. Start the listener at the source for the receiver end of the channel from the target by issuing this command:

      runmqlsr -t tcp -m source_queue_manager_name -p source_port_number

      
      

      Tip: On Windows, you can also use the start runmqlsr command to open a new command window for the listener so that you can continue using your current command window. On Linux and UNIX, add a space and ampersand ( &) to the end of the runmqlsr command.

   3. Start an interactive session with the source queue manager by issuing this command:

      runmqsc source_queue_manager_name

      
      
   4. Start the sender channel at the source by issuing this command:

      start channel (source_sender_channel_name)

      
      
   5. Use the end command to stop the interactive session with the source queue manager.
   6. Start the listener at the target for the receiver end of the channel from the source by issuing this command:

      runmqlsr -t tcp -m target_queue_manager_name -p target_port_number

      
      
   7. Start an interactive session with the target queue manager by issuing this command:

      runmqsc target_queue_manager_name

      
      
   8. Start the sender channel at the target by issuing this command:

      start channel (target_sender_channel_name)

      
      
2. Start the Q Capture program. See Starting a Q Capture program in the DB2 Information Center for startup prerequisites, Q Capture parameter options, and tips on checking the status of the program.
3. Start the Q Apply program. See Starting a Q Apply program in the DB2 Information Center for startup prerequisites, Q Apply parameter options, and tips on checking the status of the program.

Back to top

Restrictions for federated Q replication

The following general and data type restrictions apply for WebSphere Information Integrator Version 8.1 Fix Pack 9.

General restrictions

• The asntdiff and asntrep utilities require the data types to be the same at the DB2 source table and the nickname at the federated Q Apply server.
• If you use the ADDCOL signal to add a column to an existing Q subscription, the new column must already exist in the target table and the corresponding nickname (you cannot add a column to a nickname).

Data type restrictions

• Replication of large object (LOB) values is supported for Oracle targets only and requires the NET8 wrapper.
• To replicate GRAPHIC, VARGRAPHIC, or DBCLOB data types, your Oracle server and client must be version 9 or later. Your server mapping must also be version 9 or later.
• Replication of LONG VARGRAPHIC data types to Oracle and Sybase targets is not supported in fix pack 9.
• Sybase: If the source table has a column data type of LONG VARCHAR, the nickname is created with a data type of VARCHAR(32672). The length allowed for a LONG VARCHAR is greater than 32672 and this could result in truncation of data.

Resources

• The Q Replication Version 8.2 Information Roadmap on developerWorks shows the information resources available for Q replication, which is available as part of WebSphere Information Integrator Replication Edition and WebSphere Information Integrator Replication for z/OS.
  
  
• To learn more about creating, administering, and tuning federated servers and objects, see the Data Source Configuration Guide and Federated Systems Guide, available in softcopy at the DB2 UDB, DB2 Connect and DB2 Information Integrator Version 8 product manuals page.
  

About the author

		Dell Burner, BA, MS, is a technical writer on the WebSphere Information Integrator replication team at the IBM Silicon Valley Laboratory. He is co-author of the Replication and Event Publishing Guide and Reference and author of Tuning for Replication and Event Publishing Performance, as well as the developerWorks tutorial, "Replicate data in the fast lane." Dell is an IBM Certified Database Administrator (DB2 Universal Database V8.1 for Linux, UNIX and Windows).

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top