how to get the just get the incremental load from source
Moderators: chulett, rschirm, roy
how to get the just get the incremental load from source
hi,
i had a problem like my source is appending daily. Im running the DS job weekly so i want to get only the records that are loaded since my last job.
i want to use condition based on date but how to automate that. i mean i can get max(date) from target but how i should use in the source table sql
source is oracle and tgt is oracle.
thanks
sri
i had a problem like my source is appending daily. Im running the DS job weekly so i want to get only the records that are loaded since my last job.
i want to use condition based on date but how to automate that. i mean i can get max(date) from target but how i should use in the source table sql
source is oracle and tgt is oracle.
thanks
sri
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
You need to store your run start date. Your goal is to use this value, passed as a parameter to the job that selects data from your source system, select any rows with change dates >= your last run date.
Once all of your jobs run, the last thing you should do is update your stored last run date. Since you do this last, if you have a failure, you can rerun without doing anything special.
Your stored start date can be in a file or a table. In a file, a routine could easily read it and return its value. You could call this routine from a RoutineActivity stage, and pass the returned value to a job as the value of a parameter in a subsequent JobActivity stage.
The DataStage Basic manual will help. Read about the OPENSEQ and READSEQ and CLOSESEQ statements.
Once all of your jobs run, the last thing you should do is update your stored last run date. Since you do this last, if you have a failure, you can rerun without doing anything special.
Your stored start date can be in a file or a table. In a file, a routine could easily read it and return its value. You could call this routine from a RoutineActivity stage, and pass the returned value to a job as the value of a parameter in a subsequent JobActivity stage.
The DataStage Basic manual will help. Read about the OPENSEQ and READSEQ and CLOSESEQ statements.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Once you retrieve the correct processing date and pass it into your job as a job parameter you can use it in the Oracle stage where clause by enclosing the parameter name in hash symbols.
WHERE:
TRUNC(CREATION_DATE) = #PROCESS_DATE#
or
CREATION_DATE > #PROCESS_DATE#
You will find a condition tab within your Oracle stage when you use generated SQL. Please note that this will probably not identify deletes on tables that do not maintain a history or audit path.
You can also get a product from Ascential that does log scraping from the Oracle transaction logs which can make delta processing a lot easier.
WHERE:
TRUNC(CREATION_DATE) = #PROCESS_DATE#
or
CREATION_DATE > #PROCESS_DATE#
You will find a condition tab within your Oracle stage when you use generated SQL. Please note that this will probably not identify deletes on tables that do not maintain a history or audit path.
You can also get a product from Ascential that does log scraping from the Oracle transaction logs which can make delta processing a lot easier.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Vincent, is Ascential still selling this? I was under the impression that they may not be maintaining it and (thus) may no longer be offering it for sale, preferring people use Oracle's product instead.vmcburney wrote:You can also get a product from Ascential that does log scraping from the Oracle transaction logs which can make delta processing a lot easier.
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: how to get the just get the incremental load from source
but one thing i dont understand which routine i had to use to get maxvaluedate value from hash file. is it predefined or i had to write myself. since i have not much knowledgs of routines what should i do.
thanks
sri
[quote="parsi_cnu"]hi,
i had a problem like my source is appending daily. Im running the DS job weekly so i want to get only the records that are loaded since my last job.
i want to use condition based on date but how to automate that. i mean i can get max(date) from target but how i should use in the source table sql
source is oracle and tgt is oracle.
thanks
sri[/quote]
thanks
sri
[quote="parsi_cnu"]hi,
i had a problem like my source is appending daily. Im running the DS job weekly so i want to get only the records that are loaded since my last job.
i want to use condition based on date but how to automate that. i mean i can get max(date) from target but how i should use in the source table sql
source is oracle and tgt is oracle.
thanks
sri[/quote]
how to write a routine
hi akhiln,
how to write a routine. since till now i havent came a necessity to write a routine and any documentation available anywhere
thanks
sri
how to write a routine. since till now i havent came a necessity to write a routine and any documentation available anywhere
thanks
sri
Re: how to write a routine
Look up the Basic pdf and core dev guide pdf that cam,e with your installation. Do some RnD in your dev environment, I'm sure you'll get there.
- Sachin
-
- Sachin
-
parsi_cnu wrote:hi akhiln,
how to write a routine. since till now i havent came a necessity to write a routine and any documentation available anywhere
thanks
sri