FACT for DB2 | DB2 Unload Utility | Export DB2 to CSV, text filesFACT-DB2

Home » Products » FACT-DB2

Fast DB2 Unload & Reorg Software

Click here to
see IRI Tools
in the
Data Center

A fast DB2 extract step can be a critical component of database reorgs and migrations, data warehouse ETL operations, reporting, replication, and protection. IRI's Fast Extract (FACT) utility is a parallel DB2 table export program that creates portable flat files, and the metadata for optional, simultaneous transformations and loads.

Summary

FACT is a DB2 unload utility designed to speed DB2 export to text file jobs. FACT can export DB2 UDB and ESE tables using SQL syntax and multiple CPUs. It protects login information, reformats and splits data, and writes metadata for CoSort (SortCL) transforms and reports, DB2 Loads, FieldShield protections, RowGen test data, and NextForm file/data migrations.

Features/Benefits

• Rapid DB2 table unload
• Accelerates reorgs, ETL
• Speeds database migrations
• T&L create metadata options
• Feeds CoSort, RowGen jobs
• Reformats, logs performance
• Extracts CMP, LOB and LONG

Introduction

Today's DBAs and data warehouse architects face increasing table volumes and processing bottlenecks. Service-level agreement (SLA) commitments and shrinking production windows require fast database reorg and data warehouse extract, transform, and load (ETL) solutions that deliver high performance and database availability.

Efficient database reorgs consist of fast unload, sort and reload operations. Similarly, in large ETL and database migration environments, data warehouse experts like Dr. Ralph Kimball recommend that large data volumes are most efficiently staged in flat files. That is because the fastest sort, join, convert, aggregation, report, and reload processing occurs in the file system. Transforming big data within a database or the BI layer is taxing and inefficient. Data stored in proprietary formats and structured DB/ETL systems are optimized for queries, not for transforms or loads.

Description

IRI's Fast Extract (FACT) tool rapidly unloads large DB2 tables to external files, using all supported combinations of native SQL SELECT features. With FACT, you can export DB2 to CSV, for example. DB2 export to text file operations facilitate: reorgs, transforms, pre-load sorting, migrations, change and summary reporting, ETL, replication, testing, and masking.

If you also have the CoSort product's SortCL tool, you can perform or accelerate all of these processes at once. But you do not have to use SortCL; i.e. once the DB2 data are in flat files, you can do anything you want with them.

FACT's extract performance is second to none. Using superior connection protocols, parallel hints, and a variety of other proprietary techniques, FACT's unload rate is much faster than a SQL sppol function or a proprietary export function. FACT consumes only a small amount of system resources, without the need for load balancing.

In addition to speed, FACT includes several useful file layout options, including:

• custom date and timestamp formatting
• numeric data re-alignment
• fixed or variable record formatting
• delimiter and frame character choices
• column trimming (width reduction)
• packed decimal to numeric conversion
• character length displays
• null number casting
• line-feed character removal

To unload DB2, run FACT from the command line:

fact ini_file

where ini_file is a simple-to-use text or XML file that identifies the database, extracts with a SQL SELECT command, and specifies the unload target's format and destination (pipe or files). You can execute FACT from the shell, a batch script, or program.

To summarize, FACT is a fast way to simultaneously:

• perform bulk DB2 unloads in parallel
• produce formatted files from a table
• create DB2 Load file metadata
• create CoSort metadata for transforms
• roll-ups, delta reports, and pre-load sorts
• create FieldShield metadata for column
   encryption, de-identification, and masking
• create RowGen metadata for generating
   safe DB2 and other test data
• create NextForm metadata for converting
   the exported data and file types

Integrated Reorg and ETL Operations

Unlike other DB2 database utilities, FACT creates portable flat files along with their metadata so DBAs and data warehouse architects can optionally pipe bulk unloads into tools like CoSort for very fast DB2 reorg or ETL processes.

Execute the FACT .ini file together with a CoSort SortCL job specification file (.scl) that references the .ddf metadata that FACT automatically creates, and with a DB2 Load process that uses the control file metadata that FACT also automatically creates.

Many output formats can be created by SortCL using FACT - extracted DB2 data, including structured reports, CSV table replicas, XML files, web-ready reports, BI tool (cube) hand-offs, and so on.

Platform Availability

FACT for DB2 is currently available for AIX, HP-UX (PA-RISC and Itanium), Linux (x86 and IBM zSeries), Solaris (SPARC only), Tru64 Unix, and Windows 2000, 2003, XP and Vista. If you use Oracle, see FACT for Oracle.

CoSort and its Sort Control Language (SortCL) program for data transformation is available on these platforms, as well as many others where you might choose to otherwise process the FACT-extracted data. See the CoSort product page for the platforms that CoSort supports.

Licensing and Support

FACT license fees cover perpetual use and depend on the number of CPUs or cores you wish to license. FACT copies can be licensed alone, or in discounted conjunction with a CoSort license bundle.

Annual maintenance charges for FACT, which is an optional coverage for technical support and software upgrades, costs 20% of the base license fee, or 15% with a CoSort license on the same platform.

IRI, The CoSort Company