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
 

 

A temporary table problem

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Chrisyu

New User


Joined: 27 Nov 2008
Posts: 3
Location: China

PostPosted: Thu Nov 27, 2008 8:45 pm    Post subject: A temporary table problem
Reply with quote

...
Code:
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=30,REGION=3072K,COND=(0,NE)         
//STEPLIB  DD  DSN=DBLIB.DB2QMF.DSQLOAD,DISP=SHR -GOVERNOR BYPASS       
//         DD  DSN=DB2.QMF.DSQLOAD,DISP=SHR                             
//         DD  DSN=DB2.DSNLOAD,DISP=SHR                                 
//         DD  DSN=DB2.DSNEXIT,DISP=SHR                                 
//SYSPROC  DD  DSN=DBLIB.DB2QMF.DSQCLSTE,DISP=SHR                       
//ISPPLIB  DD  DSN=SYS1.ISP.SISPPENU,DISP=SHR                           
//         DD  DSN=DBLIB.DB2QMF.DSQPLIBE,DISP=SHR                       
//         DD  DSN=DB2.QMF.DSQPLIBE,DISP=SHR                           
//ISPMLIB  DD  DSN=SYS1.ISP.SISPMENU,DISP=SHR                 
//         DD  DSN=DB2.QMF.DSQMLIBE,DISP=SHR                   
//ISPSLIB  DD  DSN=SYS1.ISP.SISPSENU,DISP=SHR                 
//         DD  DSN=DBLIB.DB2QMF.DSQSLIBE,DISP=SHR             
//         DD  DSN=DB2.QMF.DSQSLIBE,DISP=SHR                   
//ISPTLIB  DD  DSN=DBLIB.DB2QMF.K6100196,DISP=SHR             
//         DD  DSN=SYS1.ISP.SISPTENU,DISP=SHR                 
//         DD  DSN=DBLIB.DB2QMF.DSQTLIBE,DISP=SHR             
//ISPPROF  DD  UNIT=SYSDA,SPACE=(TRK,(9,1,4)),                 
//             DCB=(LRECL=80,BLKSIZE=3120,RECFM=FB)           
//SYSUDUMP DD  SYSOUT=*                                       
//ADMGGMAP DD  DSN=DB2.QMF.DSQMAPE,DISP=SHR                   
//ADMCFORM DD  DSN=DB2.QMF.DSQCHART,DISP=SHR                   
//SYSEXEC  DD  DSN=DB2.QMF.DSQEXECE,DISP=SHR                   
//DSQPRINT DD  SYSOUT=*                                       
//DSQDEBUG DD  SYSOUT=*                                       
//ISPLOG   DD  SYSOUT=*,DCB=(RECFM=VA,LRECL=125,BLKSIZE=129)   
//DSQUDUMP DD  SYSOUT=*,DCB=(RECFM=VBA,LRECL=125,BLKSIZE=1632)
//DSQSPILL DD  DSN=&&SPILL,DISP=(NEW,DELETE),                 
//             UNIT=SYSVIO,SPACE=(CYL,(31,11),RLSE),                   
//             DCB=(RECFM=F,LRECL=4096,BLKSIZE=4096)                   
//SYSTSPRT DD  SYSOUT=*                                               
//SYSTSIN  DD  *                                                       
PROFILE PREFIX(PROD)                                                   
ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) +                                 
  PARM(M=B,S=DB2,I=PROD.P_USQSP1MO)                                   
//*
...

I have a job always abend in this step with S322 error. No matter what number was set to TIME parameter in the job card it still abend with S322.
On the other hand I also find that there is a temporary table will be generated in QMF proc:PROD.P_USQSP1MO and erased when the proc finish. Every time the abend happened during a query tried to select this temporary table. I tried to do a RUNSTATS on this temporary table and then reran this step. This time it ran successfully.Can anybody tell me the reason and how to avoid this kind of problem happen again,thanks!


Edited: Please use BBcode when You post some code, that's rather readable, Thanks...Anuj
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Nov 27, 2008 11:52 pm    Post subject:
Reply with quote

Hi Chrisyu,

In such problems, to get the best out of the forums, it's always helpful if you post the complete diagnostic information from the abended JOB.

Please use BBcode when You post the diagnostic info, preview it & when you are satisfied - post it.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1239
Location: Richfield, MN, USA

PostPosted: Sat Nov 29, 2008 8:39 am    Post subject:
Reply with quote

Are you sure that your JOB statement TIME parameter wasn't overridden by your site standards? What job class did it run in? How much time is allowed at your site for that particular job class?
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: Sun Nov 30, 2008 4:20 am    Post subject:
Reply with quote

Hello,

How many rows are put into the temporary table for this process? Does the run that succeeds process less data than the one that fails?

Did some process run that deleted some/many of the rows in the table used by this process to create the temporary between the abended run and the successful run?

Typically, a query that processes a similar number of rows will use a similar amount of cpu time. If the run sometimes succeeds and sometimes abends with a 322 (and is running in the same CLASS), the volume of data may be the difference.

How many total rows are processed in any table (temporary or real) in the run that abends?
Back to top
View user's profile Send private message
Chrisyu

New User


Joined: 27 Nov 2008
Posts: 3
Location: China

PostPosted: Mon Dec 01, 2008 1:25 pm    Post subject:
Reply with quote

Terry Heinze wrote:
Are you sure that your JOB statement TIME parameter wasn't overridden by your site standards? What job class did it run in? How much time is allowed at your site for that particular job class?

Thanks Terry,
Actually the original parameter is TIME=10 and this job has been there running for many years.It just suffered this problem recent few months.It's weird that every time I must do a RUNSTATS on that temporary table after it abend with S322 and then this step can run successfully.
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: Mon Dec 01, 2008 9:06 pm    Post subject:
Reply with quote

Hello,

Quote:
It's weird that every time I must do a RUNSTATS on that temporary table after it abend with S322 and then this step can run successfully.
This should be unacceptable. . . icon_sad.gif

The process needs to be corrected so that it will run successfully the first time. Proper analysis should show whether something could be done with the query(ies), the index(es) available for the process in either the input or output, or possibly simply running with a greater TIME parameter.

If the data has grown over time, it is reasonable to use more time than previously. When the job runs successfully, how much cpu time does it require?
Back to top
View user's profile Send private message
Chrisyu

New User


Joined: 27 Nov 2008
Posts: 3
Location: China

PostPosted: Mon Dec 08, 2008 8:09 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Quote:
It's weird that every time I must do a RUNSTATS on that temporary table after it abend with S322 and then this step can run successfully.
This should be unacceptable. . . icon_sad.gif

The process needs to be corrected so that it will run successfully the first time. Proper analysis should show whether something could be done with the query(ies), the index(ex) available for the process in either the input or output, or possibly simply running with a greater TIME parameter.

If the data has grown over time, it is reasonable to use more time than previously. When the job runs successfully, how much cpu time does it require?


Hi Dick,
After I do RUNSTATS on it , the step will only take no more than 1 minute to finish.
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm


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