IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Why start a new session improved efficiency


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Thu Feb 21, 2013 7:10 pm
Reply with quote

These days I'm writing a REXX PGM with selecting DB2 table records.

The code like below
Code:

ADDRESS DSNREXX                                                   
PARMSQL3 = "SELECT "KEYITEM||TEMPCOL1" FROM PK80U1."KEYTBL" A,"||,
           "PK80U1."TBLNAME" B WHERE A.NUDOSS=B.NUDOSS"           
"EXECSQL DECLARE C3 CURSOR FOR S3"                               
"EXECSQL PREPARE S3 FROM :PARMSQL3"                               
"EXECSQL OPEN C3"                                                 
DO FOREVER                                                       
"EXECSQL FETCH C3 INTO :DBDATA1"                                 
 IF SQLCODE = 100 THEN LEAVE                                     
 OUTLINE1.1 = DBDATA1                                             
 ADDRESS TSO "EXECIO 1 DISKW OUTDD1 (STEM OUTLINE1. "             
END                                                               
"EXECSQL CLOSE C3"                                               


I found that , when I run this pgm for the first time , it is very fast.
but when I rerun it , it run slowly.

If I start a new session , it still run very fast.

Can anybody tell me why ? and how can I improve my code ?
Thanks !
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Feb 21, 2013 7:42 pm
Reply with quote

Why don't you run it with a Trace so you can see where it is "slow", if that is of no help, post the Trace output.
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Fri Feb 22, 2013 7:24 am
Reply with quote

Bill Woodger wrote:
Why don't you run it with a Trace so you can see where it is "slow", if that is of no help, post the Trace output.


Thank you .
I trace the pgm and found that the 'FETCH' get slow

1st run time elapsed : 0.000518
2nd time run elapsed: 0.013257

Long run for almost 25 times.
Could you tell me why the 'FETCH' run slow ? thanks .
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Mon Feb 25, 2013 10:29 am
Reply with quote

It seems that no one could tell me why in the TSO/ISPF forum ,
May I should start a topic in the DB2 or REXX forum ?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Feb 25, 2013 2:20 pm
Reply with quote

Quote:
It seems that no one could tell me why in the TSO/ISPF forum ,


remember... replying is on voluntary base
( when and if we feel like )

there is no commitment to reply,
THERE SHOULD NOT BE ANY EXPECTATION FOR AN ANSWER

when there are two <components> involved usually it does not make too much difference in which section You initially post...
only after a while, when things clear up, the topic might be moved by a moderator in a better suited section
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Feb 25, 2013 2:28 pm
Reply with quote

maybe WLM ?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Mon Feb 25, 2013 6:16 pm
Reply with quote

Chaoj,

Posting on another forum will not get new people in the mix.

Most of the people who read these forums read them all.

The seperate forums is for subject/topic seperation, not for user seperation.

If you did not get an answer to:
Quote:
Can anybody tell me why ? and how can I improve my code ?


It is because nobody knows, or cares to try and figure it out.

It would be best to take a new line of research in this matter.
Back to top
View user's profile Send private message
Ed Goodman

Active Member


Joined: 08 Jun 2011
Posts: 556
Location: USA

PostPosted: Mon Feb 25, 2013 8:12 pm
Reply with quote

One of my DBA co-workers once told me that there is a setting in TSO to allow up to a certain number of I/Os in a session before they start getting throttled.

It was a system-wide parm, probably to help prevent run-away tasks.

Also, you may be having a bit of fun with the OUTDD1 file. Are you freeing it and deleting the Dataset between runs? If not, you are re-using the space, or getting a not cat 2 error, which could be slowing things down.

Third, do you have to use a stem? If you are doing one line at a time, maybe a simple variable would reduce overhead.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2013 11:34 am
Reply with quote

Hello,

It sounds like runs 2 thru n are having to deal with clutter left over from the first run.

I don't know how to be more specific, but maybe this will generate a thought . . .
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Thu Feb 28, 2013 12:51 pm
Reply with quote

Ed Goodman wrote:
One of my DBA co-workers once told me that there is a setting in TSO to allow up to a certain number of I/Os in a session before they start getting throttled.

It was a system-wide parm, probably to help prevent run-away tasks.

Also, you may be having a bit of fun with the OUTDD1 file. Are you freeing it and deleting the Dataset between runs? If not, you are re-using the space, or getting a not cat 2 error, which could be slowing things down.

Third, do you have to use a stem? If you are doing one line at a time, maybe a simple variable would reduce overhead.


Thanks for your comment , I tried to delete the code which write to file , it also run slowly , I traced every line and compare the time , the [fetch] statement is the reason , but I can't not figure out why start a new session could affect the [fecth]

I run thIS REXX code with a panel , if I don't quit the panel , change the input and run again , it was OK , if I quit the panel ,and run again , it run slowly , I hope this test could help you (in fact help me to solve the problem )
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Thu Feb 28, 2013 1:19 pm
Reply with quote

dick scherrer wrote:
Hello,

It sounds like runs 2 thru n are having to deal with clutter left over from the first run.

I don't know how to be more specific, but maybe this will generate a thought . . .


Thanks ! I delete many codes which may confuse ,
In my system , there are about 10000 records in table :SYSIBM.SYSTABLES , if I run 2 times in the same TSO session , the 2nd time will be very slow than 1st time.

Code:

/* REXX */
/*----------------------------------------------------------------*/
/*                        MAIN PGM                                */
/*----------------------------------------------------------------*/

  CALL VAR_INIT /* VAR Init                   */
  CALL DB2_CONN /* DB2 Connect                */
  CALL OUT_WRIT /* Output DS Write            */

  SAY "*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*"
  SAY "             Unload Complete !!!!!               "
  SAY "*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*"

  ADDRESS DSNREXX "DISCONNECT"

EXIT
/*----------------------------------------------------------------*/
/*                        SUB PROC                                */
/*----------------------------------------------------------------*/
VAR_INIT:
  HDATCONT = '10000'
  COUNTER1 = 0     /* Table Data Counter        */
  PERCENT  = 0.1   /* Progress                  */
RETURN
/*----------------------------------------------------------------*/
DB2_CONN:
  ADDRESS TSO "SUBCOM DSNREXX"
  IF RC THEN
     S_RC = RXSUBCOMM('ADD','DSNREXX','DSNREXX')
  ADDRESS DSNREXX "CONNECT SUN"
RETURN
/*----------------------------------------------------------------*/
OUT_WRIT:
  ADDRESS DSNREXX
  PARMSQL1 = "SELECT NAME FROM SYSIBM.SYSTABLES"
  "EXECSQL DECLARE C1 CURSOR FOR S1"
  "EXECSQL PREPARE S1 FROM :PARMSQL1"
  "EXECSQL OPEN C1"
  DO FOREVER
   COUNTER1 = COUNTER1 + 1
  "EXECSQL FETCH C1 INTO :DBDATA1"
   IF SQLCODE = 100 THEN LEAVE
   IF (COUNTER1/HDATCONT) > PERCENT THEN
     DO
       SAY "  "PERCENT*100"% Complete."
       PERCENT = PERCENT + 0.1
     END
  END
  "EXECSQL CLOSE C1"
RETURN
/*----------------------------------------------------------------*/
[/quote]
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Feb 28, 2013 2:24 pm
Reply with quote

Hi Chaoj,

Looking at the first code, take out the below line out of the loop and try to increment the stem counter for each loop to hold all the data and once the loop is complete try to write the entire stem to the dataset.
Code:
ADDRESS TSO "EXECIO 1 DISKW OUTDD1 (STEM OUTLINE1. " 


And in your select query add "WITH UR"

Are you re-initializing the variables/stems properly after the first-run.

Is the third, fouth and fifth run's more slower than the earlier ones.

Regards,
Sushanth
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Thu Feb 28, 2013 2:43 pm
Reply with quote

sushanth bobby wrote:
Hi Chaoj,

Looking at the first code, take out the below line out of the loop and try to increment the stem counter for each loop to hold all the data and once the loop is complete try to write the entire stem to the dataset.
Code:
ADDRESS TSO "EXECIO 1 DISKW OUTDD1 (STEM OUTLINE1. " 


And in your select query add "WITH UR"

Are you re-initializing the variables/stems properly after the first-run.

Is the third, fouth and fifth run's more slower than the earlier ones.

Regards,
Sushanth


Thanks !
I deleted the EXECIO , and added "WITH UR" and then rerun , it still run slowly , 2nd,3rd......is the same .
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 3:12 pm
Reply with quote

Add this : RXSUBCOM("DELETE","DSNREXX","DSNREXX") after :
"EXECSQL CLOSE C1"
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Thu Feb 28, 2013 3:23 pm
Reply with quote

PeterHolland wrote:
Add this : RXSUBCOM("DELETE","DSNREXX","DSNREXX") after :
"EXECSQL CLOSE C1"



So cool !

It worked !

Thank you !!!!!!

and thanks all people who reply !!!!
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 3:30 pm
Reply with quote

You should have asked me before icon_rolleyes.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Feb 28, 2013 6:50 pm
Reply with quote

nice shot...
just curious, where did You find the tip ?

it is strange that a delete would be needed
Code:
DB2_CONN:
  ADDRESS TSO "SUBCOM DSNREXX"
  IF RC THEN
     S_RC = RXSUBCOMM('ADD','DSNREXX','DSNREXX')
  ADDRESS DSNREXX "CONNECT SUN"
RETURN


the rexx scripts does not add it if it is already there
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 7:06 pm
Reply with quote

enrico-sorichetti wrote:
nice shot...
just curious, where did You find the tip ?


A bit of googling for RXSUBCOM pointed me to the DELETE function
wich seemed appropiate for this case.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Feb 28, 2013 7:09 pm
Reply with quote

I was going over the topic again ...
and while You were replying I was editing my previous post,
to add the comment about the script checking before adding IT

( even if I do not agree on the <sloppy> style, better to put the continuation , for clarity )
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 7:55 pm
Reply with quote

A wild gues, maybe because of this
Code:
ADDRESS TSO "SUBCOM DSNREXX"
, instead of this
Code:
"SUBCOM DSNREXX"
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Fri Mar 01, 2013 7:23 am
Reply with quote

PeterHolland wrote:
You should have asked me before icon_rolleyes.gif


I go through all my topic and , it seems no icon_biggrin.gif
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Fri Mar 01, 2013 7:25 am
Reply with quote

PeterHolland wrote:
A wild gues, maybe because of this
Code:
ADDRESS TSO "SUBCOM DSNREXX"
, instead of this
Code:
"SUBCOM DSNREXX"


Share my trace log for subcom

1st time run
Code:

29 *-*  ADDRESS TSO "SUBCOM DSNREXX"               
   >>>    "SUBCOM DSNREXX"                         
   +++ RC(1) +++                                   
30 *-*  IF RC                                       
   *-*   THEN                                       
31 *-*   S_RC = RXSUBCOMM('ADD','DSNREXX','DSNREXX')
32 *-*  ADDRESS DSNREXX "CONNECT SUN"               
   >>>    "CONNECT SUN"                             
33 *-*  TRACE OFF                                   


2nd time run
Code:

29 *-*  ADDRESS TSO "SUBCOM DSNREXX"
   >>>    "SUBCOM DSNREXX"           
30 *-*  IF RC                       
32 *-*  ADDRESS DSNREXX "CONNECT SUN"
   >>>    "CONNECT SUN"             
33 *-*  TRACE OFF                   
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Fri Mar 01, 2013 7:29 am
Reply with quote

PeterHolland wrote:
A wild gues, maybe because of this
Code:
ADDRESS TSO "SUBCOM DSNREXX"
, instead of this
Code:
"SUBCOM DSNREXX"


Then I change to "SUBCOM DSNREXX"

1st time run
Code:

29 *-*  "SUBCOM DSNREXX"                           
   >>>    "SUBCOM DSNREXX"                         
   +++ RC(1) +++                                   
30 *-*  IF RC                                       
   *-*   THEN                                       
31 *-*   S_RC = RXSUBCOMM('ADD','DSNREXX','DSNREXX')
32 *-*  ADDRESS DSNREXX "CONNECT SUN"               
   >>>    "CONNECT SUN"                             
33 *-*  TRACE OFF                                   


2nd time run
Code:

29 *-*  "SUBCOM DSNREXX"             
   >>>    "SUBCOM DSNREXX"           
30 *-*  IF RC                       
32 *-*  ADDRESS DSNREXX "CONNECT SUN"
   >>>    "CONNECT SUN"             
33 *-*  TRACE OFF                   
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Fri Mar 01, 2013 4:19 pm
Reply with quote

SUBCOM returns zero to indicate that the named environment (DSNREXX, in this case) exists, and one to indicate that it doesn't. You are apparently not deleting the DSNREXX environment when you are finished.

Note that SUBCOM only detects the existence of an environment (more strictly, the existence of a row with that name in the command environment table), it does not validate it. I have difficulty believing that DSNREXX is doing significantly more validation when addressed via a preexisting row, but perhaps that is what is causing the delay.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Start CICS transaction every day at 2AM CICS 4
No new posts Z Monitoring server fails to start IBM Tools 1
No new posts I don't really get the, "Start a... General Talk & Fun Stuff 3
No new posts EIBCALEN 0 even if we pass LEN and FR... CICS 4
No new posts IBM Personal Communications session i... All Other Mainframe Topics 4
Search our Forums:

Back to Top