Unable
to Get a Connection From a Data Source
Problem Description
A user receives an exception while trying to get a connection from a data source.
Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException
|
Scenario Type: |
Error analysis |
|
NetWeaver Component: |
J2EE Engine |
|
Validity |
J2EE Engine 6.40 and higher |
Possible Error Sources
● The database is not running, or the database URL is incorrect
● Wrong user or password parameters
● Maximum count of simultaneous connections to the database has been reached
● Problem with connection sharing if J2EE transaction is running
Decision Roadmap
Prerequisites
Main Tools
● Visual Administrator
or
● Standalone Log Viewer
Analysis
All the error messages mentioned below can be seen with the Log Viewer or in the browser window if the DataSource.getConnection() call is within a Web application.
Below, the possible reasons for the connection failure are explained together with the solutions.
...
The database is not running or the database URL is incorrect
The error message looks as follows:
Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.dbpool.exceptions.BaseResourceException: SQLException thrown by the physical connection: com.sap.dbtech.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Cannot connect to jdbc:sapdb://invalid_hostname/C11 [Unknown host invalid_hostname [invalid_hostname], -709.].
at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:59)
at jsp_index1079344494656._jspService(jsp_index1079344494656.java:37)
at com.sap.engine.services.servlets_jsp.server.servlet.JSPServlet.service(JSPServlet.java:464)
at com.sap.engine.services.servlets_jsp.server.servlet.JSPServlet.service(JSPServlet.java:181)
--- the remainder of trace is skipped ---
The problem can be solved using the Visual Administrator or by a manual modification of the data-sources.xml file.
● The solution using the Visual Administrator can be done in the following steps:
a. Open the Visual Administrator and select Server 0 ® Services ® JDBC Connector.
b.
Select the appropriate DataSource within the Runtimetab
on the right.
In the example this is TestDS DataSource from the sap.com/TestDS
application.
c.
Change the URL given in the Database
URL field
with the appropriate one.
In the screenshot below the database URL is jdbc:sapdb://invalid_hostname/C11. The URL format is correct;
however, the given hostname is wrong.
d. After changing the URL select Save. The Visual Administrator will prompt you for application redeployment. After confirming that, you should be able to get the connection from the database.
● The other option to fix the problem is to manually modify the <url> tag in the data-sources.xml file and redeploy the application. For more information see the data-sources.dtd deployment descriptor.
Wrong user or password parameters
The error message looks as follows:
Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.dbpool.exceptions.BaseResourceException: SQLException thrown by the physical connection: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-4008]: Unknown user name/password combination
at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:59)
at jsp_index1079089445437._jspService(jsp_index1079089445437.java:37)
at com.sap.engine.services.servlets_jsp.server.servlet.JSPServlet.service(JSPServlet.java:464)
at com.sap.engine.services.servlets_jsp.server.servlet.JSPServlet.service(JSPServlet.java:181)
--- the remainder of trace is skipped ---
The problem can be solved via the Visual Administrator or by a manual modification of the data-sources.xml file.
● The solution using the Visual Administrator consists in the following steps:
a. Open the Visual Administrator and select Server 0 ® Services ® JDBC Connector.
b. Select the appropriate DataSource within the Runtime tab on the right.
c. Modify the user and/or password field(s). See the screenshot above.
d. After modification press Save. The Visual Administrator will prompt you for application redeployment. After confirming that, you should be able to get the connection from the database.
● The other option to fix the problem is to manually modify the <user-name> and/or <password> tags in the data-sources.xml file and redeploy the application. For more information see the data-sources.dtd deployment descriptor.
The maximum count of simultaneous connections to the database has been reached.
The error message looks as follows:
com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot get connection for 60 seconds. Possible reasons: 1) Connections are cached within SystemThread(can be any server service or any code invoked within SystemThread in the SAP J2EE Engine), 2) The pool size of adapter "TestDS" is not enough according to the current load of the system or 3) The specified time to wait for connection is not enough according to the pool size and current load of the system. In case 1) the solution is to check for cached connections using the Connector Service list-conns command, in case 2) to increase the size of the pool and in case 3) to increase the time to wait for connection property. In case of application thread, there is an automatic mechanism which detects unclosed connections and unfinished transactions.
at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:59)
at jsp_index1079353203296._jspService(jsp_index1079353203296.java:63)
at com.sap.engine.services.servlets_jsp.server.servlet.JSPServlet.service(JSPServlet.java:464)
--- the remainder of trace is skipped ---
This exception is thrown when the count of maximum simultaneous connections to the database has been reached and none of the used connections is freed within the specified waiting time. The possible reasons are as follows:
1. There are connections in the thread started within the application component which have not been closed for a long time. We recommend observing the following rule in your application source code – right after the group of database operations has finished, the connection should be closed. This way the freed connection can be used within other components. If such code design is inappropriate for your needs, then the maximum number of simultaneous connections and/or maximum time to wait for a connection should be increased. See below.
2. The pool size (maximum number of simultaneously open connections) for the specific DataSource is too low for your system.
3. There are connections in the system component which have not been closed for a long time. Try to increase the maximum number of simultaneous connections or report the problem.
Increase maximum number of connections and/or the waiting time
To increase the maximum number of opened connections and/or the time to wait for a new connection you should change the DataSource properties in the Visual Administrator or manually modify of the data-sources.xml file.
● The necessary steps in the Visual Administrators are as follows:
a. Open the Visual Administrator and select Server 0 ® Services ® JDBC Connector.
b. Select the appropriate DataSource within the Runtimetab on the right.
c. Select the tab named Additional.
d. Set the desired values for the fields Maximum Connectionsand/or Maximum Time to Wait for Connection.
e. After your modification select Save. The Visual Administrator will prompt you for application redeployment. After confirming that, you should be able to get the connection from the database.
● The other option to fix the problem is to manually modify the values within <max-connections> and/or <max-time-to-wait-connection> tags in the data-sources.xml file and redeploy the application. For more information see the data-sources.dtd deployment descriptor.
Problem with connection sharing if J2EE transaction is running
The error message looks as follows:
Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot open resource(Managed Connection) with LocalTransaction support or add a second one in the same transaction, for component "webContainer/applications/sap.com/TestDB/TestDB" in application "sap.com/TestDB".
at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:59)
at jsp_in1079450298593._jspService(jsp_in1079450298593.java:78)
at com.sap.engine.services.servlets_jsp.server.servlet.JSPServlet.service(JSPServlet.java:464)
--- the remainder of trace is skipped ---
The problem is caused while in an active JTA transaction there is an inappropriate attempt to open a second connection. There is either an attempt to get a second connection from an un-shareable non-XA DataSource, or an attempt to get it from another non-XA DataSource. You have to:
● Use no more than one connection from this data source while the JTA transaction is active or
● Declare the data source as shareable, depending on your application logic.
Additional Information
Documentation:
●
Tuning Database
Connectivity