When trying to run EXPLAIN on the following stored procedure we get the error message:
'Cannot display the query diagram because the query access plan information was not collected for the query'.
-- Generate SQL
-- Version: V6R1M0 080215
-- Generated on: 04/28/15 14:48:42
-- -- Standards Option: DB2 for i
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","USER" ;
CREATE PROCEDURE ADC/PROVIDERS_SELECT_V3_dee(
IN IN_FACILITY_CODE CHAR(4) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC ADC/PROVIDERS_SELECT_V3_dee
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
CLOSQLCSR = *ENDMOD ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE C1 CURSOR FOR
WITH
ACTIVEPROVIDERS AS (
SELECT
PIPRV@ AS ID ,
PINAMD AS NAME ,
PIPRVT AS TYPE ,
PIDEL@ AS STATUS ,
PIDTTERM AS TERMINATIONDATE ,
PWEXTVAL AS DOSESPOTCLINICIANID
FROM
PRVMAS PM LEFT OUTER JOIN
PRVEXT ON PIPRV@ = PWPRV@ AND PWEXT@ = 'CI'
WHERE
PIPRV@ <> '***' AND
PIABRV <> '.' AND
PIDEL@ = 'A' AND
PIDTTERM = '1940-01-01' ) ,
LOCALPROVIDERS AS (
SELECT
BWPRV@ AS ID
FROM
FACPRVLOOK
WHERE
BWFAC@ = '4131'
)
SELECT
AP . ID ,
AP . NAME ,
AP . TYPE ,
AP . STATUS ,
AP . TERMINATIONDATE ,
AP . DOSESPOTCLINICIANID ,
CASE WHEN LP . ID IS NULL THEN 'False' ELSE 'True' END AS ISLOCAL
FROM
ACTIVEPROVIDERS AP LEFT OUTER JOIN
LOCALPROVIDERS LP ON AP . ID = LP . ID
FOR READ ONLY ;
OPEN C1 ;
END ;
COMMENT ON SPECIFIC PROCEDURE ADC/PROVIDERS_SELECT_V3
IS 'Returns list of all providers for ADC' ;
COMMENT ON PARAMETER SPECIFIC PROCEDURE ADC/PROVIDERS_SELECT_V3
( IN_FACILITY_CODE IS 'Facility Code' ) ;
2. Does it do what you expect it to do?
3. Can you successfully use Visual Explain on the associated SELECT statement?
-- DaveSlash