Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Why start a new session improved efficiency
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Why start a new session improved efficiency
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Feb 21, 2013 7:42 pm    Post subject: Reply to: Why start a new session improved efficiency
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    Post subject: Re: Reply to: Why start a new session improved efficiency
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    Post subject: Reply to: Why start a new session improved efficiency
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Mon Feb 25, 2013 2:20 pm    Post subject: Reply to: Why start a new session improved efficiency
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: 1278
Location: Belgium

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

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

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Mon Feb 25, 2013 6:16 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Tue Feb 26, 2013 11:34 am    Post subject:
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    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Thu Feb 28, 2013 2:24 pm    Post subject:
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    Post subject:
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: 2422
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 3:12 pm    Post subject:
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    Post subject:
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: 2422
Location: Netherlands, Amstelveen

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

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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Feb 28, 2013 6:50 pm    Post subject: Reply to: Why start a new session improved efficiency
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: 2422
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 7:06 pm    Post subject: Re: Reply to: Why start a new session improved efficiency
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Feb 28, 2013 7:09 pm    Post subject: Reply to: Why start a new session improved efficiency
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: 2422
Location: Netherlands, Amstelveen

PostPosted: Thu Feb 28, 2013 7:55 pm    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts CICS START AND CANCEL blayek CICS 1 Wed Dec 07, 2016 3:27 am
No new posts Event-driven programming (BTS) VS (ST... Rohit Umarjikar CICS 2 Sat Jun 11, 2016 2:05 am
No new posts START Command in CICS amitc23 CICS 17 Fri Jun 03, 2016 3:49 pm
No new posts Replacing Start Transid with LINK pkmurali CICS 12 Tue Apr 19, 2016 8:22 pm
No new posts Start a CDBM transaction whit appropr... lind sh CICS 2 Sun Apr 10, 2016 1:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us