Solved

Connection Pooling for MS SQL Server and Sun One App/Web Server

Posted on 2006-11-01
11
3,730 Views
Last Modified: 2013-11-21
Hi Experts. I am new to JAVA and I have been gien the responsibility to write and application using JDBC connection pooling with MS SQlServer Express. and SUN ONE Web server From my research, I use the SQLServerXADatasource to
register the datasource and get the connections
Somewhere this does not seem to work.
Please assist me is determining what other classes I need to use to get the connection pool to work.
Here is what I have done
I created the Connection Pool and JNDI resource in Sun One server
Then I do a lookup and bind in my java code with SQLServerXADatasource
Then I get a connection from the datasource (SQLServerXAdatasource.getConnection)
According to the documentation I have found thius far, that is all I need to do because SQlServerXADatasource implements and extends the other classes that I need
However, when I monitor the database, I don't see the connections and I still get a lot of outofmemory exceptions from the web server when I run my application under a high load.
Plase help me shed on light in this matter
Thansk you
0
Comment
Question by:rnzimiro
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 35

Expert Comment

by:girionis
Comment Utility
The OutOfMemoryError could be because you are using the connections and you never release them, so the application server creates even more to server more requests instead of getting the ones from the pool. It's hard to say what exactly is going on, but the steps you follow to create the connection pool and the datasource look correct. Can you read data from the database with the connection you get from the pool? Do you have a loop that you create connections in there? Do you release all the connections after you are done with them? How do you monitor the database?

Not sure if these question will help to identify the problem, but have a look at them and get back to us with more information.
0
 

Author Comment

by:rnzimiro
Comment Utility
Actually I am closing the connections after the database call has been completed. Initially I was destroying the connection objects but I realized after I close the connection, the connection should be returned to the pool. I am not creating the connection in a loop. To monitor the connections, I use SQL server 2005 studio and there is a facility that lets you monitor the connections to the database. When I monitor the database I can see the connections come in die which is puzzling because I thought that one the first connection is requested, the pool should, automatically create the minimum size of connections, say 20 but leave them all in stand-by until I need them. This is not the case.
Yes I can read from the database with a connection from the data source which is why I am confused. I don't know if the pool is created or if the data source get connection method is creating a physical connection.

Please advise. I need to know specifically how to use SQLServerXADataSource or SQLServerConnectionPoolDataSource. Which one to use, when to use them, when not use them

Thanks
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
> I thought that one the first connection is requested, the pool should, automatically create the minimum size of connections, say 20 but leave them all in stand-by until I need them. This is not the case.

This is not always the case. Usually the connections in the pool are created when the server starts up, therefore when you monitor the database you should already see the connections created. Of course you can also manually create the pool during run time and using Java code.

> I don't know if the pool is created or if the data source get connection method is creating a physical connection.

If you have created the pool and the datasource from the console of SUN ONE then you should already have a connection pool with a predefined number of physical connections and a datasource that is pointing to this pool.

Not sure though why you are getting the OutOfMemory exception. Have you used a profiling tool in order to see where most memory consumption occurs? Not sure what else to think of. The way you create and you use the datasource and connection pool seems ok, this is generally the way it is done.
0
 
LVL 3

Expert Comment

by:KnightFire
Comment Utility
Can you post your source code, or at least the relevant sections.
0
 

Expert Comment

by:ceritandogan
Comment Utility
Hello
i had the same problem On sun one web server, I don't know which sun one webserver are you using but ,

try http://docs.sun.com/source/817-1836-10/perftune.html#wp34760 ,

jDBC Connection Pool Attributes

set poolresizequantity to 5 to 20 if the connections are using frequently. ( For example 1..5 connection for each page request )  

But the principal thing be sure that you close your resultsets and statement when the execution is over.

"Out of memory" and "resource unavailable" errors are usually caused if the maxpoolsize is not enough.
Try to increase it.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:rnzimiro
Comment Utility
When I start the server, No Pools are created. I will postt he section of server.xml  and my source code. No I have not used profiling before, like I said I am new to JAVA.
Here is my connection manager code:
<=========================================================
import java.sql.*;
import java.util.*;
import javax.sql.*;
import javax.naming.*;
import com.microsoft.sqlserver.jdbc.*;
public class ConnectionPoolMgr {
   
    /** Creates a new instance of ConnectionPoolMgr */
    static Context cnt, myContext  = null;
    //static XAConnection conn = null;
    static SQLServerXADataSource sqVreg = null;
    static SQLServerXADataSource sqAreg = null;
    static SQLServerConnectionPoolDataSource sqlData = null;
    static SQLServerConnectionPoolDataSource sqlAVALData = null;
    //static DataSource sqlData = null;
    //static Hashtable hash = null;
    static boolean isInitialized = false;
    static boolean isAVALInitialized = false;
    public ConnectionPoolMgr() {
    }
    private static void RegisterDataSource(String strDatabase, String strServer, String strUser, String strPassword)throws NamingException{
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                sqVreg = new SQLServerXADataSource();
                sqVreg.setUser(strUser);
                sqVreg.setPassword(strPassword);
                sqVreg.setServerName(strServer+"\\SQLEXPRESS");
                //create the context
                myContext = new InitialContext();
                myContext.bind("vccon",sqVreg);
            }else{
                sqAreg = new SQLServerXADataSource();
                sqAreg.setUser(strUser);
                sqAreg.setPassword(strPassword);
                sqAreg.setServerName(strServer);
                //create the context
                cnt = new InitialContext();
                cnt.bind("vccon",sqAreg);          
            }
        } catch(NamingException ne){
            System.err.println("Error with registering context: " + ne.getMessage());
        }
    }
    static void InitializePool(String strDatabase, String strServer, String strUser, String strPassword)throws NamingException{
//        hash = new Hashtable();
//        hash.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.ldap.pool.PooledConnectionFactory");
//        hash.put(Context.PROVIDER_URL,"");
//        //javax.sql.DataSource
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                //RegisterDataSource(strDatabase,strServer,strUser,strPassword);
                sqlData = new SQLServerConnectionPoolDataSource();
                myContext = new InitialContext();
                sqlData.setDatabaseName(strDatabase);
                sqlData.setURL("jdbc:sqlserver://"+ strServer);
                //sqlData.setURL("jdbc/vccon");
                sqlData.setServerName(strServer+"\\SQLEXPRESS");
                sqlData.setUser(strUser);
                sqlData.setPassword(strPassword);
                //SQLServerPooledConnection ss = null;//SQLServerConnectionPoolDataSource();
                //sqlData.setPortNumber(1433);
                System.err.println("About to bind datasource....");
                myContext.bind("vccon",sqlData);
                //cnt.rebind("jdbc/VCConnectPool",sqlData);
                System.err.println("Datasource bound to JNDI service");
                GetDataSource(strDatabase);
            }else{
                sqlAVALData = new SQLServerConnectionPoolDataSource();
                cnt = new InitialContext();
                sqlAVALData.setDatabaseName(strDatabase);
                sqlAVALData.setURL("jdbc:sqlserver://"+ strServer);
                sqlAVALData.setServerName(strServer+"\\SQLEXPRESS");
                sqlAVALData.setUser(strUser);
                sqlAVALData.setPassword(strPassword);
                //SQLServerPooledConnection ss = null;//SQLServerConnectionPoolDataSource();
                //sqlData.setPortNumber(1433);
                System.err.println("About to bind datasource....");
                cnt.bind("avalconn",sqlAVALData);
                //cnt.rebind("jdbc/VCConnectPool",sqlData);
                System.err.println("Datasource bound to JNDI service");
                GetDataSource(strDatabase);
            }
        }catch(NamingException ne){
            System.err.println("Error with registering context: " + ne.getMessage());
        }
    }
    static void GetDataSource(String strDatabase)throws NamingException{
        //build th data source
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                sqlData = (SQLServerConnectionPoolDataSource)myContext.lookup("vccon");
                System.err.println("Pool Manager registered");
                isInitialized = true;
            }else{
                sqlAVALData = (SQLServerConnectionPoolDataSource)cnt.lookup("avalconn");
                System.err.println("Pool Manager registered");
                isAVALInitialized = true;
            }
        }catch(NamingException ne){
            System.err.println("An error occured: "+ ne.getMessage());
        }
    }
   
    static Connection GetConnection(String strDatabase) throws SQLException{
        Connection vcconn = null;
        PooledConnection conPool = null;
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                //conPool = sqlData.getPooledConnection();
                //vcconn = conPool.getConnection();
                vcconn = sqlData.getConnection();
            }else{
                //conPool = sqlAVALData.getPooledConnection();
                //vcconn = conPool.getConnection();
                vcconn = sqlAVALData.getConnection();
            }
        }catch(SQLException se){
            System.err.println("A connection Error has occured: "+se.getMessage());
        }
        return vcconn;
    }
}
==========================================================>

I did attempt to close the statement and the resultset explicitly but that did not address the issue.

Any other ideas?
Thanks
0
 

Author Comment

by:rnzimiro
Comment Utility
Forgot my config section

server.xml
<RESOURCES>
      <JDBCCONNECTIONPOOL name="Pool1" datasourceclassname="com.sybase.jdbc2.jdbc.SybDataSource" steadypoolsize="20" maxpoolsize="80" poolresizequantity="2" idletimeout="300" maxwaittime="60000" connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" validationtablename="" failallconnections="off" transactionisolationlevel="read-uncommitted" isolationlevelguaranteed="off">
        <PROPERTY name="portNumber" value="1040"/>
        <PROPERTY name="Password" value="*******"/>
        <PROPERTY name="serverName" value="131.16.91.28"/>
        <PROPERTY name="User" value="******"/>
      </JDBCCONNECTIONPOOL>
      <JDBCRESOURCE jndiname="jdbc" poolname="Pool1" enabled="on"/>
      <JDBCCONNECTIONPOOL name="VCConnect" datasourceclassname="com.microsoft.sqlserver.jdbc.SQLServerXADataSource" steadypoolsize="50" maxpoolsize="150" poolresizequantity="2" idletimeout="300" maxwaittime="60000" connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" validationtablename="" failallconnections="off" transactionisolationlevel="read-uncommitted" isolationlevelguaranteed="off">
        <PROPERTY name="User" value="*****"/>
        <PROPERTY name="Password" value="*****"/>
        <PROPERTY name="ref-type" value="javax.sql.XADataSource"/>
        <PROPERTY name="serverName" value="ACAPS-DEV\SQLEXPRESS"/>
        <PROPERTY name="port" value="1433"/>
        <PROPERTY name="databaseName" value="VISUAL_CONNECT"/>
        <PROPERTY name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      </JDBCCONNECTIONPOOL>
      <JDBCRESOURCE jndiname="vccon" poolname="VCConnect" enabled="on"/>
      <JDBCCONNECTIONPOOL name="avalPool" datasourceclassname="com.microsoft.sqlserver.jdbc.SQLServerXADataSource" steadypoolsize="50" maxpoolsize="150" poolresizequantity="5" idletimeout="300" maxwaittime="60000" connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" validationtablename="" failallconnections="off" transactionisolationlevel="read-uncommitted" isolationlevelguaranteed="off">
        <PROPERTY name="User" value="vc_user"/>
        <PROPERTY name="serverName" value="ACAPS-DEV\SQLEXPRESS"/>
        <PROPERTY name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <PROPERTY name="Password" value="*****"/>
        <PROPERTY name="portNumber" value="*****"/>
        <PROPERTY name="databaseName" value="AVAL"/>
      </JDBCCONNECTIONPOOL>
      <JDBCRESOURCE jndiname="avalconn" poolname="avalPool" enabled="on"/>
    </RESOURCES>
0
 

Expert Comment

by:ceritandogan
Comment Utility
I don't understand why you use ConnectionPoolMgr class

Try this simple piece of this code below:

try{
                            prod=true;
                            InitialContext initContext = new InitialContext();
                            DataSource source =  (DataSource) initContext.lookup("java:comp/env/vccon");
                            connection = source.getConnection();
                            return connection;
                        }catch(Exception e) {
                            e.printStackTrace();
                        }
Maybe this forum's thread can help you how to get a connection.
http://swforum.sun.com/jive/thread.jspa?threadID=49434&messageID=169670
Sun Web server can manage the connection pool using the datasource. No need to use ConnectionPoolMgr
The pool starts when you request to a jsp which is using the datasource

Regards
0
 

Author Comment

by:rnzimiro
Comment Utility
I already tried the above code and I get the same result. I am getting a connection fine with m above code, I just can't verify if a pool has been created.
I Created a connection Pool manager classes because I am using the DataSource class that ships with the JDBC 3.0 driver from Microsof for SQL server. The class does implement DataSoiurce so there should be no difference between DataSource and SQLServerXADataSource. I want to make sure that the ConnectionPool Manager class is only initialized  (registered)once for the life of the application or until the web server is restarted
0
 
LVL 35

Accepted Solution

by:
girionis earned 500 total points
Comment Utility
> I just can't verify if a pool has been created.

If you can read/write from/to the database then the pool and the datasource are created.

> I want to make sure that the ConnectionPool Manager class is only initialized  (registered)once for the life of the application or until the web server is restarted

This depends on you. If you create everything with the hand (i.e. by writing java code) then you have to make sure that the class containing the datasource and the pool won't be loaded several times by the rest of the components that use it. Otherwise, if you create it by using the console (and thus you leave it up to the application server) then you can be pretty sure that it will be loaded once.
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
:)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This video teaches viewers about errors in exception handling.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now