Use pre session command for that particular session at workflow level, rather than resuable level. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy.
Already have an account? Sign in. By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account. We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic. Data Management.
I can see this occurring when you need to repeatedly import test data or you have routines that use work tables or scratch tables to store information. This is important when you need to guarantee each row uses a key that has never been used before. Perhaps, this need to happen for audit reasons. Depending on the locking you are using, row locks are placed on deleted rows. Unaffected rows remain unlocked. Share this:.
Like this: Like Loading What are the tasks performed by DTM while running the workflow? Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Informatica try to do truncate first in case the user do not have the previlege , it will do the same by issuing a delete DML. In your presql command, try to truncate detail table first and then truncate master table. It will solve your problem. Could you please let me know, whether the two tables that are having PK-FK relationship are present in the same mapping or not?
If not, then you should understand informatica will not bother about your database dependencies while using Truncate Table option in session. If you use, presql command then you are executing the command on database side so it will consider the PK-FK dependencies that's the reason it is failing. If you are saying ' informatica will not bother about your database dependencies while using Truncate Table option in session' then it means informatica truncate table option property disable the constraints automatically or ignore it?
Both tables placed in single mapping and use truncate option but etl not fail. I go through the informatica help guide and found one interesting topic,Below content is directly from Informatica help guide:. The Integration Service can truncate target tables before running a session.
You can choose to truncate tables on a target-by-target basis. If you have more than one target instance, select the truncate target table option for one target instance. The Integration Service issues a delete or truncate command based on the target database and primary key-foreign key relationships in the session target.
To optimize performance, use the truncate table command. Which database you are using? Could you tell the difference in run time of reports. These are all run daily morning for each and every workflow. Could you tell the difference in run time of reports? Since delete statement is based on mostly on filter conditions, we can try creating index or partitions based on those columns.
This may improve the delete performance. Have to test and see. Create a cursor on the records to be deleted. I would suggest creating a mapping that does the deletes first…actually it would be better to truncate and then reload if possible but if not just delete. Make sure there is an index on the PK and what ever the filter is?
Next you can run your normal mapping to insert the data. Again, if you are inserting lots of data.. You should then trigger your reports to run only after you are done loading your tables. That way you are ensured that there will be no contention on the tables. I cannot use trunc and load, because I need to only delete a specific amount of data before each load.
But will try other options above. In case only delete is to be used try using disabling constraints and then delete and enable them again …. Before deleting please drop the index once records deleted rebuild the index.
0コメント