DSXchange

There exists a recommendation from the architecture team to use only Stored Procedures for all Database access in our datastage jobs ( Yes - no SQL queries in datastage)- Every data request from a database will need to be executed via a stored proc ( which apparently the DBA has better control on and can tune or maintain in case of performance issues) .

Is that approach going to compromise performnace ? I believe that Stored Proc Stages probably use ODBC connectivity rather than the NATIVE database API ( OCI/CLI/OpenConnect etc) - and even though the ODBC access uses wire protocol drivers , i still feel this 'recommendation' may throw unexpected surprises - including longer development/ testing times, increased complexity of job deployment and migration etc ,more DB objects to maintain etc.. apart from the potential for performance issues by skipping native connectors.. can anybody let me know for the PRO's and CONS of this approach ..

This "standard" is extremely dubious in my opinion, and showing ignorance of how DataStage can work best.

Do we have a justification from the architects for this decision? (I'm sure the DBA's won't agree with it.)

DataStage can perform "direct read" and "direct write", bypassing the SQL engine completely. This streams data from the table without limiting the ability to limit the rows and columns delivered.

If what's sought is the ability to audit all access to the data, this can be accomplished in other ways, such as using Guardium software - which is specifically purposed to this task.

To quote some old movie: They have chosen... poorly.

Seems like either the architects or DBA's don't trust their developers to write SQL correctly.

...and forget about Data Lineage with a 100% SP solution.... Data Lineage can be done with SP's but not without a whole lot more effort than just doing things in DataStage either via auto generation of SQL or even user-defined.

I talk to sites who are in the process of moving away from unmanageable sp's....

Ernie

Thanks all for your suggestions on this one.. Yes we do have an architecture team thats wholly unfamiliar with datastage - and they've had performance issues with SQl in the past - and these factors may have contributed to this 'standard'.. This being more of a data and process integration project that pure DW.. It looks like for the moment they will not object to writes using Insert/Update or direct load- which is a relief given their earlier stand .. but I'll probably factor in the other opinions posted here to justify the need for direct SQL reads using native connectors - but that I guess will be a stretch : ( ..

I wonder what makes them think inefficient SQL buried in stored procedures would be any better than inefficient SQL not buried in stored procedures?

Apparently a DBA well versed in SQL would have better control on Stored Procs with bad SQL that he can see in the DB, that try to change the bad SQL that resides in the datastage job - there may be a DBA who isn't familiar with datastage- so the saying goes from the powers that be ...

It's non-negotiable decisions like that which ultimately lead to higher costs of ownership.....there are pros and cons to everything. This same site will potentially be in a situation 3 years from now when they have serious DataStage experience but their long time DBAs have all left the company....and no one will know how to review the SQL.......

Ernie