Link to home
Start Free TrialLog in
Avatar of Dale Edmonds
Dale Edmonds

asked on

iSeries Stored Procedure with SQL CTE Error

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' ) ;
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image

1. Have you been able to successfully RUN the stored procedure?
2. Does it do what you expect it to do?
3. Can you successfully use Visual Explain on the associated SELECT statement?

-- DaveSlash
Avatar of Dale Edmonds
Dale Edmonds

ASKER

1. No, can't get to the creation of stored procedure with error.

2. Yes, expected results in SQL realized.

3. No, and Yes.  If I remove the Declare Cursor and use the code below I can get Explain to work.

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;
> No, can't get to the creation of stored procedure with error

That explains why the Visual Explain won't work. If there are errors in the code, Visual Explain won't help at all. First, fix any errors in your code. Then, try Visual Explain.

Based on past experience, I would recommend getting a "simple-case" to work before adding more complexity. In other words, use an EXTREMELY simple select-statement, get the proc working. Then, gradually add more complexity to the query, compiling as you go.

HTH,
DaveSlash
As far as I'm aware, Visual Explain supports the following types of SQL statements: SELECT, INSERT, UPDATE, and DELETE.   DECLARE CURSOR isn't supported - need to unwrap the SELECT and VE that.
Yes, I agree, and I am sorry I am not being clear about the question.  I have no issue with cutting and pasting out the SQL to EXPLAIN>  I also agree to start simple and build but I must say that is what is done here.  

My question is related to why with a CTE there was still a Declare Cursor required to get this to create as Stored Procedure.  If you leave off the Declare Cursor the SQL is still fine but then iNavigator throws error stating an INTO is required.  

Once the Declare Cursor is back the stored procedure will create.  I see there are some PTF chats previously on IBM KB but we are current with what the APAR was discussing.

So basic question is why is Declare Cursor being required when we really don't want to use because of the single row processing and system performance impact.  

We have, like most today, a very SQL request heavy system because of User Window interface and of course all the I/O is QZDASOINIT related.  Most due to poorly developed SQL.  

Thanks for thoughts.
Just FYI.  This code does work and created stored procedure as we intended.  This is the error when the Declare Cursor is not there:  SQL0029 INTO clause missing from embedded statement.   Any thoughts?  Thank you all.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial