Re: SQL external UDF or proc: Ability to specify binding directory to locate external functions and procedures

Hi Alan,

When creating a external SQL function or procedure, it appears to me the
only options you can specify are:
1) PROGRAM TYPE MAIN EXTERNAL NAME A_PROGRAM, or
2) PROGRAM TYPE SUB EXTERNAL NAME A_SERVICE_PROGRAM( "A_PROCEDURE")

On one of our V7R1 systems, the QC2LE binding directory contains 34 service
programs. If I want to, for example, create an SQL wrapper around the
SLEEP function, I don't want to have to dig through 34 service programs to
find the one that exports SLEEP. I want to specify a binding directory
(e.g. QC2LE) and a routine name (e.g. SLEEP), and let the SQL compiler
figure out where the routine resides, so it can store the compiler located
external service program name in SYSPROCS.EXTERNAL_NAME or
SYSFUNCS.EXTERNAL_NAME.

I see no syntax that supports creating a PROGRAM TYPE SUB wrapper around an
external service program routine, without locating the name of the service
program ourselves. We have to locate and specify the service program name.

This ability would be the same as specifying a binding directory in RPG, or
similar to using a namespace in C#, or a package in Java.

Mike

date: Wed, 22 Nov 2017 13:05:45 -0800

from: Alan Campin <alan0307d@xxxxxxxxx>
subject: Re: SQL external UDF or proc: Ability to specify binding
directory to locate external functions and procedures

One thing. You do not need to specify QC2LE or any other IBM binding
directory. It just checks and automatically binds in IBM functions
. This has been true for many, many years.

On Wed, Nov 22, 2017 at 1:01 PM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

Hi,

I don't believe we yet have a way to specify a binding directory for
locating external functions and procedures when creating SQL external
UDF's and procedures. If I'm wrong, please educate me.

For example, in RPG, we can use an H-spec or CTL-OPT spec with: BNDDIR(
'QC2LE' ), and then specify a prototype for the C language SLEEP

function.

When creating an SQL external UDF or procedure, I'd like to be able to
specify:

PROGRAM TYPE SUB EXTERNAL NAME BNDDIR( 'QC2LE' ) EXTPROC( 'SLEEP' )

or

PROGRAM TYPE MAIN EXTERNAL NAME BNDDIR( 'MY_BNDDIR' ) EXTPGM( 'MY_PGM' )

...or something similar, like we can in RPG.

Unless I'm missing something, today, some of our alternatives include:
* Figure out where SLEEP actually resides and point the EXTERNAL NAME at
its location.
* Create a, for example, RPG wrapper specifying BNDDIR( 'QC2LE' ) plus a
prototype with EXTPROC( 'SLEEP' ), and then point the SQL external UDF or
procedure EXTERNAL NAME to the RPG wrapper program or procedure.

The ability to specify a binding directory and routine would make it
faster, and easier to create SQL external UDFs and procedure wrappers.

I'm wondering if there's significant interest in creating a RFE

requesting

said ability from IBM.

Mike