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
Srinimf 