I've tried calculating the Line sequential buffer length, changing the file encoding to ANSI, WW1252, bot UTF, etc., and even playing with Pushdown optimization, but mapping still not being able to load the file unless I split it out on 450 rows for file.
Can you give us a little bit more information?
The error is a sql error but it's strange there is a limit of 450 rows (is it exactly 450?)
Are you using PowerExchange for Redshift or are you using the Redshift odbc driver?
Did you already reach out to database administrators? It looks like PowerCenter itself doesn't have a problem but the connectivity to the dbms is the problem.
The PostgreSQL error is in this class
Class 42 — Syntax Error or Access Rule Violation
Many thanks for your answer.
We're using Redshift ODBC Driver and I don't know the rows limit exactly. It seems that the problem appears when the file exceeds 400 rows.
We have got the same problem with another similar session, but it was fixed by putting Pushdown Optimization to "Full Mode" and activating "Allow temporary view for pushdown".
However, applying the same configuration to the new one, it doesn't work. We haven't talk to DBAs yet.
Do you know if any particular parameter or configuration ODBC needs to be set?
Seems to be a limitation in DB side.
Please, could you check this?
For Redshift odbc connections in the repository connection the ODBC subtype must be configured to AWS Redshift.
But on the odbc connection itself you can also configure some options.
Under Additional options you can choose out of 4 options: Single row mode
Use Multiple Statements
Retrieve Entire Result Into Memory
Please check which option is active and which option would best suit your requirements.
AWS offers a lot of documention on the subject.
Hope this helps