Executing Fast Unload for Db2 for z/OS (PFU) unloading a DECIMAL 3,2 column with OUTPUT-FORMAT FIXED and noticed that before the column x'FF' is present.
The x'FF' is the null indicator.
Sample table:
CREATE TABLE authid.table
(EMPID INTEGER
WITH DEFAULT NULL
,DEPTID CHARACTER(8) FOR MIXED DATA
WITH DEFAULT NULL
,SALARY DECIMAL(9, 2)
NOT NULL
,BONUS DECIMAL(9, 2)
WITH DEFAULT NULL
)
IN dbname.tsname
APPEND NO
NOT VOLATILE CARDINALITY
DATA CAPTURE NONE
AUDIT NONE
CCSID EBCDIC;
Sample unload cards:
//SYSIN DD *
FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
EXCP YES
INPUT-FORMAT TABLE
IO-BUFFERS 50
LOAD-CONTROL DB2LOAD
OUTPUT-FORMAT FIXED
PART-INDEPENDENCE NO
SHRLEVEL REFERENCE
SORTFLAG ALL
SORTSIZE 4M
SORTNUM 8
ESTIMATED-ROWS 75
SQL-ACCESS ONLY
SELECT * FROM authid.table;
Sample data seen via RC/Update:
ssid ----------------------------------------------------------
N:EMPID N:DEPTID SALARY N:BONUS
N 6,000 Y -------- 5.99 N 5.99
N 7,000 Y -------- 5.99 N 5.99
N 8,000 Y -------- 5.99 N 5.99
Sample unloaded data showing the x'FF' in column 7.
=COLS> ----+----1----+----2----+---
000006
000017FF00000000000590000059
000070FF000000000009C000009C
--------------------------------------
000007
000015FF00000000000590000059
0000B8FF000000000009C000009C
--------------------------------------
000008
000014FF00000000000590000059
0000F0FF000000000009C000009C
--------------------------------------
Sample load cards:
LOAD DATA INDDN SYSREC01
RESUME NO REPLACE
EBCDIC CCSID(1027,5035,4396)
INTO TABLE authid.table
(
EMPID POSITION( 3: 6 )
INTEGER
NULLIF( 1 ) = X'FF'
,
DEPTID POSITION( 9: 16 )
CHAR MIXED ( 8)
NULLIF( 7 ) = X'FF'
,
SALARY POSITION( 17: 21 )
DECIMAL PACKED
,
BONUS POSITION( 24: 28 )
DECIMAL PACKED
NULLIF( 22 ) = X'FF'
)
The Null indicator can be removed by using the NOTNULL parm in the INTO statement of the unload.
SQL-ACCESS ONLY
SELECT
EMPID ,
DEPTID ,
SALARY ,
BONUS
INTO
EMPID INTEGER(4) NOTNULL,
DEPTID CHAR(8) NOTNULL,
SALARY DECIMAL(9) NOTNULL,
BONUS DECIMAL(9) NOTNULL
FROM authid.table;
The load cards now look like this:
LOAD DATA INDDN SYSREC01
RESUME NO REPLACE
EBCDIC CCSID(1027,5035,4396)
INTO TABLE authid.table
(
EMPID POSITION( 1: 4 )
INTEGER
,
DEPTID POSITION( 5: 12 )
CHAR MIXED ( 8)
,
SALARY POSITION( 13: 17 )
DECIMAL PACKED
,
BONUS POSITION( 18: 22 )
DECIMAL PACKED
)
The data now looks like this:
-------------------------------
000006 * *
0017444444440000500005
0070000000000000C0000C
-------------------------------
000007 * *
0015444444440000500005
00B8000000000000C0000C
-------------------------------
000008 * *
0014444444440000500005
00F0000000000000C0000C
-------------------------------
Note that NOTNULL treats the field output of a nullable column as though it were not nullable. If the nullable column has a null value in the unloaded row, the Db2 default for that data type is assigned.