DB2 Daily failing due to space restriction in monthy file.

Document ID : KB000006887
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

MICS Daily job failing in DAY065 due to space restriction in Months file.   Need to purge old data from Months file so the Daily can resume. 

Cause:

The checkpoint contained a date of 1999 next to M=900.  As a result, all of the data in this case (64 million records) were being written to the MONTHS file.  The long term solution is to regularly run a monthly as it will age the cycles off of the database based on the DBMODEL parm for the DB2 files. 

Resolution:

We first ran a PROC CONTENTS to see if there were any high cycle files that could be deleted 

//CONTENTS EXEC MICxxx

//SYSIN DD * 

PROC CONTENTS NODS DETAILS DATA=MONTHS._ALL_; RUN; 

This showed that there were no high cycle files but did show the page size for some of the DB2 files using the most space (DSU, DBU, DSP).

We then had the customer run the following code to give us counts of how much data we could drop.  We picked a date of 2014

//CONTENTS EXEC MICxxxx

//SYSIN DD * 

DATA DBPDBUM0; 

SET MONTHS.DBPDBUM0; 

IF DATEPART(ENDTS) GT MDY(5,1,2014); 

RUN; 

 

This gave us a count of how many records there are prior to 2014 which ended up being the majority of the 64 million records. 

We then had the customer run the following code for DB2 files using up the most space to drop the records prior to 2014 and

writes to the MONTHS file.   It is followed by another PROC CONENTS to get a new count after the write. 

 

//CONTENTS EXEC MICxxxx

//SYSIN DD * 

DATA MONTHS.DBPDBU00; 

SET MONTHS.DBPDBU00; 

IF DATEPART(ENDTS) GT MDY(5,1,2014); 

RUN; 

 

DATA MONTHS.DB2DSU00; 

SET MONTHS.DB2DSU00; 

IF DATEPART(ENDTS) GT MDY(5,1,2014); 

RUN; 

 

DATA MONTHS.DB2DPP00; 

SET MONTHS.DB2DPP00; 

IF DATEPART(ENDTS) GT MDY(5,1,2014); 

RUN; 

 

DATA MONTHS.DB2DSP00; 

SET MONTHS.DB2DSP00; 

IF DATEPART(ENDTS) GT MDY(5,1,2014); 

RUN; 

 

PROC CONTENTS NODS DATA=MONTHS._ALL_; 

 

The customer then restarted the job in the DAY065 step and it completed successfully.