Re: sql function - routine not found when ACTGRP(*NEW)

[Steve Richter <stephenrichter@xxxxxxxxx>]

I dropped the function and the underlying program, then created the sql
function, etc.   When I look at sysibm.routines the parm_style is SQL and
the External_name is the program I created.

good to know that SQL wants to execute  a service program procedure.   I
can live with the behavior.

thanks,


On Tue, Mar 12, 2019 at 12:48 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> A UDF is converted under the cover into a service program and for service
> programs activation group *NEW is not allowed.
> May be the service program / UDF is not created at all if the underlying
> program must run in a *NEW activation group.
> Did you check whether the UDF (with the underlying program in activation
> group *NEW) exists?
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so
> they don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Kevin Adler
> Sent: Dienstag, 12. März 2019 17:28
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: Re: sql function - routine not found when ACTGRP(*NEW)
>
>    I'm not sure why it would matter for finding it, but I would note that I
>    would usually use ACTGRP(*CALLER) over ACTGRP(*NEW) to improve
> performance
>    by not having to create a new activation group all the time.
>
>
>      ----- Original message -----
>      From: Steve Richter <stephenrichter@xxxxxxxxx>
>      Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
>      To: Midrange Systems Technical Discussion
>      <midrange-l@xxxxxxxxxxxxxxxxxx>
>      Cc:
>      Subject: sql function - routine not found when ACTGRP(*NEW)
>      Date: Tue, Mar 12, 2019 9:36 AM
>
>      does the activation group of a program matter when creating an SQL
>      function?
>
>      I am getting a "routine not found with specified parameters" error
> when
>      using an SQL function, where the program was created with
> ACTGRP(*NEW).
>      When I CRTPGM  pgmname  ACTGRP(QILE), the sql function works.
>
>      CRTPGM PGM(utl0033MC) ACTGRP(*new)
>
>      select   utl_currentUser( ) user
>      from     sysibm/sysdummy1
>      Routine UTL0033MC in QGPL not found with specified parameters.
>
>      do this and the sql function works:
>      CRTPGM PGM(utl0033MC) ACTGRP(QILE)
>
>      CREATE OR REPLACE FUNCTION utl_currentUser  ( )
>      RETURNS     char(10)
>      language cl
>      external name  qgpl/utl0033mc
>      parameter style SQL
>
>       PGM        (&curUser &rtnNull &SqlState +
>                   &function &specific &errmsg)
>
>       dcl        &curUser *char 10
>       dcl        &rtnNull *int 2
>       dcl        &sqlstate *char 5
>       dcl        &function *char 517
>       dcl        &specific *char 130
>       dcl        &errmsg *char 72
>
>       RTVJOBA    CURUSER(&CURUSER)
>
>       endpgm
>      --
>      This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
>      list
>      To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
>      To subscribe, unsubscribe, or change list options,
>      visit: [1]https://lists.midrange.com/mailman/listinfo/midrange-l
>      or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
>      Before posting, please take a moment to review the archives
>      at [2]https://archive.midrange.com/midrange-l.
>
>      Please contact support@xxxxxxxxxxxx for any subscription related
>      questions.
>
>      Help support midrange.com by shopping at amazon.com with our
> affiliate
>      link: [3]https://amazon.midrange.com
>
>
>
>
> References
>
>    Visible links
>    1. https://lists.midrange.com/mailman/listinfo/midrange-l
>    2. https://archive.midrange.com/midrange-l
>    3. https://amazon.midrange.com/
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com