You can interactively query and modify DB2® data, call stored procedures, and issue z/OS®
UNIX System Services commands through the command
line processor. This lesson demonstrates how to perform those tasks.
Before you begin
Before you start this tutorial, make sure that your system administrator has customized your
environment appropriately:
- Your z/OS
UNIX System Services .profile file needs to be
set up so that you can use the command line processor.
- The command line processor properties file needs to include the alias MYALIAS01 for your
connection information. A line of the following form must exist in the command line processor
properties
file.
MYALIAS01=connection-url,user-id,password
Command line processor CONNECT command describes the syntax for
connection-url,user-id, and
password.
- An alias of db2 for the command that starts the command line processor must be
created:
alias db2="java com.ibm.db2.clp.db2"
- The database server to which you connect must not be a data sharing system.
See Setting up your system to use the DB2 command line processor for more information.
About this task
This tutorial demonstrates how to perform the same tasks in the command line processor that you
can perform in SPUFI or DSNTEP2. In addition, this tutorial demonstrates how to call stored
procedures from the command line processor.
In this tutorial, you will learn how to do the following command line processor tasks:
- Start and stop the command line processor
- List and modify command line processor options
- Connect to a database server
- Execute SQL statements to create, populate, query and drop a table
- Create a stored procedure
- Call a stored procedure
Conventions used in this tutorial: Although command line processor input is mostly case
insensitive, SQL statements and command line processor commands are shown in uppercase for
consistency with other DB2 operations.
Procedure
- Start the command line processor, and set command line processor options.
- Start the command line processor by typing the predefined alias:
db2
The command line processor starts, and the following prompt is displayed:
db2 =>
- Connect to a DB2 database server using the predefined connection alias:
CONNECT to MYALIAS01
The connection is established, and the following information about the connection is
displayed:
Database Connection Information
Database server =Db2 server version
SQL authorization ID =User ID
JDBC Driver =JDBC driver name and version
DSNC101I : The "CONNECT" command completed successfully.
db2 =>
Information about the JDBC driver is displayed because the command line processor is a Java database application.
- Check the current settings of the command line processor options to determine whether
they are right for your command line processor session:
LIST COMMAND OPTIONS
A list of command line processor options and their current settings is displayed:
Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit ON
-f Read from input file OFF
-o Display output ON
-s Stop execution on command error OFF
-t Set statement termination character OFF
-v Echo current command OFF
-x Suppress printing of column headings OFF
-z Save all output to output file OFF
DSNC101I : The "LIST COMMAND OPTIONS" command completed successfully.
db2 =>
The options are described in Table 1.
- Suppose that you want to perform commit operations explicitly, so you want to disable
autocommit. To do that, you need to set the c option to OFF:
UPDATE COMMAND OPTIONS USING c OFF
The following message is displayed, which indicates that you have successfully disabled
the autocommit option:
DSNC101I : The "UPDATE COMMAND OPTIONS" command completed successfully.
db2 =>
- Run SQL statements to query and modify data.
- Run an SQL CREATE statement to create a copy of sample employee table:
CREATE TABLE MY_EMP LIKE DSN8A10.EMP
- Run an SQL INSERT statement to populate your copy of the sample employee table from the
sample employee table:
INSERT INTO MY_EMP SELECT * FROM DSN8A10.EMP
- Commit the previously run SQL operations:
COMMIT
- To make the output easier to read on the screen, set the maximum width of any column data
that is returned to 12:
CHANGE MAXCOLUMNWIDTH TO 12
- Run this SELECT statement, to ensure that the table was created and the rows were
inserted successfully:
SELECT FIRSTNME, MIDINIT, LASTNAME FROM MY_EMP ORDER BY LASTNAME
Rows from table MY_EMP are displayed:
FIRSTNME MIDINIT LASTNAME
BRUCE ADAMSON
ROY R ALONZO
DAVID BROWN
JOHN B GEYER
JASON R GOUNOT
...
MICHAEL L THOMPSON
JAMES H WALKER
HELENA WONG
KIYOSHI YAMAMOTO
MASATOSHI J YOSHIMURA
42 record(s) selected
db2 =>
- Terminate the command line processor, and restart it with a different set of options.
- Terminate the command line processor:
Terminate the command line processor:
TERMINATE
Control returns to z/OS
UNIX System Services.
- Start the command line processor with the statement termination character set to the
pound sign (#), so that you can terminate statements within an SQL PL procedure with
semicolons (;):
db2 -td#
db2 =>
- Change the command line processor option for saving output to a file. Issue the following
command to direct the command line processor to save the output to a file named clplog.txt,
and to display the output on the screen:
UPDATE COMMAND OPTIONS USING z ON clplog.txt#
- Connect to a DB2 database server using the predefined connection alias:
CONNECT to MYALIAS01#
- Create and call a stored procedure. Save the output to a file.
- Create stored procedure CALC_SAL.
Type the following lines on the display. Press Enter after you type each line. The command
line processor considers all text up to the pound sign (#) to be a single statement.
CREATE PROCEDURE CALC_SAL(INOUT EMPNO_IN CHAR(6),
IN PCT_RAISE DECIMAL(2,2),
OUT EMPNAME VARCHAR(15),
OUT SAL_CALC DECIMAL(9,2))
LANGUAGE SQL
BEGIN
DECLARE CURRENT_SALARY DECIMAL(9,2) DEFAULT 0;
SELECT LASTNAME, SALARY INTO EMPNAME, CURRENT_SALARY FROM MY_EMP
WHERE EMPNO=EMPNO_IN;
SET SAL_CALC=CURRENT_SALARY*PCT_RAISE+CURRENT_SALARY;
END#
- Call the CALC_SAL stored procedure to find out what the salary of employee '000100' will
be if you give the employee a 5% raise:
CALL CALC_SAL('000100',.05,?,?)#
The following output is displayed:
Value of output parameters
--------------------------
Parameter Name : EMPNO_IN
Parameter Value : 000100
Parameter Name : EMPNAME
Parameter Value : SPENSER
Parameter Name : SAL_CALC
Parameter Value : 27457.50
DSNC101I : The "CALL" command completed successfully.
db2 =>
- Run the z/OS
UNIX System Services cat command
from within the command line processor to view the contents of the clplog.txt file. The
exclamation mark (!) at the beginning of a statement indicates that it is a z/OS
UNIX System Services command.
!cat clplog.txt#
The contents of the clplog.txt file are displayed:
DSNC101I : The "UPDATE COMMAND OPTIONS" command completed successfully.
Value of output parameters
--------------------------
Parameter Name : EMPNO_IN
Parameter Value : 000100
Parameter Name : EMPNAME
Parameter Value : SPENSER
Parameter Name : SAL_CALC
Parameter Value : 27457.50
DSNC101I : The "CALL" command completed successfully.
db2 =>
- Clean up all objects that you created for this tutorial, and exit the command line
processor.
- Change the command line processor option for saving output to a file back to its original
setting of OFF:
UPDATE COMMAND OPTIONS USING z OFF#
- Drop the SQL objects that you created, and commit the changes.
DROP TABLE MY_EMP#
DROP PROCEDURE CALC_SAL#
COMMIT#
- Delete the file that contains command line processor output.
!rm clplog.txt#
- Terminate the command line processor session:
TERMINATE#
The command line processor session ends.