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 start this tutorial, make sure that your system administrator has customized your environment appropriately:
Command line processor CONNECT command describes the syntax for connection-url,user-id, and password.
alias db2="java com.ibm.db2.clp.db2"
See Setting up your system to use the DB2 command line processor for more information.
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:
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.
The command line processor starts, and the following prompt is displayed:
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.
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.
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 =>
CREATE TABLE MY_EMP LIKE DSN8A10.EMP
INSERT INTO MY_EMP SELECT * FROM DSN8A10.EMP
CHANGE MAXCOLUMNWIDTH TO 12
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:
Control returns to z/OS UNIX System Services.
db2 -td# db2 =>
UPDATE COMMAND OPTIONS USING z ON clplog.txt#
CONNECT to MYALIAS01#
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#
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 =>
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 =>
UPDATE COMMAND OPTIONS USING z OFF#
DROP TABLE MY_EMP#
DROP PROCEDURE CALC_SAL#
The command line processor session ends.