Reading data by Oracle Connector and ORA-1000 error

[buzzylee]

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

[keshav0307]

what is the value of open_cursors parameters in Oracle.. can you increase that

[buzzylee]

It's 1500 and yes I can increase it and wait till it crushes next time, when bigger chunk of data will be loaded...

Let's wait for PMR to have it really fixed. I was just curious if any of you expected Transaction / Record Count parameter to be effective for SELECT statements