DSNREXX Performance

Rolf Drees

DSNREXX Performance

In some cases we use REXX for SQL-processing with DSNREXX, usually to generate JCL, Grants, etc.. But when I process a large cursor with thousands rows this solution is always very slow.

What I can see is that there are a lot of EXCPs and from a Strobe-Report I know that these EXCPs occur on DD-Name Steplib. So I assume that they come from the FETCHes, where in every Fetch the DSNREXX-module is called.

Am I right? Is there any way to avoid these EXCPs by some kind of preloading the DSNREXX-module?

Kind regards

Rolf Drees

Walter Janißen

AW: DSNREXX Performance
(in response to Rolf Drees)
Hi Rolf

What about multi-row-fetch?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rolf Drees [mailto:[login to unmask email]
Gesendet: Donnerstag, 12. Oktober 2017 10:14
An: [login to unmask email]
Betreff: [DB2-L] - DSNREXX Performance


In some cases we use REXX for SQL-processing with DSNREXX, usually to generate JCL, Grants, etc.. But when I process a large cursor with thousands rows this solution is always very slow.

What I can see is that there are a lot of EXCPs and from a Strobe-Report I know that these EXCPs occur on DD-Name Steplib. So I assume that they come from the FETCHes, where in every Fetch the DSNREXX-module is called.

Am I right? Is there any way to avoid these EXCPs by some kind of preloading the DSNREXX-module?

Kind regards

Rolf Drees

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Roy Boxwell

AW: DSNREXX Performance
(in response to Rolf Drees)
Jim Ruddy wrote this many years ago:

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.

Perhaps it might help? All I can say is that REXX SQL has the performance of a very sick puppy dog...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

Von: Rolf Drees [mailto:[login to unmask email]
Gesendet: Donnerstag, 12. Oktober 2017 10:14
An: [login to unmask email]
Betreff: [DB2-L] - DSNREXX Performance


In some cases we use REXX for SQL-processing with DSNREXX, usually to generate JCL, Grants, etc.. But when I process a large cursor with thousands rows this solution is always very slow.

What I can see is that there are a lot of EXCPs and from a Strobe-Report I know that these EXCPs occur on DD-Name Steplib. So I assume that they come from the FETCHes, where in every Fetch the DSNREXX-module is called.

Am I right? Is there any way to avoid these EXCPs by some kind of preloading the DSNREXX-module?

Kind regards

Rolf Drees

-----End Original Message-----

Rolf Drees

RE: AW: DSNREXX Performance
(in response to Walter Janißen)

Hello Walter,

as far as I know Multi-Row-Processing is still missing in DSNREXX.

Kind regards, Rolf

Mick Graley

AW: DSNREXX Performance
(in response to Roy Boxwell)
Roy is correct (multiple LOADs/DELETEs of the module) and it's terrible in
tight fetch loop processing. However, you don't have to put the DSNREXX
module into the LPA to get around this. Many years ago I wrote a tiny
assembler program that loads DSNREXX into your JPA. All you have to do is
link to the assembler program at the top of your ReXX exec.

Here's the assembler code:

DSLOADXX MGSTART ,
LOAD EP=DSNREXX
MGRETURN ,
END

MGSTART and MGRETURN are my own entry and exit macros, just replace them
with the standard linkage code.

Here's an example start of a ReXX exec that uses it:

/* SET UP DB2 INTERFACE */
S_RC = RXSUBCOM("ADD","DSNREXX","DSNREXX")
ADDRESS LINK "DSLOADXX"
ADDRESS DSNREXX "CONNECT xxxx"

Hope that's useful.

Cheers,

Mick.


On 12 October 2017 at 12:21, Boxwell, Roy <[login to unmask email]> wrote:

> Jim Ruddy wrote this many years ago:
>
>
>
> 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.
>
>
>
> Perhaps it might help? All I can say is that REXX SQL has the performance
> of a very sick puppy dog...
>
>
>
> *Roy Boxwell*
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
>
> *Heinrichstrasse 83
> https://maps.google.com/?q=Heinrichstrasse+83&entry=gmail&source=g -85
> 40239 Duesseldorf/Germany*
> * Tel. *
>
> *+49 (0)211 96149-675 <+49%20211%2096149675> Fax +49 (0)211 96149-32
> <+49%20211%209614932> Email: **[login to unmask email]* <[login to unmask email]>
> *http://www.seg.de* http://www.seg.de
>
>
>
> * Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Bettina Schubert*
>
>
>
> *Von:* Rolf Drees [mailto:[login to unmask email]
> *Gesendet:* Donnerstag, 12. Oktober 2017 10:14
> *An:* [login to unmask email]
> *Betreff:* [DB2-L] - DSNREXX Performance
>
>
>
> In some cases we use REXX for SQL-processing with DSNREXX, usually to
> generate JCL, Grants, etc.. But when I process a large cursor with
> thousands rows this solution is always very slow.
>
> What I can see is that there are a lot of EXCPs and from a Strobe-Report I
> know that these EXCPs occur on DD-Name Steplib. So I assume that they come
> from the FETCHes, where in every Fetch the DSNREXX-module is called.
>
> Am I right? Is there any way to avoid these EXCPs by some kind of
> preloading the DSNREXX-module?
>
> Kind regards
>
> Rolf Drees
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Rolf Drees

RE: AW: DSNREXX Performance
(in response to Mick Graley)

I spoke to our sysprogs, DSNREXX is not in LPA. Thanks Mick, I will try to to use your program.

I will put a note here how it works.

Regards, Rolf

Rolf Drees

RE: AW: DSNREXX Performance
(in response to Rolf Drees)

Mick Graleys little Assembler-program was very helpful for me, thank you very much, Mick!

I wrote a small REXX-Script that fetches 100,000 rows from SYSIBM.SYSOLUMNS with this SQL:

 

select tbname, tbcreator, name from sysibm.syscolumns fetch first 100000 rows only 

 

The rows are only fetched into Hostvariables, no further processing.

This script without loading DSNREXX was running more than 6 minutes, with loading it took only 11 seconds!

Here are some numbers from the Joblog:

 

                                         --TIMINGS (MINS.)--       
JOBNAME  STEPNAME PROCSTEP    RC   EXCP    CPU    SRB  CLOCK   SERV
SYSCOLS  LOADNO       IKJEFT01       00    4400K    .72        .00        6.55   5374K
SYSCOLS  LOADYES     IKJEFT01       00         353    .07        .00          .11     350K             

 

Kind regards

Rolf