Loader Regression Testing

Some customers have a need to know what testing we do for the Loader. This article is designed to reveal the dynamics of the Loader’s automated and randomized regression testing.

The Loader is a flexible tool that can do many things.  It is middleware and it uses a wide range of what we call “companion products”. To test all of the Loader features with all of the possible companion products and all of the versions of those companion products is a near overwhelming combinatorics issue.  Over time, we have varied versions of products and, on some testing platforms, have frozen versions so that we can validate new versions of the Loader for customers who have been forced to "freeze" their VMS, Rdb or target database environments.

Loader regression testing is designed to cover as many of these combinations as possible.  We use a large number of systems (over 80 cores), many virtual and many physical.  They are running various versions of OpenVMS and multiple versions of WIndows and Linux.  We run the regression cycles with multiple iterations randomly varying conditions. With the arrival of our 3Par SAN and with large memories for all operating systems and databases, we have managed to peg the CPUs on almost all the cores all the time.

The goal is to do as much as possible in a finite time frame and test unexpected combinations of features, targets, data types, transaction profiles, failure scenarios, etc.  This is done through randomization of parameters to test varying combinations of parameters and logical names. To this end, the testing environment depends heavily on the idea of "random".  That will be addressed later.

A Loader family is the term we use to describe the detached process running the Loader together with the sub processes for running the LogMiner from Rdb and potentially multiple Loader worker processes (aka threads.)  The family is the fundamental unit. A family is run  under the directives of various logical names and a Loader control file that specifies many "knobs" that the Loader has.  Validating the answers produced by a Loader family is what a test is all about.

A single test is called a cycle instance or just instance.  The term cycle is used because, at the successful completion of an instance, it reports success and then restarts from the beginning, thus recycling. Failures leave complete evidence of the cycle and allow diagnosis of what happened and why. 

A cycle may consist of one Loader family doing its work or may incorporate multiple families.  There is one cycle which has seven Loader families.  This last cycle dates from a time when the disk space to store a single source database was scarce and we were trying to get in as many tests as possible.  Today we tend to create cycles with one or two Loader families in them.

A cycle generally consists of a single DCL executive procedure which submits component DCL procedures and synchronizes with their completion.  Some cycles are very complex and these are managed by a Job Scheduler that allows for very complex and conditional execution of component DCL.

The source Rdb database provides the workload.  This database contains tables that contain every datatype that Rdb supports, including large character strings and other formats that sometimes stress software.  One table is stored in a mixed format area and a hash index is used to control physical placement of the rows. Triggers are used to make a complete audit of all changes and to record the TSN of the transaction (TSN) that made the change.

The actual workload is done by a C program which uses VMS RTL routines to generate a series of random numbers that cause it to make random changes to the source database.  The seed of the random numbers is the VMS system time so the sequences of generated values do not repeat.  Randomness applies to which rows are selected for processing, how many rows and which columns to change.  Sometimes columns are set to NULL and sometimes rows are deleted.  The probability of these actions is determined randomly for a cycle instance at the start.  Randomness also determines how large transactions are and which tables are processed in the transaction. The changes are also driven by a table that itself is calculated randomly at the start of the regression cycle.

At the beginning of a cycle, the source database is restored to a known state. After this, some random parameters for the workload program are stored in the database.  Once this restoration is complete, the target database(s), if any, are also restored.  If the target of the Loader is a database, one thing we need to test is DB-key replication. To support testing this case, one table is replicated by DB-key and is always copied to the target before testing starts.

There are multiple ways the testing initializes tables in the target.  The important point is that these are all independent of the Loader.  In the case of Rdb targets, we can use SQL attached to both databases and do simple table copies.  For tables in Oracle targets, we can use OCI connections and use SQL*Plus and Oracle linked tables and copy using Oracle technology. For SQL Server, we originally used linked tables and ODBC connections via Rdb Services.  For new JDBC targets (including SQL Server and Oracle) for the regression testing of version 3.5, we constructed a couple of small JDBC programs.  The first allows copying rows in one database to rows in another database.  The second program allows execution of arbitrary SQL statements in a database such as truncate table or, in fact, any other statement.  For all newer JDBC targets we use these two JAVA programs integrally in the regression cycles. This permits the entire cycle to be under the control of a VMS DCL procedure.

Not all targets are databases.  The FILE, API and Tuxedo targets are all non-relational mechanisms.  For the Tuxedo target, we have developed a multi-server Tuxedo application that receives FML messages from the Loader and parses and writes them to a file, one file per target. For the API target, the API shareable image writes to files also. Both API and Tuxedo target code simulate failure randomly. In any event, from any of the three we get files. The files are then parsed and the results stored in the source database to be used in the validation phase of a cycle instance.

After the database(s) or other targets are established, we run a program that randomizes the context of the cycle instance. It generates random values for logical names that can affect the Loader's behavior.  It also generates a random set of control file elements. 

The Loader family is then started (or multiple families are started) and the random workload is also started. There are multiple workloads for each cycle instance.  They each will have different parameters.  Running them all together produces an intense update rate suitable for testing the Loader under stress. After the random workload is complete, we run a "batch-like" workload which does big transactions.  

After the Loader families are started, the cycle starts a process to monitor the AERCP of the Loader families in the cycle.  This is done using the JCC_LML_DUMP_CHECKPOINT program.  We use the AERCP mechanism to determine when the Loader has completed its work.  When the check AERCP process notices that the AERCP has been constant for a while and if many families are participating in a cycle that all the AERCPs are the same, it exits thereby notifying the DCL controlling the cycle that it is time stop testing and start doing an evaluation of the work done. The Loader families for the cycle are shut down. After shutdown is complete for all families, the differences begin. 

The current differences model is to copy tables from the target database back to the workload database.  They may be compared there using SQL.  We have found this to be much faster than materializing the tables into files and doing differences using the VMS differences utility.  The differences are done row by row and column by column.

One important issue for comparing columns is dealing with floating point.  Different databases represent floating point differently.  Accordingly, the values for a floating point column may differ in the least significant bit(s) of the mantissa.  The row by row, column-by-column comparison is particularly appropriate for analyzing the precision of a floating point column.  The differences procedure will compute a difference betwen the source and target value and the compute the ratio of that value with the source database.  It uses that value as a tolerance.  The ratio must be less than  for F-float numbers 0.0000001 and for G-Float is 0.0000000000001.  These are tiny percentage values.

Once the differences processes are run for each table and each family, the resulting files for each are analyzed. If all show that there are no differences, email is sent reporting no differences. The result is also recorded in a central logging database together with the parameter and logical name values used during the cycle.  This report also includes a number of characteristics of the workload, such as the number of rows updated, deleted, or inserted.

A successful cycle (zero differences) will trigger a repeat of the cycle.

An unsuccessful cycle sends mail indicating problems and waits for analysis.

Some cycles contain logic that permits killing a Loader family process at a random time.  The victim Loader family is then restarted and allowed to complete to conclusion. The goal is to ensure that the Loader can restart from surprise interruptions. Customer interruptions include system crash, target crashes, exhaustion of VMS quotas by the Loader family or any other factor leading to surprise shutdown.

Some Loader cycles test "auditing" and comparisons are done with the Rdb audit tables instead of the base tables.

One Loader cycle tests to make sure we can "skip" a specific period of time and thereby some of the workload, because at least one customer uses a Loader feature to permit this.

Of course, there are cycles that test filters and the statements along with the transforms (MapResult) that were introduced in version 3.5.

One important criteria of the testing is that the there are multiple paths to getting data from one database to another.  The Loader is one path.  OCI, JDBC programs, ODBC linked tables provide a different path.  The the comparisons are not dependent only on the Loader.

Additional Loader cycles and testing criteria are added as customers determine new ways to use the Loader and as new features are added to the Loader.

Our goal is to identify as many issues as possible – no matter how strange the combination of circumstances – before releasing a new version of the Loader.