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' ) ;
Dale EdmondsAsked:
Who is Participating?
 
Dave FordSoftware Developer / Database AdministratorCommented:
It all boils down to "what are you trying to accomplish"? From the looks of it, I thought you were trying to return a result-set from your stored procedure.

That's why you'd need the DECLARE CURSOR. That's how you return a result-set.

If you're trying to get some value into a variable, you'd use "SELECt <somestuff> INTO <:someHostVariable> FROM <someTable>", but if you want to return a result-set, you need to DECLARE CURSOR.

So, what exactly ARE you trying to accomplish?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
 
Dale EdmondsAuthor Commented:
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;
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Dave FordSoftware Developer / Database AdministratorCommented:
> 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
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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.
0
 
Dale EdmondsAuthor Commented:
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.
0
 
Dale EdmondsAuthor Commented:
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.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
I think there is just a fundamental mis-understanding going on here, probably due to the complexity of the query.

Let's break it down:

1) VE works on SELECT statements.  Not DECLARE CURSOR, and not CREATE PROCEDURE.  So to get VE to help you, you have to pare it down ti just the SELECT.

2) A SELECT that uses one of more CTEs is just a SELECT.  Forget about the CTEs. Even though they come first, this is still just a SELECT statement.

3) A stand-alone SELECT statement doesn't do anything useful in a procedure, since it provides no interface to CAPTURE THE RESULT SET.  So CREATE PROCEDURE, as part of the syntax check, doesn't allow a "naked" select - it recognizes it for the programming error that it is.  

4) In a procedure, if you have a query that produces a single row result set, you can use SELECT INTO to capture the returned data into host variables, or you can use a cursor.  If you have a query that may produce a multiple row result set, you must use a cursor to access the result set produced by the SELECT.  You don't have any mention of a cursor, so the error is trying to tell you that you that you need to change the SELECT to a SELECT INTO and provides some host variables to store the result of the query.

5) This procedure is coded to return a result set: "DYNAMIC RESULT SETS 1 ".    The cursor C1 is exactly what is returned by this procedure.  It is the whole point of the procedure.  Strip out the cursor, and you break the whole thing.

So, if you want to run a SELECT in an SQL procedure, you have to code an interface that allows you to retrieve the results of that query.  If you want to create a procedure that returns a result set, you have to create a cursor.  If you want to return a single row, you would typically not return a result set and instead return a list of variables containing the results from a SELECT INTO.  

In your modified code, you haven't done either of those things, so CREATE PROCEDURE fails with SQL0029.  Even if it somehow got past that, and coded a SELECT INTO, you've said you were going to return a result set, and to do that you need to open a cursor- so it would fail for that reason, too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.