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.

John Welch wrote: 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. > 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? > 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 notplay 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. > 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

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 John Welch wrote: > > 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.

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