Oracle DB Connectivity Check

, ,
1

Hi Forum,

I am intending to make a service that would check an existing Oracle DB listed in the JDBC Adapter connection’s page that would give me a status of the oracle database if it is ready and available for use or is showing some kind of connectivity problem or error.

I have gone through the services listed in the WmJDBC Package and i am not finding any service that i can use to do this for me. Any ideas would be welcomed on the same.

thanks in advance,
nightfox.

2

You can use WmDB package service: pub.db:connect and test the connection. If you are not using this package, then you have to write a java service that connects to the DB using the DB url, user and password.

Cheers
Guna

3

hi guna,

I have already done that solution using WmDB package but i wanted to implement the same using WmJDBCAdapter, also i have another question in this regard how is Oracle directly always related to WmJDBCAdapter package. Is there any complication in using services in the WmDB package vias vis WmJDBCAdapter to perform DB related operations…?

thanks,
nightfox

4

Have you tried following services:

WmART/pub.art.connection:queryConnectionState
WmART/pub.art.connection:getConnectionStatistics

5

Don’t use WmDB.
Don’t use Java.

If you want to test a DB connection, create a JDBC adapter service and do a simple “select sysdate from dual”. Call that service from another service X which perhaps sends an e-mail if the JDBC service fails. Schedule a task to call service X at the interval you’d like.

There are no issues using WmDB and the JDBC Adapter at the same time. There are a variety of things about WmDB that make it more difficult to work with than the JDBC adapter (ensuring the releasing of connections for example) and it will eventually go away.

Oracle isn’t “always related to WmJDBCAdapter package.” The adapter can use any number of database types, as long as you have the correct JDBC libraries in the classpath for the DB types you want to use.

6

Thanks Rob, i was planning to the same thing about it by using the Dual table, but was wondering if there were any alternatives of doing the same thing in a more formalized way. i am also trying the possibility of using the WmART services so let me just see what i can use to get this thing sorted…! will keep this thread updated with my findings.!

Thanks guys.!

Any other bright ideas are always welcomed.

reg,
nightfox

7

You won’t be able to tell if the database is accessible or not by the JDBC Adapter Services other than calling an Adapter Service (e.g. select statement like reamon suggested). The pub.art.connection:queryConnectionState will not give you any hint of the current status of the database. It’s intended to present the state of the configured connection in the Adapter.

You should have some kind of config file where you configure the backend scheduled downtimes within your services so that you can at least handle those ones.

8

Another idea is to not use IS for monitoring DB availability at all and instead use the typical infrastructure monitoring tools to do that.

9

Rob and ahuebenettkn,

Your replies are insightful. After doing some testing yesterday, I found that calling an adapter service is a good approach to test the DB availability. Thanks.

Cheers
Guna

10

Also, if it helps,
I have used the same approach in the past(on a 6.5 IS) with the following logic.

  1. Create a dynamicSQL JDBC adapter service which takes the whole query.
  2. Create a Flow service called ping, get the connection properties, and based on database type(using Datasource classname), issue the corresponding ping query.
  3. The query returns 1 if it can connect.

Here are the ping queries I used for some databases.
AS400JDBCDataSourcePingQuery: SELECT 1 FROM SYSIBM.SYSDUMMY1
IfxConnectionPoolDataSourcePingQuery SELECT 1 FROM SysTables WHERE TabID = 1
OracleConnectionPoolDataSourcePingQuery SELECT 1 FROM DUAL
SQLServerDataSourcePingQuery SELECT 1

11

Hi Sekay,

I had planned something similar myself in terms of a STATUS FALG being set to 1/0 depending on the availaibilty of the database server to check its availibility. So i guess we have somekimda solution in place.

Hi reamon,

I agree to your point as well of having a third party tool to get this database connectivity solution in place, but my problem is that within webMethods my handlers for a particular db instance are getting expended there by leaving no handlers left for future connecions. So it is by far essential that i have some kind of a reporting mechanism in place.

Thanks for you views guys…
Hope this thread helps people in the community…

reg,
nightfox