RE: JDBC Data Source

["Richard Dettinger" <cujo@xxxxxxxxxx>]

So when you say "DataSource classes are specific to
the driver they work with." I get confused.

Ok, let me try to do this with a rough example... I don't know if all the
code in here will be completely accurate, but it will be close and should
serve as a reasonable background on the subject.

The 'ideal' use of DataSources is broken into two phases.  There is a setup
of the DataSource and there is the use of the DataSource by an application.
The two phases are linked together typically by the use of JNDI.  That is,
someone sets up the DataSource and puts it at a JNDI location.  Then an
application comes along and uses the location to get at the object.

Registering a DataSource in JNDI would generally look something like this:

import java.sql.*;
import com.ibm.db2.jdbc.app.UDBDataSource;
import javax.naming.*;
import java.util.*;
import javax.sql.*;

public class UDBDataSourceExample1
{

/**
Deployment of a UDBDataSource object to a file
system using JNDI.
**/
    public static void main(java.lang.String[] args)
    throws Exception
    {
        // Create a DB2 DataSource and set the properties how
        // you would like them.
        UDBDataSource ds = new UDBDataSource();
        ds.setDescription("Basic JDBC DataSource for iSeries Native JDBC
driver");


        // Set the environment variables used by the default
        // initial context of JNDI.
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY,
                "com.sun.jndi.fscontext.RefFSContextFactory");
        env.put(Context.PROVIDER_URL, "file:/jdbc/DataSources");


        // Establish a JNDI context.
        Context ctx = new InitialContext(env);


        ctx.rebind("BaseDataSource", ds);
    }
}

As you can see in the above example, we bound a DataSource into JNDI.  I
don't want to get too into the various things that you can do with JNDI.
Here I just used the fscontext.jar file I talked about earlier.  As you can
see, the DataSource is implementation specific at this point in
development.  That is, I created a DataSource which is used with the Native
JDBC driver.  All this program has really done is create an object, set it
up and stuff a serialized version of it into IFS at the name
/jdbc/DataSources/BaseDataSource.

Ok, now we have a DataSource object out there that can be used.  So the
next thing is the use of this DataSource by an application.  That would
look similar to this:

import java.sql.*;
import com.ibm.db2.jdbc.app.UDBDataSource;
import javax.naming.*;
import java.util.*;
import javax.sql.*;

public class UDBDataSourceExample2
{

/**
Deployment of a UDBDataSource object to a file
system using JNDI.
**/
    public static void main(java.lang.String[] args)
    throws Exception
    {
        // Set the environment variables used by the default
        // initial context of JNDI.
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY,
                "com.sun.jndi.fscontext.RefFSContextFactory");
        env.put(Context.PROVIDER_URL, "file:/jdbc/DataSources");


        // Establish a JNDI context.
        Context ctx = new InitialContext(env);


        UDBDataSource ds = (UDBDataSource) ctx.lookup("BaseDataSource");
        System.out.println("Text: " + ds.getDescription());
    }
}

This program retrieved the DataSource we bound and output the description
to ensure that we had a valid object.  Instead of casting this object to a
UDBDataSource, I could have just used DataSource and used the interface
methods to start getting connections.  Of course, these connections are not
pooled, this is just a DataSource object.

So now you start to get into some nubulous areas where what works today
gets a little fuzzy.  The way DataSources are *supposed to work* (at least
as far as I can tell) is that you should be able to have a DataSource which
references a ConnectionPoolDataSource to provide users with pooling
behavior without the user ever doing anything different in their
application.  I don't believe the Toolbox implementation today does exactly
this.  The native JDBC support today in DB2DataSource and DB2StdDataSource
does not either.  I don't I believe WebSphere makes everything about this
invisble with its own wrappers.  Anyway, the UDBDataSource code is designed
to work this way so you would deploy with something like this:

import java.sql.*;
import com.ibm.db2.jdbc.app.UDBDataSource;
import com.ibm.db2.jdbc.app.UDBConnectionPoolDataSource;
import javax.naming.*;
import java.util.*;
import javax.sql.*;

public class UDBDataSourceExample3
{

/**
Deployment of a UDBDataSource object to a file
system using JNDI.
**/
    public static void main(java.lang.String[] args)
    throws Exception
    {
        // Create a DB2 DataSource and set the properties how
        // you would like them.
        UDBConnectionPoolDataSource cpds = new UDBConnectionPoolDataSource
();
        cpds.setDescription("Connection Pooling JDBC DataSource for iSeries
Native JDBC driver");


        // Set the environment variables used by the default
        // initial context of JNDI.
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY,
                "com.sun.jndi.fscontext.RefFSContextFactory");
        env.put(Context.PROVIDER_URL, "file:/jdbc/DataSources");


        // Establish a JNDI context.
        Context ctx = new InitialContext(env);

        // Put the value into our datasource collection.
        ctx.rebind("ConnectionPoolDataSource", cpds);


        // Now you need to get a user level data source pointing to the
        // underlying one.
        UDBDataSource ds = new UDBDataSource();
        ds.setDescription("Data source for Application work (over pool)");
        ds.setDataSourceName("ConnectionPoolDataSource");
        ctx.rebind("PoolingDataSource", ds);

    }
}


This example just created a ConnectionPoolDataSource and created a
DataSource which will use the ConnectionPoolDataSource so it will do
pooling.  In setting things up like this, the user application (which we
will see in a minute) will only ever have to know about DataSource but
connection pooling can be plugged in underneath it.  It should be pointed
out that the reason we have pooling going on now with the
ConnectionPoolDataSource is because the default setting for the
UDBConnectionPoolDataSource minConnections is 1.  Therefore, the data
source will be pooling 1 connection by default.  This is, of course,
configurable.

Just to make sure you understand where we are at here, we have now put a
two data sources into a JNDI context (BaseDataSource and PoolingDataSource
are their names).  We have also put a ConnectionPoolDataSource (with that
as its JNDI name) into the context as well.

Finally, here is how your program would use either of these DataSources:

import java.sql.*;
import com.ibm.db2.jdbc.app.UDBDataSource;
import javax.naming.*;
import java.util.*;
import javax.sql.*;

public class UDBDataSourceExample5
{

    public static int connectionsDesired = 100;

/**
Deployment of a UDBDataSource object to a file
system using JNDI.
**/
    public static void main(java.lang.String[] args)
    throws Exception
    {
        if (args.length != 0) {
            System.out.println("Setting connections desired to " +
args[0]);
            connectionsDesired = Integer.parseInt(args[0]);
        } else {
            System.out.println("Testing getting of " + connectionsDesired +
" connections");
        }

        // Do the setup for the test.
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.fscontext.RefFSContextFactory");
        env.put(Context.PROVIDER_URL, "file:/jdbc/DataSources");
        Context ctx = new InitialContext(env);


        // Do the work without a pool:
        UDBDataSource ds = (UDBDataSource) ctx.lookup("BaseDataSource");
        System.out.println("\nStart timing the non-pooling DataSource
version...");

        long startTime = System.currentTimeMillis();
        for (int i = 0; i < connectionsDesired; i++) {
            Connection c1 = ds.getConnection();
            c1.close();
        }
        long endTime = System.currentTimeMillis();
        System.out.println("End timing the non-pooling DataSource
version...");
        System.out.println("Time spent: " + (endTime - startTime));


        // Do the work with pooling:
        ds = (UDBDataSource) ctx.lookup("PoolingDataSource");
        System.out.println("\nStart timing the pooling version...");

        startTime = System.currentTimeMillis();
        for (int i = 0; i < connectionsDesired; i++) {
            Connection c1 = ds.getConnection();
            c1.close();
        }
        endTime = System.currentTimeMillis();
        System.out.println("End timing the pooling version...");
        System.out.println("Time spent: " + (endTime - startTime));
    }
}


The important thing to point out here is that the two timed runs are
identical other than the string name they used in lookup.  The only
difference is how we configured our DataSource objects.  Here are notes
that I have from one of the runs of this program when I was first testing
it.  I don't promise that it will perform quite like this now, its just the
best indicator I have right now without setting everything up and running
it again.

Testing getting of 100 connections

Start timing the non-pooling DataSource version...
End timing the non-pooling DataSource version...
Time spent: 2537

Start timing the pooling version...
End timing the pooling version...
Time spent: 29
Java program completed


This performance difference shouldn't be all that surprising.  In the one
case a new database connection is being created each time and in the other
a new wrapper object is being created for an open connection.

So anyway, there is much more than you probably wanted to understand about
DataSources at this point.  Now, to try to answer your question in a very
direct way.   You asked:

I've tested the ConnectionPool example from the book
"SERVLETS  and  JAVASERVER  PAGESTM by Marty Hall" but
it is a pool per servlet.  He talks a little about
a pool across the application server but doesn't give a
complete example.

If you want the easiest possible way to handle this, all you need to do is
scope your resources to the right place.  You don't need anything like a
DataSource at all.  For example, we created a website that used Servlets in
a simulation of an online book seller.  All the servlets got their database
connections from  a connection pool.  It worked something like this:

1) Our initial (home) servlet had an init method.
2) That method constructed a pool object (the pool object is public static)
3) The pool was nothing more than a stack of connection objects (push a
bunch of them on the stack)
4) Every servlet that needed a connection calls
Home.dbPool.getDBAccessObject();
5) When done, they ensure they call Home.dbPool.returnDBAccessObject(dbao);
6) These two methods are synchronized so that multiple threads running at
the same time can't mess the pool up.

Depending on how much of a solution you want, this can often solve the
problem.  I've talked about this in presentations before.  You can download
both a pool implementation and the slides that explain this at
http://www.as400.ibm.com/developer/jdbc/index.html.  The link for the
COMMON Spring 2001presentation has everything.  The link for the November
2000 iSeries Magazine article "How to Pool JDBC Objects" has just the
connection/statement pooling code in it.  If you want some words to go with
the code on connection pooling this way, that article is still available at
http://www.the400resource.com/content/monthly/200011/tips11.html

I think there is something in the Servlet API that allows access to a
global context and stuff like that, but I don't really write many servlets
anymore and I forget the details.  Someone else would have to provide that
kind of information.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"Biologists have a special word for stability -- dead"

                Larry Wall
                Open Source Developers Journal
                Issue 1, Jan  2000


"Patrick L Archibald" <Patrick.Archibald@HOMETELCO.COM>@midrange.com on
06/27/2001 11:57:35 AM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   <JAVA400-L@midrange.com>
cc:
Subject:  RE: JDBC Data Source



Richard

Bare with me.  I don't know if I know enough to ask
the right questions.

According to the javadocs: javax.sql.DataSource is

"a factory for Connection objects. An object that
implements the DataSource interface will
typically be registered with a JNDI service provider.
A JDBC driver that is accessed via the DataSource API
does not automatically register itself with the DriverManager."

So when you say "DataSource classes are specific to
the driver they work with." I get confused.

In Websphere there is a "Default DataSource" configured
which I use to get DB connections in all my servlets.

My question is how do I configure a DataSource to
use 1 pool of connections across all of my servlets
in Tomcat?

I've tested the ConnectionPool example from the book
"SERVLETS  and  JAVASERVER  PAGESTM by Marty Hall" but
it is a pool per servlet.  He talks a little about
a pool across the application server but doesn't give a
complete example.

Thanx, PLA

-----Original Message-----
From: owner-java400-l@midrange.com
[mailto:owner-java400-l@midrange.com]On Behalf Of Richard Dettinger
Sent: Wednesday, June 27, 2001 10:16 AM
To: JAVA400-L@midrange.com
Subject: RE: JDBC Data Source



Note:  when throughout this note, when I talk about there being a
DataSource class (like DB2DataSource), I really mean to suggest that there
is a complete suite of DataSources classes around that same base name (like
DB2ConnectionPoolDataSource and all the other stuff).

Which JDBC driver are you going to use?  DataSource classes are specific to
the driver they work with.  The native JDBC driver has a DB2DataSource and
DB2StdDataSource class which can be used to create connections (you would
want to use DB2StdDataSource as the DB2DataSource is a non-standard variety
from before there was a complete standard).  I think there is a prototype
of a connection pool implementation that uses them shipped with the driver,
but I would suggest you build your own pool if you are going to use these
(its not very hard).  That is what websphere does (which is why I never
wrote the complete pooling module in the first place).

The Toolbox JDBC driver has an AS400JDBCDataSource class which does the
same thing with toolbox connections.  They also have a class called
AS400ConnectionPool which is an implementation of a pool (I think).  I will
leave it up to someone else to explain it in more detail or you to read the
javadoc for it or ask more specific questions about it.  Here is something
interesting though... you could probably specify the Toolbox connection
property of driver=native and use the AS400JDBCDataSource class to
implement a native JDBC connection pool.  I don't know that it would work,
but I can't see an immediate reason why it wouldn't/shouldn't.

As far as running a naming service, in stand alone applications, I have
always used JNDI and the fscontext support (I think that is still a
seperately availble jar file).  The fs stands for file system and it allows
you to run JNDI over the file system.  So I persist my datasource to some
common IFS directory and load it from there to create the pool.

Finally:  I wrote a new set of data source classes for the Native JDBC
driver in v5r1 which I will be discussing in an article in the September
issue of News/400.  They are UDBDataSource, UDBConnectionPoolDataSource,
etc.  They are designed to support connection and statement pooling
completely transparently to the running applications that use them (read
the JDBC 3.0 spec available from Sun's website if you want to learn about
statement pooling and how it works).  This new feature does have all the
pooling support available (the pool gets created internally based on the
DataSource properties).  There is no XA support in this new feature yet,
but that will likely be in our next release.  If people are interested in
seeing some of the source code for the article that creates/uses the new
support, send me an e-mail and I will forward some examples to you (as I
get them written, that is).  There is some thought of allowing this new
DataSource feature work with both Native and Toolbox JDBC drivers, but I
have not done any work in that area just yet.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"Biologists have a special word for stability -- dead"

                Larry Wall
                Open Source Developers Journal
                Issue 1, Jan  2000


"Patrick L Archibald" <Patrick.Archibald@HOMETELCO.COM>@midrange.com on
06/27/2001 06:56:52 AM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   <JAVA400-L@midrange.com>
cc:
Subject:  RE: JDBC Data Source




Along the same  lines as Giles,  I'd like to know how  to set up a
DataSource connection pool in Tomcat on the AS/400.

Thanx,  PLA
-----Original Message-----
From: owner-java400-l@midrange.com  [mailto:owner-java400-l@midrange.com]On
Behalf Of DUCRET Gilles  (GVA)
Sent: Wednesday, June 27, 2001 3:25 AM
To:  'JAVA400-L@midrange.com'
Subject: JDBC Data  Source



Hi all,

has anyone of you ever used the Jdbc 2 datasource  feature on the as400?

In this cas, how do you run/manage the naming  service on the AS400 without
using  Websphere.

With Websphere it is really easy, since you can  declare this datasource.

In this case i can't use websphere.

Any example?

Many thanks

Gilles Ducret
Wealth Management Division
IT  Architect
Lloyds TSB Bank
Tel: + 41 22 307 31 50
Mob: +41 79 217 21 41


**********************************************************************
This  E-mail and any files transmitted with it are confidential
and intended for  the exclusive use of the addressee(s) only.
You should not disclose its  contents to any other person.
If you are not the intended recipient please  notify the  sender
immediately.
**********************************************************************




+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---

+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---