Test connection on each node for cell scope datasource

Test connection on each node for cell scope datasource


I am sure pretty much every WAS administrator has used the "test connection" button in the WAS console to prove a JDBC datasource has been set up correctly.

Although not a problem, something that always got me and didn't seem to be as good as it could be, is the fact that if you work in a large scale enironment you may well end up setting the datasource at a cell level as this would cut down on the time it takes to set up a datasource on each node or appserver and also redude the likelihood of something being mis typed. Hoever, if you do this and then run test connection, the connection is just from the dmgr as you can see by lookiig in the dmgr logs.


So what happens if you have 10, 20 or more nodes and you want to make sure that all of them can connect correctly to the database. You could telnet from each box to the DB server on the correct port, but that just shows network connectivity rather than a full databsee connection.

I assumed this could be done in a jython script but it took me a day or 2 to figure this out. If I connected to the nodeagent in wsadmin, I couldn't get the config details of the datasources as these are accessed from a wsadmin session connected to the dmgr. But running a test connection when in a wsadmin session connected to the dmgr just does the same as the "test connection" through the admin console.

In the end, I managed to write a simple unix script, which does the following:

1. Open a wsadmin session to the dmgr to get the datasource ids and write these to a file, passing in the name of the cell

2. open a wsadmin session to each nodeagent, read the datasource id's from the file, then run a test connection.


This is the basic unix script:

###########################################################################

CELL=epwsdr21Cell

NODES="epwsdr21 epwsdr22 epwsdr23 epwsdr24 epbtdr21"

PORT=8878

echo "Running connection from each node to the datasources in WAS"

echo "Connecting to dmgr through wsadmin....."


# Connect to the dmgr through wsadmin - pass in the name of the cell - and run scropt dsconnect.py


/usr/was6/WebSphere/AppServer/bin/wsadmin.sh -lang jython -f ./jython/dsconnect.py $CELL


echo "Connecting to each nodeagent to run test connections....."


for node in $NODES;do

echo "Connecting to ${node} on port ${PORT} through wsadmin"



# Connect to each nodeagent in my list of nodes above - and run script dsconnect2.py


/usr/was6/WebSphere/AppServer/bin/wsadmin.sh -lang jython -conntype SOAP -host $node -port $PORT -f ./jython/dsconnect2.py $node


done

echo 'Complete '

#############################################################################

And here is what is in the first jython script - dsconnect.py


# Jython script to get the datasource id's once connected to the dmgr through wsadmin

import sys

print ' '

print "Getting datasources for cell " + sys.argv[0]

# First build the cell name we are interested in fromm the cell name passed from the main script

constructcell ="/Cell:" + sys.argv[0] + "/"

# get the cell id

cellid = AdminConfig.getid( constructcell )

# Get the datasource id's

print 'Datasources found are listed below:'


# In this instance I am after v4 datasources for v5 datasources use "dsid = AdminConfig.list("DataSource", cellid).splitlines()"

dsid = AdminConfig.list("WAS40DataSource", cellid).splitlines()


print dsid


# Now open a tmp file and write the list of dsid's to tfe file - this will be a string rather than a jython list


f=open('/tmp/dsconnect.out','w')

s=str(dsid)


f.write(s)

f.close()


##############################################################################

So the list of ids is written to /tmp/dsconnect.out, now the second script is called for each nodeagent I want to connect to and then run a test connection. The list that has been put into a file will be seen as a jython string rather than a list which is why I use the eval statment so it goes back into string format

# Jython script to run a test connection on a list of datasources

import sys
#

# Open temp file to get string of datasource ids and assign to a jython list

f=open('/tmp/dsconnect.out')

test=f.read()

dsids=eval(test)

f.close()


for ds in dsids:

print ' '

print 'Testing connection from ' + sys.argv[0] + ' to ' + ds

try:

outp=AdminControl.testConnection(ds)

except:

print 'Error connecting to datasource'


print outp

else:
print outp


##############################################################################

Deploying a data access application in websphere (jdbc access)

Steps involved in this are:



•Create a JDBC source

•Create a J2C alias

•Create a Data source

•Deploy Application

Creating a JDBC Provider

The JDBC provider object provides the actual JDBC driver implementation class for access to a specific database type, that is, Oracle, SQL Server, DB/2 etc. We associate a data source with a JDBC provider and data source provides the connection to the database.



•In the administration console

Resources -> JDBC -> JDBC Provider



•Here we need to select the scope, at which the jdbc provider will be available like CELL/NODE etc. Scope specifies the level at which the resource definition is visible





•Then click new

•select database type, Provider, Implementation type and Name



•Click Next to go to the database classpath screen



•Click next, shows the summery of your respones. then click finish to create your new jdbc provider

Creating J2C Alias

If a database has security enabled, we need to provide the username and password for the connection. By creating a J2C alias, we can create an authentication resource independent from the provider and data source



•In the administration console

security -> global security -> Authentication ->Java Authentication and Authorization Service ->J2C authentication data



•on the next screen provide, Alias name, DB user name and password

•and then apply->Save

Creating Data Source

•In the administration console

Resources -> JDBC -> DataSource



•Select the scope

•On the next page, be asked to fill out the data source information



•Enter the value for Data source name field and JNDI name field. and proceed to next page



•Select the JDBC provider that we created, using the above steps

•Click Next, and in the next screen…enter the database URL field and click next

•here, you have option to select the authentication for resource type

•Component-managed Use when the resource configured in the EJB’s deployment descriptor res-auth property is set to Application.

•Container-managed Use when the resource configured in the EJB’s deployment descriptor res-auth property is set to Container.

•Select the appropriate value and click next to see the summery of your responses.

•Once they are confirmed, click finish to create the data source.

Deploying application (you can refere previous post also for details)



•Goto Applications ->install new application

•select enterprise application and browse the archive (EAR) file

•Select the install method

•Map the modules to servers

•choose the deployment options (detailed was chosen here to show all the options)

•Map shared libraries, if required

•Map resource references to resources

•Mapping virtual hosts for web modules (by default, default_host will be there)

•Map the context root for the application

•Review all the steps and options you provided

•and finish the application deployment

•start the application and test it


http://josephamrithraj.wordpress.com/2010/02/05/656/