Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Ability to merge UPDATE files for a Db
Tue, Sep 17 2013 6:38 AMPermanent Link

Adam Brett

Orixa Systems

No idea whether this is possible. But it would be nice.

In a system using replication with multiple users many Update files are generated. It is sometimes useful to be able to reapply these updates, for example when restoring a backup of a database.

1. User backs up database.
2. Update files are applied to database from multiple users.
3. Database is corrupted.

My systems generate a lot of update files (30 users x 1 update per hour ... ) so most of my customers delete the OLD versions of the update files just on a house-keeping basis.

It would be great to be able to "Add" update files.

i.e.

SET FILES STORE TO Updates

ADD "User1Update-2013-09-17" TO "UpdateBackUp-2013"

COPY FILE "UpdateBackUp-2013" IN STORE Updates to "UpdateBackUp-2013" IN STORE UpdateBackups

In this way you end up with a main database backup (perhaps 1 gig or more) and a small number of smaller cumulative backup files which can be stored away somewhere the customer doesn't notice, but which give a really useful audit trail and improve "restorability"

I partly suggest this as calling BACKUP on a really big DB takes a long time, and you end up with lots of big files if you do it repeatedly.

The above suggestion is quicker and uses less space.
Tue, Sep 17 2013 6:52 AMPermanent Link

Uli Becker

Adam,

+1 for this one. That's exactly what I was planning to ask. Smile

In my case I have update files created every 15 minutes and it would be
nice to have ONE file instead of 200 in the evening.

Uli
Tue, Sep 17 2013 12:52 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< No idea whether this is possible. But it would be nice. >>

Yes to both. Smile

I'll see what I can do.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com
Sun, Sep 22 2013 6:02 AMPermanent Link

Adam Brett

Orixa Systems

Tim

Having thought about this a bit more, I realise that the best way to work it might be to alter the SAVE UPDATES statement rather than adding an "ADD TO UPDATES"

Current Syntax:

SAVE UPDATES FOR DATABASE <DatabaseName>
AS <UpdateName> TO STORE <StoreName>
[TABLES <TableName> [,<TableName>]]
[DESCRIPTION <Description>]
[COMPRESSION <Compression>]
[IF NOT EMPTY]


New Syntax could be:

REPLACE "AS" in the above with

AS | MERGE INTO

i.e. I could then write:

SAVE UPDATES FOR MyDatabase MERGE INTO MyUpdateFile

If MyUpdateFile does not exist (run for the first time, or after an APPLY UPDATE the file is created, otherwise the updates are added/merged with the existing file.

Such a syntax would make a lot of things easier, i.e. I have to copy multiple UPDATE files from local machine to server at present & then rename them all. This syntax would simplify that process down to a single file.
Mon, Sep 23 2013 2:13 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< New Syntax could be:

REPLACE "AS" in the above with

AS | MERGE INTO  >>

Great idea - saves a lot of effort on my part since it's much easier to add
to an existing statement than to create a whole new statement.

However, thinking more about this - you're going to have to be very careful
about these types of update files in order to avoid sending over duplicates
of updates.  IOW, you need to make sure that you aren't appending to an
update file that has already been copied/sent elsewhere.  I'm not opposed to
doing the improvement, just letting you know that it could be problematic.
Smile

Tim Young
Elevate Software
www.elevatesoft.com
Sat, Sep 28 2013 8:28 AMPermanent Link

Adam Brett

Orixa Systems

>>However, thinking more about this - you're going to have to be very careful
>>about these types of update files in order to avoid sending over duplicates
>>of updates.  IOW, you need to make sure that you aren't appending to an
>>update file that has already been copied/sent elsewhere.  I'm not opposed to
>>doing the improvement, just letting you know that it could be problematic.
>>Smile

Yup, I agree this is a serious issue. If you create a massive update file with a bunch of updates in it & then copy it up to the server before the server has applied a previous set with the same name ... bam it will overwrite and you'll lose the older updates.

There are ways to deal with this as a problem such as renaming the file if duplicate exists on server, but at present this feature isn't built in to EDB's COPY FILE ... and I can understand why. It could result in a duplication of identical files which would cause issues with updates!

On balance it is still a really useful feature. Particularly for archiving updates. But perhaps there should be a separate "MERGE UPDATE FILE xxx in STORE yyy WITH zzz in store yyy" ... I realise this would be more work for you to introduce.
Image