PLI program using DB2 top ideas

I have given the best ways to write a PL1 program using DB2 for mainframe projects.

How to include  DCLGEN…

EXEC SQL
INCLUDE member-name
END-EXEC.

How to compile the program…

You can compile the PLI – DB2 program using either Chgman/Endevor tool.

I have collected some examples from IBM. The below tips are highly useful for developers who will attend for interviews and to get instant knowledge as well.

Best examples to use DB2 logic in PLI programs

How to write SQL query for cursors.

Example 1

In a PL/I program, use the cursor C1 to fetch the values for a given project (PROJNO) from the first four columns of the EMP_ACT table a row at a time and put them into the following host variables: EMP (char(6)), PRJ (char(6)), ACT (smallint), and TIM (dec(5,2)).

EXEC SQL  BEGIN DECLARE SECTION; 
DCL EMP CHAR(6); DCL PRJ CHAR(6); 
DCL SEARCH_PRJ CHAR(6);
DCL ACT BINARY FIXED(15); 
DCL TIM DEC FIXED(5,2); 
EXEC SQL END DECLARE SECTION;
     .
     .
     .
EXEC SQL  DECLARE C1 CURSOR FOR
       SELECT EMPNO, PROJNO, ACTNO, EMPTIME FROM EMP_ACT
       WHERE PROJNO = :SEARCH_PRJ; EXEC SQL OPEN C1; 
EXEC SQL FETCH C1 
INTO :EMP, :PRJ, :ACT, :TIM; 
IF SQLSTATE = '02000' THEN CALL DATA_NOT_FOUND; 
    ELSE DO WHILE (SUBSTR(SQLSTATE,1,2) = '00' | SUBSTR(SQLSTATE,1,2) = '01'); 
EXEC SQL 
FETCH C1 
INTO :EMP, :PRJ, :ACT, :TIM; END; 
EXEC SQL CLOSE C1;

Example 2

In a PL/I program, declare a cursor named INCREASE to return from the EMPLOYEE table all the employee numbers (EMPNO), surnames (LASTNAME) and price (SALARY increased by 10 percent) of people who have the job of clerk (JOB).

Order the result table in descending order by the increased salary.

   EXEC SQL  DECLARE INCREASE CURSOR FOR
               SELECT EMPNO, LASTNAME, SALARY * 1.1 FROM EMPLOYEE
                 WHERE JOB = 'CLERK'
                 ORDER BY 3 DESC;

Example 3

In a PL/I program, declare a cursor named UP_CUR to update all the columns of the DEPARTMENT table.

   EXEC SQL  DECLARE UP_CUR CURSOR FOR
             SELECT *
             FROM DEPARTMENT
             FOR UPDATE OF DEPTNO, DEPTNAME, MGRNO, ADMRDEPT;

Example 4

In a PL/I program, declare a cursor named DEL_CUR to examine, and potentially delete, rows in the DEPARTMENT table.

   EXEC SQL  DECLARE DEL_CUR CURSOR FOR
               SELECT *
                 FROM DEPARTMENT;

Example 5

This example is similar to Example 1. The difference is that the right-hand side of the WHERE clause is to be specified dynamically; thus the entire select-statement is placed into a host variable and dynamically prepared.

EXEC SQL  BEGIN DECLARE SECTION; DCL EMP CHAR(6); DCL PRJ CHAR(6); DCL SEARCH_PRJ CHAR(6); 
DCL ACT BINARY FIXED(15); DCL TIM DEC FIXED(5,2);
DCL SELECT_STMT CHAR(200) VARYING; 
EXEC SQL END DECLARE SECTION; SELECT_STMT = 'SELECT EMPNO, PROJNO, ACTNO, 
     EMPTIME ' || 'FROM EMP_ACT ' || 'WHERE PROJNO = ?'; . . . 
EXEC SQL PREPARE SELECT_PRJ FROM :SELECT_STMT;

EXEC SQL  DECLARE C1 CURSOR FOR SELECT_PRJ;
EXEC SQL  OPEN C1 USING :SEARCH_PRJ;
EXEC SQL  FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM; IF SQLSTATE = '02000' THEN CALL DATA_NOT_FOUND;
     ELSE DO WHILE (SUBSTR(SQLSTATE,1,2) = '00' | SUBSTR(SQLSTATE,1,2) = '01'); 
     EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;
END;
EXEC SQL  CLOSE C1;

Example 6

The DECLARE CURSOR statement associates the cursor name C1 with the results of the SELECT. C1 is an updateable, scrollable cursor.

   EXEC SQL DECLARE C1 DYNAMIC SCROLL CURSOR FOR
       SELECT DEPTNO, DEPTNAME, MGRNO FROM CORPDATA.TDEPT
       WHERE ADMRDEPT = 'A00';

Example 7

Declare a cursor in order to fetch values from four columns and assign the values to host variables using the Serializable (RR) isolation level:

  DECLARE CURSOR1 CURSOR FOR
  SELECT COL1, COL2, COL3, COL4 FROM TBLNAME WHERE COL1 = :varname WITH RR

• PLI is one of the popular language in Mainframe

Recent Posts