I have developed a ETL job in two parts..Extraction and Staging in one and the second part is loading. I am loading the tables(incremental loading) using regular update existing rows and insert new rows. My requirement here is, occasionally we would want to refresh the target tables i.e. truncate the table and insert all new rows. I thought one way of doing it is create another link in the staging job between Transformer and Oracle DB with update action as Truncatetable and insert rows and pass that update action as a parameter. But it doesnt seem to work. I would want to create a parameter for the update action so that one could always choose between doing the daily incremental loading or totally refreshing the target tables. Is there a way around to do it? Other option I have on mind is write a procedure as a before sql query to truncate the table if I pass a parameter.
Note: I would know before hand when I would want to do a full refresh i.e truncate and insert. So, for that particular I wish to pass a parameter so that the job could pick it up and do the needful.
If you are using a DRS stage, then in the before tab you can specify a parameter, and whenever you want you can pass a delete command in there or if your id has truncate authority, you can pass a truncate command in there. That might do the trick
cheers
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
I think kris had already mentioned in the first post that it is Oracle DB and OCI stage is implicitly understood.
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
i totally overlooked that. Thats what happens when you try to multitask.
So coming back to the problem,
in the OCI stage, go to the tab sql, and in there is a tab before. in the section specified there put ur delete command. Test it. If it works, parametrize the Delete command and put the parameter there.
When ever you want to refresh your table, pass the delete statement through your parameter.
I dont see why that wouldnt work.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Yeah....It works whenever I want to do a refresh. But If I am doing a regular Update else Insert, the Job finishes with warnings saying invalid sql statement..which further sends an emailnotification. To be more clear,
I tried giving the TRUNCATE statement as a parameter in before sqltab in OCI stage in my extraction and staging job(First part ) where I try to pull the last successful load timestamp(for incremental loading). So it solves the purpose when I want to do a refresh because the before query is truncating the target table and I dont get any timestamp from the target which pulls the whole data from the source. But when I want to do a normal UpdateelseInsert I dont give an value for the DELETE parameter and the job is finishing with warning which I want to avoid. Still trying to comeup with something which might solve this. Please let me know if I am not clear so that I could explain it better.
The job is going to execute Before Command on every single run. It's important to give a valid SQL to your parameter. One way is to construct your SQL parameter as
"Select 1 from Dual"
make this as your default SQL to be executed every time. Whenever you want to refresh pass the value to SQL paramter as
"Truncate Table Table_Name"
If you want to use delete command
"Delete from Table_Name where SQL_Parameter"
in SQL Parameter use default value as 1=2
and whenever you want to refesh then use 1=1
Truncate is faster and more efficient to use in Oracle to remove records permanently.
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
I was thinking on the same lines too but am hesitant to pass the TRUNCATE TABLE TABLE_NAME statement as parameter becos I would end up with more problems if I give a wrong table name accidentally. But I guess there no way round as of now. Can't complain and need to be careful while I pass parameters.
Thanks
Kris
Kris - just how 'occassional' is this occassional need to occassionally truncate the table in question? If it is truly occassional, why go through all this who-ha and why not just occassionaly truncate the table... manually?
-craig
"You can never have too many knives" -- Logan Nine Fingers
Why should one waste resources by creating a seperate DS job for a simple task like 'manually' truncating a table.
A simple 'TRUNCATE Table_Name' command execution would suffice.
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
I agree with you but if it is in a job then it is more controlled. Once the code is compiled, tested and promoted to production then you don't need to worry about it. In manual approach there is always a danger on truncating other tables in production and there could be other security issues.
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
