|
View previous topic :: View next topic
|
| Author |
Message |
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| 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 |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| maybe WLM ? |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
Ed Goodman
Active Member
Joined: 08 Jun 2011
Posts: 556
Location: USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| 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 |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| 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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
Add this : RXSUBCOM("DELETE","DSNREXX","DSNREXX") after :
"EXECSQL CLOSE C1" |
|
| Back to top |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| 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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
You should have asked me before  |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| 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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
A wild gues, maybe because of this
| Code: |
ADDRESS TSO "SUBCOM DSNREXX"
|
, instead of this
|
|
| Back to top |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| PeterHolland wrote: |
| You should have asked me before |
I go through all my topic and , it seems no  |
|
| Back to top |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| PeterHolland wrote: |
A wild gues, maybe because of this
| Code: |
ADDRESS TSO "SUBCOM DSNREXX"
|
, instead of this
|
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 |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| PeterHolland wrote: |
A wild gues, maybe because of this
| Code: |
ADDRESS TSO "SUBCOM DSNREXX"
|
, instead of this
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
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 |
|
|
|
|
