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 ..
Dubious 'Standards'
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
...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
I talk to sites who are in the process of moving away from unmanageable sp's....
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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 : ( ..
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>