Miguel Fernandez
New User
Joined: 05 Dec 2016
Posts: 14
Location: Spain
|
|
|
|
Where can I find a PL1 compiler with embedded DB2 to run under the Linux operating system the following program which would query a table on Host z/OS through DB2 Connect ?
| Code: |
PLIEX: PROC OPTIONS(MAIN);
/*********************************************************************/
/* DCLGEN TABLE(EMP) */
/* LIBRARY(MIGUEL.DCLGEN.PLI) */
/* LANGUAGE(PLI) */
/* APOST */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS */
/* */
/*********************************************************************/
/* PLI DECLARATION FOR TABLE EMP */
/*********************************************************************/
EXEC SQL DECLARE EMP TABLE
( EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
HIREDATE DATE,
JOB CHAR(8),
EDLEVEL SMALLINT,
SEX CHAR(1),
BIRTHDATE DATE,
SALARY DECIMAL(9,2),
BONUS DECIMAL(9,2),
COMM DECIMAL(9,2)
) ;
/* Structure for Report 1 */
DCL 1 RPT1,
5 EMPNO CHAR(6),
5 FIRSTNME CHAR(12) VAR,
5 MIDINIT CHAR(1),
5 LASTNAME CHAR(15) VAR,
5 WORKDEPT CHAR(3),
5 PHONENO CHAR(4),
5 HIREDATE CHAR(10),
5 JOB CHAR(8),
5 EDLEVEL BIN FIXED(15),
5 SEX CHAR(1),
5 BIRTHDATE CHAR(10),
5 SALARY DEC FIXED(9,2),
5 BONUS DEC FIXED(9,2),
5 COMM DEC FIXED(9,2);
/*********************************************************************/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION */
/* TABLE EMP IS 14 COLUMNS INCLUDES ALL INTO FILE SALARIOS */
/* FOR PRINT ONLY 5 COLUMNS */
/* EMPNO, LASTNAME, SALARY, BONUS, COMM */
/* :EMPNO, :LASTNAME, :SALARY, :BONUS, :COMM */
/*********************************************************************/
DCL SYSPRINT FILE EXTERNAL OUTPUT STREAM PRINT;
DCL SALARIOS FILE RECORD OUTPUT ENVIRONMENT(FB RECSIZE(91));
EXEC SQL INCLUDE SQLCA;
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,
PHONENO, HIREDATE, JOB, EDLEVEL, SEX,
BIRTHDATE, SALARY, BONUS, COMM
FROM EMP
WHERE COMM >= 30
FOR FETCH ONLY; 00016100
OPEN FILE(SYSPRINT);
OPEN FILE(SALARIOS) OUTPUT;
EXEC SQL COMMIT;
EXEC SQL WHENEVER SQLERROR GO TO REPORT_ERROR;
/* Write out the header for Report 1 */
PUT File(Sysprint)
edit('Pgm: PGPL1SQL')
(SKIP,col(03),a);
PUT FILE(SYSPRINT)
edit('REPORTE DE PROYECTOS AFECTADOS POR AUMENTO A EMPLEADOS')
(col(22),a);
PUT FILE(SYSPRINT)
edit('SALARIO','BONUS','COMM','LEVEL','EMPNO','LASTNAME')
(SKIP,COL(7),A,X(14),A,X(15),A,X(7),A,X(7),A,X(6),A);
PUT FILE(SYSPRINT)
edit('-------','-----','----','-----','------','--------')
(SKIP,COL(7),A,X(14),A,X(15),A,X(7),A,X(6),A,X(6),A);
EXEC SQL
OPEN C1;
/* Fetch and write the rows to SYSPRINT */
EXEC SQL WHENEVER NOT FOUND GO TO DONE1;
DO UNTIL (SQLCODE ^= 0);
EXEC SQL
FETCH C1 INTO
:EMPNO, :FIRSTNME, :MIDINIT,
:LASTNAME, :WORKDEPT,
:PHONENO, :HIREDATE, :JOB,
:EDLEVEL, :SEX, :BIRTHDATE,
:SALARY, :BONUS, :COMM;
PUT FILE(SYSPRINT)
EDIT(SALARY,BONUS,COMM,EDLEVEL,EMPNO,LASTNAME)
(SKIP,COL(5),
F(9,2),X(10),F(9,2),X(10),F(9,2),X(10),F(2),X(6),A,X(6),A);
WRITE FILE(SALARIOS) FROM(RPT1);
END;
DONE1:
EXEC SQL
CLOSE C1;
GO TO FINISHED;
REPORT_ERROR:
PUT SKIP;
PUT FILE(SYSPRINT) EDIT('--> ERROR OCURRIDO AL GENERAR ALGUN '||
'REPORTE. SQLCODE=',SQLCODE)(A,F(5));
GO TO FINISHED;
/* All done */
FINISHED:
CLOSE FILE(SYSPRINT);
CLOSE FILE(SALARIOS);
RETURN;
END PLIEX;
|
| Code: |
********************************* TOP OF DATA **********************************
PGPL1SQL REPORTE DE PROYECTOS AFECTADOS POR AUMENTO A EMPLEADOS
ALARIO BONUS COMM LEVEL EMPNO LASTNA
------ ----- ---- ----- ------ ------
695.31 160.00 42.20 18 000010 HAAS
543.72 160.00 33.00 18 000020 THOMPS
504.16 160.00 30.60 20 000030 KWAN
529.52 160.00 32.14 16 000050 GEYER
612.90 90.00 37.20 19 000110 LUCCHE
612.90 100.00 42.20 18 200010 HEMMIN
******************************** BOTTOM OF DATA ********************************
|
| Code: |
┌────────────────────────────────────────────────────────────────────┐
│ • MobaXterm Personal Edition v4.0 • │
│ (Unix utilities and X-server on Gnu/Cygwin) │
│ │
│ → Your computer drives are accessible through the /drives path │
│ → Your DISPLAY is set to 10.99.1.82:0 │
│ → When using SSH, your remote DISPLAY is automatically forwarded │
│ → Each command status is specified by a special symbol (v or x) │
│ │
│ • Important: │
│ This is MobaXterm Personal Edition. The Professional edition │
│ allows you to customize MobaXterm for your company: you can add │
│ your own logo, your parameters, your welcome message and generate │
│ either an MSI installation package or a portable executable. │
│ We can also modify MobaXterm or develop the plugins you need. │
│ For more information: http://mobaxterm.mobatek.net/versions.php │
└────────────────────────────────────────────────────────────────────┘
Permanently added '10.99.2.112' (RSA) to the list of known hosts.
Last login: Mon Feb 13 14:56:22 2017 from wks00026.imcs.es
[root@svlrh6d07] ~ # su - db2inst1
[db2inst1@svlrh6d07 ~]$ db2 connect to DALLAS9 user IBMUSER using USERIBM
Información de la conexión con la base de datos
Servidor bases datos = DB2 z/OS 9.1.5
ID autorización SQL = IBMUSER
Alias base datos local = DALLAS9
[db2inst1@svlrh6d07 ~]$ db2 "SELECT SALARY, BONUS, COMM, EDLEVEL, EMPNO, LASTNAME
> FROM EMP
> WHERE COMM >= 30"
SALARY BONUS COMM EDLEVEL EMPNO LASTNAME
----------- ----------- ----------- ------- ------ ---------------
695,31 160,00 42,20 18 000010 HAAS
543,72 160,00 33,00 18 000020 THOMPSON
504,16 160,00 30,60 20 000030 KWAN
529,52 160,00 32,14 16 000050 GEYER
612,90 90,00 37,20 19 000110 LUCCHESI
612,90 100,00 42,20 18 200010 HEMMINGER
6 registro(s) seleccionado(s).
[db2inst1@svlrh6d07 ~]$
|
|
|
