Re: New to DB2/400 Procedures

Thanks Vern.  I am indeed running on an AS400.  If I refer to DB2/400, will
that suffice - or is this nomenclature officially retired?

Also, I am focused on what I want to do.  My comment about the cursors was
simply an illustration of why the DB2/400 SP syntax is generally unfamiliar
to me.  Thanks for the links.  I will check them out.

RH

<vhamberg@xxxxxxxxxxx> wrote in message
news:050320061900.5982.4458FDBF000544060000175E2200734076099D0A0D030E0890@xxxxxxxxxxxxxx
> 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.
> >
> -- 
> 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.
>
>