On 31 Oct 2012 14:58, Anderson, Kurt wrote:
I have approval to add 5 identifier columns. I was going to add
them as 9p00 (5 bytes) each. However, I could get the same values
for less space if I used the B data type (9b00), <<SNIP>>
In my 10+ years of RPG programming, I've never dealt with a B type
on a file (in fact, only ever in IBM supplied APIs, and even then
I converted them to integer types).
From the perspective of the database, the DDS data type "B" [9B00] is
the same as the SQL INTEGER data type. The database treats them
identically, even if some other interfaces [e.g. CL, Query/400, DDS,
etc.] might limit the field to the 9-digit presentation; at least
without doing something explicitly to override that effect. For
example, in Query/400 the report writer allows overriding the 9-digit
presentation to a 10-digit presentation; the same report writer, for the
sake of the STRSQL, implicitly does that expansion because SQL's
perception of INTEGER is incompatible with any 9-digit restriction.
The database SQL treats them [INTEGER and 9B00] identically also.
Either will be presented [from the SQL catalog information for columns,
SYSCOLUMNS] as DATA_TYPE='INTEGER' of LENGTH=4 bytes of storage. AFaIK
the DSPFFD still shows both as 9B00 [BINARY (9,0) 4-byte] for legacy
I did find that there is a EXTBININT header spec, but I don't really
see the purpose to using it (I mean I know you can then access the
10th digit, in the case of 9b00, but does that 10th digit get stored
on the file?
The database stores and retrieves the full signed [binary] integer.
The database will place or read the full four-byte buffer value for the
SQL INTEGER or the DDS 9B00 data type, irrespective of what any
particular interface to the database might have chosen to impose as a
restriction. A possible restriction could be an overflow on read or
write, diagnosed by the interface, not the database; the database will
always accept any value because there is no possible invalid value.
Are there any gotchas to using the B data type?
There may be some interfaces that restrict access only to the 9-digit
value; most probably just requiring some special action to ensure the
full 10-digit numbers will be available. Any restrictions however are
outside the database, as the database honors the full even-byte storage
for any integer data-type value. If the 9-digits are all that will ever
be required, as implied by the possible alternative\choice of the 9P00,
then there should be nothing about the weirdness associated with 9-digit
restrictions for use of 9B00 that could ever be a "gotcha" to give any
The Packed Binary Coded Decimal data type does provide data
validation, whereas INTEGER data can not [and therefore will not] be
validated by the database. That capability may be valuable, or probably
in the given scenario, just more of both storage and CPU costs.