I was always curious why did DataStage engineers leave the Transaction / Record Count property when "Oracle Connector" stage is used as a source, that reads data. Basicaly it didn't make any sense for me to control the size of transaction for SELECT operations.
Anyway I wasn't too sad about that since it wasn't affecting my work. Till today
![Cool 8)](./images/smilies/icon_cool.gif)
I was playing with transaction Isolation Levels on my vanilla installation of Information Server 8.1 FP1 with all the newest & greatest patches applied when suddenly Oracle started to suffer after turning on "Read Only" isolation level. It started raising ORA-01000 error - too many opened cursors.
So I started tracing Oracle side - it turned out there are hundreds of opened SQL cursors for DataStage session - each issuing the same SQL command:
SET TRANSACTION READ ONLY
Moreover - the number of opened cursors is equal to number of data packages controlled by the Transaction / Record Count property. It looks like DataStage opens such cursor each time it processes XXXX number of records.
I'm surprised and confused, have anyone of you expected this?
![Smile :)](./images/smilies/icon_smile.gif)
Regards
Buzz
P.S. In meantime I've opened PMR for this
![Wink ;)](./images/smilies/icon_wink.gif)