Re: Extract new rows only from a table with SQL to SQL server

Hi Jim,

I might have some ideas for you.

#1 if the "table" is a PF now

You could replace the existing PF with a table that contains row change timestamps and so on.

Then replace the existing PF with a LF that contains the same fields (ignoring the new timestamp fields) and results in the same record format. And then do the same with all old LFs.

In the end, the RPG programs wouldn't need to be recompiled because the record level ids wouldn't change.

#2 using triggers

You could create a second table for the transfer - with the same unique key, or with the RRN of the original file as a field.

On insert - simply insert the row into the transfer table.

On update - do a compare and write or update the record to the transfer table if needed.

On delete - delete the record from the transfer table, if it exists there.

Use the transfer table and delete the transferred rows from there (or set a marker/timestamp).

#3 using the journals

If the table is journaled (or if you set a journal on it) you can use that to keep track of changes.

Extract all changes from the last sync to now from the journal - do the same changes on the SQL server - and remember the new sync timestamp for the next run.

If I think long enough, I might come up with some more ideas. The best one would be, just not to sync to MS-SQL and using the IBM i database - but I know, that this is sometimes not an option.

HTH and kind regards,
Daniel

Am 23.09.2025 um 23:54 schrieb Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>:

The current process running has an SQL server using ODBC running a query against a IBM i table with 6 million rows (table scan, no selection criteria) and updating a SQL server table every night.

Right now, the process is a refresh of the 6 million rows on SQL server. In reality we only need the newest rows since the last run. Ordinarily I would just use a timestamp on the table to pull rows since a specific time, but this table does not have a timestamp on it (JDE supplied table so I cannot modify it)

Is there a way we can use SQL to get only the newest rows on the table? (Triggers are not available for the same reason I can’t modify the table)

My guess is no, without a timestamp we cannot, but hopefully someone will have an enlightened answer.

Might temporal tables play in this scenario?


--
Jim Oberholtzer
Agile Technology Architects




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.