How do I turn the following pseuodo-code into Watcom SQL?

IF a backup that will block a checkpoint is in progress THEN

...

END IF;

Here is the reason (from the V12 docs): a backup blocks checkpoints and any statement that causes a checkpoint, and a SQL module may decide not to proceed, or to take another path, rather than be blocked.


BTW the statement "Any statement that causes a checkpoint. This includes data definition statements and the LOAD TABLE and TRUNCATE TABLE statements." may not be entirely correct about CREATE TABLE and TRUNCATE TABLE. I have submitted a comment on the V11.0.1 DCX topic here.

asked 31 Oct '10, 12:30

Breck%20Carter's gravatar image

Breck Carter
25.8k428592853
accept rate: 20%

edited 31 Oct '10, 20:55

Volker%20Barth's gravatar image

Volker Barth
30.6k306456663


Starting with SQL Anywhere 16.0.1674, there's an easy way to answer that by checking the value of the following new database property:

SELECT DB_PROPERTY('BackupInProgress');

A new database property, BackupInProgress, has been added. Querying the property will return 'on' when there is a backup happening, and 'off' otherwise.

According to the cited CR entry, that property is not available in v12 and below.

permanent link

answered 08 Nov '13, 08:21

Volker%20Barth's gravatar image

Volker Barth
30.6k306456663
accept rate: 32%

edited 08 Nov '13, 09:15

...and the inevitable Kudos to Breck for listing all those new features in v16 EBFs/SPs on his blog:

(08 Nov '13, 11:38) Volker Barth

There's no solid way that I can see. I'd be concerned about the following logic too:

IF !(backup in progress) {
    // Drat, a backup could start right here and the following checkpoint will still wait
    checkpoint;
}

If there is a backup in progress and it uses WAIT AFTER END then an explicit CHECKPOINT by another connection will fail if it has uncommitted operations (otherwise the backup would wait forever for the other connection to commit but that connection is waiting forever for the backup to end so that it can do a checkpoint).

From the historical client API, you could attempt to start a backup. I think the attempt will fail if a backup is already in progress. I'm not sure if you could concoct a "do nothing" backup statement that would fail if another backup is in progress but succeed otherwise. I haven't checked but maybe the BACKUP statements just queue up rather than failing if another is in progress.

If you resort to any of these approaches, you didn't hear it from me :)

permanent link

answered 01 Nov '10, 21:08

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

Starting with V12, it seems that backups (both with BACKUP DATABASE and DBBACKUP) are done by a temporary connection (like same kind on event, methinks).

According to the V12 docs, the temporary connection is called "BackupDB", in my test cases with the name "INT: BackupDB".

However, I can't claim whether this connection is only active while the backup process is "active", i.e. while it doesn't wait on other connections to commit.

permanent link

answered 31 Oct '10, 20:52

Volker%20Barth's gravatar image

Volker Barth
30.6k306456663
accept rate: 32%

I think those temporary connections will only appear if we do a parallel backup and I don't think all backups are guaranteed to be parallel.

(01 Nov '10, 20:45) John Smirnios

I just tested with only a simple full backup of a small database (about 15 MB) on a notebook, just default options, so I don't expect a high degree of parallelization here. - It went so quick (just a few seconds) so it was quite hard to check the temporary connection with sa_conn_info:)

(01 Nov '10, 21:20) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×72
×8

question asked: 31 Oct '10, 12:30

question was seen: 1,614 times

last updated: 08 Nov '13, 11:38