Monitoring JDBC Connection and Adapter 6x

smk1 · October 13, 2005, 5:10pm

Hi,

I want to monitor JDBC connection and adapter.
I have observed even if oracle is down, the connection status is shown enabled in WmBrokerAdmin. We have to disable and enable it again to get the error response. I tried services in WmART to check if any service gives me the real time response but invain.

Can anyone suggest me how to get the real time response. I think we need to write a generic service to poll all the JDBC connections every unit of time.

Need suggestions for the same.

regards,
Sumit

saurabhm · October 14, 2005, 12:50am

what db’s you are specifically looking to monitor?

there is a utility in shareware section called “ResetJDBCConnections”
Here is link: [url=“wmusers.com”]wmusers.com

Check this to see if you get the idea to/how-to build per your requirement.

let me know if you have any more questions

smk1 · October 14, 2005, 10:34am

Hi Saurabh,

i have configured JDBC connections for Oracle.
But it might be anyone of the supported DBs.

I had checked 2 functions in WmArt :

WmART/wm.art.admin.connection:setResourceState
WmART/wm.art.admin.connection:getResourceState

But the problem with them is when connections are enabled they will not reflect the status even if oracle goes down or jdbc package is disabled.
Only if you disable and try to enable them again the error response comes.

I will check the shareware utility and update you back.

Thanks for the help.

regards,
Sumit

smk1 · October 14, 2005, 11:30am

Hi Saurabh,

I tried the shareware but its again using the same function setResourceState.

Using setResourceState can solve the problem but for monitoring is it recommended that we disable the connection and re-enable it. In production environment it will have an impact.

Isn’t there any other way to do so.

regards,
Sumit

saurabhm · October 14, 2005, 8:04pm

Sumit,
You are right on that demerit. The way I resolved it, is by a combination of ResetJDBCConnection and FindConnectionAsBroken Svc.

I have been using this set of services to monitor all my jdbc connections for past year.

Here is the brief gist of the solution: (I will upload a package if I find sufficient time to do so in the next week)

Step 1:
Create a Adpt Svc UniversalSelectStatement (for me, it goes like “select count(0) from systypes” - this works for Db2 and SQL Server. Check to see if it works for Oracle also or not. If you are only using Oracle, try a simpler statement as “select sysdate from dual”)

Step 2:
Create a Svc MonitorJDBCConnectionsHealth which iterates over all the adapter connections created (using listAllResources)

In this Svc, loop over all the connections to get the current state

if Enabled:
run the UniversalSelectStatement svc and see if any errors occur;
if errors occur, implies something wrong with the connection; set it to disabled state
if no errors, exit flow
if disabled:
try to set the connection to enabled state;
if error, retry after a time delay
if no error, PROBLEM SOLVED

Remember to be able to run the UniversalSelectStatement on different adapter connections, you would use the setAdapterServiceNodeConnection service.

Once completely developed, set MonitorJDBCConnectionsHealth on scheduler for 15 minutes.

Hope this clarifies the solution for you a little bit. Remember to test it extensively before putting any solution in production environment.

Let me know if you have any questions.

Cheers,
Saurabh

gupta_r.17495 · October 14, 2005, 8:20pm

Thanks saurabh for sharing the solution,it helps for everyone…

Ramesh_Sambandan · October 14, 2005, 8:52pm

Saurabh,

It would be great if you can upload the package at your leisure time.
Not clear a little bit on “Remember to be able to run the UniversalSelectStatement on different adapter connections, you would use the setAdapterServiceNodeConnection service”.

Thanks
ramesh.

saurabhm · October 14, 2005, 9:17pm

Remember to be able to run the UniversalSelectStatement on different adapter connections, you would use the setAdapterServiceNodeConnection service.

Explanation:
when you create any new adapter service, you associate it with a particular adapter connection. When you run that service, it runs for that pre-established connection at design time.
before v6.1, you were not allowed to change adapter connection associated with the adapter service.
after v6.1, you can use setAdapterServiceNodeConnection, to change the underlying adapter connection for any adapter service.

Hope this clarifies the statement.

Cheers,
Saurabh

smk1 · October 17, 2005, 10:20am

Thanks Saurabh, i will try the same way.

For Oracle : select count(0) from dual is working fine.
I want to make a generic service, i will have to check everytime for which DB the connection is made and then run the appropriate query.

I will update you about the progress.

Thanks & regards,
Sumit

saurabhm · October 17, 2005, 10:35pm

Great!! It seems you are on the right track.

Keep posted and let me know if you need any help.

Cheers,
Saurabh

sriram1 · October 19, 2005, 4:00pm

Hi,

To find whether the JDBCadapter is connected to oracle database -

“getResourceConfiguration” service is invoked to get the details of the url.

i.e servername, port number and SID, and also the username and password.

I wrote a generic java service using a function "isConnected” in which we pass these parameters (url, userid, password).

url - “jdbc:oracle:thin:@serverName:portNumber:SID”

driver - “oracle.jdbc.driver.OracleDriver”

when password is null and oracle database is UP the following exception is thrown-

java.sql.SQLExceptionRA-01005:null password given; logon denied.
when password is null and oracle database is DOWN the following exception is thrown-

java.sql.SQLException:Io exception:Connection refused

By catching these exceptions the status of the adapter, ie whether it is connected to the oracle database can be known.

Algorithm:

             try{ 
 
  
 
                        Class.forName("oracle.jdbc.driver.OracleDriver"); 
 
                        String url="jdbc:oracle:thin:@"+serverName+":"portNumber+":"+SIDName; 
 
                        Connection con = DriverManager.getConnection(url,userName," "); 
 
            }catch(Exception e) { 
 
                         
 
                        String exValue=e.toString(); 
 
  
 
                        /*ORA-01005 - Exception thrown when password is null and the Database is UP */ 
 
  
 
                        if(exValue.indexOf("ORA-01005") > 0)         
 
                        { 
 
                                    System.out.println("Exception "  + exValue); 
 
                                    System.out.println("Oracle Connected");               
 
                        } 
 
                        else 
 
                        { 
 
                                       System.out.println("Oracle Not Connected"); 
 
                        }                        
 
}

The algorithm was implemented using java client; same can be implemented using flow/java service.

If we can decrypt the password then we can get the perfect solution. Can anyone help me to decrypt the password.

Let me know if am going in right direction.

ameyec · October 26, 2005, 7:45pm

I was looking to decrypt the password to, but until now, no results.

No results there yet from your side?

paulykonikara · May 10, 2006, 12:14am

Saurab,

Could you please upload the package.

Thanks,
Pauly

saurabhm · May 19, 2006, 1:26am

Pauly,
which package you talking abt?

ResetJDBCConnection can be downloaded from the shareware section of wMUsers.

Saurabh

smk1 · July 19, 2006, 8:24pm

hey we can decrypt the password using “StringUtil.decString(pass)”. Just create a dependency on WmART package.

gany1102 · August 7, 2006, 11:39am

Hello every one,

Wondering! Have you guys found any better way to monitor the database connections (It’s alive or not) …

I have noticed an issue with one of the client where I am working now, the database connections lost (not restored) as a result (database was down and restarted etc)… Whatever, the reason… finally, Database become available and webMethods server still up and running… but, connection refused… Simple solution was disable and enable the connection…

What is the better(best) way to handle this issue… Please share your experience…

Thanks in advance.
Gany

smk1 · August 9, 2006, 11:36pm

Hi,

Set the minimumpool to “0” this avoids stale DB connections.
We followed the algorithm described above for monitoring (Although this has to be deloyed fully in real time)

Let you know once we give a go.

regards,
Sumit

arydewo · August 17, 2006, 3:11pm

Well, you may need to patch your IS with latest fix regarding the issue. This JDBC issue is quite a lot occured on system with a extremely high load.
Please check and download from webMethods Advantage.

Regards,
arydewo