i created one shared container which contains one dynamic sql st , that calls the pl/sql procedure. and i called that shared container using a test job (i.e) parallel job.
while running the job in datastage it's taking twice the time as that of compared with pl/sql procedure on unix.
anybody having any idea about incresing the speed of datastage job
shared container - increasing the speed
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What stage type inside the shared container contains the "dynamic SQL" statement?
What you seem to be trying to accomplish here is to have the following architecture run at no cost; that is, to run at the same speed as if it weren't there. The layers are:
You want to compare the above with:
The obvious question is whether it's really necessary to invoke a stored procedure for every row. Could you do the same thing with a buildop, which would be much faster? Or re-think your design, so that the data involved resides in datasets while your job is running - therefore all data modifications are effected in memory.
What you seem to be trying to accomplish here is to have the following architecture run at no cost; that is, to run at the same speed as if it weren't there. The layers are:
- UNIX
osh (several processes)
DataStage job process(es)
Shared container process(es)
Stage type (ODBC?) executing "dynamic SQL"
ODBC driver?
Oracle client software
You want to compare the above with:
- UNIX
Oracle client software (includes sqlplus)
The obvious question is whether it's really necessary to invoke a stored procedure for every row. Could you do the same thing with a buildop, which would be much faster? Or re-think your design, so that the data involved resides in datasets while your job is running - therefore all data modifications are effected in memory.
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.
stage type
inside the transfor mer stage , i am giving the dynamic sql statement
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
So, in DataStage you're generating the "dynamic SQL" for every row processed, while in PL/SQL (presumably) you're not. Is this the case? And you expect it to run at the same speed! Read my earlier post again.
I don't think you have much scope for improving performance without moving away from a "dynamic SQL" approach; this is not really how DataStage is designed to execute.
I don't think you have much scope for improving performance without moving away from a "dynamic SQL" approach; this is not really how DataStage is designed to execute.
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.