DSNREXX performance

Horacio Villa

DSNREXX performance
Hi list,

what can be wrong in a REXX using PREPARE, OPEN, FETCH & CLOSE with a
SELECT statement that takes .3 minutes to complete in DSNTIAUL, to take 90
minutes with the DSNREXX interface?
I don't think other REXX I'm using are that slow.
Don't know what else to look at.
Performance Report says it's in Application 99% of the time & lots of IO
waits.
The REXX doesn't do anything else but OPEN, FETCH (32xxx times) & CLOSE.
Any good ideas?

Cheers,

Horacio Villa

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DSNREXX performance
(in response to Horacio Villa)
Due to the nature of REXX interpretation, it will always be
significantly slower than a compiled program.

Somewhere I read that one of the enhancements in V8 is
DSNREXX performance. From fallible memory a 30 min to 2 min
reduction in elapsed times were observed for one program.

Some time ago I read an analysis of V6 DSNREXX behavoir -
apparently it invoked IRXEXCOM for every variable/column; I
presume V8 invokes IRXEXCOM only once per fetch. (IRXEXCOM
is the module that puts a program's data item into a REXX variable.
The switch into and out of REXX space makes this a rather
expensive process.)

James Campbell




On 7 Jan 2006 at 18:20, Horacio Villa wrote:

> Hi list,
>
> what can be wrong in a REXX using PREPARE, OPEN, FETCH & CLOSE with a
> SELECT statement that takes .3 minutes to complete in DSNTIAUL, to take 90
> minutes with the DSNREXX interface?
> I don't think other REXX I'm using are that slow.
> Don't know what else to look at.
> Performance Report says it's in Application 99% of the time & lots of IO
> waits.
> The REXX doesn't do anything else but OPEN, FETCH (32xxx times) & CLOSE.
> Any good ideas?
>
> Cheers,
>
> Horacio Villa
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Fazio

Re: DSNREXX performance
(in response to James Campbell)
Even though the Rexx language is interpretive I would not expect this
kind of delay.

I've got a hunch. How are you getting the data into the file?

Are you building a data stack, stem variable and then dumping to a
file
or are you writing to the file for each record?

If you are doing the later, that's your delay.

Feel free to email me your code, I'll be glad to take a peek.
fa

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn
z
>>> [login to unmask email] 2006-01-07 3:20:25 PM >>>
Hi list,

what can be wrong in a REXX using PREPARE, OPEN, FETCH & CLOSE with a
SELECT statement that takes .3 minutes to complete in DSNTIAUL, to take
90
minutes with the DSNREXX interface?
I don't think other REXX I'm using are that slow.
Don't know what else to look at.
Performance Report says it's in Application 99% of the time & lots of
IO
waits.
The REXX doesn't do anything else but OPEN, FETCH (32xxx times) &
CLOSE.
Any good ideas?

Cheers,

Horacio Villa

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html . From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org . The IDUG List Admins can be reached at
[login to unmask email] . Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jim Ruddy

Re: DSNREXX performance
(in response to Richard Fazio)
Prior to V8 we saw some very poor performance if the DSNREXX modules were
not in LPA and every execution of ADDRESS DSNREXX caused the module to be
loaded into memory and release at the end of the statement. The I/O wait you
are seeing may be an indicator that is happening. We made a change for V8 to
keep the module resident in memory and our benchmark showed the 25 times
improvement.

Btw, Rich Fazio didn't mention it but in his IDUG presentation last year he
described that the worst performance improvements he had been able to
measure was 75 times better and he could get no where close to the measly 25
times we measured. We also made an improvement in our use of the IRXEXCOM
API and saw a very nice CPU reduction (30%) as well. And no, these
improvements have not and will not be retrofitted to V7.

Jim Ruddy
DB2 for z/OS Development

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Fazio

Re: DSNREXX performance
(in response to Jim Ruddy)
Excellent point!

Actually, when we upgraded to V8 I thought one of my EXECS was broken
because it ran SO FAST.

I just ran one of them and it ran in 90 seconds, this used to run in
8-10 min.

FETCHES 38458
OPEN CSR 12850
CLOSE CSR 12850
DESCRIBES 12850

But, a single OPEN with a FETCH loop ran even faster!
faz

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2006-01-09 11:48:54 AM >>>
Prior to V8 we saw some very poor performance if the DSNREXX modules
were
not in LPA and every execution of ADDRESS DSNREXX caused the module to
be
loaded into memory and release at the end of the statement. The I/O
wait you
are seeing may be an indicator that is happening. We made a change for
V8 to
keep the module resident in memory and our benchmark showed the 25
times
improvement.

Btw, Rich Fazio didn't mention it but in his IDUG presentation last
year he
described that the worst performance improvements he had been able to
measure was 75 times better and he could get no where close to the
measly 25
times we measured. We also made an improvement in our use of the
IRXEXCOM
API and saw a very nice CPU reduction (30%) as well. And no, these
improvements have not and will not be retrofitted to V7.

Jim Ruddy
DB2 for z/OS Development

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html . From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org . The IDUG List Admins can be reached at
[login to unmask email] . Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: DSNREXX performance
(in response to Richard Fazio)
You guys are making me cry, here... I'm using REXX to automate some of our
utility processing (no, we're NOT considering using anyone's utility
automation tool, so don't ask) and we're in DB2V7 with no near-term plans to
migrate.

--Phil

P.S. I want to loudly thank Rich Fazio, Gabe Gargiulo (TSO/REXX expert on
the web), Howard Fosdick, and IBM's redbook team (the one that wrote the
book on optimizing dynamic SQL with the chapter on how to write REXX code to
access DB2; Rich, what was the book's name again?) for their priceless
advice in various presentations and books. I couldn't have done what I'm
doing without you guys.


On 1/9/06, Richard Fazio <[login to unmask email]> wrote:
>
> Excellent point!
>
> Actually, when we upgraded to V8 I thought one of my EXECS was broken
> because it ran SO FAST.
>
> I just ran one of them and it ran in 90 seconds, this used to run in 8-10
> min.
>
> FETCHES 38458
> OPEN CSR 12850
> CLOSE CSR 12850
> DESCRIBES 12850
>
> But, a single OPEN with a FETCH loop ran even faster!
> faz
>
> Rich Fazio
> Information Architecture
>
> TransUnion, LLC
> Marketing Services, 8th Floor
> 555 West Adams St. Chicago, IL 60661
> Phone (312) 985-3270 Fax (312) 466-6453
>
> Talk to teach - Listen to learn
>
>
> >>> [login to unmask email] 2006-01-09 11:48:54 AM >>>
> Prior to V8 we saw some very poor performance if the DSNREXX modules were
> not in LPA and every execution of ADDRESS DSNREXX caused the module to be
> loaded into memory and release at the end of the statement. The I/O wait
> you
> are seeing may be an indicator that is happening. We made a change for V8
> to
> keep the module resident in memory and our benchmark showed the 25 times
> improvement.
>
> Btw, Rich Fazio didn't mention it but in his IDUG presentation last year
> he
> described that the worst performance improvements he had been able to
> measure was 75 times better and he could get no where close to the measly
> 25
> times we measured. We also made an improvement in our use of the IRXEXCOM
> API and saw a very nice CPU reduction (30%) as well. And no, these
> improvements have not and will not be retrofitted to V7.
>
> Jim Ruddy
> DB2 for z/OS Development
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at *http://www.idugdb2-l.org/archives/db2-l.html* . From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at *
> http://www.idugdb2-l.org* . The IDUG List Admins can be reached at *
> [login to unmask email]* . Find out the latest on IDUG conferences
> at *http://conferences.idug.org/index.cfm*
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm




--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: DSNREXX performance
(in response to Philip Sevetson)
Ah, I found the redbook I'm asking about below:
"DB2 for z/OS and OS/390 : Squeezing the Most Out of Dynamic SQL"
(http://www.redbooks.ibm.com/abstracts/sg246418.html?Open)

Hope nobody did any work on my account for this.

--Phil again

On 1/10/06, Philip Sevetson <[login to unmask email]> wrote:
>
> You guys are making me cry, here... I'm using REXX to automate some of our
> utility processing (no, we're NOT considering using anyone's utility
> automation tool, so don't ask) and we're in DB2V7 with no near-term plans to
> migrate.
>
> --Phil
>
> P.S. I want to loudly thank Rich Fazio, Gabe Gargiulo (TSO/REXX expert on
> the web), Howard Fosdick, and IBM's redbook team (the one that wrote the
> book on optimizing dynamic SQL with the chapter on how to write REXX code to
> access DB2; Rich, what was the book's name again?) for their priceless
> advice in various presentations and books. I couldn't have done what I'm
> doing without you guys.
>
>
> On 1/9/06, Richard Fazio <[login to unmask email]> wrote:
> >
> > Excellent point!
> >
> > Actually, when we upgraded to V8 I thought one of my EXECS was broken
> > because it ran SO FAST.
> >
> > I just ran one of them and it ran in 90 seconds, this used to run in
> > 8-10 min.
> >
> > FETCHES 38458
> > OPEN CSR 12850
> > CLOSE CSR 12850
> > DESCRIBES 12850
> >
> > But, a single OPEN with a FETCH loop ran even faster!
> > faz
> >
> > Rich Fazio
> > Information Architecture
> >
> > TransUnion, LLC
> > Marketing Services, 8th Floor
> > 555 West Adams St. Chicago, IL 60661
> > Phone (312) 985-3270 Fax (312) 466-6453
> >
> > Talk to teach - Listen to learn
> >
> >
> > >>> [login to unmask email] 2006-01-09 11:48:54 AM >>>
> > Prior to V8 we saw some very poor performance if the DSNREXX modules
> > were
> > not in LPA and every execution of ADDRESS DSNREXX caused the module to
> > be
> > loaded into memory and release at the end of the statement. The I/O wait
> > you
> > are seeing may be an indicator that is happening. We made a change for
> > V8 to
> > keep the module resident in memory and our benchmark showed the 25 times
> > improvement.
> >
> > Btw, Rich Fazio didn't mention it but in his IDUG presentation last year
> > he
> > described that the worst performance improvements he had been able to
> > measure was 75 times better and he could get no where close to the
> > measly 25
> > times we measured. We also made an improvement in our use of the
> > IRXEXCOM
> > API and saw a very nice CPU reduction (30%) as well. And no, these
> > improvements have not and will not be retrofitted to V7.
> >
> > Jim Ruddy
> > DB2 for z/OS Development
> >
> > ---------------------------------------------------------------------------------
> >
> > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> > home page at *http://www.idugdb2-l.org/archives/db2-l.html *. From that
> > page select "Join or Leave the list". The IDUG DB2-L FAQ is at *http://www.idugdb2-l.org
> > *. The IDUG List Admins can be reached at *
> > [login to unmask email]* . Find out the latest on IDUG
> > conferences at *http://conferences.idug.org/index.cfm*
> >
> > ---------------------------------------------------------------------------------
> > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> > page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> > select "Join or Leave the list". The IDUG DB2-L FAQ is at
> > http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> > [login to unmask email] Find out the latest on IDUG conferences
> > at http://conferences.idug.org/index.cfm
>
>
>
>
> --
> --Phil Sevetson
> IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
> OS/390
> IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
> zOS
> Wakefern Food Corporation
> Edison, NJ, USA
> ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
> ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.




--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Horacio Villa

Re: DSNREXX performance
(in response to Philip Sevetson)
Hi Rick,

thanks for your replay.
Really, I also thought it was because it was writing to the file a record
at a time. To prove this, I "cut" the REXX, leaving only the "do while"
loop with the FETCH until it gets sqlcode 100, not writing anything. It
takes almost the same time than writing. Most of the time is in the FETCH.
The only difference I can see with other REXXs I have is that this one has
some more columns. And there's an answer from James Campbell saying that
DSNREXX invokes IRXEXCOM for every variable/column instead of once for each
fetch (as it's doing now in V8).
Anyway, here's the code (doesn't include que SELECT because it's generated
dynamically in the code):

sql_format1= ':SGrantor, :SGrantee, :SType, :STCreator, :STTName,',
':SAlter, :SDelete, :SIndex, :SInsert,',
':SSelect, :SUpdate, :SReference, :STrigger'
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE ¬= 0 THEN CALL P_ERROR_SQL
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
IF SQLCODE ¬= 0 THEN CALL P_ERROR_SQL
ADDRESS DSNREXX "EXECSQL OPEN C1"
IF SQLCODE ¬= 0 THEN CALL P_ERROR_SQL
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO" sql_format1
DO WHILE SQLCODE = 0
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO" sql_format1
IF SQLCODE < 0 THEN CALL P_ERROR_SQL
END
ADDRESS DSNREXX "EXECSQL CLOSE C1"
IF SQLCODE ¬= 0 THEN CALL P_ERROR_SQL

Cheers,

Horacio Villa




Richard Fazio
<[login to unmask email] To: [login to unmask email]
N.COM> cc:
Sent by: DB2 Data Subject: Re: [DB2-L] DSNREXX performance
Base Discussion
List
<[login to unmask email]
ORG>


09/01/2006 10:40
Please respond to
DB2 Database
Discussion list
at IDUG





Even though the Rexx language is interpretive I would not expect this kind
of delay.

I've got a hunch. How are you getting the data into the file?

Are you building a data stack, stem variable and then dumping to a file
or are you writing to the file for each record?

If you are doing the later, that's your delay.

Feel free to email me your code, I'll be glad to take a peek.
fa

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn
z
>>> [login to unmask email] 2006-01-07 3:20:25 PM >>>
Hi list,

what can be wrong in a REXX using PREPARE, OPEN, FETCH & CLOSE with a
SELECT statement that takes .3 minutes to complete in DSNTIAUL, to take 90
minutes with the DSNREXX interface?
I don't think other REXX I'm using are that slow.
Don't know what else to look at.
Performance Report says it's in Application 99% of the time & lots of IO
waits.
The REXX doesn't do anything else but OPEN, FETCH (32xxx times) & CLOSE.
Any good ideas?

Cheers,

Horacio Villa

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html . From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org . The IDUG List Admins can be reached at
[login to unmask email] . Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm