Information Management India - Bloggers Guild

DB2 9.7 PLSQL Support Part II : Compile and Execute DB2 PLSQL statements using scripts in DB2 command mode
RamyaYeleswarapu
Tags: 
compilation
packages
plsql
execute
db
windows
clp
command
db2
mode
4,726 Views
Please refer to the below link for this blog content https://www.ibm.com/developerworks/mydeveloperworks/blogs/DB2PLSQL/entry/db2_9_7_plsql_support_part_ii_compile_and_execute_db2_plsql_statements_using_scripts_in_db2_command_mode1?lang=en
|
DB2 v9.7 PLSQL Support - Compile & Execute PLSQL using DB2 CLP Interactive Input ModePL/SQL
(Procedural Language/Structured Query Language) statements can be
compiled and executed using DB2 interfaces. This support reduces the
complexity of enabling existing PL/SQL solutions so that they will
work with the DB2 data server.
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.
# 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.
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
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. Was this useful? Would you like to know more about DB2 PLSQL?
Happy Reading!! Cheers!! Ramya Yeleswarapu
|