OWB 11gR2 – Heap, JDBC and Mappings
By David Allan-Oracle on Jan 25, 2011
When using the generic JDBC connectivity with code template mappings you need to be wary of how the driver you are using works. So, if you are using code template mappings and the load templates like SQL to SQL or SQL to Oracle its worth digging into the details of the driver and how you connect and use it. Generally users put in the most basic authentication information into the URL to get up and running.
So if you get this kind of an error its worth reading on;
Job 22 error: com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large resultsets:Java heap space. Result set size:.. JVM total memory size ..
You have to be wary of how the JDBC driver you are using operates. To illustrate let’s take Microsoft SQLServer as an example. There is a property that can be defined when connecting to SQLServer named selectMethod that by default has value direct which means 'all result set rows are retained in client memory'. Yep, you read it right….all result set rows in client memory, the client here being the OWB control center agent. With SQLServer, using the value cursor (rather than direct) will mean only a limited number of rows will be retained. So…….if you are dealing with large volumes of rows that will not fit in memory its best to think about whether you will need to set this property definition to cursor otherwise you’ll be hitting out of memory errors.
So an example if you define the selectMethod=cursor as follows
You will use the cursor approach rather than the direct ‘all result set rows in memory’ approach. The SQLServer driver has other properties such as adaptive response buffering for processing large result sets, and each vendor driver has different configuration properties so ensure you make maximum use of the connectivity by understanding how best to use it.
By making these changes in the connection URL the JDBC driver will behave quite differently from the default and be able to process larger volumes of data. I found these configuration properties from the SQLServer documentation for JDBC connectivity which is here. So, when you hit such errors don’t go straight to increasing the heap - increasing the CCA heap may get you further but changing the URL to configure these kinds of properties would make it operate on much larger volumes.