So, I only wanted to delete a set of data from a huge table and not wanted to truncate the whole table, as it would take long time to reload all the data.So I decided to partition the table by list on a column values. I created informatica mappings for each partition separately and put all the sessions in one workflow. Now here is the trick I applied. For each session's properties in under mapping tab, click on the target and scroll down, you will see a PRE SQL section where you write a truncate statement.
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
Click OK and save the workflow. I did this for all the sessions, basically for each partition I had created.
This allows me to run the sessions manually if I want and run one at a time or all of them. Pre and Post sql options are awesome and gives you lots of flexibility to work with.
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
Click OK and save the workflow. I did this for all the sessions, basically for each partition I had created.
This allows me to run the sessions manually if I want and run one at a time or all of them. Pre and Post sql options are awesome and gives you lots of flexibility to work with.
Also applied the same method, however error raised if target table got table index. I have fixed it by call routine dropping index in PRE SQL and re-create index in POST SQL. Have you ever encountered the same problem and how your fix it ?
ReplyDelete