Re: New to DB2/400 Procedures

There's not necessarily a requirement for explicit cursors - might depend on 
where you mean they should go. Seems to me the example you give should be 
pretty easy to pull over. 

Also, I suggest care when using the term DB2 - that is a family of IBM RDBMS's 
that are related and not identical - the mainframe has its own code base, the 
Windows/Unix/Linux version another, with tons of knobs to turn for optimization 
that are nowhere to be found on the iSeries, and finally the iSeries version. 
Underlying architecture has something to do with the differences - esp. 
single-level store on the iSeries, which, among other things, means you don't 
spend a lot of time trying to understand details like you are used to in other 
systems - the iSeries does so much tuning stuff for you already. So going to 
the general IBM DB2 site will not be very helpful in working with the iSeries 
database - for now, at least - you will be led down various garden paths of 
things that do not apply very well if at all.

So one suggestion I have is, work on WHAT you want to do and less on HOW the 
underlying engine does it. 

The other suggestion is to go to www.iseries.ibm.com/db2 and rummage around - 
there are Redbooks (supplemental manuals, sort of) and all available there, and 
there is one especially that deals with stored procedures, user-defined 
functions, etc. There are also lots of examples. You might also go to 
www.iseries.ibm.com/infocenter and pick the V5R1 version and go to the Database 
section in the LH menu - that version of InfoCenter has a task-centric way of 
getting at SQL information - an "if you want to do this, then..." approach that 
I don't remember seeing in later incarnations.

One important manual has to do with optimization - the name has changed between 
releases of OS/400 - it is the thing where you DO want to understand something 
of how things are done. It lists the various access methods, when they are 
likely to be used, and how you might influence the optimizer in its decisions.

HTH
Vern


-------------- Original message -------------- 
From: "Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx> 

> OK, I have lots of reading to do...I know that. I'm completely unfamilar 
> with the way DB2 result sets and flow-control language are used in SP's (as 
> I understand it, DB2 requires the use of cursors - probably based on the 
> ANSI standard). 
> 
> Can someone help me get jump started here... Below is a Proc using MS SQL's 
> T-SQL (which is what I'm used to.) Can someone do a quick translation? 
> 
> 
> CREATE PROCEDURE spGetContact (@DOCOID int, @ConID smallint) 
> AS 
> 
> DECLARE @NDOCOID Integer 
> DECLARE @CONTACTID SmallInt 
> 
> SET @NDOCOID=@DOCOID 
> SET @ContactID=@ConID 
> 
> IF EXISTS( 
> SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE WHERE NDOCO=@NDOCOID AND 
> NHIWCONTACTTYPEID=@CONTACTID 
> ) 
> BEGIN 
> 
> SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1 
> INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid 
> INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid = 
> t3.nhiwcontacttypeid 
> WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = @CONTACTID 
> END 
> 
> ELSE 
> BEGIN 
> SELECT * FROM PRODDTA.CO_PEOPLEMMHIWCONTACTTYPE t1 
> INNER JOIN PRODDTA.CO_PEOPLE t2 on t1.npeopleid = t2.npeopleid 
> INNER JOIN PRODDTA.CO_HIWCONTACTTYPE t3 on t1.nhiwcontacttypeid = 
> t3.nhiwcontacttypeid 
> WHERE NDOCO = @NDOCOID AND t1.NHIWCONTACTTYPEID = 7 
> 
> END 
> 
> GO 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>