SQL Stored Procedures

[iawnspi]

Hi All,

Let me first say that this might not be the right forum for this post and for that I apologize, but the underlying concepts here focus around SQL.

I'm an experienced SQL/TSQL programmer on the SQL Server platforms and I've recently moved to an organization where the applications I'm dealing with are on an iSeries/as400 server. Up to this point the majority of the programming has been done in RPG/LE, but I know nothing about RPG/LE and would like to note that I would appreciate if any reponses to my post would note reference RPG/LE in any manner since I have no intention of learning it aside from the fact that in the future the organization will be moving to new platforms and languages and the entire purpose of this post is to start migrating applications to SQL stored procedures.

Essentially what I'm trying to understand is:

1) How do I create a SQL stored procedure? Do I have to create a DDS and execute it?
2) What is the best method to call the SQL stored procedure in a CL? CALL PGM?

Best practices are appreciated.

I know these are pretty basic questions and I've tried looking through some of the IBM redbooks as well as other posts, but everything I'm seeing is based around the post-creation of a stored procedure (which I'm sure I'll struggle with a some point but I just want to create and execute a simple select or insert first!)

[DeadManWalks]

Re: SQL Stored Procedures

/* Get Previous Called Procedure */
CREATE PROCEDURE EKRAJCI.GETPRV (OUT ParmCaller CHAR ( 10), IN ParmCalled CHAR ( 10)) 
LANGUAGE CL NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT 
EXTERNAL NAME EKRAJCI.GETPRVCALL
PARAMETER STYLE GENERAL; 


/* comment */

COMMENT ON PROCEDURE EKrajci.GetPrv IS 'Get Previous Caller';

Only way to call a procedure is with the SQL "Call".

To create a procedure, either 1) sign on to the box, then StrSql. or 2) look for the System I Navigator icon on your pc. Click it. go to database, then schema, pick a lib (or add one), then go to procedures and add new.

I like just using the straight StrSql. The other one is a prompted method.

[B.Hauser]

Re: SQL Stored Procedures

Not sure what you really need:

Here is the SQL script for creating a stored procedure that does nothing else than returning 2 result sets based on 2 SELECT-statements.

CREATE PROCEDURE MySchema/MyProc ( ) 
	DYNAMIC RESULT SETS 2 
	LANGUAGE SQL 
	NOT DETERMINISTIC 
	READS SQL DATA 
	CALLED ON NULL INPUT 
BEGIN 
    DECLARE PRVSQLCODE SMALLINT ; 
    DECLARE CursorNotOpen CONDITION FOR '24501' ; 
	 
    DECLARE CSRC1 CURSOR FOR SELECT * FROM MyTable1 ; 
    DECLARE CSRC2 CURSOR FOR SELECT * FROM MyTable2 ; 
	 
    DECLARE CONTINUE HANDLER FOR CursorNotOpen 	 
           SET PRVSQLCODE = SQLCODE ;
	 
   CLOSE CSRC1 ; 
   CLOSE CSRC2 ; 
   OPEN CSRC1 ; 
   OPEN CSRC2 ; 
END  ;

an SQL script like this can be executed in STRSQL or System iNavigator's Run an SQL Script or it can be embedded within a source physical file member and executed with the CL command RUNSQLSTM.

Once the stored procedure is created it can be called from any SQL interface (such as embedded SQL, Run an SQL Script, ODBC, JDBC) with the SQL command CALL. (In STRSQL a stored procedure returning result sets can be execute, but you won't see the result sets).

--> Call MySchema/MyProc

System iNavigator's Run an SQL Script will return and display the result sets.

There is also a very good redbook:
Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries

You may also check the following ressources:
Database SQL Programming
SQL Reference

BTW CL does not support SQL, so calling a stored procedure from CL won't work! You need to call it from a language that supports (embedded) SQL, such as RPG/RPGLE (even you do not want to have to do anyting with this language!), Cobol, C, C++, PHP, Java etc.

Birgitta

[iawnspi]

Re: SQL Stored Procedures

Hi,

Thanks for the responses, I guess with my experience in SQL Server you generally "run" (create/compile) the stored procedure first before it can be called and I just wanted to know what the process was for that on the iSeries. I'm used to seeing "created successfully" in SQL server to know that the procedure can be called, but it doesn't seems as though there's a compile option on the iSeries.

Ian

[abercrombieande]

Re: SQL Stored Procedures

Really the only big thing I noticed between going from MS to DB2 was that the produce declare specs must be at the top. Other than that its basically the same.

If you look at post #3 the create is the compile when you run that you should not get any error. The Call statement in #3 is the execute, you cant to my knowledge just use the procedure name alone like on MS.

Also Alter does not work like you think it would. You need to drop and recreate to change the code

[iawnspi]

Re: SQL Stored Procedures

Things are starting to come together a little now for me.

As an side, I know there are a few native options for creating stored procedures on the iSeries (strsql/strpdm, iSeries Navigator), but I'm wondering if anyone has any suggestions on an editor (preferably free or relatively inexpensive) that would be similar to SQL Server Studio Mgmt where the programming highlights key words and identifies errors?

[abercrombieande]

Re: SQL Stored Procedures

toad sql, SQuirreL SQL, AQT etc..

[B.Hauser]

Re: SQL Stored Procedures

You need to drop and recreate to change the code

If you are on release 7.1 you do no longer need to drop the procedure first.
Using CREATE OR REPLACE PROCEDURE will do the job.

Birgitta

[iawnspi]

Re: SQL Stored Procedures

Hmm ... Toad doesn't appear to support SQL Server for DB2?

[abercrombieande]

Re: SQL Stored Procedures

Hmm ... Toad doesn't appear to support SQL Server for DB2?

Toad has separate install packages for each instance that you are interested in. If you want both MS SQL and DB2 you need to download both.