Speed and Secure DB2

 

UDB Unload, Query, Load, Mask & Test

Challenges

 

As a DBA using the DB2 Universal Database (UDB) on Linux, Unix or Windows (LUW) -- or DB2 on the iSeries or a mainframe -- you will eventually encounter issues with:

  • Large table unload and load speed
  • Complex query or reorg performance
  • Database migration or replication
  • Change data capture and slowly changing dimensions
  • Personally identifiable information (PII)
  • Safe, relational test data creation

SQL procedures can be hard to code, and take too long to run even after optimization. Online reorgs are too slow and offline reorgs are hard to set up.

 

PII data must be protected at rest and in motion. Creating referentially correct test data is cumbersome; it usually requires access to production data, which may not be available or represent future requirements.

Solutions

IRI software addresses DB2 UDB performance bottlenecks and security concerns in production and testing:

Fast Unloads

IRI FACT (Fast Extract) software speeds UDB unloads. FACT uses native SQL syntax and DB2 drivers to dump table data to flat files in parallel. Use FACT to speed archive, migration, ETL, replication, reorg, and reporting.

FACT is also supported in database data migrations to and from DB2 on LUW performed in the IRI NextForm DBMS edition or IRI Voracity platform.

Fast Loads, ETL, & More

The SortCL program in IRI CoSort and Voracity handles table data via ODBC -- or even faster on flat files outside UDB (via IRI FACT) -- for high-volume:

    index pre-sorts for DB2 load operations
    data transformation (filter, sort, join, aggregate, etc.)
    delta (change data capture / slowly changing dimensions) reports
    data protection (column encryption, masking, etc.)
    database migration and replication operations

The "CoSort Load Accelerator for DB2" seamlessly improves the speed of bulk imports into UDB and ESE (v5-9) by as much as 6X over the DB2 Load utility. "CLA4DB2" replaces the internal sort library on LUW platforms with a compatible sort routine. Keeping tables in lookup and join orders, and thus improving query times, is much easier with CoSort in place.

Combine SortCL with FACT and DB2 Load in the IRI Workbench GUI for Voracity (built on Eclipse™) for fast offline reorgs, ETL, database migration, and data replication operations.

DB2 Column Protection

Use IRI's purpose-built data-centric protection product FieldShield -- or its parent CoSort product or Voracity platform -- for static (SDM) or dynamic data masking (DDM) of PII and other data at risk within DB2 on any platform connected through ODBC and JDBC.

Use wizards in their common IRI Workbench front-end to discover and classify the data in your tables, then automatically and globally apply the masking functions appropriate for each column based on your business and data privacy law compliance requirements. Various reporting and audit logs are produced during these processes to help you validate search, and verify masking, jobs.

DB2 Test Data

Use IRI RowGen if you need safe, intelligent, and referentially correct test data for DB2 ... especially if you do not have access to real data. RowGen parses the DDL to generate and populate any number and size of target tables while preserving the structure and relationships of data in production.

Note that all of these products -- from FACT to CoSort to NextForm and from FieldShield to RowGen -- are all supported in IRI Workbench, and most are included in Voracity platform subscriptions.