-
Notifications
You must be signed in to change notification settings - Fork 19
/
QSHQRYTMPC.CLP
121 lines (104 loc) · 5.67 KB
/
QSHQRYTMPC.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
PGM PARM(&SQLQUERY &OUTFILE &EMPTYERROR &PROMPT)
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))
/*----------------------------------------------------------------------------*/
/* Parse outfile parm */
/*----------------------------------------------------------------------------*/
CHGVAR VAR(&TEMPLIB) VALUE(%SST(&OUTFILE 11 10))
CHGVAR VAR(&TEMPFILE) VALUE(%SST(&OUTFILE 1 10))
/*----------------------------------------------------------------------------*/
/* Create record count data area */
/*----------------------------------------------------------------------------*/
CHKOBJ OBJ(QTEMP/SQLQRYCNT) OBJTYPE(*DTAARA)
MONMSG MSGID(CPF0000) EXEC(DO)
CRTDTAARA DTAARA(QTEMP/SQLQRYCNT) TYPE(*DEC) LEN(10 0) +
TEXT('Query Result Record Count')
ENDDO
CHGDTAARA DTAARA(QTEMP/SQLQRYCNT *ALL) VALUE(0)
/*----------------------------------------------------------------------------*/
/* Create outfile info data area. Added in case we add a *GEN option */
/*----------------------------------------------------------------------------*/
CHKOBJ OBJ(QTEMP/SQLQRYFIL) OBJTYPE(*DTAARA)
MONMSG MSGID(CPF0000) EXEC(DO)
CRTDTAARA DTAARA(QTEMP/SQLQRYFIL) TYPE(*CHAR) LEN(10) +
TEXT('Query Result File Name')
ENDDO
CHGDTAARA DTAARA(QTEMP/SQLQRYFIL *ALL) VALUE(' ')
CHKOBJ OBJ(QTEMP/SQLQRYLIB) OBJTYPE(*DTAARA)
MONMSG MSGID(CPF0000) EXEC(DO)
CRTDTAARA DTAARA(QTEMP/SQLQRYLIB) TYPE(*CHAR) LEN(10) +
TEXT('Query Result Lib Name')
ENDDO
CHGDTAARA DTAARA(QTEMP/SQLQRYLIB *ALL) VALUE(' ')
/*----------------------------------------------------------------------------*/
/* Drop the temp table if it exists */
/* Catch error. Create will fail if already found. */
/*----------------------------------------------------------------------------*/
CHGVAR VAR(&SQL) VALUE('DROP TABLE' |> &TEMPLIB |< +
'/' |< &TEMPFILE)
IF COND(&PROMPT *EQ *YES) THEN(DO)
? QSYS/RUNSQL ??SQL(&SQL) ??COMMIT(*NONE)
MONMSG MSGID(CPF0000)
ENDDO
IF COND(&PROMPT *NE *YES) THEN(DO)
QSYS/RUNSQL SQL(&SQL) COMMIT(*NONE)
MONMSG MSGID(CPF0000)
ENDDO
/*----------------------------------------------------------------------------*/
/* Create temp table from another table using SQL */
/*----------------------------------------------------------------------------*/
CHGVAR VAR(&SQL) VALUE('create table' |> &TEMPLIB +
|< '/' |< &TEMPFILE |> 'as (' |< +
&SQLQUERY |< ') with data')
IF COND(&PROMPT *EQ *YES) THEN(DO)
? QSYS/RUNSQL ??SQL(&SQL) ??COMMIT(*NONE)
MONMSG MSGID(CPF0000)
ENDDO
IF COND(&PROMPT *NE *YES) THEN(DO)
QSYS/RUNSQL SQL(&SQL) COMMIT(*NONE)
MONMSG MSGID(CPF0000)
ENDDO
/*----------------------------------------------------------------------------*/
/* Get record count */
/*----------------------------------------------------------------------------*/
/* Check PF record count */
RTVMBRD FILE(&TEMPLIB/&TEMPFILE) MBR(*FIRST) +
NBRCURRCD(&RECORDS)
CHGVAR VAR(&RECORDSC) VALUE(&RECORDS)
/* Save record count to data area */
CHGDTAARA DTAARA(QTEMP/SQLQRYCNT *ALL) VALUE(&RECORDS)
/* Set temp file data areas on query success */
CHGDTAARA DTAARA(QTEMP/SQLQRYFIL *ALL) VALUE(&TEMPFILE)
CHGDTAARA DTAARA(QTEMP/SQLQRYLIB *ALL) VALUE(&TEMPLIB)
/* If error on empty file, set to escape if no records */
IF COND(&EMPTYERROR *EQ *YES *AND &RECORDS *EQ +
0) THEN(DO)
CHGVAR VAR(&COMPMSGTYP) VALUE(*ESCAPE)
ENDDO
IF COND(&EMPTYERROR *EQ *NO *AND &RECORDS *EQ +
0) THEN(DO)
CHGVAR VAR(&COMPMSGTYP) VALUE(*COMP)
ENDDO
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Temp +
file' |> &TEMPLIB |< '/' |< &TEMPFILE |> +
'was created from SQL query.' |> +
&RECORDSC |> 'records') MSGTYPE(&COMPMSGTYP)
RETURN
ERRORS:
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA('QSHQRYTMP-Errors occurred running +
SQL query command. Check the job log and +
your SQL query statement') MSGTYPE(*ESCAPE)
ENDPGM