Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 358072

Summary: Connections to Informix DB are not reused (and not closed)
Product: z_Archived Reporter: meltie <tietjen>
Component: BIRTAssignee: Bin Feng <bin.feng>
Status: CLOSED WORKSFORME QA Contact: Sissi Zhu <szhu>
Severity: critical    
Priority: P3 CC: bluesoldier, mwu, tietjen
Version: 3.7.0   
Target Milestone: 4.2.2   
Hardware: PC   
OS: Windows 7   
Whiteboard:

Description meltie CLA 2011-09-19 04:27:30 EDT 
Build Identifier: v20100915-1750

We use BIRT designer 2.6.1 to create our reports and deploy them to our JBoss 4.2.3.GA application server running our application and BIRT engine 3.7.

We have a complex report that uses one datasource and six datasets. The datasets are each used several times within the report with different filters (the complexity of the report made it hard to do all the required filtering within the query). 

If a dataset is reused within a report, the database is queried once for each use, which is AFAIK normal BIRT behaviour. When running this report on a MySQL DB, all queries use the same database connection. When running the report on an Informix DB, a new database connection is created for every query. After executing the queries the connections are not closed. Another report will reuse the open connections, but the connections are not reused from within the same report.

The behaviour is the same for both JNDI and standard JDBC connections using URL and username/password. I tested this using a datasource configuration that only had the JNDI URL and no other connection data (to make sure there would be no fallback to JDBC connection), and vice versa for JDBC connections. I did numerous test with different datasource configurations and JNDI datasource options and the behaviour was always the same on Informix. I also tried different database drivers and such, with always the same result. 

Reproducible: Always

Steps to Reproduce:
1. Create a new report.
2. Create a datasource to connect with an Informix database (JNDI or standard JDBC)
3. Create a dataset using this datasource.
4. Use the dataset in the report several times with different filters. 
5. Check the database connections.
Comment 1 Bin Feng CLA 2012-05-04 04:54:41 EDT 
How the datasets are reused? Did they used in a nested table or grid? Or just having a serveral tab/xtab each tables uses a dataset.

Since the other report will reuse the open connections, the connection pool worked as expected. 
Another thing we should consider is: there is a maximum statement per connection option which is set in database. BIRT will open as many statements as they are needed until it hit java.sql.DatabaseMetaData.getMaxStatements( ). If your informix database also has a max statement = 1, it is possible for BIRT to open one connection per statement.
Comment 2 Bin Feng CLA 2013-01-18 05:29:50 EST 
The problem cannot be reproduced. Verified with BRDPro120517.