5 Replies Latest reply on Jul 27, 2010 3:43 PM by Joe Weinstein-Oracle

    com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000

    Tarun Boyella-Oracle
      Hi,

      We are running WebLogic 10.3.0 on RHEL 5.3 with JRockit JDK 1.6 and DB2 Version 8 on mainframe running z/OS 1.9.
      We are getting below exception in webLogic logs from connection pool connecting to DB2 database.

      >
      ####<Jul 23, 2010 10:05:49 AM EDT> <Warning> <JDBC> <testing.us.test.net> <testingDB2> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1279893949615> <BEA-001129> <Received exception while creating connection for pool "TestDB2Pool": DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000, SQLERRMC=MON_HEAP_SZ, DRIVER=3.53.70>
      ####<Jul 23, 2010 10:05:49 AM EDT> <Info> <JDBC> <testing.us.test.net> <testingDB2> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1279893949616> <BEA-001156> <Stack trace associated with message 001129 follows:

      com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000, SQLERRMC=MON_HEAP_SZ, DRIVER=3.53.70
           at com.ibm.db2.jcc.b.bd.a(bd.java:679)
           at com.ibm.db2.jcc.b.bd.a(bd.java:60)
           at com.ibm.db2.jcc.b.bd.a(bd.java:127)
           at com.ibm.db2.jcc.b.jb.a(jb.java:4016)
           at com.ibm.db2.jcc.t4.bb.l(bb.java:403)
           at com.ibm.db2.jcc.t4.bb.d(bb.java:141)
           at com.ibm.db2.jcc.t4.b.Oc(b.java:1305)
           at com.ibm.db2.jcc.t4.b.b(b.java:1225)
           at com.ibm.db2.jcc.t4.b.B(b.java:5129)
           at com.ibm.db2.jcc.t4.b.c(b.java:783)
           at com.ibm.db2.jcc.t4.b.b(b.java:726)
           at com.ibm.db2.jcc.t4.b.a(b.java:409)
           at com.ibm.db2.jcc.t4.b.<init>(b.java:345)
           at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:197)
           at weblogic.jdbc.common.internal.ConnectionEnvFactory.makeConnection(ConnectionEnvFactory.java:316)
           at weblogic.jdbc.common.internal.ConnectionEnvFactory.createResource(ConnectionEnvFactory.java:217)
           at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1109)
           at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1033)
           at weblogic.common.resourcepool.ResourcePoolImpl.createResources(ResourcePoolImpl.java:574)
           at weblogic.jdbc.wrapper.Connection.run(Connection.java:268)
           at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:516)
           at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
           at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
      >

      Below is snippet of my datasource configuration.


      <?xml version='1.0' encoding='UTF-8'?>
      <jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/jdbc-data-source" xmlns:sec="http://www.bea.com/ns/weblogic/90/security" xmlns:wls="http://www.bea.com/ns/weblogic/90/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bea.com/ns/weblogic/jdbc-data-source http://www.bea.com/ns/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd">
      <name>TestingDB2</name>
      <jdbc-driver-params>
      <url>jdbc:db2://DB2.testing.us.com:50003/TestingDB</url>
      <driver-name>com.ibm.db2.jcc.DB2Driver</driver-name>
      <properties>
      <property>
      <name>maxRetriesForClientReroute</name>
      <value>2</value>
      </property>
      <property>
      <name>retryIntervalForClientReroute</name>
      <value>5</value>
      </property>
      <property>
      <name>user</name>
      <value>testuser</value>
      </property>
      <property>
      <name>resultSetHoldability</name>
      <value>2</value>
      </property>
      <property>
      <name>enableSysplexWLB</name>
      <value>true</value>
      </property>
      <property>
      <name>enableConnectionConcentrator</name>
      <value>true</value>
      </property>
      <property>
      <name>databaseName</name>
      <value>DB2QA</value>
      </property>
      <property>
      <name>maxTransportObjects</name>
      <value>108</value>
      </property>
      </properties>
      <password-encrypted>{3DES}eh4WI16Xb8E=</password-encrypted>
      </jdbc-driver-params>
      <jdbc-connection-pool-params>
      <initial-capacity>40</initial-capacity>
      <max-capacity>215</max-capacity>
      <capacity-increment>1</capacity-increment>
      <shrink-frequency-seconds>300</shrink-frequency-seconds>
      <highest-num-waiters>2147483647</highest-num-waiters>
      <connection-creation-retry-frequency-seconds>15</connection-creation-retry-frequency-seconds>
      <connection-reserve-timeout-seconds>10</connection-reserve-timeout-seconds>
      <test-frequency-seconds>300</test-frequency-seconds>
      <test-connections-on-reserve>true</test-connections-on-reserve>
      <ignore-in-use-connections-enabled>true</ignore-in-use-connections-enabled>
      <inactive-connection-timeout-seconds>300</inactive-connection-timeout-seconds>
      <test-table-name>SYSIBM.SYSDUMMY</test-table-name>
      <login-delay-seconds>1</login-delay-seconds>
      <statement-cache-size>300</statement-cache-size>
      <statement-cache-type>LRU</statement-cache-type>
      <remove-infected-connections>true</remove-infected-connections>
      <seconds-to-trust-an-idle-pool-connection>15</seconds-to-trust-an-idle-pool-connection>
      <statement-timeout>-1</statement-timeout>
      <pinned-to-thread>false</pinned-to-thread>
      </jdbc-connection-pool-params>
      <jdbc-data-source-params>
      <jndi-name>TestingDB2</jndi-name>
      <global-transactions-protocol>None</global-transactions-protocol>
      </jdbc-data-source-params>
      </jdbc-data-source>


      Please help me on this.

      - BoyelT
        • 1. Re: com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000
          Joe Weinstein-Oracle
          Hi. Does this happen while WLS is trying to make the very first
          connection for the pool, or only after some connections succeed?

          This is essentially what WLS is doing. Can you try it in a tiny standalone
          Java program and tell me if it works? (include java.sql.* and java.util.*)

          Driver d = new com.ibm.db2.jcc.DB2Driver();
          Properties p = new Properties();
          p.add("user", "testuser");
          p.add("password", "XXXXXXX");
          p.add("maxRetriesForClientReroute", "2");
          p.add("retryIntervalForClientReroute", "5");
          p.add("resultSetHoldability", "2");
          p.add("enableSysplexWLB", "true");
          p.add("enableConnectionConcentrator", "true");
          p.add("databaseName", "DB2QA");
          p.add("maxTransportObjects", "108");

          Connection c = d.connect("jdbc:db2://DB2.testing.us.com:50003/TestingDB", p );
          System.out.println("We connected. The driver version is " + c.getMetaData().getDriverVersion() );
          • 2. Re: com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000
            Tarun Boyella-Oracle
            Hi Joe,

            Thanks for the reply. This does not happen for the very first connection.

            It happens after the domain is up and running and after some connections succeed.

            Thanks -
            • 3. Re: com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000
              Joe Weinstein-Oracle
              OK, then you will have to approach IBM for an explanation. Maybe there is a DBMS-enforced limit
              on the number of connections in total, or the number of connections one client (WLS) can have?

              If you expand my little code so it makes a number of simultaneous connections, maybe you will
              reproduce the problem... The fact is that WebLogic is just a Java program trying to make some
              number of connections, each one in that same way, and at some point/number-of-connections,
              the driver's request gets that failure message from the DBMS.
              • 4. Re: com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000
                Tarun Boyella-Oracle
                Thanks Joe. I will test the standalone client program you provided.

                Below are links I found on web on SQL CODE -972 error message

                http://www.dbforums.com/db2/875633-sqlcode-973-sqlstate-57011-drda_heap_sz-cobol-db2-error.html

                http://publib.boulder.ibm.com/infocenter/tivihelp/v4r1/index.jsp?topic=/com.ibm.tpc_V411.doc/fqz0_r_tbs_db2_sql_973.html

                http://www-01.ibm.com/support/docview.wss?uid=swg1IY55389

                Below is explanation about this message

                ===============================================
                SQL0973N Not enough storage is available in the "<heap-name>" heap to process the statement.
                Explanation: All available memory for this heap has been used. The statement cannot be processed.
                User Response: Terminate the application on receipt of this message (SQLCODE). Increase the configuration parameter for "<heap-name>" to increase the heap size.
                ===============================================

                Can you please advise if this is the problem from DBMS side?
                • 5. Re: com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=00000
                  Joe Weinstein-Oracle
                  Hi. It is certainly a DBMS-side issue. You would need a DBA to describe how you can
                  avoid that problem, whether it is configuring the DBMS to handle the load you are
                  giving it, or perhaps to find out whether there is some aspect of the per-user load
                  you are generating that is an issue.
                  Joe