SQLJ - Any way to simplify Customise and B ind?

Infodemic B.V. Douwe van Sluis

SQLJ - Any way to simplify Customise and B ind?
Hello Maria,

I recently went trough the same struggle. I have posted a question as well
on the DB2-L. Almost no response, so check the recent history about this.
One of the offline comments was, "You're not alone, but among few". And
that's the case. Not many customers are using Static SQLJ and have
procedures for SQLJ Customization.

What did I do? Basically I do the same things, except that we have both WAS
and DB2 on the same LPAR. We have an extra requirement that the procedure
should be run from within JCL! So I developed the following:
1 JCL which calls a USS Script via BPXBATCH, everything is
parameterised;
3 USS Scripts
. 1-UNJAR the .ser files in a directory structure. The script first
creates/empties this directory.
. 2-Do the db2sqljcustomize, including a db2sqljprint to prove the
options.
. 3-JAR the customised .ser files back into a jar file in a directory
that is in the shared lib definition of WAS.

One challenge was the userid/password combination. I did not want those to
show up in the JCL of output. I developed a procedure to create two
variables in the users .profile, userid and password. Together with a chmod
of 700 for the .profile, no one but the user has access to this file. The
variables are used in the customisation script.

We use a standard directory structure beyond a 'project directory path'.
../source for the input jar file containing the .ser files.
../custinput/... for the extracted .ser files, including the internal
serfiles paths.
../custoutput/... for the customized .ser files, again including the
internal serfiles paths.
../shared, which contains the jar file with customised .ser files. This
directory is also configured as shared library in WAS. It is not necessary
to stop and stop the application server for the renewed .ser files. Start
and stop of the application should be sufficient.


Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.


-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens Maria
McCoy
Verzonden: donderdag 12 januari 2006 14:58
Aan: [login to unmask email]
Onderwerp: [DB2-L] 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
extracted
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

---------------------------------------------------------------------------------
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