Bussiness requirement

harish841913 · Post by **harish841913** » Wed Jun 01, 2011 8:40 am

i want to know what actually data archiving is as i have to archive data such as claims,capitation,positive pay and duncan repository for 2 years and 5 years so wat are the steps how i should proceed further in order to prepare HLD and LLD

DSguru2B · Post by **DSguru2B** » Wed Jun 01, 2011 10:01 am

Involve your DBA. This usually comes under DBA tasks. What we do here is have an archive flag column. The dba runs a scripts and sets the archive flag which he then puts in history tables in a seperate schema.
There are other ways of doing it as well.

harish841913 · Post by **harish841913** » Wed Jun 01, 2011 10:09 am

but here i have to do everything from gathering requirement to design and coding

so how this is done actually i have to write some scripts for archiving so that i can call jobs from sequencer using execute command activity

harish841913 · Post by **harish841913** » Wed Jun 01, 2011 10:42 am

@DSGURU i am not a premium member so i can't read it can you explain it again

DSguru2B · Post by **DSguru2B** » Wed Jun 01, 2011 12:02 pm

I gave specifics of data archiving. If you want to understand the concept, google it.

jcthornton · Post by **jcthornton** » Wed Jun 01, 2011 10:32 pm

harish841913,

Archiving can be a medium to very large scale project. In this case, it sounds like you are being asked to be the technical solution architect (or similar) role. Yet, you are asking for a free site, full of volunteers, to give you a plan for doing this? I hope, for your sake, that this is a small archiving project.

If this is not way beyond your experience, you should at least be able to provide a framework and ask some specific questions to fill in gaps.

Use google to not only gain an understanding of archiving, but also data retention and solution design/architecture. Toss in some project planning and a few days of intensive reading. Then grab a colleague who has handled a full project lifecycle and ask them to mentor you.

Feel free to come back here with specific questions and you will find a group very willing to help out. Unfortunately, it is not possible to walk you through every step of the process in a forum like this.

I am not saying this to be mean or cruel. This is to help guide you to the best ways to use this forum so that you can get the best results for your time spent here.

I'll reiterate the steps you should take, just to be clear.
1. Use google and other references to gain the basic understanding of what you are being asked to do.

2. Find a mentor who can work with you to answer the location and environment specific questions you will have on what you are producing.

3. Once your framework is setup and you know where your gaps are, come here with your DataStage specific questions,

Use 'Search' first, and try different combinations of terms to try to find an answer. If that does not work,

Provide specifics and details about what you need help. Avoid ambiguous or generic questions. Isolate the problem and question, but provide the entire problem. We will ask for more details if you are not specific enough, but it is very refreshing to not have to ask.

vmcburney · Post by **vmcburney** » Wed Jun 01, 2011 10:39 pm

The preferred IBM solution for data archiving is Optim Data Growth. It is better than DataStage in some ways for this task as it provides the following:
- An agent for reading data from a database and executing archive delete statements back against that Database.
- A repository for storing arhived data and providing ODBC query access to that archived data.
- An interface for setting up archiving rules and monitoring the archive.

The problem with using DataStage for archiving is that you are executing delete statements in a production system across a complex database schema and you have to delete the right data in the right order. You have to remove complete business objects. DataStage would need a lot of governance and automation to do this without a user error botching your production database.

Most companies would feel safer with Optim rather than DataStage for archiving.

harish841913 · Post by **harish841913** » Wed Jun 01, 2011 11:15 pm

How to achive archival process in datastage as i have to archive data from ODS and load it into datawarehouse for 2 years and 5 years respectively.

what are the stages i can use here whether i have to maintain history if yes than i have to go with cdc right.

Please guide me on this

jcthornton · Post by **jcthornton** » Thu Jun 02, 2011 11:40 am

It depends on what the criteria are for your history. Type 1, type 2, etc. This will also be influenced by the type of reporting that will be needed from the archived data - and if there is going to be a need to be able to restore the data from your archive.

CDC is commonly used.

But if the needs do not require it, you may just do an insert or an upsert.

It comes down to those requirements you talked about needing to gather. Designing and building your warehouse before you have those makes it easy to either over-design it or miss something important.