Level: Intermediate Marichu Scanlon (marichu@us.ibm.com), Advisory Software Engineer, IBM
10 May 2007
This article discusses the system-related tasks that you, as a DB2® for z/OS® V8 or V9 user, should consider when utilizing
the functions in Developer Workbench (DWB). It provides an overview of DWB, then discusses customization JCLs, RACF,
and migration considerations. Finally, you'll take a look at some common set-up errors and problems.
DWB Overview
Developer Workbench is an Eclipse-based IDE client tool for developing DB2 business objects.
It provides connection management for DB2 servers on Linux®, UNIX®, Windows®, iSeries™, IBM® Cloudscape™, and DB2 for z/OS.
It provides quick development and management of stored procedures, SQL statements, and user-defined functions.
When the server is DB2 for z/OS V9, DWB supports development of both external and native SQL stored procedures.
You can install DWB as either:
- a separate installation from the DB2 9 for Linux, UNIX, and Windows installation pack
- a free download from the Web. See the Resources section of this article to download DWB.
DWB includes the JDBC™ drivers necessary to connect to the DB2 for z/OS servers. However, the drivers are used for application
development only. When Enterprise connectivity is required, you'll need to purchase either DB2 for Linux, UNIX, and Windows V8.2 Enterprise Edition (or later),
or DB2 Connect™ V8.2 Enterprise Edition.
DB2 for z/OS features
DWB is the successor to the DB2 Development Center (DC), an application development tool delivered with DB2 for Universal Database® (UDB) V8.2.
DWB continues to support previous DC capabilities, such as:
- Creating, editing and deploying external SQL and Java™ stored procedures
- Importing from, and exporting to a file system, one or more SQL and Java stored procedures
- Creating, editing and executing SELECT, INSERT, DELETE, UPDATE, full select, and WITH statements
- Importing and organizing statements within a stored procedure
- Deploying stored procedures from exported files
DWB enhanced the export and deploy features in the DC, as well as added new functions.
These new functions include the ability to:
- Create, edit and deploy native SQL stored procedures for DB2 for z/OS V9
- Create, edit and deploy SQL user-defined functions (UDF)
- Deploy stored procedures using binaries
- Create multiple packages, also known as package variation, for SQL and SQLJ stored procedures
- Share projects and other artifacts with other teams using the Concurrent Versions System (CVS)
or Rational ClearCase Remote Client. (Both systems can function as a source control management system).
- Include supporting jars when deploying and executing Java stored procedures
- Create, register, and annotate XML schemas; specify and manipulate XML datatype columns
- Migrate DC projects into DWB projects
A Unified Debugger for SQL and Java stored procedures is another enhancement.
Set-up for DWB : Work Load Manager
DWB uses DB2-supplied procedures for most of its processing, for instance when:
- Building external SQL stored procedures
- Building Java stored procedures
- Refreshing the Work Load Manager (WLM)
- Deploying external SQL stored procedures using binaries
- Debugging SQL and Java stored procedures
- Registering and annotating XML schemas
These DB2-supplied stored procedures are WLM-managed, that is, they execute in a WLM application environment.
Additionally, both V8 and V9 user stored procedures are WLM-managed. Therefore, the first task in your set-up would be to define the WLM environments.
A complete discussion of how to set up a WLM application environment is beyond
the scope of this article. Please refer to the Resources section of this article for a link to "DB2 for z/OS Stored Procedures: Through the CALL and Beyond" (IBM Redbooks), which thoroughly discusses how to create your WLM environments,
how many to create, what to code in your WLM procedure JCLs, and how to authorize users to
these WLM procedures. However, it's worth mentioning some common problems you may experience when setting up your WLM environments. For instance, these actions may cause errors:
- Defining the variables used in the JAVAENV dataset
- Specifying the number to put in NUMTCB in the WLM procedure JCL
Let's come back to the matter of the JAVAENV later in this article. For now, let's discuss how to determine the number of NUMTCBs to specify in the WLM Proc.
The DB2-supplied stored procedures that DWB uses are created during the execution of
several customization or tailoring jobs. As you customize these jobs, you must specify a WLM environment in the Data Definition Language (DDL) of these stored procedures. The following table lists
the recommended NUMTCB settings for these DB2-supplied stored procedures. It also identifies
the specific customization job that creates the stored procedure.
Table 1. How many NUMTCBs to specify in the WLM Proc.
| Stored Procedure (SP) Name | NUMTCB | Tailoring Job | Comments |
|---|
DB2DEBUG.SYS*
SQLJ.INSTALL_JAR
SQLJ.REPLACE_JAR
SQLJ.REMOVE_JAR
SYSIBM.SQL*
SYSPROC.WLM_REFRESH
DSNTJSPP
| 40 - 60 |
DSNTIJSD
DSNTIJSG
DSNTIJSG
DSNTIJSG
DSNTIJSG
DSNTIJSG
DSNTIJSG
|
Used by Unified Debugger SPs
Used for Java development;
All STEPLIB DDs must be authorized for WLM_REFRESH;
Used for getting DB metadata information;
Used to refresh WLM from client
Used for building Java SPs
|
DSNTPSMP
DSNTBIND
| 1 |
DSNTIJSG
DSNTIJPP
|
Used for external SQL SP development;
Used for external SQL and SQLJ SP development
| | User Java SP execution | 5 - 8 | None | Must have 1 unauthorized Steplib Dataset
| | User external or native SQL SP execution | 10 - 15 | None | Must have 1 unauthorized Steplib Dataset
|
Set-up for DWB: The tailoring jobs
These tailoring jobs create and bound the DB2-supplied stored procedures that DWB needs:
-
DSNTIJSG: This job contains the DDL and bind jobs for the DB2-supplied stored procedures which enable DWB to perform the functions listed below.
- Build an external SQL or Java stored procedure
- Obtain database metadata
- Obtain actual cost measurements of a SQL statement
- Refresh a specific WLM application environment from the client side
- Alter the Java path from the client side
- Grant execute privileges on the stored procedures
-
DSNTIJSD: This job creates the DB2-supplied stored procedures that enable DWB to debug a SQL
or Java stored procedure from the client side.
-
DSNTEJ6W: The first step of this job defines a RACF resource class for the WLM_REFRESH stored
procedure. This job allows DWB to issue a "VARY WLM..." on behalf of the userid connecting to the
server.
-
DSNTIJCC: This job creates the DB2-supplied stored procedures that enable DWB to deploy external
SQL stored procedures using binaries.
-
DSNTIJNX: This job runs automatically after DB2 9 New Function enablement and creates the XML schema
repository used by DWB for registering XML schemas.
 |
Set-up for DWB: JDBC driver considerations
The DWB client can connect to z/OS using the IBM DB2 Universal Driver (JCC) or another driver by specifying "Other"
in the pull-down selection list for JDBC drivers. When specifying "Other", you would type in all the data for the connection information as shown in Figure 1. Connection wizard. When using the IBM DB2 Universal Driver, the user can either connect using Type 2 or Type 4. DWB attempts to "discover" all connections on the client side by querying the DB2 database directory. If a new connection is
requested, and the IBM DB2 Universal Driver is selected, DWB composes the connection URL as you enter
the information for location, hostname and port.
 | | To specify JDBC properties in the connection, you have to specify "Other" as your JDBC driver. When
you do so, all the connection fields become editable. |
|
Figure 1. Connection wizard
DWB ships with the IBM DB2 Universal Driver and license jars. These jars are found here:
<install directory for DWB>\eclipse\plugins\com.ibm.datatools.db2_1.0.0\driver
|
On the z/OS side, the JDBC drivers are installed in a hierarchical file system (HFS) and pointed to by
environment variables within a dataset specified in the Java stored procedures' WLM procedure.
This dataset's DD name is JAVAENV. When connected to DB2 for z/OS V8, DWB can build
Java stored procedures on the client side (this is the default) and ship the jars to the server.
DWB can also build Java stored procedures on the server side using the DB2-supplied stored procedure, DSNTJSPP.
This is the only way to build a Java stored procedure if the client driver is the JDBC driver for JDBC and SQLJ
(or Legacy driver).
When using the former, the JAVAENV contains the environment variable DB2_HOME, which points to the directory
where the JDBC driver for JDBC and SQLJ is installed.
When using the JCC driver, the JCC_HOME environment variable is used, as shown in the sample contents of a
JAVAENV dataset below:
Listing 1. JAVAENV dataset: Sample contents
ENVAR("CLASSPATH=/u/admf001/sdk141",
"JCC_HOME=/usr/lpp/db2/db2810/jcc",
"JAVA_HOME=/usr/lpp/java140/J1.4"),
MSGFILE(JSPDEBUG,,,,ENQ),
XPLINK(ON
|
Set-up for DWB: JDK considerations
DWB ships with Java Developer Kit (JDK) Version 1.5 for processing and developing Java stored procedures.
DB2 for z/OS V8 typically have JDK 1.3 or JDK 1.4 installed. You can apply PTF for APAR PK09213 to allow client Java stored procedures (built with JDK 1.5) to execute with a 1.5 Java Virtual Machine (JVM) on the DB2 for z/OS server. There is no problem
executing Java stored procedures built with JDK 1.5 against a DB2 for z/OS V9 server.
DWB problems related to the Java set-up
Some known DWB set-up problems are discussed in this section. Please see the
problem scenarios and their solutions below.
JDBC driver setting in the WLM procedure
If you are connecting to a DB2 for z/OS V8 server using DWB, the default driver that DWB uses is the IBM
DB2 Universal Driver. When you execute a Java stored procedure, DB2 for z/OS brings up a WLM application environment
as specified in your stored procedure DDL. If the corresponding WLM procedure is not set up with the JCC_HOME environment
variable, or if the JCC_HOME environment variable is pointing to an incorrect directory, DWB returns an error.
On the DB2 for z/OS console, a SQLException is reported. The error may look something like this:
Listing 2. SQLException
- 17.38.36 STC00108 DSNX961I DSNX9WLJ ATTEMPT TO PERFORM OPERATION
- FindClass
- FAILED FOR ROUTINE . . SSN= V91A PROC= V91AWMJU ASID= 003A
- CLASS= METHOD= ERROR INFO= java.lang.NoClassDefFoundError:
- com/ibm/db2/jcc/DB2Driver
|
To fix this, edit the JAVAENV file (See Listing 1. JAVAENV dataset: Sample contents on how to code the contents of the JAVAENV dataset.). Also, ensure the JCC_HOME environment variable points to the directory where the IBM DB2 Universal Driver files are installed.
This error may also occur if the DB2_HOME environment variable is coded instead of the JCC_HOME. DB2 for z/OS V8 tolerates
having both variables specified in the JAVAENV dataset. DB2 for z/OS V9 does not allow you to specify DB2_HOME.
DB2Binder and multiple collection ids
When connecting to DB2 for z/OS V8 or V9 using the IBM DB2 Universal Driver, you could get the following
error message on connection:
Figure 2. DB2Binder error
This error is the result of the DB2Binder utility not being run against the database. When you run the DB2Binder utility, the default collection id
is NULLID. If you prefer a different collection id for the JDBC packages, specify this in the DB2Binder command as follows:
java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://mig.null.washington.ibm.com:446/DB2LOCNM
-user myuser -password mypwd -collection DSNJDBC
|
Also, when creating package variations for SQL and SQLJ stored procedures, the DB2Binder utility must be run against each collection id.
JDK incompatibilities
When a Java stored procedure is built using JDK 1.5 (the default for DWB) and the DB2 for z/OS
server is using JDK 1.4, execution fails. Unfortunately, DWB is unable to distinguish a WLM failure that is due to an abend of the
stored procedure or from an invalid JDK level. DWB simply returns the system error code returned from DB2 for z/OS to the client. An
examination of the DB2 for z/OS console shows an error similar to the following:
Listing 3. JDK incompatibility error
- 14.41.52 STC00157 $HASP395 V81AWMJU ENDED
00- 14.42.15 STC00158 +DSNX961I DSNX9WLJ ATTEMPT TO PERFORM JNI FUNCTION
- FindClass
- FAILED FOR STORED PROCEDURE ADMF001.J_V82JARS . SSN= V81A PROC=
- V81AWMJU ASID= 003D CLASS= PKG60424024051890/J_v82jars METHOD=
- j_v82jars ERROR INFO= PKG60424024051890.J_v82jars
- ADMF001.SQL6052402405858 java.lang.UnsupportedClassVersionError:
- PKG60424024051890/J_v82jars (Unsupported major.minor version 49.0)
|
DB2 for z/OS V9 provides three useful UDF to query the server's JDK and JCC versions,
namely:
- SYSADM.JVMVERS, used to determine the version of the JDK used in the server
- SYSADM.JAVDRVN and JAVDRVV, used to determine the JCC driver name and version
Below are sample queries on how to use these UDFs:
Listing 4. Useful UDFs
SELECT SYSADM.JVMVERS('java.vm.name') FROM SYSIBM.SYSDUMMY1;
SELECT SYSADM.JVMVERS('java.version') FROM SYSIBM.SYSDUMMY1;
SELECT SYSADM.JAVDRVN() FROM SYSIBM.SYSDUMMY1;
SELECT SYSADM.JAVDRVV() FROM SYSIBM.SYSDUMMY1;
|
Once the server JDK level is determined, you can specify the JDK level to be used for building and executing your Java stored procedures. DWB provides three ways or scopes for specifying the JDK, namely:
- Globally, through the Preferences page (see Figure 3),
- Limited to a project through the Project Properties page (see Figure 4)
- Limited to a specific Java stored procedure through the Deploy wizard's Routine Options page (see Figure 5)
Figure 3. Preferences
Figure 4. Project Properties
Figure 5. Routine Options
On the server side, the JDK level is changed by setting the JAVA_HOME environment variable
in the JAVAENV file to the directory where the JDK files are installed.
Using DECFLOAT in SQL statements
How to change what JDK level DWB uses when building Java stored procedures was discussed earlier.
When using the new DECFLOAT datatype, now supported in both DB2 for z/OS V9 and DB2 9 for Linux, UNIX, Windows, the
JDK level that DWB itself uses must be set to JDK 1.5. To do this, you can change the JVM
initialization variable to point to the directory that has JDK 1.5. You can do this in one of two ways:
- Specify the -vm <JDK 1.5 directory>\bin\javaw in the command line when launching DWB.
- Edit the eclipse.ini file found in the <DWB install directory>\eclipse directory of DWB as shown below.
Listing 5. Setting the -vm argument in eclipse.ini
-vm
<JDK 1.5 directory>\bin\javaw
|
If you create a shortcut to the desktop for launching DWB, you'll notice that the actual command to launch it
uses eclipse.exe. An example is: eclipse.exe -clean -showlocation -vm c:\IBM_JDK15\jre\bin\javaw. You can then use the first option
above to specify your JDK.
If you choose the second option and edit the eclipse.ini file, be sure to have the two keywords in two lines.
Specifying a default WLM for native SQL stored procedures
DB2 for z/OS V9 introduced native SQL stored procedures. Native SQL stored procedures do not have to specify a WLM
environment keyword unless you are planning to debug the stored procedure. In that case, you'd check the "Enable Debugging"
option in the New Stored Procedure wizard, or before deploying the stored procedure. DWB will add the "ALLOW DEBUG" keyword
in the DDL as well as assign the default WLM environment that you specified in the Preferences (to the "WLM ENVIRONMENT" keyword).
The user can also edit the DDL itself and type in those two keywords.
However, in some environments, the stored procedure developer does not know the default WLM environment. If the you click on the
"Enable Debugging" check box, DWB will attempt to assign the blank WLM environment as well as generate the "ALLOW DEBUG" keyword.
This causes errors when you deploy. The workaround is to simply edit the "WLM ENVIRONMENT" keyword out.
Conclusion
In summary, this article discussed the major set-up requirements for DB2 for z/OS when it's targeted as a server by DWB.
These are requirements are to:
- Define the WLM application environments for both the DB2-supplied stored procedures and user stored procedures
- Edit the customization jobs and execute
- Set up the JDBC drivers and JDK levels on both client and server sides
This article also listed and discussed a few common problems you may encounter as a result of your set-up.
Because each environment set-up is unique and there are numerous configurations available, you may encounter other problems during your set-up. The DB2 DWB Information Center contains a wealth of information to help you solve these problems.
Resources Learn
Get products and technologies
Discuss
About the author | 
| | Marichu Scanlon is a Java developer and tester for the IBM Information Management Application Development Tooling department. Among the products the group develops is the Developer Workbench for DB2 V9. She has also been involved in the development and testing of the AD Tooling features in Rational Data Architect, Rational Application Developer V6, and the Development Center. |
Rate this page
| 
