Hi,

I am looking for a way to find out if any modification has been made to
any rows within a range of rows since the last time I queried it(I have
this time). I also want to find out if any new records were added since
I queried the table the last time, if there has been a modification I
am gonna get that record again, if not, I don't wanna do it. Does DB2
provide any such facility? Or anything that I could use, that may help
me in building such an application?

Ciao, Draw

Draw wrote:

If I understand this correctly, you're looking to retrieve new or
changed records from a table (new or changed since the last time you
queried it)?

One relatively simple way to do this is to use something like a
last_modified timestamp field. For example:

CREATE TABLE sometable (
key_field INTEGER NOT NULL PRIMARY KEY,
another_field VARCHAR(30) NOT NULL,
last_modified TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL
)@

(note the @ statement terminator; if you're using the command line
processor, use -td@ to make @ the statement terminator; the reason for
using @ will become apparent below)

New records inserted into the table have the last_modified field set to
the current date and time (with sub-second precision), assuming they
don't explicitly provide a value for last_modified in the INSERT
statement.

Next, add a trigger on the table to update the last_modified field
whenever a record is updated:

CREATE TRIGGER sometable_modified
NO CASCADE BEFORE UPDATE
ON sometable
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW.last_modified = CURRENT TIMESTAMP;
END@

(because ; is used within the trigger's body to separate statements, we
need a different statement terminator to terminate the trigger
definition, hence the use of @ as a statement terminator)

Now, whenever a record is updated, the last_modified field will be
updated with the current date and time.

Therefore, to retrieve only records that are new or have changed since
the last time you saw them you simply query the table with something
like:

SELECT
key_field,
another_field
FROM sometable
WHERE last_modified >= 'yyyy-mm-dd hh:mm:ss'@

Where 'yyyy-mm-dd hh:mm:ss' is the date and time you last queried the
table.


HTH,

Dave.

--