DB Connection lifespan

[Prasant Barik]

Hi All,

I have created some tests and in between am connecting to the database using the JDBC step available in devtest.

Once the connection gets established, am able to do multiple operations as usual on the DB.

But i am not able to see any step in devtest to close the connection from the DB.

Does devtest take care of the closing of the connection by itself once the test gets closed or do i need to explicitly close the connection, if yes how can I do that.

Please help to answer.

Thanks

------------------------------
Prasant
------------------------------

[Sankar Natarajan]

Hi Prasant

        It was not clear to me if you are using the JDBC connection pool. If you are using the connection pool, devtest creates a connection pool for you and manages idle connections and taking care of returning connections to the pool based on various parameters. Please refer to this URL for lisa.jdbc.pool.* properties. https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/continuous-testing/devtest-solutions/10-5/devtest-property-file-lisa-properties.html

In case if you are not using connection pool, the connection is automatically closed after the SQL step/operation completed. In case if you are not using connection pool and if you choose the option 'Keep Connection Open' then the following happens "If this option is selected, the database connection that is opened the first time that the step executes is cached. That database connection is then closed when garbage collection happens for the step. If 

Keep Connection Open

 is not selected, the connection is closed each time that the step executes. " Refer: https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/continuous-testing/devtest-solutions/10-5/reference/test-step-descriptions/other-transaction-steps/sql-database-execution-jdbc.html

We highly recommend using connection for efficient management of your DB connections.

--

regards

Sankar Natarajan

[Sankar Natarajan]

Please read the last line as " We highly recommend using connection pool for efficient management of your DB connections. "

--

regards

Sankar Natarajan

[venkat raj]

Hi Sankar,

Even We are facing the same kind of issue. After opening the connection to the database, on completion of the run the connection doesn't get closed automatically.
I have tried your suggestion of using the connection Pool.

The properties I have used in local.properties are as below:

lisadb.reporting.poolName=common
lisadb.acl.poolName=common
lisadb.broker.poolName=common
lisadb.dradiscache.poolName=common

lisadb.pool.common.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
lisadb.pool.common.url=jdbc:sqlserver://Hostname:Port;databaseName=DATABASENAME (Used our project Connection URL along with username and password)
lisadb.pool.common.user=*****
lisadb.pool.common.password_enc=*****

lisadb.pool.common.minPoolSize=0
lisadb.pool.common.initialPoolSize=0
lisadb.pool.common.maxPoolSize=10
lisadb.pool.common.acquireIncrement=1
lisadb.pool.common.maxIdleTime=45
lisadb.pool.common.idleConnectionTestPeriod=5

After providing the above configurations in the logs i could see the following:

DEBUG com.itko.util.JdbcConnectionPool - Waited 0 ms for connectioncom.mchange.v2.c3p0.impl.NewProxyConnection@6e0768bf [wrapping: ConnectionID:36 ClientConnectionId: de686641-961a-4883-a8c4-5cdf944aedec] from DataSource: com.mchange.v2.c3p0.PoolBackedDataSource@a108e9fc [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@b4b3c1a4 [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2st33fa61affxatbei2rm|4950e17d, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 45, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@2829aacb [ description -> null, driverClass -> null, factoryClassLocation -> null, forceUseNamedDriverClass -> false, identityToken -> 2st33fa61affxatbei2rm|1ba4f653, jdbcUrl -> jdbc:sqlserver://HOSTNAME:PORT, properties -> {user=******, password=******} ], preferredTestQuery -> null, privilegeSpawnedThreads -> false, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, extensions -> {}, factoryClassLocation -> null, identityToken -> 2st33fa61affxatbei2rm|6f0152c6, numHelperThreads -> 3 ]

I have set the initial Pool size to be 0, but in the logs i see the count to be 3. So I guess the values which i have configured in the properties file has not been configured properly. Can you please help on the configuration part?

[Sankar Natarajan]

HI Venkat

               Did you restart the DevTest components after updating the connection pool settings ? I expect Registry, Coordinator and Simulator components to be restarted.
--

regards

Sankar Natarajan

[venkat raj]

Hi sankar,

I am trying to run the step from my local machine in ITR mode. I have restarted the workstation after making the changes.

[Sankar Natarajan]

HI Venkat

             PLease update the db connection pool properties on Registry machine and restart registry, coord and simulator components once and check.

--

regards

Sankar Natarajan

[venkat raj]

Hi Sankar,
I have added the db connection pool properties on Registry machine and after the restart I am getting the below error:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "SVSE_TRANSACTIONS" because it does not exist or you do not have permissions.
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - ==== #################################### ====
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - Database does not contain a table ACTIVATION
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - ==== #################################### ====
2019-11-26 18:57:26,786Z (02:57) [main] ERROR System.err - Database does not contain a table ACTIVATION
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - ==== #################################### ====
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - Database does not contain a table DRADISCACHE
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - ==== #################################### ====
2019-11-26 18:57:26,786Z (02:57) [main] ERROR System.err - Database does not contain a table DRADISCACHE
2019-11-26 18:57:26,786Z (02:57) [main] ERROR com.itko.lisa.utils.JpaUtil - ==== #################################### ====

The registry is trying to connect to the DB which is configured in the local.properties file instead of the registry DB. 
Is it right to add the DB connection pool properties in the local.properties file of the registry server?

[venkat raj]

Hi Sankar, 
Can you help on the above error?