[JAVA] A fix to prevent the increase in the number of DB connections in the custom authentication provider of the Cognos SDK sample

Overview

In the Cognos Analytics SDK, there is a sample of a custom authentication provider that performs Cognos authentication in a Java program. * See below for custom authentication. ** Cognos Custom Authentication Setup Procedure ** https://qiita.com/shinyama/items/1f9c7842a3966233a5d7

In this sample program, I recently encountered some problems with endless connections to the database of the authentication source, which consumes a lot of Db2 memory.

Since this program is provided as a sample, it is provided on the premise that it is your own responsibility to use it in the production environment, but there are many projects that are actually used, and I am troubled with the same problem. I think there are many projects, so I will post them.

Coping

In queryImpl () of QueryUtil.java program, it is necessary to add the following "<-add" part and close statement and resultSet. Regarding connection, if you explicitly close it in the program, an authentication error will occur, so do not explicitly close it. However, connections that are no longer needed will be released at the time of the Cognos built-in WebSphere Liberty GC.

		try (final PreparedStatement statement = connection.prepareStatement(sql))
		{
			for (int i = 0; i < parameters.length; ++i)
				statement.setObject(i + 1, parameters[i]);

			try (final ResultSet resultSet = statement.executeQuery())
			{
				if (includeMetadata)
					data.add(QueryUtil.getMetaDataRow(resultSet));

		while (resultSet.next()) 
            		data.add(QueryUtil.getDataRow(resultSet)); 
 
 		resultSet.close(); 　　<-add to
                }
 		statement.close(); 　　<-add to
        } 
        catch (final SQLException ex) 
        { 
            throw new UnrecoverableException("SQL Exception", "An exception was caught while querying the authentication database."); 
        } 

<2018/6/29 postscript> In addition to the above SDK program fix, I found that changing the WebSphere Liberty GC policy built into Cognos will free up a large number of DB sessions before they accumulate, so I added it. Put.

For Linux, change the GC in the following file. /opt/ibm/cognos/analytics/bin64/bootstrap_wlp_linuxi38664.xml

Change the gencon here and restart Cognos. <param condName="${java_vendor}" condValue="IBM">-Xgcpolicy:gencon</param>

In that case, I changed it to "Balanced". https://www.ibm.com/developerworks/jp/websphere/library/was/was8_gc/2.html

If you want to use Restorable JDBC Sample and JDBC Sample with custom authentication, please remember this article.