Re: Using the B data type in files

On 31 Oct 2012 14:58, Anderson, Kurt wrote:

<<SNIP>>

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

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

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.