CODEWITHSUNDEEP
ibm-midrangedb2-400
Pinchas K
Pinchas K

Reputation: 1571

using stored procedure in IBM I

I have tried for some time but still not able to make a stored procedure on the ibm i. if anyone can write the steps needed.

I have a cl that contains about 8 Query /400. I can have it run at night but in the event the user wants latest data it would not be.

I have looked at making a group of views but the queries do breaks and totals and this is not converting well. i need the exact same data as this bunch of queries makes.

is the idea that i create an sp and link to the cl? or do i code the contents of the cl in the sp? please if you can give me a step 1. 2. 3 would be best.

Upvotes: 1

Views: 274

Answers (2)

Buck Calabro
Buck Calabro

Reputation: 7648

I run the convert command to get the sql equivalent. RTVQMQRY. But when the query is doing breaks and totals on that, and summary level, the data is off. I prefer to get the exact data.

There is a terminology issue here that needs to be addressed. The 'exact data' - level breaks and totals - that you see with Query/400 is a formatted report; the sort of thing that you would do with program logic in an RPG program. It is very important that you understand this part: Query/400 is manipulating the raw data.

You can put business logic inside a stored procedure, but you will have to write that code yourself. Since you are using Crystal Reports, why don't you put that business logic inside the Crystal Report?

Upvotes: 1

Jairo R. Flores
Jairo R. Flores

Reputation: 734

Create a external stored procedure and call your CL program from there.

CREATE PROCEDURE mylib/sqlprocname LANGUAGE CL MODIFIES SQL DATA EXTERNAL NAME mylib/clprogname

Redbooks

IT jungle article

SQL Reference on iSeries

Upvotes: 2

Related Questions