DB2 Store Procedure with Parameters in Create View

[ALezama]

Hello ...

I'm trying to create a Stored Procedure that receives two parameters (both are Date). The SP must create a View from a Select containing two JOIN and three WHERE conditions involving the two parameters.

I am working with DB2 9.7 FixPack 2 on Windows 7 32-bit and IBM Data Studio 2.2.

The problem I have is that by making the deployment of the SP returns the following error:

COBR.SP_CREATEVIEWMOVCUSTOMER: 17: "STARTDATEMONITOR" is not valid in the context .. Where It is Used SQLCODE =- 206, SQLSTATE = 42703, DRIVER = 3.61.65

The code of my SP is as follows:

CREATE PROCEDURE SP_CreateViewMovCustomer (StartDateMonitor IN DATE,
FinishDateMonitor IN DATE)
 
SPECIFIC SP_CreateViewMovCustomer
 
P1: BEGIN
 
If Exists (Select 1 From SYSIBM.SYSVIEWS Where Name = 'VIEWMOVCUSTOMER' and creator = 'Cobra')
Then
DROP VIEW COBR.VIEWMOVCUSTOMER;
End If;
 
CREATE view COBR.VIEWMOVCUSTOMER (CustCod, PolCod, MonCod, RamCod, EmiTypeCod, EmiNro,
EmiDate, MovTypeCod, MovPolNro) AS
 
SELECT CUST.CustCod, POL.PolCod, POL.MonCod, POL.RamCod, MOV.EmiTypeCod, MOV.EmiNro,
MOV.EmiDate, MOV.MovTypeCod, MOV.MovPolNro
 
FROM COBR.CUSTOMER AS CUST JOIN COBR.POLICY AS POL ON  CUST.CustomerCod = POL.CustomerCod
 
JOIN COBR.MOVEMENTS AS MOV ON  
MOV.PolCod = POL.PolCod and
MOV.MonCod = POL.MonCod and 
MOV.RamCod = POL.RamCod
 
WHERE MOV.MovTypeCod = 'PAY' and
MOV.MovPolDate> = StartDateMonitor and
MOV.MovPolDate <= FinishDateMonitor;
 
END P1
 
 

Can anyone help me?

[Rabbit]

The correct header syntax for a DB2 stored procedure is

create procedure procName (IN paramName dataType)

[ALezama]

@Rabbit
Hi Rabbit.

Thank you for your observation. I believe that DB2 accepts it both ways, and it was previously as you mention it. I changed it to see if this was not the problem and the results were the same. Apparently DB2 is not recognizing the SQL parameters as valid in the SQL sentence.

Thanks again for your response.

[Rabbit]

Well, if it's not the header, then it might be the > =. There should be a space after the field name and the greater than symbol. And I don't think there should be a space between the symbol and the equal sign.

[ALezama]

Yes sir, you are right in your comment about the spaces, but I think that was introduced when I did the Copy & Paste becuase my original code I have it as they talk about it.

Let me tell you that the tests changed the parameters SQL for constant date: '2011-09-01 'and '2011-09-07' and thus does the SP is OK. The issue is that I need the SQL sentence is dynamic. In the IBM site I read about a setting "DYNAMICRULES" for dynamic SQL, however when is not clear to me as I set this up.

[Rabbit]

I have no idea what you just said. Can you please explain that second paragraph again?

[ALezama]

Sorry... Online translation was not very good. I got someone to help me:

I ran some test, changing the SQL parameters for date constants ('2011-09-01 'and '2011-09-07'). In this case, the SP worked perfectly. However, I need the SQL sentence to be dynamic, using the SP parameters. In the IBM site I read about a setting "DYNAMICRULES" for dynamic SQL but I am not clear as to how to implement it.

[Rabbit]

At this point, I'm not sure what could be wrong. I'll see if any of the other experts have more experience in DB2 stored procedures.

[ALezama]

Thanks Rabbit... I believe that the problem occurs with Dynamic SQL because now I ran changing the SQL parameters with variables SQL (defined with DECLARE) and occurs same error.

Tahnk you for your help. If you can find something, I'll be very grateful

[ALezama]

Rabbit,

In the IBM Data Studio Forum Ruiming replied with the following:

Hi,

Currently the CREATE VIEW statement does not support parameters. You may refer to the CREATE VIEW statement section in DB2 LUW 9.7 manuals for more information. Here are some excerpt from the DB2 manual:

"Defines the view. At any time, the view consists of the rows that would result if the SELECT statement were executed. The fullselect must not reference host variables, parameter markers, or declared temporary tables. However, a parameterized view can be created as an SQL table function."

Ruiming

This explains why it failed with different options that I tried.

Thanks a lot for your help!!

[Rabbit]

Glad you found your answer, good luck with your project.