SP CURSOR WITH RETURN - DB2 8 z/OS

phill baker

SP CURSOR WITH RETURN - DB2 8 z/OS
The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does this mean
the whole cursor is physically sent to the client or does the cursor stay on the
mainframe and the client can fetch rows from it? TIA Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Seibert

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to phill baker)
Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to David Seibert)
Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

M. Khalid Khan

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Philip Sevetson)
Do you have to code multi-row fetch for over-the-network applications ? I
thought that block fetch already took care of that. Can someone please
clarify this issue ?
TIA
Khalid





Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)



**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Todd Burch

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to M. Khalid Khan)
Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Agus Kwee

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Todd Burch)
Phil,
When the stored procedure terminates, some data is returned to the calling program so that the call to the stored procedure can receive sqlcode value +466 indicating that it is receiving 1 or more result sets.
Using DRDA, the number of result rows transmitted in one data transmission from the server to the client can be controlled by the OPTIMIZE FOR n ROWS option in the DECLARE CURSOR statement in the stored procedure.
What I do not know is whether this first group of result rows are transmited after the stored procedure termination or the first FETCH statement of the calling program.
Regards,
Agus Kwee
Themis Traing
http://www.themisinc.com

----- Original Message -----
From: fruitgum Phill
Date: Tuesday, January 27, 2009 12:05 pm
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
To: [login to unmask email]

> The manual seems to suggest that a CURSOR WITH RETURN in a
> Stored
> Procedure retrurns the whole cursor to the calling application.
> Does this mean
> the whole cursor is physically sent to the client or does the
> cursor stay on the
> mainframe and the client can fetch rows from it? TIA Phill
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new
> password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your
> member profile. If you are not already an IDUG.org member,
> please register at http://www.idug.org/component/juser/register.html
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Agus Kwee)
Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Todd Burch

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Philip Sevetson)
You can influence block fetch, but you don't control it, specifically.

For instance, FOR FETCH ONLY and FOR READ ONLY are triggering clauses
to enable block fetch.

"You" aren't doing it (blocking the data), per se - it's how DB2
packages messages for sending across a network. DB2 does it to
improve performance.

Check the Performance Management and Tuning Guide, or Info Center, and
search on "block fetch".

Todd

On Jan 27, 2009, at 1:28 PM, Sevetson, Phil wrote:

Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Simpson

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Todd Burch)
The way I understand it, DDF implements multi-row fetch for distributed
applications whenever block fetch is already in play. No coding changes
are required. The result set must be considered read only for this to
work (same rules as block fetch).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sevetson, Phil
Sent: Tuesday, January 27, 2009 1:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use
of the recipient(s) named above. If you are not an intended recipient,
or the employee or agent responsible to deliver it to an intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify me immediately by
replying to this message and delete this communication from your
computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to David Simpson)
What you are doing with Multi-Row Fetch is you are minimizing the number
of calls to DB2.
Each time you FETCH you have to pass control to DB2. Muli-Row Fetch
will help you with performance even if you application is running on the
same machine as DB2.
The idea is that in one FETCH or INSERT you can pass and receive an
array of data that you then processing in your program.
So let's say you have 1000 rows to fetch. You could open a cursor then
fetch 1001 times then close the cursor which would be 1003 conversations
with DB2, or
you could open a cursor, fetch for N rows, close cursor with a total of
3 conversations with DB2.
We have seen dramatic CPU performance gains from 30 to 70% using
multi-row fetch and insert.

Troy Coleman, Technical Sales Engineer
IBM Certified Database Administrator - DB2 9 for z/OS and LUW

SoftBase Systems, Inc.
847-776-0618
828-670-9900 ext. 334
[login to unmask email]

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.



Sevetson, Phil wrote:
> Clarification, please. How do you fetch multiple app data rows for a
> cursor without coding multi-row fetch? Is the network aware of the
> structure of the return set? I'm clearly missing something here. (Is
> there a redbook on minimizing network traffic with DB2 applications?)
>
> --Phil Sevetson
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Todd Burch
> Sent: Tuesday, January 27, 2009 12:57 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
>
> Hey Phil, just a quick point.
>
> Multi-row Fetch and block-fetch are two separate critters. Block-
> fetch is handled by the network pieces to cut down on network
> messages, while multi-row fetch deals with how the data actually gets
> put into application memory. You can have block fetch w/o multi-row
> fetch.
>
> Todd
>
>
> On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:
>
> Phill,
>
> What he said, and don't forget to code multi-row FETCH to cut down on
> network traffic.
>
> Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
> volunteering to leave.)
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Seibert, Dave
> Sent: Tuesday, January 27, 2009 11:21 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
>
> Hi Phil,
>
> It is my understanding and experience that the SP just leaves the
> cursor open and allows the caller to fetch from the result set.
> I don't think any of the result set is returned when the SP terms.
>
> I posted a Rexx example to the IBM DB2 examples trading post a couple
> years back. That site got migrated to a new site and I haven't reposted.
> It's pretty simple. I guess I should put it back out there.
> Lemme know if you're interested
>
> Dave
>
>
> The contents of this e-mail are intended for the named addressee only.
> It contains information that may be confidential. Unless you are the
> named addressee or an authorized designee, you may not copy or use it,
> or disclose it to anyone else. If you received it in error please notify
> us immediately and then destroy it.
>
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of fruitgum Phill
> Sent: Tuesday, January 27, 2009 10:17 AM
> To: [login to unmask email]
> Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
>
> The manual seems to suggest that a CURSOR WITH RETURN in a Stored
> Procedure retrurns the whole cursor to the calling application. Does
> this mean the whole cursor is physically sent to the client or does the
> cursor stay on the mainframe and the client can fetch rows from it? TIA
> Phill
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned to
> your account. Please log in and update your member profile. If you are
> not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned to
> your account. Please log in and update your member profile. If you are
> not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> =========
> Confidentiality Notice: This e-mail communication, and any
> attachments, contains confidential and privileged information for the
> exclusive use of the recipient(s) named above. If you are not an
> intended recipient, or the employee or agent responsible to deliver it
> to an intended recipient, you are hereby notified that you have
> received this communication in error and that any review, disclosure,
> dissemination, distribution or copying of it or its contents is
> prohibited. If you have received this communication in error, please
> notify me immediately by replying to this message and delete this
> communication from your computer. Thank you.
>
> Any opinions, expressed or implied, presented are solely those of the
> author and do not necessarily represent the opinions of the agency or
> the City.
> =========
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned
> to your account. Please log in and update your member profile. If you
> are not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned to
> your account. Please log in and update your member profile. If you are
> not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> =========
> Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.
>
> Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
> =========
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Myron Miller

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Troy Coleman)
Not all app's benefit from multiple row fetch. One of my application programmers converted for a test purpose, one of their app's that does literally thousands of fetches from a cursor. We tried fetch with 100, 10, 500, 1000 and 10000 and 1 for the number of rows to fetch. For our specific app, we found absolutely no measurable differences between any of the counts. For us, multiple row fetch made no difference at all. (and we did this for two different systems, the same type of benchmark).

I'm not saying all app's will work in similar manners but your mileage may vary and you need to test the app to see if the advertised performance gains actually are there.

Myron




________________________________
From: Troy Coleman <[login to unmask email]>
To: [login to unmask email]
Sent: Tuesday, January 27, 2009 5:01:58 PM
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

What you are doing with Multi-Row Fetch is you are minimizing the number of calls to DB2.
Each time you FETCH you have to pass control to DB2. Muli-Row Fetch will help you with performance even if you application is running on the same machine as DB2.
The idea is that in one FETCH or INSERT you can pass and receive an array of data that you then processing in your program.
So let's say you have 1000 rows to fetch. You could open a cursor then fetch 1001 times then close the cursor which would be 1003 conversations with DB2, or
you could open a cursor, fetch for N rows, close cursor with a total of 3 conversations with DB2.
We have seen dramatic CPU performance gains from 30 to 70% using multi-row fetch and insert.

Troy Coleman, Technical Sales Engineer
IBM Certified Database Administrator - DB2 9 for z/OS and LUW

SoftBase Systems, Inc. 847-776-0618
828-670-9900 ext. 334
[login to unmask email]
Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/
The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.


Sevetson, Phil wrote:
> Clarification, please. How do you fetch multiple app data rows for a
> cursor without coding multi-row fetch? Is the network aware of the
> structure of the return set? I'm clearly missing something here. (Is
> there a redbook on minimizing network traffic with DB2 applications?)
>
> --Phil Sevetson
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Todd Burch
> Sent: Tuesday, January 27, 2009 12:57 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
>
> Hey Phil, just a quick point.
>
> Multi-row Fetch and block-fetch are two separate critters. Block- fetch is handled by the network pieces to cut down on network messages, while multi-row fetch deals with how the data actually gets put into application memory. You can have block fetch w/o multi-row fetch.
>
> Todd
>
>
> On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:
>
> Phill,
>
> What he said, and don't forget to code multi-row FETCH to cut down on
> network traffic.
>
> Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
> volunteering to leave.)
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Seibert, Dave
> Sent: Tuesday, January 27, 2009 11:21 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
>
> Hi Phil,
>
> It is my understanding and experience that the SP just leaves the
> cursor open and allows the caller to fetch from the result set.
> I don't think any of the result set is returned when the SP terms.
>
> I posted a Rexx example to the IBM DB2 examples trading post a couple
> years back. That site got migrated to a new site and I haven't reposted.
> It's pretty simple. I guess I should put it back out there.
> Lemme know if you're interested
>
> Dave
>
>
> The contents of this e-mail are intended for the named addressee only.
> It contains information that may be confidential. Unless you are the
> named addressee or an authorized designee, you may not copy or use it,
> or disclose it to anyone else. If you received it in error please notify
> us immediately and then destroy it.
>
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of fruitgum Phill
> Sent: Tuesday, January 27, 2009 10:17 AM
> To: [login to unmask email]
> Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS
>
> The manual seems to suggest that a CURSOR WITH RETURN in a Stored
> Procedure retrurns the whole cursor to the calling application. Does
> this mean the whole cursor is physically sent to the client or does the
> cursor stay on the mainframe and the client can fetch rows from it? TIA
> Phill
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned to
> your account. Please log in and update your member profile. If you are
> not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned to
> your account. Please log in and update your member profile. If you are
> not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> =========
> Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.
>
> Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
> =========
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
> You should have gotten an e-mail with the temporary password assigned to
> your account. Please log in and update your member profile. If you are
> not already an IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> =========
> Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.
>
> Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
> =========
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Myron Miller

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Myron Miller)
Actually DDF does not implement multi-row fetch for distributed apps when block fetch is in play. The DDF app must still be coded to receive the multiple rows all at once, just like a mainframe cobol program. If it is not coded this way, multiple row fetch does not happen. Look up the syntax for JAVA fetches and apps to see how this works in the java world.

I've got a lot of apps which get block fetch (measured and seen by sniffers), but which are definitely not doing multiple row fetch. Several monitors, including local server java app monitors show the app issuing the full number of fetches. So we see a serious reduction in network traffic due to block fetch, but no reduction in the total number of FETCH calls by the app.

Myron




________________________________
From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Tuesday, January 27, 2009 4:57:03 PM
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The way I understand it, DDF implements multi-row fetch for distributed
applications whenever block fetch is already in play. No coding changes
are required. The result set must be considered read only for this to
work (same rules as block fetch).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sevetson, Phil
Sent: Tuesday, January 27, 2009 1:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use
of the recipient(s) named above. If you are not an intended recipient,
or the employee or agent responsible to deliver it to an intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify me immediately by
replying to this message and delete this communication from your
computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Seibert

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to Myron Miller)
Everybody's talked around this, without saying it.
With DDF implementing block fetch, you get some of the benefit of
multi-row fetch without the coding change. THe benefit is the avoidance
of network traffic -- Block fetch enables many rows to be brought over
the network to your local buffer, but you still need to fetch them one
by one.
You have to make code changes to get multi-row fetch.

Dave



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Myron Miller
Sent: Tuesday, January 27, 2009 8:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS


Actually DDF does not implement multi-row fetch for distributed apps
when block fetch is in play. The DDF app must still be coded to receive
the multiple rows all at once, just like a mainframe cobol program. If
it is not coded this way, multiple row fetch does not happen. Look up
the syntax for JAVA fetches and apps to see how this works in the java
world.

I've got a lot of apps which get block fetch (measured and seen by
sniffers), but which are definitely not doing multiple row fetch.
Several monitors, including local server java app monitors show the app
issuing the full number of fetches. So we see a serious reduction in
network traffic due to block fetch, but no reduction in the total number
of FETCH calls by the app.

Myron


________________________________

From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Tuesday, January 27, 2009 4:57:03 PM
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The way I understand it, DDF implements multi-row fetch for distributed
applications whenever block fetch is already in play. No coding changes
are required. The result set must be considered read only for this to
work (same rules as block fetch).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sevetson, Phil
Sent: Tuesday, January 27, 2009 1:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use
of the recipient(s) named above. If you are not an intended recipient,
or the employee or agent responsible to deliver it to an intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify me immediately by
replying to this message and delete this communication from your
computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Simpson

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to David Seibert)
From the redbook "DB2 Version 8 Everything You Ever Wanted to Know..."

"In most cases, you must change your applications in order to exploit multi-row fetch

operations. For example, you have to set up your applications to use host variable arrays.

However, when you use a remote client to connect to DB2, for example, a Java application

using a Type 4 connection with the Universal Driver, DB2 will automatically use multi-row

fetch "under the covers" when fetching rows from the tables while building a block that will be

sent back to the client.

In order for DB2 to be able to automatically exploit multi-row fetch for distributed applications,

the cursor has to be read-only or you must be using CURRENTDATA NO with an ambiguous

cursor. When these conditions are satisfied, DB2 can enable block fetching. When DB2, is

putting together a block of data to be sent to the client inside the DDF address space, DDF

issues FETCH statements (like any other application). When you are using block fetching

against a V8 DB2 system, the DDF address space will use multi-row fetch to build the blocks

of data to be sent to the client. This is completely transparent to the requester."


David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

________________________________

From: DB2 Data Base Discussion List on behalf of Myron Miller
Sent: Tue 1/27/2009 7:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS


Actually DDF does not implement multi-row fetch for distributed apps when block fetch is in play. The DDF app must still be coded to receive the multiple rows all at once, just like a mainframe cobol program. If it is not coded this way, multiple row fetch does not happen. Look up the syntax for JAVA fetches and apps to see how this works in the java world.

I've got a lot of apps which get block fetch (measured and seen by sniffers), but which are definitely not doing multiple row fetch. Several monitors, including local server java app monitors show the app issuing the full number of fetches. So we see a serious reduction in network traffic due to block fetch, but no reduction in the total number of FETCH calls by the app.

Myron


________________________________

From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Tuesday, January 27, 2009 4:57:03 PM
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The way I understand it, DDF implements multi-row fetch for distributed
applications whenever block fetch is already in play. No coding changes
are required. The result set must be considered read only for this to
work (same rules as block fetch).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com < http://www.themisinc.com/ >

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sevetson, Phil
Sent: Tuesday, January 27, 2009 1:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org < http://idug.org/ > was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use
of the recipient(s) named above. If you are not an intended recipient,
or the employee or agent responsible to deliver it to an intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify me immediately by
replying to this message and delete this communication from your
computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia < http://idug.org/lsAU >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Simpson

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to David Simpson)
Perhaps I need to distinguish between a z/OS to z/OS DDF application and a distributed one. For z/OS only DDF applications you must change your code. For some distributed access to DB2 you get it for "free" (see my earlier post with doc).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

________________________________

From: DB2 Data Base Discussion List on behalf of Seibert, Dave
Sent: Tue 1/27/2009 9:17 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS


Everybody's talked around this, without saying it.
With DDF implementing block fetch, you get some of the benefit of multi-row fetch without the coding change. THe benefit is the avoidance of network traffic -- Block fetch enables many rows to be brought over the network to your local buffer, but you still need to fetch them one by one.
You have to make code changes to get multi-row fetch.

Dave



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Tuesday, January 27, 2009 8:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS


Actually DDF does not implement multi-row fetch for distributed apps when block fetch is in play. The DDF app must still be coded to receive the multiple rows all at once, just like a mainframe cobol program. If it is not coded this way, multiple row fetch does not happen. Look up the syntax for JAVA fetches and apps to see how this works in the java world.

I've got a lot of apps which get block fetch (measured and seen by sniffers), but which are definitely not doing multiple row fetch. Several monitors, including local server java app monitors show the app issuing the full number of fetches. So we see a serious reduction in network traffic due to block fetch, but no reduction in the total number of FETCH calls by the app.

Myron


________________________________

From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Tuesday, January 27, 2009 4:57:03 PM
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The way I understand it, DDF implements multi-row fetch for distributed
applications whenever block fetch is already in play. No coding changes
are required. The result set must be considered read only for this to
work (same rules as block fetch).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com < http://www.themisinc.com/ >

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sevetson, Phil
Sent: Tuesday, January 27, 2009 1:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Clarification, please. How do you fetch multiple app data rows for a
cursor without coding multi-row fetch? Is the network aware of the
structure of the return set? I'm clearly missing something here. (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters. Block-
fetch is handled by the network pieces to cut down on network
messages, while multi-row fetch deals with how the data actually gets
put into application memory. You can have block fetch w/o multi-row
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple. I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application. Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it? TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org < http://idug.org/ > was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any
attachments, contains confidential and privileged information for the
exclusive use of the recipient(s) named above. If you are not an
intended recipient, or the employee or agent responsible to deliver it
to an intended recipient, you are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is
prohibited. If you have received this communication in error, please
notify me immediately by replying to this message and delete this
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned
to your account. Please log in and update your member profile. If you
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use
of the recipient(s) named above. If you are not an intended recipient,
or the employee or agent responsible to deliver it to an intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify me immediately by
replying to this message and delete this communication from your
computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia < http://idug.org/lsAU >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


________________________________

IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia < http://idug.org/lsAU >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: SP CURSOR WITH RETURN - DB2 8 z/OS
(in response to David Simpson)
Hi David,
I would agree with you. I assumed DDF was handled the same from z/OS to z/OS and from z/OS to non-z/OS clients.
But now that you bring it up I do remember hearing or maybe it was reading from the redbook that you get multi-row fetch automatically on non-z/OS clients as long as you are using CURRENTDATA NO and type 4 drivers.
I know for our application which is mostly z/OS to z/OS we coded for multi-row fetch and greatly improved the performance of our application.

Troy Coleman, Technical Sales Engineer IBM Certified Database Administrator - DB2 9 for z/OS and LUW SoftBase Systems, Inc. 847-776-0618 828-670-9900 ext. 334 [login to unmask email] Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/ The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.

David Simpson wrote: DIV { MARGIN: 0px }
Perhaps I need to distinguish between a z/OS to z/OS DDF application and a distributed one.  For z/OS only DDF applications you must change your code.  For some distributed access to DB2 you get it for "free" (see my earlier post with doc).
 
David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]


From: DB2 Data Base Discussion List on behalf of Seibert, Dave
Sent: Tue 1/27/2009 9:17 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Everybody's talked around this, without saying it.
With DDF implementing block fetch, you get some of the benefit of multi-row fetch without the coding change. THe benefit is the avoidance of network traffic -- Block fetch enables many rows to be brought over the network to your local buffer, but you still need to fetch them one by one.
You have to make code changes to get multi-row fetch.

Dave

 


From: DB2 Data Base Discussion List [mailto:[login to unmask email]] On Behalf Of Myron Miller
Sent: Tuesday, January 27, 2009 8:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Actually DDF does not implement multi-row fetch for distributed apps when block fetch is in play.  The DDF app must still be coded to receive the multiple rows all at once, just like a mainframe cobol program.  If it is not coded this way, multiple row fetch does not happen.  Look up the syntax for JAVA fetches and apps to see how this works in the java world.

I've got a lot of apps which get block fetch (measured and seen by sniffers), but which are definitely not doing multiple row fetch.  Several monitors, including local server java app monitors show the app issuing the full number of fetches.  So we see a serious reduction in network traffic due to block fetch, but no reduction in the total number of FETCH calls by the app.

Myron


From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Tuesday, January 27, 2009 4:57:03 PM
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The way I understand it, DDF implements multi-row fetch for distributed
applications whenever block fetch is already in play.  No coding changes
are required.  The result set must be considered read only for this to
work (same rules as block fetch).

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sevetson, Phil
Sent: Tuesday, January 27, 2009 1:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Clarification, please.  How do you fetch multiple app data rows for a
cursor without coding multi-row fetch?  Is the network aware of the
structure of the return set?  I'm clearly missing something here.  (Is
there a redbook on minimizing network traffic with DB2 applications?)

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Todd Burch
Sent: Tuesday, January 27, 2009 12:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hey Phil, just a quick point.

Multi-row Fetch and block-fetch are two separate critters.    Block-
fetch is handled by the network pieces to cut down on network 
messages, while multi-row fetch deals with how the data actually gets 
put into application memory.  You can have block fetch w/o multi-row 
fetch.

Todd


On Jan 27, 2009, at 10:31 AM, Sevetson, Phil wrote:

Phill,

What he said, and don't forget to code multi-row FETCH to cut down on
network traffic.

Phil Sevetson (There are TOO MANY PHILs on this forum, and no, I'm not
volunteering to leave.)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, January 27, 2009 11:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

Hi Phil,

  It is my understanding and experience that the SP just leaves the
cursor open and allows the caller to fetch from the result set.
I don't think any of the result set is returned when the SP terms.

I posted a Rexx example to the IBM DB2 examples trading post a couple
years back. That site got migrated to a new site and I haven't reposted.
It's pretty simple.  I guess I should put it back out there.
Lemme know if you're interested

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of fruitgum Phill
Sent: Tuesday, January 27, 2009 10:17 AM
To: [login to unmask email]
Subject: [DB2-L] SP CURSOR WITH RETURN - DB2 8 z/OS

The manual seems to suggest that a CURSOR WITH RETURN in a Stored
Procedure retrurns the whole cursor to the calling application.  Does
this mean the whole cursor is physically sent to the client or does the
cursor stay on the mainframe and the client can fetch rows from it?  TIA
Phill


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October  * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October  * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any 
attachments, contains confidential and privileged information for the 
exclusive use of the recipient(s) named above. If you are not an 
intended recipient, or the employee or agent responsible to deliver it 
to an intended recipient, you are hereby notified that you have 
received this communication in error and that any review, disclosure, 
dissemination, distribution or copying of it or its contents is 
prohibited. If you have received this communication in error, please 
notify me immediately by replying to this message and delete this 
communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the 
author and do not necessarily represent the opinions of the agency or 
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October  * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. 
You should have gotten an e-mail with the temporary password assigned 
to your account. Please log in and update your member profile. If you 
are not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October  * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use
of the recipient(s) named above. If you are not an intended recipient,
or the employee or agent responsible to deliver it to an intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify me immediately by
replying to this message and delete this communication from your
computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the
author and do not necessarily represent the opinions of the agency or
the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October  * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October  * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.



IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.



IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.



IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.