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

Hi Mark,

I'm aware of all that.

The primary purpose for specifying a binding directory is so we don't have
to dig to find out where an IBM, or third party, service program procedure
or function is located.

For example, do you know where the IBM C language SLEEP function actually
resides (i.e. the name of the service program)?

In RPG, we can specify binding directory QC2LE, and create a prototype to
use the C language SLEEP function. At compile time, the RPG compiler will
figure out for us where that function resides, and bind in the compiler
located service program containing SLEEP.

I'd like the compilation of SQL external functions and procedures to do the
same thing. Granted, compiling an external SQL function or procedure
simply creates a row in SYSPROCS or SYSFUNCS, but that row created contains
the external name specified (column EXTERNAL_NAME). I want the compile
time lookup of that external name to be able to be done from a binding
directory, and not have to take the time to figure out where the C language
SLEEP function resides.

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 and
a routine name, and let the SQL compiler figure out where the routine
resides, so it can store the compiler located external name location in
SYSPROCS.EXTERNAL_NAME or SYSFUNCS.EXTERNAL_NAME.

I'd like the SQL compiler to locate the routine for me, just like using a
binding directory in RPG, or a namespace in C#, or a package in Java.

Mike

date: Wed, 22 Nov 2017 23:54:16 -0500
from: Mark Murphy <jmarkmurphy@xxxxxxxxx>
subject: Re: SQL external UDF or proc: Ability to specify binding
directory to locate external functions and procedures

I'm not real sure what you are asking for here. When you define an external
UDF or procedure, this points at an already compiled object, either a
program or service program object. The specific object is named in EXTERNAL
NAME. No binding directory necessary here, or even useable, the definition
of the external UDF or procedure tells where to find the program or
procedure. You can use a binding directory when creating the program or
service program if necessary, but the SQL definition is just a pointer, it
doesn't cause anything to be compiled or created.

Second point, the binding directory is only used when a program or service
program object is created. Since defining an external SQL UDF or procedure
does not create a program object, not real sure how you think a binding
directory might be helpful here either.

On Wed, Nov 22, 2017 at 4: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