PLI-DB2 Program Coding

I am giving here the best ways of coding with PL1 and DB2 programs in mainframe environment.

How to include  DCLGEN…

EXEC SQL
INCLUDE member-name
END-EXEC.

How to compile the program…

DB2-PL1 program you can compile using Chgman/Endevor.

Some of the best examples collected from “IBM”. The below tips highly useful for developers and who attend for job interviews can use for instant knowledge.

PL1-DB2 examples

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
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.