-
Notifications
You must be signed in to change notification settings - Fork 21
/
Copy pathRUNSQLSRCC.CLP
211 lines (184 loc) · 9.97 KB
/
RUNSQLSRCC.CLP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
PGM PARM(&SRCFILE &SRCMBR &PARMS &PARMVALS +
&PROMPT &DLTTMPSRC &COMMIT &NAMING +
&OPTION &OUTPUT &SECLVLTXT &DSPOUTPUT +
&PRTTMPSRC &SPLF &USRDTA &USRDFNDTA +
&PRTOUTQALL)
DCL VAR(&USRDFNDTA) TYPE(*CHAR) LEN(255)
DCL VAR(&USRDTA) TYPE(*CHAR) LEN(10)
DCL VAR(&PRTOUTQALL) TYPE(*CHAR) LEN(20)
DCL VAR(&PRTOUTQ) TYPE(*CHAR) LEN(10)
DCL VAR(&PRTOUTQLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&SPLF) TYPE(*CHAR) LEN(10)
DCL VAR(&PRTTMPSRC) TYPE(*CHAR) LEN(4)
DCL VAR(&SECLVLTXT) TYPE(*CHAR) LEN(4)
DCL VAR(&DSPOUTPUT) TYPE(*CHAR) LEN(4)
DCL VAR(&OPTION) TYPE(*CHAR) LEN(10)
DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(10)
DCL VAR(&RTNERROR) TYPE(*CHAR) LEN(1)
DCL VAR(&COMMIT) TYPE(*CHAR) LEN(5)
DCL VAR(&NAMING) TYPE(*CHAR) LEN(4)
DCL VAR(&PARMS) TYPE(*CHAR) LEN(3002)
DCL VAR(&PARMVALS) TYPE(*CHAR) LEN(3002)
DCL VAR(&IDCOLNAME) TYPE(*CHAR) LEN(30)
DCL VAR(&CRTIDCOL) TYPE(*CHAR) LEN(4)
DCL VAR(&DLTTMPSRC) TYPE(*CHAR) LEN(4)
DCL VAR(&SQLIFSFILE) TYPE(*CHAR) LEN(255)
DCL VAR(&SQLLOC) TYPE(*CHAR) LEN(10)
DCL VAR(&SRCFILE) TYPE(*CHAR) LEN(20)
DCL VAR(&SQLSRCFILE) TYPE(*CHAR) LEN(10)
DCL VAR(&SQLSRCLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&SRCMBR) TYPE(*CHAR) LEN(10)
DCL VAR(&PROMPT) TYPE(*CHAR) LEN(4)
DCL VAR(&EMPTYERROR) TYPE(*CHAR) LEN(4)
DCL VAR(&IFILE) TYPE(*CHAR) LEN(10) VALUE(QCUSTCDT)
DCL VAR(&ILIB) TYPE(*CHAR) LEN(10) VALUE(QIWS)
DCL VAR(&OUTFILE) TYPE(*CHAR) LEN(20)
DCL VAR(&TEMPFILE) TYPE(*CHAR) LEN(10) VALUE(CUST1)
DCL VAR(&TEMPLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&SQL) TYPE(*CHAR) LEN(5000)
DCL VAR(&SQLQUERY) TYPE(*CHAR) LEN(5000)
DCL VAR(&RECORDS) TYPE(*DEC) LEN(10)
DCL VAR(&RECORDSC) TYPE(*CHAR) LEN(10)
DCL VAR(&COMPMSGTYP) TYPE(*CHAR) LEN(10) +
VALUE(*COMP)
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERRORS))
/* Extract output queue info. */
CHGVAR VAR(&PRTOUTQ) VALUE(%SST(&PRTOUTQALL 1 10))
CHGVAR VAR(&PRTOUTQLIB) VALUE(%SST(&PRTOUTQALL 11 10))
/* Parse SQL source member name */
CHGVAR VAR(&SQLSRCLIB) VALUE(%SST(&SRCFILE 11 10))
CHGVAR VAR(&SQLSRCFILE) VALUE(%SST(&SRCFILE 1 10))
CHKOBJ OBJ(&SQLSRCLIB/&SQLSRCFILE) OBJTYPE(*FILE) +
MBR(&SRCMBR)
MONMSG MSGID(CPF0000) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('SQL +
Source member' |> &SQLSRCLIB |< '/' |< +
&SQLSRCFILE |< '(' |< &SRCMBR |< ') was +
not found or you don''t have access. +
RUNSQLSRC action query cancelled') +
MSGTYPE(*ESCAPE)
ENDDO
/* Drop diag message into joblog for SQL source */
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('INFO: +
SQL Source member' |> &SQLSRCLIB |< '/' +
|< &SQLSRCFILE |< '(' |< &SRCMBR |< ') is +
being used to run query') TOPGMQ(*SAME) +
MSGTYPE(*DIAG)
/* Delete temporary source file */
/* Make sure doesn't exist. */
DLTF FILE(QTEMP/TMPQRYSRC)
MONMSG MSGID(CPF0000)
/* Create temporary source file */
CRTSRCPF FILE(QTEMP/TMPQRYSRC) RCDLEN(240)
ADDPFM FILE(QTEMP/TMPQRYSRC) MBR(TMPQRYSRC) +
SRCTYPE(SQL)
/* Make snapshot of source member to QTEMP for our job */
CPYSRCF FROMFILE(&SQLSRCLIB/&SQLSRCFILE) +
TOFILE(QTEMP/TMPQRYSRC) FROMMBR(&SRCMBR) +
TOMBR(TMPQRYSRC) MBROPT(*REPLACE)
/* Read SQL from source member, scan and replace values */
/* and update the temp SQL member file in QTEMP */
OVRDBF FILE(TMPQRYSRC) TOFILE(QTEMP/TMPQRYSRC) +
MBR(*FIRST) OVRSCOPE(*ACTGRPDFN)
CALL PGM(RUNSQLSRCR) PARM(&PARMS &PARMVALS +
&RTNERROR)
DLTOVR FILE(TMPQRYSRC) LVL(*ACTGRPDFN)
/* If rtnerror <> '0', bail out now */
IF COND(&RTNERROR *EQ '1') THEN(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Same +
number of parms and parm values are +
required to be passed') MSGTYPE(*ESCAPE)
ENDDO
IF COND(&RTNERROR *EQ '2') THEN(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('At +
least one parameter required error +
occurred while processing SQL source +
member parameters') MSGTYPE(*ESCAPE)
ENDDO
IF COND(&RTNERROR *EQ '3') THEN(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Unknown +
error occurred while handling parameters. +
Check the joblog') MSGTYPE(*ESCAPE)
ENDDO
/* Output actual query source that will run */
/* if PRTTMPSRC is *YES. */
IF COND(&PRTTMPSRC *EQ *YES) THEN(DO)
/* Override without specific outq */
IF COND(&PRTOUTQ *EQ *SAME) THEN(DO)
OVRPRTF FILE(QSYSPRT) TOFILE(*FILE) PAGESIZE(*N 198) +
HOLD(*YES) USRDTA(&USRDTA) +
USRDFNDTA(&USRDFNDTA) SPLFNAME(&SPLF) +
OVRSCOPE(*ACTGRPDFN)
ENDDO
IF COND(&PRTOUTQ *NE *SAME) THEN(DO)
/* Override with specific outq */
OVRPRTF FILE(QSYSPRT) TOFILE(*FILE) PAGESIZE(*N 198) +
OUTQ(&PRTOUTQLIB/&PRTOUTQ) HOLD(*YES) +
USRDTA(&USRDTA) USRDFNDTA(&USRDFNDTA) +
SPLFNAME(&SPLF) OVRSCOPE(*ACTGRPDFN)
ENDDO
CPYF FROMFILE(QTEMP/TMPQRYSRC) TOFILE(QSYSPRT)
DLTOVR FILE(QSYSPRT) LVL(*ACTGRPDFN)
ENDDO /* ENDDO print source */
/* Run the SQL action query now */
/* Override without specific outq */
IF COND(&PRTOUTQ *EQ *SAME) THEN(DO)
OVRPRTF FILE(QSYSPRT) TOFILE(*FILE) PAGESIZE(*N 198) +
HOLD(*YES) USRDFNDTA(&USRDFNDTA) +
SPLFNAME(&SPLF) OVRSCOPE(*ACTGRPDFN)
ENDDO
IF COND(&PRTOUTQ *NE *SAME) THEN(DO)
/* Override with specific outq */
OVRPRTF FILE(QSYSPRT) TOFILE(*FILE) PAGESIZE(*N 198) +
OUTQ(&PRTOUTQLIB/&PRTOUTQ) HOLD(*YES) +
USRDFNDTA(&USRDFNDTA) SPLFNAME(&SPLF) +
OVRSCOPE(*ACTGRPDFN)
ENDDO
IF COND(&PROMPT *EQ *YES) THEN(DO)
? QSYS/RUNSQLSTM ??SRCFILE(QTEMP/TMPQRYSRC) +
??SRCMBR(TMPQRYSRC) ??COMMIT(&COMMIT) +
??NAMING(&NAMING) ??OPTION(&OPTION) +
??SECLVLTXT(&SECLVLTXT) ??OUTPUT(&OUTPUT)
ENDDO
IF COND(&PROMPT *NE *YES) THEN(DO)
QSYS/RUNSQLSTM SRCFILE(QTEMP/TMPQRYSRC) +
SRCMBR(TMPQRYSRC) COMMIT(&COMMIT) +
NAMING(&NAMING) OPTION(&OPTION) +
SECLVLTXT(&SECLVLTXT) OUTPUT(&OUTPUT)
ENDDO
DLTOVR FILE(QSYSPRT) LVL(*ACTGRPDFN)
/* Delete temp source member if specified */
IF COND(&DLTTMPSRC *EQ *YES) THEN(DO)
DLTF FILE(QTEMP/TMPQRYSRC)
ENDDO
/* Last spool file should be most recent query */
IF COND(&DSPOUTPUT *EQ *YES) THEN(DO)
DSPSPLF FILE(&SPLF) SPLNBR(*LAST)
MONMSG MSGID(CPF0000) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('No +
RUNSQLSTM listing spool files +
available.') TOPGMQ(*SAME) MSGTYPE(*DIAG)
ENDDO
ENDDO
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('SQL +
action query ran successfully. Source +
member' |> &SQLSRCLIB |< '/' |< +
&SQLSRCFILE |< '(' |< &SRCMBR |< ')') +
MSGTYPE(*COMP)
RETURN
ERRORS:
/* Last spool file should be most recent query */
IF COND(&DSPOUTPUT *EQ *YES) THEN(DO)
DSPSPLF FILE(&SPLF) SPLNBR(*LAST)
MONMSG MSGID(CPF0000) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('No +
RUNSQLSTM listing spool files +
available.') TOPGMQ(*SAME) MSGTYPE(*DIAG)
ENDDO
ENDDO
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA('RUNSQLSRC-Errors occurred running +
SQL action query. Check the job +
log and your SQL source query') +
MSGTYPE(*ESCAPE)
ENDPGM