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:

FunctionDescription
ibec_DeleteFileErases the file from the disk.
ibec_DirectoryExistsCall ibec_DirectoryExists to determine whether the directory specified by the Name parameter exists.
ibec_FileExistsTests if a specified file exists.
ibec_FileSizeReturns the size of the specified file.
ibec_GetFilesRetrieves specified file or list of files.
ibec_LoadFromFileLoads file data into variable.
ibec_SaveToFileSaves value of variable into file.
ibec_CopyFileCopies an existing file to a new one.
ibec_MoveFileRenames an existing file or a directory (including all its children).
ibec_FileDateTimeReturns the TIMESTAMP of a specified file.
ibec_ExtractFileDirExtracts the drive and directory parts from FileName.
ibec_ExtractFileNameExtracts the name and extension parts of a file name.
ibec_ForceDirectoriesCreates 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.

back to top of page
<< Change all triggers | IBEBlock | Automatic script execution >>