Hi,

Our customer tried to increase global_database_id option (in test DB for now), but it took more than 7 hours with no end until they restarted the server by force. After that I see that the global_database_id value has actually changed. But I guess that something could go wrong. So there are the questions:

  1. Are there any tricks to quickly change global_database_id, i. e. avoid such a huge hanging?
  2. What should be checked (if that occurs in production) to see if there is anything to fix after such unmanaged restart?
  3. Maybe someone could list all the actions that are being performed when executing this statement (to understand what is really happening and what might not be finished):

    SET OPTION public.global_database_id = '4';

One of ~350 tables with global autoincrement(50000000) columns has almost reached the top of the range.
The database size is >200 GB.
SQL Anywhere version: 11.0.1.3158.

asked 18 Oct '16, 04:42

Arthoor's gravatar image

Arthoor
1.1k264056
accept rate: 0%

edited 18 Oct '16, 04:44

That's puzzling. AFAIK, that setting should take place immediately. I would usually add an explicit CHECKPOINT afterwards, cf. that question.

Have you checked whether a connection was blocked?

Do you use an event of type "GlobalAutoincrement" with event_condition('RemainingValues') < x to reserve the next ID range?

(18 Oct '16, 05:55) Volker Barth
Replies hidden

I will answer about connection blocking later.
An event is used only to inform administrator about remaining identities. The actual change was being done manually using dbisql.

(18 Oct '16, 06:14) Arthoor

Now I've tried it myself. A few attempts were lucky, I've changed global_database_id back and forth several times with almost immediate response... But now again it is freezed. It blocked all other connections, i. e. I can't connect though another instance of Sybase Central. DB output log has zero info about that.

(18 Oct '16, 06:33) Arthoor

What are other connections doing at the time you are executing the SET OPTION statement? (I would leave a DBISQL connection open to query for sa_conm_info() and sa_locks() during the tests.)

In my tests with v16.0.0.2270 and several connections inserting continuously (within an open transaction) into a table with default global autoincrement, no blocking/locking happens when the option is changed, and it has immediate effect. (Of course, it does an automatic commit.)

However, the change in systabcol.max_identity is only reflected once a checkpoint is made.

(18 Oct '16, 07:26) Volker Barth

I had left another dbisql instance opened to execute sa_conn_info() but I could not execute it when the DB hanged.

(18 Oct '16, 07:57) Arthoor

Hm, sorry, then I'm out of my wits...:(

That being said, that question is still open:

What are other connections doing at the time you are executing the SET OPTION statement?

(18 Oct '16, 08:05) Volker Barth

As the reason stays undiscovered, we've just changed column types from global autoincrement(...) to autoincrement. Replication had been used many years ago but is very unlikely to be used again.

(19 Oct '16, 03:46) Arthoor
showing 2 of 7 show all flat view
Be the first one to answer this question!
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:

×137
×5
×1

question asked: 18 Oct '16, 04:42

question was seen: 146 times

last updated: 19 Oct '16, 03:46