Information Management India - Bloggers Guild

The supported interfaces include:

* DB2 command line processor (CLP)

* DB2 CLPPlus

* IBM® Data Studio full client

The DB2 Command Line Processor (CLP) is a command line interface and tool available with both DB2 servers and DB2 clients from which DB2 commands can be issued, SQL statements executed, and utilities run. The CLP is essentially a command processor or shell environment that is customized for working with DB2.The DB2 CLP can be used as a primary interface for interacting with DB2 instances and databases, as an alternative to using DB2 graphical user-interfaces, or as an interface for occasional use. The db2 command starts the command line processor (CLP).

We will learn to execute PLSQL statements through Interactive input mode, characterized by the

db2 => input prompt.

PL/SQL statement execution is not enabled from these interfaces by default. PL/SQL statement execution support must be enabled on the DB2 data server.

Let us see how to enable PLSQL statements support in DB2 v9.7 and then write sample PLSQL programs and learn how to compile and execute them through DB2 CLP command window.

1. Enable PLSQL statements in DB2 environments

# Open a DB2 command window.

# Start the DB2 database manager.

db2start

# Set the DB2_COMPATIBILITY_VECTOR registry variable to the hexadecimal value ORA that enables the compatibility features that you want to use.

db2set DB2_COMPATIBILITY_VECTOR=ORA

# Set the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to YES to enable deferred prepare support.

db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

# Issue the db2stop command and the db2start command to stop and then restart the database manager.

db2stop

db2start

After the above steps are executed successfully we may continue to create database and database objects like tables, indexes, views, sequences etc. The steps to create these will not be discussed as the focus would be on the PLSQL statements creation and execution.

Remember to execute the below db2 update cfg command immediately after the database is created and before creating any db objects like tables, indexes etc

db2 update db cfg for <dbname> using AUTO_REVAL DEFERRED_FORCE

db2 update db cfg for <dbname> using DECFLT_ROUNDING ROUND_HALF_UP

In this section we shall learn to execute PLSQL procedures using simple datatype variables, record types and associative arrays as INPUT variables to the procedures using DB2 CLP.

2. Create and Execute a PLSQL procedure

   The below connection steps will be one time setup for the following SQL statements.

db2 connect to mydb

db2 set schema DEMO

db2 set PATH=SYSTEM PATH, 'DEMO'

db2 -td@

Here '@' is the statement termination character.

a. PLSQL procedure with simple datatype variable as IN parameter using DB2 CLP

db2 => CREATE TABLE emp (

name VARCHAR2(10),

salary NUMBER ) @

db2 => CREATE OR REPLACE PROCEDURE fill_comp( p_name IN VARCHAR,

p_sal IN NUMBER,

msg OUT VARCHAR)

AS

BEGIN

insert into emp VALUES (p_name, p_sal) ;

msg := 'The procedure inserts values into emp table:' ;

END fill_comp;

db2=> CALL fill_comp('Paul',200)@

The SQL command exceuted successfully

The procedure inserts values into emp table

b. PLSQL procedure with record type as IN parameter

db2=> CREATE OR REPLACE PACKAGE REC_PKG

AS

TYPE EMP_OBJ IS RECORD(

NAME VARCHAR(200),

ADDR VARCHAR(200),

PHONE NUMBER);

PROCEDURE fill_comp( IN_EMP IN EMP_OBJ, stat OUT VARCHAR2);

END @

db2 => CREATE OR REPLACE PACKAGE BODY REC_PKG

AS

PROCEDURE fill_comp( IN_EMP IN EMP_OBJ,

stat OUT VARCHAR2)

AS

BEGIN

insert into employee values(IN_EMP.NAME,

IN_EMP.ADDR,

IN_EMP.PHONE);

stat := ' This procedure inserts into employee';

END fill_comp;

END REC_PKG @

db2=> SET SERVEROUTPUT ON@

DECLARE

EMP_VAL REC_PKG.EMP_OBJ;

status VARCHAR2(30);

BEGIN

EMP_VAL.NAME := 'Paul';

EMP_VAL.ADDR := 'Sandiego';

EMP_VAL.PHONE := 1234;

REC_PKG.fill_comp(EMP_VAL,status);

DBMS_OUTPUT.PUT_LINE(status);

@

db2=> The SQL command completed successfully

This procedure inserts into employee

100. PLSQL procedure with Array type as IN parameter

db2=> CREATE OR REPLACE PACKAGE REC_PKG

AS

TYPE EMP_OBJ IS RECORD(

NAME VARCHAR(200),

ADDR VARCHAR(200),

PHONE NUMBER);

TYPE emp_array is table of EMP_OBJ INDEX BY BINARY_INTEGER;

PROCEDURE fill_comp( IN_EMP_ARR IN emp_array, stat OUT VARCHAR2);

END @

db2 => CREATE OR REPLACE PACKAGE BODY REC_PKG

AS

PROCEDURE fill_comp( IN_EMP_ARR IN emp_array ,

stat OUT VARCHAR2)

AS

BEGIN

FOR i IN 1 .. IN_EMP_ARR.COUNT

LOOP

insert into employee values(IN_EMP_ARR(i).NAME,

IN_EMP_ARR(i).ADDR,

IN_EMP_ARR(i).PHONE);

END LOOP;

stat := ' This procedure inserts into employee from an array';

END fill_comp;

END REC_PKG @

db2=> SET SERVEROUTPUT ON@

DECLARE

EMP_ARR REC_PKG.emp_array;

status VARCHAR2(30);

BEGIN

EMP_ARR(1).NAME := 'Mike;

EMP_ARR(1).ADDR := 'Paris';

EMP_ARR(1).PHONE := 4367;

REC_PKG.fill_comp(EMP_ARR,status);

DBMS_OUTPUT.PUT_LINE(status);

@

db2=> The SQL command completed successfully

This procedure inserts into employee from an array

This topic helps you to compile and execute PLSQL procedures, packages from DB2 CLP interactive input mode. I have tried to explain through examples, the most frequently used PLSQL procedure formats and how to perform runtime testing or execute them in DB2 CLP.