SQL and pre-opened data paths

[jayvaughn]

can someone explain why this insert was allowed to execute although the the ovrdbf/opndbf were in effect?

would the option(*INP) restrict the insert from sql? my guess is that SQL ignores any pre-open/shares and uses its own data path?


on command line...

OVRDBF FILE(QUE27) TOFILE(TSTJHV/QUE27) SHARE(*YES)
OPNDBF FILE(TSTJHV/QUE27) OPTION(*INP)

then ran this pgm...

0001.00 H option(*srcstmt:*nodebugio)
0002.00 H dftactgrp(*no) actgrp(*caller)
0003.00 *----------------------------------------------------------
0004.00 * SQL Default Options
0005.00 *----------------------------------------------------------
0006.00 exec sql
0007.00 set option
0008.00 commit = *NONE,
0009.00 closqlcsr = *ENDMOD,
0010.00 datfmt = *ISO;
0010.01
0010.02
0010.03 exec sql insert into tstjhv.que27
0010.04 (select * from tstjhv.que27
0010.05 fetch first 1 row only);
0048.04
0049.00 *inlr = *on;

[B.Hauser]

AFAIK SQL does not care about OPNDBF because SQL runs through an optimization, in which an access plan is built or validated. The access plan describes which access methods and if (Table Scan) and which access paths (indexes, LF, Key Constraints) are to be used. After the ODP (Open Data Path) is opened, based on the access plan.

BTW CLOQLCSR = *ENDMOD makes ODPs not reusable, i.e. as soon as the module is left the ODP is deleted (HARD CLOSE). All sub-sequent calls must run through the complete optimization, and cannot reuse a previously opened access path (FULL OPEN). Opening the data path is the most time consuming part of the optimization. PSEUDO Open, i.e. an opened data path can be reused, and only the data within the data path is updated.

Whether the CLOSQLCSR is set to *ENDMOD or *ENDACTGRP does not matter in your example, because you perform an OVRDBF. The ODP gets closed as soon as the DLTOVR is performed.

Birgitta

[jayvaughn]

that's good stuff to know - thank you Birgitta