We have been working over the past several months with a client who has DataStage (IBM Information Manager). At one point we reached a fork in the road and had a choice of which interface to use into the database – ODBC vs API.
Not sure exactly why we chose ODBC stage to connect to Teradata vs the API but we did. We felt good about doing so.
So why am I wondering if this was still a good route. It seemed to make so much sense, but we are having so much trouble keeping the DataStage .ini configuration stable to not have random Abend issues. ODBC should be a perfect fit except when we use fast export or another more specific utility from Teradata.
OK so it should be fine. The Oracle interfaces don’t seem to have this issue.
It was and probably is fine.
But if it can not remain stable it is not fine.
Outside of theoretical rhetoric, has anyone else had that issue?
There is a lot to be said about portability.
In lessons learned on the Application side of the house there is much we can learn from the evolution to and of Service Oriented Architecture (SOA).
The lessons of loosely coupled systems is very sound and practicle and has application here. There are times though where perfomance can not allow loosely coupled and we need to tighten.
This is when governance is necessary to weigh and guide in these areas as customization can impede reuse and Enterprise evolution (upgrades).
Plain and simple: The benefit in choosing a light weight protocol/driver such as ODBC for low data volumes is PORTABILITY. A rule of thumb: if you can avoid locking your system on to a specific vendor then avoid it. Decoupling the system through utilization of industry accepted db access standards such as ODBC is a well verified practice.
I usually keep ODBC as a last resort since it is slower than native database connectivity. In parallel jobs and server jobs you get the Teradata Multiload stage with user friendly access to Multiload, TPump and Fast Export. You then need to choose carefully between Multiload and TPump depending on the volume in each job.
I can’t think of any advantage in the ODBC stage.