Delta load methodology

[shrey3a]

Hi,

I've source as an SQL server and target as an Oracle.

At end of my job I'm writing the lastJobruntime to a table in oracle. For the delta load I need to extract the data >= lastjobruntime.
I was thinking to extarct the lastjobruntime in hash file and then in constraint have a condition sqlserverTable.updated_on >= hashfile.lastjobruntime.

But the problem is the source has 2 million records and it will extract all the records first and then apply the condition in transformer. Is there is a way where i can filter the records before it comes to transformer stage .

Limitation : I can not create a temp table on SQL server as then I would have queried directly in SQL server enviorment against the lastjobruntime table in SQL.

Thanks in Advance.

Regards

[vmcburney]

Your delta date is stored in the Oracle database, it is easy to retrieve this from a sequence job using a routine. From the routine run the DSExecute Unix command SQLPLUS and pass it the select statement as an argument. This will return the result of the SQL in the command output. Parse this output for the date, convert it to the right format and return it to the calling job.

Do a search for SQLPLUS, I am sure there are threads that demonstrate this.

Now pass the value to the SQL Server extract job as a job parameter. Add it to your SQL Server input stage WHERE clause, eg.
CREATION_DATE > #LASTJOBRUNTIME# OR UPDATE_DATE > #LASTJOBRUNTIME#

Also be aware that you will be missing out on the deletes that are occuring in your SQL Server database. You need some method for capturing deletes (if they are occuring) such as a trigger that populates a delete table.

[Sainath.Srinivasan]

And also include an upper cap for the extract period so to know where to start next time.

[shrey3a]

Hi Vincet,

I've made a job which extarcts the date in correct format in hash file.
Can you please guide how to pass this value job parameter in my main job whare I'm extracting the data from SQl server.

as u said
Now pass the value to the SQL Server extract job as a job parameter. Add it to your SQL Server input stage WHERE clause, eg.
CREATION_DATE > #LASTJOBRUNTIME# OR UPDATE_DATE > #LASTJOBRUNTIME#

Regards,
Munish

Your delta date is stored in the Oracle database, it is easy to retrieve this from a sequence job using a routine. From the routine run the DSExecute Unix command SQLPLUS and pass it the select statement as an argument. This will return the result of the SQL in the command output. Parse this output for the date, convert it to the right format and return it to the calling job.

Do a search for SQLPLUS, I am sure there are threads that demonstrate this.

Now pass the value to the SQL Server extract job as a job parameter. Add it to your SQL Server input stage WHERE clause, eg.
CREATION_DATE > #LASTJOBRUNTIME# OR UPDATE_DATE > #LASTJOBRUNTIME#

Also be aware that you will be missing out on the deletes that are occuring in your SQL Server database. You need some method for capturing deletes (if they are occuring) such as a trigger that populates a delete table.

[SriKara]

A quick solution i can think of is, use the UTILITYRUNJOB in a transformer stage. Immediately after the Oracle plugin, use the transformer stage, to pick the date value and pass it to the utitlityrunjob.

But running a job from transformer stage is not a good practice from coding standards point of view.

Any better suggestions??

[shrey3a]

Hi ,

I've a job which writes to Oracle table i.e. the last job run time is written to oracle. In my next job where I extract the data from SQL server so I need to Pass the parameter of my last job run time in that. Can you explain the Utilitylastrun in detail....or there can be other design

Thanks in advance.....

[quote="SriKara"]A quick solution i can think of is, use the UTILITYRUNJOB in a transformer stage. Immediately after the Oracle plugin, use the transformer stage, to pick the date value and pass it to the utitlityrunjob.

But running a job from transformer stage is not a good practice from coding standards point of view.

Any better suggestions??[/quote]