Use complex SQL to manipulate Db2 data being unloaded with Fast Unload
Article ID: 48205
Updated On: 05-22-2023
Products
Issue/Introduction
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 Fast Unload for Db2 for z/OS (PFU)
in order to do that. The SQL-ACCESS EXTENSION parm can be used. It uses native 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.
Environment
Release: r20
Component: Fast Unload for Db2 for z/OS
Resolution
Below is a sample of a complex SQL statement that 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 calculates the Financial/Fiscal Year the date falls into 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.
Table DDL:
CREATE TABLE authid.xxxxx
( AGT_ID CHAR ( 8 ) NOT NULL
FOR SBCS DATA
, AGT_NM CHAR ( 40 ) NOT NULL
FOR SBCS DATA
, AGT_CORE_CREATE_TS TIMESTAMP NOT NULL
, AGT_CORE_UPDATE_TS TIMESTAMP NOT NULL
, AGT_CREATE_TS TIMESTAMP NOT NULL
, AGT_UPDATE_TS TIMESTAMP NOT NULL
,PRIMARY KEY
(AGT_ID
)
)
WITH RESTRICT ON DROP
IN xxxxxxxx.xxxxxxxx
CCSID EBCDIC
;
Fast Unload cards:
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(AGT_CORE_CREATE_TS,'YYYY-MM-DD-HH24-MI-SS'))
||' '||
'FINANCIAL YEAR'
||' '||
STRIP(CHAR(
CASE WHEN MONTH(AGT_CORE_CREATE_TS) <= 6
THEN YEAR(AGT_CORE_CREATE_TS) - 1
ELSE YEAR(AGT_CORE_CREATE_TS)
END
||'/'||
CASE WHEN MONTH(AGT_CORE_CREATE_TS) <= 6
THEN YEAR(AGT_CORE_CREATE_TS)
ELSE YEAR(AGT_CORE_CREATE_TS) + 1
END
))
||' '||
' FINANCIAL YEAR START DATE'
||' '||
STRIP(CHAR(
CASE WHEN MONTH(AGT_CORE_CREATE_TS) <= 6
THEN
TIMESTAMP_FORMAT(
STRIP(CHAR(YEAR(AGT_CORE_CREATE_TS) - 1)) ||'-07-01',
'YYYY-MM-DD-HH24-MI-SS-NNNNNN')
ELSE
TIMESTAMP_FORMAT(
STRIP(CHAR(YEAR(AGT_CORE_CREATE_TS)))||'-07-01',
'YYYY-MM-DD-HH24-MI-SS-NNNNNN')
END
))
FROM authid.xxxxx;
Where the data column looks like this:
AGT_CORE_CREATE_TS
2022-07-26-03.10.37.000000
the result is:
Command ===>
****** ***************************** Top of Data **********************************************************
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
000001 2022-07-26-03-10-37 FINANCIAL YEAR 2022/2023 FINANCIAL YEAR START DATE 2022-07-01-00.00.00.000000
Additional Information
Feedback
