IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query regarding Data Archival & Retrieval ?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England

PostPosted: Wed May 23, 2007 6:29 pm
Reply with quote

Hi,

I have a requirement of creating new DB2 Tables(Offcourse this will be created by the DBA). Now in these DB2 tables around 10 million records will come on a daily basis.
We have a requirement originating because of this mentioned below.
1) The number of tables to be archived is less(around 10) but the volume is VERY HIGH.
2) We want to keep this archived DATA for 2 YEARS(OFFLINE) and keep the last 6 months data (ONLINE on DASD's ).
3) Now business wants to retrieve this data ON DEMAND basis for security reasons.

Questions:
------------
1) Can i implement some Archival/Retrieval strategy MVS environment, if YES, Can you please let me know which TOOL/UTILITY can i use to ARCHIVE the Data?
2) What sort of Architecture can i propose which will help in retrieval of historical data rapidly(In terms of where the Data Archived should reside)?

Any sort of Inputs will be helpful. Please do share your experiences.

Thank
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed May 23, 2007 7:24 pm
Reply with quote

Hello,

Before you begin design, i'd suggest you get a definition of just what is "rapidly". That may mean quite different things to different people in your organization.

I'm unsure how the "on demand" requirement relates to security. Clarification would help.

How much of the archived data might be needed for an "on demand" process? Something to consider is that the smaller 6 months of data will be about 1,800,000,000 rows. The larger (2 years or 1.5 years) will be 3-4 times that many rows. I suspect that there is no really rapid (again, depending on the definition of rapid) way to reload over a billion rows - and that is the smaller case.

Your organization may have a different theory, but most of the places i support operate on the theory that their databases are just as (if not more) secure than flat files.

If your organization is willing to invest in a large volume of historical data, using one or more read-only databases may accomplish what you want. Keep in mind that the read-only databases could be defined with many alternate indexes to support a variety of "on demand" requests for data.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England

PostPosted: Thu May 24, 2007 12:42 pm
Reply with quote

Hello Dick,

Thank you for your response. I have tried to provide some explanations to your queries, please do read.
First of all 'rapidly' is not the correct word(thanks for pointing it out)... they are more intersted in architecture part and then looking for more cost effective solution to this problem.
We can push the request for Archived Data by business into Batch process also.

There are some transactions being carried out by the Back End staff, and due to recent SOX compliance directives, my organization want to track those(infact all) trasactions now. I hope this clarifies the word security reasons. Just to prevent frauds etc..

In the worst case, the request for online+offline data could even be for 2.5 years(maximum). But i guess even in this case the resultset will not be billion rows, infact i suppose it is going to be significantly less
(around 1000) as it is based on some conditional inputs from business.

Yes you are correct we don't need to update the archived DB, could you please clarify what is meant by READ ONLY Database?

Could you share with me what sort of Architecture can i propose for this situation? And you mentioned something about the Index to be built on the Archived Data, though i have some understanding about indexes, Could you please throw more light on how multiple indexes could be used in this case?

Thanks
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts RC query -Time column CA Products 3
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
Search our Forums:

Back to Top