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

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

Description:

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'.

30Ascii
4444FFHex
000030 

 

A negative value looks like this:

-30Ascii
4446FFHex
000030 

 

Solution:

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:

000030Ascii
FFFFFFHex
000030 

 

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

This may not be what you want when dealing with external data.

0003}Ascii
FFFFDHex
00030 

 

If your data definitely does not have any negative values then this option may be the best because of all the options 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:

+00030Ascii
4FFFFFHex
E00030 

 

or a negative number

-00030Ascii
6FFFFFHex
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 possibly 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:

+0000000030Ascii
4FFFFFFFFFFHex
E0000000030 

 

or a negative number

-0000000030Ascii
6FFFFFFFFFFHex
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:

+00030Ascii
4FFFFFHex
E00030 

 

or a negative number

-00030Ascii
6FFFFFHex
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
4FFFFF4Hex
E00030B 

 

A negative value looks like:

-00030.Ascii
6FFFFF4Hex
000030B 

 

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