MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

Re: Philosophical question...define table to be load from external data with integer or decimal fields...



fixed

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








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact