Stored procedure calling sub-programs : Sqlcode -927

teldb2kals

Stored procedure calling sub-programs : Sqlcode -927
Hi,

When a SProc calls other programs (with SQL in them), do these
subprograms also need to be linked with DSNALI ? (we r using DB2 Stored
Procedure Addressspace).

We have a SP calling subprograms, but get an error -927 for the
subprogram. Cd somebody lead us on the right way ?

Thanx.

Cheers,
Kals

----------------
Powered by telstra.com



Martin Nicholson

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to teldb2kals)
Assuming that your sub-program call is static and you include load code
rather than object code:

Could there be a problem in the way you are link editing the stored
procedure.
All the CLIs have the same entry point name. I believe the first one found
is the first one kept. Check the order of your includes. Include DSNALI
before you include your subprogram. If the DSNELI is found first, it will
resolve the DSNHLI calls.

> -----Original Message-----
> From: teldb2kals [SMTP:[login to unmask email]
> Sent: Wednesday, December 19, 2001 8:08 PM
> To: [login to unmask email]
> Subject: Stored procedure calling sub-programs : Sqlcode -927
>
> Hi,
>
> When a SProc calls other programs (with SQL in them), do these
> subprograms also need to be linked with DSNALI ? (we r using DB2 Stored
> Procedure Addressspace).
>
> We have a SP calling subprograms, but get an error -927 for the
> subprogram. Cd somebody lead us on the right way ?
>
> Thanx.
>
> Cheers,
> Kals
>
> ----------------
> Powered by telstra.com
>
>
>
>
>



Tim Lowe

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to Martin Nicholson)
Kals,
You do not say if you are calling your subprograms dynamically or
statically linkediting them together with your stored procedure. (either
using the DYNAM compiler option, or variables)
A standard DB2 installation has problems dynamically calling subroutines
from DB2 Stored Procedures, and gets the type of error that you describe,
so I will assume that this is your problem.
There are several restrictions on this that are documented.
You have several options:
Use static calls so that your subroutine's calls to DSNHLI and DSNHLIR
are resolved to the correct attach that was linkedited with your DB2
Stored Procedure. This is what IBM recommends, and is certainly the
safest option. But, it can be difficult to maintain if you have
changes to DB2 subroutines that are linkedited into many different
locations.
Compile your DB2 subroutine with the ATTACH(CAF) compiler option so that
the calls to DSNHLI routines become calls to DSNALI routines. (and
once you convert to RRS, use the ATTACH(RRSAF) compiler option to
convert to using DSNRLI routines. You will also need to relinkedit
existing subroutines at that time.) This of course means that you do
not want to dynamically call this version from programs using a
different attach, you need to maintain different versions of the load
module, and this can create interesting problems with DB2 package
cleanup routines.
Create your own "stub" DSNHLI routine that calls the correct attach, and
put it in a new loadlib that is in the steplib for your stored procedure
address space.
Linkedit the DSNALI routine with a DSNHLI alias into a new loadlib that
is in the steplib for your stored procedure address space. (and when
you convert to RRS, do the same thing with DSNRLI) This is what we do,
and what I would recommend. However, it does mean that you need to
maintain increased work for your DB2 system programmers who will have to
maintain this copy of the DB2 attach routines.

This is because IBM has never created separate load libraries for the CAF
or RRS attach modules. The DSNALI alias resolves to the TSO attach in the
DB2 loadlib. And, IMS does not have this problem because the DSNALI alias
is resolved to the IMS attach in the IMS loadlib.

This subject has been discussed several times on this list. You might
want to check the archives.

If I have gotten anything wrong, will someone please correct me?

Thanks,
Tim




teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Stored procedure calling sub-programs
Data Base : Sqlcode -927
Discussion
List
<[login to unmask email]
M>


12/19/2001
08:07 PM
Please respond
to DB2 Data
Base
Discussion
List






Hi,

When a SProc calls other programs (with SQL in them), do these
subprograms also need to be linked with DSNALI ? (we r using DB2 Stored
Procedure Addressspace).

We have a SP calling subprograms, but get an error -927 for the
subprogram. Cd somebody lead us on the right way ?

Thanx.

Cheers,
Kals

----------------
Powered by telstra.com








Roger Miller

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to Tim Lowe)
There are some differences for various meanings of call and by language, but
load modules generally need to be linked and must use the same language
interface, so there is no mixing of ( DSNALI, DSNELI, DFSLI000, DSNCLI,
DSNRLI ). If this is a WLM-managed stored procedure, then DSNRLI is what
you should be using.

Roger Miller, DB2 for z/OS



Larry Kirkpatrick

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to Roger Miller)
The IDUG Solutions Journal issue that came out in the Winter of 1996 had an
article that was called "Sharing DB2 Application Code Bridging CICS, IMS
and TSO". This article actually had assembler code that established a
"universal" language interface module that included all of the language
interface modules at the time. I am not saying that this is the "be all
end all" but we have been using a variation of this for some of our common
code and it has worked well.

Larry Kirkpatrick
Mutual of Omaha






"Roger Miller"
<[login to unmask email] To: [login to unmask email]
BM.COM> cc:
Sent by: "DB2 Subject: Re: Stored procedure calling sub-programs : Sqlcode -927
Data Base
Discussion
List"
<[login to unmask email]
M>


12/20/2001
10:40 AM
Please respond
to "DB2 Data
Base
Discussion
List"






There are some differences for various meanings of call and by language,
but
load modules generally need to be linked and must use the same language
interface, so there is no mixing of ( DSNALI, DSNELI, DFSLI000, DSNCLI,
DSNRLI ). If this is a WLM-managed stored procedure, then DSNRLI is what
you should be using.

Roger Miller, DB2 for z/OS








teldb2kals

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to Larry Kirkpatrick)
Tom/Martin/Roger/Larry,

Thanx for ur replies. I did some more playing around before I got ur
replies, and got it working. But I am still trying to figure out if I
have fully understood all the possibilities. I am not too keen to setup
complex (eg. assembler stubs) solutions for my developers.
Unfortunately, the manual mentions only abt the setting of CURRENT
PACKAGESETs and nothing abt the compile/link-edits for calling
subprograms from SPs.

(I guess I have just got used to IBM manuals describing everything in
little detail that when I don't find something, I get lots of doubts)

My scenario is this : I have existing programs performing some
functions. Now these programs need to be called from the SP as well.
These subprograms have originally been compiled with 'DYNAM' option.
They may or may not call other programs. They do not have the DSNxLI
module lined with them.

From what I can see, the options for me r :

(1) Call the subprograms in static mode (I suppose CALL 'literal' is
the way to code), compile the SP with NODYNAM option, link the SP
INCLUDEing the subprograms along with DSNALI. As Tim said, the SP will
then need relinking when the subprogram changes and the subsequent
maintenance.

(2) Call them dynamically (CALL identifier), compile the SP with DYNAM
option and link it with DSNALI. In this case, I wd need to link my
subprograms with DSNALI and then store them in the SPAS STEPLIB
dataset. I wd then be having two different copies of my subprogram.

Can somebody confirm if the above two options wd work ?

Can I have a combination of both static and dynamic calls ?

I am still playing around.......

Thanx.

Cheers,
Kals
-----Original Message-----
From: Nicholson, Martin [SMTP:[login to unmask email]
Sent: Friday, December 21, 2001 2:24 AM
To: [login to unmask email]
Subject: Re: Stored procedure calling sub-programs : Sqlcode -927

Assuming that your sub-program call is static and you include load code
rather than object code:

Could there be a problem in the way you are link editing the stored
procedure.
All the CLIs have the same entry point name. I believe the first one
found
is the first one kept. Check the order of your includes. Include
DSNALI
before you include your subprogram. If the DSNELI is found first, it
will
resolve the DSNHLI calls.

> -----Original Message-----
> From: teldb2kals [SMTP:[login to unmask email]
> Sent: Wednesday, December 19, 2001 8:08 PM
> To: [login to unmask email]
> Subject: Stored procedure calling sub-programs : Sqlcode -927
>
> Hi,
>
> When a SProc calls other programs (with SQL in them), do these
> subprograms also need to be linked with DSNALI ? (we r using DB2
Stored
> Procedure Addressspace).
>
> We have a SP calling subprograms, but get an error -927 for the
> subprogram. Cd somebody lead us on the right way ?
>
> Thanx.
>
> Cheers,
> Kals

----------------
Powered by telstra.com



Tim Lowe

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to teldb2kals)
Kals,
You did not address your message to me, but I should mention that your
second option will not work unless you also use the DB2 precompiler option
ATTACH(CAF). If you compile your program with DYNAM, then it does not
matter what you linkedit with it, it will dynamically call it, and ignore
the attach routine that you included in the linkedit. And, unless you
have also specified ATTACH(CAF), your dynamic call will be to module
DSNHLI, which is an alias to DSNELI, the DB2 TSO attach, and you will get
-927. By using ATTACH(CAF), the code generated by the precompiler will be
a call to DSNALI instead of DSNHLI.

Without any precompiler options, the DB2 calls become calls to the module
DSNHLI. With ATTACH(CAF), they are calls to DSNALI.

Below are the DB2 attach environments, their names, and my notes on options
how to make dynam work:
IMS DFSLI000 DYNAM OK since DSNHLI alias resolves to DFSLI000
in IMS RESLIB
CICS DSNCLI Need a loadlib w/DSNHLI alias
CAF DSNALI Needs a loadlib w/DSNHLI alias or PRECOMPILE W/
ATTACH(CAF)
RRS DSNRLI Needs a loadlib w/DSNHLI alias or PRECOMPILE W/
ATTACH(RRSAF)
TSO DSNELI DYNAM OK since DSNHLI alias resolves to DSNELI in DB2
SDSNLOAD

I hope this helps.
As alway, If there are any mistakes, I hope that someone will correct them.

Thanks,
Tim



teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Re: Stored procedure calling
Data Base sub-programs : Sqlcode -927
Discussion
List
<[login to unmask email]
M>


12/20/2001
09:25 PM
Please respond
to DB2 Data
Base
Discussion
List






Tom/Martin/Roger/Larry,

Thanx for ur replies. I did some more playing around before I got ur
replies, and got it working. But I am still trying to figure out if I
have fully understood all the possibilities. I am not too keen to setup
complex (eg. assembler stubs) solutions for my developers.
Unfortunately, the manual mentions only abt the setting of CURRENT
PACKAGESETs and nothing abt the compile/link-edits for calling
subprograms from SPs.

(I guess I have just got used to IBM manuals describing everything in
little detail that when I don't find something, I get lots of doubts)

My scenario is this : I have existing programs performing some
functions. Now these programs need to be called from the SP as well.
These subprograms have originally been compiled with 'DYNAM' option.
They may or may not call other programs. They do not have the DSNxLI
module lined with them.

From what I can see, the options for me r :

(1) Call the subprograms in static mode (I suppose CALL 'literal' is
the way to code), compile the SP with NODYNAM option, link the SP
INCLUDEing the subprograms along with DSNALI. As Tim said, the SP will
then need relinking when the subprogram changes and the subsequent
maintenance.

(2) Call them dynamically (CALL identifier), compile the SP with DYNAM
option and link it with DSNALI. In this case, I wd need to link my
subprograms with DSNALI and then store them in the SPAS STEPLIB
dataset. I wd then be having two different copies of my subprogram.

Can somebody confirm if the above two options wd work ?

Can I have a combination of both static and dynamic calls ?

I am still playing around.......

Thanx.

Cheers,
Kals
-----Original Message-----
From: Nicholson, Martin [SMTP:[login to unmask email]
Sent: Friday, December 21, 2001 2:24 AM
To: [login to unmask email]
Subject: Re: Stored procedure calling sub-programs : Sqlcode -927

Assuming that your sub-program call is static and you include load code
rather than object code:

Could there be a problem in the way you are link editing the stored
procedure.
All the CLIs have the same entry point name. I believe the first one
found
is the first one kept. Check the order of your includes. Include
DSNALI
before you include your subprogram. If the DSNELI is found first, it
will
resolve the DSNHLI calls.

> -----Original Message-----
> From: teldb2kals [SMTP:[login to unmask email]
> Sent: Wednesday, December 19, 2001 8:08 PM
> To: [login to unmask email]
> Subject: Stored procedure calling sub-programs : Sqlcode -927
>
> Hi,
>
> When a SProc calls other programs (with SQL in them), do these
> subprograms also need to be linked with DSNALI ? (we r using DB2
Stored
> Procedure Addressspace).
>
> We have a SP calling subprograms, but get an error -927 for the
> subprogram. Cd somebody lead us on the right way ?
>
> Thanx.
>
> Cheers,
> Kals

----------------
Powered by telstra.com








James Campbell

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to Tim Lowe)
As Tim implies (but somehow doesn't actually say) some people
handle this by copying DSNALI (DSNRLI for WLM SPASs) into a
library at the front of STEPLIB, and giving it aliases of DSNHLI and
DSNWLI (don't forget that the module is also used for IFI calls).
That way, in the SPAS, dynamic calls to DSNHLI are resolved
using entry points in DSNALI and using DSNELI in other address
spaces.

For CICS, my understanding is that the question is moot. DSNCLI
_must_ be statically linked into the load module.

To be pedantic, ATTACH(CAF) will generate a call to DSNHLI2 -
which is an alternative entry point in DSNALI. The DSNALI entry
point itself, is used to manage the connection to DB2.

James Campbell


On 21 Dec 2001, at 10:11, [login to unmask email] wrote:

Date sent: Fri, 21 Dec 2001 10:11:01 -0600
Send reply to: DB2 Data Base Discussion List <[login to unmask email]>
From: [login to unmask email]
Subject: Re: [DB2-L] Stored procedure calling sub-programs : Sqlcode -927
To: [login to unmask email]

> Kals,
> You did not address your message to me, but I should mention that your
> second option will not work unless you also use the DB2 precompiler option
> ATTACH(CAF). If you compile your program with DYNAM, then it does not
> matter what you linkedit with it, it will dynamically call it, and ignore
> the attach routine that you included in the linkedit. And, unless you
> have also specified ATTACH(CAF), your dynamic call will be to module
> DSNHLI, which is an alias to DSNELI, the DB2 TSO attach, and you will get
> -927. By using ATTACH(CAF), the code generated by the precompiler will be
> a call to DSNALI instead of DSNHLI.
>
> Without any precompiler options, the DB2 calls become calls to the module
> DSNHLI. With ATTACH(CAF), they are calls to DSNALI.
>
> Below are the DB2 attach environments, their names, and my notes on options
> how to make dynam work:
> IMS DFSLI000 DYNAM OK since DSNHLI alias resolves to DFSLI000
> in IMS RESLIB
> CICS DSNCLI Need a loadlib w/DSNHLI alias
> CAF DSNALI Needs a loadlib w/DSNHLI alias or PRECOMPILE W/
> ATTACH(CAF)
> RRS DSNRLI Needs a loadlib w/DSNHLI alias or PRECOMPILE W/
> ATTACH(RRSAF)
> TSO DSNELI DYNAM OK since DSNHLI alias resolves to DSNELI in DB2
> SDSNLOAD
>
> I hope this helps.
> As alway, If there are any mistakes, I hope that someone will correct them.
>
> Thanks,
> Tim
>
>
>
> teldb2kals
> <[login to unmask email] To: [login to unmask email]
> LSTRA.COM> cc:
> Sent by: DB2 Subject: Re: Stored procedure calling
> Data Base sub-programs : Sqlcode -927
> Discussion
> List
> <[login to unmask email]
> M>
>
>
> 12/20/2001
> 09:25 PM
> Please respond
> to DB2 Data
> Base
> Discussion
> List
>
>
>
>
>
>
> Tom/Martin/Roger/Larry,
>
> Thanx for ur replies. I did some more playing around before I got ur
> replies, and got it working. But I am still trying to figure out if I
> have fully understood all the possibilities. I am not too keen to setup
> complex (eg. assembler stubs) solutions for my developers.
> Unfortunately, the manual mentions only abt the setting of CURRENT
> PACKAGESETs and nothing abt the compile/link-edits for calling
> subprograms from SPs.
>
> (I guess I have just got used to IBM manuals describing everything in
> little detail that when I don't find something, I get lots of doubts)
>
> My scenario is this : I have existing programs performing some
> functions. Now these programs need to be called from the SP as well.
> These subprograms have originally been compiled with 'DYNAM' option.
> They may or may not call other programs. They do not have the DSNxLI
> module lined with them.
>
> From what I can see, the options for me r :
>
> (1) Call the subprograms in static mode (I suppose CALL 'literal' is
> the way to code), compile the SP with NODYNAM option, link the SP
> INCLUDEing the subprograms along with DSNALI. As Tim said, the SP will
> then need relinking when the subprogram changes and the subsequent
> maintenance.
>
> (2) Call them dynamically (CALL identifier), compile the SP with DYNAM
> option and link it with DSNALI. In this case, I wd need to link my
> subprograms with DSNALI and then store them in the SPAS STEPLIB
> dataset. I wd then be having two different copies of my subprogram.
>
> Can somebody confirm if the above two options wd work ?
>
> Can I have a combination of both static and dynamic calls ?
>
> I am still playing around.......
>
> Thanx.
>
> Cheers,
> Kals
> -----Original Message-----
> From: Nicholson, Martin [SMTP:[login to unmask email]
> Sent: Friday, December 21, 2001 2:24 AM
> To: [login to unmask email]
> Subject: Re: Stored procedure calling sub-programs : Sqlcode -927
>
> Assuming that your sub-program call is static and you include load code
> rather than object code:
>
> Could there be a problem in the way you are link editing the stored
> procedure.
> All the CLIs have the same entry point name. I believe the first one
> found
> is the first one kept. Check the order of your includes. Include
> DSNALI
> before you include your subprogram. If the DSNELI is found first, it
> will
> resolve the DSNHLI calls.
>
> > -----Original Message-----
> > From: teldb2kals [SMTP:[login to unmask email]
> > Sent: Wednesday, December 19, 2001 8:08 PM
> > To: [login to unmask email]
> > Subject: Stored procedure calling sub-programs : Sqlcode -927
> >
> > Hi,
> >
> > When a SProc calls other programs (with SQL in them), do these
> > subprograms also need to be linked with DSNALI ? (we r using DB2
> Stored
> > Procedure Addressspace).
> >
> > We have a SP calling subprograms, but get an error -927 for the
> > subprogram. Cd somebody lead us on the right way ?
> >
> > Thanx.
> >
> > Cheers,
> > Kals
>
> ----------------
> Powered by telstra.com

James A Campbell



teldb2kals

Re: Stored procedure calling sub-programs : Sqlcode -927
(in response to James Campbell)
Tim,

My humble apologies. That was a typo. Shd have been Tim instead of Tom
in my mail. (I blame the keyboard layout......)

Ur explanation abt the Dynam option and the fact that it makes a call
to DSNELI instead of DSNALI even if the latter is included in the
linkedit, made it clearer for me. Also ur initial suggestion abt
putting DSNALI in the STEPLIB. I am still testing the possibilities,
and hopefully will have set up something easy for the developers in a
few days.

Once again thanx to all of u for ur replies.

Merry Xmas.

Cheers,
Kals.


-----Original Message-----
From: [login to unmask email] [SMTP:[login to unmask email]
Sent: Saturday, December 22, 2001 3:11 AM
To: [login to unmask email]
Subject: Re: Stored procedure calling sub-programs : Sqlcode -927

Kals,
You did not address your message to me, but I should mention that your
second option will not work unless you also use the DB2 precompiler
option
ATTACH(CAF). If you compile your program with DYNAM, then it does not
matter what you linkedit with it, it will dynamically call it, and
ignore
the attach routine that you included in the linkedit. And, unless you
have also specified ATTACH(CAF), your dynamic call will be to module
DSNHLI, which is an alias to DSNELI, the DB2 TSO attach, and you will
get
-927. By using ATTACH(CAF), the code generated by the precompiler will
be
a call to DSNALI instead of DSNHLI.

Without any precompiler options, the DB2 calls become calls to the
module
DSNHLI. With ATTACH(CAF), they are calls to DSNALI.

Below are the DB2 attach environments, their names, and my notes on
options
how to make dynam work:
IMS DFSLI000 DYNAM OK since DSNHLI alias resolves to
DFSLI000
in IMS RESLIB
CICS DSNCLI Need a loadlib w/DSNHLI alias
CAF DSNALI Needs a loadlib w/DSNHLI alias or PRECOMPILE W/
ATTACH(CAF)
RRS DSNRLI Needs a loadlib w/DSNHLI alias or PRECOMPILE W/
ATTACH(RRSAF)
TSO DSNELI DYNAM OK since DSNHLI alias resolves to DSNELI in
DB2
SDSNLOAD

I hope this helps.
As alway, If there are any mistakes, I hope that someone will correct
them.

Thanks,
Tim



teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Re: Stored
procedure calling
Data Base sub-programs : Sqlcode -927
Discussion
List
<[login to unmask email]
M>


12/20/2001
09:25 PM
Please respond
to DB2 Data
Base
Discussion
List






Tom/Martin/Roger/Larry,

Thanx for ur replies. I did some more playing around before I got ur
replies, and got it working. But I am still trying to figure out if I
have fully understood all the possibilities. I am not too keen to setup
complex (eg. assembler stubs) solutions for my developers.
Unfortunately, the manual mentions only abt the setting of CURRENT
PACKAGESETs and nothing abt the compile/link-edits for calling
subprograms from SPs.

(I guess I have just got used to IBM manuals describing everything in
little detail that when I don't find something, I get lots of doubts)

My scenario is this : I have existing programs performing some
functions. Now these programs need to be called from the SP as well.
These subprograms have originally been compiled with 'DYNAM' option.
They may or may not call other programs. They do not have the DSNxLI
module lined with them.

From what I can see, the options for me r :

(1) Call the subprograms in static mode (I suppose CALL 'literal' is
the way to code), compile the SP with NODYNAM option, link the SP
INCLUDEing the subprograms along with DSNALI. As Tim said, the SP will
then need relinking when the subprogram changes and the subsequent
maintenance.

(2) Call them dynamically (CALL identifier), compile the SP with DYNAM
option and link it with DSNALI. In this case, I wd need to link my
subprograms with DSNALI and then store them in the SPAS STEPLIB
dataset. I wd then be having two different copies of my subprogram.

Can somebody confirm if the above two options wd work ?

Can I have a combination of both static and dynamic calls ?

I am still playing around.......

Thanx.

Cheers,
Kals
-----Original Message-----
From: Nicholson, Martin [SMTP:[login to unmask email]
Sent: Friday, December 21, 2001 2:24 AM
To: [login to unmask email]
Subject: Re: Stored procedure calling sub-programs : Sqlcode -927

Assuming that your sub-program call is static and you include load code
rather than object code:

Could there be a problem in the way you are link editing the stored
procedure.
All the CLIs have the same entry point name. I believe the first one
found
is the first one kept. Check the order of your includes. Include
DSNALI
before you include your subprogram. If the DSNELI is found first, it
will
resolve the DSNHLI calls.

> -----Original Message-----
> From: teldb2kals [SMTP:[login to unmask email]
> Sent: Wednesday, December 19, 2001 8:08 PM
> To: [login to unmask email]
> Subject: Stored procedure calling sub-programs : Sqlcode -927
>
> Hi,
>
> When a SProc calls other programs (with SQL in them), do these
> subprograms also need to be linked with DSNALI ? (we r using DB2
Stored
> Procedure Addressspace).
>
> We have a SP calling subprograms, but get an error -927 for the
> subprogram. Cd somebody lead us on the right way ?
>
> Thanx.
>
> Cheers,
> Kals


----------------
Powered by telstra.com