IRI FACT™ (Fast Extract) rapidly unloads large tables to external files using DB-native APIs, SQL SELECT syntax, and a choice of split (parallel) query methods.
Unlike other database unload methods (e.g., Oracle data pump), FACT creates portable flat files. Your 'dump-table-to-file' data is thus quickly available for any purpose, including: reorgs, transforms, pre-load sorting, migrations, change and summary reporting, ETL, replication, testing, and protection.
If you also use the IRI Voracity platform or IRI CoSort product, you can use the SortCL program to perform or accelerate all these post-extraction steps at once. But you do not have to use SortCL; i.e., once the data are in flat files, you can do anything you want with them.
Speed
FACT's extract performance is second to none. Using superior connection protocols, parallel hints and queries, and a variety of other proprietary techniques, FACT's unload rate is much faster than database spool or export functions.
Benchmarks also show that flat files are produced many times faster through FACT than through SQL*Plus or ODBC operations. FACT consumes few system resources and does not require load balancing.
Formatting
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 (with reduction)
- packed decimal to numeric conversion
- varchar length displays
- null number casting
- line-feed character removal
Invocation
To unload any supported database, run FACT from the command line:
fact ini_file
or from the free IRI Workbench GUI, built on Eclipse™. Workbench users of the IRI Voracity platform for ETL and other data management activities can also configure FACT specification and execution through various wizards or state-of-the-art workflow diagrams.
The 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 format and destination (pipe or files).
FACT also works with (via metadata creation) the CoSort SortCL data transformation and reporting program, and with database load utilities (like Oracle SQL*Loader). This consolidates entire database reorg or data warehouse ETL processes into single-pass operations. Such users can also perform reporting, replication, conversion, and row/column security operations at the same time.
Integrated Reorg and ETL Operations
With FACT, DBAs and data warehouse architects can optionally pipe bulk unloads to complete very fast offline reorg or ETL processes.
Execute the FACT .ini file with a:
- CoSort SortCL job specification file (.scl) that references the .ddf metadata that FACT automatically creates
- SQL*Loader process that uses the control file metadata that FACT also automatically creates
or as part of a total ETL or reorg workflow designed and executed in the IRI Voracity platform, supported in the same Eclipse IDE, IRI Workbench:
For example, for Oracle, the operational template is:
fact -c ini_file; fact ini_file | sortcl /spec=transform.scl /spec=fact.ddf | sqlldr control=fact.ctl direct=true
This command initiates a rapid table extraction and pipes it to the CoSort SortCL program. SortCL will perform simultaneous field filtering, sorting, aggregation, conversion, protection, reformatting, replication, and/or reporting (transform.scl). In turn, one of SortCL's output targets (stdout.dat) can be piped, pre-sorted, in index order to SQL*Loader.
SortCL can create other outputs during this process as well, including: structured reports, CSV table replicas, XML files, web-ready reports, BI tool (cube) hand-offs, and so on. No other ETL solution offers this level of parallel processing performance, versatility, or ease of use, while simultaneously relieving the database of so much overhead.
Summary
IRI FACT is the fastest way to simultaneously:
- unload very large database (VLDB) transaction (fact) tables
- produce formatted flat files from a table
- create the control file metadata for database bulk load utilities
- create IRI CoSort (or Voracity ETL) metadata for data warehouse transforms, roll-ups, delta reports, and pre-sorted DB loads
- create IRI NextForm metadata for converting the exported data and file types
- create IRI FieldShield metadata for column encryption, de-identification, and masking
- create IRI RowGen metadata for generating safe test data in the table's format
