Setting up DB2 for zOS for developing stored procedures with IBM Data Studio V1'1

1
Setting up DB2 for z/OS for developing stored
procedures with IBM Data Studio V1.1 Marichu
Scanlon marichu_at_us.ibm.com MDUG Dec 6, 2007
2
Topics

• Overview of IBM Data Studio
• Installation and Connectivity
• Setup tasks for both DB2 for z/OS V8 and V9
• WLM environment setup
• Tailoring jobs and optional setup jobs
• Java setup
• Debugger setup
• Common problems / suggested resolutions
• Summary and Cheat Sheets

3
IBM Data Studio
What is it ?
IBM Data Studio is a comprehensive data
management solution that empowers you to
effectively design, develop, deploy and manage
your data, databases and database applications
throughout the entire application development
life cycle utilizing a consistent and integrated
user interface
4
IBM Data Studio
Who will use it ?
Application Life Cycle
Administration Configuration Performance Managemen
t Backup Recovery Database Management
Data Auditing Data Archiving Data Masking Data
Encryption Security Access Security
Analysis Data Governance
Logical Modeling Physical Modeling Integration Mod
eling Data Modeling
Java .NET Web Services PHP RUBY COBOL Applicatio
n Development
Stored Procedures SQL XQuery User Defined
Functions Database Development
Govern
Manage
Deploy
Develop
Design
5
IBM Data Studio
A Consistent, Integrated Solution
6
IBM Data Studio v1.1

• Empowering developers and database administrators
• Complimentary and available in October of 2007
• Support for DB2 on all platforms and IDS

DB2 for LUW
DB2 for z/OS
DB2 for i5/OS
IDS

• Physical Data Modeling
• Data Distribution Viewer
• Integrated Query Editor
• SQL Builder
• SQL Routine Debugger
• Java Routine Debugger
• XML Editor
• XML Schema Editor
• pureQuery for Java
• Data Web Services
• Object Management
• Data Management
• Update Statistics
• Security Access Controls
• Project Management

• Physical Data Modeling
• Data Distribution Viewer
• Integrated Query Editor
• SQL Builder
• SQL Routine Debugger
• XML Editor
• XML Schema Editor
• pureQuery for Java
• Data Web Services
• Object Management
• Data Management
• Security Access Controls
• Project Management

• Physical Data Modeling
• Data Distribution Viewer
• Integrated Query Editor
• SQL Builder
• XML Editor
• XML Schema Editor
• pureQuery for Java
• Data Web Services
• Object Management
• Data Management
• Security Access Controls
• Project Management

• Physical Data Modeling
• Data Distribution Viewer
• Integrated Query Editor
• SQL Builder
• SQL Routine Debugger
• Java Routine Debugger
• XML Editor
• XML Schema Editor
• pureQuery for Java
• Data Web Services
• Object Management
• Data Management
• Update Statistics
• Health Monitoring
• Visual Explain
• Security Access Controls
• Project Management

7
Why IBM Data Studio ?

• Increase productivity for all roles throughout
  the data life cycle
• Slash development time up to 50 with an
  integrated data management environment
• Promote collaboration across roles to optimize
  data server and application performance
• Accelerate Java development productivity with new
  pureQuery data access
• Simplify development of applications implementing
  industry specific XML standards
• Monitor data server operation performance
  anywhere, anytime from a Web browser
• Simplify and speed development of new skills
• Learn once, use with all supported data servers
• Easy-to-use and integrated user interface,
  compatible with Rational Software Development
  Platform
• Extensible with Eclipse plug-ins to customize the
  environment for each team member
• Accelerate data as a service for Service Oriented
  Architecture
• Develop and publish data as a Web service without
  programming
• Info 2.0 Ready - support for Web 2.0 protocols
  and format

8
Feature details

• Provides connection management for DB2 servers on
  LUW, iSeries, zSeries, IBM Cloudscape and Apache
  Derby databases
• Provides quick development and management of
  Stored Procedures, SQL statements and User
  Defined Functions
• Support for Native SQL stored procedures when
  server is DB2 for z/OS V9
• Provides a visual outline of the server database
  objects grouped by schema
• Builds, parses and runs SQL statements
• Visually explains execution of SQL statements

9
Feature details (continued)

• Provides rich support for XML
• Support for XML data type in routines, table
  editor, SQL statements, SQL Builder
• XML schema registration
• Map XML schemas (XSD) to relational schemas
• Unified debugger for SQL and Java stored
  procedures
• Allows sharing and tracking of changes to data
  objects through a common team support interface

10
Installation and Connectivity

• Data Studio V1.1 is available as a free download
  from the Web. Visit
• www.ibm.com/software/data/studio.
• Ships with JDBC drivers to connect to DB2 on LUW,
  iSeries and z/OS
• Drivers can only be used with Data Studio
• Can also use DB2 Connect licenses to connect to
  z/OS.
• Need to check driver versions for compatibility

11
The workspace
12
Connection Wizard

• Connect to DB2 servers
• Support for DB2 Universal driver or other JDBC
  driver
• DB2 database Aliases defined in DB2s
  Configuration Assistant
• Configuring location of the driver
• If using DB2 Universal Driver, Connection URL is
  built as you enter information.
• Test Connection option

13
Database Explorer

• Hierarchical organization of databases and
  objects in it.
• Supports creation and modification of Connections
  through the Connection wizard.
• Connections can be refreshed with newly created
  objects.
• Supports filtering before loading objects from
  database catalogs.
• Database model objects created for each entry in
  the catalog.
• Called Reverse Engineering or Catalog
  Loading
• Objects can be dragged and dropped to a project.

14
Data Project Explorer
Project folder contains business objects
Target connection
Launch Wizards from context menu
Import and Export to File System
15
Supported routine tooling tasks

• Create, edit and deploy External SQL and Java
  stored procedures
• Import from and export to a file system, one or
  more External SQL and Java stored procedures
• Create, edit and execute SELECT, INSERT, DELETE,
  UPDATE, full select, WITH statements
• Import and organize statements to a stored
  procedure
• Deploy stored procedures from exported file

16
New functions for DB2 for z/OS

• V8 and V9
• Support for SQL User Defined Functions
• Binary deploy of stored procedures
• Package variation for SQL and SQLJ stored
  procedures
• Team support
• V9 only
• Native PSM
• Multiple jar support for Java stored procedures
• Unified Debugger for SQL and Java stored
  procedures
• XML Support
• V8 or V9
• DC Project Migration

17
Workload Manager setup
18
WLM Setup why do this first?

• DS uses DB2 supplied procedures for
• Building external SQL stored procedures
• Building Java stored procedures
• Refreshing the WLM
• Binary Deploy of external SQL stored procedures
• Unified Debugger
• XML support
• V8 and V9 user stored procedures are also
  WLM-managed, no more SPAS
• Therefore, first step is to define the WLM
  environments.

19
Syplex vs Monoplex

• If running on a MONOPLEX or SYSPLEX with one DB2
  LPAR per image use one set of WLM definitions
  for all DB2 images. For example
• WLMENV1
• WLMENV2
• WLMENV3
• ...
• WLMENVnn
• If running on a SYSPLEX with multiple DB2 LPARs
  per image, need one set of WLM definitions to be
  used by each DB2 image on the LPAR. For example
• SSIDWLMt
• SSIDWLM2
• ...
• SSIDWLMnn

20
How many WLMs to setup?

• One WLM Definition for Miscellaneous System SPs
• Two WLM Definitions recommended for SQL stored
  procedures
• Three WLM Definitions recommended for Java stored
  procedures
• For V8, one WLM definition for DSNTJSPP, one for
  executing SPs when connected using the JDBC
  Legacy driver, and another for executing SPs when
  connected using the IBM Universal driver

21
How many NUMTCBs ?
22
WLMs for DWB

• External SQL stored procedure
• WLM1 for DSNTPSMP used to build SP
• WLM for user SQL SP used when executing SP
• Optional PSMDEBUG DD card for collecting debug
  information
• Java stored procedure
• WLM1 for DSNTJSPP used to build SP in V8,
• WLM1 for SQLJ_INSTALLJAR, etc used to build SP in
  V9
• WLM for user Java SP used to execute SP with the
  legacy driver
• WLM for user Java SP used to execute SP with the
  universal driver
• Native SQL stored procedure
• No WLM required to build native SQL SP
• WLM used to debug SP

23
Create the Applenv and Procs

• Create the WLM address space startup proc
• Modify DSN8WLMP sample startup proc
• Define the WLM Application Environments
• The Procedure Name here must match the JCL
  startup procedure name defined above.
• Set the NUMTCB as suggested in the table on slide
  17.

24
Customization jobs and other optional jobs
25
Tailoring Jobs

• Tailoring jobs
• DSNTIJSG
• DSNTIJSD
• DSNTEJ6W
• DSNTIJCC
• DSNTIJNX
• Run after V9 New Function enablement
• Other jobs
• RACF job for DSNTIJCC SPs
• Jobs for setting up the Session Manager
• DSNTIJEX

26
General Tailoring changes

• Customize the following values in the tailoring
  jobs
• The subsystem name '!DSN!' to the name of your
  DB2
• 'DSNTIA!!' to the plan name for DSNTIAD on your
  DB2
• '!WLMENV! ( to the name of the WLM application
  environment defined for DSNTPSMP, DSNTJSPP, etc)
• 'DSN!!0' to the prefix of the target library for
  DB2
• '!SYS1.PROCLIB!' to the name of the system
  proclib for DB2 language PROCs

27
DSNTIJSG -1

• Creates DB2-supplied stored procedures that DS
  uses when creating a SP
• Change WLMENV!! to appropriate WLM value
• Creates DSNTPSMP and DSNTJSPP
• DSNTPSMP
• Utility stored procedure for creating an external
  SQL stored procedure in the server
• DSNTJSPP
• Utility stored procedure for creating a Java
  stored procedure in the server
• Modify the RUN OPTIONS to contain the path for
  DSNTJSPP.properties (more later in Java setup)

28
DSNTIJSG - 2

• Creates DB2 metadata stored procedures,
  SYSIBM.SQL
• These SPs are used by DS to retrieve the catalog
  information for a particular database object
• Also used by SQL Tools within DS to populate the
  panels in SQL Wizard
• Need to run DB2Binder in the workstation after
  customization
• Creates DSNWSPM stored procedure for processing
  Actual Cost (optional)
• DSNWSPM measures the cost of SQL statements in
  your stored procedure
• Applies to SQL and Java SPs
• After tailoring, need to start DB2 accounting
  trace
• START TRACE(ACCTG) CLASS(1,2,3)

29
DSNTIJSG - 3

• Creates WLM_REFRESH stored procedure
• WLM_REFRESH enables DS to ensure that the latest
  version of a stored procedure is in effect
• Manually mimic this from the TSO SDSF screen by
  typing a Vary WLM command
• DWB calls WLM_REFRESH to issue the Vary WLM on
  its behalf
• Creates ALTER_JAVA_PATH stored procedure
• Binds all the packages of the DB2 supplied stored
  procedures created
• Grants necessary authorizations to PUBLIC

30
Racf job to permit authids to issue WLM_REFRESH

• DSNTEJ6W
• At a minimum, tailor STEP1
• Allows DWB user to issue against the server, a
  Vary WLM, ltwlmenvgt, REFRESH from the client side
• Create and populate a resource profile in
  resource class DSNR
• ltdsngt.WLM_REFRESH.ltwlmprocgt
• Where
• ltdsngt DB2 location
• ltwlmprocgt WLM proc where user SP executes
• Assign to an ID that has SYSADM rights

• //SYSTSIN DD
  
• RDEFINE DSNR (.WLM_REFRESH.) UACC(NONE)
  
• PERMIT .WLM_REFRESH. CLASS(DSNR) ID(MARICHU)
  ACCESS(READ)

31
Other tailoring jobs

• DSNTIJSD
• Creates objects used by the Unified Debugger
• DS uses Unified Debugger for debugging Java and
  SQL stored procedures
• Grants necessary authorizations
• For V8, need APAR PK41138 - "UNIFIED DEBUGGER V9
  FP2 ON DB2 FOR Z/OS V8
• DSNTIJCC
• Creates objects required for using the DB2
  Control Center and for DS to do binary deploy of
  External SQL SPs.
• For V8, need PTF UK10768 to be applied
• Jobs to define RACF authorities and grant
  authorizations to JES2 (see next slide)
• Required for using DB2 supplied stored
  procedures

32
Sample RACF Job

• //RACFJOB JOB,ACCOUNT),'NAME',MSGCLASSH,MSGLEVEL
  (1,1),
• // CLASSA,NOTIFYSYSUID
• //STEP01 EXEC PGMIKJEFT01
• //SYSTSPRT DD SYSOUT
• //SYSTSIN DD   
• RDEFINE FACILITY BPX.DAEMON.HFSCTL UACC(READ)
• RALTER OPERCMDS MVS.MCSOPER. UACC(READ)
• PE MVS.MCSOPER. CLASS(OPERCMDS) ID(USRT003)
  ACCESS(NONE)
• SETROPTS RACLIST(OPERCMDS) REFRESH
• SETROPTS RACLIST(FACILITY) REFRESH
• ALU USRT001 OPERPARM(ROUTCODE(ALL))   
• ALU USRT002 OPERPARM(ROUTCODE(ALL) AUTH(INFO))

33
After migrating to New Function Mode

• DSNTIJNX
• Non-optional job in the ENFM process
• Contains step to create XML Schema Repository
  routines for XML support in DWB
• Last step in this job is to install and verify
  routines that require new function mode by
  running DSNADMVY
• DSNADMVY
• REXX program that can be run through JCL
• Verifies the setup, configuration and activation
  of the DB2 supplied and required stored
  proceudres
• Produces a report when setup is wrong

34
Helpful Java UDFs for checking JCC and JDK
versions

• SYSADM.JVMVERS determine the version of the JDK
  used in the server
• SELECT SYSADM.JVMVERS('java.vm.name') FROM
  SYSIBM.SYSDUMMY1
• SELECT SYSADM.JVMVERS('java.version') FROM
  SYSIBM.SYSDUMMY1
• SYSADM.JAVDRVN and JAVDRVV determine the JCC
  driver name and version
• SELECT SYSADM.JAVDRVN() FROM SYSIBM.SYSDUMMY1
• SELECT SYSADM.JAVDRVV() FROM SYSIBM.SYSDUMMY1

35
Unified Debugger Setup

• Two setups client and server (optional)
• DB2 9 on LUW
• Set up on DWB Preferences page
• Session Manager automatically starts
• DB2 UDB v8.2 FP14
• Manually start Session Manager
• Enter port information in DWB Preferences page
• DB2 for zSeries V9
• DB2 for zSeries V8 (with PTF number UK25860
  applied)

36
Session Manager on server - 1

• RACF setup for Session Manager userid
• Select a userid (e.g. USRT005) to use for running
  the Session Manager daemon and define an OMVS
  segment for this id.
• Define a STARTED class profile, DB2UDSMD., and
  associate the userid above to this profile
• See sample JCL below

//RACFDEF EXEC TSOBATCH
//SYSTSIN DD

ALTUSER USRT005 OMVS( UID(5) HOME('/u/usrt005')
PROGRAM('/bin/sh') )- TSO(
PROC(TPROC02) SIZE(40960) COMMAND(OMVS) )
RDEFINE STARTED DB2UDSMD.
STDATA(USER(USRT005))
SETROPTS RACLIST(STARTED) REFRESH
END
37
Session Manager on server - 2

• // Create a file in the HFS from inline data
  using COPY
• //-----------------------------------------------
  ----------------------
• //OCOPY EXEC PGMIKJEFT01,DYNAMNBR30
  
• //SYSTSPRT DD SYSOUT
  
• //HFSOUT DD PATH'/u/usrt005/DB2UDSMDprofile',
  
• // PATHOPTS(OWRONLY,OCREAT,OAPPEND,OT
  RUNC),
• // PATHMODE(SIRUSR,SIWUSR,SIRGRP,SIRO
  TH)
• //INLINE DD
  
• -------------------------------------------------
  ---------------------
• Environment settings for running the Unified
  Debugger Session Manager
• _BPX_BATCH_SPAWNNO
  
• _BPX_SHAREASYES
  
• Arrange for the JVM to run in the same
  address space. This avoids
• launching 2 additional address spacss for
  the Started TasK.
• CLASSPATH
  
• The location of the UDBG Session Manager
  jar file
• JAVA_COMPILERNONE
  
• Disable the JIT. The Started Task runs the
  Session Manager only
• one time, so disabling this saves space
  that will not be used.

• Create a configuration file in HFS to define the
  execution environment for the Session Manager
  daemon.
• The userid previously created must have read and
  execute authority to this file
• CLASSPATH should contain the location of the
  Session Manager jar file, typically
  /usr/lpp/db2910_base/classes/db2dbgm.jar

38
Debugger setup on server - 3

• Create a PARMLIB member named DB2UDSMD. This is
  the JCL proc that will execute the Session
  Manager as a daemon.
• Issue START DB2UDSMD in console to start Session
  Manager.
• From the Console display, capture the port and
  host name of the Session Manager.
• Enter these values in the clients DWB
  preferences page.

• //DB2UDSMD PROC PORT4553,TIMEOUT60
  
  
• // DB2 Unified Debugger Session Manager
  DAEMON FOR OPENEDITION
  
• // This JCL assumes no .profile exists for
  the user.
• // Environment settings (CLASSPATH) come
  from a named profile.
• // Explicitly identify the java edition
  to run. PATH cannot be
• // controlled by the environment file.
  
• //
  
• //DB2UDSMD EXEC PGMBPXBATCH,DYNAMNBR128,REGION0
  M,
• // TIME1440,PARM'SH
  date/usr/lpp/java140/J1.4/bin/java
• // com.ibm.db2.psmd.mgr.Daemon
  -timeout TIMEOUT -port
• // PORTdate'
  
• //STDOUT DD PATH'/tmp/DB2UDSMD.stdout',
  
• // PATHOPTS(OWRONLY,OCREAT,OAPPEND,OT
  RUNC),
• // PATHMODE(SIRUSR,SIWUSR,SIRGRP,SIRO
  TH)
• //STDERR DD PATH'/tmp/DB2UDSMD.stdout',
  
• // PATHOPTS(OWRONLY,OCREAT,OAPPEND,OT
  RUNC),
• // PATHMODE(SIRUSR,SIWUSR,SIRGRP,SIRO
  TH)
• //STDENV DD PATH'/u/usrt005/DB2UDSMDprofile',
  
• // PATHOPTSORDONLY

39
DSNTIJEX exit routines

• DSNTIJEX job to assemble DB2 exit routines
• Sample exit routines delivered with DB2
• DSN3SSGN sign on exit routine for remote access
  to DB2
• DSN3AUTH authorization exit routine when using
  secondary authids
• Other exit routines for CICS and IMS (not needed
  by DWB)

40
Setup specific to Java stored procedures
41
JDBC Driver - client

• Client can connect to z/OS using the IBM DB2
  Universal driver or another driver
• Universal driver jar and license jars
• db2jcc.jar
• db2jcc_license_cisuz.jar
• db2jcc_license_cu.jar
• DS supplied jars in
• ltinstall dir for DSgt \eclipse\plugins\
  com.ibm.datatools.db2_1.0.0\driver

42
JDK setup - client

• With Universal driver, Java SPs are built on the
  client side and the jars installed on the server.
• Location of JDK and sqlj.zip files
• DS supplied JDK 1.4 in ltinstall_dirgt\eclipse\jdk
• DB2 9 supplied JDK 1.5 in ltinstall_dirgt\SQLLIB\jav
  a
• 3 ways to set the JDK level
• in DS preferences file for global setting
• in DS Project gt Properties gt for project-wide
  setting
• In Deploy Wizard for a specific Java stored
  procedure

43
Specifying JDK level
Project Properties
Preferences page
Deploy Wizard
44
Server side setup for Java SPs on z/OS (1 of 2)

• Install Java 2 Technology Edition
• For DB2 for z/OS V8, install SDK 1.3.1, SDK 1.4.1
• For DB2 for z/OS V9, install V1.4.2 SR2 or later
• Note DECFLOAT support requires Java 2 Technology
  Edition V1.5 or higher
• Run the SMP/E jobs to allocate the HFS directory
  structure and load the JDBC and SQLJ libraries
• Enable DDF and TCPIP support
• Set ZPARM DECSTAT to YES
• In USS, customize the environment variable
  settings in .profile file

45
Server side setup for Java on z/OS (2 of 2)

• Enable the DB2 supplied stored procedures and
  define the tables that are used by the IBM DB2
  Universal Driver
• Run DSNTIJSG or DSNTIJMS
• In z/OS USS, or in the client workstation, run
  the DB2Binder utility
• Binds the DB2 packages used at the server by the
  IBM DB2 Driver for JDBC and SQLJ
• Grants EXECUTE authority on packages to PUBLIC

46
The JAVAENV dataset

• JAVAENV DD resides in WLM AE proc where the DB2
  Java SP executes
• Maximum dataset size used, 245 bytes, limited by
  LE
• Sequential data set
• LRECL255 (extra bytes used by DB2 for POSIX),
• RECFMVB
• Optional CLASSPATH for any additional classes
  that the Java SP may use.
• V9 multiple jar support eliminates the need to
  modify this variable

47
Sample JAVAENV file

• ENVAR("CLASSPATH/u/admf001/sdk141",
  
• "JCC_HOME/usr/lpp/db2/db2810/jcc",
• "JAVA_HOME/usr/lpp/java140/J1.4"),
• MSGFILE(JSPDEBUG,,,,ENQ),
• XPLINK(ON)

• Connection from client is IBM DB2 Universal
  Driver
• XPLINK(ON) is required with Universal Driver
• No need for DB2_HOME, if present, this is
  ignored.

48
Extending the JAVAENV file

• To extend JAVAENV DD size beyond 245 usable bytes
• Format using _CEE_ENVFILE environment variable

ENVAR("_CEE_ENVFILE/u/admf001/javasp/jspnolimit.t
xt", "JAVA_HOME/usr/lpp/java/IBM/J1.3"), MSGFILE(
JSPDEBUG,,,,ENQ)
CLASSPATH/u/admf001/javasp JCC_HOME/usr/lpp/db2/
db2810/jcc WORK_DIR/u/admf001/tmp DB2SQLJPROPERTI
ES/u/admf001/db2sqljjdbc.properties
Create HFS file /u/admf001/javasp/jspnolimit.
txt
49
Miscellaneous Java notes - 1

• JDK incompatibilities
• DS ships with JDK 1.5
• DB2 for z/OS V8 typically have JDK 1.3 or JDK 1.4
• Apply PTF for APAR PK09213 to allow a client Java
  SP built with JDK 1.5 to execute with a 1.5 JVM
• No problem if connecting to DB2 for z/OS V9
• JCC_HOME vs DB2_HOME
• If the SP will be connecting using the IBM
  Universal Driver, ensure the WLM proc points to a
  JAVAENV file that has the JCC_HOME set
• Otherwise, WLM proc should point to a JAVAENV
  file that has DB2_HOME set. (not supported in DB2
  9 for z/OS)

50
Miscellaneous Java notes - 2

• If server is DB2 for z/OS V8, and connecting
  using the legacy driver
• Check Build stored procedure on server
• DWB calls DSNTJSPP utility on server to build the
  Java Stored Procedures

51
Common problems and solutions
52
Unable to Connect

• Verify TCPIP setup
• Verify DDF on DB2 for z/OS is started
• Can set ZParm to automatically start DDF
• Capture LOCATION, HOST and PORT from the Display
  DDF output

53
SQLException on connect

• Cause The DB2Binder Utility was not run against
  the database.
• Solutioin When the DB2Binder Utility is run
  without the collection parameter, 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
  jdbcdb2//mig.null.washington.ibm.com446/DB2LOCN
  M -user myuser -password mypwd -collection ltmy
  collidgt

54
Performance problems during catalog loading

• Cause too many objects to load
• Solution
• Enable filtering at the database, schema or
  object level
• To filter out an entire object type (e.g.
  Tables), specify a schema that is non-existent.
  (e.g. )

55
Deploy problems - 1

• Symptom Deploy fails, sometimes with EC7009C
  abend
• Possible Solutions
• External SQL SP
• DSNTPSMP may be stopped issue START Proc
• The WLM assigned to SP is not available issue a
  VARY WLM, APPLENVRESUME
• The Preferences setting for default DSNTPSMP is
  incorrect
• Native SQL SP
• Invalid procedure options specified see SQL
  Reference
• Enable debug specified, but WLM proc is not
  specified or default WLM in zParm is blank check
  with System Admin

56
Deploy Problems - 2

• Symptom DSNTPSMP on z/OS 1.7 fails with
  message
• CBCDRVR is not found
• Solution
• Create a member in a library that is in the WLM
  proc STEPLIB, (e.g.USER.PRIVATE.PROCLIB(DWBPARM)
• In this dataset, add this line to define the
  Compiler to use
• C_COMPILER CCNDRVR
• Edit the WLM proc assigned to DSNTPSMP
• Activate, or uncomment, the CFGTPSMP DD line.
• CFGTPSMP DD DISPSHR,DSNUSER.PRIVATE.PROCLIB(DWB
  PARM)
• Refresh the WLM ENVIRONMENT assigned to DSNTPSMP,
  to pickup the revised JCL definition

57
Deploy Problems - 3

• Symptom Warning message from DS says that
  WLM_REFRSH failed
• Solution
• Run RACF job from DSNTEJ6W, step 1 to permit the
  DS login id access to the WLM_REFRSH resource
  class

58
SQL Statement with DECFLOAT failing

• Symptom SQL statement that contains DECFLOAT
  cannot be executed
• Solution
• Change the DWB JDK to JDK 1.5
• Edit eclipse.ini file, specify

- vm ltlocation of your JDK 1.5 driversgt
59
Binary Deploy problems

• Symptom EDC5139I Operation not permitted
• For external SQL SP, the connection login id is
  not authorized to issue JES2 commands.
• Run RACF job for DSNTIJCC
• For Java SP, the stored procedure may have
  initially been deployed using DSNTJSPP
• Redeploy the Java SP from DWB Connect DB2 for
  z/OS using the IBM DB2 Universal driver.

60
Execution problems for Java SPs

• The JDK level used to create and initially deploy
  the stored procedure is incompatible with the
  servers JDK level.
• Verify the JCC and JDK level at the server
• Use Java verification UDFs mentioned earlier to
  determine the versions on the server
• Verify the JCC and JVM versions at the client
  from Windows command prompt as follows

61
Native SQL SP shows up as 1 line

• SQLFormat parm in DB2 for z/OS V9 is not set to
  SQLPL

62
Unable to debug Java SPs

• JVM 1.42 default native heap is insufficient to
  use for debugging java stored procedures
• JVM 1.5 has a sufficient default native heap size
• JVM 1.42 heap needs to be increased to
  HEAP(8M,2M,ANYWHERE,KEEP) in JAVAENV settings.
  For example
• MSGFILE(JSPDEBUG,,,,ENQ),
  
• XPLINK(ON),
• HEAP(8M,2M,ANYWHERE,KEEP),
• ENVAR("_CEE_ENVFILE/u/oeusr05/CEEOPTIONS.txt")

63
Summary

• DS requires DB2 supplied stored procedures to be
  enabled before user can connect and use its
  features
• WLM environment must be set up first before
  customizing any of the tailoring jobs needed for
  DWB
• Customize tailoring jobs provided by DB2
• Dont forget to check the GRANTs and INSERTs in
  DSNTIJSG for the DB2 supplied stored procedures

CHECK THE REDBOOK DB2 FOR Z/OS STORED
PROCEDURES, THROUGH THE CALL AND BEYOND FOR
DETAILS
64
References - 1

• AVAILABLE IN 2008 DB2 9 for z/OS Stored
  Procedures, Give them a CALL Through the Network,
  SG24-7083-01
• DB2 for z/OS Stored Procedures Through the CALL
  and beyond
• http//publib-b.boulder.ibm.com/abstracts/sg247083
  .html?Open
• Application and Programming Guide for Java
• http//publib.boulder.ibm.com/epubs/pdf/dsnjvj10.p
  df
• Get your DB2 for z/OS system ready for Developer
  Workbench V9.2
• http//www.ibm.com/developerworks/db2/library/tech
  article/dm-0705scanlon/

65
References - 2

• DB2 Developer Workbench, Part 1 Developer
  Workbench concepts and basic tasks
• http//www-128.ibm.com/developerworks/edu/dm-dw-dm
  -0608eaton-i.html?S_TACT105AGX11S_CMPWN
• DB2 Developer Workbench, Part 2 Developer
  Workbench and stored procedures
• http//www-128.ibm.com/developerworks/edu/dm-dw-dm
  -0609eaton-i.html?S_TACT105AGX54cadnw-733