|JCC's LogMiner Loader|
|Home||Training||Products||Services||List Server||Search Engine||SQL|
The JCC LogMiner Loader™ is a fast, powerful, flexible tool to reflect data changes from a source database to multiple targets. The source is an Rdb™ database. Targets can be other Rdb databases, Oracle® databases, a customer supplied API in XML format, Tuxedo, or can use a JDBC class 4 driver to write to an SQL Server database or another compliant target. The Loader is extensively tested and tuned for all recent versions of these companion products.
Topics on this page are:
Is the path to your data swamped and in danger of going under?
Who Uses the JCC LogMiner Loader?
Those who use the JCC LogMiner Loader™ recognize that the information stored in their databases is one of their most valuable resources. They cannot afford additional impacts on the production database resources, but they need one or more of the following:
- To have a composite of regional databases
- To have multiple copies or subsets of the corporate information to scatter to remote locations
- To be able to use the data warehousing tools of Oracle or other platforms
- To provide web access without performance impact on the production databases
- To apply tools not available in the source database environment
- To reorganize a huge, critical, and overworked database without major downtime
- To add coherency to an environment with more than one database platform
- To audit data changes and/or archive data
- To convert to another platform without interruption in support
- To provide realistic data for serious application testing
Further, those who use the Loader have some of the largest, highest throughput databases in the world, as well as some of the most stringent demands for timeliness.
An Oracle Rdb database may be configured such that the database engine logs all changes made to a production database into a special file called the after image journal. The after image journal (or AIJ) contains records that indicate the status of each and every database object at the completion of a transaction. The Oracle Rdb LogMiner™ tool uses the after image journal to extract the final copies of data rows as they appear at the end of each transaction.
The LogMiner output represents the changes to the source database. The JCC LogMiner Loader enables a database administrator to apply the contents of the LogMiner output to one or more targets.
When run in continuous mode, the Loader coordinates the actions of all the Loader processes and of the LogMiner. In continuous mode, the LogMiner Loader updates the target in near real-time.
The JCC LogMiner Loader™ and the Oracle Rdb LogMiner™ write entire transactions and do not lose transactions.
The Loader is tolerant of environmental and downstream difficulties. The Loader can be stopped to resolve difficulties. The Loader recovers from difficulties and interruptions without losing data.
The Loader does not interfere with systems operations such as backup. The Loader – running with the Continuous LogMiner – can resume in the backed up AIJs and automatically switch to the live AIJ, after processing the backed up AIJs.
The JCC LogMiner Loader™ and the Oracle Rdb LogMiner™ are most often used in continuous mode, as a “live feed.” They can also be run in a mode that uses only backed up AIJs or in a mode (Copy Mode) that takes output from the LogMiner and applies it as if it were running "live." Copy Mode is excellent for testing and for environments that do not provide fast, reliable network communication between the source and target.
The Loader may be configured to write all changes to the target or can be limited to a subset of tables, a subset of columns, and/or a subset of actions (insert, update, delete). A row can be included or excluded based on a filter applied to the row. Additional transforms are supported.
Monitoring, performance related characteristics, database administration options, and others are all configurable.
The JCC LogMiner Loader™ collects statistics and can display them in an online monitor, a file, or the log. The Loader also manages statistics from the LogMiner.
There are several styles of output available for the Loader statistics, including an online full-screen display and a Comma Separated Values (CSV) output suitable to interface with “T4 and Friends,” the Total Timeline Tracking Tools from OpenVMS Engineering.
The monitoring and logging tools can be constrained for normal operation or expanded for testing or resolution of a problem. Each step in the Loader processes can be thoroughly documented. Some indications of the behavior of the source and target are also reflected in the Loader monitor data.
The JCC LogMiner Loader™ impact on the system supporting the production database (the source) is generally negligible.
In continuous mode, the updates to the target are “near real time.” Since the Loader does not get any information until after the commit, large transactions can vary from real time. Small transactions will appear to be real time. Throughput in all cases is impressive; as is demonstrated in the case studies.
The Loader supports dynamically adjusting parallel processes. The minimum and maximum number of processes to use in parallel are configurable through the Control File and are also adjustable while the Loader is running.
The Loader provides configurable commit intervals to group source transactions into single target transactions to minimize overhead. If the source becomes quiescent, timeouts prevent the Loader’s stalling with a partially filled commit interval.
Many Loader families can run simultaneously from the same source database with differing targets and different configuration choices.
The JCC LogMiner Loader™ is a variety of ETL (Extract Transform and Load) software. The transforms supported can be combined to solve a wide range of issues with disparate information systems. The Loader can also be used to create intended differences in the source and the target.
Indexing and other physical database parameters can be different on the source and the target.
Even the primary key can be different, although, for updates, there must be some way to uniquely identify a row in the target. In fact, there is a mechanism for using the DBkey from the source as a key in the target to address situations for which there is no reliable natural key.
Configuration choices can limit the tables, rows, or actions replicated and row-based filtering can further define the subset of data written to the target.
Values can be materialized to provide virtual columns. The list of possible virtual columns includes such things as commit timestamp and other timestamps, values that can be used to partition the data, defined constants that can be added to the key to aid in rolling up similar databases with overlapping keys, Loadername, LSN (Loader Sequence Number), TSN, and other identifying criteria.
The Loader can cause commit rows in the LogMiner output to result in target rows in a materialized table. The columns in such rows can be any of the virtual columns.
Dates are properly transformed or transformed according to configurable settings.
Columns can have a “value if null” specified to use in the target, when the column is null in the source.
There is a configurable trim option for trailing blanks in going to non-Rdb targets.
The data type for representing the dbkey from the source database can be configured so that, when it is used to create a unique key in the target, it can have a data type compatible with downstream tools.
Additional DBA Support
The JCC LogMiner Loader™ is fully multi-variant. One version can be run while another is being tested.
There are procedures for generating template Control Files. For many of the Control File options, there are defaults that will be satisfactory, without needing to define the option.
A maintenance facility for the logical names used is part of the Loader kit.
Examples are included in the kit.
There are Loader features that aid testing the overall application and its impact on the database. These include the option of throttling Loader performance or setting Loader performance to emulate real time. The Loader can, alternately be set to run faster than real time by some set amount to test the performance of the overall system in the face of growth.
In addition, the Loader provides operator messages. What triggers an operator message and where the message should be displayed are both configurable. For example, operator messages can be used to provide an alert if the downstream processes are backing up and not able to absorb the Loader output.
The Data Pump
Downstream processes may do worse than just slow the throughput. When target databases lose data or are inappropriately updated, it is often possible to trace which data has become corrupted. In these cases, as well as for initial population of the target, the Data Pump of the JCC LogMiner Loader™ is a valuable resource.
A Data Pump can be configured to use a Structure File of SQL statements and a Driver File with selection criteria. The Structure File can represent hierarchical structures. For example, if a certain block of accounts were corrupted, both the accounts and the child tables of bills and payments may need to be updated.
The Data Pump is packaged with the Loader and takes advantage of the Loader’s nature. The Data Pump works by making no change updates to the source database, causing the Loader to write the unchanged data to the targets.
The Data Pump can also be used for initial population of a target.
Users of the Data Pump are finding it fast compared to alternate approaches. The reliability and configurability are also important.
Companion Products, Versions, and Testing
The JCC LogMiner Loader™ is rigorously tested, continuously, with an automated, random regression test that generates different options and, also, randomly “attacks” processes to emulate failure scenarios.
Loader testing has included Open VMS for both alphas and Integrity. The JCC LogMiner Loader has been fully supported on Integrity platforms since Version 3.0.
The Loader has been tested with all recent versions of the companion products – Rdb, Oracle, SQL*net, OpenVMS – and some of the older versions, as well. For the JDBC target, the Loader has been tested with SQL Server and some of the available class 4 drivers. Details are provided in the version portion of the release notes. Should you have any doubts about your proposed combination of products, please contact JCC.
The JCC LogMiner Loader is a general-purpose tool and can assist a database administrator with a variety of functions. The following examples demonstrate a selection of the varied uses of the Loader:
Case 1: DB Reorg when Downtime is Not Possible
A company did not provide routine maintenance for the Rdb database because the company could not afford to be without it for long enough to accomplish the maintenance. In time, the company “hit the wall” as mixed areas filled up. They exceeded the limits of the database physical design. This was a 60 Gb database and traditional reorganization techniques would require 24 hours of down time. Business growth was healthy, but the computer support was already not adequate to handle past growth. Database reorganization could be deferred no longer.
A copy of the production database was made. The reorganization was accomplished on the copy and was extensively tested. Meanwhile, business continued on the production database. When the reorganized database was ready, the Loader was used to apply the data changes that had occurred in the production database since the copy was made. The total downtime for user support was thirty-five minutes, including additional testing.
Case 2: Making HUGE Data Volumes Available to Additional Tools
A company wanted to begin using a wider range of products and applications. All of these were to be dependent on the data in the production Rdb databases. The original application had divided the data into almost thirty related Rdb databases because the data volumes, update rates, and query demands were so large that partitioning was deemed necessary to successfully manage the huge data volumes.
The Loader provides XML to a customer-defined API and also provides data to a Tuxedo application. A set of Loaders handle all the transactions for all the source databases while maintaining transaction consistency and interacting with the targets. The Loaders process the data at a peak rate of 2,400 rows per second.
Case 3: Meeting the Challenge of Timely Web Access
The primary Rdb database resides at the central office. Other offices are around the world. The critical challenge came when the production database could not offer information quickly enough to satisfy queries in the remote offices and to capture the business that was available through the internet.
Using the Loader has permitted distributing multiple copies of the database with up-to-the-second accuracy. The query databases can be tuned differently from the source database to get the best query responses. Bookings are rolling in.
Case 4: Application Testing with Realistic Data
A company is already using the Loader to replicate data to products supporting a variety of specialized functions. The company needs to test a replacement for the downstream database and applications. The Loader is used to run transactions packaged from actual AIJs. These are, then, run in real time emulation.
They could, alternately, be run to feed the actual data at a configurable pace that exceeds the production rate. This option tests scalability.
Case 5: Speed and Extremely Large Transactions
An enterprise regularly peaks at over 4,000 transactions per second and the Loader maintains near realtime performance.
The DBAs did a convert/commit and had to change the truncate table statements into delete from table, resulting in transactions of half a million rows. It took an enhancement to the Loader to provide the performance desired. The Loader, now, changes locking strategies upon encountering extremely large transactions and processes them with priority so that normal processing may continue. The threshold for the strategy change is, of course, configurable.
Case 6: Displaying Time Critical Information on a Map
A utility company with all its data in an Rdb database wanted to use a graphics package to display the (service) outage information. The package did not have an interface to Rdb and nobody wanted to re-enter data.
Using the Continuous LogMiner Loader, data updates are available in both the Rdb database and the Oracle 10g database in apparent real time. The combination yields significant improvements in service, communication, and safety.
Following a recent weather related outage that put one third of the customers without power, the press raved about the service provided and did feature presentations on the technology.
Given that success, additional applications of the Loader have added SQL Server databases to support web applications. One provides a subset of consumer billing and payment data. Another provides installation data for subcontractors.
Case 7: Building a Coherent Information Resource
A number of regional databases and aging applications were hindering a company with critical schedules to keep. The Loader was used to build a coherent database from the regional databases. Having more timely information available and being able to coordinate among the regions was so successful that they moved on to other applications, including distributing subsets of the data where they needed it.
Now, with much better control of their data and applications, they are looking at a complete re-write, built, from the beginning, with the Loader as an integral part.
A Basic support contract for one year is provided as part of the software purchase. Basic support includes the right to new releases (during the term of the support contract), plus call in and e-mail support during normal JCC business hours.
An upgrade to Gold support includes all Basic support, plus 24 X 7 coverage.
Basic or Gold support contracts can be renewed for subsequent years.
JCC Consultants are also available to work with you on-site on a consulting basis.
License, Documentation, and Kit
The distribution kit may be obtained from the JCC FTP site FTP.JCC.COM via anonymous ftp. This path also includes the documentation and release notes for recent versions, together with training seminar materials and presentations on the Loader. You will need a license key to be able to use the product.
Temporary licenses are available for investigation prior to purchase.
Send mail to firstname.lastname@example.org with additional questions or comments.
Copyright © 2005 - 2009 JCC Consulting, Inc.
Last modified: February 25, 2009
Trademarks and credits.