Fast Unload : How do I unload SMALLINT or INT data to produce leading zeros instead of spaces?

Products

• Fast Unload for DB2 for z/OS

Issue/Introduction

If SMALLINT or INT data is unloaded using Fast Unload as OUTPUT-FORMAT EXTERNAL the data is unloaded with leading spaces x'40'.

Example value of SMALLINT field is 30.

The unloaded data would look like this. The six byte field has leading spaces x'40'.

30	Ascii
4444FF	Hex
000030

 

A negative value looks like this:

-30	Ascii
4446FF	Hex
000030

 

 

Environment

Release: R20
Component: PFU

Resolution

Firstly the PFU parms must be set correctly.

Set this parm.

OUTPUT-FORMAT FIXED

After the unload parms we must alter the normal select statement.

There are a number of outcomes that could be chosen depending on your requirement.

The options are not in any specific order.

Option 1

SMALLINT data could be converted to DECIMAL ZONED data using an INTO clause.

SELECT
COLUMN1
INTO
COLUMN1A DECIMAL ZONED

This produces the following result data:

000030	Ascii
FFFFFF	Hex
000030

 

A negative value produces this with the sign as part of the last char.

This may not be what what is required when dealing with external data.

0003}	Ascii
FFFFD	Hex
00030

 

If your data definitely does not have any negative values then this option may be the best.  It does not unload a sign character at the start of the field.

Option 2

SMALLINT data could be converted to INTEGER EXTERNAL data using an INTO clause.

SELECT
COLUMN1
INTO
COLUMN1A INTEGER EXTERNAL(6)

For a SMALLINT Using INTEGER EXTERNAL(6) will produce:

+00030	Ascii
4FFFFF	Hex
E00030

 

or a negative number

-00030	Ascii
6FFFFF	Hex
000030

 

When dealing with a SMALLINT you must specify (6) as the length otherwise the data is unloaded as a full integer as in option 3 below.

If your data may contain negative numbers and the ultimate target of your unloaded data is on another external system then this option is probably the best one for input to external non-DB2 applications.

Option 3

INTEGER data could be converted to INTEGER EXTERNAL data using an INTO clause.

SELECT
COLUMN1
INTO
COLUMN1A INTEGER EXTERNAL

For an INTEGER Using INTEGER EXTERNAL will produce:

+0000000030	Ascii
4FFFFFFFFFF	Hex
E0000000030

 

or a negative number

-0000000030	Ascii
6FFFFFFFFFF	Hex
00000000030

 

Option 4

SMALLINT data could be converted to CHAR data using an INTO clause.

SELECT
COLUMN1
INTO
COLUMN1A CHAR(6)

Using CHAR(6) will produce:

+00030	Ascii
4FFFFF	Hex
E00030

 

or a negative number

-00030	Ascii
6FFFFF	Hex
000030

 

Note: for an INTEGER it should be CHAR(11) and for a SMALLINT it should be CHAR(6). If you just leave off the length and just have CHAR, then Fast Unload will default to 11 and 6 respectively depending on the data type.

Option 5

SMALLINT data could be converted to DECIMAL EXTERNAL data using an INTO clause.

SELECT
COLUMN1
INTO
COLUMN1A DECIMAL EXTERNAL

Using DECIMAL EXTERNAL will produce:

+00030.	Ascii
4FFFFF4	Hex
E00030B

 

A negative value looks like:

-00030.	Ascii
6FFFFF4	Hex
000030B

 

This last option may be the least useful as it has a trailing period to contend with.