Re: How are you modernising your as400 applications?

Sarah,

If in doubt, RTFM. The SQL Reference does state this on the CREATE
PROCEDURE, but its not in big letters or flashing lights!

'EXTERNAL NAME external-program-name
Specifies the program or service program that will be executed when the
procedure is called by the CALL statement. The program name must identify a
program or service program that exists at the application server at the time
the procedure is called. If the naming option is *SYS and the name is not
qualified:

   - The current path will be used to search for the program or service
   program at the time the procedure is called.
   - *LIBL will be used to search for the program or service program at
   the time grants or revokes are performed on the procedure'

cheers
Colin.W

On 27/02/06, Sarah Poger Gladstone <listmember@xxxxxxxxxxxxxx> wrote:
>
> Aaron- We originally handled the stored procs as you did, using
> MYLIB123/MYRPG123 to refer to the actual RPG program. IBM had told us
> as well that was the only way.  Fortunatly, my coworker realized that
> the lib name was not needed, and when left off, *LIBL would be used
> instead.
>
> We also kept the SQL statement to create and delete the definition
> outside the RPG program. It was in a seperate util program where
> "PROCLIB" was a parm.
>
> Where did you keep the SQL statement to create the procedure
> definition? In the same source member as the program that implements
> the definition?
>
> -Sarah
>
> On 2/27/06, albartell <albartell@xxxxxxxxx> wrote:
> > > I would disagree. There is only one QSYS2/SYSROUTINES table on each
> > machine, but you could have multiple definitions for each stored proc.
> >
> > I should have qualified the latter part of my sentence because I was
> > referencing the system table that holds the definitions (as you have
> further
> > defined). Thanks for clarifying that.
> >
> > >Moving from dev to QA to prod did not require a change to the
> definition.
> > We just moved the object.
> > I am trying to remember the exact details, but I seem to remember that
> we
> > had need to recompile at each level (i.e. development, test, qa,
> production)
> > which requires the source of the stored proc, and if I remember
> correctly
> > the RPG program to call from the SQL stored proc has to be library
> qualified
> > (i.e. MYLIB123/MYRPG123).  So what we did was retain the source code of
> the
> > stored proc and replaced the library each time it moved to a different
> > holding place (i.e. from QA to production).
> >
> >
> > <reading your article...>
> >
> > We spoke with some IBM'ers and that was not mentioned as an alternative
> to
> > the problem we were having. Our problem is that the place where you are
> > specifying PROCLIB we had  a replacable variable for our CMS to crawl
> over
> > and capture and change (i.e. %LIBRARY% or something like that)
> >
> >
> > Aaron Bartell
> >
> > -----Original Message-----
> > From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
> On
> > Behalf Of Sarah Poger Gladstone
> > Sent: Monday, February 27, 2006 9:27 AM
> > To: Web Enabling the AS400 / iSeries
> > Subject: Re: [WEB400] How are you modernising your as400 applications?
> >
> > Aaron-
> >
> > My previous company also used the stored procedure route.  But you said:
> >
> > > A stored procedures definition is stored in system tables, and there
> > > are only one set of them on a machine, which made moving from dev, to
> > > test, to QA, to production problematic because the stored procedure
> > > had to be deleted and recreated each time (you couldn't just move the
> > object).
> >
> >  I would disagree. There is only one QSYS2/SYSROUTINES table on each
> > machine, but you could have multiple definitions for each stored proc.
> > Either with a differenet alias library name or a different parameter
> > list.   AT my old company, the only time the procedure definition was
> > updated/created was when we deployed on a new machine, or the
> > parameters changed.   Moving from dev to QA to prod did not require a
> > change to the definition. We just moved the object.  If you are
> interested
> > in the details, check the article
> > http://www.ignite400.org/news/news2003031001.htm
> >
> > Regardless, managing 1500+ procedures sounds cumbersome!
> >
> > Also no matter what approach is chosen ( stored procs, web services,
> > PCML) you need to determine a mechanism for the UI to communicate with
> the
> > backend.  You could write a generic "request handler" so that for any
> new
> > functionality in the UI, you are just creating a new request.
> > The new request would be sent to the existing request handler, which
> would
> > forward the request to the appropriate program/service program or Java
> > method.
> >
> > -Sarah
> >
> >
> > On 2/27/06, albartell <albartell@xxxxxxxxx> wrote:
> > > >wrap it up in APIs that you can call as a stored procedure via the
> > > >database
> > >
> > > The previous company I worked for went this route because it creates
> > > an easy way to connect to your RPG program from any language that can
> > > all an SQL stored procedure.  The problem with this approach is all in
> > > the change management. We had an environment with a separate dev
> > > machine running change management software (name purposely left out)
> > > that didn't do a good job of managing the stored procedures (IMO).
> > >
> > > A stored procedures definition is stored in system tables, and there
> > > are only one set of them on a machine, which made moving from dev, to
> > > test, to QA, to production problematic because the stored procedure
> > > had to be deleted and recreated each time (you couldn't just move the
> > > object). In the end I believe we ended up writing our own exit point
> > > extensions within the change management software to handle everything.
> > >
> > > I think stored procedures are fine for a handful, but when I left it
> > > was reaching levels of 1500+ stored procedures and that was quite the
> > > task to manage.
> > >
> > > If you have Java knowledge in your shop I still think a Java front-end
> > > calling RPG business logic as needed creates a easy UI design front.
> > > The only problem with an approach like this is that unless you have a
> > > Java person that can do RPG or vice versa it gets difficult to debug
> > > applications in a timely manner because you have to involve other
> > > people as soon as it leaves your language.
> > >
> > > Having your business logic in a separate language than your front end
> > > definitely comes at a cost, but so does putting your business logic in
> > > Java or PHP if that isn't part of your long term goal and it gets
> > > dumped after a few years of use.  This is definitely something to
> > > think long and hard about before introducing a new language/approach
> into
> > your shop.
> > >
> > > Anyways, those are my thoughts on the matter :-) Aaron Bartell
> > >
> > > -----Original Message-----
> > > From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
> > > On Behalf Of Colin Williams
> > > Sent: Monday, February 27, 2006 2:27 AM
> > > To: Web Enabling the AS400 / iSeries
> > > Subject: [WEB400] How are you modernising your as400 applications?
> > >
> > > Following from the long discussion re PHP/SQL/App Modernisation,
> > >
> > > I would be interested to find out how most of us are going about the
> > > Iseries Application Modernisation process.
> > >
> > > I have always been a fan of the route where you keep your existing RPG
> > > business logic, wrap it up in APIs that you can call as a stored
> > > procedure via the database, and create a nice browser from end, using
> > > Java or whatever else you prefer, but also use some direct access from
> > > front end to DB via SQL. That way you dont have to use the big-bang
> > > approach, but can modernise as and when.
> > >
> > > Just interested to find out what others have done or prefer, have no
> > > personal axe to grind
> > > --
> > > This is the Web Enabling the AS400 / iSeries (WEB400) mailing list To
> > > post a message email: WEB400@xxxxxxxxxxxx To subscribe, unsubscribe,
> > > or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/web400
> > > or email: WEB400-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives at
> > > http://archive.midrange.com/web400.
> > >
> > > --
> > > This is the Web Enabling the AS400 / iSeries (WEB400) mailing list To
> > > post a message email: WEB400@xxxxxxxxxxxx To subscribe, unsubscribe,
> > > or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/web400
> > > or email: WEB400-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives at
> > > http://archive.midrange.com/web400.
> > >
> > >
> >
> > --
> > This is the Web Enabling the AS400 / iSeries (WEB400) mailing list To
> post a
> > message email: WEB400@xxxxxxxxxxxx To subscribe, unsubscribe, or change
> list
> > options,
> > visit: http://lists.midrange.com/mailman/listinfo/web400
> > or email: WEB400-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives at
> > http://archive.midrange.com/web400.
> >
> > --
> > This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
> > To post a message email: WEB400@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/web400
> > or email: WEB400-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/web400.
> >
> >
>
> --
> This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
> To post a message email: WEB400@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/web400
> or email: WEB400-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/web400.
>
>