Improve DB2 Load, Reorg and Unload Performance

Home » Solutions » ETL DB Acceleration » DB2

Improve DB2 Performance

UDB ETL & Reorg 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 loads into UDB and ESE (v5-8) by as much as 6X. CoSort's parallel load accelerator replaces the internal sort library with a compatible CoSort routine. In DB2 v9, you can use a pre-CoSort operation on external (flat) files to speed bulk loads by bypassing the database's indexing 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.

IRI, The CoSort Company