Launching DB2 Command Line Processor (CLP) from UNIX System Services (USS)

Launching DB2 Command Line Processor (CLP) from UNIX System
Services (USS)
Intended Audience for this document is users who have basic knowledge of ‘DB2 on
UNIX’ environment and ‘USS’.
Purpose served by this document:
In a project development life cycle of a software product on z/OS, user has to set
database environment for testing its various functionalities. In Windows environment,
user has DB2 Client’s GUI interface or Command Line Processor command prompt to
issue SQL statements for creating databases, tables or to run a database cleanup script.
In general, USS does not provide DB2 Command Line Processor support with default
DB2 installation on it. So in order to set DB2 Command Line Processor environment,
user has to set some java paths and variables and this document explains each step in a
simple and user friendly way with example.
UNIX System Services is a UNIX operating system environment which is installed and
implemented within the z/OS operating system. z/OS UNIX System Services (USS) is
also referred by its shorter name z/OS UNIX
DB2 Command Line Processor is used to issue SQL statements, bind DBRMs
(DataBase Request Module) that are stored in HFS (Hierarchical File System) files, and
for calling stored procedures. The Command Line Processor on DB2 for z/OS is a JAVA
application that runs under UNIX System Services (USS).
Environment setup for DB2 CLP on z/OS USS is a simple task if users system meets all
prerequisites.
Prerequisites for Command Line Processor:
Before setting DB2 Command Line Processor on your system, the system must meet
below prerequisites
a) DB2 Command Line Processor should be installed under base directory (e.g.
/ZOS111/usr/lpp/db2910base)
b) Java 1.5 should be installed on system.
c) IBM DB2 JDBC Drivers should be present on the system.
In order to set DB2 CLP environment on your system follow below steps (System
Environment variable values may vary from system to system).
1) Set JAVA_HOME variable in your .profile file up to directory J5.0 as below
export JAVA_HOME="/ZOS111/usr/lpp/java/J142SR5/J5.0/"
2) Add JAVA_HOME bin directory to your path variable in .profile file as below
export PATH="JAVA_HOME/bin:$PATH"
3) Set JDBCHOME variable to DB2 CLP installation location in .profile file as
below
export JDBCHOME="/ZOS111/usr/lpp/db2910base"
Rohit Kumar Singh
November 11, 2010
[email protected]
4) Set JDBCCLASSES variable to ‘classes’ directory in .profile file as below
export JDBCCLASSES="${JDBCHOME}/classes"
5) Set LIBPATH variable to ‘lib’ directory in .profile file as below
export LIBPATH=${JDBCHOME}/lib
6) Set JDBC driver files path to CLASSPATH variable in .profile file as below
export
CLASSPATH=${JDBCCLASSES}:${JDBCCLASSES}/db2jcc.jar:${JDBCCLASS
ES}/db2jcc_license_cisuz.jar:${JDBCCLASSES}/db2jcc_javax
.jar:${CLASSPATH}
7) Modify CLASSPATH variable to include ‘clp.jar’ file in .profile file as below
export CLASSPATH=$CLASSPATH:/ZOS111/usr/lpp/db2910base/lib/clp.jar
8) Define the CLPPROPERTIESFILE environment variable in your .profile file to
include the full path name where the properties file is located. A sample
properties file is in the directory where the command line processor is installed.
Define CLPPROPERTIESFILE variable as below in .profile file
export
CLPPROPERTIESFILE=/ZOS111/usr/lpp/db2910base/samples/clp.properties
9) Define following alias in your .profile file as below and save all above changes
in .profile file.
alias db2="java com.ibm.db2.clp.db2"
Refer below screen shot as a sample of above settings in .profile file.
Add your database entry with login details in ‘clp.properties’ file located at location
defined in .profile variable ‘CLPPROPERTIESFILE’. Refer red box in below screen
shot as an example. User can add as many entries as possible for different databases.
Rohit Kumar Singh
November 11, 2010
[email protected]
Execute .profile file or open another session so that your changes in .profile file will
take effect for user’s environment. Issue ‘db2’ and ‘connect’ commands from USS
console to connect database as given in below screen
If you want to exit from db2 prompt, issues ‘TERMINATE’ command.
Add db2 connect command inside SQL file, if user wants to run SQL file from USS
command prompt. Below screen shots will provide some guidance for adding connect
command and running SQL file form USS command prompt.
Created by: Rohit K Singh2/India/IBM@IBMIN
Rohit Kumar Singh
November 11, 2010
Date: 11 November 2010
[email protected]
Rohit Kumar Singh
November 11, 2010
[email protected]