I've been recently playing a lot with Vertica 6.0 connectivity under DataStage. Setting up the connection has its "gotchas" and isn't described well but what bothers me more is the ODBC Connector performance when it's used in "write" mode.
It basically takes at minimum 10-15 seconds to initialize every session, moreover - the more sessions you open (degree of parallelism) the slower it is. In absurd situations we are waiting hours (sic!) to get the job to the point of actual data load. Sounds insane, doesn't it?
![Smile :)](./images/smilies/icon_smile.gif)
We investigated the main root cause of that - looks like DataStage is indirectly responsible for it.
Let me explain - as you probably know DataStage implicitly issues catalog queries whenever Connector type of stages are used. This is to validate the target tables structure, generate DML command, etc. The problem is this queries kill Vertica. I know - most of you will say: "report it to Vertica support". And we did it - but the fact is, it's not guaranteed there will be simple patch for it.
What I was interested to know is - does any of you ever faced this issue and if so - what was the remediation (besides HP support call)?
And secondly - is there any way to turn off ODBC Connector's catalog metadata calls? For the record - turning off schema reconciliation doesn't help at all - db catalog queries are still being issued.
And last but not least - using ODBC Enterprise stage isn't an option. Even though it doesn't use catalog queries it doesn't satisfy other project requirements (no support of data rejection)
Regards
Buzz