Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Unload

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
deybumba_2k

New User


Joined: 09 Jan 2006
Posts: 17

PostPosted: Thu Jun 07, 2012 11:55 am    Post subject: DB2 Unload
Reply with quote

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
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 07, 2012 12:18 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jun 07, 2012 12:30 pm    Post subject:
Reply with quote

Hi Subha,

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

Thanks,
Sushanth
Back to top
View user's profile Send private message
deybumba_2k

New User


Joined: 09 Jan 2006
Posts: 17

PostPosted: Thu Jun 07, 2012 12:37 pm    Post subject: Reply to: DB2 Unload
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jun 07, 2012 12:43 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 07, 2012 7:13 pm    Post subject:
Reply with quote

Hello,

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

Suggest the timestamp be included in the original table.

Other than the table change and including the new column in code that needs it, there should be little/no change to the existing processes. Processes that do not need the new column would need no change. Processes that need the new column have to be changed anyway and i believe it is better to add only some code referring to some column than adding a join of 2 tables.

Some initial value could be placed in the existing rows. Depending on what is done for backup/restore, these might need to be changed.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 07, 2012 7:39 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jun 08, 2012 11:25 am    Post subject:
Reply with quote

Quote:
Suggest the timestamp be included in the original table.


Agreed Dick

Hope he ensures there is no select * anywhere
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 08, 2012 11:54 am    Post subject:
Reply with quote

Since DB2 v9 there is a solution for select * : IMPLICITLY HIDDEN columns
http://db2portal.blogspot.co.uk/2007/05/implicitly-hidden-columns-db2-9-for-zos.html
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Fri Jun 08, 2012 5:56 pm    Post subject:
Reply with quote

GuyC wrote:
Since DB2 v9 there is a solution for select * : IMPLICITLY HIDDEN columns
http://db2portal.blogspot.co.uk/2007/05/implicitly-hidden-columns-db2-9-for-zos.html
Does this solution make the problem better or worse? As Mr. Mullins points out, it effectively disables SELECT * even in those cases where it is appropriate.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 08, 2012 8:15 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts DXT unload utility - DVRE0000 mistah kurtz DB2 1 Thu Jul 07, 2016 4:56 pm
No new posts How to suppress retun code of partil ... mkk157 DB2 9 Fri May 27, 2016 6:59 am
No new posts FAST UNLOAD and LOAD issue GaganGarg DB2 1 Mon Feb 15, 2016 2:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us