Can CA Fast Unload use complex SQL to manipulate DB2 data as it's unloading it?

Document ID : KB000048205
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

When data is unloaded from a table there may be a need to manipulate it into a different form or even to obtain business knowledge from it. Advanced SQL can be provided to CA Fast Unload in order to do that. The SQL-ACCESS EXTENSION parm can be used. It uses native CA Fast Unload access methods when possible to process SQL statements. It switches to DB2 SQL processing only for those SELECT statements that are not supported.

Solution:

Here is a sample of a complex SQL statement that CA Fast Unload can process to transform data into another form:

Column CREATE_TS is a TIMESTAMP data type.

The SQL below looks at CREATE_TS and figures out what Financial/Fiscal Year the date falls in and writes out the Financial/Fiscal Year start date for that date. It also writes it out in a different timestamp format stripping out the microseconds.

It utilizes CASE Expressions and various SQL Scalar functions, concatenation operators and arithmetic operators to produce a different result than the original which could be used to transport data to another platform or loaded into a different table altogether.

Assumption: Financial/Fiscal Year starts on the 1st of July.


FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
INPUT-FORMAT TABLE
IO-BUFFERS 50
LOAD-CONTROL FASTLOAD
OUTPUT-FORMAT DSNTIAUL
PART-INDEPENDENCE NO
SHRLEVEL CHANGE
SORTFLAG ALL
SORTSIZE 4M
SORTNUM 64
ESTIMATED-ROWS 7500000
SQL-ACCESS  EXTENSION
SELECT
STRIP(VARCHAR_FORMAT(CREATE_TS,'YYYY-MM-DD-HH24-MI-SS'))
||' '||
'FINANCIAL YEAR'
||' '||
STRIP(CHAR(
CASE WHEN MONTH(CREATE_TS)