DB2 exit routine

Frank Swarbrick

DB2 exit routine
Is there a DB2 for z/OS exit routine that is invoked on, at least, the first EXEC SQL statement? Specifically, I'm interested in writing an exit that would allow me to specify the plan 'dynamically' when the first SQL statement is executed.

This is for BATCH (CAF), not CICS.

I know I can explicitly call a routine that calls the DSNALI OPEN function, but I'd like to avoid having the program do this explicitly, if possible. If there was such an exit routine invoked I would add the call to DSNALI OPEN inside of it.

Thanks,
Frank


--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


>>>

The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Todd Burch

Re: DB2 exit routine
(in response to Frank Swarbrick)
This is an interesting proposal.

There is nothing DB2 provided, and I'm not aware of any vendor product
that might do this.

You could (or, someone could) certainly write a stub DSNHLI that calls
DSNALI OPEN.

Todd




On Jan 5, 2011, at 5:21 PM, Frank Swarbrick wrote:

Is there a DB2 for z/OS exit routine that is invoked on, at least, the
first EXEC SQL statement? Specifically, I'm interested in writing an
exit that would allow me to specify the plan 'dynamically' when the
first SQL statement is executed.

This is for BATCH (CAF), not CICS.

I know I can explicitly call a routine that calls the DSNALI OPEN
function, but I'd like to avoid having the program do this explicitly,
if possible. If there was such an exit routine invoked I would add
the call to DSNALI OPEN inside of it.

Thanks,
Frank


--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


>>>

The information contained in this electronic communication and any
document attached hereto or transmitted herewith is confidential and
intended for the exclusive use of the individual or entity named
above. If the reader of this message is not the intended recipient or
the employee or agent responsible for delivering it to the intended
recipient, you are hereby notified that any examination, use,
dissemination, distribution or copying of this communication or any
part thereof is strictly prohibited. If you have received this
communication in error, please immediately notify the sender by reply
e-mail and destroy this communication. Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA
*
* If you are going to attend only one conference this year, this is
it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-
L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Tony Saul

Re: DB2 exit routine
(in response to Todd Burch)
An product I know of, picks up on the -923 to then issue the CAF connect and
rerun the sql, but it isn't something that can be used as an 'add on' product to
an existing application
 Regards,
Tony



----- Original Message ----
From: Frank Swarbrick <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, 6 January, 2011 9:51:07 AM
Subject: [DB2-L] DB2 exit routine

Is there a DB2 for z/OS exit routine that is invoked on, at least, the first
EXEC SQL statement?  Specifically, I'm interested in writing an exit that would
allow me to specify the plan 'dynamically' when the first SQL statement is
executed.

This is for BATCH (CAF), not CICS.

I know I can explicitly call a routine that calls the DSNALI OPEN function, but
I'd like to avoid having the program do this explicitly, if possible.  If there
was such an exit routine invoked I would add the call to DSNALI OPEN inside of
it.

Thanks,
Frank


--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO  USA
P: 303-235-1403


>>>

The information contained in this electronic communication and any document
attached hereto or transmitted herewith is confidential and intended for the
exclusive use of the individual or entity named above.  If the reader of this
message is not the intended recipient or the employee or agent responsible for
delivering it to the intended recipient, you are hereby notified that any
examination, use, dissemination, distribution or copying of this communication
or any part thereof is strictly prohibited.  If you have received this
communication in error, please immediately notify the sender by reply e-mail and
destroy this communication.  Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA
*
*  If you are going to attend only one conference this year, this is it!  *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
home of IDUG's Listserv





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 exit routine
(in response to Tony Saul)
Exit routine? No

BUT it is possible to write code that replaces DSNHLI and thus gets called INSTEAD of the real DSNHLI

I did this some time ago to provide such things as host variable tracing for COBOL programs

It gets a bit messy, and all I had to worry about was 16/31 bit addressing conflicts - now there's 64 bit as well

The theory should still work though

In "your" DSNHLI, you are responsible for doing a CAF connect to DB2 with a plan name of your choice and then, every time you are called, passing the SQL to DSNALI for DB2 to execute

Just been looking at my code - it was written in 1992(!), so I am not sure I want to share it as I'm pretty sure it won't work without significant modification

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Frank Swarbrick
Sent: 05 January 2011 23:21
To: [login to unmask email]
Subject: [DB2-L] DB2 exit routine

Is there a DB2 for z/OS exit routine that is invoked on, at least, the first EXEC SQL statement? Specifically, I'm interested in writing an exit that would allow me to specify the plan 'dynamically' when the first SQL statement is executed.

This is for BATCH (CAF), not CICS.

I know I can explicitly call a routine that calls the DSNALI OPEN function, but I'd like to avoid having the program do this explicitly, if possible. If there was such an exit routine invoked I would add the call to DSNALI OPEN inside of it.

Thanks,
Frank


--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


>>>

The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Todd Burch

Re: DB2 exit routine
(in response to Phil Grainger)
No worries about 64 bit addresses - the API (even DB2 10) does not
support 64 bit parms or addresses anywhere from "user" programs.

Todd

(Phil, I'm sure you meant 24 bit, not 16 bit!)



On Jan 6, 2011, at 8:54 AM, Phil Grainger wrote:

Exit routine? No

BUT it is possible to write code that replaces DSNHLI and thus gets
called INSTEAD of the real DSNHLI

I did this some time ago to provide such things as host variable
tracing for COBOL programs

It gets a bit messy, and all I had to worry about was 16/31 bit
addressing conflicts - now there's 64 bit as well

The theory should still work though

In "your" DSNHLI, you are responsible for doing a CAF connect to DB2
with a plan name of your choice and then, every time you are called,
passing the SQL to DSNALI for DB2 to execute

Just been looking at my code - it was written in 1992(!), so I am not
sure I want to share it as I'm pretty sure it won't work without
significant modification

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 exit routine
(in response to Todd Burch)
Whoops - yes, you're right (AMODE 24/31)

Sorry

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Todd Burch
Sent: 06 January 2011 15:19
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 exit routine

No worries about 64 bit addresses - the API (even DB2 10) does not
support 64 bit parms or addresses anywhere from "user" programs.

Todd

(Phil, I'm sure you meant 24 bit, not 16 bit!)



On Jan 6, 2011, at 8:54 AM, Phil Grainger wrote:

Exit routine? No

BUT it is possible to write code that replaces DSNHLI and thus gets
called INSTEAD of the real DSNHLI

I did this some time ago to provide such things as host variable
tracing for COBOL programs

It gets a bit messy, and all I had to worry about was 16/31 bit
addressing conflicts - now there's 64 bit as well

The theory should still work though

In "your" DSNHLI, you are responsible for doing a CAF connect to DB2
with a plan name of your choice and then, every time you are called,
passing the SQL to DSNALI for DB2 to execute

Just been looking at my code - it was written in 1992(!), so I am not
sure I want to share it as I'm pretty sure it won't work without
significant modification

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Bernd Oppolzer

Re: DB2 exit routine
(in response to Phil Grainger)
We do this all the time - checking on first call to "our own" DSNHLI,
if there is already a connection to DB2 and, if not, what kind of
connection
is required (TSO, IMS, Batch/CAF, Batch/RRSAF - no CICS here), then
establish the correct connection based on specified or looked-up subsystem
and plan informations, then calling the appropriate IBM language interface.

"Our" DSNHLI replacement is only a little stub which calls the logic that
I described above dynamically (MVS LOAD, entry point statically stored,
module loaded only once).

I'm not allowed to share the code, because it belongs to my customer,
but I'm certainly allowed to tell you about some technical details and
answer questions about it.

Kind regards

Bernd



Am 06.01.2011 15:54, schrieb Phil Grainger:
> Exit routine? No
>
> BUT it is possible to write code that replaces DSNHLI and thus gets called INSTEAD of the real DSNHLI
>
> I did this some time ago to provide such things as host variable tracing for COBOL programs
>
> It gets a bit messy, and all I had to worry about was 16/31 bit addressing conflicts - now there's 64 bit as well
>
> The theory should still work though
>
> In "your" DSNHLI, you are responsible for doing a CAF connect to DB2 with a plan name of your choice and then, every time you are called, passing the SQL to DSNALI for DB2 to execute
>
> Just been looking at my code - it was written in 1992(!), so I am not sure I want to share it as I'm pretty sure it won't work without significant modification
>
> Phil Grainger

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv