Changing global_database_id takes forever

[former_SQLA_member1694875]

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.

[VolkerBarth]

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?

[former_SQLA_member1694875]

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.

[former_SQLA_member1694875]

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.

[VolkerBarth]

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.

[former_SQLA_member1694875]

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

[VolkerBarth]

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?

[former_SQLA_member1694875]

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.