SQLJ – Any way to simplify Customise and Bind?

Maria McCoy

SQLJ – Any way to simplify Customise and Bind?
Hi fellow DB2 enthusiasts,

I have recently been responsible for co-ordinating the successful
implementation of the first few SQLJ packages at our site.

Our Websphere processing comes in through a local DB2, and the work is
passed through to a remote DB2 (our production or test system) via a
firewall, using the Distributed Data Facility. We perform the
customisation on the local DB2 and it connects across to the remote DB2 to
perform the bind. Here is the sequence of events that I have to follow
every time the application .ear is redeployed (regardless of whether the
SQLJ bit of the application has changed).

1) ‘Unjar’ the uncustomised serialised profiles from the .jar to a separate
file path
2) Copy into that file path a simple Unix script which eliminates some of
the manual processing
3) Run the script which prompts for the serialised profile name, package
name, userid and password (which are visible on the screen when you enter
them, so this is a security issue too) and these variables, along with hard
coded values such as the -URL are all fed into the db2sqljcustomize command
4) Optional step – use db2sqljprint command to check the contoken against
the package, which has been bound on the remote DB2
5) We have a AppServerA and B directories (which represent the server
clusters on each of the 2 member of the data-sharing group) to avoid single
point of failure so the newly customised serialise profiles need to be
copied to the B directory as well
6) Jar the serialised profiles back into the .jar from which they were
7) Change directory to AppServerB and jar the serialised profiles into
the .jar there also
8) Go to the remote DB2 and grant Execute on the new package / explain
access paths
9) Bounce the servers

This process can take between 20 mins and 1.5 hours (!) depending on how
smoothly it goes. Due to the length of file paths, complexity of the
commands and manual nature it is obviously an error prone, slow and
unreliable method to deploy an application, but we know of no other way.
Some of the actions could be further automated with a more complex Unix
script, but we do not have the expertise to write or maintain such a
script. (In fact, a consultant helped us devise the simple one we are
using – thanks Jules!)

I would like to know the following: Are other installations finding this
process to be as cumbersome? If not how are you achieving it? Has anyone
developed a better script / utility to help with this process?

I have initiated enquiries to the IBM labs to see whether any such tool is
being or plans to be developed but I think it’s unlikely at this time.

Thanks for your attention,

Maria McCoy
DB2 DBA - H.M Land Registry

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm