connection pool problem using in Tomcat

I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000 under
Tomcat App server.
I wish to control the number of physical connections made to the database to
50. Can somebody guide me about this.
At the moment even with as low as 30 concurrent hits we see a very high
number of phyiscal connection to the database as high as 200.
I am using the following settings in SERVER.XML
<Resource name="jdbc/ECTDB" auth="Container"
type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
<ResourceParams name="jdbc/ECTDB">
<parameter><name>maxActive</name><value>30</value></parameter>
<parameter><name>maxIdle</name><value>20</value></parameter>
<parameter><name>maxWait</name><value>10000</value></parameter>
<parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe rDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.SQLServe rDataSource</value></parameter>
<parameter><name>user</name><value>..</value></parameter>
<parameter><name>password</name><value>..</value></parameter>
<parameter><name>driverName</name><value>SQLServer</value></parameter>
<parameter><name>serverName</name><value>127.0.0.1</value></parameter>
<parameter><name>portNumber</name><value>1433</value></parameter>
<parameter><name>databaseName</name><value>..</value></parameter>
<parameter><name>selectMethod</name><value>cursor</value></parameter>
<parameter><name>loginTimeout</name><value>60</value></parameter>
</ResourceParams>
Thanks a lot
| Thread-Topic: connection pool problem using in Tomcat
| thread-index: AcSMuy5kABS/93PSRZ6MtRi1273r1w==
| X-WBNR-Posting-Host: 202.134.106.86
| From: "=?Utf-8?B?a2FpY2FsbA==?=" <kaicall@.discussions.microsoft.com>
| Subject: connection pool problem using in Tomcat
| Date: Fri, 27 Aug 2004 21:55:03 -0700
| Lines: 30
| Message-ID: <22AD57F5-91A6-48C5-81BF-F9B84365D03F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6282
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000
under
| Tomcat App server.
|
| I wish to control the number of physical connections made to the database
to
| 50. Can somebody guide me about this.
|
| At the moment even with as low as 30 concurrent hits we see a very high
| number of phyiscal connection to the database as high as 200.
| I am using the following settings in SERVER.XML
| <Resource name="jdbc/ECTDB" auth="Container"
| type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
| <ResourceParams name="jdbc/ECTDB">
| <parameter><name>maxActive</name><value>30</value></parameter>
| <parameter><name>maxIdle</name><value>20</value></parameter>
| <parameter><name>maxWait</name><value>10000</value></parameter>
<parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe
rDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.
SQLServerDataSource</value></parameter>
| <parameter><name>user</name><value>..</value></parameter>
| <parameter><name>password</name><value>..</value></parameter>
| <parameter><name>driverName</name><value>SQLServer</value></parameter>
| <parameter><name>serverName</name><value>127.0.0.1</value></parameter>
| <parameter><name>portNumber</name><value>1433</value></parameter>
| <parameter><name>databaseName</name><value>..</value></parameter>
| <parameter><name>selectMethod</name><value>cursor</value></parameter>
| <parameter><name>loginTimeout</name><value>60</value></parameter>
| </ResourceParams>
|
|
| Thanks a lot
|
|
If you are using SelectMethod=direct, then it is possible that you are
seeing an increase in connections due to the creation of cloned
connections. These additional connections are necessary because each
statement requires its own connection. Your Tomcat settings have no
bearing on this particular behavior, as it is inherent in the JDBC driver
itself.
Are you using transactions in your code? Can you provide code that
reproduces the problem behavior?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb Simien ,
Yes, I use connection in my code and use a following function for getting
conncection.
public static Connection getConnection() throws Exception {
Connection dbConnection = null;
InitialContext ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
ConnectionPoolDataSource ds =
(ConnectionPoolDataSource) envCtx.lookup("jdbc/ECTDB");
Connection conn = ds.getPooledConnection().getConnection();
conn.setAutoCommit(false);
return conn;
}
Is this funciton cause the problem?
Thanks a lot.
kaicall
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: connection pool problem using in Tomcat
> | thread-index: AcSMuy5kABS/93PSRZ6MtRi1273r1w==
> | X-WBNR-Posting-Host: 202.134.106.86
> | From: "=?Utf-8?B?a2FpY2FsbA==?=" <kaicall@.discussions.microsoft.com>
> | Subject: connection pool problem using in Tomcat
> | Date: Fri, 27 Aug 2004 21:55:03 -0700
> | Lines: 30
> | Message-ID: <22AD57F5-91A6-48C5-81BF-F9B84365D03F@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6282
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000
> under
> | Tomcat App server.
> |
> | I wish to control the number of physical connections made to the database
> to
> | 50. Can somebody guide me about this.
> |
> | At the moment even with as low as 30 concurrent hits we see a very high
> | number of phyiscal connection to the database as high as 200.
> | I am using the following settings in SERVER.XML
> | <Resource name="jdbc/ECTDB" auth="Container"
> | type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
> | <ResourceParams name="jdbc/ECTDB">
> | <parameter><name>maxActive</name><value>30</value></parameter>
> | <parameter><name>maxIdle</name><value>20</value></parameter>
> | <parameter><name>maxWait</name><value>10000</value></parameter>
> <parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe
> rDataSourceFactory</value></parameter>
> <parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.
> SQLServerDataSource</value></parameter>
> | <parameter><name>user</name><value>..</value></parameter>
> | <parameter><name>password</name><value>..</value></parameter>
> | <parameter><name>driverName</name><value>SQLServer</value></parameter>
> | <parameter><name>serverName</name><value>127.0.0.1</value></parameter>
> | <parameter><name>portNumber</name><value>1433</value></parameter>
> | <parameter><name>databaseName</name><value>..</value></parameter>
> | <parameter><name>selectMethod</name><value>cursor</value></parameter>
> | <parameter><name>loginTimeout</name><value>60</value></parameter>
> | </ResourceParams>
> |
> |
> | Thanks a lot
> |
> |
> If you are using SelectMethod=direct, then it is possible that you are
> seeing an increase in connections due to the creation of cloned
> connections. These additional connections are necessary because each
> statement requires its own connection. Your Tomcat settings have no
> bearing on this particular behavior, as it is inherent in the JDBC driver
> itself.
> Are you using transactions in your code? Can you provide code that
> reproduces the problem behavior?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<zzuzMF7jEHA.2516@.cpmsftngxa10.phx.gbl>...
> If you are using SelectMethod=direct, then it is possible that you are
> seeing an increase in connections due to the creation of cloned
> connections. These additional connections are necessary because each
> statement requires its own connection. Your Tomcat settings have no
> bearing on this particular behavior, as it is inherent in the JDBC driver
> itself.
> Are you using transactions in your code? Can you provide code that
> reproduces the problem behavior?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
This is a little scary isn't it? If I have a J2EE app that starts out
pre-preparing (for subsequent re-use) N prepared statements per
connection and I have M concurrent clients using the app then we'll
require N*M physical database connections. Is that correct? That
number could be pretty huge for even a mid size app..
Is there any plans to address this in any future driver?
Grant M
|||grantmitchell7@.yahoo.com wrote:

> CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<zzuzMF7jEHA.2516@.cpmsftngxa10.phx.gbl>...
>
> This is a little scary isn't it? If I have a J2EE app that starts out
> pre-preparing (for subsequent re-use) N prepared statements per
> connection and I have M concurrent clients using the app then we'll
> require N*M physical database connections. Is that correct? That
> number could be pretty huge for even a mid size app..
Not to mention if you plan to do anything transactionally, or use temp tables,
which would be limited to one real connection, and not involve all the
under-the-covers connections involved in one logical connection.
I am confident that an upcoming version of the free MS driver will
address this. In the mean time you have the option of using commercial
drivers, or ensuring you run with selectMethod=cursor. As I recall, the
original poster actually already showed he was running with selectMethod=cursor...
Joe Weinstein at BEA
> Is there any plans to address this in any future driver?
> Grant M
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<4136035E.9000108@.bea.com>...[vbcol=seagreen]
> grantmitchell7@.yahoo.com wrote:
>
> Not to mention if you plan to do anything transactionally, or use temp tables,
> which would be limited to one real connection, and not involve all the
> under-the-covers connections involved in one logical connection.
> I am confident that an upcoming version of the free MS driver will
> address this. In the mean time you have the option of using commercial
> drivers, or ensuring you run with selectMethod=cursor. As I recall, the
> original poster actually already showed he was running with selectMethod=cursor...
> Joe Weinstein at BEA
Yes the transactional problem is alarming.. Does the driver transact
all the cloned connections for statement when the original connection
is transacted? Who knows but I suppose a test would be relativly
quick.
In regards to selectMethod=cursor - our tests show that result sets
generated with server side cursors can be up to 8 times more expensive
on the database (and slower) than forward only, 'client side' result
sets (depending on the characteristics of the cursor). So instead of
having N*M connections we might end up with N*M server side cursors.
I'm not sure which is worse..
Therefore I would argue that server cursor based result sets are a
major obstacle to scalability and should be avoided at all costs
(except possibly for singleton processes that need a table snapshot
like a report for example).
I hope you are right that an upcoming release resolves this although I
remain skeptical since my guesss is that changing this behavior would
mean a major structural change in the driver's architecture.
On this newsgroup there has been recent reference by Microsoft to a
"new" JDBC driver for SQL 2005. However when I tried to get more
details on this driver there was no response. Perhaps someone from
Microsoft could comment on the "new" driver, and specifically comment
on whether it resolve this serious issue..
Grant

Connection Error

Hi friends ! , you may help me with this...

I have a report made with CR 8.0 that I call from VB6 application with a CrystalReport Control. When I execute myCrystalReport.Action = 0, an error raises: error number 20536, description: not possible to begin session in the server.
The report takes the data from a table in a Sql Server.

This is the code:

CR.ReportFileName = gstrPathReports & "\People.rpt"
CR.Action = 0

I also tried with:

CR.LogOnServer "P2SSQL.DLL", gstrCfgServerName & "\" & gstrCfgInstanceName, _
gstrCfgDBName, "sa", "hello"

but it still odesn't work...

Thanks for your help !Hi try the following
CR.ReportFileName = gstrPathReports & "\People.rpt"
CR.Action = 1
Madhivanan|||Now it says: "Cannot open the sql server"

Any other idea?

By the way, what's the difference between 0 and 1 in the Action?

Thanks !!!|||so its the problem of connection..not the printing.
Have you tried showing the report like Report.Show without passing the parameters for printing?
The problem with the connection may be due that, you are not passing correct user name and password in the parameters.
And secondly that the report ypu are not setting database location.

You can check for parameters in the sql server profiler, if the query is fine and checking the query in the query analyzer.|||My CrystalReport control doesn't have the Show method...

The password and username are ok, and the database location is also ok, because when I open the report from Seagate Crystal Report 8.0 it works fine.

The report doesn't have parameters, because it just shows columns from a table.

That's why I tried with LogOnServer, because the problem seems to be that I can't open the connection from the VB6 application when I invoke the report, but still doesn't work...

Thank you for any ideas !|||does your report have subreports?|||No, it doesn't...|||Hi vbUsername, check whether gstrCfgServerName & "\" & gstrCfgInstanceName gives correct server name that you are using.

Madhivanan|||Yes, they are ok, as well as the DBName, UID and PWD...

Thank you for your concern !
I just cannot make it work, and it's such a simple report !