BIG problem with database sessions

[Former Member]

Dear all,

we have big problem with part of application.

Scenario should look like this: we get excel files from partners, load it, parse it and update tables with the content. For xls import apache/poi api is used, for database manipulation SQLJ (static statements) and JDBC (dynamic statements) . Tables are created as standard dictionary project. Also, everything is done through NWDI.

Parser is going through every single row of the Excel table and doing lookup in the table where it compares material ID from excel with material ID from DB table. Excel tables can be extremly big, e.g. 15.000 rows, so that means 15.000 lookups per excel table.

Problem is that it seems that system isn't closing connections / sessions to database so J2EE system freeze after 30-40 rows. Number of connections in J2EE engine has been set to 50.

Here is code snippet, which is called for performing lookup function:

public Vector getList(String where)
	{
		MaterialIt itr = null;
		Vector vec = new Vector();
		
		String sql = "SELECT GEC_MATERIAL.ID, GEC_MATERIAL.MATERIAL_GROUP_ID, GEC_MATERIAL.NAME FROM GEC_MATERIAL ";

		if(where != null && !"".equals(where))
			sql += "WHERE "+where;
		
		try
		{
			
			java.sql.Connection conn;
			ConnEcat context;
			context = new ConnEcat();
			context.getConnection().setAutoCommit(false);
			
			conn = context.getConnection();
			java.sql.Statement stmt = conn.createStatement();
			
			try {
			
			java.sql.ResultSet rs = stmt.executeQuery(sql);	
			#sql itr = { CAST :rs };
			
			try{
			while(itr.next())
			{
				Material matOut = new Material();
			
				matOut.ACTIVE = itr.ACTIVE();
				matOut.CODE = itr.CODE();
				matOut.ID = itr.ID();
				matOut.MATERIAL_GROUP_ID = itr.MATERIAL_GROUP_ID();
				matOut.NAME = itr.NAME();
				
				vec.add(matOut);
			}
			}
			finally {
				itr.close();
			}			
		}
		catch (Exception e) {
			System.err.println(e);
		}
		finally {
			context.close();
		
		}
		}
finally{
	return vec;
}

Btw., it's been tested on MaxDB and Oracle, with the same result.

Problem is kind of critical and quite urgent so I'll appreciate fast response and promise to assign points (and buy a beer in Zagreb for help.

Best regards,

Ivan

[Former Member · Accepted solution]

Ivan,

Are you using Native SQL or Vendor SQL when you created the database connection? what's this class, <b>ConnEcat </b> ?

Native SQL, SAP says, has better performance features like SQL statement pooling etc.. since u r using same sql.. that might help u.

check that out.

Kiran

[Former Member · Accepted solution]

ConnEcat is connection class (ConnEcat.sqlj):

package com.gbs.gmob.sqlj;

#sql public 
	context ConnEcat 
	with 	(dataSource = "jdbc/SAP/BC_JMS");

Why do you think that Native SQL should be used? I would like to keep project independent of the DB server and this doesn't look like a way to keep it that way.

BR, Ivan

[Former Member · Accepted solution]

Hello Ivan,

Just to add a remark

"jdbc/SAP/BC_JMS" is an internal datasource that comes with the server (jms_provider uses it) and should be for internal usage of the server only. There is no guarantee that it will be present in the future versions and that the server will not internally change something during its runtime.

Additionally the server will take connections from that datasource that will somewhat interefere with your careful tuning.

Please consider creating your own datasource. That may help solcing your problems.

You may do so by VA and the tab JDBC connector. There is also a monitor that shows at a graph number of opened and used connections. That may be useful to you.

Best Regards

Peter

[Former Member · Accepted solution]

Hello Peter,

/SAP/BC_JMS is an alias to datasource which comes with the server, and all applications use that datasource with different aliases. I created new alias and now using this one, without change. I'm using monitor in VA and when I start this code, connections immediately goes above maximum defined (I defined 50 concurrent connections as max, which is 5 times more than default).

This is new code, simplified, using only SQLJ:

public Material getItem(String SUPPLIER_ID2, String SUPPLIER_MCODE2) throws Exception
	{
		Material matOut = new Material();
		
		MaterialIt itr = null;
		ConnEcat context;
		context = new ConnEcat();
		try
		{
			
	
			#sql [context] itr = {
			SELECT GEC_MATERIAL.ID, GEC_MATERIAL.MATERIAL_GROUP_ID, GEC_MATERIAL.NAME AS MATERIAL_GROUP_NAME, 
			GEC_MATERIAL.CODE, GEC_MATERIAL.NAME, GEC_MATERIAL.SUPPLIER_ID, GEC_SUPPLIER.NAME AS SUPPLIER_NAME, 
			GEC_MATERIAL.SUPPLIER_MCODE, GEC_MATERIAL.SUPPLIER_MGROUP_ID, GEC_SUPP_MAT_GROUP.NAME AS SUPPLIER_MGROUP_NAME,  
			GEC_MATERIAL.PRODUCER_ID, GEC_PRODUCER.NAME AS PRODUCER_NAME, GEC_MATERIAL.UNIT, GEC_MATERIAL.DESCRIPTION, 
			GEC_MATERIAL.UNIT_COST, GEC_MATERIAL.CURRENCY, GEC_MATERIAL.EMAIL_SN, GEC_MATERIAL.ACTIVE, 
			GEC_MATERIAL.MAT_CATALOG_ID, GEC_MATERIAL.UNIT_COST_LPRICE, GEC_MATERIAL.LINK, GEC_MATERIAL.NSTD_DEL_DATE 
			FROM GEC_MATERIAL 
			LEFT JOIN GEC_PRODUCER ON GEC_MATERIAL.PRODUCER_ID = GEC_PRODUCER.ID 
			LEFT JOIN GEC_MATERIAL_GROUP ON GEC_MATERIAL.MATERIAL_GROUP_ID = GEC_MATERIAL_GROUP.ID 
			LEFT JOIN GEC_SUPPLIER ON GEC_MATERIAL.SUPPLIER_ID = GEC_SUPPLIER.ID 
			LEFT JOIN GEC_SUPP_MAT_GROUP ON GEC_MATERIAL.SUPPLIER_MGROUP_ID = GEC_SUPP_MAT_GROUP.ID
			
			where GEC_MATERIAL.SUPPLIER_ID = :SUPPLIER_ID2
			AND GEC_MATERIAL.SUPPLIER_MCODE = :SUPPLIER_MCODE2
			};
			
			
			
			if(itr.next())
			{
				matOut.ACTIVE = itr.ACTIVE();
				matOut.CODE = itr.CODE();
				matOut.CURRENCY = itr.CURRENCY();
				matOut.DESCRIPTION = itr.DESCRIPTION();
				matOut.EMAIL_SN = itr.EMAIL_SN();
				matOut.ID = itr.ID();
				matOut.MATERIAL_GROUP_ID = itr.MATERIAL_GROUP_ID();
				matOut.MATERIAL_GROUP_NAME = itr.MATERIAL_GROUP_NAME();
				matOut.NAME = itr.NAME();
				matOut.PRODUCER_ID = itr.PRODUCER_ID();
				matOut.PRODUCER_NAME = itr.PRODUCER_NAME();
				matOut.SUPPLIER_ID = itr.SUPPLIER_ID();
				matOut.SUPPLIER_MCODE = itr.SUPPLIER_MCODE();
								matOut.SUPPLIER_MGROUP_NAME = itr.SUPPLIER_MGROUP_NAME();
				matOut.SUPPLIER_NAME = itr.SUPPLIER_NAME();
				matOut.UNIT = itr.UNIT();
				matOut.UNIT_COST = itr.UNIT_COST();
				matOut.NSTD_DEL_DATE = itr.NSTD_DEL_DATE();
			}
		}
		catch(Exception e)
		{
			
		}
		finally
		{
			try{
				itr.close();
			}
			catch(Exception e2)
			{
				
			}
			
			context.close();
		}
		
		return matOut;
	}

BR,

Ivan

[Former Member]

Hi Ivan,

There are two ways of closing the context.

1. KEEP_CONNECTION - To keep the underlying physical connection open.

2. CLOSE_CONNECTION - To close the underlying physical connection.

Try to close the context as

context.close(ConnectionContext.CLOSE_CONNECTION);

Note: If you process a result set iterator through its underlying result set, you should close the result set iterator, not the result set, when you are finished. Closing the result set iterator will also close the result set, but closing the result set will not close the result set iterator.

Hope this helps.

Regards,

Uma

[Former Member]

Hi Uma,

thanks for your tip - we tried with CLOSE_CONNECTION with no help

I

[Former Member]

Hi,

looking at this part of code:

finally {
				stmt.close();
				itr.close();
				conn.close();
			}
		}
		catch (Exception e) {
			System.err.println(e);
		}
		finally {
			context.close();
		}
		}

I have the following remarks:

put stmt.close();

itr.close();

conn.close();

<b>each</b> in a try/catch statement.

Because, if the statement is already closed and throws an Exception, the connection will never be closed.

Regards, Astrid

[Former Member]

Hi Ivan,

I found it strange that the connection is not closed, but perhaps you may switch the code to get the connection not from the SQLJ context, but to work directly with the connection from the datasource. Just an ugly workaround but you seem to be in urgent need of something

I.e. I am thinking of something like this :

#sql static context Ctx;

and then :

conn = getConnection();

try {

Ctx ctx = new Ctx(conn);

try {

//do the data extraction here

} finally {

ctx.close()

}

} finally {

conn.close()

}

private Connection getConnection() {

//instantiate the datasource here or use caching

return datasource.getConnection();

}

[Former Member]

Hi Ivan,

Just a try.

Close the ResultSet also and close in the same sequence in which you have instantiated.

Hope this will work.

Regards

Vishal Kumar

[Former Member]

Hi Vishal,

nope, it didn't help. It will drive me crazy...

Tnx for help,

Ivan

[Former Member]

Hi Ivan,

You need to close the connection to database also.

finally {	<b>conn.close();</b>			
                itr.close();			}

Add this statement. This will close the connection to the database. This will also close the session.

Hope this helps.

regards,

P.Venkat

[Former Member]

Unfortunately, it didn't solve the problem. It seems that context.close and conn.close don't close connection.

Here is the complete source:

public Vector getList(String where)
	{
		MaterialIt itr = null;
		Vector vec = new Vector();
		
		String sql = "SELECT GEC_MATERIAL.ID, GEC_MATERIAL.MATERIAL_GROUP_ID, GEC_MATERIAL_GROUP.NAME AS MATERIAL_GROUP_NAME, ";
		sql += "GEC_MATERIAL.CODE, GEC_MATERIAL.NAME, GEC_MATERIAL.SUPPLIER_ID, GEC_SUPPLIER.NAME AS SUPPLIER_NAME, ";
		sql += "GEC_MATERIAL.SUPPLIER_MCODE, GEC_MATERIAL.SUPPLIER_MGROUP_ID, GEC_SUPP_MAT_GROUP.NAME AS SUPPLIER_MGROUP_NAME, "; 
		sql += "GEC_MATERIAL.PRODUCER_ID, GEC_PRODUCER.NAME AS PRODUCER_NAME, GEC_MATERIAL.UNIT, GEC_MATERIAL.DESCRIPTION, ";
		sql += "GEC_MATERIAL.UNIT_COST, GEC_MATERIAL.CURRENCY, GEC_MATERIAL.EMAIL_SN, GEC_MATERIAL.ACTIVE, ";
		sql += "GEC_MATERIAL.MAT_CATALOG_ID, GEC_MATERIAL.UNIT_COST_LPRICE, GEC_MATERIAL.LINK, GEC_MATERIAL.NSTD_DEL_DATE ";
		sql += "FROM GEC_MATERIAL ";
		sql += "LEFT JOIN GEC_PRODUCER ON GEC_MATERIAL.PRODUCER_ID = GEC_PRODUCER.ID ";
		sql += "LEFT JOIN GEC_MATERIAL_GROUP ON GEC_MATERIAL.MATERIAL_GROUP_ID = GEC_MATERIAL_GROUP.ID ";
		sql += "LEFT JOIN GEC_SUPPLIER ON GEC_MATERIAL.SUPPLIER_ID = GEC_SUPPLIER.ID ";
		sql += "LEFT JOIN GEC_SUPP_MAT_GROUP ON GEC_MATERIAL.SUPPLIER_MGROUP_ID = GEC_SUPP_MAT_GROUP.ID ";
		
		if(where != null && !"".equals(where))
			sql += "WHERE "+where;
		
		try
		{
			java.sql.Connection conn;
			ConnEcat context;
			context = new ConnEcat();
			conn = context.getConnection();
			conn.setAutoCommit(false);

			java.sql.Statement stmt = conn.createStatement();
			
			try {
			
			java.sql.ResultSet rs = stmt.executeQuery(sql);
			#sql itr = { CAST :rs };

			try{
			while(itr.next())
			{
				Material matOut = new Material();
			
				matOut.ACTIVE = itr.ACTIVE();
				
				matOut.CODE = itr.CODE();
				matOut.CURRENCY = itr.CURRENCY();
				matOut.DESCRIPTION = itr.DESCRIPTION();
				matOut.EMAIL_SN = itr.EMAIL_SN();
				matOut.ID = itr.ID();
				matOut.MATERIAL_GROUP_ID = itr.MATERIAL_GROUP_ID();
				matOut.MATERIAL_GROUP_NAME = itr.MATERIAL_GROUP_NAME();
				matOut.NAME = itr.NAME();
				matOut.PRODUCER_ID = itr.PRODUCER_ID();
				matOut.PRODUCER_NAME = itr.PRODUCER_NAME();
				matOut.SUPPLIER_ID = itr.SUPPLIER_ID();
				matOut.SUPPLIER_MCODE = itr.SUPPLIER_MCODE();
				matOut.SUPPLIER_MGROUP_NAME = itr.SUPPLIER_MGROUP_NAME();
				matOut.SUPPLIER_NAME = itr.SUPPLIER_NAME();
				matOut.UNIT = itr.UNIT();
				matOut.UNIT_COST = itr.UNIT_COST();
				matOut.NSTD_DEL_DATE = itr.NSTD_DEL_DATE();
				
				vec.add(matOut);
				
			}
			}
			finally {
				stmt.close();
				itr.close();
				conn.close();
			}
		}
		catch (Exception e) {
			System.err.println(e);
		}
		finally {
			context.close();
		}
		}
finally{
	return vec;
}

Maybe guru Valery can also look at it?