Re: which would be more suitable for ID codes? Integer or Packed?
Knowing that they are not intended for arithmetic operations, I would
opt solely for minimizing the storage they require, given the
definitions of these /numbers/ for their number-of-digits. But I would
choose Packed [SQL DECIMAL data type], for each component of the
three-part ID.
If I was worried that the ID-codes might change to include alpha,
then I might just go purely character with the inherently larger disk
storage requirements for the physical data and access paths, and greater
CPU requirements for character translation [CCSIDs] and Sort Sequence.
If I was not concerned about a change to alpha, then I would certainly
not give any consideration for an expansion of the ID-code; i.e. ensure
that the values could fit in the same size numeric type if expanded,
such as choosing SMALLINT for myfld2. But I would also most definitely
*not* choose SMALLINT for the N(5) nor INTEGER for the N(10) [like was
shown in the earlier reply with a link below; INTEGER being 4-byte
binary for the DB2 for i SQL, presumably the same in the other SQL
rather than being equivalent to a BIGINT], because then there is already
an inability to hold the largest possible value of an N(10) according to
the written requirements. To be sure, I would not depend on the
knowledge that, of the existing data, the current values are all "less
than 1000".
Reference to the earlier message with the other SQL definitions
[CREATE TABLE requests] and the apparent requirements [for number of
digits surely, though possibly also numeric implied]:
http://archive.midrange.com/midrange-l/201401/msg00514.html
I would probably choose to use the following:
create table mytbl
( myfld1 decimal( 5) not null /* 3-bytes */
, myfld2 decimal( 2) not null /* 2-bytes */
, myfld3 decimal(10) not null /* 6-bytes */
, myDate /* I would demand some historical and use information
before I would commit to a decision on this one */
...
, primary key (myfld1, myfld2, myfld3)
)
Regarding my choices: The myfld2 could just as well be NUMERIC
instead. But I have to admit first a preference for Packed Binary Coded
Decimal, and second a preference for physical storage viewing in hex,
because the first is packed, that the others would be packed. And
because I am much quicker at using decimal digits, knowing that the
first component of a key is 00333 I can see that conspicuously with
either BCD data type [using *CHAR display for zone and *HEX for pack],
whereas for any /integer/ data type I will be seen taking out my hex
calculator to determine what I am looking at ;-) Similarly... While
searching on '00333' is easy for zoned or char, I consider searching on
x'00333F' to be almost as easy to type [and there is a bonus, that such
a search string also will avoid unwanted matches across alphanumeric
data]. And I will not ask my calculator presently, to tell me what to
compose as my x'########' search, if the value had been INT :-) to
emphasize my earlier point.
Regards, Chuck
On 23-Jan-2014 08:57 -0800, Charles Wilt wrote:
Any thoughts on which would be more suitable for ID codes? Integer
or packed?
On Wed, Jan 22, 2014 at 3:25 PM, Charles Wilt wrote:
In the example given, Myfld1-3 are ID codes making up a composite
primary key.
<<SNIP>>
On Wed, Jan 22, 2014 at 2:46 PM, CRPence wrote:
On 22-Jan-2014 10:51 -0800, Charles Wilt wrote:
Philosophical question for you all...
I'm creating a new table to hold data received from an external
source.
The specs given include
Name, Type/Length, Picture
MyFld, N/5 , 9(5)
So MyFld is a 5 digit number...
I could defined this as Packed/Zoned 5,0
Or I could use integer (or even small integer since the
current number of values is less than 1000)
Since I know DB2 and RPG for that matter perform best with
integer, I'm leaning that direction. But I can't help but think
that Packed (5,0) is more correct.
Thoughts?
I would decide according to the storage requirements for the data
[in both dataspace and an access path], and according to how the
data will be used in programs and queries which is likely
dependent on what the data represents. <<SNIP>>
midrange.com
