DB2 9.1.0 not move values to Host-Variables on PL1 3.7.0

IBM Mainframe Forums -> DB2

Quick References

View previous topic :: View next topic

Author

Message

Miguel Fernandez

New User

Joined: 05 Dec 2016
Posts: 14
Location: Spain

Posted: Wed Jan 25, 2017 8:24 pm

I have tested the program written in PL1-DB2 with the SELECT & FETCH clauses where host variables whose TYPES are declared as characters lose the CONTENT of their value when executing the EXEC SQL statement from program PL1. However the variables declared as numerical are fully deployed with their true values that are defined in the columns of the consulted table. Is this an error in PL1-DB2 versions that need to be repaired or is it a bad programming? [img] OUTPUT 1 REPORTE DE PROYECTOS AFECTADOS AL AUMENTO DE SALARIOS EMPLEADO NOMBRE-EMPLEADO SALARIO BONUS COMM ÍÍÍÍºÍ ‡ÿÿ‰ 695.31 160.00 42.20 -RPT1.EMPNO='ÍÍÍÍºÍ' RPT1.FIRSTNME='„‡ˆ¤‰Š¤+ ' RPT1.MIDINIT='¤' RPT1.LASTNAME='‡ÿÿ‰' RPT1.WORKDEPT='ÿÍÍ' RPT1.PHONENO='¼Ë¹' RPT1.HIREDATE='ºËÌÉÍºÍº' RPT1.JOB='&ˆ ‰ÄÄÄÄ' RPT1.EDLEVEL= 18 RPT1.SEX='Æ' RPT1.BIRTHDATE='ºË¼¼Í¹º»' RPT1.SALARY= 695.31 RPT1.BONUS= 160.00 RPT1.COMM= 42.20; [/img]

Miguel Fernandez

New User

Joined: 05 Dec 2016
Posts: 14
Location: Spain

Code:

1 REPORTE DE PROYECTOS AFECTADOS AL AUMENTO DE SALARIOS
EMPLEADO NOMBRE-EMPLEADO SALARIO BONUS COMM
ÍÍÍÍºÍ ‡ÿÿ‰ 695.31 160.00 42.20
-RPT1.EMPNO='ÍÍÍÍºÍ' RPT1.FIRSTNME='„‡ˆ¤‰Š¤+ ' RPT1.MIDINIT='¤' RPT1.LASTNAME='‡ÿÿ‰'
RPT1.WORKDEPT='ÿÍÍ' RPT1.PHONENO='¼Ë¹' RPT1.HIREDATE='ºËÌÉÍºÍº' RPT1.JOB='&ˆ ‰ÄÄÄÄ'
RPT1.EDLEVEL= 18 RPT1.SEX='Æ' RPT1.BIRTHDATE='ºË¼¼Í¹º»' RPT1.SALARY= 695.31
RPT1.BONUS= 160.00 RPT1.COMM= 42.20;

Miguel Fernandez

New User

Joined: 05 Dec 2016
Posts: 14
Location: Spain

Code:

1DB2 SQL PRECOMPILER VERSION 9 REL. 1.0 PAGE 2
0 1 PLIEX: PROC OPTIONS(MAIN);
2
3 DCL SYSPRINT FILE EXTERNAL OUTPUT STREAM PRINT;
4
5 EXEC SQL INCLUDE SQLCA;
6
7 EXEC SQL BEGIN DECLARE SECTION;
8 DCL 1 RPT1,
9 15 EMPNO CHAR(6) INIT (''),
10 15 FIRSTNME CHAR(12) VAR INIT (''),
11 15 MIDINIT CHAR(1) INIT (''),
12 15 LASTNAME CHAR(15) VAR INIT (''),
13 15 WORKDEPT CHAR(3) INIT (''),
14 15 PHONENO CHAR(4) INIT (''),
15 15 HIREDATE CHAR(10) INIT (''),
16 15 JOB CHAR(8) INIT (''),
17 15 EDLEVEL FIXED(15) BINARY INIT(0),
18 15 SEX CHAR(1) INIT (''),
19 15 BIRTHDATE CHAR(10) INIT (''),
20 15 SALARY FIXED DECIMAL (9,2) INIT (0),
21 15 BONUS FIXED DECIMAL (9,2) INIT (0),
22 15 COMM FIXED DECIMAL (9,2) INIT (0);
23 EXEC SQL END DECLARE SECTION;
24
25 EXEC SQL DECLARE EMP TABLE
26 (EMPNO CHAR(6) NOT NULL,
27 FIRSTNME VARCHAR(12) NOT NULL,
28 MIDINIT CHAR(1) NOT NULL,
29 LASTNAME VARCHAR(15) NOT NULL,
30 WORKDEPT CHAR(3) NOT NULL,
31 PHONENO CHAR(4) NOT NULL,
32 HIREDATE DATE NOT NULL,
33 JOB CHAR(8) NOT NULL,
34 EDLEVEL SMALLINT NOT NULL,
35 SEX CHAR(1) NOT NULL,
36 BIRTHDATE DATE NOT NULL,
37 SALARY DECIMAL(9,2) NOT NULL,
38 BONUS DECIMAL(9,2) NOT NULL,
39 COMM DECIMAL(9,2) NOT NULL);
40
41 OPEN FILE(SYSPRINT);
42 /* EMPNO, LASTNAME, SALARY, BONUS, COMM */
43 /* :EMPNO, :LASTNAME, :SALARY, :BONUS, :COMM */
44
45 EXEC SQL WHENEVER SQLERROR GO TO SELECT_ERROR;
46
47 EXEC SQL SELECT
48 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,
49 PHONENO, HIREDATE, JOB, EDLEVEL, SEX,
50 BIRTHDATE, SALARY, BONUS, COMM
51 INTO
52 :RPT1
53 FROM
54 EMP
55 WHERE SALARY = 695.31;
56
57 EXEC SQL COMMIT;
58
1DB2 SQL PRECOMPILER VERSION 9 REL. 1.0 PAGE 3
0 59 EXEC SQL WHENEVER SQLERROR GO TO REPORT_ERROR;
60
61 /* Write the header */
62 PUT FILE(SYSPRINT)
63 EDIT('REPORTE DE PROYECTOS AFECTADOS AL AUMENTO DE SALARIOS')
64 (COL(22),A);
65 PUT FILE(SYSPRINT)
66 EDIT('EMPLEADO','NOMBRE-EMPLEADO','SALARIO','BONUS','COMM')
67 (SKIP,COL(7),A,X(13),A,X(6),A,X(7),A,X(8),A);
68 PUT FILE(SYSPRINT)
69 EDIT(EMPNO,LASTNAME,SALARY,BONUS,COMM)
70 (SKIP,COL(5),A,X(10),A,X(22),F(9,2),X(3),F(9,2),X(3),F(9,2));
71 PUT SKIP(3);
72 PUT DATA(RPT1);
73 IF SQLCODE = 0 THEN GOTO FINISHED;
74
75 /* Error occurred while selecting table. Inform and rollback */
76 SELECT_ERROR:
77 EXEC SQL WHENEVER SQLERROR CONTINUE;
78 PUT FILE(SYSPRINT) EDIT('--> ERROR OCURRIDO AL SELECT TABLA EMP '||
79 ' SQLCODE=',SQLCODE)(SKIP,A,F(5));
80 EXEC SQL ROLLBACK;
81
82 GO TO FINISHED;
83
84 /* Error occurred while generating reports. Inform user and exit. */
85 REPORT_ERROR:
86 PUT FILE(SYSPRINT) EDIT('--> ERROR OCURRIDO MIENTRAS GENERA '||
87 'REPORTE. SQLCODE=',SQLCODE)(SKIP,A,F(5));
88
89 /* All done */
90 FINISHED:
91 CLOSE FILE(SYSPRINT);
92 RETURN;
93
94 END PLIEX;
1DB2 SQL PRECOMPILER SYMBOL CROSS-REFERENCE LISTING PAGE 4
0DATA NAMES DEFN REFERENCE
0"BIRTHDATE" 36 DATE(10) COLUMN (NOT NULL) IN "EMP"

"BIRTHDATE" **** COLUMN
50
"BONUS" 38 DECIMAL(9,2) COLUMN (NOT NULL) IN "EMP"

"BONUS" **** COLUMN
50
"COMM" 39 DECIMAL(9,2) COLUMN (NOT NULL) IN "EMP"

"COMM" **** COLUMN
50
"EDLEVEL" 34 SMALL INTEGER COLUMN (NOT NULL) IN "EMP"

"EDLEVEL" **** COLUMN
49
"EMP" 25 TABLE
54
"EMPNO" 26 CHARACTER(6) COLUMN (NOT NULL) IN "EMP"

"EMPNO" **** COLUMN
48
"FIRSTNME" 27 VARCHAR(12) COLUMN (NOT NULL) IN "EMP"

"FIRSTNME" **** COLUMN
48
"HIREDATE" 32 DATE(10) COLUMN (NOT NULL) IN "EMP"

"HIREDATE" **** COLUMN
49
"JOB" 33 CHARACTER(8) COLUMN (NOT NULL) IN "EMP"

"JOB" **** COLUMN
49
"LASTNAME" 29 VARCHAR(15) COLUMN (NOT NULL) IN "EMP"

"LASTNAME" **** COLUMN
48
"MIDINIT" 28 CHARACTER(1) COLUMN (NOT NULL) IN "EMP"

"MIDINIT" **** COLUMN
48
"PHONENO" 31 CHARACTER(4) COLUMN (NOT NULL) IN "EMP"

"PHONENO" **** COLUMN
49
"SALARY" 37 DECIMAL(9,2) COLUMN (NOT NULL) IN "EMP"

"SALARY" **** COLUMN
50 55
"SEX" 35 CHARACTER(1) COLUMN (NOT NULL) IN "EMP"

"SEX" **** COLUMN
49
"WORKDEPT" 30 CHARACTER(3) COLUMN (NOT NULL) IN "EMP"

1DB2 SQL PRECOMPILER SYMBOL CROSS-REFERENCE LISTING PAGE 5
0DATA NAMES DEFN REFERENCE
0"WORKDEPT" **** COLUMN
48
BIRTHDATE 19 CHARACTER(10) IN RPT1

BONUS 21 DECIMAL(9,2) IN RPT1

COMM 22 DECIMAL(9,2) IN RPT1

EDLEVEL 17 SMALL INTEGER IN RPT1

EMPNO 9 CHARACTER(6) IN RPT1

FIRSTNME 10 VARCHAR(12) IN RPT1

HIREDATE 15 CHARACTER(10) IN RPT1

JOB 16 CHARACTER(8) IN RPT1

LASTNAME 12 VARCHAR(15) IN RPT1

MIDINIT 11 CHARACTER(1) IN RPT1

PHONENO 14 CHARACTER(4) IN RPT1

REPORT_ERROR **** LABEL
59
RPT1 8 STRUCTURE
52
SALARY 20 DECIMAL(9,2) IN RPT1

SELECT_ERROR **** LABEL
45
SEX 18 CHARACTER(1) IN RPT1

SYSPRINT 3
WORKDEPT 13 CHARACTER(3) IN RPT1

1DB2 SQL PRECOMPILER MESSAGES PAGE 6
0DSNH084I W DSNHSQL LINE 7 COL 14 UNACCEPTABLE SQL STATEMENT
DSNH084I W DSNHSQL LINE 23 COL 14 UNACCEPTABLE SQL STATEMENT
1DB2 SQL PRECOMPILER STATISTICS PAGE 7
0SOURCE STATISTICS
SOURCE LINES READ: 94
NUMBER OF SYMBOLS: 48
SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 3496
-THERE WERE 2 MESSAGES FOR THIS PROGRAM.
THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
224724 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
RETURN CODE IS 4
DSNH084I W DSNHSQL LINE 7 COL 14 UNACCEPTABLE SQL STATEMENT
BEGIN DECLARE SECTION

DSNH084I W DSNHSQL LINE 23 COL 14 UNACCEPTABLE SQL STATEMENT
END DECLARE SECTION

SOURCE STATISTICS
SOURCE LINES READ: 94
NUMBER OF SYMBOLS: 48
SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 3496
THERE WERE 2 MESSAGES FOR THIS PROGRAM.
THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
224724 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
RETURN CODE IS 4
15655-H31 IBM(R) Enterprise PL/I for z/OS V3.R7.M0 (Built:20080924) 2017.01.25 15:40:55 Page 1
- Options Specified
0 Install:
0 Command: OPTIONS,SOURCE,COMPILE,XREF,ATTRIBUTES,GONUMBER
0 Install:
- Options Used
0 NOAGGREGATE
ARCH(5)
+ ATTRIBUTES(FULL)
BACKREG(5)
BIFPREC(15)
BLANK('05'x)
BLKOFF
CEESTART(FIRST)
CHECK( NOCONFORMANCE NOSTORAGE )
CMPAT(V2)
CODEPAGE(01140)
NOCOMMON
NOCOMPACT
NOCOMPILE(S)
NOCOPYRIGHT
CSECT
CSECTCUT(4)
CURRENCY('$')
NODBCS
DD('SYSPRINT','SYSIN','SYSLIB','SYSPUNCH','SYSLIN','SYSADATA','SYSXMLSD','SYSDEBUG')
DECIMAL( FOFLONASGN NOFORCEDSIGN )
DEFAULT(IBM ASSIGNABLE NOINITFILL NONCONNECTED UPPERINC
DESCRIPTOR DESCLOCATOR DUMMY(ALIGNED) ORDINAL(MIN)
BYADDR RETURNS(BYADDR) LINKAGE(OPTLINK) NORETCODE
NOINLINE ORDER NOOVERLAP NONRECURSIVE ALIGNED
NULL370 BIN1ARG EVENDEC SHORT(HEXADEC)
EBCDIC HEXADEC NATIVE NATIVEADDR E(HEXADEC))
DISPLAY( WTO )
NODLLINIT
NOEXIT
EXTRN(FULL)
FLAG(W)
FLOAT( AFP(NOVOLATILE) NODFP )
FLOATINMATH(ASIS)
+ GONUMBER
NOGRAPHIC
INCAFTER(PROCESS(""))
NOINCDIR
NOINCPDS
NOINITAUTO
NOINITBASED
NOINITCTL
NOINITSTATIC
NOINSOURCE
NOINTERRUPT
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 2
0 LANGLVL(SAA2 OS)
LIMITS( EXTNAME(7) FIXEDBIN(31,31) FIXEDDEC(15,15) NAME(100) )
LINECOUNT(60)
NOLINEDIR
NOLIST
NOMACRO
NOMAP
MARGINI(' ')
+ MARGINS(2,72)
MAXMEM(1048576)
MAXMSG(W 250)
MAXNEST( BLOCK(17) DO(17) IF(17) )
MAXSTMT(4096)
MAXTEMP(50000)
NOMDECK
NONAME
NAMES('@#$' '@#$')
NATLANG(ENU)
NONEST
NOT('^')
NUMBER
OBJECT
NOOFFSET
OPTIMIZE(0)
+ OPTIONS(DOC)
OR('|')
NOPP
NOPPCICS
NOPPMACRO
NOPPINCLUDE
NOPPSQL
NOPPTRACE
PRECTYPE(ANS)
PREFIX(CONVERSION FIXEDOVERFLOW INVALIDOP OVERFLOW
NOSIZE NOSTRINGRANGE NOSTRINGSIZE NOSUBSCRIPTRANGE
UNDERFLOW ZERODIVIDE)
NOPROCEED(S)
PROCESS(DELETE)
QUOTE('"')
REDUCE
NORENT
RESEXP
RESPECT()
RULES(IBM BYNAME NODECSIZE EVENDEC ELSEIF GOTO NOLAXBIF NOLAXCTL
LAXDCL NOLAXDEF LAXIF LAXINOUT LAXLINK LAXMARGINS
LAXPUNC LAXQUAL LAXSEMI LAXSTG NOLAXSTRZ MULTICLOSE UNREF)
NOSCHEDULER
NOSEMANTIC(S)
NOSERVICE
+ SOURCE
SPILL(512)
STATIC(SHORT)
NOSTDSYS
NOSTMT
NOSTORAGE
STRINGOFGRAPHIC(GRAPHIC)
NOSYNTAX(S)
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 3
0 SYSPARM('')
SYSTEM(MVS)
NOTERMINAL
NOTEST
TUNE(5)
USAGE( ROUND(IBM) UNSPEC(IBM) )
WIDECHAR(BIGENDIAN)
WINDOW(1950)
WRITABLE
XINFO(NODEF NOMSG NOSYM NOSYN NOXMI NOXML)
XML( CASE(UPPER) )
+ XREF(FULL)
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 4
0 Compiler Source
0 Line.File
0 1.0 PLIEX: PROC OPTIONS(MAIN);
2.0 DECLARE DSNHLI ENTRY EXTERNAL OPTIONS(ASM INTER);
3.0 DECLARE SQLTEMP CHAR(128);
4.0 DECLARE DSNTEMP FIXED(31) BINARY;
5.0 DECLARE DSNTMP2 DECIMAL FIXED(15,0);
6.0 DECLARE DSNNROWS FIXED(31) BINARY;
7.0 DECLARE
8.0 1 SQLPLIST_SQLDHDR BASED,
9.0 2 SQLPDAID CHAR(8),
10.0 2 SQLPABC FIXED(31) BINARY,
11.0 2 SQLPN FIXED(15) BINARY,
12.0 2 SQLPD FIXED(15) BINARY;
13.0 DECLARE
14.0 1 SQLPLIST_SQLVAR BASED(SQLPLIST_SQLVNROW),
15.0 2 SQLPTYPE FIXED(15) BINARY,
16.0 2 SQLPLEN FIXED(15) BINARY,
17.0 2 SQLPDATA POINTER,
18.0 2 SQLPIND POINTER,
19.0 2 SQLPNAME CHAR(30) VARYING;
20.0 DECLARE SQLPLIST_SQLVNROW POINTER;
21.0 DECLARE DSNPNM CHAR(386);
22.0 DECLARE DSNCNM CHAR(128);
23.0 DECLARE SQL_FILE_READ BINARY FIXED(31) INIT(2);
24.0 DECLARE SQL_FILE_CREATE BINARY FIXED(31) INIT(8);
25.0 DECLARE SQL_FILE_OVERWRITE BINARY FIXED(31) INIT(16);
26.0 DECLARE SQL_FILE_APPEND BINARY FIXED(31) INIT(32);
27.0
28.0
29.0 DCL SYSPRINT FILE EXTERNAL OUTPUT STREAM PRINT;
30.0
31.0
32.0 /***$$$
33.0 EXEC SQL INCLUDE SQLCA
34.0 $$$***/
35.0 /***$$$ SQLCA $$$***/
36.0 DECLARE
37.0 1 SQLCA,
38.0 2 SQLCAID CHAR(8),
39.0 2 SQLCABC FIXED(31) BINARY,
40.0 2 SQLCODE FIXED(31) BINARY,
41.0 2 SQLERRM CHAR(70) VAR,
42.0 2 SQLERRP CHAR(8),
43.0 2 SQLERRD(6) FIXED(31) BINARY,
44.0 2 SQLWARN,
45.0 3 SQLWARN0 CHAR(1),
46.0 3 SQLWARN1 CHAR(1),
47.0 3 SQLWARN2 CHAR(1),
48.0 3 SQLWARN3 CHAR(1),
49.0 3 SQLWARN4 CHAR(1),
50.0 3 SQLWARN5 CHAR(1),
51.0 3 SQLWARN6 CHAR(1),
52.0 3 SQLWARN7 CHAR(1),
53.0 2 SQLEXT,
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 5
0 Line.File
0 54.0 3 SQLWARN8 CHAR(1),
55.0 3 SQLWARN9 CHAR(1),
56.0 3 SQLWARNA CHAR(1),
57.0 3 SQLSTATE CHAR(5);
58.0
59.0
60.0
61.0 /***$$$
62.0 EXEC SQL BEGIN DECLARE SECTION
63.0 $$$***/
64.0
65.0 DCL 1 RPT1,
66.0 15 EMPNO CHAR(6) INIT (''),
67.0 15 FIRSTNME CHAR(12) VAR INIT (''),
68.0 15 MIDINIT CHAR(1) INIT (''),
69.0 15 LASTNAME CHAR(15) VAR INIT (''),
70.0 15 WORKDEPT CHAR(3) INIT (''),
71.0 15 PHONENO CHAR(4) INIT (''),
72.0 15 HIREDATE CHAR(10) INIT (''),
73.0 15 JOB CHAR(8) INIT (''),
74.0 15 EDLEVEL FIXED(15) BINARY INIT(0),
75.0 15 SEX CHAR(1) INIT (''),
76.0 15 BIRTHDATE CHAR(10) INIT (''),
77.0 15 SALARY FIXED DECIMAL (9,2) INIT (0),
78.0 15 BONUS FIXED DECIMAL (9,2) INIT (0),
79.0 15 COMM FIXED DECIMAL (9,2) INIT (0);
80.0
81.0 /***$$$
82.0 EXEC SQL END DECLARE SECTION
83.0 $$$***/
84.0
85.0
86.0
87.0 /***$$$
88.0 EXEC SQL DECLARE EMP TABLE
89.0 (EMPNO CHAR(6) NOT NULL,
90.0 FIRSTNME VARCHAR(12) NOT NULL,
91.0 MIDINIT CHAR(1) NOT NULL,
92.0 LASTNAME VARCHAR(15) NOT NULL,
93.0 WORKDEPT CHAR(3) NOT NULL,
94.0 PHONENO CHAR(4) NOT NULL,
95.0 HIREDATE DATE NOT NULL,
96.0 JOB CHAR(8) NOT NULL,
97.0 EDLEVEL SMALLINT NOT NULL,
98.0 SEX CHAR(1) NOT NULL,
99.0 BIRTHDATE DATE NOT NULL,
100.0 SALARY DECIMAL(9,2) NOT NULL,
101.0 BONUS DECIMAL(9,2) NOT NULL,
102.0 COMM DECIMAL(9,2) NOT NULL)
103.0 $$$***/
104.0
105.0
106.0 OPEN FILE(SYSPRINT);
107.0 /* EMPNO, LASTNAME, SALARY, BONUS, COMM */
108.0 /* :EMPNO, :LASTNAME, :SALARY, :BONUS, :COMM */
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 6
0 Line.File
0 109.0
110.0
111.0 /***$$$
112.0 EXEC SQL WHENEVER SQLERROR GO TO SELECT_ERROR
113.0 $$$***/
114.0
115.0
116.0
117.0 /***$$$
118.0 EXEC SQL SELECT
119.0 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,
120.0 PHONENO, HIREDATE, JOB, EDLEVEL, SEX,
121.0 BIRTHDATE, SALARY, BONUS, COMM
122.0 INTO
123.0 :RPT1
124.0 FROM
125.0 EMP
126.0 WHERE SALARY = 695.31
127.0 $$$***/
128.0 DO;
129.0 SQLPLIST5.SQLAVADDR(1) = ADDR(RPT1.EMPNO);
130.0 SQLPLIST5.SQLAVTYPE(1) = 452;
131.0 SQLPLIST5.SQLAVLEN(1) = 6;
132.0 SQLPLIST5.SQLAVADDR(2) = ADDR(RPT1.FIRSTNME);
133.0 SQLPLIST5.SQLAVTYPE(2) = 448;
134.0 SQLPLIST5.SQLAVLEN(2) = 12;
135.0 SQLPLIST5.SQLAVADDR(3) = ADDR(RPT1.MIDINIT);
136.0 SQLPLIST5.SQLAVTYPE(3) = 452;
137.0 SQLPLIST5.SQLAVLEN(3) = 1;
138.0 SQLPLIST5.SQLAVADDR(4) = ADDR(RPT1.LASTNAME);
139.0 SQLPLIST5.SQLAVTYPE(4) = 448;
140.0 SQLPLIST5.SQLAVLEN(4) = 15;
141.0 SQLPLIST5.SQLAVADDR(5) = ADDR(RPT1.WORKDEPT);
142.0 SQLPLIST5.SQLAVTYPE(5) = 452;
143.0 SQLPLIST5.SQLAVLEN(5) = 3;
144.0 SQLPLIST5.SQLAVADDR(6) = ADDR(RPT1.PHONENO);
145.0 SQLPLIST5.SQLAVTYPE(6) = 452;
146.0 SQLPLIST5.SQLAVLEN(6) = 4;
147.0 SQLPLIST5.SQLAVADDR(7) = ADDR(RPT1.HIREDATE);
148.0 SQLPLIST5.SQLAVTYPE(7) = 452;
149.0 SQLPLIST5.SQLAVLEN(7) = 10;
150.0 SQLPLIST5.SQLAVADDR(8) = ADDR(RPT1.JOB);
151.0 SQLPLIST5.SQLAVTYPE(8) = 452;
152.0 SQLPLIST5.SQLAVLEN(8) = 8;
153.0 SQLPLIST5.SQLAVADDR(9) = ADDR(RPT1.EDLEVEL);
154.0 SQLPLIST5.SQLAVTYPE(9) = 500;
155.0 SQLPLIST5.SQLAVLEN(9) = 2;
156.0 SQLPLIST5.SQLAVADDR(10) = ADDR(RPT1.SEX);
157.0 SQLPLIST5.SQLAVTYPE(10) = 452;
158.0 SQLPLIST5.SQLAVLEN(10) = 1;
159.0 SQLPLIST5.SQLAVADDR(11) = ADDR(RPT1.BIRTHDATE);
160.0 SQLPLIST5.SQLAVTYPE(11) = 452;
161.0 SQLPLIST5.SQLAVLEN(11) = 10;
162.0 SQLPLIST5.SQLAVADDR(12) = ADDR(RPT1.SALARY);
163.0 SQLPLIST5.SQLAVTYPE(12) = 484;
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 7
0 Line.File
0 164.0 SQLPLIST5.SQLAVLEN(12) = 2306;
165.0 SQLPLIST5.SQLAVADDR(13) = ADDR(RPT1.BONUS);
166.0 SQLPLIST5.SQLAVTYPE(13) = 484;
167.0 SQLPLIST5.SQLAVLEN(13) = 2306;
168.0 SQLPLIST5.SQLAVADDR(14) = ADDR(RPT1.COMM);
169.0 SQLPLIST5.SQLAVTYPE(14) = 484;
170.0 SQLPLIST5.SQLAVLEN(14) = 2306;
171.0 SQLPLIST5.SQLAVIND(1) = NULL();
172.0 SQLPLIST5.SQLAVNAME(1) = '';
173.0 SQLPLIST5.SQLAVIND(2) = NULL();
174.0 SQLPLIST5.SQLAVNAME(2) = '';
175.0 SQLPLIST5.SQLAVIND(3) = NULL();
176.0 SQLPLIST5.SQLAVNAME(3) = '';
177.0 SQLPLIST5.SQLAVIND(4) = NULL();
178.0 SQLPLIST5.SQLAVNAME(4) = '';
179.0 SQLPLIST5.SQLAVIND(5) = NULL();
180.0 SQLPLIST5.SQLAVNAME(5) = '';
181.0 SQLPLIST5.SQLAVIND(6) = NULL();
182.0 SQLPLIST5.SQLAVNAME(6) = '';
183.0 SQLPLIST5.SQLAVIND(7) = NULL();
184.0 SQLPLIST5.SQLAVNAME(7) = '';
185.0 SQLPLIST5.SQLAVIND(8) = NULL();
186.0 SQLPLIST5.SQLAVNAME(8) = '';
187.0 SQLPLIST5.SQLAVIND(9) = NULL();
188.0 SQLPLIST5.SQLAVNAME(9) = '';
189.0 SQLPLIST5.SQLAVIND(10) = NULL();
190.0 SQLPLIST5.SQLAVNAME(10) = '';
191.0 SQLPLIST5.SQLAVIND(11) = NULL();
192.0 SQLPLIST5.SQLAVNAME(11) = '';
193.0 SQLPLIST5.SQLAVIND(12) = NULL();
194.0 SQLPLIST5.SQLAVNAME(12) = '';
195.0 SQLPLIST5.SQLAVIND(13) = NULL();
196.0 SQLPLIST5.SQLAVNAME(13) = '';
197.0 SQLPLIST5.SQLAVIND(14) = NULL();
198.0 SQLPLIST5.SQLAVNAME(14) = '';
199.0 SQLPLIST5.SQLAVARS.SQLAVDAID = 'E2D8D3C4C1404008'X;
200.0 SQLPLIST5.SQLAVARS.SQLAVDABC = 632;
201.0 SQLPLIST5.SQLAVARS.SQLAVN = 14;
202.0 SQLPLIST5.SQLAVARS.SQLAVD = 14;
203.0 SQLPLIST5_SQLAPARM = ADDR(SQLPLIST5.SQLAVARS.SQLAVDAID);
204.0 SQLPLIST5.SQLCODEP=ADDR(SQLCA);
205.0 CALL DSNHLI(SQLPLIST5);
206.0 IF SQLCODE<0 THEN GO TO SELECT_ERROR;
207.0 DECLARE
208.0 1 SQLPLIST5,
209.0 2 SQLPLLEN FIXED(15) BINARY INIT(64),
210.0 2 SQLFLAGS FIXED(15) BINARY INIT(1024),
211.0 2 SQLCTYPE FIXED(15) BINARY INIT(30),
212.0 2 SQLPROGN CHAR(8) INIT('5047504C31535132'X),
213.0 2 SQLTIMES(2) FIXED(31) BINARY UNALIGNED
214.0 INIT(440400130,60659904),
215.0 2 SQLSECTN FIXED(15) BINARY INIT(1),
216.0 2 SQLCODEP POINTER,
217.0 2 SQLVPARM FIXED(31) BINARY INIT(0),
218.0 2 SQLAPARM FIXED(31) BINARY INIT(0),
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 8
0 Line.File
0 219.0 2 SQLSTNM7 FIXED(15) BINARY INIT(1208),
220.0 2 SQLSTYPE FIXED(15) BINARY INIT(231),
221.0 2 SQLSTNUM FIXED(31) BINARY INIT(47),
222.0 2 SQLFLGS2 FIXED(15) BINARY INIT(0),
223.0 2 SQLRSRVD CHAR(18) INIT('00'X),
224.0 2 SQLAVARS,
225.0 3 SQLAVDAID CHAR(8),
226.0 3 SQLAVDABC FIXED(31) BINARY,
227.0 3 SQLAVN FIXED(15) BINARY,
228.0 3 SQLAVD FIXED(15) BINARY,
229.0 2 SQLAVELT(14),
230.0 3 SQLAVTYPE FIXED(15) BINARY,
231.0 3 SQLAVLEN FIXED(15) BINARY,
232.0 3 SQLAVADDR POINTER,
233.0 3 SQLAVIND POINTER,
234.0 3 SQLAVNAME CHAR(30) VAR,
235.0 1 SQLPLIST5_SQLVPARM POINTER
236.0 BASED(ADDR(SQLPLIST5.SQLVPARM)),
237.0 1 SQLPLIST5_SQLAPARM POINTER
238.0 BASED(ADDR(SQLPLIST5.SQLAPARM));
239.0 END;
240.0
241.0
242.0
243.0 /***$$$
244.0 EXEC SQL COMMIT
245.0 $$$***/
246.0 DO;
247.0 SQLPLIST6.SQLCODEP=ADDR(SQLCA);
248.0 CALL DSNHLI(SQLPLIST6);
249.0 IF SQLCODE<0 THEN GO TO SELECT_ERROR;
250.0 DECLARE
251.0 1 SQLPLIST6,
252.0 2 SQLPLLEN FIXED(15) BINARY INIT(64),
253.0 2 SQLFLAGS FIXED(15) BINARY INIT(0),
254.0 2 SQLCTYPE FIXED(15) BINARY INIT(150),
255.0 2 SQLPROGN CHAR(8) INIT('5047504C31535132'X),
256.0 2 SQLTIMES(2) FIXED(31) BINARY UNALIGNED
257.0 INIT(440400130,60659904),
258.0 2 SQLSECTN FIXED(15) BINARY INIT(0),
259.0 2 SQLCODEP POINTER,
260.0 2 SQLVPARM FIXED(31) BINARY INIT(0),
261.0 2 SQLAPARM FIXED(31) BINARY INIT(0),
262.0 2 SQLSTNM7 FIXED(15) BINARY INIT(1208),
263.0 2 SQLSTYPE FIXED(15) BINARY INIT(310),
264.0 2 SQLSTNUM FIXED(31) BINARY INIT(57),
265.0 2 SQLFLGS2 FIXED(15) BINARY INIT(0),
266.0 2 SQLRSRVD CHAR(18) INIT('00'X),
267.0 1 SQLPLIST6_SQLVPARM POINTER
268.0 BASED(ADDR(SQLPLIST6.SQLVPARM)),
269.0 1 SQLPLIST6_SQLAPARM POINTER
270.0 BASED(ADDR(SQLPLIST6.SQLAPARM));
271.0 END;
272.0
273.0
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 9
0 Line.File
0 274.0
275.0 /***$$$
276.0 EXEC SQL WHENEVER SQLERROR GO TO REPORT_ERROR
277.0 $$$***/
278.0
279.0
280.0 /* Write the header */
281.0 PUT FILE(SYSPRINT)
282.0 EDIT('REPORTE DE PROYECTOS AFECTADOS AL AUMENTO DE SALARIOS')
283.0 (COL(22),A);
284.0 PUT FILE(SYSPRINT)
285.0 EDIT('EMPLEADO','NOMBRE-EMPLEADO','SALARIO','BONUS','COMM')
286.0 (SKIP,COL(7),A,X(13),A,X(6),A,X(7),A,X(8),A);
287.0 PUT FILE(SYSPRINT)
288.0 EDIT(EMPNO,LASTNAME,SALARY,BONUS,COMM)
289.0 (SKIP,COL(5),A,X(10),A,X(22),F(9,2),X(3),F(9,2),X(3),F(9,2));
290.0 PUT SKIP(3);
291.0 PUT DATA(RPT1);
292.0 IF SQLCODE = 0 THEN GOTO FINISHED;
293.0
294.0 /* Error occurred while selecting table. Inform and rollback */
295.0 SELECT_ERROR:
296.0
297.0 /***$$$
298.0 EXEC SQL WHENEVER SQLERROR CONTINUE
299.0 $$$***/
300.0
301.0 PUT FILE(SYSPRINT) EDIT('--> ERROR OCURRIDO AL SELECT TABLA EMP '||
302.0 ' SQLCODE=',SQLCODE)(SKIP,A,F(5));
303.0
304.0 /***$$$
305.0 EXEC SQL ROLLBACK
306.0 $$$***/
307.0 DO;
308.0 SQLPLIST9.SQLCODEP=ADDR(SQLCA);
309.0 CALL DSNHLI(SQLPLIST9);
310.0 DECLARE
311.0 1 SQLPLIST9,
312.0 2 SQLPLLEN FIXED(15) BINARY INIT(64),
313.0 2 SQLFLAGS FIXED(15) BINARY INIT(0),
314.0 2 SQLCTYPE FIXED(15) BINARY INIT(155),
315.0 2 SQLPROGN CHAR(8) INIT('5047504C31535132'X),
316.0 2 SQLTIMES(2) FIXED(31) BINARY UNALIGNED
317.0 INIT(440400130,60659904),
318.0 2 SQLSECTN FIXED(15) BINARY INIT(0),
319.0 2 SQLCODEP POINTER,
320.0 2 SQLVPARM FIXED(31) BINARY INIT(0),
321.0 2 SQLAPARM FIXED(31) BINARY INIT(0),
322.0 2 SQLSTNM7 FIXED(15) BINARY INIT(1208),
323.0 2 SQLSTYPE FIXED(15) BINARY INIT(277),
324.0 2 SQLSTNUM FIXED(31) BINARY INIT(80),
325.0 2 SQLFLGS2 FIXED(15) BINARY INIT(0),
326.0 2 SQLRSRVD CHAR(18) INIT('00'X),
327.0 1 SQLPLIST9_SQLVPARM POINTER
328.0 BASED(ADDR(SQLPLIST9.SQLVPARM)),
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 10
0 Line.File
0 329.0 1 SQLPLIST9_SQLAPARM POINTER
330.0 BASED(ADDR(SQLPLIST9.SQLAPARM));
331.0 END;
332.0
333.0
334.0 GO TO FINISHED;
335.0
336.0 /* Error occurred while generating reports. Inform user and exit. */
337.0 REPORT_ERROR:
338.0 PUT FILE(SYSPRINT) EDIT('--> ERROR OCURRIDO MIENTRAS GENERA '||
339.0 'REPORTE. SQLCODE=',SQLCODE)(SKIP,A,F(5));
340.0
341.0 /* All done */
342.0 FINISHED:
343.0 CLOSE FILE(SYSPRINT);
344.0 RETURN;
345.0
346.0 END PLIEX;
15655-H31 IBM(R) Enterprise PL/I for z/OS PLIEX: PROC OPTIONS(MAIN); 2017.01.25 15:40:55 Page 11

Miguel Fernandez

New User

Joined: 05 Dec 2016
Posts: 14
Location: Spain

Code:

SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, 00021003
PHONENO, HIREDATE, JOB, EDLEVEL, SEX, 00030003
BIRTHDATE, SALARY, BONUS, COMM 00040003
FROM EMP 00050003
WHERE SALARY = 695.31; 00060007
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
000010 CHRISTINE I HAAS A00 3978 1965-01-01 PRES 18 F 1933-08-14 695.31 160.00 42.20
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

prino

Senior Member

Joined: 07 Feb 2009
Posts: 1315
Location: Vilnius, Lithuania

Miguel Fernandez wrote:

Is this an error in PL1-DB2 versions that need to be repaired or is it a bad programming?

Do you really think that any errors in Enterprise PL/I V3.7 (which, by the way has left support years ago) and/or DB2 would not have been noticed by others?

Sheesh...

Ergo conclusio: you're a bad programmer!

Robert Sample

Global Moderator

Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA

Quote:

Is this an error in PL1-DB2 versions that need to be repaired or is it a bad programming?

You had better hope it is bad programming. Enterprise PL/I 3.7 went out of support as of April 30, 2012 so there is no chance you will get IBM to look at doing any repairs to it.

Did you notice you have two compile Warning messages? So we KNOW you have bad programming since you're posting code with warnings.

Miguel Fernandez

New User

Joined: 05 Dec 2016
Posts: 14
Location: Spain

Posted: Thu Jan 26, 2017 1:03 am

Warning messages are considered as comments that the precompiler ignores. Many customers installations have programs running in production environment working without any problem with warning messages. In this case the statements causing the warning messages were removed with the same result as before. I am not a perfectionist but it makes me think that I will have to solve my problem with updated versions, to see what happens. Thanks to prino for the flowers and their compulsive response.

Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

Quote:

I am not a perfectionist but it makes me think that I will have to solve my problem with updated versions, to see what happens.

If I were you in the confused situation then, I would try executing any production code in the current test environment which has similar sqls and verify the reuslts and in that way it is confirmed either as a version issue or a bug in the program?

Miguel Fernandez

New User

Joined: 05 Dec 2016
Posts: 14
Location: Spain

To finish closing this incident and as general culture I comment the following:

The program runs correctly, the output in the host variables are ASCII type and not EBCDIC

To solve it I was studying the types of code CCSID that are defined in PARM and the best solution that I found is to define in the BIND the variable ENCODING like EBCDIC

Code:

********************************* TOP OF DATA **********************************
Pgm: PGPL1SQ2 REPORTE DE PROYECTOS AFECTADOS AL AUMENTO DE SALARIOS
SALARIO BONUS COMM LEVEL EMPNO LASTNAM
695.31 160.00 42.20 18 000010 HAAS
******************************** BOTTOM OF DATA ********************************

Code:

DSN SYSTEM (DB9G) RETRY(5)
BIND PACKAGE (CAIXPACK) -
OWNER (IBMUSER) -
QUALIFIER (DSN8910) -
MEMBER (PGPL1SQ2) -
LIBRARY ('MIGUEL.DBRMLIB') -
RELEASE (DEALLOCATE) -
ACTION (REPLACE) -
EXPLAIN (YES) -
VALIDATE (RUN)-
ISOLATION (CS) -
FLAG (I) -
[b] ENCODING (EBCDIC) - [/b]
CURRENTDATA (NO) -
DEGREE (1) -
ENABLE (*) -
SQLERROR (NOPACKAGE)
END

View previous topic :: :: View next topic

View Bookmarks
All times are GMT + 6 Hours

Forum Index -> DB2

Topic		Forum	Replies
Similar Topics
	Repeat n times by substuting SEQNUM i...	DFSORT/ICETOOL	2
	Preserve changes to ISPF Panel Variables	TSO/ISPF	5
	how to eliminate null indicator value...	DB2	7
	INCLUDE OMIT COND for Multiple values...	DFSORT/ICETOOL	5
	Replace Multiple Field values to Othe...	DFSORT/ICETOOL	12

Search our Forums:

IBMMainframes.com is not an official and/or affiliated with IBM^® in anyway
Board Rules \| FAQ \| Downloads \| Wiki \| SiteMap \| Contact Us