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

It has the benefit that it is only visible to SQL when you mention it in an SQL statement. It is not included when you SELECT * from the table. INSERT does not expect a value for it unless you explicitly indicate you are inserting into it.

But you'll get a huge problem if in your RPG program an external data structure is used for to receive the data selected with SELECT *!!!
Since the external data structure includes all columns.
Sure SELECT * is not the best choice for embedded SQL!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Sue Romano
Sent: Wednesday, 24 September 2025 00:53
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Extract new rows only from a table with SQL to SQL server

Can you really not add a column, even if it is not disruptive to the SQL application designed to use the table? If the table is used for any native access, the following solution would not work.

I suggest looking into adding a ROW CHANGE TIMESTAMP column that is IMPLICITLY HIDDEN.

This column would be assigned a value whenever an insert or update of the row occurs.

It has the benefit that it is only visible to SQL when you mention it in an SQL statement. It is not included when you SELECT * from the table. INSERT does not expect a value for it unless you explicitly indicate you are inserting into it.

Sue Romano
Db2 for IBM i development

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?

Sue Romano
Db2 for IBM i development

--
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.