Innovative Routines International (IRI), Inc., The CoSort Company

UDB Load, Reorg, ETL Optimization + Test Data

Challenges:

You may face one, or more, of these time-consuming issues:

• Unloading and loading large tables
• Routine utility operations (reorgs)
• Complex queries
• Database migration or replication
• Safe, relational test data creation

Specific performance diagnoses and tuning also take time, and may affect other users. Stored SQL procedures may also be programmed inefficiently, require optimization, and then still take too long to run. Creating referentially correct test data is cumbersome.

Solutions:

IRI offers three metadata-compatible products to help:

1) IRI's Fast Extract (FACT) for DB2 speeds unloads. FACT dumps data out of UDB in parallel into flat files you can use to stage, replicate, and report on table data. This is also an important first step in data warehouse process and reorg acceleration. And, flat files are the way to go in volume.

2) CoSort Version 9's SortCL tool works on flat files outside of UDB to accelerate high-volume:

• transforms (sorts, joins, aggregates)
• delta reports (changed data capture)
• data protection (e.g. column encryption)
• index pre-sorts (on the primary table key)

Run 1 and 2 together ...
FACT can work through metadata and pipes with CoSort's SortCL tool to perform fast reorg, replication, encryption, ETL, and BI operations, all in one I/O pass. In the CoSort phase, you can:

• Further filter and format the extracts
• Sort on the index key for direct path loads
• Join and aggregate multiple tables (files)
• Capture and segment new (changed) data
• Replicate and migrate (convert) your data
• Generate custom detail and summary reports
• Encrypt, de-ID or mask sensitive fields

CoSort's seamless "Load Accelerator for DB2" can improve the speed of high volume imports into UDB and ESE (v5-9) by as much as 6X. CoSort's parallel load accelerator replaces the internal sort library with a compatible CoSort routine. Alternatively, you can also run CoSort against external (flat) files (sorting on longest index key) to speed bulk loads, since this will bypass the database loader's sort engine.

3) To rapidly populate UDB or ETL operations with safe data, use IRI's high-volume test data generator, RowGen. RowGen uses your data models to automatically generate the test data for an entire database with referential integrity.