Regarding point 1 of your query, you can get the active log file from
the db cfg and delete earlier files by comparing filenames. Mine is a
windows environment. I've written a C program that gets the first active
log filename into a file from the db cfg and then deletes log files
whose name is "less" than that of the active log file. I've attached the
script. It might be of help. It also keeps a log file of how much space
was freed up after deleting the log files. It gets the names of the
databases to process from another temp file.
Regards,
Anand
-----Original Message-----
From: RLam
[mailto:db2udbdba-ezmlmshield-x31811580.[Email address protected]
Sent: Thursday, April 26, 2007 4:52 AM
To: LazyDBA Discussion
Subject: Re: SQL1036C An I/O error occurred while accessing the
database.
I think the DB is beyond repair. To work within a space constraint on
this play db, I have a cron job to purge any LOG that's older than 7
days. I'm guessing the LOG needed to complete the new container request
was already deleted by the cron.
To change topic slightly,
1. is there a way I can make the cron job more intelligent so that it
will purge only non active logs?
2. is it true that DB2 will continue to "function" even if the active
log was deleted?
3. how true is the beyond repair assumption?
Thanks for your ideas in advance.
Cheers
Ray
Raymond Lam/RBA
04/25/2007 03:49 PM
To
"LazyDBA Discussion" <[Email address protected] cc
Subject
SQL1036C An I/O error occurred while accessing the database.
hi gurus - thank god I'm getting this error on a play database but it'll
be great if I can learn the steps to fix besides restoring from backup.
What I'm trying to do is to compare the I/O performance between a
container located in NETAPP Storage versus LOCAL hard disk.
So I started with creating a new tablespace via a 3rd party GUI tool.
For some reason, the generated script was hanging and so is "LIST
TABLESPACES"
The file SQLTAG.NAM with 0 byte was created and I had to kill the
instance to terminate the application holding the lock.
Now that the instance was restarted, I could no longer connect to the
database due to the I/O error. Is there a way I can fix up this play db?
CREATE TABLESPACE LOCAL4K
IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4K
MANAGED BY SYSTEM
USING
('/usr/tmp '
)
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL DATABP
OVERHEAD 24.10
TRANSFERRATE 0.90
DROPPED TABLE RECOVERY OFF;
Cheers
Ray
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
#include <sys\types.h>
#include <sys\stat.h>
#include <stdio.h>
#include <sys\unistd.h>
FILE* firstactivelogfile(char dbname[]);
FILE* getpathtologfiles(char dbname[]);
FILE* listoflogfiles(char a[]);
FILE* listofinstances(void);
FILE* listofdatabasesforinstance(char i[]);
int filesize( const char *t );
int main()
{
float totalsize=0;
time_t *curtime;
struct tm *loctime;
char fmtdttm[40];
char cmnd[100], dbname[9],dbtype[9], instname[9];
char dbnamefile[20]=".\\temp\\listofdbs";
char frstactvlogfile[20],logfilename[20];
char pathtologfiles[40],pathofeachlogfile[100];
FILE *ffa,*fpf,*ffn,*fdb,*fw,*finst;
int filecount=0,filesize=0,totalcount=0,numdbs=0;
finst=listofinstances();
/* get the local time and format it to append to file*/
curtime=(time_t *) time(NULL);
loctime=localtime(&curtime);
strftime(fmtdttm,sizeof fmtdttm ,"%dth %B %Y %H.%M.%S",loctime);
/* Open log file and print current date-time*/
fw=fopen(".\\logclearlog.log","a+");
fprintf(fw,"%s :-\n",fmtdttm);
while(!feof(finst))
{
fscanf(finst,"%s\n",&instname);
fdb=listofdatabasesforinstance(instname);
printf("%s ",instname);
/* till the end of listofdbs file */
while(!feof(fdb))
{
/*intialize variables to zero/null */
filecount=0;
filesize=0;
strcpy(dbname,"");
strcpy(frstactvlogfile,"");
strcpy(pathtologfiles,"");
/* get the DB name and type and check if local db*/
fscanf(fdb," Database alias = %s\n",&dbname);
fscanf(fdb," Directory entry type = %s\n",&dbtype);
if((strcmp(dbtype,"Remote")==0) || (strcmp(dbname,"TOOLSDB")==0))
continue;
/* Get file ptrs to the files that have the first active log file name
and the path to the log files
*/
ffa=firstactivelogfile(dbname);
fpf=getpathtologfiles(dbname);
// Get the contents of the above files into variables
fscanf(ffa," First active log file = %s",&frstactvlogfile);
fscanf(fpf," Path to log files = %s",&pathtologfiles);
/* log the DB name and the first active log file */
fprintf(fw,"\t%s - %s", dbname, frstactvlogfile);
/* continue if first active log file NULL */
if(frstactvlogfile=="")
continue;
// Get a file ptr on the directory list of the log file path
ffn=listoflogfiles(pathtologfiles);
// Compare log file name and delete if lower than first active log file.
while(!feof(ffn))
{
// get the log file name
fscanf(ffn,"%s\n",&logfilename);
// if filename lower, delete.
if(strcmp(logfilename,frstactvlogfile)<0)
{
// increment filecount and get filesize
filecount++;
strcpy(pathofeachlogfile,pathtologfiles);
strcat(pathofeachlogfile,logfilename);
filesize+=sizeoffile(pathofeachlogfile);
// delete the log file
strcpy(cmnd,"del ");
strcat(cmnd,pathtologfiles);
strcat(cmnd,logfilename);
system(cmnd);
}
}
//end compare-while loop
// print count of deleted files.
fprintf(fw," - %d files deleted - total of %.2fMB cleared up\n",filecount,filesize/(1024*1024.0));
// increment total variables and numdbs
totalsize+=filesize/(1024*1024.0);
totalcount+=filecount;
numdbs++;
//close files since they'll be made again
fclose(ffa);
fclose(fpf);
fclose(ffn);
}
//end-of-while for all DBs
}
//end-of-while for instances
// if single DB exists, dont output total counts
if(numdbs>1)
fprintf(fw," Total:\t%d files deleted\t%.3fGB freed up\n\n",totalcount,totalsize/1024);
// close logclearlog file
fclose(fw);
// delete temp files
system("del .\\temp\\firstactivelogfile.tmp");
system("del .\\temp\\pathtologfiles.tmp");
system("del .\\temp\\logfilenames.tmp");
getch();
return 0;
}
/*
Function to return ptr to file having list of instances on the system
*/
FILE* listofinstances(void)
{
FILE* fp;
char cmnd[100]="";
char fileforinstancelist[]=".\\temp\\instancelist.tmp";
strcpy(cmnd,"db2ilist>");
strcat(cmnd,fileforinstancelist);
system(cmnd);
fp=fopen(fileforinstancelist,"r");
return fp;
}
/*
Function to return ptr to file having list of databases for particular instance
*/
FILE* listofdatabasesforinstance(char inst[])
{
FILE* fp;
int i;
char cmnd[100]="";
char filefordblist[]=".\\temp\\dblist.tmp";
system("db2cmd -i");
strcpy(cmnd,"set db2instance=");
strcat(cmnd,inst);
printf("%s",cmnd);
i=system(cmnd);
printf("%d",i);
getch();
system("db2cmd -w db2 get instance");
strcpy(cmnd,"db2cmd -w db2 list db directory | findstr /i \"alias type\" > ");
strcat(cmnd,filefordblist);
system(cmnd);
fp=fopen(filefordblist,"r");
return fp;
}
/*
Function to return ptr to file having first active log file name
*/
FILE* firstactivelogfile(char db[])
{
FILE* fp;
char cmnd[100]="";
char fileforfirstactivelogfile[]=".\\temp\\firstactivelogfile.tmp";
strcpy(cmnd,"db2cmd -c -w -i db2 get db cfg for ");
strcat(cmnd,db);
strcat(cmnd," | findstr /c:\"First active log file\" > ");
strcat(cmnd,fileforfirstactivelogfile);
system(cmnd);
fp=fopen(fileforfirstactivelogfile,"r");
return fp;
}
/*
Function to return ptr to file having path to log files
*/
FILE* getpathtologfiles(char db[])
{
FILE* fp;
char cmnd[100]="";
char fileforpathtologfiles[]=".\\temp\\pathtologfiles.tmp";
strcpy(cmnd,"db2cmd -c -w -i db2 get db cfg for ");
strcat(cmnd,db);
strcat(cmnd," | findstr /c:\"Path to log files\" > ");
strcat(cmnd,fileforpathtologfiles);
system(cmnd);
fp=fopen(fileforpathtologfiles,"r");
return fp;
}
/*
Function to return ptr to file having list of files in the log directory
*/
FILE* listoflogfiles(char pathfile[])
{
FILE *fp;
char cmnd[100]="";
char fileforlistoffilesatlogpath[]=".\\temp\\logfilenames.tmp";
strcpy(cmnd,"dir /b /on ");
strcat(cmnd,pathfile);
strcat(cmnd," > ");
strcat(cmnd,fileforlistoffilesatlogpath);
system(cmnd);
fp=fopen(fileforlistoffilesatlogpath,"r");
return fp;
}
/*
Function to return the size of a log file
*/
int sizeoffile( const char * filename )
{
struct stat filestat;
int err = stat(filename,&filestat );
if (0 != err) return 0;
return filestat.st_size;
}
DB2 & UDB email list listserv db2-l LazyDBA home page