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-----

Brad Wissink

RE: Program shared in batch and stored procedure so is DSNULI the way to go?
(in response to bernd oppolzer)

Bernd,

  We tried option 2 and recompiled and linked all the sub DB2 programs with DSNRLI.  That worked great if the request if local.   If the request is remote we get a -751 which says one of our sub programs issued a COMMIT and that is not allowed.   One way to fix that is to use single phase commits, which ironically is what DSNALI uses.

So I am very interested in option 1.   The question I have is which sub-program do we need to put in a stored procedure.  Our current store procedure program, which has no db2, does a COBOL CALL to a driver program.  the driver program calls other programs, which call other program, until it get to a program that uses DB2.  Do we put the driver program or the actual DB2 program in the new stored procedure?    Plus, how does using this new stored procedure get rid of the DSNRLI requirement and allow the user of DSNALI?

bernd oppolzer

Program shared in batch and stored procedure so is DSNULI the way to go?
(in response to Brad Wissink)
Hello Brad,


Am 19.01.2018 um 17:08 schrieb Brad Wissink:
>
> Bernd,
>
>   We tried option 2 and recompiled and linked all the sub DB2 programs
> with DSNRLI.  That worked great if the request if local.   If the
> request is remote we get a -751 which says one of our sub programs
> issued a COMMIT and that is not allowed. One way to fix that is to use
> single phase commits, which ironically is what DSNALI uses.
>

Ok ... if you relinked the DB2 programs with DSNRLI, you could maybe also
inspect them, look for the COMMITs there and replace them with a
RRSAF COMMIT. The problem in this case is: if you use the same routines
locally, too, the batch jobs around them must establish RRSAF
connections, too
(and all the other DB2 sub programs contained there, too, and so on and
so on).
That's what I did at a large customer of mine: get RID of CAF and DSNALI
completely !!!
and change everything to RRSAF. This was very successful in the end,
although not
an easy way to go.

> So I am very interested in option 1.   The question I have is which
> sub-program do we need to put in a stored procedure.  Our current
> store procedure program, which has no db2, does a COBOL CALL to a
> driver program.  the driver program calls other programs, which call
> other program, until it get to a program that uses DB2.  Do we put the
> driver program or the actual DB2 program in the new stored
> procedure?    Plus, how does using this new stored procedure get rid
> of the DSNRLI requirement and allow the user of DSNALI?
>
>

This really doesn't matter much. You can also have a stored procedure
which does no DB2 call at all, but something completely different
(no reference to Monty Python intended). So you can decide at which
point you change the normal interface to a Stored Proc interface.

At my current customer's site, all stored procs are main programs,
which means that the parameters are passed like parameters to a
main function (which works OK for C programs, given the argc / argv
technique).
At my former customer's site, they were PL/1 procedures, not MAIN,
using normal PL/1 parameter conventions. But take care: there are
different parameter styles, depending on the definition of the stored
proc: GENERAL, GENERAL WITH NULLS and so on ...

Your last question: IMO you can use DSNALI inside the stored proc, at least
that's what I did at my current customer's site. I call some routines
dynamically
which I cannot change, and they are compiled and linked (and bound) using
CAF and DSNALI.

But: the routines that I call don't do COMMITs ... maybe this is what makes
my idea obsolete in the end ... the problem that you face will be the
"traditional" DB2 commit in a situation where there are two participants
under RRSAF control ...

Sorry about that ... so you may be stuck to option 2 ...