StarQuest Technical Documents

Quick Start Guide to Using SQDR Plus for DB2 UDB on Linux/UNIX

Last Update: 01 June 2010
Product: SQDR Plus for DB2 UDB
Version: 3.65 or later
Article ID: SQV00PU002

Abstract

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

The SQDR Plus software can be installed using either a text-based or a GUI-based installer. The instructions in this document use the text-based version, which is recommended for users who do not require a user interface. Users who would prefer to use a GUI-based installer should refer to the SQDR Plus for UDB User's Guide for installation instructions.

This document is intended for first-time users of SQDR and SQDR Plus for DB2 UDB. Users who are upgrading an existing installation should refer to the SQDR Release Notes or SQDR Plus for UDB Release Notes and the SQDR Plus for UDB User's Guide for upgrade instructions.

The estimated time to complete all tasks is approximately 2 hours.

SQDR Plus for DB2 UDB Quick Start Guide Outline

The same individual or a combination of individuals must be capable of performing in the following three roles in order to successfully complete the evaluation.

• DB2 Administrator is someone with administrative authority on the DB2 system.
• SQL Server Administrator is someone with system administrator ("sa") authority or is a member of the sysadmin fixed server role on the SQL Server database, or has Windows administrative authority to install a version of SQL Server.
• Client-Platform Administrator is someone with administrative authority to install StarAdmin, StarSQL, and SQDR on a Windows computer. This person must also have a set of DB2 user credentials that provide adequate authority to work with the libraries and tables you want to replicate.

General considerations

The Windows platform that will run StarAdmin, StarSQL, and SQDR must have:

1. TCP-IP access to the database host.
2. A version of Microsoft SQL Server installed. This is because SQDR uses a SQL Server database to store replication subscription information. 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/2008 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/2008 Express with SQDR.

SQL Server considerations

1. SQL Server 2008 users must install the SQL Distributed Management Objects (SQL-DMO), which are included in the Microsoft SQL Server 2005 Backward Compatibility Components included in the SQL Server 2008 Feature Pack. In some cases, SQL Server 2005 users may also need to install the Microsoft SQL Server 2005 Backward Compatibility Components, available in the SQL Server 2005 Feature Pack.
2. 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/2008). 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. Refer to the SQL Server Books Online for more information on collation values.
3. The SQL Server 2005/2008 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/2008 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.
4. 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 is a member of the System Administrators (sysadmin) server role.

DB2 UDB host computer requirements

• Verify that the DB2 UDB server software has been installed on the host computer and that it is version 8 with FixPak 10 or later, or version 9.1 or later. You can use the db2level command to display the Release, Level, and information tokens of the DB2 instance. For example, a typical result of running the db2level command on a UNIX computer that has DB2 UDB v9 installed would be:

DB21085I  Instance "db297adm" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23027", and Fix Pack"1".
Product is installed at "/opt/IBM/db2/V9.7"

• Set up the UNIX environment for the root user to access the DB2 command line processor (CLP) and run DB2 commands:

  1. While logged in as root user, run db2profile (sh/ksh/bash) or db2cshrc (csh) as shown below.

sh/ksh/bash	1. Navigate to the sqllib directory of the DB2 instance owner directory, as in: # cd /home/<db2inst_owner>/sqllib 2. Add the following commands to the userprofile file in the same directory: export INSTHOME export DB2DIR 3. Source the DB2 instance owner's profile by running the command: # . ./db2profile
csh	1. Navigate to the sqllib directory of the DB2 instance owner directory, as in: % cd /home/<db2inst_owner>/sqllib 2. Source the DB2 instance owner's profile by running the command: % source db2cshrc

2. Enter the command db2. If the db2 command fails, edit the PATH environment variable to include the directory in which DB2 is installed. Use the command setenv or export, depending on your UNIX/Linux version, to set the PATH environment variable for the session or set the PATH in the user profile.
• As you install SQDR Plus you also must specify a DB2 user name and password that has access to the DB2 UDB database that SQDR Plus will monitor. You can use the DB2 instance owner (e.g., db2inst1), but we recommend that you create a non-privileged UNIX user, such as "sqdradm", for this purpose. This DB2 user account needs authority to:
  • Connect to the database
  • Create tables
  • Create packages
  • Create and register routines
  • Create schemas
  • Create external routines
  • Perform SELECT statements on tables to be replicated by SQDR
  In addition, you may want to create a second non-privileged user, such as "sqdrusr", that can be used by the SQDR client software to connect to DB2. This user needs only the authority to connect to the database and issue SELECT statements against the tables to be replicated by SQDR.
• The database must be configured to use archive logging for SQDR Plus to access the log files it needs to ensure incremental replication operations are properly applied. Follow the instructions below to enable archive logging for the database.

Note: Modifying the logging method may put the database in a "BACKUP PENDING" state. Perform a backup of the database before continuing with the installation.

Using the DB2 command line processor:

1. Start the command line processor by running the db2 command.
2. Connect to the database (e.g., connect to <database>).
3. Display the database configuration using the get db cfg command.
4. Review the First log archive method (LOGARCHMETH1) property value. If this value is set to OFF, archive logging is not enabled.

Update the configuration to specify LOGARCHMETH1=USEREXIT if you plan to install the SQDR Plus exit program to manage the logs. For example:

db2 update db cfg for <database> using LOGARCHMETH1 USEREXIT

Or, set this to the desired value as instructed by the IBM DB2 documentation.

5. Stop and restart the DB2 instance.

Using the DB2 Control Center:

1. From the DB2 Control Center select the database, right-click, and select the Configure Database Logging option.
2. Change the type of logging to Archive.
3. Stop and restart the DB2 instance.

DB2 UDB host computer JAVA requirements

• Ensure that the DB2 UDB computer where you will install SQDR Plus has a Java Virtual Machine (JVM) v1.4 or later installed and specified in the system PATH.

1. Type java -version at the command line to display the active Java version. If you are installing SQDR Plus on Solaris or HP-UX, issue the following command to verify that you have a 64-bit mode of Java available before you begin the installation.

java -d64 -version

2. If the Java version is not returned, or is earlier than v1.4, download the JVM software from the IBM developerWorks Web site or, if you are running Solaris or HP-UX, from the Sun Microsystems or Hewlett-Packard Web site, respectively.
• If you have more than one version of Java installed, verify that DB2 UDB is configured to use the correct version of the Java Development Kit (JDK).
  1. Enter the following command in the DB2 command window and look for the Java Development Kit installation path field, JDK_PATH:

  db2 get dbm cfg

  2. If you need to set the JDK_PATH value to the correct location of the JDK or JVM, enter the following on the DB2 command window, replacing the path as necessary with the location where Java is installed on your computer.

  db2 update dbm cfg using JDK_PATH /home/db2inst/jdk14

  3. If the server computer supports both 32-bit and 64-bit instances (AIX, HP-UX, or Solaris) use the JDK_64_PATH configuration parameter instead of the JDK_PATH parameter. The JDK_64_PATH parameter specifies the directory under which the 64-bit version of the JDK is installed.
  4. If you change the JDK_PATH parameter, enter the following commands in a DB2 command window to stop and restart the DB2 instance so the change takes effect.

  db2stop
  db2start

Setting Up the Java Environment [HP-UX Only]

To run Java stored procedures on an HP-UX computer with a 64-bit database instance you need to use the db2hpjv tool to enable Java routine support, which is disabled by default.

1. Enter the following commands on the command line to enable support for Java stored procedures.

db2hpjv –e
db2stop
db2start

The HP-UX run-time linker must be able to access Java shared libraries, and the DB2 system must be able to load the shared libraries and the JVM. The loading program runs with setuid privileges and therefore looks for the dependent libraries only in /usr/lib/pa20_64.  Perform the following step to specify the location of the Java shared libraries in the configuration file.

2. Log in as root user and add the /opt/java1.4/jre/lib/PA_RISC2.0W and /opt/java1.4/jre/lib/PA_RISC2.0W/hotspot directories to the /etc/dld.sl.conf file.

Setting Up the Java Environment [Linux Only]

To run Java stored procedures, the Linux run-time linker must be able to access certain Java shared libraries, and DB2 must be able to load both these libraries and the Java Virtual Machine (JVM). After you ensure that the JDK and JVM are available, you can add the name of the directory that stores the Java shared libraries to the /etc/ld.so.conf file.

1. Add the Java shared libraries to /etc/ld.so.conf, and execute the ldconfig command as root user to activate the changes, such as shown below for 32- and 64-bit instances.

32-bit Instance:

/opt/IBMJava2-142/jre/lib
/opt/IBMJava2-142/lib
/opt/IBMJava2-142/jre/bin
/opt/IBMJava2-142/jre/bin/classic

ldconfig

64-bit Instance:

/opt/IBMJava2-amd64-142/jre/lib
/opt/IBMJava2-amd64-142/lib
/opt/IBMJava2-amd64-142/jre/bin
/opt/IBMJava2-amd64-142/jre/bin/j9vm

ldconfig

2. Restart DB2 for the changes to take effect.

If you plan to allow SQDR Plus to manage the log files, the installing user (typically root) must be defined in a group that has authorization to run the DB2 UPDATE CFG statement. This requires the authorization of SYSADM, SYSCTRL, or SYSMAINT group.

1. Enter the following command in a DB2 command window to determine the database manager configuration value for the SYSADM_GROUP, SYSCTRL_GROUP, or SYSMAINT_GROUP.

db2 get dbm cfg | grep [SYSADM_GROUP | SYSCTRL_GROUP | SYSMAINT_GROUP]

2. Edit the etc/group to add the root user to a group specified in SYSADM_GROUP (a group named DB2GRP1 typically is created during installation) or to a group in SYSCTRL or SYSMAINT.
3. Enter the following commands to restart the DB2 instance so the changes take effect.

db2stop
db2start

Summary of tasks

Perform the tasks in the order described, noting that some provide information used in subsequent steps. The detailed instructions for each task follow this summary.

1. Request to download SQDR Plus for DB2 UDB.
2. Receive the e-mail with a Registration Key and download links for StarAdmin, StarSQL, SQDR, and SQDR Plus.
3. DB2 Administrator: Download StarAdmin using the download link from step 2 and install the software on the SQDR Platform. Using StarAdmin, bind packages to the database. This requires knowledge of the database host and sufficient user privilege. Provide database connectivity information to the Client-Platform Administrator to be used in step 6c.
4. DB2 Administrator: Download SQDR Plus using the download link from step 2, complete the pre-installation tasks, and install the software.
5. DB2 Administrator: Verify SQDR Plus installation and test Support functions using CAMAINT.
6. Client-Platform Administrator:
   1. Download StarSQL using the download link from step 2 and install the software.
   2. License the software using the Registration Key from step 2.
   3. Create ODBC DBMS Data Sources for the source and destination databases.
7. SQL Server Administrator: Start the SQL Server service and configure ODBC DSN for the control database.
8. Client-Platform Administrator:
   1. Download SQDR using the download link from step 2 and install the software.
   2. Run the SQDR configuration, connecting to the local SQL Server using the DSN created in step 7.
   3. Configure an SQDR incremental replication group and replication subscription.
   4. Review the subscription status.

Step 1: Request SQDR Plus for DB2 for UDB Software

Estimated Time: 5 minutes

All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a web browser enter the address http://www.starquest.com/ and click on Download for Trial under the Products menu. Request to download SQDR Plus for DB2 UDB.

Step 2: Receive the Download Confirmation E-mail from StarQuest

Estimated Time: 5 minutes

After submitting a request to download SQDR Plus for DB2 UDB, you will receive an e-mail containing a Registration Key and download links for the SQDR Plus, StarAdmin, StarSQL, and SQDR software. The registration key will be used in step 7b to obtain temporary license keys valid for 15 days and the download links will be used in steps 3-9 below.

Step 3: (DB2 Administrator task) Install StarAdmin and Bind StarSQL Packages

Estimated Time: 20 minutes

The user who installs StarAdmin must be an administrator on the Windows platform (that will eventually run SQDR). Download StarAdmin to a temporary directory using the download link from step 2 and install the software using the instructions below.

Install StarAdmin

1. Log on as an administrative user.
2. Execute setup.exe to launch the installer.
3. If prompted to install the Microsoft Visual C++ 2008 SP1 Redistributable package, click the Install button.
4. On the Welcome dialog, click Next.
5. Accept the License Agreement and click Next.
6. Accept to install StarAdmin in the default directory of C:\Program Files\StarQuest\StarAdmin or browse to select an alternate directory. Click Next to continue.
7. Click Install to begin the installation or click the Back button to make any changes.
8. After the installation is complete, click Finish.

Bind StarSQL Packages

The database connectivity information collected in this section should be provided later to the SQDR Client-Platform Administrator for use in creating ODBC data sources.

1. Collect the DB2 host information and fill in the values for your environment in the space provided in the table below.

   Connection Parameter	Description	Your Value
   Host	The network Host Name or IP address of the DB2 server. At the command line, execute ifconfig -a or netstat -in (HP-UX) to determine the IP address of the system.
   Port	The port number on which DB2 listens for connection requests, as determined by: a) running the DB2 get dbm cfg command and locating the TCP/IP Service name (SVCENAME), and b) reviewing the /etc/services file to find the port number associated with this Service name. The default port for DB2 is 50000.
   Database Name	The name of the source DB2 database.
   Package Collection	Set this value to SQDR.	SQDR
   Username/Password	A DB2 user account that has authority to create and bind packages in the SQDR collection.

2. From the StarAdmin program group, start the StarAdmin application.
3. Enter the database connectivity values in the Connection Settings dialog and click OK.
4. Upon connecting successfully, StarAdmin will immediately bind one package, which will be displayed in the package list. The status bar at the bottom of the dialog will display the Database Name, Package Collection, DB2 Type, and Version. If StarAdmin fails to connect to the database, review the suggested resolutions in the Common Error Messages section of the StarQuest technical document Binding StarSQL Packages Using StarAdmin and make any necessary corrections to the values in the connection dialog.
5. Leave the default values for the Package Settings and Grant Options.
6. From the Command menu, select Bind to create and bind the remaining packages.
7. Once the bind package operation has completed, review the summary dialog. If the packages are created and bound successfully, the summary output will look similar to the following:

Package binding starting: Wed Jul 29 14:44:03 PDT 2009
UID=SUPERUSER
HostName=DB2HOST.DOMAIN.COM
Port=446
Server=DB2PROD
PkgColID=SQDR
AutoTypDefOvr=
BindRules=RUN
CustomizePrdid=No
UseJumboPackages=No
UseEncryption=Any

jdbc:StarSQL_JDBC://DB2HOST.DOMAIN.COM:446/DB2PROD;collection=SQDR

SQL package SWNC0000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWNC0000 to PUBLIC
SQL package SWRU0000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWRU0000 to PUBLIC
SQL package SWRC0000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWRC0000 to PUBLIC
SQL package SWRR0000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWRR0000 to PUBLIC
SQL package SWTS0000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWTS0000 to PUBLIC
SQL package SWNC1000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWNC1000 to PUBLIC
SQL package SWRU1000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWRU1000 to PUBLIC
SQL package SWRC1000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWRC1000 to PUBLIC
SQL package SWRR1000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWRR1000 to PUBLIC
SQL package SWTS1000 in collection SQDR has been created.
Granted execute privileges on SQDR.SWTS1000 to PUBLIC
SQL package QSYS2 in collection SQDR has been created.
Granted execute privileges on SQDR.QSYS2 to PUBLIC

Package binding completed: Wed Jul 29 14:44:55 PDT 2009

If any errors are reported, review the suggested resolutions in the Common Error Messages section of the StarQuest technical document Binding StarSQL Packages Using StarAdmin, correct the problem and execute the bind operation again. If you are unable to resolve the error condition, copy the entire Summary text and send it to StarQuest Customer Support at contact@starquest.com.

8. Copy the connection information at the top of the Summary text to the clipboard, as this information will be used in step 6c.
9. Click the Finished button to return to the main dialog.
10. From the File menu, choose Close Database and then Exit.

Step 4: (DB2 Administrator task) Download and Install SQDR Plus

Estimated Time: 35 minutes

Before you begin, complete the following pre-installation tasks to ensure that the DB2 UDB system meets the minimum requirements. You cannot successfully complete the installation until these requirements have been fulfilled.

Pre-installation Tasks

Estimated Time: 15 minutes

1. Log on to the DB2 UDB host computer as the root user. Be sure that you have sourced the DB2 profile of the DB2 instance owner by running db2profile (sh/ksh/bash) or db2cshrc (csh) as previously described in DB2 UDB host computer requirements.
2. Determine the current database manager instance by running the command db2 get instance at the UNIX/Linux command line. If this database instance is satisfactory, go to Step 3.

If you need to change the default instance, view the available instances using the db2ilist command, and set the DB2INSTANCE environment variable by executing db2profile (sh/ksh/bash) or db2cshrc (csh) in the sqllib directory of the instance owner of the desired instance, as previously described in DB2 UDB host computer requirements.

3. Have available the DB2 connectivity information, as collected previously in step 3.
4. Make a note of the optional email notification values for your environment if you want to specify these values in the installation configuration script. You may leave these fields blank and configure the email notification feature at a later time, if desired.

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

Download and Install the SQDR Plus Software

Estimated Time: 20 minutes

Follow the instructions below to configure the setup script and invoke the text-based installer that will install SQDR Plus on the UNIX/Linux computer. Be sure to have available the credentials for the user (e.g., sqdradm) created in the DB2 UDB host computer requirements section.

1. On the computer where you downloaded the SQDR Plus software, extract the contents of the .zip file to a temporary directory.
2. Copy all of the extracted files to the UNIX/Linux computer on which you want to run the installation.
3. Log on to the UNIX/Linux computer as a user with root authority.
4. Change to the directory where you copied the SQDR Plus installation files and locate setup.conf.template.
5. Copy setup.conf.template to setup.conf (i.e., cp setup.conf.template setup.conf).
6. Open setup.conf in a text editor, modify as instructed below, and save the file.
   • Provide the DB2 connectivity information for your system in the DB2 Information section. The following example configures the installation script to connect to a database named DB2PROD running on a computer with local IP address of 127.0.0.1 and listening on port 50002. The installer will prompt for the password for the DB2USER (sqdradm, in this example) at install time.

   # DB2 Information:
   RDBNAME=DB2PROD
   DB2HOST=127.0.0.1
   DB2PORT=50002
   DB2USER=sqdradm
   

   • If the DB2 logging method is configured to use a user exit program (i.e., LOGARCHMETH1=USEREXIT) and you would like to install the SQDR Plus exit program, set the values of both managedLogs and deleteLogsWithoutSave to "true" (i.e, managedLogs=true, deleteLogsWithoutSave=true). Otherwise, comment out the managedLogs line by preceding it with the "#" character (i.e., #managedLogs=true).
   • For evaluation purposes, the default values for the remainder of the parameters should be acceptable.
7. Execute the setup script to begin the installation.
8. Respond Yes ("y") to the "Are you ready to continue (y/n)? prompt and provide the password for the DB2 user. The installation output will look similar to the following:

[root@fedora11 sqdrplus]# ./setup

This appears to be a new install, installing to /opt/StarQuest/sqdrplus.
If you were expecting an update, please cancel this installation
and verify the value of INSTALLDIR in setup.conf.

Are you ready to continue (y/n)? y

Enter password for DB2 user db2inst1:
Testing connectivity to DB2

Database Connection Information

Database server = DB2/LINUXX8664 9.5.5
SQL authorization ID = DB2INST1
Local database alias = DB2PROD

Installing program directory
Setting up database-specific working area
Copying exit program
Creating control tables
Registering stored procedures
Starting SQDR Plus Capture Agent for DB2PROD...
Please review the installation logs in /var/sqdrplus/DB2PROD/logs

Additional References

SQDR Plus for DB2 Universal Database User’s Guide

Step 5: (DB2 Administrator task) Verify SQDR Plus Installation using CAMAINT

Estimated Time: 5 minutes

Use the SQDR Plus Capture Agent Maintenance Utility (CAMAINT) to verify that the Capture Agent is running and to test the support functions.

1. From a Linux/UNIX command line, change to the /var/sqdrplus/<database_name>/bin directory.
2. Enter the following command to run the Capture Agent Maintenance utility.

# ./camaint

3. Enter a valid DB2 user ID and password. The default user ID is the DB2 User specified during the installation of SQDR Plus.
4. In the Capture Agent Management Main Menu, choose Option 3, “Display current status” and verify that the Capture Agent component has a status of Running.
5. Press Enter to return to the main menu.
6. In order to verify that the DB2 server can send SQDR Plus support logs to StarQuest Customer Support, select Option 9, "Support" to enter the Support menu.
7. Select Option 1, "Send SQDRPlus Support Logs", which will collect and ftp support files directly to StarQuest. If the DB2 server is not able to reach external ftp sites, use Option 5 to create and store the support files on an alternative computer. Send these files to StarQuest Customer Support if requested to do so.
8. Press Enter twice to return to the main menu and enter 50 to exit.

Step 6a: (Client-Platform Administrator task) 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. However, SQDR does support the use of the IBM DB2 Connect driver. If you plan to use the IBM DB2 Connect driver with SQDR, skip this step and proceed with step 6c below.

The StarSQL software is distributed as a compressed file that you download from the StarQuest Ventures Web site.

1. Log on as an Administrator.
2. Download StarSQL using the download link provided in step 2 and uncompress the file into a temporary directory.
3. Run the setup.exe program from the StarSQL installer image.
4. Select the option to perform a Typical installation and respond to the prompts accordingly.

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 6b: (Client-Platform Administrator task) License the StarQuest Products

Estimated Time: 5 minutes

StarQuest products require a valid set of licenses for evaluation purposes. You can request license keys for multiple products at once, as described below. If the computer running the StarQuest software has access to the Internet, follow the Online Licensing Instructions below. Otherwise, follow the Alternate Licensing Instructions.

Online Licensing Instructions

1. If the StarSQL License Configuration utility is not already running already running, open the StarLicense Configuration utility from Start --> Programs --> StarSQL --> License Configuration.
2. Click on the License Online tab. Select a License Lock Type, enter in the Registration Key provided in the download confirmation email from step 2, and click Get License.

When the request successfully completes, the license(s) for the software you are registered to use appear in the License Keys list of the Licenses tab. The Registration Key may produce several License Keys, depending on the products you are registered to use. Subsequent attempts to use the same Registration Key will result in the identical License Key(s) being retrieved.

Alternate Licensing Instructions

1. On the computer where the StarSQL software is installed, open the License Configuration utility from Start --> Programs --> StarSQL --> License Configuration. Record the Host ID displayed on the License Online tab.
2. From a computer that has access to the Internet, click on or browse to the following URL:

   http://starcust.starquest.com/Registration/index.html#license

3. On the StarQuest Online Licensing Form web page, enter in the email address used for the original download request and the Registration Key you received in the download confirmation email.
4. Enter the Host ID value recorded previously and select the "Node-locked" license option. Click Next.
5. Review the information provided. If any changes are required, click Previous and modify the values as needed. Otherwise, click the Accept button.
6. Copy the license key(s) displayed on the web page. You will also receive an e-mail with the license key(s).
7. On the computer running the StarQuest software, open the License Configuration utility from Start --> Programs --> StarSQL --> License Configuration.
8. Under the Licenses tab, click the Add button and enter in the license key. Repeat until all of the license keys have been entered.

Step 6c: (Client-Platform Administrator task) Create ODBC DBMS Data Sources

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 and from.

Create ODBC DSN for the DB2 source database

Create an ODBC System DSN to connect to the source database.

NOTE

If you plan to use DB2 Connect as the ODBC driver, refer to the appropriate IBM documentation for instructions on how to configure a DB2 Connect DSN. If you need to replicate LOB data, in the DB2 Connect data source configuration add the LONGDATACOMPAT parameter from the Advanced Settings tab of the CLI/ODBC Settings and set this value to “As LONGVAR data”. Set the Maximum LOB column size value if desired, or accept the default value. Proceed to the next section to create the destination DSN. Proceed to the next section to create the destination database ODBC DSN.

The following steps describe how to create a StarSQL ODBC data source to connect to the DB2 source database.

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 the database connectivity parameter values obtained by the DB2 Administrator in step 3 .
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 DB2 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.

Create ODBC DSN for the destination database

The following steps describe how to create a SQL Server ODBC data source that connects to the SQL Server destination database. If your destination database is not SQL Server, refer to the documentation for the ODBC driver specific for your database for instructions on creating ODBC DSNs.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows this is located in the menu path Control Panel—>Administrative Tools—>Data Sources (ODBC). If using a Windows 64-bit operating system, create a 32-bit DSN using the 32-bit ODBC Data Source Administrator (odbcad32.exe) found in the \WINDOWS\SysWOW64 directory.
2. Click the System DSN tab of the ODBC Data Source Administrator window.
3. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server for the driver and click Finish. (If you are using SQL Server 2005/2008, be sure to select the SQL Server driver instead of the SQL Server Native Client driver.) 
4. Specify a name and select which SQL Server you want the data source to connect to, and click Next to proceed.
5. Continue through the remainder of the wizard and click Test Data Source to ensure the DSN can connect to the specified SQL Server. If you do not see TESTS COMPLETED SUCCESSFULLY, modify the data source configuration until it can successfully connect to the server.

Additional References

StarSQL Quick Start Guide
StarSQL User’s Guide
StarSQL Online Help

Step 8: (SQL Server Administrator task) Start the SQL Server Service and Configure ODBC DSN

Estimated Time: 10 minutes

The client computer on which you will install SQDR must have a supported 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/2008 Express Edition free of charge from Microsoft to use as a control database for the Data Replicator. Review the SQL Server considerations above for more information.

SQL Server 2008 users should verify that they have already installed the SQL Distributed Management Objects (SQL-DMO), which are included in the Microsoft SQL Server 2005 Backward Compatibility Components available at the Microsoft Download Center.

Start SQL Server Service

The SQL Server service must be running for the Data 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.

Create a SQL Server ODBC DSN for the Control Database

Define the ODBC DSN for the SQL Server database that the Data Replicator Service will use as a control database. This should be a different DSN than one that will be used to access the destination database (created in the previous step).

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows this is located in the menu path Control Panel—>Administrative Tools—>Data Sources (ODBC).

NOTE

If using a 64-bit SQL Server, you will need to create both a 32-bit and a 64-bit ODBC DSN for the SQL Server database with identical data source names. This is because the SQDR Configuration uses SQL Server's 64-bit osql application to create its control database, while the SQDR service uses the 32-bit DSN to communicate with the control database. Create the 64-bit DSN using the (64-bit) ODBC Data Source Administrator found in Administrative Tools. To create the 32-bit DSN, use the 32-bit ODBC Data Source Administrator (odbcad32.exe) typically located in \WINDOWS\SysWOW64 directory.

2. Click the System DSN tab of the ODBC Data Source Administrator window.
3. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server for the driver and click Finish. (If you are using SQL Server 2005/2008 Express, be sure to select the SQL Server driver instead of the SQL Server Native Client driver.)  If you already have a data source defined for the SQL Server database you want to use for the Data Replicator control tables, select it and click Configure to verify the properties.
4. Specify a name and select which SQL Server you want the data source to connect to, and click Next to proceed.
5. Continue through the remainder of the wizard and click Test Data Source to ensure the DSN can connect to the specified SQL Server. If you do not see TESTS COMPLETED SUCCESSFULLY, modify the data source configuration until it can successfully connect to the server.

Step 9a: (Client-Platform Administrator) Install 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.

1. Log on as an Administrator.
2. Download SQDR using the download link provided in step 2 and uncompress the file into a temporary directory.
3. Run the setup.exe program from the SQDR installer image.
4. Respond to the prompts accordingly to complete the installation.

At the end of the installation, the installer prompts you to start the ODBC Administrator and/or the SQDR Configuration. You do not need to launch the ODBC Administrator, as ODBC DSNs were created in steps 6c and 7 above. Select the option to launch the Configuration after the installation completes and proceed to the next step.

Step 9b: (Client-Platform Administrator) Configure the StarQuest Data Replicator Service

Estimated Time: 10 minutes

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 (in step 7) to access SQDR's control database and enter the SQL Server credentials for a user who is a member of the sysadmin server role.
   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 default option Create a new control database and click Next.
3. Leave the default database name of ControlDB or enter an alternative name. Leave the schema name field blank, as it will default to "dbo". Click Next.
4. Select Automatic as the Service Startup Type and choose the System Account as the Log On As user. If you would like to change the Data Replicator service properties, we recommend doing this in Windows Services once the SQDR configuration has been completed. Choose the correct Service Locale for the local Windows system and click Next.
5. Review the configuration summary, choose to start the Data Replicator Service and Manager and click Finish to save the Data Replicator configuration.

When the Data Replicator Manager launches for the first time, a License Configuration dialog prompts you to enter your license keys. Since the license keys have already been requested, click OK to close this dialog.

Step 9c: (Client-Platform Administrator) 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 the three license keys entered (with PROD IDs "SQ", "DR", and "U+) and click OK. See step 6b if you have not entered license keys for using the evaluation software.
3. Stop and restart the SQDR service in order for the license keys to take effect. In the left pane, right-click the Service node for your computer and Stop the service. Right-click again and choose Start.

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. Click on the Advanced tab.

SQDR will automatically detect any available Capture Agent schemas and enable incremental replication.

3. On the Advanced dialog, set the default values for Database and/or Object Schema, as appropriate for your database. These values are necessary for the Copy Member operations.
4. We recommend that you supply a Notification Address (host name or IP address) of the computer that you want to receive a UDP notification when changes are committed to this source. Configuring a notification address will reduce the time it takes for the SQDR client to detect and retrieve source table changes. The drop-down menu lists the IP addresses of all active network connections.
5. Click OK to create the source.

 

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.

3. On the Advanced dialog, set the default value for the Database and/or Object Schema, as appropriate for your database. These values will be used by the SQDR subscription wizard to populate configuration fields and during Copy Member operations. Click OK to create 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 in the Comment field. The default values for the remaining options are typically suitable for first-time users. To learn more about a particular option, click the Help button in this dialog to launch the SQDR online help topic for this tab.

2. On the Advanced tab, select the Source and Destination that will be used for each incremental replication subscription in this group. Ensure that the Receive Change Data Notifications from Capture Agent option is selected (i.e., checked). Leave the Apply options and Subscription Defaults as their default values and click OK to create 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.

3. 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.
4. 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 9d: 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 source table is actively monitored by SQDR Plus 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 was intended to help you install the StarQuest software and perform a data replication operation as quickly as possible.  Please continue to experiment with the software by adding additional subscriptions, scheduling replication operations, and performing SQL operations on data before and/or after it is replicated. As you experiment with the SQDR Plus and SQDR software, refer to the product documentation for more information.

• The SQDR Plus for DB2 Universal Database User's Guide 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.
• The SQDR Plus Documentation Addendum and the SQDR Documentation Addendum contain information on new features and supplement the SQDR Plus User's Guide and SQDR online help.

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 2009 StarQuest Ventures, Inc. All rights reserved.

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