Tutorial: Using the Db2 command line processor

You can interactively query and modify Db2 data, call stored procedures, and issue z/OS® UNIX System Services commands through the Db2 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 Db2 command line processor.
The Db2 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 Db2 command line processor properties file.
```
MYALIAS01=connection-url,user-id,password
```
CONNECT (Db2 command line processor) describes the syntax for connection-url,user-id, and password.
An alias of db2 for the command that starts the Db2 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 the Db2 command line processor for more information.

About this task

This tutorial demonstrates how to perform the same tasks in the Db2 command line processor that you can perform in SPUFI or DSNTEP2. In addition, this tutorial demonstrates how to call stored procedures from the Db2 command line processor.

In this tutorial, you will learn how to do the following Db2 command line processor tasks:

Start and stop the Db2 command line processor
List and modify Db2 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 input to the Db2 command line processor is mostly case insensitive, SQL statements and Db2 command line processor commands are shown in uppercase for consistency with other Db2 operations.

Procedure

Start the Db2 command line processor, and set Db2 command line processor options.

Start the Db2 command line processor by typing the predefined alias:
```
db2
```
The Db2 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 Db2 command line processor is a Java™ database application.

Check the current settings of the Db2 command line processor options to determine whether they are right for your Db2 command line processor session:

LIST COMMAND OPTIONS

A list of Db2 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
-u      Use user ID and password for input file      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 DSN8C10.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 DSN8C10.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 Db2 command line processor, and restart it with a different set of options.
1. Terminate the Db2 command line processor:
  Terminate the Db2 command line processor:
```
TERMINATE
```
  Control returns to z/OS UNIX System Services.
2. Start the Db2 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 =>
```
3. Change the Db2 command line processor option for saving output to a file. Issue the following command to direct the Db2 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#
```
4. 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 Db2 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.
1. Change the Db2 command line processor option for saving output to a file back to its original setting of OFF:
```
UPDATE COMMAND OPTIONS USING z OFF#
```
2. Drop the SQL objects that you created, and commit the changes.
```
DROP TABLE MY_EMP#
```
```
DROP PROCEDURE CALC_SAL#
```
```
COMMIT#
```
3. Delete the file that contains Db2 command line processor output.
```
!rm clplog.txt#
```
4. Terminate the Db2 command line processor session:
```
TERMINATE#
```
  The Db2 command line processor session ends.