| Overview
StarQuest Data Replicator (SQDR) is a software product that replicates tables of an IBM DB2 database and inserts them in Microsoft SQL Server or Oracle RDBMS. SQDR also replicates tables of SQL Server or Oracle and inserts them in a DB2 database of a remote IBM host.
To access IBM databases, SQDR uses the StarQuest StarSQL.
DB2-to-SQL Server and SQL Server-to-DB2
Features of SQDR are the following:
- Runs on NT Server.
- Runs on top of the StarSQL ODBC driver.
- Uses SNA or TCP/IP to communicate with an IBM host.
- Sends and receives data in messages that adhere to the IBM Distributed Relational Database Architecture (DRDA) format.
- Connects to DB2 through DRDA facilities in the host, which are standard in all DB2 systems.
- Supports all security facilities of the IBM host systems to which it connects, for example RACF and ACF2 for DB2/MVS, OS/400 security for AS/400, and UNIX security for DB2 Universal Server.
- Requires no special, non-standard software on the IBM host.
- Operates with DB2/MVS; DB2/VM-VSE; DB2/400; DB2/2; DB2/6000; DB2/Universal Server or Common Server for OS/2, Windows NT, AIX, HP-UX, and Solaris.
- Supports subsetting the database horizontally and vertically, i.e. by rows and/or columns.
- Supports automatic scheduling of replications.
- Supports "before" and "after" processing through stored procedures.
- Notifies users of successful and unsuccessful completions.
- Provides processing statistics.
- Runs from within SQL Server in same way as replication between SQL Servers.
- Connects to Oracle through ODBC interface.
- Runs from applications or from interactive sessions.
Operation
As shown in the diagram above, StarQuest Data Replicator provides an interactive user interface and an application program interface (API).
Through the interactive user interface, one may do the following:
- Start SQDR and select source and destination tables.
- Specify source and destination table properties.
- Create and schedule a subscription - a replication to be run at a later time.
- Specify horizontal and vertical selection criteria - for selection of table rows and columns.
- Select the destination table.
- Specify "before" and "after processing - stored procedures to execute before and after transfer.
Through the Application Program Interface, an executing application may invoke all the functions supported by the interactive interface. This allows an application program to make use of SQDR in a way that conceals the operation from the application user. SQDR provides a convenient call-level interface for use by application programs in most languages.
Applications
Decision Support
One of the frequently seen applications for data replication is decision support. Typically, these applications do not need the most up-to-date information. For example, it usually does not matter when studying monthly and annual business trends to have information that is up to the second. Also, record-by-record access to data on IBM hosts must compete with production data processing in these environments. Therefore, it often proves beneficial to transfer a database to a local machine, thereby burdening the production host system with only one access to its database. After transferring a copy of the host data to SQL Server or Oracle, analysts may read and re-read the data many times on their local systems without impacting host processing.
Database Subsetting
SQDR provides convenient dialog boxes and API verbs by which users may subset the database being replicated. Subsetting may be horizontal or vertical; which is to say users may limit the transfer of data to particular rows or particular columns or both. For example, an analyst may wish to look at sales data only for the July and August and would select only the rows of the database corresponding to these months. Similarly, another analyst may wish to look only at sales of a particular product and would select only the columns of the database corresponding to this product.
Once selected, the database subset in the target RDBMS is easier to manage and use than the huge database superset in DB2. Most analysts running decision support applications understand and appreciate the value of subsetting and use the technique frequently. However, the technique is a valid one as well for production data processing applications. For example, a desktop sales application might use SQDR to download every day a subset of the company's price list. The application might need prices of only certain products (vertical subsetting) and only current pricing (horizontal subsetting).
Data Warehousing
Often production data processing systems do not store historical data in DB2 that may be valuable for trend analysis and similar decision support applications. One solution is to schedule regular SQDR replications from DB2 to SQL Server or Oracle, thereby accumulating needed historical data. Over time, this replication creates a data warehouse that is valuable for historical analysis and other purposes.
Data Cleaning
Frequently production systems undergo change that add and subtract fields in the databases they use. Also, database fields tend to change their definitions over time. Such additions, subtractions, and changes make it difficult to accumulate information over time and make it necessary to cleanse the data. To accomplish this cleansing requires some careful alteration of the data in ways that are not easy to do in production mainframe or midrange IBM systems. Rather, such cleansing requires finesse: making a change, examining the change, testing the resulting database, backing out the change, trying another change, and so forth. This is easy and safe to do on replicated, where there are many tools for conducting these steps.
Data Pushing
SQDR includes the ability to transfer data to DB2, the reverse of normal replication. This is easy-to-do as shown in the example dialog boxes below. Data pushing allows a PC-based application to (1) replicate data from DB2 to SQL Server or Oracle, (2) perform local processing that changes the data in the local RDBMS, and (3) return the updated data to DB2. Note that data replication does not support data locking and two-phase commit that are necessary to ensure correct synchronization of data stored in distributed data network models. However, for many casual data processing requirements, using periodic two-way replication is a convenient way of managing distributed data.
Replication Versus Direct Database Access
A Windows user usually has two ways to access DB2 data. One way is to replicate the data from DB2 to a local RDBMS and then operate on the data in Windows. SQDR supports this approach. A second way is to run an application in Windows that, during its execution, issues SQL commands to the remote database. StarSQL for Windows and Windows NT supports this approach. The configuration is similar to that for SQDR.
As shown in the diagram above, a client application in Windows or Windows NT issues ODBC commands to StarSQL to send SQL statements to DB2.
The above direct connection configuration complements the SQDR configuration. For example, an application might need certain information in real time or may need to update information immediately. Meanwhile, the same application might use replicated data in a local RDBMS for certain information that is not so time sensitive.
The following screens show an example of using SQDR
Start SQDR and select source and destination tables.
Specify source and destination table properties as required.
Subscription Wizard - choose a table.
Specifiy horizontal selection criteria - for selection of table rows.
Select the destination database and request bulk copy or ODBC insertion - bulk copy is faster; ODBC logs database changes.
Specify vertical selection criteria - for selection of table columns. Combine fields and change data types if required.
Specify "before" and "after" processing - on the source and destination databases.
Review replication statistics in detail.
Configuration
The figure below shows in the protocol stacks that implement SQDR:
- StarQuest Data Replicator co-resides with SQL Server in Windows NT.
- SQDR accesses the StarSQL driver using a standard ODBC interface.
- StarSQL - the ODBC driver for accessing DB2 - converts ODBC commands to DRDA messages.
- SQL commands transfer data to the host in DRDA messages, which are an efficient format for specifying one or more SQL commands and their result sets. For efficiency, one DRDA message may contain multiple SQL commands.
- On the host, the DRDA messages are processed by DRDA facilities that are standard for all IBM implementations of DB2. These host-resident facilities are called DRDA Application Servers, and they handle StarSQL and other clients, which are called DRDA Application Requesters. In MVS, the DRDA Application Server is called Distributed Data Facility (DDF). DRDA is the most efficient way of processing DB2 facilities from a network. Other methods, such as running an application in TSO, CMS, CICS, or IMS typically require three times as much host processing.
- All versions of DB2 support DRDA and may be accessed by SQDR, StarSQL, and SNA Server as shown in the above diagram. Supported versions include DB2/MVS; DB2/VM-VSE; DB2/400; DB2/2; DB2/6000; DB2/Universal Server or Common Server for OS/2, Windows NT, AIX, HP-UX, and Solaris.
- Upon processing the SQL commands sent by SQDR, DB2 returns result sets in DRDA messages. These go to SQDR in the Windows NT system and are inserted into DB2 as required for the replication.
© 1998 StarQuest Software, Inc,. All rights reserved. This document is for informational purposes only.
The information contained in this document represents the current view of Microsoft Corporation on the Microsoft issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
Microsoft's support services are subject to Microsoft's then-current prices, terms, and conditions, and are subject to change without notice.
MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft and Windows are registered trademarks, and Windows NT SQDR are trademarks of Microsoft Corporation.
Oracle is a registered trademark.
IBM, OS/2, AIX, MVS and AS/400 are registered trademarks, and DDF, TSO, CMS, DB2, NCP, CICS, IMS, are trademarks of International Business Machines Corporation.
HP-UX is a trademark of Hewlett-Packard.
Solaris is a registered trademark of Sun Microsystems.
All other trademarks or registered trademarks are the properties of their respective owners.
Back to the White Papers Summary
To find out more about StarQuest products,
fill out this information request form or e-mail to sales@starquest.com.
Products | White Papers | Customer Support | Partners | About StarQuest | Press & Events
Copyright 2000 StarQuest Software, Inc. Please read our legal notice.
Contact webmaster@starquest.com with site comments.
.
|
