IBM Data Management magazine Issue 2, 2009

(0x400) Collection methods

(0x200) Character literals (0x100)

(0x40) (0x80) (0x20) (0x10) (0x02) (0x04)

(0x08)

Bit pos

Table 1: DB2_COMPATIBILITY_VECTOR values

ition Feature Explanation

(0x01) RO WNUM Enables the use of RO WNUM as a synonym for RO W_

NUMBER() OVER( ) and permits RO WNUM to appear in the WHERE clause of SQL statements.

Enables the DUAL dummy table.

Enables support for the outer join operator (+).

Enables support for hierarchical queries using the

CONNECT BY clause.

Enables the NUMBER data type and associated numeric processing.

Enables the VARCHAR2 data type and associated character string processing.

A combined date and time value.

Enables alternate semantics for the TRUNCATE statement under which IMMEDIATE is an optional keyword that is assumed to be the default if not specified. An implicit commit operation is performed before the TRUNCATE statement executes if the TRUNCA TE statement is not the first statement in the logical unit of work.

Enables the assignment of the CHAR or GRAPHIC data type (instead of the VARCHAR or VARGRAPHIC data type) to character and graphic string constants whose byte length is less than or equal to 254.

Enables the use of methods to perform operations on arrays, such as first, last, next, and previous. Also enables the use of parentheses in place of square brackets in references to specific elements in an array; for example, array1(i) refers to element i of array1.

Enables the creation of data dictionary–compatible views.

DUAL Outer join operator

Hierarchical queries NUMBER data type VARCHAR2 data type DATE data type TRUNCATE TABLE

(0x800)

Data dictionary– compatible views

PL/SQL compilation

Enables the compilation and execution of PL/SQL statements and language elements.

PL/SQL features

We will now explore the features that allow you to create PL/SQL procedures. DB2 9. 7 supports the following:

User-defined packages `

PL/SQL procedures/functions/anonymous blocks ` Built-in packages ( ` DBMS_OUT.PUT_LINE, DBMS_PIPE ,

DBMS_U TL, and so on)

Associative ( ` INDEX BY) arrays

Varrays `

Triggers `

%ROWTYPE `

%TYPE `

…

EXCEPTIONS `

Ref-Cursors `

Note that PL/SQL support is available only in the Enterprise Server Edition and Workgroup Edition of DB2 9. 7 for LUW.

PL/SQL is not supported in DB2 Express-C, DB2 Express, or DB2 Personal Edition.

PL/SQL procedures in DB2

DB2 supports much of the PL/SQL language. Let’s see how this works with some sample code. Once it is compiled and executed, this PL/SQL procedure displays a welcome message to the user. To see the output on the console, first issue the command SET SERVEROUTPUT ON.

CREATE OR REPLACE PROCEDURE message_proc (myname varchar2) IS

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Welcome to DB2 9. 7 ()! ‘ || myname || ‘. Now supports for ORACLE SQL and PL/SQL as well :-)!’);

END message_proc;

To call this PL/SQL procedure in DB2, issue the following commands:

EXEC proc_name

EXEC message_proc(‘Anil’);

Here is an example that uses a FOR loop:

BEGIN

END LO

FOR a IN 10 .. 20 LOOP

DBMS_OUTPUT.PUT_LINE(‘Counter ‘ || a); OP;

END;

This loop will produce the following output:

Counter 10

Counter 20

The TRUNCATE TABLE command Those of you with an Oracle database background have probably used the command TRUNCATE TABLE at some point in your careers. DB2 now supports this command as well. The following code creates a simple table, adds rows to it, and then uses the TRUNCATE command to remove all the rows from the table: