When we import data from the outside world, one can never be sure it will fit the mold forever into the future.
But, it may be very important that if 100 rows arrive, all 100 rows are recorded.
If this is important, make all the fields alpha. Then if any invalid data arrives, you will still have a record of it.
Space consumption is a secondary concern to data integrity. If myfldN fields are identifiers, as opposed to quantities or values, then they should be alpha. For example the source could use numeric part numbers today, and switch to alphanumeric next year. You don't want to be in a position down the road to have to redesign the database due to changes outside your organization.
If for example myfld3 is actually a value such as cost, then create a new field COST which is packed or zoned. At import time, move myfld3 into COST only if it is a valid numeric value.
Then when the trading partner sends you 100 rows, you will always have those hundred rows stored in your database for auditing, even if the rows had 10 junk (invalid numeric) rows.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, January 22, 2014 2:05 PM
To: Midrange Systems Technical Discussion
Subject: Re: Philosophical question...define table to be load from external data with integer or decimal fields...
Perhaps this will help explain my quandry...
The data source provided DDL for Oracle and MS SQL Server...
So I've got
--Oracle
create table mytbl
(myfld1 number(5) not null
, myfld2 number(2) not null
, myfld3 number(10) not null
, myDate varchar2(10) null
<...>
--MS SQL Server
create table mytbl
(myfld1 integer not null
, myfld2 integer not null
, myfld3 integer not null
, mydate varchar(10) null
<...>
The hard copy documentation has
Data
Element Record Type/
Name Position Length Picture
Myfld1 1-5 N/5 9(5)
Myfld2 6-7 N/2 9(2)
Myfld3 8-17 N/10 9(10)
Mydate 18-26 N/8 9(8)
Note: The data is coming as a pipe delimited text file, so the "Record
Position" above really doesn't apply.
Now, I've already decided to define mydate as an actual date :) Now I'm
trying to decide how to define myfld1, myfld2, myfld3.
My personal preference for new internal stuff is always integer if I'm
dealing with whole numbers. I've never really dealt with a "legacy" data
feed. Now I can certainly do legacy to match...but should I?
Charles
Charles
On Wed, Jan 22, 2014 at 1:51 PM, Charles Wilt <charles.wilt@xxxxxxxxx>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 MyField 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?
Charles
midrange.com
