JDBC Adapter602 CustomSQL problem

, ,
1

Can somebody help to resolve this issue???

Thanks,

2

shooting in the dark…
why dont you list what is the nature of the customsql query you running…
also, did u try increasing the maximum pool size…
also check to see if there are any restrictions on the DB itself to restrict number of connections from a particular server??

hope above leads to the issue.

3

I would change the transaction type to LOCAL_TRANSACTION. Do not use the start/commit/rollback functions in WmART. It will behave the same as NO TRANSACTION. See if the behavior changes. You will need to recreate the adapter service.

HTH

Ray

4

Are there any errors bein captured in your database logs? Also, why set your min connections to 0?

Can you tell from your DBMS management tools how many connections you are actually using when this error occurs?

-mdc

5

Mark,

The reason setting min connections to 0 is it will create new connection from the pool when ever it needs.Errors are showing in the Error,server logs.

This error happens only for CustomSQL adapter services only.

Sure i will check the DBMS management tools and will let you know.

Ray,

We set NO_Transaction since we are using this connection for select queries only and we have another one LOCAL_TRANSACTION for insert,updates…which are connectiong to same resource.

Saurabh,

The nature of the query is just custom select SQL and it will pulls the data from couple of OracleApps tables.

currently the maximum pool size set to 50.so i dont think this can be issue.

Thanks for all your kind responses and glowing light on this issue.

RMG,

6

Hello,
We are having exactly the same problem. Strange thing is that we have a identical environment where the problem does not occur. An urgent help will be appericiated.
Our enviroment is :
Environment:
webMethods:

Product webMethods Integration Server
Version 6.0.1
Updates IS_6-0-1_SP2_Fix86
IS_6-0-1_SP2_Fix73
IS_6-0-1_SP2
IS_6-0-1_SP2_Fix118

Build Number 425

JDBC Adapter:

Description JDBC Adapter
Adapter Version 6.0.2 Release Notes
JCA Spec Version 1.0
Vendor Name webMethods

Java Version:
Java Version 1.3.1 (46.0)
Java Vendor IBM Corporation
Java Home /usr/java130/jre

Oracle Version:
Oracle 8i (8.1.7)

OS version:
AIX 5.1

Connection Pool Settings:
Transaction Type NO_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
serverName localhost
user xxxx
password ******
databaseName xxxx
portNumber 1521
networkProtocol
Other Properties driverType=thin
Connection Management Properties
Enable Connection Pooling true
Minimum Pool Size 1
Maximum Pool Size 50
Pool Increment Size 1
Block Timeout (msec) 1000
Expire Timeout (msec) 1000

…

7

interesting…

RMG, did you try the solution suggested by Ray? (changing the transaction type to local_transaction) - at least this way, you can nail if it has anything to do with transaction type setting on the JDBC adapter.

RMG/Ali Khan, what does webMethods support has to say on this? did you guys open the ticket? I know this route is difficult as the problem is intermittent.

If it is not production instance, you may want to increase the log level, to get more debug info.

RMG, the only good news is that you are not alone facing the problem :wink:

8

We still have the problem. We have opened the ticket with webMethods support. However we did not get anything useful. Further investigation has resulted in following information.

  1. On the Oracle we notice the “INACTIVE” connections and OPEN CURSORS.
  2. We tried the LOCAL TRANSACTION but it then threw error “TRANSACTION IS ACTIVE”.
  3. The same code is working fine on other server which has identical configuration. There is no problem on that server.
  4. Fact is that JDBC adapter is not closing the connections. We tried using both OCI and thin drivers.

RMG do you any further info?
Thanks
Ali

9

Is there a fix for this issue?Kindly throw some light. I am facing the same issue.

10

I am also facing exactly the same problem. I want to know if webMethods provided some solution to this problem.

11

Hi All,

I am trying to run a Custom sql query in WM 6x where i provide input as the whole sql statement. I put ${sql} in the SQL tab and sql as the input field as this was followed in WM 4x.
But i get the following error while running the Adapter service:
“[ADA.1.316] Cannot execute the SQL statement “${sql}”. "
(17034) Non supported SQL92 token at position: 5: sql”
Non supported SQL92 token at position: 5: sql"
Please suggest me that how can i provide the whole sql statement as the input in the Custom Sql query.

Thanks
Natasha

12

I have used the DynamicSQL template to do this several times. The trick seemed to be NOT using any input fields like you would think. Just put in ${sql} for the SQL and enough output fields to handle the columns you are going to retrieve. I have only tried this with NO_TRANSACTION adapter connections. Have you run into a case where you need to use CustomSQL instead of DynamicSQL?

13

hi,
Thanks it worked!!!

Natasha