Loading Data 
Author Message
 Loading Data

Hello All,
  I'm new to Informix and RDBMS so forgive me if my questions are
elementary.  My company is in the process of changing from a legacy
enterprise-wide software package to a software package written in
Informix 4GL with an Informix 7.3 database.  
  We are getting ready to start moving data from our current system
over to the Informix database.  I know there are several utilities
under Informix that enable the loading of data.  My question is this:
How do these utilities handle the repetitive loading of the same data?
For example let's say I have an inventory related table that contains
item_number, description, quantity_onhand, qty_committed, qty_on_order
with item_number being the primary key.  I know the item_number and
description columns won't change much so I want to load these now.
However, the columns that contain quantities will constantly be
changing as orders are placed, shipped, and P.O.'s are received. So I
can load them now but will have to obviously reload these columns
right before we are ready to go live.  If I load the table with all
the columns now but just want to load in the quantities at a later
time can I do this?  Will it fail because the primary key already
exists?  Will it over-write the existing record, erasing the fields
that I don't load this second time?  Do all of the Informix utilities
handle this type of load in the same manner?  If the Informix
utilities cannot handle this type of database loading are there 3rd
party utilities that can do this?
I know my example is pretty simple.  In this case why not just load
all the fields again?  But in my actual environment there are several
tables that have many columns.  A lot of these columns have pretty
static data.  We would like to be able to load these somewhat static
columns far in advance of the go live time.  And then just load the
non-static fields at the last minute.

Thanks,
John Welch
Systems Analyst
Brockway-Smith Co.



Wed, 18 Jun 1902 08:00:00 GMT
 Loading Data

John,

I've left all of your post intact an just interspersed a few
comments. You've posted to the right newsgroup, and did sell to
explain the context as well. There are ways around the problem,
but you are right to suspect that "load" will not like to process
the same data twice.

Quote:
> Hello All,
>   I'm new to Informix and RDBMS so forgive me if my questions are
> elementary.  My company is in the process of changing from a legacy
> enterprise-wide software package to a software package written in
> Informix 4GL with an Informix 7.3 database.
>   We are getting ready to start moving data from our current system
> over to the Informix database.  I know there are several utilities
> under Informix that enable the loading of data.  My question is this:
> How do these utilities handle the repetitive loading of the same data?
> For example let's say I have an inventory related table that contains
> item_number, description, quantity_onhand, qty_committed, qty_on_order
> with item_number being the primary key.  I know the item_number and
> description columns won't change much so I want to load these now.
> However, the columns that contain quantities will constantly be
> changing as orders are placed, shipped, and P.O.'s are received. So I
> can load them now but will have to obviously reload these columns
> right before we are ready to go live.

Hmm, it's really a question of data migration and moving to live
(he said in obvious mode). I would have been tempted to drop the
table and load the whole lot again as part of the move to live
process, but it depends how much data you have. If the data is
small enough to create the whole table in one go, then this is
more auditable. If it's a big table, I can see how you might want
to spread the load. Can you experiment with the High Performance
Loader, that might give you enough speed to go for a "big bang"
approach. Otherwise I think you will need a roll-your-own
solution which loads the base data, and then updates these
records with the new quantities, etc. Is there anyway to
gradually phase in your new software in the cut-over? Maybe some
kind of parallel run would obviate the problem?

Quote:
>  If I load the table with all
> the columns now but just want to load in the quantities at a later
> time can I do this?  

LOAD FROM <whatever> INSERT INTO, dbimport and the HPL will not
play this game. You really need to hack together some utility to
do this. I don't recall seeing something that will do it for you,
because generally it's hard to guess what data structures and
sources people will be migrating. Informix might be able to help,
and it won't do any harm to have a look around the IIUG archives
at www.iiug.org.

Quote:
> Will it fail because the primary key already
> exists?  Will it over-write the existing record, erasing the fields
> that I don't load this second time?  Do all of the Informix utilities
> handle this type of load in the same manner?  If the Informix
> utilities cannot handle this type of database loading are there 3rd
> party utilities that can do this?
> I know my example is pretty simple.  In this case why not just load
> all the fields again?  But in my actual environment there are several
> tables that have many columns.  A lot of these columns have pretty
> static data.  We would like to be able to load these somewhat static
> columns far in advance of the go live time.  And then just load the
> non-static fields at the last minute.

Is there any way you can change the schema? The static columns
and the frequently changing columns might be better in different
tables, especially from a performance viewpoint. There are a lot
of tuning options in Informix, and you will want to treat
slow-changing tables differently from fast-changing ones (as a
for instance, the index fill factor can be much higher for static
data). It would seem logical to make a schema where relatively
slow-changing stock descriptions are separate from stock levels.
Of course, you may not have the luxury of playing with this,
especially close to go-live time...

--
:etrow nosraeP werdnA



Wed, 18 Jun 1902 08:00:00 GMT
 Loading Data


Quote:

>   I'm new to Informix and RDBMS so forgive me if my questions are
>elementary.  My company is in the process of changing from a legacy
>enterprise-wide software package to a software package written in
>Informix 4GL with an Informix 7.3 database.

That's an oxymoron, surely?

Quote:
>   We are getting ready to start moving data from our current system
>over to the Informix database.  I know there are several utilities
>under Informix that enable the loading of data.  My question is this:
>How do these utilities handle the repetitive loading of the same data?
>For example let's say I have an inventory related table that contains
>item_number, description, quantity_onhand, qty_committed, qty_on_order
>with item_number being the primary key.  I know the item_number and
>description columns won't change much so I want to load these now.
>However, the columns that contain quantities will constantly be
>changing as orders are placed, shipped, and P.O.'s are received. So I
>can load them now but will have to obviously reload these columns
>right before we are ready to go live.  If I load the table with all
>the columns now but just want to load in the quantities at a later
>time can I do this?  Will it fail because the primary key already
>exists?  Will it over-write the existing record, erasing the fields
>that I don't load this second time?  Do all of the Informix utilities
>handle this type of load in the same manner?  If the Informix
>utilities cannot handle this type of database loading are there 3rd
>party utilities that can do this?

Depends on how you do it. Depends on how you do it. Depends on how you do
it. Depends on how you do it. I don't know of any.

Quote:
>I know my example is pretty simple.  In this case why not just load
>all the fields again?  But in my actual environment there are several
>tables that have many columns.  A lot of these columns have pretty
>static data.  We would like to be able to load these somewhat static
>columns far in advance of the go live time.  And then just load the
>non-static fields at the last minute.

I can tell that you're already making a swift transition to relational
technology. Anyway, one way of doing this would be to load the latest data
into a copy of the database and generate a bunch of UDPATE statements to
copy over the latest values into the "real" database.

Or you could use 4GL to do the same thing. Or you could do a fresh load
(which is probably my preference anyway).
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 Loading Data
Welcome John!

Get Jonathan Leffler's sqlcmd package, available from the IIUG Software
Repository.  One of the included utilities beginning with version 5.2
or so is sqlupload which can either insert a new row or update an
existing row based on rules you supply.  It even has options to try
either the update or the insert first as one method will be faster than
the other depending on the ratio of inserts to updates expected.

Art S. Kagel

Quote:

> Hello All,
>   I'm new to Informix and RDBMS so forgive me if my questions are
> elementary.  My company is in the process of changing from a legacy
> enterprise-wide software package to a software package written in
> Informix 4GL with an Informix 7.3 database.
>   We are getting ready to start moving data from our current system
> over to the Informix database.  I know there are several utilities
> under Informix that enable the loading of data.  My question is this:
> How do these utilities handle the repetitive loading of the same data?
> For example let's say I have an inventory related table that contains
> item_number, description, quantity_onhand, qty_committed, qty_on_order
> with item_number being the primary key.  I know the item_number and
> description columns won't change much so I want to load these now.
> However, the columns that contain quantities will constantly be
> changing as orders are placed, shipped, and P.O.'s are received. So I
> can load them now but will have to obviously reload these columns
> right before we are ready to go live.  If I load the table with all
> the columns now but just want to load in the quantities at a later
> time can I do this?  Will it fail because the primary key already
> exists?  Will it over-write the existing record, erasing the fields
> that I don't load this second time?  Do all of the Informix utilities
> handle this type of load in the same manner?  If the Informix
> utilities cannot handle this type of database loading are there 3rd
> party utilities that can do this?
> I know my example is pretty simple.  In this case why not just load
> all the fields again?  But in my actual environment there are several
> tables that have many columns.  A lot of these columns have pretty
> static data.  We would like to be able to load these somewhat static
> columns far in advance of the go live time.  And then just load the
> non-static fields at the last minute.

> Thanks,
> John Welch
> Systems Analyst
> Brockway-Smith Co.



Wed, 18 Jun 1902 08:00:00 GMT
 Loading Data
I want to thank everyone that responded to this inquiry.  These
answers certainly give me something to think about and have me pointed
in the right direction.  The sqlcmd is something I will definitely
check out.

Thanks Again,
John Welch



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Problems loading data: LOAD vs. DTS

2. loading data dump from ASE 11.9 to ASE 12.0 sets it to offline after load

3. Error loading data via BUTIL -LOAD

4. Loading data from non-relational data stores

5. loading data into a table from an external data source

6. Trouble loading data via query to SQL Server

7. Part 2: Trouble loading data via query to SQL Server

8. How to Import/Export/Load data in SQL Server

9. Can′t use DTS to load data from SQLserver into Analysis Server

10. Loading data from ASCII files into tables

11. How to load data from ASCII text file?


 
Powered by phpBB® Forum Software