Hi Experts,
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
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?
Regards
Buzz
P.S. In meantime I've opened PMR for this
Reading data by Oracle Connector and ORA-1000 error
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia