meta data for this page
  •  

IBExpert file system inventory tips and tricks

Have you ever tried to find how many large duplicate files are taking up space on your storage system?

We had the same idea and used some very easy IBExpert scripts to read all network files into a database. With about 8 million files on several NAS systems, the tools available in Microsoft Windows are not really helpful. Here you can find our solution, including sample code, using IBExperts IBEBlock scripting language and a Firebird database for storing the inventory.

First we created a table: 

 CREATE TABLE F (
     FN  VARCHAR(512) NOT NULL PRIMARY KEY,     --filename incl. path
     FS  BIGINT,                                --filesize in bytes
     FT  TIMESTAMP,                             --file timestamp
     P   VARCHAR(512),                          --path to file without filename
     N   VARCHAR(512)                           --filename without path
 );

Then we read all file names including path, timestamp and size into the table: 

execute ibeblock
as
begin
  ibec_progress('Searching for files...');
  files_count = ibec_getfiles(files_list, '\\ibexpertnas\data\', '*.*',__gfRecursiveSearch + __gfFullName);

  if (files_count > 0) then
  begin
    i = 0;
    while (i <= ibec_high(files_list)) do
    begin
     try
      file_name = files_list[i];

      file_size = ibec_filesize(file_name);          -- File size
      file_time = ibec_FileDateTime(file_name);
      n=ibec_ExtractFileName(file_name);
      p=ibec_ExtractFileDir(file_name);
      update or insert into F (FN, FS, FT,p,n) values (:File_name, :File_Size, :File_time,:p,:n) matching (fn);
     except
      ibec_Progress(i||' von '||files_count||': '||file_name||'    '||ibec_GetLastError());
     end
      i = i + 1;
      x=ibec_mod(i,1000);
      if (x=0) then
      begin
         commit;
         ibec_Progress(i||' von '||files_count);
      end
    end
    commit;
  end
end;

Then we moved all *.iso files from any other directory to the iso directory on the NAS: 

execute ibeblock
as
begin
 for
  select f.fn,f.n from f
where (f.fn like '%.iso'
    and f.fn not starting with '\\ibexpertnas\data\iso')
 into fn,n do
 begin
   if (ibec_MoveFile(fn,'\\ibexpertnas\data\iso\'||n)) then delete from f where f.fn=:fn;
   ibec_progress(fn);
 end
 commit;
end

To get a list of the largest files on your file system, including how much space they use, how often they are stored and in which directories they can be found: 

select f.n,sum(f.fs),count(*),list(f.p) from f
group by 1
order by 2 desc

The following file-handling functions are available in IBEBlock:

Function Description
ibec_DeleteFile Erases the file from the disk.
ibec_DirectoryExists Call ibec_DirectoryExists to determine whether the directory specified by the Name parameter exists.
ibec_FileExists Tests if a specified file exists.
ibec_FileSize Returns the size of the specified file.
ibec_GetFiles Retrieves specified file or list of files.
ibec_LoadFromFile Loads file data into variable.
ibec_SaveToFile Saves value of variable into file.
ibec_CopyFile Copies an existing file to a new one.
ibec_MoveFile Renames an existing file or a directory (including all its children).
ibec_FileDateTime Returns the TIMESTAMP of a specified file.
ibec_ExtractFileDir Extracts the drive and directory parts from FileName.
ibec_ExtractFileName Extracts the name and extension parts of a file name.
ibec_ForceDirectories Creates all the directories along a directory path if they do not already exist.

Important: IBEBlock is a set of DDL-Data Definition Language, DML-Data Manipulation Language and other statements that are executed on the server and on the client side, and which include some specific constructions applicable only in IBExpert or the IBExpert command-line tool IBEScript (excluding the free versions of these products), independent of the database server version.

Do you need our help? Here you can find our offers for hotline or onsite help.