StarQuest Technical Documents

Quick Start Guide to Using SQDR Plus for iSeries
with the StarSQL ODBC Driver

Updated: 16 April 2008
Product: StarQuest Data Replicator Plus for iSeries
Version: 3.4 or later
Article ID: SQV00PL001

Abstract

This Quick Start Guide describes how to install and configure all the software you need, including the StarSQL ODBC driver, to perform incremental replication operations using SQDR and SQDR Plus for iSeries.

This guide is divided into two major parts: the first part focuses on preparing the iSeries host, and the second part describes how to prepare a Windows computer and define and run an SQDR replication operation. The estimated time to complete all tasks is approximately 1.5 hours, as summarized below.

Part One (on the iSeries): approximately 40 minutes

Step 1: Pre-installation Tasks. (estimated time: 15 minutes)

Step 2: Install the SQDR Plus Software. (estimated time: 20 minutes)

Step 3: Verify the Capture Agent is Running. (estimated time: less than 5 minutes)

Part Two (on the Windows client): approximately 45 minutes

Step 1: Install the StarSQL ODBC Driver. (estimated time: 5 minutes)

Step 2: License the StarQuest Products. (estimated time: 5 minutes)

Step 3: Create DBMS Data Source. (estimated time: 5 minutes)

Step 4: Start the SQL Server Service.  (estimated time: less than 5 minutes)

Step 5: Install and Configure the StarQuest Data Replicator Software. (estimated time: 10 minutes)

Step 6: Start the Data Replicator Manager and Create a Subscription. (estimated time: 10 minutes)

Step 7: Review the Subscription Status. (estimated time: less than 5 minutes)

Solution

The following sections guide you through the steps that are summarized in the preceding Abstract. The steps in Part One prepare the iSeries computer and install the SQDR Plus software, and the steps in Part Two are performed on a Windows-based computer that has TCP/IP connectivity to the target iSeries computer.

Before You Begin

Before you begin, review the following list to ensure that you have the information and files you need, and that your computer environment provides the minimum requirements. You cannot successfully complete the procedures in Part One or Two until these requirements are met.

• You have requested and downloaded evaluation copies of the StarSQL, SQDR, and SQDR Plus software. All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a browser enter the address http://www.starquest.com/ and click on Trial Download if the prerequisite software has not been downloaded. Request the download of SQDR Plus to get bundled download instructions for all the required StarQuest products, including StarSQL, SQDR, and SQDR Plus for iSeries.
• You have the Download Instructions that were sent to the email address specified in the StarQuest Product Download Request form. You need these instructions and Internet access to license the software.
• Ensure that the client computer from which you will run the SQDR Plus installer has a compatible  Java Virtual Machine (JVM) or Java Runtime Environment (JRE) installed and specified in the system PATH. You can obtain the Java Runtime Environment free of charge from Sun Microsystems at http://www.java.com/en/download/. From a command prompt, run the java -version command to verify that Java is installed and can be located, as shown in the following example.

C:\>java -version
java version "1.6.0_03"
Java(TM) SE Runtime Environment (build 1.6.0_03-b05)
Java HotSpot(TM) Client VM (build 1.6.0_03-b05, mixed mode)

• Ensure that the client computer from which you will run the SQDR Plus installer has TCP/IP connectivity to the target iSeries computer.  You also need access to a 5250 emulation application or know how to use "telnet" to verify some values during setup.
• The client computer on which you will install SQDR and StarSQL must have some version of Microsoft SQL Server installed. If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) or the SQL Server 2005 Express Edition free of charge from Microsoft to use as a control database for the Data Replicator. The StarQuest Technical Document "Installing SQL Server for a Control Database" provides details for using MSDE 2000 or 2005 Express with SQDR.
•  The collation of the SQL Server database to where data will be replicated should be case-sensitive. Verify the collation for the database by viewing the database properties in the SQL Server Enterprise Manager (SQL Server 2000) or the SQL Server Management Studio (SQL Server 2005). A case-sensitive collation will have the characters “CS” appended to the Collation Designator. For example, the collation SQL_Latin1_General_CP1_CS_AS is a case-sensitive collation for U.S. English systems. You can set the collation for a new database using the SQL Server Enterprise Manager or the SQL Server Management Studio. To change the collation for an existing database, use the ALTER DATABASE (Transact-SQL) command with the COLLATE clause. Refer to the SQL Server Books Online for more information on collation values.
• Depending on the type of authentication that is configured for the SQL Server, you must have a SQL Server user ID (if using SQL Server and Windows authentication) or a Windows user ID (if using Windows-only authentication) that has permission to access the SQL Server Control Database that is used by SQDR. Typically this user ID is a member of the System Administrators server role.
• Your SQL Server user ID (if using SQL Server and Windows authentication) or Windows user ID (if using Windows-only authentication) also must have sufficient permissions to perform the replication operations on the target SQL Server database.
• You need to have an OS/400 user ID with Security Officer (SECOFR) authority to install the SQDR Plus software on the iSeries platform.
• Your OS/400 user ID, which is used to connect using SQDR, also must provide adequate authority to work with the libraries and tables you want to replicate. StarQuest recommends that the OS/400 user ID used to operate SQDR not be the same QSECOFR-privileged USERID that is used to install SQDR Plus.

If you have any problem installing or using the evaluation software, please send an email to StarQuest Customer Support at contact@starquest.com or call (+1) 415-669-9619 for assistance.

Part One: Prepare the iSeries Computer

Total Estimated Time to Complete: 40 minutes

Step 1: Pre-installation Tasks

Estimated Time: 15 minutes

1. Start a 5250 session, using either emulation software or the telnet command, and sign on to the iSeries with a QSECOFR user profile.
2. Ensure that the installed version of Operating System/400 (OS/400) is release v5r2 or later.
   Run the OS/400 command GO LICPGM at the command line. Select option 10, "Display installed licensed programs" and press F11. The Installed Release column displays the version of the Operating System/400 software.
3. Confirm that Qshell is installed.
   Run the command GO LICPGM and select option 10, "Display installed licensed programs," and look for Licensed Program 5722SS1 (or 5761SS1 for V6R1) with an Installed Status of *COMPATIBLE and a Description of “Qshell, Program Option 30.”
4. Confirm that the Java Developer Kit is installed.
   Run the command GO LICPGM. Select option 10, "Display installed licensed programs," and look for Licensed Program 5722JV1 with an Installed Status of *COMPATIBLE and a Description of “Java Developer Kit 1.4, Program Option 6.”
5. Verify that the following cumulative PTF, DB2 Group PTF, and Java Group PTF packages, and the appropriate individual PTFs have been installed on the OS/400 system. The OS/400 system should have the following fixes or their superseding fixes applied.

OS Version	CUM PTF	DB2 Group	Java Group	Individual PTFs
V5R2	C6080520 (March 2006)	SF99502-22 (Nov 2005)	SF99169-24 (Dec 2005)	5722SS1-SI23370
V5R3	C6101530 (March 2006)	SF99503-9 (March 2006)	SF99269-10 (March 2006)	5722SS1-SI18139
V5R4	C6115540 (April 2006)	SF99504-3 (March 2006)	SF99291-2 (March 2006)	5722SS1-SI22551 5722SS1-SI22324 5722SS1-SI22335 †
V6R1	C8064610			5761SS1-SI30581
†  Install PTF 5722SS1-SI22335 if you are working with BLOB data.

To verify that an individual PTF has been installed, use the DSPPTF command, entering the License Program (Product) number and the PTF number to select, as in the following example.

DSPPTF LICPGM(5722SS1) SELECT(SI18139)

View the General Information for the PTF and verify that the PTF status is either Superseded, Temporarily applied, or Permanently applied.

To review the DB2 Group or Java Group PTF packages that are installed, run the WRKPTFGRP command and browse the list of installed PTF Groups.

If you are running V5R3 or later release of OS/400, the WRKPTFGRP command also shows the CUM PTF level. If you are running V5R2, use the DSPPTF LICPGM(5722SS1) command to display all the system PTFs (look for the most recent marker PTF with a name such as TCyyddd), or DSPPTF LICPGM(5722999) to display all microcode PTFs (look for the most recent marker PTF with a name of TLyyddd).

6. This Quick Start Guide assumes that the default configuration values are used during the installation as they are generally appropriate in most environments. Make a note of the optional email notification values for your environment if you want to specify these values during installation. You can leave these fields blank during the installation and configure the email notification feature at a later time. Refer to the SQDR Plus for iSeries User's Guide for more information about these parameters.

Configuration Parameter	Sample Value	Your Value
SMTP Server:	mail.mydomain.com
From:	sqdr@mydomain.com
To:	sqdradmin@mydomain.com
Notification Level:	SEVERE

Step 2: Install the SQDR Plus Software

Estimated Time: 20 minutes

The SQDR Plus installer requires a GUI environment, so it is typically run on a remote system, such as on a computer that is running Windows, MaxOS X, or UNIX with X Windows. The computer from which you run the installer must have TCP/IP connectivity to the target iSeries computer. The instructions and illustrations that follow reflect using a Windows-based computer.

1. Run the setup.bat shell script from the SQDR Plus installer image. In the Signon to the Server dialog, enter the system name, a powerful (SECOFR) user ID, and password for accessing the iSeries computer and click OK.

For new installations of SQDR Plus, select to perform the following tasks during installation:

• Create schema
• Create control tables in schema
• Change ownership of schema and other objects
• Setup up job descriptions and autostart job

Accept all of the default values in the installer dialogs, except for those values gathered in Step 7 above if you want to configure the email notification feature.

Note that the Capture Agent Maintenance utility allows you to specify which tables in the database are available for replication. During the installation, only enable the checkbox for the Restrict Subscriptions to Published Tables option if the security policy for your AS/400 requires this. This option can be changed at any time after the software is installed.

Step 3: Verify the Capture Agent is Running

Estimated Time: less than 5 minutes

After the installation is complete, verify that the Capture Agent is running.

1. Start a 5250 session with the iSeries and run the OS/400 command ADDLIBLE SQDR to add the SQDR library to the library list. 
2. Run the command CAMAINT to launch the Capture Agent Maintenance Utility.
3. Choose Option 3, “Display current status” and verify that the Capture Agent component has a status of Running.

It may take a few minutes to display the status of the Capture Agent, depending upon the availability of resources on the iSeries. You can continue the installation tasks without the Capture Agent running, but you cannot define subscriptions that perform incremental replication operations until the Capture Agent is available.

Additional References

SQDR Plus for iSeries User’s Guide

Part Two: Prepare a Windows Computer

Total Estimated Time to Complete: 50 minutes

All of the procedures in this part are performed from a Windows-based computer that has TCP/IP connectivity to the target iSeries computer.

Step 1: Install the StarSQL ODBC Driver

Estimated Time: 5 minutes

For best results with SQDR, StarQuest recommends using the StarSQL ODBC driver for access to DB2. The StarSQL software is distributed as a compressed file that you download from the StarQuest Ventures Web site. See the section “Before You Begin” on page 2 if you have not already downloaded the StarSQL software.

1. To install StarSQL, run the setup.exe program from the StarSQL installer image.
2. Select to perform a Typical or Custom installation.

At the end of the installation, a License Configuration dialog prompts you to enter your license key for using StarSQL. You can enter the license keys for all the StarQuest products you want to use, as described in the next step.

Step 2: License the StarQuest Products

Estimated Time: 5 minutes

If you have not already received temporary license keys for the evaluation software, refer to the Download Instructions that were sent via email in response to the software evaluation request. The Download Instructions provide specific instructions for obtaining a license key that allows you to use the software product. You can add the license key for multiple products at once, as described below for adding the StarSQL, SQDR, and SQDR Plus license keys.

1. From the Licenses tab of the License Configuration dialog, click Add to display the Add License dialog.
2. In the Add License dialog enter the license key for StarSQL and click the Add button. Repeat this step to enter the license key for SQDR and SQDR Plus. After you have added the license key for each of the products, click OK in the License Configuration dialog.

Step 3: Create DBMS Data Source

Estimated Time: 5 minutes

An ODBC data source name (DSN) defines the information that a driver needs to access a specific instance of data in a DBMS. You must define an ODBC DSN for each host DBMS that you want to replicate to or from. The following steps describe how to create a StarSQL data source with an isolation level of Read Committed, which is the isolation level that is required for performing incremental replication operations with a SQDR Plus host.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows the ODBC Administrator is typically found in the menu path Settings—>Control Panel —>Administrative Tools—>Data Sources (ODBC).
2. Click the System DSN tab of the ODBC Data Source Administrator window. Click Add and, in the next dialog, select StarSQL 32 for the driver and click Finish.
3. In the first dialog of the StarSQL Data Source Wizard, enter a name for the data source and, optionally, a description. Click Next to step through the Wizard panes, specifying values appropriate for your environment. A typical data source connecting to an iSeries server has the following configuration values:
   

Database Server Name	RDB Name (run the WRKRDBDIRE command on the iSeries and locate the entry name for the remote location of *LOCAL)
Package Collection Name	SQDR
Host Name	Network name or IP address of the iSeries computer
Port	446, unless configured differently on the iSeries
Catalog Schema	QSYS2

4. From the Network pane of the StarSQL Data Source Wizard, click the Test Connection button to ensure you can connect to the host database. On the next pane, enter a valid AS/400 user ID and password so that the data source can connect to the database and set default values, and click Summary.  If the connection fails, modify the data source configuration until the ODBC DSN can successfully connect to the host.
5. Click the Expert Page item in the left pane of the StarSQL Data Source Wizard. Select IsolationLevel in the Data Source Entry list box, and select Read Committed in the Values list box. Click Next to display the summary page and click OK to save the DSN.

Additional References

StarSQL Quick Start Guide
StarSQL User’s Guide
StarSQL Online Help
StarSQL Technical Document: How to bind packages with StarAdmin

Step 4: Start the SQL Server Service

Estimated Time: less than 5 minutes

The StarQuest Data Replicator requires a control database in which to store definitions for sources, destinations, subscriptions, and groups of subscriptions. If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition free of charge from Microsoft, as further described in the StarQuest Technical Document "Installing SQL Server for a Control Database.”

The SQL Server service must be running for the Replicator Service to access its control database. If you intend to configure replication subscriptions to run according to a schedule, also start the SQL Server Agent service. Start the SQL Server and SQL Server Agent services from Windows Services, typically found under Control Panel—> Administrative Tools—> Services.

NOTE

The SQL Server 2005 Express Edition does not include the SQL Server Agent component, which the Data Replicator Manager uses to schedule subscriptions to run automatically. If you use the free SQL Server 2005 Express Edition as a control database for Data Replicator, you can still use the Data Replicator Manager to run subscriptions manually, and you can use other methods, such as Windows Scheduler, to schedule them to run automatically.

Additional References

StarQuest Technical Document: Installing SQL Server for a Control Database

Step 5: Install and Configure the StarQuest Data Replicator Software

Estimated Time: 10 minutes

The StarQuest Data Replicator software is distributed as a compressed file that you download from the StarQuest Ventures Web site. See the section “Before You Begin” if you have not already downloaded the SQDR software.

1. Run the setup.exe program from the SQDR installer image.
2. At the end of the installation, choose to launch the Data Replicator Configuration and click Finish.

Configure the Data Replicator Service

You must configure the Data Replicator service to specify which SQL Server data source to use for the control tables and how the Data Replicator service should log in to the SQL Server.

1. In the first pane of the Configuration Wizard, select the SQL Server data source that you configured for the Data Replicator control tables and enter the user ID required to log in to the SQL Server.
   Click Next to proceed.

NOTE

If you receive an error that the data source cannot be found, launch the ODBC Data Source Administrator from Control Panel--> Administrative Tools-->Data Sources (ODBC), double-click the SQL Server System DSN, and review the configuration. At the configuration summary verify that the Test Connection succeeds. Exit the ODBC Administrator and return to the SQDR Configuration Wizard.

2. Select the option Create a New Control Database and click Next.
3. Choose whether to start the Data Replicator service automatically when the computer is started or manually when the service is needed, and the Windows account under which the Data Replicator service will run. Click Finish to save the Data Replicator configuration.

Step 6: Start the Data Replicator Manager and Create a Subscription

Estimated Time: 10 minutes

You use the Data Replicator Manager to set up and administer replication operations.

1. Select Data Replicator Manager from the StarQuest Data Replicator program group if it is not already running.
2. In the License Configuration dialog, verify that you have a license key entered with a Product ID of DR and click OK. See “Step 2: License the StarQuest Products” if you have not entered license keys for using the evaluation software.

Define a Source

To define the source data that you want to replicate:

1. In the main Data Replicator Manager window, right-click the Sources folder and select the Insert Source command.
2. Select the DSN that connects to the source database that contains the data you want to replicate, and enter a user ID and password that has permission to access that database, as shown in the following screen. Click OK to add the source.

       

3. SQDR will automatically detect any available Capture Agent schemas and display a message. Click Cancel on the displayed message box and click on the Advanced tab. We recommend that you supply a Notification Address (hostname or IP address) of the computer that you want to receive a UDP notification when changes are committed to this source.

       

The drop-down menu lists the IP addresses of all active network connections. Usually one of the listed values is best; in the case of multiple subnets use the same subnet as the iSeries host. If NAT is being used and the IP address known to the host is different than the local address, use the "external" address that the iSeries host uses to communicate with the SQDR computer.

Define a Destination

Now define the destination that will receive the replicated data.

1. Right-click the Destinations folder and select the Insert Destination command.
2. Select the DSN that connects to the database to which you want to replicate the data, and enter a user ID and password that has permission to access that database. Click OK to add the destination.

       

Create an Incremental Group

In this section you create a group in which to place one or more subscriptions that perform incremental replication operations between the specified source and destination.

1. Right-click the Groups folder and select Insert Incremental Group. In the Group properties dialog, enter a name for the Group and an optional description.

The option Force Use of RRN causes SQDR Plus to add a Relative Record Number (RRN) to the staging table for tracking changes to the source. The RRN can help ensure the data integrity of tables that do not have unique indexes, or for tables that use unique indexes with columns that contain timestamps or other data types which do not map precisely from the source DBMS to the target DBMS. If the subscription relies upon the RRN and the source table is reorganized or dropped and re-created, the subscription must be run to create a new baseline snapshot of the table. You may not want to use the Force Use of RRN option if your source tables are reorganized frequently and especially if they are extremely large, but you will need to ensure that an appropriate index is selected in the subscription (e.g. one which doe not rely upon a non-uniquely mapped column type) if there is no RRN field to identify the change data.

.

2. On the Advanced tab, select the Source and Destination that will be used for each incremental replication subscription in this group. Click OK to add the group.

Create a Subscription

The Replicator Manager provides a Subscription Wizard to help you define the specific source data and how you want to replicate it.

1. Right-click the Subscriptions folder beneath the source that you added and select the Insert Subscription command. The Add Subscription Wizard appears.
2. In the Select the Source pane, select Incremental as the Replication Type and enter a schema or object name if you want to filter the source objects list. Click the Refresh button to display the available source objects in the right pane and select the source object you want to replicate. Click Next to proceed.

1. In the Select a Destination pane, select the DSN that connects to the destination you added to the Replicator Manager. Review the Destination Options and Replication Options, and click Next to proceed.
2. In the Select Group pane select the incremental replication group that you created earlier. Click Next to continue.

5. The Define Criteria pane allows you to specify a SQL WHERE clause in order to horizontally partition the data to be replicated. Enter a valid WHERE clause and click the Verify Criteria button, or leave the field blank. Click Next to continue.
6. The Define Destination Columns pane allows you to review and change the data type mappings for the source and destination objects. Modify the destination columns if desired and click Next.

7. Click Next in the Create Table Statement pane. The CREATE TABLE statement will not appear until the subscription has been saved.
8. On the Define Processing pane, enter optional host commands to be executed before and/or after the data is replicated. Click Next to continue.
9. On the Indexes/Constraints pane, click Refresh to display any indexes or constraints defined for the source. Select any that you want to replicate to the destination and click Next.
10. Enter a name for the subscription and click Finish.

The subscription is added beneath the Subscriptions folder of the defined source and to the corresponding group.

Step 7: Review the Subscription Status

Estimated Time: less than 5 minutes

After you define an incremental replication subscription the Data Replicator runs a baseline snapshot replication that forms the base against which incremental changes are tracked. After the baseline snapshot replication succeeds, the incremental subscription actively monitors the source for changes. This step shows you how to monitor the status of a subscription and a group of incremental subscriptions.

1. Review the status of the subscription by highlighting the subscription in the left pane of the Replicator Manager.

2. Double-click the replication event, indicated by the  icon, to view the statistics of the baseline snapshot. If the data did not replicate successfully, review the error(s) and correct the subscription properties as necessary.

       

3. Locate the member subscription under Groups, and double-click on the Replication Event to view the status of the subscription.

       

After the baseline snapshot operation completes successfully the incremental subscription is automatically enabled and actively monitoring for changes at the source.

Continuing Your Software Evaluation

This Quick Start Guide is intended to help you install the StarQuest software and perform a data replication operation as quickly as possible. Please continue to explore the capabilities of SQDR Plus by adding additional subscriptions, scheduling replication operations, and performing SQL operations on data before and/or after it is replicated. The product documentation provides detailed information about performing these and many other tasks. Refer to the following documentation as you become familiar with using the SQDR software in your environment.

• The SQDR Plus for iSeries manual is available as a PDF file in the SQDR Plus installation image. It provides more detail about configuring and managing the SQDR Plus Capture Agent.
• The Data Replicator Manager provides an online help system that provides more information about defining and running replication operations. Press F1 or click a Help button from the Data Replicator Manager to display the online help system.

Please especially be aware of the following operational requirements and suggestions, which are covered more thoroughly in the product documentation, as you proceed with your evaluation:

• SQDR Plus uses TCP for inter process communications and requires TCP services to shut down properly. Shut down the SQDR subsystem before you end TCP services or shut down the OS/400 system. If there is a delay of 30 minutes or more between IPL of the OS/400 and restart of the SQDR subsystem, you also may want to lengthen the time interval for sending notifications that the SQDR journal receivers are unavailable to avoid flooding the operator's QSYSOPR message queue.
• The Capture Agent spawns jobs in other subsystems, such as QSQSRVR in the QSYSWRK subsystem, QRWTSRVR in the QUSRWRK subsystem, QZDASOINIT in the QUSRWRK subsystem, and QZRCSRVS which can run in different subsystems. Do not manually stop any of these jobs.
• You may want to modify the system startup routine to automatically start the SQDR subsystem when an IPL of the iSeries server is performed.

If you encounter any problems while using the evaluation software, please send an email to StarQuest Customer Support at contact@starquest.com or call (+1) 415-669-9619 for assistance.

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2008 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.