Unable to Repair Large Data Set
in [SAS]
|
From: Patrick on 3 Oct 2009 22:49 Why couldn't you just re-create the index? Something like: data large; do keyA=1 to 10000; keyB=ceil(ranuni(0)*1000); output; end; run; proc sql; create index keyAKeyB on large ( KeyA,Keyb); create index KeyB on large ( Keyb); quit;
From: Michael Raithel on 5 Oct 2009 08:03 Dear SAS-L-ers, Kevin Myers posted the following: > > Using SAS 8.2 under Windows XP, I had a situation come up where index > files (sas7bndx) were accidentally deleted for a number of large SAS > datasets. Most of these data sets were repaired using PROC DATASETS > without any problems. However, the largest data set, approximately 2.9 > GB, could not be repaired. Eventually I gave up and re-created the > data set, which I had hoped to avoid, since that required a process > that takes several hours. > > Anyway, has anyone else run into a similar scenario where they were > unable to repair a very large SAS data set, after a relatively minor > corruption such as index deletion? > Kevin, first of all, sorry to read about your problem; what a drag! Second= ly, to answer your question right up front: Yes, over the years, I have be= en unable to repair both large and small SAS data sets that became corrupte= d. But, mostly, they became corrupted for reasons that I was never able to= discern-except for a few that definitely ran out of disk space while being= created or updated. But, corrupted SAS data sets have generally been a ra= rity for me. I couldn't help but be curious about your statement regarding "...after a r= elatively minor corruption such as index deletion". When you use the SAS s= ystem to delete an index (PROC DATASETS or PROC SQL), SAS dutifully removes= the specified index. If there were multiple indexes for a given SAS data = set and you have deleted them all, SAS will delete the index file, itself-t= he .sas7bndx file. Because SAS is doing all of the work and keeping score,= it updates the SAS data set with information concerning the fact that its = indexes are history. So, there "shouldn't" be a problem with index deletio= ns through SAS. If you delete the index file, the .sas7bndx file, via an OS command, SAS re= cognizes that the SAS data set is "damaged" the next time that it opens it = (reads the SAS data set's header page). If the DLDMGACTION option is enabl= ed (the default), SAS attempts to re-create all of the indexes. So, luckil= y, a deleted, moved, or renamed index file via an OS command issue is usual= ly handily resolved by SAS. In these cases, I precipitate SAS rebuilding t= he deleted indexes by simply running a PROC CONTENTS on the afflicted SAS d= ata set. Could it be that your index file was deleted via the OS and that, when you = attempted to repair your HUMONGO SAS data set, there was not enough room on= the disk to rebuild the indexes and thus repair your SAS data set? That c= ould account for the SAS data set continuing to be "damaged". Or, am I rea= ding too much into your posting? Kevin, best of luck in all of your SAS endeavors! I hope that this suggestion proves helpful now, and in the future! Of course, all of these opinions and insights are my own, and do not reflec= t those of my organization or my associates. All SAS code and/or methodolog= ies specified in this posting are for illustrative purposes only and no war= ranty is stated or implied as to their accuracy or applicability. People de= ciding to use information in this posting do so at their own risk. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Michael A. Raithel "The man who wrote the book on performance" E-mail: MichaelRaithel(a)westat.com Author: Tuning SAS Applications in the MVS Environment Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second= Edition http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=3D1&pc=3D58172 Author: The Complete Guide to SAS Indexes http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=3D1&pc=3D60409 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Of what use is freedom of speech to those who fear to offend? - Roger Ebert +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From: Kevin Myers on 5 Oct 2009 16:31 Yes, the index files (.sas7bndx) were deleted by an OS command, and subsequent attempts to repair, both automatic and manual were unsuccessful. No, the disks in question were not even remotely close to running out of space. But thanks for trying... :-) ----- Original Message ----- From: "Michael Raithel" <michaelraithel(a)WESTAT.COM> To: <SAS-L(a)LISTSERV.UGA.EDU> Sent: Monday, October 05, 2009 07:03 Subject: Re: Unable to Repair Large Data Set Dear SAS-L-ers, Kevin Myers posted the following: > > Using SAS 8.2 under Windows XP, I had a situation come up where index > files (sas7bndx) were accidentally deleted for a number of large SAS > datasets. Most of these data sets were repaired using PROC DATASETS > without any problems. However, the largest data set, approximately 2.9 > GB, could not be repaired. Eventually I gave up and re-created the > data set, which I had hoped to avoid, since that required a process > that takes several hours. > > Anyway, has anyone else run into a similar scenario where they were > unable to repair a very large SAS data set, after a relatively minor > corruption such as index deletion? > Kevin, first of all, sorry to read about your problem; what a drag! Secondly, to answer your question right up front: Yes, over the years, I have been unable to repair both large and small SAS data sets that became corrupted. But, mostly, they became corrupted for reasons that I was never able to discern-except for a few that definitely ran out of disk space while being created or updated. But, corrupted SAS data sets have generally been a rarity for me. I couldn't help but be curious about your statement regarding "...after a relatively minor corruption such as index deletion". When you use the SAS system to delete an index (PROC DATASETS or PROC SQL), SAS dutifully removes the specified index. If there were multiple indexes for a given SAS data set and you have deleted them all, SAS will delete the index file, itself-the .sas7bndx file. Because SAS is doing all of the work and keeping score, it updates the SAS data set with information concerning the fact that its indexes are history. So, there "shouldn't" be a problem with index deletions through SAS. If you delete the index file, the .sas7bndx file, via an OS command, SAS recognizes that the SAS data set is "damaged" the next time that it opens it (reads the SAS data set's header page). If the DLDMGACTION option is enabled (the default), SAS attempts to re-create all of the indexes. So, luckily, a deleted, moved, or renamed index file via an OS command issue is usually handily resolved by SAS. In these cases, I precipitate SAS rebuilding the deleted indexes by simply running a PROC CONTENTS on the afflicted SAS data set. Could it be that your index file was deleted via the OS and that, when you attempted to repair your HUMONGO SAS data set, there was not enough room on the disk to rebuild the indexes and thus repair your SAS data set? That could account for the SAS data set continuing to be "damaged". Or, am I reading too much into your posting? Kevin, best of luck in all of your SAS endeavors! I hope that this suggestion proves helpful now, and in the future! Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or methodologies specified in this posting are for illustrative purposes only and no warranty is stated or implied as to their accuracy or applicability. People deciding to use information in this posting do so at their own risk. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Michael A. Raithel "The man who wrote the book on performance" E-mail: MichaelRaithel(a)westat.com Author: Tuning SAS Applications in the MVS Environment Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second Edition http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172 Author: The Complete Guide to SAS Indexes http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Of what use is freedom of speech to those who fear to offend? - Roger Ebert +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From: Kevin Myers on 5 Oct 2009 16:36 SAS would not allow the "damaged" data set to be read or repaired at all. Nothing that I tried worked. Everything resulted in an error message to the effect that "the index file was damaged", and it wouldn't let you to anything else at all. ----- Original Message ----- From: "Patrick" <patrick.matter(a)GMX.CH> To: <SAS-L(a)LISTSERV.UGA.EDU> Sent: Saturday, October 03, 2009 21:49 Subject: Re: Unable to Repair Large Data Set > Why couldn't you just re-create the index? > > Something like: > > data large; > do keyA=1 to 10000; > keyB=ceil(ranuni(0)*1000); > output; > end; > run; > proc sql; > create index keyAKeyB on large ( KeyA,Keyb); > create index KeyB on large ( Keyb); > quit; >
From: Joe Matise on 5 Oct 2009 10:59
Just a random thought, but did you check that OPTION DLDMGACTION (As referenced earlier) is set to force it to attempt to repair the dataset? -Joe On Mon, Oct 5, 2009 at 3:36 PM, Kevin Myers <KevinMyers(a)austin.rr.com>wrote: > SAS would not allow the "damaged" data set to be read or repaired at all. > Nothing that I tried worked. Everything resulted in an error message to > the > effect that "the index file was damaged", and it wouldn't let you to > anything else at all. > > ----- Original Message ----- > From: "Patrick" <patrick.matter(a)GMX.CH> > To: <SAS-L(a)LISTSERV.UGA.EDU> > Sent: Saturday, October 03, 2009 21:49 > Subject: Re: Unable to Repair Large Data Set > > > Why couldn't you just re-create the index? >> >> Something like: >> >> data large; >> do keyA=1 to 10000; >> keyB=ceil(ranuni(0)*1000); >> output; >> end; >> run; >> proc sql; >> create index keyAKeyB on large ( KeyA,Keyb); >> create index KeyB on large ( Keyb); >> quit; >> >> |