Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Binaries » View Thread |
Messages 1 to 5 of 5 total |
Procedures to assist reviewing replication status of an EDB database |
Mon, Jul 3 2017 6:28 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
Very nice, thank you. I should probably put something like this in the EDB Manager as a task. Tim Young Elevate Software www.elevatesoft.com |
Mon, Jul 3 2017 12:02 PM | Permanent 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 PM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Friday, September 20, 2024 at 05:39 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |