Program shared in batch and stored procedure so is DSNULI the way to go?

Brad Wissink

Program shared in batch and stored procedure so is DSNULI the way to go?

this is my first dive into Stored procedures and we have run into the issue where the stored procedure program calls another batch program that does DB2 SQL calls. That generated an error saying the called batch program was not linked with DSNRLI.   Since then I have been reading up on ways to deal with a program being used in batch and in a stored procedure and have found a lot of information on coping DSNRLI into a load library, rename it as DSNHLI and then put it first in the load library concatation for stored procedures.  then doing the same for DSNALI and batch programs.   I have also been reading up on DSNULI and doesn't it do that all for me.  I just have to link the program with DSNULI and then it will  figure out if the program is being used from a stored procedure or batch and make the correct interface connection?     I know that DSNULI has a performance impact so I was just interested in what people think is the best way to go?

Thanks

Philip Sevetson

Program shared in batch and stored procedure so is DSNULI the way to go?
(in response to Brad Wissink)
To clarify: when you say, “Batch Program,” do you mean an application-language program which runs under the address space of the calling SP?

From: Brad Wissink [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 11:00 AM
To: [login to unmask email]
Subject: [DB2-L] - Program shared in batch and stored procedure so is DSNULI the way to go?


this is my first dive into Stored procedures and we have run into the issue where the stored procedure program calls another batch program that does DB2 SQL calls. That generated an error saying the called batch program was not linked with DSNRLI. Since then I have been reading up on ways to deal with a program being used in batch and in a stored procedure and have found a lot of information on coping DSNRLI into a load library, rename it as DSNHLI and then put it first in the load library concatation for stored procedures. then doing the same for DSNALI and batch programs. I have also been reading up on DSNULI and doesn't it do that all for me. I just have to link the program with DSNULI and then it will figure out if the program is being used from a stored procedure or batch and make the correct interface connection? I know that DSNULI has a performance impact so I was just interested in what people think is the best way to go?

Thanks

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Brad Wissink

Program shared in batch and stored procedure so is DSNULI the way to go?
(in response to Philip Sevetson)
Yes. The ‘batch program’ is a COBOL application program we wrote and runs under the address space of the calling SP.

Brad Wissink| Sr. Systems Programmer
Sammons® Financial Group Member Companies
4350 Westown Pkwy | West Des Moines | IA 50266
Phone 515-267-2735 | ext. 33735 | Cell: 515-231-1511

[login to unmask email]<mailto:[login to unmask email]> | www.SammonsFinancialGroup.com http://www.sammonsfinancialgroup.com

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 10:06 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Program shared in batch and stored procedure so is DSNULI the way to go?

To clarify: when you say, “Batch Program,” do you mean an application-language program which runs under the address space of the calling SP?

From: Brad Wissink [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 11:00 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Program shared in batch and stored procedure so is DSNULI the way to go?


this is my first dive into Stored procedures and we have run into the issue where the stored procedure program calls another batch program that does DB2 SQL calls. That generated an error saying the called batch program was not linked with DSNRLI. Since then I have been reading up on ways to deal with a program being used in batch and in a stored procedure and have found a lot of information on coping DSNRLI into a load library, rename it as DSNHLI and then put it first in the load library concatation for stored procedures. then doing the same for DSNALI and batch programs. I have also been reading up on DSNULI and doesn't it do that all for me. I just have to link the program with DSNULI and then it will figure out if the program is being used from a stored procedure or batch and make the correct interface connection? I know that DSNULI has a performance impact so I was just interested in what people think is the best way to go?

Thanks

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

The information contained in this message is confidential, protected from disclosure and may be legally privileged. If the reader of this message is not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any disclosure, distribution, copying, or any action taken or action omitted in reliance on it, is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to this message and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.

bernd oppolzer

Program shared in batch and stored procedure so is DSNULI the way to go?
(in response to Brad Wissink)
You get the error from the program which calls the stored procedure and
which is therefore using the DSNRLI interface, correct? And this program
calls
another "normal" SQL subprogram, too, which probably uses DSNALI, right?

At my current customer we have a similar problem to yours: some batches
need the services of sub modules from a foreign system. The batches need
a RRSAF connection (that is, a DSNRLI interface) due to other reasons,
but the
sub modules only exist with DSNALI interfaces.

We get an error from the first SQL call of the sub module (SQL code
-991, I guess).

If this is the same situation as yours, you have two solutions, IMO:

1) put the sub program in a stored procedure and call this stored proc
instead.
This will be some coding effort, because the interface to the new stored
proc
will be different from the existiting interface to the SQL subprogram.
(That's what
we did: we had no control over the subprogram, because it belongs to
another team,
so we wrote a stored proc around it and called this stored proc ...
inside the stored
proc, the DSNALI interface was no problem).

2) if you can, link the sub program with DSNRLI instead of DSNALI. This
will be
much simpler, because you will not have to change the subprogram.

At my former customer's site we used DSNRLI for all batch programs, too
and eliminated DSNALI completely.

We did this, because we had some performance problems with a batch program
which dynamically loaded many submodules (hundreds!), and the CAF interface
DSNALI did not cope well with this situation. DSNRLI was much better. In
the end
we converted ALL batches to DSNRLI.

I have to admit that this was very easy to do, because at this site the
interfaces
are not hard linked into the batch modules, but instead we built a home
grown
replacement for ALL IBM DB2 interfaces (similar to DSNULI), which examines
the environment on the very first call and than branches to the appropriate
IBM interface (DSNRLI for Batch and Stored Procs, DSNELI for TSO,
another one
for IMS/DC - I don't remember the name, and DSNALI was obsolete after the
change mentioned above). So the change to move all batches to DSNRLI was
a change in one place only and some tests ... and many changes to job
control.

You will have to decide, which of the ways is best for you. We didn't
examine
DSNULI at my former customer's site, because our solution was already
working,
when DSNULI came out ... but IMO, it is virtually the same. We didn't
observe
any performance problems WITH OUR SOLUTION.

Kind regards

Bernd



Am 09.01.2018 um 16:59 schrieb Brad Wissink:
>
> this is my first dive into Stored procedures and we have run into the
> issue where the stored procedure program calls another batch program
> that does DB2 SQL calls. That generated an error saying the called
> batch program was not linked with DSNRLI. Since then I have been
> reading up on ways to deal with a program being used in batch and in a
> stored procedure and have found a lot of information on coping DSNRLI
> into a load library, rename it as DSNHLI and then put it first in the
> load library concatation for stored procedures.  then doing the same
> for DSNALI and batch programs.   I have also been reading up on DSNULI
> and doesn't it do that all for me.  I just have to link the program
> with DSNULI and then it will  figure out if the program is being used
> from a stored procedure or batch and make the correct interface
> connection?     I know that DSNULI has a performance impact so I was
> just interested in what people think is the best way to go?
>
> Thanks
>
>
> -----End Original Message-----