Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Procedures to assist reviewing replication status of an EDB database
Mon, Jul 3 2017 6:28 AMPermanent Link

Adam Brett

Orixa Systems

Elevate includes  powerful Replication features, allowing multiple instances of a database to be installed on servers and PCs. These instances can then be synchronized according to administrators requirements using a wide range of synchronization schema. The system is extremely flexible, allowing all or parts of the database to be synchronized, and allowing many options for whether there is one "master" database, or databases are linked together in a chain.

This complexity can make it hard to know the "state" of a machine.
* Is this machine "fully up to date" with the main server?
* Are there updates waiting on this machine which have not yet been applied?

Updates can be waiting in Update Files in Stores on the computer, or can be changes which have been made within a database that save not yet been "saved away" into an Update File.

In normal operation users do not need to know the update-state of a system. However it can be useful to see the update-state to get a quick snap-shot of Records a user has added / edited on their machine which have not yet been updated to other machines.

Also, if a system is being changed and the database is updated it is essential that all machines are synchronized prior to any database changes. At these times it is useful to have a mechanism to view the "update state" of a system.

With Build 2.26 EDB has extended the ability to create temporary tables which display the contents of update-logs so that it is now possible to view the contents of updates that have already been saved to Update Files AND to see the contents of updates that are still "live" in the database.

The following Stored Procedures create temporary tables which allow administrators to see a simplified summary of the "state" of both "Saved Updates" and "Live Updates" on a machine.

It is not much code, but I share it as I think it might be useful for others. Note that once you have created the procedures to view the state of a database you need to

CALL CreatePublishedUpdates();
CALL CreateSavedUpdates();

And then

SELECT * FROM PublishedUpdatesView

SELECT * FROM SavedUpdatesView

Will actually give simple lists of updates.

NOTES:

My Procedures assume:

All primary-keys are formed of single fields with field-name "ID". There are variables in the procedures you can play with if you use different primary-key field-name conventions.

On any system all "Uploads" are stored in a store called "Uploads" and all "Downloads" are stored in a store called "Downloads" ... again you can rewrite the procedures if your system uses different methodologies.

--CREATE SQL STARTS

CREATE PROCEDURE "CreatePublishedUpdates" ()
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;                               
 DECLARE KeyFieldName VARCHAR(100) DEFAULT 'ID';     
 DECLARE KeyFieldLength INTEGER;             
 DECLARE KeyFieldLengthStr VARCHAR(100);
SET KeyFieldLength = LENGTH(KeyFieldName) + 3;
SET KeyFieldLengthStr = CAST(KeyFieldLength as VARCHAR);

PREPARE Stmt FROM
' SELECT Name
  FROM Information.TemporaryTables
  WHERE Name = ''PublishedUpdates'' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
 EXECUTE IMMEDIATE
 ' DROP TABLE PublishedUpdates ';
 END IF;
CLOSE Crsr;

EXECUTE IMMEDIATE
' CREATE TEMPORARY TABLE PublishedUpdates
  FROM PUBLISHED UPDATES ';

PREPARE Stmt FROM
' SELECT Name
  FROM Information.TemporaryTables
  WHERE Name = ''PublishedUpdatesView'' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
 EXECUTE IMMEDIATE
 ' DROP TABLE PublishedUpdatesView ';
 END IF;
CLOSE Crsr;

EXECUTE IMMEDIATE
 ' CREATE TEMPORARY TABLE PublishedUpdatesView
   ("TableName" VARCHAR(250),
    "UpdateType" VARCHAR(20),
    "DateDone" CLOB,
    "IDs" CLOB,
    "ChangeCount" INTEGER ) ';

EXECUTE IMMEDIATE
' INSERT INTO PublishedUpdatesView
  SELECT
    TableName,
    UpdateType,
    REPLACE('','' WITH '', '' IN LIST(DISTINCT ORDERED CAST(DateDone as VARCHAR(10)))) as DateDone,
    REPLACE('','' WITH '', '' IN LIST(DISTINCT ORDERED ID)) as IDs,
    COUNT(DISTINCT ID) as "ChangeCount"
  FROM
  (SELECT
    TableName, UpdateType,
    CAST(UpdateTimeStamp as DATE) as DateDone,
    IF(UpdateType = ''Insert'' THEN
       SUBSTRING(CAST(RowData AS VARCHAR(100)) FROM ' + KeyFieldLengthStr + '
                 FOR POSITION(#13 IN RowData) - ' + KeyFieldLengthStr + ')
       ELSE
       REPLACE(#10 WITH '''' IN REPLACE('' '' WITH '''' IN
       REPLACE(#13 WITH '''' IN REPLACE(''' + KeyFieldName + ':'' WITH '''' IN
          CAST(KeyData as VARCHAR(50))))))
    ) AS ID
  FROM PublishedUpdates PU
  LEFT JOIN Information.Tables T ON (T.Name = PU.TableName AND T.PublishID = PU.Manifest)
  WHERE IF(T.Name is NULL THEN false else true) = true ) as Upd
  GROUP BY TableName, UpdateType ';
END
VERSION 1.00!

CREATE PROCEDURE "CreateSavedUpdates" ()
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE aFileName VARCHAR;
 DECLARE aStoreName VARCHAR;
 DECLARE KeyFieldName VARCHAR(100) DEFAULT 'ID';
 DECLARE KeyFieldLength INTEGER;             
 DECLARE KeyFieldLengthStr VARCHAR(100);
SET KeyFieldLength = LENGTH(KeyFieldName) + 3;
SET KeyFieldLengthStr = CAST(KeyFieldLength as VARCHAR);

PREPARE Stmt FROM
' SELECT Name
  FROM Information.TemporaryTables
  WHERE Name = ''SaveUpd'' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
 EXECUTE IMMEDIATE
 ' DROP TABLE SaveUpd ';
 END IF;
CLOSE Crsr;

PREPARE Stmt FROM
' SELECT Name
  FROM Information.TemporaryTables
  WHERE Name = ''SavedUpdatesView'' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
 EXECUTE IMMEDIATE
 ' DROP TABLE SavedUpdatesView ';
 END IF;
CLOSE Crsr;

EXECUTE IMMEDIATE
 ' CREATE TEMPORARY TABLE SavedUpdatesView
   ("StoreName" VARCHAR(30),
    "FileName" VARCHAR(250),
    "TableName" VARCHAR(250),
    "UpdateType" VARCHAR(20),
    "DateDone" CLOB,
    "IDs" CLOB,
    "ChangeCount" INTEGER ) ';

EXECUTE IMMEDIATE
' SET Files Store TO "Uploads"';

PREPARE Stmt FROM
' SELECT REPLACE(''.EDBUpd'' WITH '''' IN Name) as FileName
 FROM Configuration.Files
 WHERE UPPER(Name) LIKE ''%.EDBUPD%'' ';
OPEN Crsr;
FETCH FIRST FROM Crsr('FileName') INTO aFileName;
  WHILE NOT EOF(Crsr) DO
     EXECUTE IMMEDIATE
      ' CREATE TEMPORARY TABLE SaveUpd
        FROM Updates "' + aFileName + '" IN STORE "Uploads"';   
     EXECUTE IMMEDIATE
      ' INSERT INTO SavedUpdatesView
        (StoreName, FileName, TableName, UpdateType,
         DateDone, IDs, ChangeCount)
       SELECT
         ''Uploads'' as StoreName,
         ''' + aFileName + ''' as FileName,
         TableName, UpdateType,
         REPLACE('','' WITH '', '' IN LIST(DISTINCT ORDERED CAST(DateDone as VARCHAR(10)))) as DateDone,
         REPLACE('','' WITH '', '' IN LIST(DISTINCT ORDERED ID)) as IDs,
         COUNT(DISTINCT ID) as "ChangeCount"
       FROM
       (SELECT
          TableName, UpdateType,
          CAST(UpdateTimeStamp as DATE) as DateDone,
          IF(UpdateType = ''Insert'' THEN
             SUBSTRING(CAST(RowData AS VARCHAR(100)) FROM ' + KeyFieldLengthStr + '
                       FOR POSITION(#13 IN RowData) - ' + KeyFieldLengthStr + ')
             ELSE
             REPLACE(#10 WITH '''' IN REPLACE('' '' WITH '''' IN
             REPLACE(#13 WITH '''' IN REPLACE(''' + KeyFieldName + ':'' WITH '''' IN
                CAST(KeyData as VARCHAR(50))))))
          ) AS ID
              FROM SaveUpd SU
        LEFT JOIN Information.Tables T ON (T.Name = SU.TableName AND T.PublishID = SU.Manifest)
        ) as TempTable
        GROUP BY TableName, UpdateType ';                       
     EXECUTE IMMEDIATE
      ' DROP TABLE SaveUpd ';                                   
    FETCH NEXT FROM Crsr('FileName') INTO aFileName;
  END WHILE;
CLOSE Crsr;

EXECUTE IMMEDIATE
' SET Files Store TO "Downloads"';

PREPARE Stmt FROM
' SELECT REPLACE(''.EDBUpd'' WITH '''' IN Name) as FileName
 FROM Configuration.Files
 WHERE UPPER(Name) LIKE ''%.EDBUPD%'' ';
OPEN Crsr;
FETCH FIRST FROM Crsr('FileName') INTO aFileName;
  WHILE NOT EOF(Crsr) DO
     EXECUTE IMMEDIATE
      ' CREATE TEMPORARY TABLE SaveUpd
        FROM Updates "' + aFileName + '" IN STORE "Downloads"';
     EXECUTE IMMEDIATE
      ' INSERT INTO SavedUpdatesView
        (StoreName, FileName, TableName, UpdateType,
         DateDone, IDs, ChangeCount)
       SELECT
         ''Downloads'' as StoreName,
         ''' + aFileName + ''' as FileName,
         TableName, UpdateType,
         REPLACE('','' WITH '', '' IN LIST(DISTINCT ORDERED CAST(DateDone as VARCHAR(10)))) as DateDone,
         REPLACE('','' WITH '', '' IN LIST(DISTINCT ORDERED ID)) as IDs,
         COUNT(DISTINCT ID) as "ChangeCount"
       FROM
       (SELECT
          TableName, UpdateType,
          CAST(UpdateTimeStamp as DATE) as DateDone,
          IF(UpdateType = ''Insert'' THEN
             SUBSTRING(CAST(RowData AS VARCHAR(100)) FROM 5 FOR POSITION(#13 IN RowData) - 5)
             ELSE
             REPLACE(#10 WITH '''' IN REPLACE('' '' WITH '''' IN
             REPLACE(#13 WITH '''' IN REPLACE(''ID:'' WITH '''' IN
                CAST(KeyData as VARCHAR(50))))))
          ) AS ID
        FROM SaveUpd SU
        LEFT JOIN Information.Tables T ON (T.Name = SU.TableName AND T.PublishID = SU.Manifest)
        ) as TempTable
        GROUP BY TableName, UpdateType ';                       
     EXECUTE IMMEDIATE
      ' DROP TABLE SaveUpd ';                                   
    FETCH NEXT FROM Crsr('FileName') INTO aFileName;
  END WHILE;
CLOSE Crsr;
END
VERSION 1.00!








.
Mon, Jul 3 2017 11:16 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

Very nice, thank you.

I should probably put something like this in the EDB Manager as a task. Smile

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Jul 3 2017 12:02 PMPermanent Link

Adam Brett

Orixa Systems

Yes Tim ... that would be great. I am already surprised how useful it is, just seeing all the "touched" records in a users session is helpful, the published updates become a record of user-activity you can return without the need to add anything extra to the DB .
Mon, Jul 3 2017 1:09 PMPermanent Link

Adam Brett

Orixa Systems

I also find these useful:

CREATE VIEW "ServerStatisticsUserSummary" AS
SELECT
 Process as User,
 IF(EXTRACT(Hour FROM LastConnected) > 9 THEN '' ELSE '0')
 + CAST(EXTRACT(Hour FROM LastConnected) AS VARCHAR(10)) + ':' +
 IF(EXTRACT(Minute FROM LastConnected) > 9 THEN '' ELSE '0')
 + CAST(EXTRACT(Minute FROM LastConnected) AS VARCHAR(10)) as Connected,
 SUM(SSS.CurrentBufferSize) as MemoryUse,
 SUM(Writes) as Writes
FROM Configuration.ServerSessions SS
LEFT JOIN Configuration.ServerSessionStatistics SSS ON SS.ID = SSS.SessionID
GROUP BY Process
HAVING MemoryUse > 0
ORDER BY MemoryUse DESC
VERSION 1.00!

CREATE VIEW "ServerStatisticsTableSummary" AS
SELECT
 SSS.TableName,
 SUM(SSS.CurrentBufferSize) as MemoryUse,
 SUM(Writes) as Writes
FROM Configuration.ServerSessionStatistics SSS
GROUP BY TableName
HAVING MemoryUse > 0
ORDER BY MemoryUse DESC
VERSION 1.00!

CREATE VIEW "ServerStatisticsSummary" AS
SELECT
 SUM(SSS.CurrentBufferSize) as MemoryUse,
 SUM(Writes) as Writes
FROM Configuration.ServerSessionStatistics SSS
HAVING MemoryUse > 0
VERSION 1.00

CREATE VIEW "WhoIsConnected" AS
SELECT
 ID, Process + ' (' + IPAddress + ')' as Who,
 CAST(LastConnected as Time) as When
FROM Configuration.ServerSessions
WHERE Connected = true
ORDER BY "Process"
VERSION 1.00

Showing the "Live state" of the database.

I am sure you could do better, but they give a useful picture of who is connected ... together they make a nice dashboard.
Wed, Jul 5 2017 10:49 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< Showing the "Live state" of the database.

I am sure you could do better, but they give a useful picture of who is connected ... together they make a nice dashboard. >>

Beautiful, thanks.

The thing that I need to improve is the concurrency of getting lock stats, etc. for sessions.  It's too easy to run into a "session is locked" error when trying to query some of the Session* tables.  I've been reluctant to make a lot of the session information more accessible because of the session locking problems associated with constantly viewing this information.

Tim Young
Elevate Software
www.elevatesoft.com
Image