Configuring a Generic Datasource

Configuring the datasource is one time activity for any project in production environment but where as for development or testing environment there is always need for change the datasource as per the demand. These changes makes interrupt majority of the development process. To improve this process we can have a Generic JDBC Data source configuring script in your utilities space.

Creating the data source using a python script makes 100% reusable. Here my perception is that if we don’t hard code the JDBC parameters it will be easy to use for all environments as well as for any kinds of database platforms and their respective driver param values to replace also.

Initially lets go with single Data source creation with targeting to the user input Server it can be Managed Server or AdminServer (basic domain), later on we can go for improve further to target to a Cluster.

JDBC Datasource in offline vs Online WLST

This scripting we can write in two ways offline and in online. In the offile mode we need to navigate the MBean and create, configure parameter and finally do assign() them to a Server or Cluster of a domain. For the online script we need to set the target using setTarget() command. To do this we must connect to the Admin server and acquire lock on configuration repository by edit() or startEdit() commands.

assign('JDBCSystemResource', ‘myds', 'Target', demoCluster')
# -- or --
assign('JDBCSystemResource', ‘myds', 'Target', demoServer')

JDBCSystemResourceMBean tree

Oracle WebLogic supports jDriver for various DBMS. And also WebLogic supports XA drivers for distributed transactions. Supporting Third-Party Drivers also available from DBMS vendors. Note the following are the WebLogic supports Drivers for DBMS:

Cloudscape DB2 PostgreSQL
Oracle  Ms SQL Progress
MySQL  PointBase Sybase

Configuring new data source custom properties
Now let us try to configure a new data source with a custom properties for Connection Pool parameters, weblogic console connecting parameters into a same file or you can specify with different properties files. When the properties file is used it must be loaded before first line of processing statement in the script. We have two options to load the properties one is using command line and other one is using loadProperties() method.

###################****##############****########################
# Generic script applicable on any Operating Environments (Unix, Windows)
# ScriptName    : ConfigDS.py
# Properties    : ConfigDS.properties
# Author        : Srikanth Panda
# Updated by    : Pavan Devarakonda
###############     Connecting to Start     #################################
def connectAdmin() :
 try:
  connect(CONUSR,CONPWD, CONURL)
  print('Successfully connected')
 except:
  print 'Unable to find admin server...'
  exit()
################### Configuring Connection Pool #############################
def connPool(DSnam) :
 DRVPARM='/JDBCSystemResources/'+DSnam+'/JDBCResource/'+DSnam+'/JDBCDriverParams/'+DSnam
 cd(DRVPARM)
 set('Url',DBURL)
 set('DriverName',DBDRV)
 set('Password',DBPASS)
 cd(DRVPARM+'/Properties/'+DSnam)
 cmo.createProperty('user')
 cd(DRVPARM+'/Properties/'+DSnam+'/Properties/user')
 set('Value',DBUSR)

############         Creating Data source    ###############################
def createDS() :
 print('Naming the datasource')
 DSnam = DSName
 cmo.createJDBCSystemResource(DSnam)
 RESOURCE='/JDBCSystemResources/'+DSnam+'/JDBCResource/'+DSnam
 cd(RESOURCE)
 set('Name',DSnam)

 #Setting JNDI name
 cd(RESOURCE+'/JDBCDataSourceParams/'+DSnam)
 print RESOURCE+'/JDBCDataSourceParams/'+DSnam
 set('JNDINames',jarray.array([String(DSnam)], String))

 connPool(DSnam)

 #Set Connection Pool specific parameters
 cd(RESOURCE+'/JDBCConnectionPoolParams/'+DSnam)
 cmo.setTestConnectionsOnReserve(true)
 cmo.setTestTableName('SQL SELECT 1 FROM DUAL')
 cmo.setConnectionReserveTimeoutSeconds(25)
 cmo.setMaxCapacity(15)
 cmo.setConnectionReserveTimeoutSeconds(10)
 cmo.setTestFrequencySeconds(120)

 cd(RESOURCE+'/JDBCDataSourceParams/'+DSnam)
 cmo.setGlobalTransactionsProtocol('TwoPhaseCommit')
 cd('/JDBCSystemResources/'+DSnam)

 # targets the DS to Servers(Cluster or Server)
 targetType=raw_input('Target to (C)luster or (S)erver: ')
 if targetType in ('C','c') :
  clstrNam=raw_input('Cluster Name: ')
  set('Targets',jarray.array([ObjectName('com.bea:Name='+clstrNam+',Type=Cluster')], ObjectName))
 else:
  servr=raw_input('Server Name: ')
  set('Targets',jarray.array([ObjectName('com.bea:Name='+servr+',Type=Server')], ObjectName))

###############     Main Script   #####################################
if __name__== "main":
 print('This will enable you to create or update a Datasource')
 connectAdmin()
 edit()
 startEdit()
 # Create a new JDBC resource)
 cd('/')
 createDS()
 save()
 activate()
 disconnect()
####################################

You can configure as many as datasource but you need to provide the responding properties file. The base script will remain unchanged only the properties files will be varies when you move to different database environment.

Derby datasource using WLST on Weblogic 12.1.2

Here I have experimented the above datasource creation script with Apache Derby Database which is a default database part of Oracle WebLogic 12c. You don't need to run the database externally. When you run the WebLogic instance it automatically runs this Derby database instance.

The sample derby database properties are as follows:

DBURL= jdbc:derby://localhost:1527/demodbs
DBDRV=org.apache.derby.jdbc.ClientXADataSource
#oracle.jdbc.OracleDriver
DBPASS=welcome1
DBUSR=weblogic
DSName=myDs
CONUSR=weblogic
CONPWD=welcome1
CONURL=192.168.56.101:8100

Lets execute the script and see...

~/pybin$ wlst -loadProperties createDS.properties createDS.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

This will enable you to create or update a Datasource
Connecting to t3://192.168.56.101:56001 with userid weblogic ...
Successfully connected to Admin Server "demoadm" that belongs to domain "demodomain".

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

Successfully connected
Location changed to edit tree. This is a writable tree with
DomainMBean as the root. To make changes you will need to start
an edit session via startEdit().

For more help, use help('edit')
You already have an edit session in progress and hence WLST will
continue with your edit session.

Starting an edit session ...
Started edit session, please be sure to save and activate your
changes once you are done.
Naming the datasource
/JDBCSystemResources/myDs/JDBCResource/myDs/JDBCDataSourceParams/myDs
Target to (C)luster or (S)erver: C
Cluster Name: clstr01
Saving all your changes ...
Saved all your changes successfully.
Activating all your changes, this may take a while ...
The edit lock associated with this edit session is released
once the activation is completed.
Activation completed
Disconnected from weblogic server: demoadm

Oracle Datasource using WLST

The customized properties file "configDS.properties" goes like this:

#==========================
# FileName : ConfigDS.properties
#==========================
DBURL=jdbc:oracle:thin:@dbhostname:dbport:dbschema
DBDRV=oracle.jdbc.OracleDriver
DBPASS=dbpasswd
DBUSR=dbuser
DSName=myDs
CONUSR=system
CONPWD=*********
CONURL=hostname:adminport

General WLST execution instructions

Now to execute the custom properties datasource script the command will be given as follows:

java weblogic.WLST –loadProperties ConfigDS.properties ConfigDS.py