SQL
From MidrangeWiki
SQL, or Structured Query Language, is a platform independent way of accessing databases.
However, the version of SQL which runs on the AS/400, known as SQL400 or SQLDB2, does have syntax differences from other SQL dialects such as Microsoft SQL Server or Oracle, as well as DB2 on other platforms.
Detractors of DB2 for SystemI tend to point up these differences as deficiencies, but every vendor has differences that make each dialect somewhat incompatible.
Contents
- 1 Terminology
- 2 Tips
- 3 Pseudo Close
- 3.1 IBM Support Document 18874457
- 3.1.1 What Is a Pseudo Closed Cursor?
- 3.1.2 When Is a Cursor Pseudo Closed?
- 3.1.3 How Many Pseudo Closed Cursors Are Cached? Can This Be controlled?
- 3.1.4 What Happens When the Limit Is Reached?
- 3.1.5 What Are the Impacts of Pseudo Closed Cursors?
- 3.1.6 How Do I Force Pseudo Closed Cursors to Hard Close?
- 3.1.7 How Can I Track Pseudo Closed Cursor Reuse?
- 3.1.8 What are Common Reasons for Not Reusing a Pseudo Closed Cursor?
- 3.2 Pseudo Close solutions
- 3.1 IBM Support Document 18874457
- 4 References
- 5 See also
- 6 Categories
Terminology
| SQL Term | iSeries Term |
|---|---|
| TABLE | PHYSICAL FILE |
| ROW | RECORD |
| COLUMN | FIELD |
| INDEX | KEYED LOGICAL FILE ACCESS PATH |
| VIEW | NON-KEYED LOGICAL FILE |
| SCHEMA | LIBRARY COLLECTION |
| LOG | JOURNAL |
| ISOLATION LEVEL | COMMITMENT CONTROL LEVEL |
Tips
SQL7008 error
&FILEin&LIBRARYnot valid for operation.- -- Code 3 --
&FILEis not journaled, or you do not have authority to the journal. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.
- Resolution: Use
RUNSQLSTMwith parameterCOMMIT(*NONE)
SELECT *
- For the sake of example, use two files,
masterandtrans. We want to select all the columns intransbut only the name column from tablemaster. - If using *SYS naming you can execute
SELECT trans.*, name FROM trans JOIN master ON... - If using *SQL naming, we need to use a correlation name:
SELECT t.*, name FROM trans t JOIN master ON...
RUNSQL command for ad-hoc SQL statements
SQL via QM Query
Another method is based on Query Management Query. Create the QMQRY as all substitution variables and populate them in CL. One example is below.
QMQRY-RUNSQLSTM
&A&B&C&D&E&F&G&H&I&J&K&L&M&N&O&P&Q&R&S&T&U&V&W&X&Y&Z
CMD-RUNSQL Compile to use command processing program CL-RUNSQLSTM
CMD PROMPT('Run a SQL statement')
PARM KWD(SQLSTM) TYPE(*CHAR) LEN(1280) MIN(1) +
EXPR(*YES) PROMPT('SQL statement')
PARM KWD(OUTPUT) TYPE(*CHAR) LEN(8) RSTD(*YES) +
DFT(*) VALUES(* *PRINT *OUTFILE) +
EXPR(*YES) PROMPT('Output')
PARM KWD(OUTFILE) TYPE(OUTFILE) PMTCTL(FILE) +
PROMPT('File to receive output')
PARM KWD(OUTMBR) TYPE(*NAME) LEN(10) DFT(*FIRST) +
SPCVAL((*FIRST)) EXPR(*YES) +
PMTCTL(FILE) PROMPT('Member')
PARM KWD(MBROPT) TYPE(*CHAR) LEN(8) RSTD(*YES) +
DFT(*REPLACE) VALUES(*REPLACE *ADD) +
EXPR(*YES) PMTCTL(FILE) PROMPT('Replace +
or add records')
PARM KWD(PAGESIZE) TYPE(PAGESIZE) PMTCTL(PRINT) +
PROMPT('Page size:')
PARM KWD(LPI) TYPE(*DEC) LEN(1 0) RSTD(*YES) +
DFT(6) VALUES(6 8) EXPR(*YES) +
PMTCTL(PRINT) PROMPT('Lines per inch')
PARM KWD(CPI) TYPE(*DEC) LEN(3 0) RSTD(*YES) +
DFT(10) VALUES(10 12 15) EXPR(*YES) +
PMTCTL(PRINT) PROMPT('Characters per inch')
PARM KWD(OVRFLW) TYPE(*DEC) LEN(3 0) +
DFT(*PAGESIZE) RANGE(1 255) +
SPCVAL((*PAGESIZE 999)) EXPR(*YES) +
PMTCTL(PRINT) PROMPT('Overflow line number')
PARM KWD(PRTSTM) TYPE(*CHAR) LEN(4) RSTD(*YES) +
DFT(*YES) VALUES(*YES *NO) EXPR(*YES) +
PMTCTL(PRINT) PROMPT('Print SQL statement')
PARM KWD(RDB) TYPE(*CHAR) LEN(15) DFT(*NONE) +
SPCVAL((*NONE *CURRENT)) EXPR(*YES) +
PMTCTL(*PMTRQS) PROMPT('Relational database')
PARM KWD(QMFORM) TYPE(QMFORM) DFT(*SYSDFT) +
SNGVAL((*SYSDFT)) PMTCTL(*PMTRQS) +
PROMPT('Form to use to format output')
PARM KWD(NAMING) TYPE(*CHAR) LEN(11) RSTD(*YES) +
DFT(*SYS) VALUES(*SAA *SYS) EXPR(*YES) +
PMTCTL(*PMTRQS) PROMPT('Naming convention')
FILE: PMTCTL CTL(OUTPUT) COND((*EQ *OUTFILE))
PRINT: PMTCTL CTL(OUTPUT) COND((*EQ *PRINT))
OUTFILE: QUAL TYPE(*NAME) LEN(10) DFT(SQLOUT) EXPR(*YES)
QUAL TYPE(*NAME) LEN(10) DFT(QTEMP) +
SPCVAL((*CURLIB) (*LIBL)) EXPR(*YES) +
PROMPT('Library')
QMFORM: QUAL TYPE(*NAME) LEN(10) EXPR(*YES)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*CURLIB) (*LIBL)) EXPR(*YES) +
PROMPT('Library')
PAGESIZE: ELEM TYPE(*CHAR) LEN(3) DFT('066') RANGE('001' +
'255') FULL(*YES) EXPR(*YES) +
PROMPT('Length--lines per page')
ELEM TYPE(*CHAR) LEN(3) DFT('132') RANGE('001' +
'198') FULL(*YES) EXPR(*YES) +
PROMPT('Width--positions per line')
CLP RUNSQLSTM
/* RUN A SQL STATEMENT */
PGM (&SQLSTM +
&OUTPUT +
&OUTFILE +
&OUT_MBR +
&MBROPT +
&PAGESIZ +
&LPI +
&CPI +
&OVRFLW +
&PRTSTM +
&RDB +
&FORM +
&NAMING)
DCL &SQLSTM *CHAR 1280
DCL &OUTPUT *CHAR 8
DCL &OUTFILE *CHAR 20
DCL &OUT_MBR *CHAR 10
DCL &MBROPT *CHAR 8
DCL &OUT_NAM *CHAR 10
DCL &OUT_LIB *CHAR 10
DCL &NAMING *CHAR 11
DCL &RDB *CHAR 15
DCL &FORM *CHAR 20
DCL &FRM_NAM *CHAR 10
DCL &FRM_LIB *CHAR 10
DCL &PAGESIZ *CHAR 10
DCL &PG_LEN *DEC (3 0)
DCL &PG_WID *DEC (3 0)
DCL &CPI *DEC (3 0)
DCL &LPI *DEC (1 0)
DCL &OVRFLW *DEC (3 0)
DCL &PRTSTM *CHAR 4
DCL &A *CHAR 50
DCL &B *CHAR 50
DCL &C *CHAR 50
DCL &D *CHAR 50
DCL &E *CHAR 50
DCL &F *CHAR 50
DCL &G *CHAR 50
DCL &H *CHAR 50
DCL &I *CHAR 50
DCL &J *CHAR 50
DCL &K *CHAR 50
DCL &L *CHAR 50
DCL &M *CHAR 50
DCL &N *CHAR 50
DCL &O *CHAR 50
DCL &P *CHAR 50
DCL &Q *CHAR 50
DCL &R *CHAR 50
DCL &S *CHAR 50
DCL &T *CHAR 50
DCL &U *CHAR 50
DCL &V *CHAR 50
DCL &W *CHAR 50
DCL &X *CHAR 50
DCL &Y *CHAR 50
DCL &Z *CHAR 30
DCL &ERRORSW *LGL /* Std err */
DCL &MSGID *CHAR LEN(7) /* Std err */
DCL &MSGDTA *CHAR LEN(100) /* Std err */
DCL &MSGF *CHAR LEN(10) /* Std err */
DCL &MSGFLIB *CHAR LEN(10) /* Std err */
MONMSG MSGID(CPF0000 QWM0000) EXEC(GOTO STDERR1) /* Std err */
/* Parse the input */
CHGVAR &FRM_NAM %SST(&FORM 01 10)
CHGVAR &FRM_LIB %SST(&FORM 11 10)
CHGVAR &PG_LEN %SST(&PAGESIZ 03 03)
CHGVAR &PG_WID %SST(&PAGESIZ 06 03)
/* Adjust to fit 50 char blocks */
CALL RUNSQLSTM2 &SQLSTM
/* Set up the variables */
CHGVAR &A %SST(&SQLSTM 0001 50)
CHGVAR &B %SST(&SQLSTM 0051 50)
CHGVAR &C %SST(&SQLSTM 0101 50)
CHGVAR &D %SST(&SQLSTM 0151 50)
CHGVAR &E %SST(&SQLSTM 0201 50)
CHGVAR &F %SST(&SQLSTM 0251 50)
CHGVAR &G %SST(&SQLSTM 0301 50)
CHGVAR &H %SST(&SQLSTM 0351 50)
CHGVAR &I %SST(&SQLSTM 0401 50)
CHGVAR &J %SST(&SQLSTM 0451 50)
CHGVAR &K %SST(&SQLSTM 0501 50)
CHGVAR &L %SST(&SQLSTM 0551 50)
CHGVAR &M %SST(&SQLSTM 0601 50)
CHGVAR &N %SST(&SQLSTM 0651 50)
CHGVAR &O %SST(&SQLSTM 0701 50)
CHGVAR &P %SST(&SQLSTM 0751 50)
CHGVAR &Q %SST(&SQLSTM 0801 50)
CHGVAR &R %SST(&SQLSTM 0851 50)
CHGVAR &S %SST(&SQLSTM 0901 50)
CHGVAR &T %SST(&SQLSTM 0951 50)
CHGVAR &U %SST(&SQLSTM 1001 50)
CHGVAR &V %SST(&SQLSTM 1051 50)
CHGVAR &W %SST(&SQLSTM 1101 50)
CHGVAR &X %SST(&SQLSTM 1151 50)
CHGVAR &Y %SST(&SQLSTM 1201 50)
CHGVAR &Z %SST(&SQLSTM 1251 30)
IF (&OVRFLW *EQ 999) +
CHGVAR &OVRFLW (&PG_LEN - &LPI)
IF (&OUTPUT *EQ '*OUTFILE') DO
CHGVAR &OUT_NAM %SST(&OUTFILE 01 10)
CHGVAR &OUT_LIB %SST(&OUTFILE 11 10)
IF ((&OUT_LIB *NE '*CURLIB') *AND +
(&OUT_LIB *NE '*LIBL')) DO
CHKOBJ &OUT_LIB *LIB
MONMSG CPF9801 EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA('Output library' *BCAT +
&OUT_LIB *BCAT 'not found') +
MSGTYPE(*ESCAPE)
RCLRSC
RETURN
ENDDO
ENDDO
/* Run the SQL stmt via QM query */
IF (&FRM_NAM *EQ '*SYSDFT') DO
STRQMQRY QMQRY(RUNSQLSTM) +
OUTPUT(&OUTPUT) +
OUTFILE(&OUT_LIB/&OUT_NAM) +
OUTMBR(&OUT_MBR &MBROPT) +
SETVAR((A &A) +
(B &B) +
(C &C) +
(D &D) +
(E &E) +
(F &F) +
(G &G) +
(H &H) +
(I &I) +
(J &J) +
(K &K) +
(L &L) +
(M &M) +
(N &N) +
(O &O) +
(P &P) +
(Q &Q) +
(R &R) +
(S &S) +
(T &T) +
(U &U) +
(V &V) +
(W &W) +
(X &X) +
(Y &Y) +
(Z &Z)) +
NAMING(&NAMING) +
RDB(&RDB) +
QMFORM(*SYSDFT)
ENDDO
IF (&FRM_NAM *NE '*SYSDFT') DO
STRQMQRY QMQRY(RUNSQLSTM) +
OUTPUT(&OUTPUT) +
OUTFILE(&OUT_LIB/&OUT_NAM) +
OUTMBR(&OUT_MBR &MBROPT) +
SETVAR((A &A) +
(B &B) +
(C &C) +
(D &D) +
(E &E) +
(F &F) +
(G &G) +
(H &H) +
(I &I) +
(J &J) +
(K &K) +
(L &L) +
(M &M) +
(N &N) +
(O &O) +
(P &P) +
(Q &Q) +
(R &R) +
(S &S) +
(T &T) +
(U &U) +
(V &V) +
(W &W) +
(X &X) +
(Y &Y) +
(Z &Z)) +
NAMING(&NAMING) +
RDB(&RDB) +
QMFORM(&FRM_LIB/&FRM_NAM)
ENDDO
RCLRSC
RETURN
ENDDO
/* This one has no OUTFILE statement */
OVRPRTF QPQXPRTF PAGESIZE(&PG_LEN &PG_WID) +
LPI(&LPI) +
CPI(&CPI) +
CTLCHAR(*NONE) +
OVRFLW(&OVRFLW)
IF ((&PRTSTM *EQ '*YES') *AND +
(&OUTPUT *EQ '*PRINT')) +
CALL RUNSQLSTMR PARM(&A +
&B +
&C +
&D +
&E +
&F +
&G +
&H +
&I +
&J +
&K +
&L +
&M +
&N +
&O +
&P +
&Q +
&R +
&S +
&T +
&U +
&V +
&W +
&X +
&Y +
&Z) +
IF (&FRM_NAM *EQ '*SYSDFT') DO
STRQMQRY QMQRY(RUNSQLSTM) +
OUTPUT(&OUTPUT) +
SETVAR((A &A) +
(B &B) +
(C &C) +
(D &D) +
(E &E) +
(F &F) +
(G &G) +
(H &H) +
(I &I) +
(J &J) +
(K &K) +
(L &L) +
(M &M) +
(N &N) +
(O &O) +
(P &P) +
(Q &Q) +
(R &R) +
(S &S) +
(T &T) +
(U &U) +
(V &V) +
(W &W) +
(X &X) +
(Y &Y) +
(Z &Z)) +
NAMING(&NAMING) +
RDB(&RDB) +
QMFORM(*SYSDFT)
ENDDO
IF (&FRM_NAM *NE '*SYSDFT') DO
STRQMQRY QMQRY(RUNSQLSTM) +
OUTPUT(&OUTPUT) +
SETVAR((A &A) +
(B &B) +
(C &C) +
(D &D) +
(E &E) +
(F &F) +
(G &G) +
(H &H) +
(I &I) +
(J &J) +
(K &K) +
(L &L) +
(M &M) +
(N &N) +
(O &O) +
(P &P) +
(Q &Q) +
(R &R) +
(S &S) +
(T &T) +
(U &U) +
(V &V) +
(W &W) +
(X &X) +
(Y &Y) +
(Z &Z)) +
NAMING(&NAMING) +
RDB(&RDB) +
QMFORM(&FRM_LIB/&FRM_NAM)
ENDDO
RCLRSC
RETURN
STDERR1: /* Standard error handling routine */
/* Set "Error in progress" switch */
IF &ERRORSW SNDPGMMSG MSGID(CPF9999) +
MSGF(QCPFMSG) +
MSGTYPE(*ESCAPE) /* Func chk */
IF &ERRORSW (GOTO END)
CHGVAR &ERRORSW '1' /* Set to fail if error occurs */
/* Re-send diagnostic messages */
STDERR2:
RCVMSG MSGTYPE(*DIAG) +
PGMQ(*SAME *) +
MSGDTA(&MSGDTA) +
MSGID(&MSGID) +
MSGF(&MSGF) +
MSGFLIB(&MSGFLIB)
MONMSG CPF0000 EXEC(GOTO STDERR3)
IF (&MSGID *EQ ' ') GOTO STDERR3
SNDPGMMSG MSGID(&MSGID) +
MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) +
MSGTYPE(*DIAG)
GOTO STDERR2 /* Loop back for addl diagnostics */
/* Re-send escape messages */
STDERR3:
RCVMSG MSGTYPE(*EXCP) +
MSGDTA(&MSGDTA) +
MSGID(&MSGID) +
MSGF(&MSGF) +
MSGFLIB(&MSGFLIB)
MONMSG CPF0000
SNDPGMMSG MSGID(&MSGID) +
MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) +
MSGTYPE(*ESCAPE)
SNDPGMMSG MSGID(CPF9898) +
MSGF(QCPFMSG) +
MSGDTA('RUNSQLSTM failed') +
MSGTYPE(*ESCAPE)
END:
RCLRSC
ENDPGM
CLP-RUNSQLSTM2
/* Adjust SQL statement to fit within 50 character blocks */
PGM &SQLSTM
DCL &SQLSTM *CHAR 1280
DCL &LEFT *CHAR 1280
DCL &RIGHT *CHAR 1280
DCL &OUTPUT *CHAR 1280
DCL &COLUMN *DEC (5 0)
DCL &COL_1 *DEC (5 0)
DCL &WORK *DEC (5 0)
CHGVAR &COLUMN 50
CHGVAR &OUTPUT &SQLSTM
/* See if the block ends with a space */
CHECK:
IF (%SST(&OUTPUT &COLUMN 1) *NE ' ') (GOTO INCR)
/* Split block */
CHGVAR &COL_1 (&COLUMN + 1)
CHGVAR &WORK (1280-&COLUMN)
CHGVAR &LEFT %SST(&OUTPUT 1 &COLUMN)
CHGVAR &RIGHT %SST(&OUTPUT &COL_1 &WORK)
/* End of statement? */
IF (&LEFT *EQ ' ') (GOTO END)
IF (&RIGHT *EQ ' ') (GOTO END)
/* Insert blank on the front of the right block */
CHGVAR &RIGHT (' ' *CAT &RIGHT)
/* Put the halves together */
CHGVAR &OUTPUT (%SST(&LEFT 1 &COLUMN) *CAT &RIGHT)
/* Test next block */
INCR:
CHGVAR &COLUMN (&COLUMN+50)
IF (&COLUMN *LE 1250) (GOTO CHECK)
END:
CHGVAR &SQLSTM &OUTPUT
ENDPGM
RPGLE-RUNSQLSTMR
F* Print SQL statement on RUNSQLSTM output
F*
F* We resort to the array because the SQL statement is 1280
F* characters long, but RPG won't deal with that.
F*
fqpqxprtf o f 132 printer oflind(*inof)
d out s 50 dim(26)
c *entry plist
c parm a 50
c parm b 50
c parm c 50
c parm d 50
c parm e 50
c parm f 50
c parm g 50
c parm h 50
c parm i 50
c parm j 50
c parm k 50
c parm l 50
c parm m 50
c parm n 50
c parm o 50
c parm p 50
c parm q 50
c parm r 50
c parm s 50
c parm t 50
c parm u 50
c parm v 50
c parm w 50
c parm x 50
c parm y 50
c parm z 30
c*
c movel a out(01)
c movel b out(02)
c movel c out(03)
c movel d out(04)
c movel e out(05)
c movel f out(06)
c movel g out(07)
c movel h out(08)
c movel i out(09)
c movel j out(10)
c movel k out(11)
c movel l out(12)
c movel m out(13)
c movel n out(14)
c movel o out(15)
c movel p out(16)
c movel q out(17)
c movel r out(18)
c movel s out(19)
c movel t out(20)
c movel u out(21)
c movel v out(22)
c movel w out(23)
c movel x out(24)
c movel y out(25)
c movel z out(26)
c*
c time hhmmss 6 0
c except prttof
c*
c 1 do 26 xx 5 0
c out(xx) ifne *blanks
c except prtout
c endif
c enddo
c*
c except prtend
c seton lr
oqpqxprtf e prttof 2 03
o*
o udate y 8
o hhmmss 18 ' 0. . '
o 64 'sql statement'
o 127 'runsqlstm page'
o page 132
o*
o e prtout 1
o*
o out(xx) 50
o*
o e prtend 2
o*
o 24 '------------------------'
o 48 '------------------------'
o 50 '--'
PNLGRP-RUNSQLSTM
.* Help panel for RUNSQLSTM .* .* CRTPNLGRP PNLGRP(BUCK/RUNSQLSTM) .* SRCFILE(BUCK/QPNLSRC) .* :PNLGRP. .* ==================================================================== .* Use help from IBM's CPYF :IMPORT PNLGRP=QHCPCMD1 NAME='CPYF/MBROPT'. .* .* Use help from IBM's STRQMQRY :IMPORT PNLGRP=QHQXCMD NAME='STRQMQRY/NAMING'. :IMPORT PNLGRP=QHQXCMD NAME='STRQMQRY/OUTPUT'. :IMPORT PNLGRP=QHQXCMD NAME='STRQMQRY/OUTFILE'. :IMPORT PNLGRP=QHQXCMD NAME='STRQMQRY/OUTMBR'. :IMPORT PNLGRP=QHQXCMD NAME='STRQMQRY/QMFORM'. :IMPORT PNLGRP=QHQXCMD NAME='STRQMQRY/RDB'. .* .* Use help from IBM's OVRPRTF :IMPORT PNLGRP=QHDMCMD1 NAME='OVRPRTF/CPI'. :IMPORT PNLGRP=QHDMCMD1 NAME='OVRPRTF/LPI'. :IMPORT PNLGRP=QHDMCMD1 NAME='OVRPRTF/PAGESIZE'. :IMPORT PNLGRP=QHDMCMD1 NAME='OVRPRTF/OVRFLW'. .* .* Use help from Buck's index search :IMPORT PNLGRP=SCHHLP NAME='SQL'. :IMPORT PNLGRP=SCHHLP NAME='SQL_select'. :IMPORT PNLGRP=SCHHLP NAME='SQL_update'. :IMPORT PNLGRP=SCHHLP NAME='SQL_delete'. :IMPORT PNLGRP=SCHHLP NAME='SQL_insert'. .* ==================================================================== .* Bring all the parms together for the search index .* :HELP NAME='RUNSQLSTM/ALL'. :IMHELP NAME='RUNSQLSTM'. :IMHELP NAME='RUNSQLSTM/SQLSTM'. :IMHELP NAME='RUNSQLSTM/OUTPUT'. :IMHELP NAME='RUNSQLSTM/OUTFILE'. :IMHELP NAME='RUNSQLSTM/OUTMBR'. :IMHELP NAME='RUNSQLSTM/MBROPT'. :IMHELP NAME='RUNSQLSTM/PAGESIZE'. :IMHELP NAME='RUNSQLSTM/LPI'. :IMHELP NAME='RUNSQLSTM/CPI'. :IMHELP NAME='RUNSQLSTM/OVRFLW'. :IMHELP NAME='RUNSQLSTM/PRTSTM'. :IMHELP NAME='RUNSQLSTM/RDB'. :IMHELP NAME='RUNSQLSTM/QMFORM'. :IMHELP NAME='RUNSQLSTM/NAMING'. :EHELP. .* ==================================================================== .* Extended help .* :HELP NAME='RUNSQLSTM'. Run a SQL statement :P. This command executes a single SQL statement. :IMHELP NAME='SQL'. :P. The following SQL data definition statements are allowed: :UL COMPACT. :LI.COMMENT ON :LI.CREATE COLLECTION :LI.CREATE INDEX :LI.CREATE TABLE :LI.CREATE VIEW :LI.DROP COLLECTION :LI.DROP INDEX :LI.DROP TABLE :LI.DROP VIEW :LI.GRANT :LI.LABEL ON TABLE :LI.LABEL ON COLUMN :LI.LABEL ON VIEW :LI.LOCK TABLE :LI.REVOKE :EUL. :P. For help on data definition statements, see an appropriate SQL reference book. I've included minimal help here; sorry! :P. The following SQL data manipulation statements are allowed: :UL COMPACT. :LI.:LINK PERFORM='DSPHELP SQL_select'.SELECT:ELINK. :LI.:LINK PERFORM='DSPHELP SQL_update'.UPDATE:ELINK. :LI.:LINK PERFORM='DSPHELP SQL_delete'.DELETE:ELINK. :LI.:LINK PERFORM='DSPHELP SQL_insert'.INSERT INTO:ELINK. :EUL. :EHELP. .* ==================================================================== .* SQLSTM .* :HELP NAME='RUNSQLSTM/SQLSTM'. :HP1.SQL statement (SQLSTM) - Help:EHP1. :XH3.SQL statement (SQLSTM) :P.Specify the SQL statement you want to execute here. :UL COMPACT. :LI.:LINK PERFORM='DSPHELP SQL_select'.SELECT:ELINK. - Extract records from a file. Records may be displayed, printed or placed in a file. :LI.:LINK PERFORM='DSPHELP SQL_update'.UPDATE:ELINK. - Update records in a file. :LI.:LINK PERFORM='DSPHELP SQL_delete'.DELETE:ELINK. - Delete records from a file. :LI.:LINK PERFORM='DSPHELP SQL_insert'.INSERT INTO:ELINK. - Insert records into a file. :EUL. :EHELP. .* ==================================================================== .* OUTPUT .* Use IBM's help .* :HELP NAME='RUNSQLSTM/OUTPUT'. :IMHELP NAME='STRQMQRY/OUTPUT'. :EHELP. .* ==================================================================== .* OUTFILE .* Use IBM's help .* :HELP NAME='RUNSQLSTM/OUTFILE'. :IMHELP NAME='STRQMQRY/OUTFILE'. :EHELP. .* ==================================================================== .* OUTMBR .* Use IBM's help .* :HELP NAME='RUNSQLSTM/OUTMBR'. :IMHELP NAME='STRQMQRY/OUTMBR'. :EHELP. .* ==================================================================== .* MBROPT .* Use IBM's help .* :HELP NAME='RUNSQLSTM/MBROPT'. :IMHELP NAME='CPYF/MBROPT'. :EHELP. .* ==================================================================== .* PAGESIZE .* Use IBM's help .* :HELP NAME='RUNSQLSTM/PAGESIZE'. :IMHELP NAME='OVRPRTF/PAGESIZE'. :EHELP. .* ==================================================================== .* LPI .* Use IBM's help .* :HELP NAME='RUNSQLSTM/LPI'. :IMHELP NAME='OVRPRTF/LPI'. :EHELP. .* ==================================================================== .* CPI .* Use IBM's help .* :HELP NAME='RUNSQLSTM/CPI'. :IMHELP NAME='OVRPRTF/CPI'. :EHELP. .* ==================================================================== .* OVRFLW .* Use IBM's help .* :HELP NAME='RUNSQLSTM/OVRFLW'. :IMHELP NAME='OVRPRTF/OVRFLW'. :EHELP. .* ==================================================================== .* PRTSTM .* My own parm needs my own help .* :HELP NAME='RUNSQLSTM/PRTSTM'. :P. If you specified OUTPUT(*PRINT), specifies if you want the SQL statement printed along with the printed output. :P.Choices are: :PARML. :PT.:PK DEF.*YES:EPK.:PD.Print the SQL statement along with the report. :PT.:PK.*NO:EPK.:PD.Do not print the SQL statement. :EPARML. :EHELP. .* ==================================================================== .* RDB .* Use IBM's help .* :HELP NAME='RUNSQLSTM/RDB'. :IMHELP NAME='STRQMQRY/RDB'. :EHELP. .* ==================================================================== .* QMFORM .* Use IBM's help .* :HELP NAME='RUNSQLSTM/QMFORM'. :IMHELP NAME='STRQMQRY/QMFORM'. :EHELP. .* ==================================================================== .* NAMING .* Use IBM's help .* :HELP NAME='RUNSQLSTM/NAMING'. :IMHELP NAME='STRQMQRY/NAMING'. :EHELP. .* ==================================================================== .* End of source .* :EPNLGRP.
Program Notes:
One option when installing this onto your system is to rename the objects using RUNSQLSEL as the name, so there is no conflict with the new RUNSQL or RUNSQLSTM command from IBM.
The command is quite useful in CL or from the command line. Any SQL SELECT statement can be used. This includes SELECT with JOIN and GROUP BY. Quotes should go around the entire SELECT statement. An example:
RUNSQLSEL ('select phproj, ph.* from padbfad/papph ph where phproj like ''%799%'' ' )
RUNSQLSEL SQLSTM('select USAGRP from SCN000
group by USAGRP
order by USAGRP' )
If there is a particular member of the file to look at, issue an OVRDBF MBR( Mbrname ) prior to the SELECT command:
OVRDBF FILE( FILENAME ) MBR( MBRNAME ) SECURE(*YES) OVRSCOPE(*JOB) SHARE(*YES)
Hints: ( John Voris, Crown Holdings, June 2013 )
Paste in the &A&B&C . . . just as it appears on this page. Use the following command to compile the QMQRY object:<P> CRTQMQRY QMQRY(BUCK/RUNSQLSELQ) SRCFILE(BUCK/SOURCE)
To compile the command with Panel Help Text:
? CRTCMD ??CMD(BUCK/RUNSQLSEL)
??PGM(BUCK/RUNSQLSELC)
?*SRCFILE(BUCK/SOURCE)
?*SRCMBR(RUNSQLSEL)
HLPPNLGRP(BUCK/RUNSQLSELP)
HLPID(RUNSQLSELP) . . . . If the source member for Panel Help is called RUNSQLSELP
??REPLACE(*NO)
SQL via QSH
original post [[1]]
Use the db2 command in QShell as a really easy way to implement this sort of thing.
Write a simple QShell script like this:
#!/usr/bin/qsh LIB=$1 TABLE=$2 COL=$3 db2 "select $COL,count(*) from $LIB.$TABLE \ group by $COL, order by $COL" \ > /tmp/report.txt
Run that Qshell script (or submit it to batch, if you like)
SBMJOB CMD(QSH CMD('myscript.sh MYLIB MYTABLE MYCOL'))
Query Options File
- IBM i allows one to alter the behaviour of the Query optimiser by use of a file called QAQQINI IBM supply a template in QSYS which you can copy into QUSRSYS and alter to suit your needs. If you want a custom options file, copy it into your own library and use the command CHGQRYA to tell the system where to find it. The system uses the one in QUSRSYS as a system-wide default.
V5R4 query options
| Parameter | Value | Description | Notes |
|---|---|---|---|
| ALLOW_TEMPORARY_INDEXES | *DEFAULT | The default value is set to *YES. | |
| *YES | Allow temporary indexes to be considered. | ||
| *ONLY_ REQUIRED | Do not allow any temporary indexes to be considered for this access plan. Choose any other implementation regardless of cost to avoid the creation of a temporary index. Only if no viable plan can be found, will a temporary index be allowed. | ||
| APPLY_REMOTE | *DEFAULT | The default value is set to *YES. | |
| *NO | The CHGQRYA attributes for the job are not applied to the remote jobs. The remote jobs will use the attributes associated to them on their servers. | ||
| *YES | The query attributes for the job are applied to the remote jobs used in processing database queries involving distributed tables. For attributes where *SYSVAL is specified, the system value on the remote server is used for the remote job. This option requires that, if CHGQRYA was used for this job, the remote jobs must have authority to use the CHGQRYA command. | ||
| ASYNC_JOB_USAGE | *DEFAULT | The default value is set to *LOCAL. | |
| *LOCAL | Asynchronous jobs may be used for database queries that involve only tables local to the server where the database queries are being run. In addition, for queries involving distributed tables, this option allows the communications required to be asynchronous. This allows each server involved in the query of the distributed tables to run its portion of the query at the same time (in parallel) as the other servers. | ||
| *DIST | Asynchronous jobs may be used for database queries that involve distributed tables. | ||
| *ANY | Asynchronous jobs may be used for any database query. | ||
| *NONE | No asynchronous jobs are allowed to be used for database query processing. In addition, all processing for queries involving distributed tables occurs synchronously. Therefore, no inter-system parallel processing will occur. | ||
| CACHE_RESULTS | *DEFAULT | The default value is the same as *SYSTEM. | |
| *SYSTEM | The database manager may cache a query result set. A subsequent run of the query by that job or, if the ODP for the query has been deleted, by any job, will consider reusing the cached result set. | ||
| *JOB | The database manager may cache a query result set from one run to the next for a job, as long as the query uses a reusable ODP. When the reusable ODP is deleted, the cached result set is destroyed. This value mimics V5R2 processing. | ||
| *NONE | The database does not cache any query results. | ||
| COMMITMENT_CONTROL_ LOCK_LIMIT |
*DEFAULT | *DEFAULT is equivalent to 500,000,000. | |
| Integer Value | The maximum number of records that can be locked to a commit transaction initiated after setting the new value. The valid integer value is 1–500,000,000. | ||
| FORCE_JOIN_ORDER | *DEFAULT | The default is set to *NO. | |
| *NO | Allow the optimizer to reorder join tables. | ||
| *SQL | Only force the join order for those queries that use the SQL JOIN syntax. This mimics the behavior for the optimizer before V4R4M0. | ||
| *PRIMARY nnn | Only force the join position for the file listed by the numeric value nnn (nnn is optional and will default to 1) into the primary position (or dial) for the join. The optimizer will then determine the join order for all of the remaining files based upon cost. | ||
| *YES | Do not allow the query optimizer to reorder join tables as part of its optimization process. The join will occur in the order in which the tables were specified in the query. | ||
| IGNORE_DERIVED_INDEX | *DEFAULT | The default value is the same as *NO. | |
| *YES | Allow the SQE optimizer to ignore the derived index and process the query. The resulting query plan will be created without any regard to the existence of the derived index(s). The index types that are ignored include:
| ||
| *NO | Do not ignore the derived index. If a derived index exists, have CQE process the query. | ||
| IGNORE_LIKE_ REDUNDANT_SHIFTS |
*DEFAULT | The default value is set to *OPTIMIZE. | |
| *ALWAYS | When processing the SQL LIKE predicate or OPNQRYF command %WLDCRD built-in function, redundant shift characters are ignored for DBCS-Open operands. Note that this option restricts the query optimizer from using an index to perform key row positioning for SQL LIKE or OPNQRYF %WLDCRD predicates involving DBCS-Open, DBCS-Either, or DBCS-Only operands. | ||
| *OPTIMIZE | When processing the SQL LIKE predicate or the OPNQRYF command %WLDCRD built-in function, redundant shift characters may or may not be ignored for DBCS-Open operands depending on whether an index is used to perform key row positioning for these predicates. Note that this option will enable the query optimizer to consider key row positioning for SQL LIKE or OPNQRYF %WLDCRD predicates involving DBCS-Open, DBCS-Either, or DBCS-Only operands. | ||
| LIMIT_PREDICATE_ OPTIMIZATION |
*DEFAULT | Do not eliminate the predicates that are not simple isolatable predicates (OIF) when doing index optimization. Same as *NO. | |
| *NO | Do not eliminate the predicates that are not simple isolatable predicates (OIF) when doing index optimization. | ||
| *YES | Eliminate the predicates that are not simple isolatable predicates (OIF) when doing index optimization. | ||
| LOB_LOCATOR_THRESHOLD | *DEFAULT | The default value is set to 0. This indicates that the database will take no action to free locators. | |
| Integer Value | If the value is 0, then the database will take no action to free locators. For values 1 through 250,000, on a FETCH request, the database will compare the active LOB locator count for the job against the threshold value. If the locator count is greater than or equal to the threshold, the database will free host server created locators that have been retrieved. This option applies to all host server jobs (QZDASOINIT) and has no impact to other jobs. | ||
| MATERIALIZED_QUERY_ TABLE_REFRESH_AGE |
*DEFAULT | The default value is set to 0. | |
| 0 | No materialized query tables may be used. | ||
| *ANY | Any tables indicated by the MATERIALIZED_ QUERY_TABLE_USAGE INI parameter may be used. | ||
| timestamp_ duration | Only tables indicated by MATERIALIZED_ QUERY_TABLE_USAGE INI option which have a REFRESH TABLE performed within the specified timestamp duration may be used. | ||
| MATERIALIZED_QUERY_ TABLE_USAGE |
*DEFAULT | The default value is set to *NONE. | |
| *NONE | Materialized query tables may not be used in query optimization and implementation. | ||
| *ALL | User-maintained materialized query tables may be used. | ||
| *USER | User-maintained materialized query tables may be used. | ||
| MESSAGES_DEBUG | *DEFAULT | The default is set to *NO. | |
| *NO | No debug messages are to be displayed. | ||
| *YES | Issue all debug messages that are generated for STRDBG. | ||
| NORMALIZE_DATA | *DEFAULT | The default is set to *NO. | |
| *NO | Unicode constants, host variables, parameter markers, and expressions that combine strings will not be normalized. | ||
| *YES | Unicode constants, host variables, parameter markers, and expressions that combine strings will be normalized | ||
| OPEN_CURSOR_CLOSE_ COUNT |
*DEFAULT | *DEFAULT is equivalent to 0. See Integer Value for details. | |
| Integer Value | OPEN_CURSOR_CLOSE_COUNT is used in conjunction with OPEN_CURSOR_THRESHOLD to manage the number of open cursors within a job. If the number of open cursors, which includes open cursors and pseudo-closed cursors, reaches the value specified by the OPEN_CURSOR_THRESHOLD, pseudo-closed cursors are hard (fully) closed with the least recently used cursors being closed first. This value determines the number of cursors to be closed. The valid values for this parameter are 1 to 65536. The value for this parameter should be less than or equal to the number in the OPEN_CURSOR_THREHOLD parameter. This value is ignored if OPEN_CURSOR_THRESHOLD is *DEFAULT. If OPEN_CURSOR_THRESHOLD is specified and this value is *DEFAULT, the number of cursors closed is equal to OPEN_CURSOR_THRESHOLD multiplied by 10 percent and rounded up to the next integer value. | ||
| OPEN_CURSOR_ THRESHOLD |
*DEFAULT | *DEFAULT is equivalent to 0. See Integer Value for details. | |
| Integer Value | OPEN_CURSOR_THRESHOLD is used in conjunction with OPEN_CURSOR_CLOSE_COUNT to manage the number of open cursors within a job. If the number of open cursors, which includes open cursors and pseudo-closed cursors, reaches this threshold value, pseudo-closed cursors are hard (fully) closed with the least recently used cursors being closed first. The number of cursors to be closed is determined by OPEN_CURSOR_CLOSE_COUNT. The valid user-entered values for this parameter are 1 - 65536. Having a value of 0 (default value) indicates that there is no threshold and hard closes will not be forced on the basis of the number of open cursors within a job. | ||
| OPTIMIZATION_GOAL | *DEFAULT | Optimization goal is determined by the interface (ODBC, SQL precompiler options, OPTIMIZE FOR nnn ROWS clause). | |
| *FIRSTIO | All queries will be optimized with the goal of returning the first page of output as fast as possible. This goal works well when the control of the output is controlled by a user who is most likely to cancel the query after viewing the first page of output data. Queries coded with an OPTIMIZE FOR nnn ROWS clause will honor the goal specified by the clause. | ||
| *ALLIO | All queries will be optimized with the goal of running the entire query to completion in the shortest amount of elapsed time. This is a good option for when the output of a query is being written to a file or report, or the interface is queuing the output data. Queries coded with an OPTIMIZE FOR nnn ROWS clause will honor the goal specified by the clause. | ||
| OPTIMIZE_STATISTIC_ LIMITATION |
*DEFAULT | The amount of time spent in gathering index statistics is determined by the query optimizer. | |
| *NO | No index statistics will be gathered by the query optimizer. Default statistics will be used for optimization. (Use this option sparingly.) | ||
| *PERCENTAGE integer value | Specifies the maximum percentage of the index that will be searched while gathering statistics. Valid values for are 1 to 99. | ||
| *MAX_ NUMBER_ OF_RECORDS_ ALLOWED integer value | Specifies the largest table size, in number of rows, for which gathering statistics is allowed. For tables with more rows than the specified value, the optimizer will not gather statistics and will use default values. | ||
| PARALLEL_DEGREE | *DEFAULT | The default value is set to *SYSVAL. | |
| *SYSVAL | The processing option used is set to the current value of the system value, QQRYDEGREE. | ||
| *IO | Any number of tasks can be used when the database query optimizer chooses to use I/O parallel processing for queries. SMP parallel processing is not allowed. | ||
| *OPTIMIZE | The query optimizer can choose to use any number of tasks for either I/O or SMP parallel processing to process the query or database file keyed access path build, rebuild, or maintenance. SMP parallel processing is used only if the system feature, DB2® Symmetric Multiprocessing for i5/OS®, is installed. Use of parallel processing and the number of tasks used is determined with respect to the number of processors available in the server, this job has a share of the amount of active memory available in the pool in which the job is run, and whether the expected elapsed time for the query or database file keyed access path build or rebuild is limited by CPU processing or I/O resources. The query optimizer chooses an implementation that minimizes elapsed time based on the job has a share of the memory in the pool. | ||
| *OPTIMIZE xxx | This option is very similar to *OPTIMIZE. The value xxx indicates the ability to specify an integer percentage value from 1-200. The query optimizer determines the parallel degree for the query using the same processing as is done for *OPTIMIZE, Once determined, the optimizer will adjust the actual parallel degree used for the query by the percentage given. This provides the user the ability to override the parallel degree used to some extent without having to specify a particular parallel degree under *NUMBER_OF_TASKS. | ||
| *MAX | The query optimizer chooses to use either I/O or SMP parallel processing to process the query. SMP parallel processing will only be used if the system feature, DB2 Symmetric Multiprocessing for i5/OS, is installed. The choices made by the query optimizer are similar to those made for parameter value *OPTIMIZE except the optimizer assumes that all active memory in the pool can be used to process the query or database file keyed access path build, rebuild, or maintenance. | ||
| *NONE | No parallel processing is allowed for database query processing or database table index build, rebuild, or maintenance. | ||
| *NUMBER_OF _TASKS nn | Indicates the maximum number of tasks that can be used for a single query. The number of tasks will be capped off at either this value or the number of disk arms associated with the table. | ||
| PARAMETER_MARKER_ CONVERSION |
*DEFAULT | The default value is set to *YES. | |
| *NO | Constants cannot be implemented as parameter markers. | ||
| *YES | Constants can be implemented as parameter markers. | ||
| QUERY_TIME_LIMIT | *DEFAULT | The default value is set to *SYSVAL. | |
| *SYSVAL | The query time limit for this job will be obtained from the system value, QQRYTIMLMT. | ||
| *NOMAX | There is no maximum number of estimated elapsed seconds. | ||
| integer value | Specifies the maximum value that is checked against the estimated number of elapsed seconds required to run a query. If the estimated elapsed seconds is greater than this value, the query is not started. Valid values range from 0 to 2,147,352,578. | ||
| REOPTIMIZE_ACCESS_PLAN | *DEFAULT | The default value is set to *NO. | |
| *NO | Do not force the existing query to be reoptimized. However, if the optimizer determines that optimization is necessary, the query will be reoptimized. | ||
| *YES | Force the existing query to be reoptimized. | ||
| *FORCE | Force the existing query to be reoptimized. | ||
| *ONLY_ REQUIRED | Do not allow the plan to be reoptimized for any subjective reasons. For these cases, continue to use the existing plan since it is still a valid workable plan. This may mean that you may not get all of the performance benefits that a reoptimization plan may derive. Subjective reasons include, file size changes, new indexes, and so on. Non-subjective reasons include, deletion of an index used by existing access plan, query file being deleted and recreated, and so on. | ||
| SQLSTANDARDS_MIXED_ CONSTANT |
*DEFAULT | The default value is set to *YES. | |
| *YES | SQL IGC constants will be treated as IGC-OPEN constants. | ||
| *NO | If the data in the IGC constant only contains shift-out DBCS-data shift-in, then the constant will be treated as IGC-ONLY, otherwise it will be treated as IGC-OPEN. | ||
| SQL_FAST_DELETE_ROW_COUNT | *DEFAULT | The default value is set to 0. Having a value of 0 indicates that the database manager will choose how many rows to consider when determining whether fast delete should be used instead of a traditional delete. When using the default value, the database manager will most likely use 1000 as a row count. This means that using the INI option with a value of 1000 result in no operational difference than using 0 for the option. | |
| *NONE | This value will force the database manager to never attempt to fast delete on the rows. | ||
| *OPTIMIZE | This value is same as using *DEFAULT. | ||
| integer value | Specifying a value for this option allows the user to tune the behavior of DELETE. The target table for the DELETE statement must match or exceed the number of rows specified on the option, for fast delete to be attempted. A fast delete will not write individual rows into a journal. The valid values range from 1 to 999,999,999,999,999. | ||
| SQL_STMT_COMPRESS_MAX | *DEFAULT | The default value is set to 2, which indicates that the access plan associated with any statement will be removed after a statement has been compressed twice without being executed. | |
| Integer Value | The integer value represents the number of times that a statement is compressed before the access plan is removed to create more space in the package. Note that executing the SQL statement resets the count for that statement to 0. The valid Integer values are 1 to 255. | ||
| SQL_SUPPRESS_WARNINGS | *DEFAULT | The default value is set to *NO. | |
| *YES | Examine the SQLCODE in the SQLCA after execution of a statement. If the SQLCODE is + 30, then alter the SQLCA so that no warning is returned to the caller. Set the SQLCODE to 0, the SQLSTATE to '00000' and SQLWARN to ' '. | ||
| *NO | Specifies that SQL warnings will be returned to the caller. | ||
| SQL_TRANSLATE_ASCII_ TO_JOB |
*DEFAULT | The default value is set to *NO. | |
| *YES | Translate ASCII SQL statement text to the CCSID of the iSeries® job. | ||
| *NO | Translate ASCII SQL statement text to the EBCIDIC CCSID associated with the ASCII CCSID. | ||
| STAR_JOIN (see note) | *DEFAULT | The default value is set to *NO | |
| *NO | The EVI Star Join optimization support is not enabled. | ||
| *COST | Allow query optimization to consider (cost) the usage of EVI Star Join support. The determination of whether the Distinct List selection is used will be determined by the optimizer based on how much benefit can be derived from using that selection. | ||
| STORAGE_LIMIT | *DEFAULT | The default value is set to *NOMAX. | |
| *NOMAX | Never stop a query from running because of storage concerns. | ||
| Integer Value | The maximum amount of temporary storage in megabytes that may be used by a query. This value is checked against the estimated amount of temporary storage required to run the query as calculated by the query optimizer. If the estimated amount of temporary storage is greater than this value, the query is not started. Valid values range from 0 through 2147352578. | ||
| SYSTEM_SQL_STATEMENT_ CACHE |
*DEFAULT | The default value is set to *YES. | |
| *YES | Examine the SQL system-wide statement cache when an SQL prepare request is processed. If a matching statement already exists in the cache, use the results of that prepare. This allows the application to potentially have better performing prepares. | ||
| *NO | Specifies that the SQL system-wide statement cache should not be examined when processing an SQL prepare request. | ||
| UDF_TIME_OUT | *DEFAULT | The amount of time to wait is determined by the database. The default is 30 seconds. | [2] |
| *MAX | The maximum amount of time that the database will wait for the UDF to finish. | ||
| integer value | Specify the number of seconds that the database should wait for a UDF to finish. If the value given exceeds the database maximum wait time, the maximum wait time will be used by the database. Minimum value is 1 and maximum value is system defined. | ||
| VARIABLE_LENGTH_ OPTIMIZATION |
*DEFAULT | The default value is set to *YES. | |
| *YES | Allow aggressive optimization of variable length columns. Allows index only access for the column(s). It also allows constant value substitution when an equal predicate is present against the column(s). As a consequence, the length of the data returned for the variable length column may not include any trailing blanks that existed in the original data. | ||
| *NO | Do not allow aggressive optimization of variable length columns. |
Notes:
- ↑ Business Intelligence Indexing and statistics strategies for DB2 UDB for iSeries by Michael W. Cain iSeries Teraplex Integration Center, December 2003, Version 3.0, Template:PDF
- ↑ Only modifies the environment for the Classic Query Engine.
There is an IBM Support Technical Document that describes in-depth debugging using additional internal QAQQINI options. Document number 462591814
SQL Settings
On V5R4, using OLAP can mean having a good look at the value of IGNORE_DERIVED_INDEX. One may want to set it to YES in order to use a heritage database with OLAP.
- Adjust Default Query Optimizer Settings with QAQQINI Midrange Guru, Sept 2003.
- My Own Private QAQQINI MC Press Online, May 2001
Pseudo Close
IBM i added new support for enhanced SQL performance in the V4 time frame called pseudo close. Pseudo close support allows DB2 to reuse an SQL access path. One side effect is that DB2 keeps a lock on the file, making it impossible for another process (like CLRPFM) to gain an exclusive lock.
Here is an IBM document on pseudo close support:
IBM Support Document 18874457
IBM Software Technical Document Document Number: 18874457 ____________________________________________________________
Functional Area: Host Servers SubFunctional Area: Database Server SubSubFunctional Area: General
____________________________________________________________
Product: 400-12 HOST SUPPORT (5769SS1HS); OS/400 BASE (5722SS100) Release: V4R3M0; V4R4M0; V4R5M0; V5R1M0; V5R2M0; V5R3M0; V5R4M0 Classification: IBM Internal Use Keywords: JDBC, ODBC, SQL, SQLCLI
____________________________________________________________
Document Title:Pseudo Closed Cursor FAQ
Document Description:
What Is a Pseudo Closed Cursor?
Pseudo closed cursors are a key part of a performance optimization feature of IBM® DB2®/400 SQL. When an application closes a cursor, DB2/400 normally closes the cursor and closes the file (deleting the open data path - ODP). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. DB2/400 can then reuse the cursor as needed without the overhead of a full open of the file. DB2/400 can also decide to hard close the pseudo closed cursor when needed.
When Is a Cursor Pseudo Closed?
This varies depending on PTF level. In general, the first time a dynamic SQL statement is prepared, run, and closed, the cursor is hard closed, the ODP is deleted, and the file is closed. However, DB2/400 keeps track of the SQL statement and can detect if it is run a second time. DB2/400 tracks the number of times the statement is used against the number of times the program is invoked. These statistics are kept in the SQL statement area in the SQL package, Prepared Statement Area, or Program Associated Space. If the same statement is run a second time, the cursor is pseudo closed and cached (it is left in the Open Cursor List but a bit is set showing the cursor as pseudo closed). Further running of the same SQL statement can then use the existing ODP (assuming, of course, that it is reusable). Note that R420 APAR SA76930 enabled support for forcing cursors to pseudo close (rather than hard close) on the first execution. In R520 IBM® OS/400®, statements executed out of a SQL package may be pseudo-closed after the first execution.
How Many Pseudo Closed Cursors Are Cached? Can This Be controlled?
The number of pseudo closed cursors that are held is set by IBM and can be changed at any time. Internally, pseudo closed cursor are tracked differently depending on how they are named. Cursors that reuse an existing cursor's name are treated differently than cursors that have a unique name.
The current default setting for uniquely named cursors is no limit. The limit can be controlled in R450 operating system and later by the OPEN_CURSOR_THRESHOLD QAQQINI file option. For additional information, refer to the Rochester Support Center knowledgebase document 16262423, Use of QAQQINI File. The limit is enforced only during a full open. Therefore, some applications may exceed the limit.
If the application reuses the same cursor name (close cursor, drop statement, allocate a new statement which uses the previous cursor name), the pseudo closed cursor is treated differently. DB2/400 must rename the cursor, cache the new name, and match a later execute of the same statement with the renamed cursor. At this time, the limit to these pseudo closed cursors is 150. This limit is controlled through the data area QSQCSRT. However, this is not documented for customer use. The limit is enforced on a prepare. Therefore, it is possible for jobs to exceed this limit. For example, statements executed from an extended dynamic packages are not prepared. In situations like this, the 150 cursor limit can be exceeded.
What Happens When the Limit Is Reached?
By default, all cursors are hard closed and the entire list is cleared when the limit is reached (this is subject to change). The cursor close behavior can be adjusted using the OPEN_CURSOR_CLOSE_COUNT QAQQINI file option. This option affects only the uniquely named cursor list. The list of 150 renamed ("dummy") cursors is always cleared when the limit is reached. For additional information, refer to Rochester Support Center knowledgebase document 16262423, Use of QAQQINI File.
What Are the Impacts of Pseudo Closed Cursors?
- Increase use of storage:
- Each pseudo closed cursor represents a significant amount of storage used for the associated resources. A typical value is 1 Meg per cursor. The number of pseudo closed cursors can be reduced (see above) to decrease storage per job.
- Shared file locks even when all cursors are closed:
- As stated above, a pseudo closed cursor does not hold any record locks; however, it will continue to hold a shared lock on the file. In theory, the shared lock left on the file should have a minimal effect on other applications. Some operating system commands and all SQL statements that require an exclusive lock (DLTF, DROP TABLE, CLRPFM, and so on) will cause a pseudo closed cursor to be hard closed so that the lock is released. Each command that wants to force closing of pseudo closed cursors causes an event to be signaled if a lock conflict occurs. This event causes a program to run that hard-closes any pseudo closed cursors that hold the object. For this to function correctly, the maximum file wait time (WAITFILE) must be set to a value other than *IMMED. In general, a value of 1 second or greater is sufficient (actual time depends on system performance).
Because hard closing of pseudo closed cursor is dependent on the implementation of each command, the behavior can vary between commands, operating system releases, and even PTFs. For example, the command ALCOBJ does not force pseudo closed cursors to hard close in R430 or later. It will return the message CPF3156 stating that the object cannot be allocated. Most IBM RPG/400® and COBOL programs using native (non-SQL) database file functions that require an exclusive lock also fail if pseudo closed cursors exist.
How Do I Force Pseudo Closed Cursors to Hard Close?
In R450 and later, you can force closing of the pseudo closed cursors of other jobs by specifying *RQSRLS for the Lock conflict action parameter of the ALCOBJ command. This option is new to R450 of OS/400. The syntax is:
ALCOBJ OBJ((QCUSTCDT *FILE *EXCL)) CONFLICT(*RQSRLS)
Note: This command does not close pseudo closed cursors scoped to the current job. So, for example, if you run a command that requires the file be hard closed (in addition to an exclusive lock) and the current job has pseudo closed cursors, then the *RQSRLS option will not work. You must use one of the techniques described below to hard close the cursors. The CHGPFCST command is one example of a command that requires a hard closed file.
To close all pseudo closed cursors in the current job the application can:
- Disconnect from the database server.
- Execute the statements DISCONNECT ALL; CONNECT RESET to essentially disconnect and reconnect to the local database.
Note: Both techniques have adverse effects on performance and should be avoided if at all possible.
If a lock is still held after trying the ALCOBJ *RQSRLS, the lock is most likely held by an open cursor. Verify that the application actually issued the close cursor command and committed any open transaction. If using Client Access ODBC or IBM Toolbox for Java JDBC, verify that the lazy close option is disabled.
How Can I Track Pseudo Closed Cursor Reuse?
The database monitor utility, DBMON, can be used. The QQUCNT field on the open (1000 format) identifies a unique ODP (full open) while QQRCNT is incremented each time the ODP is used (a pseudo open). QQC11 of the 3010 records shows if the ODP is reusable.
In R440, the SQL Performance Monitor feature of Operations Navigator can be used to easily check for statement reuse, reusable ODPs, number of full opens, and number of pseudo opens (reuse of pseudo closed cursor). Run the statement summary Basic Statement query from the Detailed Results tab. Starting debug on a job (STRDBG command) will add additional debug messages to the job's joblog. These messages can also be used to identify cursor reuse.
What are Common Reasons for Not Reusing a Pseudo Closed Cursor?
The query must be implemented with a reusable ODP. All attributes of the cursor must be the same (isolation level, ability to be updated, cursor type and so on). The SQL Statement text must match exactly (including white space and the value of any literals). Anything that might cause an access plan rebuild can also prevent reuse.
Reuse of pseudo closed cursors is highly dependent on application design and, to a lesser extent, operating system PTF levels and Client PTF levels (ODBC, JDBC, and so on). Ideally, the database will always match a later execution of the same SQL statement to an existing pseudo closed cursor if one is available. This behavior is not documented nor guaranteed (problems are not accepted as defect).
For best reuse applications need to follow these guidelines:
- Use connection pooling.
- Use parameterized SQL Statements.
- Use a statement cache.
Pseudo Close solutions
There are instances where a pseudo close is undesirable. One example is a web application using an ODBC connexion to a stored procedure. Because that stored procedure uses the same SQL statement over and over, execution of an SQL CLOSE will actually result in a pseudo close, making it impossible for commands like SAVOBJ or CLRLIB to get an exclusive lock.
IBM has added a new parameter to ALCOBJ:
Lock conflict action . . . . . . CONFLICT(*NORQSRLS)
*RQSRLS
Specify the action to be taken if a lock conflict exists. This
parameter is only supported for database files and is ignored for
all other objects. The supported database *FILE objects are:
* physical file
* logical file
* distributed file
*NORQSRLS
No requests are sent to other jobs or threads which are holding
conflicting locks.
*RQSRLS
A request is sent to the system code running in each job or
thread that is holding a conflicting lock for the specified
object. Notification of lock contention is not visible to user
applications which hold conflicting locks. Only locks which are
acquired implicitly by system code are eligible to be released.
Locks acquired explicitly by user application code are not
eligible to be released. If *RQSRLS specified for a distributed
file, the request to release the lock is sent to each node in
the node group that holds a conflicting lock.
References
See also
- See also: SQL Primer and SOUNDEX
- See also on Wikipedia: SQL
- "TechTip: Calling SQL from REXX" Written by Joe Pluta, Thursday, 24 February 2005
