DSXchange

Hi All,

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.

Need guidance.

Thanks
Kris.

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

I don't have DRS stage in my job or palette. I am staging date in a sequential file in my first job and calling it in the second job.

Thanks
Kris.

For loading the table, what stage are u using, and what DB is it?

I am loading into Oracle DB and using Oracle OCI stage

Kris

Hi,

I think kris had already mentioned in the first post that it is Oracle DB and OCI stage is implicitly understood.

Thanks,
Naveen.

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.

Kris,
I just made a test job and tested the solution i proposed. It works just fine.
cheers

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.

Thanks
Kris

Kris

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.

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?

Or even create a separate job in datastage to truncate table and run it manually whenever you want to truncate table....

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.

Naveen

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.