To point (1) of your query.
If the log is active, you cron job will *not* be able to purge active log
files as they are locked to db2. Running your job should get an error msg.
if you attempt it.

It is only if you run a job with a name of DB2UEXT2 or you use LOGARCHMETH1,
LOGARCHMETH2 that db2 will archive the *active* log as it fills up.

As to getting rid of the non-active log to purge them from your active log
path; if you use any of the above mentioned method, DB2 will take care of
it.
Assume S0000004.log is currently active and fills up. It is the 5th log
file open, there may be in flight transactions or committed non-externalized
transactions but the methods will archive the file. Logs ...05.log,
...06.log are treated the same way.
Example:
Logprimary 5 Logfilsiz 250pages ==> Primary log space=5.120MB
Logsecondary 10
First Active S0000005.log Next Active S0000009

When the MinBufLSN and the OldTranLSN are *not* part of log ...04.log it
means all transactions have been dealt with and the log can be deleted,
replaced, copied or whatever. When DB2 comes to allocate ...010.log, it is
cheaper and faster to rename ...04.log to ...010.log than to leave ...04.log
and create/allocate a new ...010.log
In this fashion you don't even have to purge your log path as DB2 will do it
automatically for you but always leaving at least as many log files as you
have defined no. of primaries.

To point (2)
If the active log is deleted as a result of a mishap and the db was active,
it will come to a state of "crash recovery" required. It will attempt to do
this as far in the currently active logs it can go. When it reaches a
damaged so that it can't go beyond. It stops and *presumes* it is at the
end of the logs. Closes everything and comes up clean but obviously short
of something which it and you won't know. The db cannot function without its
active log.

To point(3)
Very, very true. If you damage a db that is operational and running and you
lose (really lost, not accessible) one of its logs generated between now and
last backup, your db is only recoverable to the log preceding the one you
lost. It cannot roll forward and bypass the missing one. That's beyond
repair.

Hope this helps, Pierre.


-----Message d'origine-----
De : Kaushal Anand
[mailto:db2udbdba-ezmlmshield-x48101894.[Email address protected]
Envoyé : 28 avril, 2007 04:34
À : LazyDBA Discussion
Objet : RE: SQL1036C An I/O error occurred while accessing the database.

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;
}



---------------------------------------------------------------------
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




DB2 & UDB email list listserv db2-l LazyDBA home page