What about having a functionality in edbmanager for verify
all tables and do a repair only in those where problems found.

At the moment i have to right click to each table to do this.

Yusuf Zorlu
MicrotronX

Yusuf,

I agree that this functionality would be useful, I have rolled my own. It doesn't do exactly what you say, but it is close, it is simple and can be extended:

CREATE PROCEDURE "RepairAllTables" ()
BEGIN
  DECLARE Crsr Cursor FOR Stmt;
  DECLARE TableName VARCHAR(120);

PREPARE Stmt FROM
' SELECT
    Name
  FROM Information."Tables" ';
OPEN Crsr;
FETCH FIRST FROM Crsr ('Name') INTO TableName;
WHILE NOT EOF(Crsr) DO
  IF NOT (TableName IS NULL) OR NOT (TableName='') THEN
     --iterate the file-list and copy / rename the tables.
     EXECUTE IMMEDIATE
       ' REPAIR TABLE "' + TableName + '" ';
     EXECUTE IMMEDIATE
       ' OPTIMIZE TABLE "' + TableName + '" ';
     END IF;
  FETCH NEXT FROM Crsr ('Name') INTO TableName;
  END WHILE;
CLOSE Crsr;

END

Adam


>   DECLARE TableName VARCHAR(120);

Don't you get fed up of typing table names that long?

Roy Lambert

ps thanks for the procedure - trying it right now.

Adam


Suggestion for your procedure - delete the .old files if repair / optimise are successful

Roy Lambert

Thanks Adam, will try your code

Yusuf Zorlu
MicrotronX

Yusuf,

Here's a more complete version (no offense, Adam) that I use specifically for verification of entire databases:

SCRIPT
BEGIN
  DECLARE CatalogCursor SENSITIVE CURSOR FOR CatalogStatement;
  DECLARE TableName VARCHAR DEFAULT '';
  DECLARE CurrentTable INTEGER DEFAULT 1;
  DECLARE VerifyStmt STATEMENT;
  
  PREPARE CatalogStatement FROM 'SELECT * FROM Information.Tables';
  OPEN CatalogCursor;

  SET PROGRESS TO 0;
  SET LOG MESSAGE TO '*** Verification of database '+CURRENT_DATABASE()+
                     ' started on '+CAST(CURRENT_TIMESTAMP() AS VARCHAR);

  FETCH FIRST FROM CatalogCursor ('Name') INTO TableName;

  WHILE NOT EOF(CatalogCursor) DO

     SET STATUS MESSAGE TO 'Verifying table '+TableName;

     PREPARE VerifyStmt FROM 'VERIFY TABLE '+QUOTEDSTR(TableName,'"');
     EXECUTE VerifyStmt;
     
     IF STMTRESULT(VerifyStmt) THEN
        SET LOG MESSAGE TO 'Error while verifying '+TableName;
     END IF;

     FETCH NEXT FROM CatalogCursor ('Name') INTO TableName;

     SET PROGRESS TO TRUNC((CurrentTable/ROWCOUNT(CatalogCursor))*100);

     SET CurrentTable=CurrentTable+1;

  END WHILE;

  SET LOG MESSAGE TO '*** Verification of database '+CURRENT_DATABASE()+
                     ' completed on '+CAST(CURRENT_TIMESTAMP() AS VARCHAR);

END

Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim.

Yusuf Zorlu
MicrotronX