Informatica Powerexchange post sql greenplum writer

Question

I am facing issue in informatica target greenplum writer post sql..I have query that is running fine on database but when I am placing the query in post sql its not working ,there are lot of case statements present in query. When I tried removing couple of case statements then its working fine using post sql otherwise throwing fatal error.Can someone in the past faced similar scenario and can provide solution how to deal with it.Is there any limitations with informatica greenplum writer?

Answer 1

Some things to consider -

1. Is the user that youre using to run SQL in database is same as informatica user?
2. Is the SQL youre running in DB is same as infa post process ?
3. When you run the SQL in the DB, does the target table has all the indexes etc. ?
4. Does informatica loads large amount of data? Doesn infa drops indexes before loading ?

Now, please note post load SQL is just a simple SQL runs all at once. So, post SQL can be slow

1. if you do not have an index on target,
2. if you have large amount of data,
3. if table size grows a lot because of infa load,
4. if the table has stale statistics,
5. if table involved in SQL case statements is large

So, i recommend, please move the post SQL to informatica mapping because informatica inserts or updates in bunch and not apply the changes all together like a post SQL. Your performance will be much better.