insert new rows?

[dbatlanta]

I have a source in DB2/AS400 and target in Informix. The source doesn't have any PM key or any unique key. All the columns contain dublicate datas, or it is not unique. I want to implement incremental load for the target table. But I am not sure on how to implement this since nothing in the table is unique. I only want to insert new rows which doesn't exist in the target table.

Thank you all for you help in advance.

 

[blom0344]

How do you expect to define 'new rows' if you cannot define a primary key in the target, then what do you mean with the concept 'new rows' ?

Inserting only new rows is BUILD UPON the rule that you have a PK in your target.

If there is any structure/logic in the target you are writing to (and believe there has to be) then you should be able to define a PK (even if it is one consisting of many fields)

Or ,in other words , what type data is stored in the Informix table?

Ties Blom

 

[dbatlanta]

Ties Blom,

I created the target table in Informix. I only need to extract some of the columns from the source DB2/AS400, so I created the table in informix using these limited columns. And then I loaded from the Source to the target. I am not sure how exactly I should be creating the PM key. Can I just make one of my columns PM key? Because the source doesn't have a PM key. I read in the manual you could use a datestamp using the Mapping variable to do the incremental load, but I don't have any date columns or do I create one in the target? Or do I truncate and load everytime I run the workflow? But I heard truncating and loading every run is not a good practice. That is why I wanted to keep the history in the target table and just load the new changed data everytime I ran the workflow.

Thank you for your time.

 

[blom0344]

Let's go through this one step at a time.

A PK is a set of one or more fields from a table that is unique. Example, suppose you have a table that stores revenue by customer for each month. The PK will then be :

customer / month

INFA allows you to tackle this in more than one way,depending upon your requirements.
Let us consider them one at a time:

1. Inserting after truncate

Without a PK this may be an option. It may allow you to use external bulk-loaders for performance-gain.
However, for a period your table will be empty and what happens if the source table is purged after some time.
This option should only be used if all others are not viable

2. Using update else insert

Essentially every time source data is written to the target and when the PK for the records already exists the row is overwritten, otherwise it is inserted.
Obviously, this is only possible with a meaningful PK.

3. Using datadriven strategy

This allows the greatest control, cause by caching the target as lookup you can ascertain beforehand if a record should be treated as insert/update/ delete.
However, performance is often reduced, cause caching the target may be quite demanding. Also, you need a PK to control the update strategies in your mapping

4. Using a timestamp to perform only inserts.

If the source data has a date/timestamp field that indicates when the record was inserted, you could use this to write inserts on a daily basis. (no PK needed)
However,this is much trickier than one would think. If you need to postpone the workflow for one or more days than you risk entire days of data missing.
(And yes, we have several jobs running that have been build that way, even after trying to prevent them from being accepted in production)

It is VERY unlikely that you cannot define a PK on the AS400 source table. If this is an OLTP database there is a 99% chance that it can be defined.
Bye the way what are you running on the AS400. It may be something I have worked with as well

Ties Blom