View previous topic :: View next topic
Author
Message
vishal_arora New User Joined: 20 May 2005Posts: 16
Hi All,
My requirement is to load a Table for which the input records are about 2.5 million . The input load files are 5 in numbers and together make it 2.5 million.
All the input files are on tape . The job is taking hell lot of time to complete i.e a total elapsed time of 374 mins with a CPU time of 20 mins.
Want to reduce the total elapsed time taken .
Pls contribute with some suggestions as how i can reduce the elapsed time
Back to top
dbzTHEdinosauer Global Moderator Joined: 20 Oct 2006Posts: 6966 Location: porcelain throne
is each tape sorted,
and is the order in which you load the tapes, sorted? -- no key in tape 2 is within the range loaded by tape 1?
Back to top
vishal_arora New User Joined: 20 May 2005Posts: 16
Yes the Tapes are sorted and concatenated in sorted order
Back to top
vishal_arora New User Joined: 20 May 2005Posts: 16
The following code extract is how i am loading the table from my load job
Code:
//TRXHX EXEC PGM=DSNUTILB,
// PARM=(DB0T,'LOAD99ARC'),
// REGION=0M
//STEPLIB DD DSN=DSN710.TARGETA.SDSNLOAD,DISP=SHR
//ABNLIGNR DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD DSN=NDLR.LOAD.PARMLIB(TRXHX),
// DISP=SHR
//SYSUT1 DD DSN=&&TSYSUT1,
// DISP=(NEW,DELETE),
// SPACE=(CYL,(600,200)),
// UNIT=SYSDA
//SORTOUT DD DSN=&&TSORTOUT,
// DISP=(NEW,DELETE),
// SPACE=(CYL,(600,200)),
// UNIT=SYSDA
//SYSMAP DD DSN=&&TSYSMAP,
// DISP=(NEW,DELETE),
// SPACE=(CYL,(600,200)),
// UNIT=SYSDA
//SYSERR DD DSN=&&TSYSERR,
// DISP=(NEW,DELETE),
// SPACE=(CYL,(600,200),RLSE),
// UNIT=SYSDA
//SYSREC00 DD DSN=NDLR.S1VTI.TRXHX.V8.LOAD1,DISP=SHR
// DD DSN=NDLR.S1VTI.TRXHX.V8.LOAD2,DISP=SHR
// DD DSN=NDLR.S1VTI.TRXHX.V8.LOAD3,DISP=SHR
// DD DSN=NDLR.S1VTI.TRXHX.V8.LOAD4,DISP=SHR
// DD DSN=NDLR.S1VTI.TRXHX.V8.LOAD5,DISP=SHR
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19244 Location: Inside the Matrix
Hello,
We cannot do much without the sysin statements. . .
Back to top
vishal_arora New User Joined: 20 May 2005Posts: 16
Sorry for late reply..
Here is the sysin card :
Code:
LOAD DATA INDDN SYSREC00 REPLACE LOG NO ENFORCE NO INTO TABLE
LCDDU.TRXHX
(
COMPANY_CODE POSITION( 1 )
CHAR( 3) ,
MASTER_ID POSITION( 4 )
CHAR( 15) ,
SEQUENCE_NUMBER POSITION( 19: 22)
DECIMAL ,
PRODUCT_CODE POSITION( 23 )
CHAR( 2) ,
SORT_CTL_IND POSITION( 25 )
CHAR( 1) ,
EFFECTIVE_DATE POSITION( 26: 30)
DECIMAL ,
SEQUENCE_NO POSITION( 31 )
CHAR( 2) ,
TRX_CODE POSITION( 33 )
CHAR( 4) ,
TRX_DATE POSITION( 37: 41)
DECIMAL ,
DATE_PROCESSED POSITION( 42: 45)
DECIMAL ,
TIME_PROCESSED POSITION( 46: 49)
DECIMAL ,
OPER_ID POSITION( 50 )
CHAR( 3) ,
TERM_ID POSITION( 53 )
CHAR( 4) ,
ORIGINATOR_CODE POSITION( 57 )
CHAR( 8) ,
SECONDARY_ID POSITION( 65 )
CHAR( 5) ,
ONLINE_DEL_REV POSITION( 70 )
CHAR( 1) ,
FILE_CODE POSITION( 71: 73)
DECIMAL ,
REVERSAL_CODE POSITION( 74 )
CHAR( 1) ,
REVERSAL_FILE_CODE POSITION( 75: 77)
DECIMAL ,
PRIOR_STATUS_CODE POSITION( 78 )
CHAR( 1) ,
CURR_STATUS_CODE POSITION( 79 )
CHAR( 1) ,
TRX_TYPE POSITION( 80 )
CHAR( 2) ,
PROCESS_DATE POSITION( 82: 86)
DECIMAL ,
SOURCE_OF_TRX_CODE POSITION( 87 )
CHAR( 1) ,
COUNTRY_OF_ORIGIN POSITION( 88 )
CHAR( 2) ,
CURRENCY_CODE POSITION( 90 )
CHAR( 1) ,
LANGUAGE_CODE POSITION( 91 )
CHAR( 1) ,
DISBURSEMENT_IND POSITION( 92 )
CHAR( 1) ,
PR_EVENT_TRX_CODE POSITION( 93 )
CHAR( 4) ,
PR_EVENT_FILE_CODE POSITION( 97: 99)
DECIMAL ,
REALTIME_PROC_IND POSITION( 100 )
CHAR( 1) ,
PREP_SORT_GROUP POSITION( 101 )
CHAR( 1) ,
LOAN_IND POSITION( 102 )
CHAR( 1) ,
ERROR_INFO POSITION( 103 )
CHAR( 40) ,
FAV_CODE POSITION( 143 )
CHAR( 6) ,
TRADE_PTNR_ID_NUM POSITION( 149 )
CHAR( 20) ,
CLRNG_HOUSE_NAME POSITION( 169 )
CHAR( 30) ,
TRADE_PTNR_CTL_NUM POSITION( 199 )
CHAR( 30) ,
CLRNG_HOUS_CTL_NUM POSITION( 229 )
CHAR( 30) ,
DISTR_CUST_ACT_NUM POSITION( 259 )
CHAR( 30) ,
DISTR_TRX_ID_NUM POSITION( 289 )
CHAR( 30) ,
APPL_CTL_NUM POSITION( 319 )
CHAR( 20) ,
BRC_CODE POSITION( 339 )
CHAR( 4)
)
Back to top
wanderer Active User Joined: 05 Feb 2007Posts: 199 Location: Sri Lanka
The input data is not very large, you may try to copy it to DASD and use it for SYSREC. Also, does the table have any indexes? If so, what type and how many columns?
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
The job is taking hell lot of time to complete i.e a total elapsed time of 374 mins with a CPU time of 20 mins.
Yes, that is a lot of time for not so many rows. . . Does the load take this long when run overnight or on a weekend or is the the "prime time" elapsed/cpu?
Quote:
The input data is not very large, you may try to copy it to DASD
Is the load data not on dasd already? With disp=shr, dasd seemed likely.
As Wanderer asked, info about any indexes would be useful.
Back to top
Please enable JavaScript!