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 |
Release: R20
Component: PFU
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.