Fast Unload : Character x'FF' found in unloaded data with OUTPUT-FORMAT FIXED

Products

• Fast Unload for DB2 for z/OS

Issue/Introduction

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.

Resolution

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.