?
Solved

Setup DB2 8.0 with Tomcat 5.5

Posted on 2005-04-15
51
Medium Priority
?
1,530 Views
Last Modified: 2007-12-19
Hi I have a Windows PC running
1/ Apache Tomcat Web server (Version 5.5)  
2/ DB2 Database server (Version 8.0)

I can connect DB2 working with Tomcat normally in separate JSP page. It 's working fine !
Now, my boss want me to setup Tomcat with DB2 in professional way: anytime user connect to the site then it automatically connects to database, then after user log-out or time session expired (let say in 30 minutes) or user close the browser ... then database is disconnected.

Do you think that is that possible or not ? If it's possible, the please help me to step by step how to set this up.

Thanks a lot.
0
Comment
Question by:mnphan
  • 22
  • 15
  • 14
51 Comments
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13794411
I dont understand your question..
if you have your connection coded in the first page of your web app it will connect to the database as soon as you access the page...
so what do you mean that it connects to the database any time user connects to the site?
0
 

Author Comment

by:mnphan
ID: 13794852
Sorry ! it made you confused !

My web server is to show data from database to users. Most of the pages in this server need to connect to database.

Right now in every page that need to query data, I have to connect to database, query data and then disconnect. User then click on the next page, he need to connect to database, query other data and then disconnect again and again... and so on...

My boss thinking that it will slower the database performance (right ?) so he want me to find the way that connect database only at the first time, then do not need to disconnect from database then connect to database again and again. Only when user logout or session expired or close the browser the  connection to database will be disconnected.

Hope this's clear now !
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13795374
Alright! Now I get it...
You need to use the Connection pooling...

Here is a very good article on connection pooling which will help you thru...

http://www.datadirect.com/developer/jdbc/topics/connpooling/index.ssp
http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html

Let me know if you need more help
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mnphan
ID: 13795436
Hi !

The second link looks more interesting !
However, in that it provides some example codes to modify the server.xml and web.xml in other database languages (mysql, oracle...) could you provide me an example of DB2 code, please. It will save me a lot of time.

Thank so much.
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13796093
Hi,

Normally, what you need to do is to download the JDBC driver for IBM DB2 and place it in your TOMCAT_HOME/common/lib as well as in your WEB_APPLICATION/WEB-INF/lib. Then you need to change few parameters in your server.xml i.e. driverClassName and url.

I will try to find the location to download the DB2 JDBC driver.

Best Regards
Dave
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13796101
Hi,

Please have a look at http://www.experts-exchange.com/Web/Application_Servers/Q_20460211.html

I just extracted one comment that I think is useful for you.

----------------------------------------------------------------
Here's my version using DB connection pooling and JNDI
Get the following packages from
http://jakarta.apache.org/commons:
commons-dbcp.jar
commons-pool.jar
commons-collections.jar

These provide the connection pool functionality. Copy
the jars to $CATALINA_HOME/common/lib.

Copy the DB2 driver jar to the same directory. On my
machine the DB2 instance is called db2inst1:
cp ~db2inst1/sqllib/java12/db2java.zip $CATALINA_HOME/common/lib/db2java.jar
Note the extension .jar in the copied file. This is important!

Now configure the datasource for tomcat. Edit $CATALINA_HOME/conf/server.xml and add the following
to the DefaultContext element:

          <Resource name="jdbc/db2ds"
                    auth="Container"
                    type="javax.sql.DataSource" />
          <ResourceParams name="jdbc/db2ds" >
            <parameter>
              <name>factory</name>
              <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
            </parameter>
            <parameter>
              <name>maxActive</name>
              <value>10</value>
            </parameter>
            <parameter>
              <name>maxIdle</name>
              <value>3</value>
            </parameter>
            <parameter>
              <name>maxWait</name>
              <value>10000</value>
            </parameter>
            <parameter>
              <name>username</name>
              <value>db2inst1</value>
            </parameter>
            <parameter>
              <name>password</name>
              <value>db2inst1</value>
            </parameter>
            <parameter>
              <name>driverClassName</name>
              <value>COM.ibm.db2.jdbc.app.DB2Driver</value>
            </parameter>
            <parameter>
              <name>url</name>
              <value>jdbc:db2:wasrepos</value>
            </parameter>
         </ResourceParams>

This creates a datasource named "jdbc/db2ds" which connects
to the database catalogued as WASREPOS using the
the user db2inst1 and the password db2inst1.

Now code something which uses this datasource, e.g.

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;
import javax.sql.*;
import java.sql.Connection;
import java.sql.SQLException;

public class DSTestServlet extends HttpServlet {

    public void doGet(HttpServletRequest req,
                HttpServletResponse res)
     throws ServletException, IOException {

     String dsName = req.getParameter("ds");
     
     PrintWriter out = res.getWriter();
     res.setContentType("text/html");
     out.println("<html>\n<head>\n<title>DSTestServlet</title>\n</head>");
     out.println("<body style=\"background-color: white;\">");
     if ( dsName == null ) {
         out.println("<h1>Parameter 'ds' is empty.</h1>");
     } else {
         try {
          Context initCtx = new InitialContext();
          Context envCtx = (Context)initCtx.lookup("java:comp/env");

          DataSource ds = (DataSource)envCtx.lookup(dsName);
          Connection conn = ds.getConnection();
          out.println("<h1>Successfully connected to " + dsName +
                   "</h1>");
          conn.close();
          out.println("<h1>Successfully disconnected from " + dsName +
                   "</h1>");
         }
         catch (SQLException e) {
          out.println("<h1>Connect failed: " + e + "</h1>");
         }
         catch (NamingException e) {
          out.println("<h1>NamingException while resolving " + dsName +
                   ": " + e);
         }
            catch (InterruptedException e) { }
     }
     out.println("</body>\n</html>");
     out.close();
     
    }
}

Compile it and install it (i.e create an entry in
the appropriate web.xml for it).
If you haven't done so already, set the DB2 environment
variables. On my machine: . ~db2inst1/sqllib/db2profile
(This is UNIX specific.On Win you'd open a DB2 command
window). If you are on Linux: do an export LD_ASSUME_KERNEL=2.2.5. The DB2 "app" JDBC-driver behaves strange, otherwise.

Restart Tomcat.

Assuming that the DSTestServlet has been deployed
under the context "myapp", you should now be
able to access it and tell it to use the datasource
name "jdbc/db2ds" using: http://localhost:8080/DSTestServlet?ds=jdbc/db2ds

I've tested this setup using Tomcat 4.0.3, DB2 7.2 on
RH Linux 7.2.

If you'd rather use the "net" driver than the "app" driver,
replace the driverClassName in server.xml by
COM.ibm.db2.jdbc.net.DB2Driver and the url by
jdbc:db2://localhost:4777/wasrepos. Remember to start
the DB2 java daemon on the DB server machine,. if you
are using the "net" driver: db2jstrt 4777
 --------------------------------------------------------

Pleaes let me know if you need any help.

Best Regards
Dave
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13796106
Hi,

Setting up datasource for DB2 in Tomcat
http://www.logemann.org/day/archives/000059.html

IBM JDBC Driver Download
http://developers.sun.com/product/jdbc/drivers

Remember to download the JDBC driver for IBM DB2 and place *.jar to your TOMCAT_HOME/common/lib as well as in your WEB_APPLICATION/WEB-INF/lib. Then you need to change few parameters in your server.xml i.e. driverClassName and url.

Please let me know if you need any help.

Best Regards
Dave
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13808606
sorry for late reply, but looks like suprapto has got it covered already....
0
 

Author Comment

by:mnphan
ID: 13808791
Sorry all of you for my late reply because I had a trouble internet access in the weekend.
I am still working on this matter based on your advices, however I still have problem, get some error. Let me double check and then give you the result.

Minh Phan
0
 

Author Comment

by:mnphan
ID: 13811135
Sorry, I can not get it work,
please point out what I did it wrong to help me out.

here is what I have:
local DB2 database: (Vesion 8)
database system name: sample
username: user
password: pass
table person (first varchar(20), last varchar(20))




Tomcat Server (Version 5.5)
#
# $CATALINA_HOME/conf/server.xml  ##############
# these code put below the </host> tag ????????????
#

<Resource type="com.ibm.db2.jcc.DB2SimpleDataSource" name="jdbc/db2ds" auth="Container"/>
<ResourceParams name="jdbc/db2ds" >
      <parameter>
            <name>factory</name>
            <value>com.ibm.db2.jcc.DB2DataSourceFactory</value>
      </parameter>
      <parameter>
            <name>maxActive</name>
            <value>10</value>
      </parameter>
      <parameter>
            <name>maxIdle</name>
            <value>3</value>
      </parameter>
      <parameter>
            <name>maxWait</name>
            <value>10000</value>
      </parameter>
      <parameter>
            <name>username</name>
            <value>user</value>
      </parameter>
      <parameter>
            <name>password</name>
            <value>pass</value>
      </parameter>
      <parameter>
            <name>driverClassName</name>
            <value>COM.ibm.db2.jdbc.net.DB2Driver</value>
      </parameter>
      <parameter>
            <name>url</name>
            <value>jdbc:db2://localhost:6789/sample</value>
      </parameter>
</ResourceParams>


#
# $CATALINA_HOME/conf/web.xml  ##############
# these code put just above the </web-app> tag ????????????
#

<description>DB2 Test App</description>
<resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/db2ds</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
</resource-ref>


#
# jsp test file: test.jsp
#

<%@ page import = "javax.naming.*" %>
<%@ page import = "java.util.*" %>
<%@ page import = "javax.sql.*" %>
<%@ page import = "java.sql.*" %>

<%
String last, first;
out.println("<html>\n<head>\n<title>" + "Student Names</title>" + "\n</head>\n<body>");

try
{
      Context initCtx = new InitialContext();
      Context envCtx = (Context)initCtx.lookup("java:comp/env");

      DataSource ds = (DataSource)envCtx.lookup("jdbc/db2ds");

      Connection con = ds.getConnection();

      Statement s = con.createStatement();
      String sql = "select * from person";
      ResultSet rs = s.executeQuery(sql);

      while (rs.next())
      {
            last = rs.getString("last");
            first = rs.getString("first");
            out.println(last + " " + first + "<br>");
      }
      rs.close();
      s.close();
      con.close();
}
catch (NamingException e)
{
      out.println("<h1>Failed to resolve datasource</h1>\n<pre>");
      out.println(e.toString());
}
catch (SQLException e)
{
      out.println("<h1>SQL exception</h1>\n<pre>");
      out.println(e.toString());
}
catch (Exception e)
{
      out.println("<h1>General exception</h1>\n<pre>");
      out.println(e.toString());
}
out.println("</body>\n</html>");
%>





Error receive:
SQL exception
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'


0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13811778
Hi,

The error is caused by the wrong configuration of the datasource in your server.xml. It may not be wrong but it lacks of libraries or etc. Have you located all your *.jar to your TOMCAT_HOME/common/lib as well as your WEB-INF/lib?

Thank you.

Best Regards
Dave
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13811781
Hi,

I mean all the IBM DB2 *.jar to be located into your TOMCAT_HOME/common/lib as well as your WEB-INF/lib.

Best Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13818031
Yes, i have db2jcc.jar and db2java.jar in both:
TOMCAT_HOME/common/lib and WEB-INF/lib folders
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13818088
I normally put the details also in web.xml..
try doing this.. make your web.xml as follows..

<context>
>>
>>
>>

<Resource type="javax.sql.DataSource" name="jdbc/db2ds" auth="Container"/>
<ResourceParams name="jdbc/db2ds" >
     <parameter>
          <name>factory</name>
          <value>com.ibm.db2.jcc.DB2DataSourceFactory</value>
     </parameter>
     <parameter>
          <name>maxActive</name>
          <value>10</value>
     </parameter>
     <parameter>
          <name>maxIdle</name>
          <value>3</value>
     </parameter>
     <parameter>
          <name>maxWait</name>
          <value>10000</value>
     </parameter>
     <parameter>
          <name>username</name>
          <value>user</value>
     </parameter>
     <parameter>
          <name>password</name>
          <value>pass</value>
     </parameter>
     <parameter>
          <name>driverClassName</name>
          <value>COM.ibm.db2.jdbc.net.DB2Driver</value>
     </parameter>
     <parameter>
          <name>url</name>
          <value>jdbc:db2://localhost:6789/sample</value>
     </parameter>
</ResourceParams>
</context>

and then try your java class
0
 

Author Comment

by:mnphan
ID: 13818478
Hi
In my web.xml, I do not have a context tag, so I create one at above the last line (</web-app>) and put your code inside it. Restart Tomcat, then run the jsp file, now I got a new error:

Failed to resolve datasource
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context

and one thing: Do you guys think that the url is correct:
     <parameter>
          <name>url</name>
          <value>jdbc:db2://localhost:6789/sample</value>
     </parameter>
specially for the port number.

Thanks


0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13818995
in what web.xml you put it in??
web.xml is suppose to have the context for your web app..
normally it looks something like
<Context path="/din" docBase="C:\Tomcat5.0\webapps\din" debug="0" reloadable="true" workDir="C:\Tomcat5.0\webapps\din\work"
crossContext="true">

if you are on tomcat 5.0 & above then this file is created by the name of your app in conf/catalina/localhost directory..

i.e. in above example it will be din.xml...
so thats the xml file you need to update..
I do not know if the URL is correct.. I will check and let you know ( never setup a DB2 connection before)
0
 

Author Comment

by:mnphan
ID: 13819085
I am using Tomcat 5.5

The code is put inside web.xml of
C:\ApacheTomcat5.5\conf\web.xml.  


But I checked the web.xml in
C:\ApacheTomcat5.5\webapps\ROOT\WEB-INF\web.xml
This file does not have the <Context> tag neither.

0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13819124
if you are using 5.5. than conf shd have a folder named as localhost this folder keeps all the webapps context files in it..

you should have one file there with yourwebapp.xml name ( if you are not having one that means your web app is being loaded thru autoloader)..
remove your changes from web.xml and put them in this xml file..
if you do not have one then create one.. it will start like

<Context path="/yourwebapp" docBase="yourwebappslocation" debug="0" reloadable="true" workDir="yourwebapplocation\work" crossContext="true">
<Resource type="javax.sql.DataSource" name="jdbc/db2ds" auth="Container"/>
<ResourceParams name="jdbc/db2ds" >
     <parameter>
          <name>factory</name>
          <value>com.ibm.db2.jcc.DB2DataSourceFactory</value>
     </parameter>
     <parameter>
          <name>maxActive</name>
          <value>10</value>
     </parameter>
     <parameter>
          <name>maxIdle</name>
          <value>3</value>
     </parameter>
     <parameter>
          <name>maxWait</name>
          <value>10000</value>
     </parameter>
     <parameter>
          <name>username</name>
          <value>user</value>
     </parameter>
     <parameter>
          <name>password</name>
          <value>pass</value>
     </parameter>
     <parameter>
          <name>driverClassName</name>
          <value>COM.ibm.db2.jdbc.net.DB2Driver</value>
     </parameter>
     <parameter>
          <name>url</name>
          <value>jdbc:db2://localhost:6789/sample</value>
     </parameter>
</ResourceParams>
</Context>

then test the app

0
 

Author Comment

by:mnphan
ID: 13819272
There is a file: manager.xml in this folder: C:\Apache Tomcat 5.5\conf\Catalina\localhost

here is its content:
<Context docBase="${catalina.home}/server/webapps/manager"
         privileged="true" antiResourceLocking="false" antiJARLocking="false">

  <!-- Link to the user database we will get roles from -->
  <ResourceLink name="users" global="UserDatabase"
                type="org.apache.catalina.UserDatabase"/>
</Context>



So now I added your code inside, it's now:




<Context docBase="${catalina.home}/server/webapps/manager"
         privileged="true" antiResourceLocking="false" antiJARLocking="false">

  <!-- Link to the user database we will get roles from -->
  <ResourceLink name="users" global="UserDatabase"
                type="org.apache.catalina.UserDatabase"/>


<Resource type="javax.sql.DataSource" name="jdbc/db2ds" auth="Container"/>
<ResourceParams name="jdbc/db2ds" >
     <parameter>
          <name>factory</name>
          <value>com.ibm.db2.jcc.DB2DataSourceFactory</value>
     </parameter>
     <parameter>
          <name>maxActive</name>
          <value>10</value>
     </parameter>
     <parameter>
          <name>maxIdle</name>
          <value>3</value>
     </parameter>
     <parameter>
          <name>maxWait</name>
          <value>10000</value>
     </parameter>
     <parameter>
          <name>username</name>
          <value>user</value>
     </parameter>
     <parameter>
          <name>password</name>
          <value>pass</value>
     </parameter>
     <parameter>
          <name>driverClassName</name>
          <value>COM.ibm.db2.jdbc.net.DB2Driver</value>
     </parameter>
     <parameter>
          <name>url</name>
          <value>jdbc:db2://localhost:6789/sample</value>
     </parameter>
</ResourceParams>
</Context>


After restart Tomcat, running the temp.jsp

Still got same error:

Failed to resolve datasource
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context



0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13819398
okay there is one more that we need to check..
do you have following lines in your web.xml under web-inf directory???
if not then you need them..

<resource-ref>
     <description>DB Connection</description>
     <res-ref-name>jdbc/db2ds</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

it shd be just above the closing tag of <web-app>

after this restart the tomcat and see if it picks up
0
 

Author Comment

by:mnphan
ID: 13819779
I added the code inside C:\ApacheTomcat5.5\webapps\ROOT\WEB-INF\web.xml

then restarted PC

thrn run the test file, got another error:

SQL exception
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13821132
Mmm....let me check that out. Sorry for the delay though

regards
Dave
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13830177
Hi mnphan,

Can you post me your entire server.xml please? I am suspicious that you wrongly put your datasource. It should be above the </host>

Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13836795
Hi , I post the file my my web server.
Here is the link:

http://phanngoc.com/server.xml

0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13836940
Thats what I have been saying all along...
you do not have to modify conf/server.xml ( unless you want to make a datasource for every webapp in tomcat)...

for example the webapp name of your application is MyTest
and its residing in
c:\tomcat5.0\webapp then these are the files you need to change and this is how you need to change them..

in c:\tomcat5.0\conf\Catalina\localhost\MyTest.xml

<Context path="/MyTest" docBase="C:\Tomcat5.0\webapps\MyTest" debug="0" reloadable="true" workDir="C:\Tomcat5.0\webapps\MyTest\work" crossContext="true">
  <Resource name="jdbc/TESTDB" auth="Container" type="javax.sql.DataSource" />
              <ResourceParams name="jdbc/TESTDB">
                  <parameter>
                    <name>username</name>
                    <value>user</value>
                  </parameter>
                  <parameter>
                    <name>password</name>
                    <value>Pwd</value>
                  </parameter>
                  <parameter>
                    <name>driverClassName</name>
                    <value>com.ibm.db2.jdbc.net.DB2Driver</value>
                  </parameter>
<parameter>
<name>url</name>
<value>jdbc:db2:SAMPLE</value>
</parameter>
<parameter>
  <name>max-connections</name>
  <value>10</value>
</parameter>
 </ResourceParams>
</Context>

and then in
c:\tomcat5.0\webapps\MyTest\Web-Inf\Web.xml should have these lines at the end..

<resource-ref >
         <res-ref-name>jdbc/TESTDB</res-ref-name>
         <res-type>javax.sql.DataSource</res-type>
         <res-auth>Container</res-auth>
      </resource-ref>
 </web-app>

then your DB class should be doing

try
{
     Context initCtx = new InitialContext();
     Context envCtx = (Context)initCtx.lookup("java:comp/env");

     DataSource ds = (DataSource)envCtx.lookup("jdbc/TESTDB");

     Connection con = ds.getConnection();

     Statement s = con.createStatement();
     String sql = "select * from person";
     ResultSet rs = s.executeQuery(sql);


I know it works!!!
0
 

Author Comment

by:mnphan
ID: 13838722
Hi,

I do exactly followed your instruction:

1/ Restore the server.xml with the original file (i.g. nothing change)
2/ Create a new webapp folder: MyTest inside : c:\tomcat5.5\webapps folder
3/ Create xml file MyTest.xml inside c:\tomcat5.5\conf\Catalina\localhost\
4/ Create xml file web.xml inside: c:\tomcat5.5\webapps\MyTest\WEB-INF\
5/ Copy 2 driver files (not sure if these files is correct) : db2jcc.jar, db2java.jar into into lib folder C:\tomcat5.5\webapps\MyTest\WEB-INF\lib\
6/ Create a test file inside C:\tomcat5.5\webapps\MyTest\

restart tomcat then run it, and I got old error message:
"org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'"

I change the URL from: to I change the url from I change the url to be: jdbc:db2:sample
 to be: jdbc:db2://localhost:6789/sample, it's the same.

Any idea ?
 
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13838744
in your catch blocks.. do a e.printStackTrace();
It will give us a full list of exception as where its being generated... now your setup is perfect.. we need to tweak the driver and url...
we will know once we see the trace...
0
 

Author Comment

by:mnphan
ID: 13839031
I see something in the log file:

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
      at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:780)
      at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
      at org.apache.jsp.test_jsp._jspService(org.apache.jsp.test_jsp:63)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:825)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:738)
      at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:526)
      at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
      at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: No suitable driver
      at java.sql.DriverManager.getDriver(Unknown Source)
      at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:773)
      ... 22 more
0
 

Author Comment

by:mnphan
ID: 13839045
So the problem is the DB2 Driver or JDBC Driver ?
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13841089
Okay.

What you need to do now is to try it in a simple Java application (Traditional java App) and NOT in web based application.

http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2a0/db2a0159.htm

Try the sample codes there. Please let us know whether you can connect or not.

Regards
Dave
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13843694
before that lets make sure that we are reading the JNDI name properly..

in your code lets put some System. outs..

try
{
     Context initCtx = new InitialContext();
     Context envCtx = (Context)initCtx.lookup("java:comp/env");

     DataSource ds = (DataSource)envCtx.lookup("jdbc/db2ds");
     System.out.println(ds); // atleast see if we have got an object... most probably there will be an exception before this line..
     Connection con = ds.getConnection();

     Statement s = con.createStatement();


In the mean while I researching about Db2 JDBC driver
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13843770
see if this helps...
see the name of jar file and driver name..
http://www-128.ibm.com/developerworks/db2/library/techarticle/0209hutchison/0209hutchison.html
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13843946
Also can you post the code for your stand alone jsp that was working??
0
 

Author Comment

by:mnphan
ID: 13846988
Sorry, for late reply, I need to do something urgent from work.

Here is the working code jsp:


<%@ page import = "java.util.*" %>
<%@ page import = "javax.sql.*" %>
<%@ page import = "java.sql.*" %>

<%
String url = "jdbc:db2://localhost:6789/sample";
//String url = "jdbc:db2:sample";
String user = "user";
String pwd = "pass";
String ibm_driver = "COM.ibm.db2.jdbc.net.DB2Driver";
String last = null, first = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String sql = null;

out.println("<html>\n<head>\n<title>" + "Student Names</title>" + "\n</head>\n<body>");

try
{
      sql = "select * from person";

      Class.forName(ibm_driver).newInstance();
      con = DriverManager.getConnection(url, user, pwd);

      stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery(sql);

      while (rs.next())
      {
            last = rs.getString("last");
            first = rs.getString("first");
            out.println(last + " " + first + "<br>");
      }
      rs.close();
      stmt.close();
      con.close();
}
catch (Exception e)
{
      out.println("<br>Can not get data from database<br>");
      out.println(e.toString());
}

out.println("</body>\n</html>");

%>



 
0
 

Author Comment

by:mnphan
ID: 13847237
1/ out.println(ds);
       org.apache.tomcat.dbcp.dbcp.BasicDataSource@b31b77


2/ From the working code jsp, I posted above:
This url = "jdbc:db2://localhost:6789/sample" is working,
but url = "jdbc:db2:sample" is not.


3/The IBM Driver: "COM.ibm.db2.jdbc.net.DB2Driver" is working
but "com.ibm.db2.jdbc.net.DB2Driver" is not.


mnphan
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13847893
Hi,

So by that jsp, your query is running and print out the result, am I right?

     while (rs.next())
     {
          last = rs.getString("last");
          first = rs.getString("first");
          out.println(last + " " + first + "<br>");
     }

Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13848437
Yes, that jsp code (i mean the stand alone jsp code that I posted on Date: 04/22/2005 02:48PM PDT) is working well, and the result is printed out.

But the new setup test page, from MyTest/test.jsp does not work
It 's getting error when it executes the line:

DataSource ds = (DataSource)envCtx.lookup("jdbc/TESTDB");

The error is:
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

and look inside the log file: I see some thing wrong about the driver or DataSource:
Caused by: java.sql.SQLException: No suitable driver
     at java.sql.DriverManager.getDriver(Unknown Source)
     at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:773)
...




0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13848471
mm...strange.

Let me check the IBM website.

Regards
Dave
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13848478
Hi,

Is your DB2 driver in your CLASSPATH?

Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13848601
In my ClassPath there are some IBM drivers:

.;
C:\PROGRA~1\IBM\SQLLIB\java\db2java.zip;
C:\PROGRA~1\IBM\SQLLIB\java\db2jcc.jar;
C:\PROGRA~1\IBM\SQLLIB\java\sqlj.zip;
C:\PROGRA~1\IBM\SQLLIB\bin;
C:\PROGRA~1\IBM\SQLLIB\java\common.jar;
0
 

Author Comment

by:mnphan
ID: 13850443
I tried the simple Java application that modified from

http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2a0/db2a0159.htm

and I got the output from database correctly.

Here is the modified java code:
import java.sql.*;
 
import java.sql.*;
 
class DB2Appl
{
  static
  {
    try
    {
      Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
    }
    catch (Exception e)
    {
      System.out.println(e);
    }
  }

  public static void main(String argv[])
  {
    Connection con = null;

    // URL is jdbc:db2:dbname
    String url = "jdbc:db2:sample";

    try
    {
      if (argv.length == 0)
      {
        // connect with default username/password
        con = DriverManager.getConnection(url);
      }
      else if (argv.length == 2)
      {
        String userid = argv[0];
        String passwd = argv[1];

        // connect with user-provided username and password
        con = DriverManager.getConnection(url, userid, passwd);
      }
      else
      {
        System.out.println("Usage: java DB2Appl [username password]");
        System.exit(0);
      }

      // retrieve data from the database
      System.out.println("\nRetrieve some data from the database...");
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * from person");

      System.out.println("\nReceived results:");

      // display the result set
      // rs.next() returns false when there are no more rows
      while (rs.next())
      {
        String first = rs.getString(1);
        String last = rs.getString(2);

        System.out.print("\nfirst = " + first);
        System.out.print("\nlast = " + last);
        System.out.print("");
      }

      rs.close();
      stmt.close();

      // update the database
      System.out.println("\nUpdate the database... ");
      stmt = con.createStatement();
      int rowsUpdated = stmt.executeUpdate("UPDATE person SET last = 'phanngoc' where lcase(last) = 'phan'");

      System.out.print("Changed "+rowsUpdated);

      if (1 == rowsUpdated)
        System.out.println(" row.");
      else
        System.out.println(" rows.");

      stmt.close();
      con.close();
    }
    catch( Exception e )
    {
      System.out.println(e);
    }
  }
}
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13852670
Hi,

Really....I am quite confused why can't you get the driver if you acccess it from DataSource. Now, which way do you use? Are you using DataSource in server.xml or you create another xml file?

Can you post your entire server.xml?

Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13852856
Yes I am very confused too,

I have these files:

1/ C:\tomcat5.5\conf\server.xml
      Link: http://phanngoc.com/conf.server.xml

2/ C:\tomcat5.5\conf\web.xml
      Link: http://phanngoc.com/conf.web.xml

3/ C:\tomcat5.5\conf\Catalina\localhost\MyTest.xml
      Link: http://phanngoc.com/conf.Catalina.localhost.MyTest.xml

4/ C:\tomcat5.5\webapps\MyTest\WEB-INF\web.xml
      Link: http://phanngoc.com/webapps.MyTest.WEB-INF.web.xml

5/ C:\tomcat5.5\webapps\MyTest\test.jsp (jsp test page that is NOT WORKING)
      Link: http://phanngoc.com/webapps.MyTest.test.jsp.html

6/ C:\tomcat5.5\webapps\ROOT\testDB.jsp (stand alone jsp test page that is WORKING)
      Link: http://phanngoc.com/webapps.ROOT.testDB.jsp.html

In CLASSPATH there are some IBM driver:
.;
C:\PROGRA~1\IBM\SQLLIB\java\db2java.zip;
C:\PROGRA~1\IBM\SQLLIB\java\db2jcc.jar;
C:\PROGRA~1\IBM\SQLLIB\java\sqlj.zip;
C:\PROGRA~1\IBM\SQLLIB\bin;
C:\PROGRA~1\IBM\SQLLIB\java\common.jar;

In addition, I have 2 drivers db2java.jar, db2jcc.jar (unsure these are correct drivers) that inside these folders:
a/ C:\tomcat5.5\common\lib\
b/ C:\tomcat5.5\webapps\MyTest\WEB-INF\lib\
c/ C:\tomcat5.5\webapps\ROOT\WEB-INF\lib\

When running the jsp test.jsp I get this output:

DataSource = org.apache.tomcat.dbcp.dbcp.BasicDataSource@650892
You are here
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13853014
I am not pretty sure but try this.

      Context initialContext = new InitialContext();
      DataSource datasource = (DataSource)initialContext.lookup("java:comp/env/jdbc/TESTDB");

      if (datasource != null)
      {
           System.out.println("Not null");
      }
      else
     {
           System.out.println("Null");
      }
      out.println("<BR>DataSource = " + ds);
      out.println("<br>You are here<br>");


Then checks out whether "Not Null" or "Null" is printed in your Tomcat console. I hope that works.

Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13853632
Hi Dave,

Modify your code a bit:

Context initialContext = new InitialContext();
DataSource datasource = (DataSource)initialContext.lookup("java:comp/env/jdbc/TESTDB");
if (datasource != null)
{
      out.println("<BR>DataSource = " + datasource);
}
else
{
      out.println("<BR>DataSource is Null");
}
out.println("<br>You are here<br>");
Connection con = datasource.getConnection();


Here is the 3-line output:

DataSource = org.apache.tomcat.dbcp.dbcp.BasicDataSource@1551b0
You are here
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

Thanks a lot
0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 1000 total points
ID: 13854696
in your localhost\MyTest.xml change the url to jdbc:db2:sample

for the url and see if it works ( that the url you were using in the test jsp which is working)
0
 
LVL 16

Assisted Solution

by:suprapto45
suprapto45 earned 1000 total points
ID: 13855911
Hi,

It means that actually the datasource is not null. mmm ????

Try to change the localhost into your ip address.

  <name>url</name>
  <value>jdbc:db2://localhost:6789/sample</value>

Regards
Dave
0
 

Author Comment

by:mnphan
ID: 13856307
Hi all,

I did try many times the url, none of these works:

jdbc:db2://localhost:6789/sample
jdbc:db2://localhost/sample
jdbc:db2:/sample
jdbc:db2://132.239.222.16:6789/sample
jdbc:db2://132.239.222.16/sample
...
and off of them get the same error when execute this line:
Connection con = datasource.getConnection();

Error:
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
0
 

Author Comment

by:mnphan
ID: 13869864
Hi Dave, hi Kuldeepchaturvedi

Even that the problem is not solved yet, but you both help me a lot. I guest you are running Tomcat 5.0 not 5.5 as mine, so the setting is a bit different. Let me try to install Tomcat 5.0 in another PC in my home and start setting up over and I belive it should be done.

So I stop this topic right here, and give the credit to both of you (I did double the points to 500 pts) and I am very, very thank you for all your help.

Hopefully if I have another problem in the future, you can help me again.

Again thank you so much.

Minh Phan
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13870005
Hey Minh,
Thanks for the point. Though I think we have not done justice with your problem.. but frankly speaking.. with given settings and the variations we have tried. It should have worked...
I do not have DB2 installed on my machine but I connect to 3 diff databases using these same settings and it connects every time.. If I will find some settings I will post them again..
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 13872274
Yes,

Thanks Minh Phan for the points. Yes, it should work anyway. Well, thanks and I hope that it runs properly.

Regards
Dave
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Social messanging services like WhatsApp and Facebook can help businesses in ways that many owners don't even imagine, giving new opportunities to connect with customers. Discover some of the most innovative things they can do for your company.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

850 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