If you have a replication-based  EDB database

It is possible to "CREATE TABLE FROM UPDATES" This basically posts all the saved "PBL" data in the database into a useful format to analyse what is going on with the update.

However, you have to do it for the whole database, and with more complex systems the resulting tables can be quite big.

It would be great to add the ability to query the update status of individual tables.

I am imagining a situation where in EDB Manager on right click / in the task-pane there was a "View Pending Updates" option, clicking this could open a list of the updates for that individual table.

If this was done through the metaphor of a PendingUpdates system-table, the following SQL could be run:

SELECT * FROM Information.PendingUpdates WHERE TableName = 'SomeTable'

The replication features in EDB are really powerful, but they remain a bit complex, I think this feature would help to make them more comprehensible to users.

On 6/1/2017 7:05 AM, Adam Brett wrote:
> However, you have to do it for the whole database, and with more complex systems the resulting tables can be quite big.
> ...
> I am imagining a situation where in EDB Manager on right click / in the task-pane there was a "View Pending Updates" option, clicking this could open a list of the updates for that individual table.

Assuming you ran SAVE UPDATES using a table name each update would be
for a single table. One now has to deal with per table save updates and
lot more update files - however might be useful workaround for maybe one
or few key tables.

Raul

Raul

Thanks for this that is a really useful idea.

Obviously in a relational database it would be impossible to use the resulting update files to update the database as inserts with missing foreign keys would not apply. So it would be necessary to backup and restore the db to a test location first, which is possible a bit cumbersome.

I have a couple of procedures I run which save updates, create  temporary pending updates table and then create some summary views so I can analyse the update situation table by table when I need to. It would jut be great if this was built into the product.

If any would like the SQL for these procedures let me know and I will post them.

Adam,

<< However, you have to do it for the whole database, and with more complex systems the resulting tables can be quite big. >>

How big is big here ?  Let's start with row counts, for example.  If the amount of pending updates is particularly large, then my first recommendation would be to perform SAVE UPDATES operations more often.  It will cut down on transmission time/size, as well as the time required to perform the SAVE UPDATES.  As a side bonus, it will also fix this issue.

<< If this was done through the metaphor of a PendingUpdates system-table, the following SQL could be run:

SELECT * FROM Information.PendingUpdates WHERE TableName = 'SomeTable' >>

The system information tables are out - they're in-memory, so it's too easy to blow up the memory consumption by using that.  Especially considering that EDB isn't going to necessarily be able to push the WHERE conditions down into the selection of the appropriate records.  This means that EDB is going to need to do what it's doing now, but *in-memory*.  As you can imagine, that's not going to work, per the above.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>>How big is big here ?  Let's start with row counts, for example.  

I am not talking about vast numbers, just numbers that are big enough to be hard for a human to look through.

A large update might be 5k or 10k rows.

I ask users to "save updates" regularly, but don't enforce it with automated JOBs, as I have had users close laptops part-way through JOBs running with bad results.

--

I guess this discussion throws up an even slightly more complex issue Tim:

Users want databases so that they can know stuff about their businesses.
Replication databases enable fantastic inter-linkages of different business units.
But replication means that often data is spread around, between different database instances, PBL files within an instance and .EDBUpd that have already been saved.

With a replication-based system customers start asking "yes but where is the data about xxx right now" (for example if an auditor turns up unannounced and you have to immediately run a tracability exercise).

At that point they are never very happy if you say, well the data is "travelling through the ether" ... i.e. in transit in some replication process.

This is in no way a criticism of EDB, it is an inherent part of replication.

Additions to EDB which increase the transparency of whether the data that is in the database is "fully in" or "just added but not yet fully updated across the whole system", systems which allow users to see data which is "in transit" would all increase the power of the product.

Adam,

<< Additions to EDB which increase the transparency of whether the data that is in the database is "fully in" or "just added but not yet fully updated across the whole system", systems which allow users to see data which is "in transit" would all increase the power of the product.  >>

I understand what you're saying, but I'm not quite sure what you're asking for here.  You can view the updates in an update file via the CREATE [TEMPORARY] TABLE syntax, so that information is available.  Are you asking to see what's in the .edbpbl files ?  If so, then that information is also viewable, you just simply need to use SAVE UPDATES and then CREATE [TEMPORARY] TABLE to view it.  Also, the information that is in the .edbpbl isn't *only* present there, it's also in the actual table.  The contents of the .edbpbl file are simply what's going to be replicated and not something distinct from the contents of the actual table.  But, more importantly, this is all very conditional on the usage of the SAVE UPDATES statement itself.  In other words, just looking at the .edbpbl contents is not sufficient.  An installation could literally publish a table and *never* issue a SAVE UPDATES statement. In such a case, the contents of the .edbpbl file are irrelevant.  It's only after the SAVE UPDATES statement is executed *and* the update file is transmitted and loaded by another installation/database that the updates materially affect the contents of the other installation/database.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Yes. And this complexity can make it hard to grasp "where" data is, which makes diagnosis of issues and problems hard, which in turn makes implementations of solutions hard.

Having multiple instances of databases is complex enough, then you have multiple streams of EDBUpd files coming from each instance at that point "where" data is becomes very hard to be sure of, i.e., whether there has already been a "SAVE UPDATES" with the resulting file copied up to a server, whether an update file has been created, but is just on the local machine, whether it is actually still in a PBL file "within" the current database are initially unknowns.

Once you start to diagnose a system you can get to the bottom of the issue, but running the diagnosis does take time. I am trying to think of ways to make the process easier, as it would make adoption of the product easier (and make my life easier!).

Any of the following would be useful:

"The PBL file for table XXX has records from date-time YYY", then I know the length of time since a save update process has run.

... any other stats that can be gleaned from the PBL (for example the number of INSERTS, or the number of updates which originate from the current machine, and therefore have a blank manifest) are all useful to know.

Adam,

<< "The PBL file for table XXX has records from date-time YYY", then I know the length of time since a save update process has run. >>

Okay, that's a little more specific, and yes, something like that could be done via the system information tables.

<< ... any other stats that can be gleaned from the PBL (for example the number of INSERTS, or the number of updates which originate from the current machine, and therefore have a blank manifest) are all useful to know.
>>

That's a little more difficult, and would need to involve dumping the .pbl file first, so I need to refer back to my other reply.  EDB needs to avoid holding locks on the .pbl file as much as possible, so the only way to analyze the .pbl is to dump it to an external file.  IOW, execute a SAVE UPDATES statement.  So, if you need this type of analysis, then that is your answer.

Is there any particular reason that you don't want to issue a SAVE UPDATES statement in certain circumstances like this ?  There's no downside to doing so, especially if you include the IF NOT EMPTY clause.

I *could* add a clause to the SAVE UPDATES statement that doesn't clear out the existing published updates, but that can be fairly dangerous if not used carefully, so I'm a little hesitant to do so.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I agree that a "save updates" that doesn't reset the PBLs would be horribly dangerous.

You are right it is always possible to Save Updates and then review the resulting update file to check on system operation. Sometimes with a live system this can be a bit tricky, as a system may well be being used by multiple users and it feels better not to have to jump in and override the normal operation of the system while debugging or fixing.

I will have a bit of a think about this myself, and perhaps try to code something useful given how the system works at the moment.

We have the ability to call

SET UPDATE STORE TO "MyUpdates"!

SELECT * FROM Configuration.Updates

This already does give useful data (NumTables, Size, Tables, CreatedOn) I will think about surfacing this first, as it is really helpful.

Adam,

<< I agree that a "save updates" that doesn't reset the PBLs would be horribly dangerous.

You are right it is always possible to Save Updates and then review the resulting update file to check on system operation. Sometimes with a live system this can be a bit tricky, as a system may well be being used by multiple users and it feels better not to have to jump in and override the normal operation of the system while debugging or fixing. >>

Well, no worries - you convinced me and I implemented it for 2.26.  There's now this version of CREATE TABLE:

CREATE TEMPORARY TABLE PublishedUpdates
FROM PUBLISHED UPDATES

or, if you only want a few tables:

CREATE TEMPORARY TABLE PublishedUpdates
FROM PUBLISHED UPDATES TABLES Customers, Products

Of course, you don't want to be running this constantly because it does need to use locking to read the published updates from the database tables, but it is very useful for diagnosing any issues and doesn't involve any dangerous stuff with the SAVE UPDATES.

I also improved the performance of the SAVE UPDATES statement a bit.  It wasn't using buffering as well as it could when writing out the updates, so that's been corrected.

Tim Young
Elevate Software
www.elevatesoft.com