DB2 Unload

deybumba_2k · New User Joined: 09 Jan 2006 Posts: 17

Hello,
I am working in a shop where there is DB2 on AS400. The tables in DB2 does not have any"update datetimestamp" column. Now the requirement is to extract rows of few tables which are updated everyday.

For example if today is 06/07, in the nightly batch job, I want to extract the rows of the table which were updated on 06/07.

One option is to insert an additional column for datetimestamp and change all programs to update it going forward whenever an update is made on the table and then extract it based on that column.

Is there any other option (maybe in DB2 Unload utility) where we can achieve the above requirement?

Thanks and appreciate your response.

Subha

Pandora-Box · Posted: Thu Jun 07, 2012 12:18 pm

Options I see is compare the data which was unloaded yesterday needs to be compared with data extracted today so you could find the delta of changes like records which are inserted , updated and deleted

But a wiser option would be to have a date or timestamp field in the the table

If you dont want to disturb your table structure anticipating any huge impact

why not have a new table created just with keyfields and a timestamp field and populate keydata with current timestamp

So you can process that days's processed records by joining these two tables

Experts would have better word

sushanth bobby · Posted: Thu Jun 07, 2012 12:30 pm

Hi Subha,

Best option would be to add a column as ROW CHANGE TIMESTAMP

Thanks,
Sushanth

deybumba_2k · New User Joined: 09 Jan 2006 Posts: 17

Dear Sushanth,

I got that, but not sure whether that will work with the DB2 on AS400. Saw the feature on DB v29 on Z/OS

Thanks
Subha

sushanth bobby · Posted: Thu Jun 07, 2012 12:43 pm

Hi Subha,

Just went to google and typed "DB2 Row change timestamp", the second link i got is this link

I think System i is AS400. So, i think, its there... what version are you using ?

Thanks,
Sushanth

dick scherrer · Posted: Thu Jun 07, 2012 7:13 pm

Hello,

dbzTHEdinosauer · Posted: Thu Jun 07, 2012 7:39 pm

you have a potentially huge table that daily has updates to include deletes.

deletes by the way, for which you have no audit......

if you were to create

a new table with columns
- timestamp
- old-table-key
- action - Insert,Update,Delete
- image-type - Before After
- all the columns from old-table
a trigger on the basic table to populate the new-table

Then:
your unload Program problems would be solved,
(though the analysis pgm would require modifications in order to provide a decent audit trail)

No new code to lay in existing pgrms, just a trigger to INSERT to two (2) rows per modification of base table.

Pandora-Box · Posted: Fri Jun 08, 2012 11:25 am

GuyC · Posted: Fri Jun 08, 2012 11:54 am

Since DB2 v9 there is a solution for select * : IMPLICITLY HIDDEN columns
db2portal.blogspot.co.uk/2007/05/implicitly-hidden-columns-db2-9-for-zos.html

don.leahy · Posted: Fri Jun 08, 2012 5:56 pm

GuyC · Posted: Fri Jun 08, 2012 8:15 pm

I did not see any reference to any application program where SELECT * is appropriate.
But yes, people who need all columns need to be aware of this feature.