Re: How are you modernising your as400 applications? -- WEB400

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.
>
>