Global Auto Increment : Add to existing table

[Former Member]

If I do this in passthrough mode to all remotes

alter table ABC add unique_cnt BIGINT not null default GLOBAL AUTOINCREMENT(1000000);

will the database automatically update all the existing records in the database for each remote. What happens if the table is locked by the user when the statement hits via the replciation system.

How does the partition get set for new remote databases. I use a custom extraction routine that copies a "template" db . Then scripts are run on this database to configure the remote user and publications for the database.

I will be using a system that has 99% version latest 12 with the other 1% being version 8.02.4258

[Former Member]

Actually the table I am trying to get the global autoincrement on is not part of any publications thus there will be no identical rows in any of the remotes or consolidated for this table..

This table will conatin this unique global autoincrement value as a mechanism for me to place a key value to link multiple tables that are part of the replciation system. i.e. I need to insert data into a set of replciating tables at different sections / functions of the application. I will than need to have some key to run joins on these different tables. (My global autoincrement)

Yes this is SQL Remote... I will look into stting up the global database ID... I hope I will be able to do this via passthrough to each of my 1000+ remotes.

Thanks for the help...

[VolkerBarth]

Well, that's a different thing then - it would have been fine if the "this table is not replicated" information had been already claimed in the original posting...

[Former Member]

Sorry for the oversite.. I was just trying to understand if I could add the global auto increment and get the values set to existing rows via passthrough mode.
My brain was just keyed in at the specific item, but I can easily see how my original posting triggered the responces.

[VolkerBarth]

I see - the personal PASSTHROUGH mode - and I surely do happen to forget the PASSTROUGH STOP statement:)

[VolkerBarth]

As Bill has explained, the DEFAULT VALUE will be set during the ALTER TABLE - which means that any Remote will fill the rows it contains with values from its own GlobalDbId range. In case several remotes (or the consolidated) will contain the same rows (which would be very typical), then different remotes would set different default values to the same row. And that would not be resolved by replication. In my book, that's a no-go.

In such cases (adding NOT NULL columns to non-empty tables) we have used a different approach that will only set the default value for each row at only one site - assuring that data remains consistent.

1. Use Passthrough mode (PM) to alter the table with a NULLable column. Stop PM.

2. Update the row in the consolidated with the according default value (i.e. by selecting get_identity() or the like), something like
   Update ABC set unique_cnt = ... order by MyPk;
   As this is done outside PM, the update will be replicated.

3. Use PM (only) to issue an update that only affects the rows in the remotes that have not already been updated in the previous steps (usually rows that have been inserted in the rows and are not yet replicated to the consolidated): Update ABC set unique_cnt = ... where unique_cnt is null order by MyPk;

4. Stop PM;
5. Use PM to change the column to have its DEFAULT VALUE and set it to NOT NULL. Stop PM.

I guess it's best practise to let SQL Remote run at the consolidated after each step (in send-only mode) in order to build separate message files for each step.

CAVEAT: (I'm not sure whether you could include the DEFAULT clause in step 1 without filling the new columns. If so, then in step 2 and 3 you could simply use UPDATE ... set unique_cnt = DEFAULT, methinks.)

As always, I would recommend to verrrrry thoroughly test your SQL Remote schema modifications...

[Breck_Carter]

I am guessing the global database id will have to be set before the alter, otherwise it will try to set the column to null: "If the public option global_database_id is set to the default value of 2147483647, a NULL value is inserted into the column."

[VolkerBarth]

Yes, I was simply assuming that the remotes in the field are already using GLOBAL AUTOINCREMENT defaults, therefore requiring the value to have been set before...

[Former Member]

From the context it sounds like you are referring to SQL Remote, correct?

Yes, when adding a column with a default clause using the alter table statement "All rows in the column are populated with this [default] value." The alter table statement doc provides additional details of behaviours and side affects around altering tables such as affects of other connections to table, etc. I'm not familiar with details of how SQL Remote processes requests, so I cannot comment further on your locking question.

It is up to you to set the partition using the global_database_id option. The section on assigning unique identification numbers to each database provides some ideas on managing the database IDs.

[Former Member]

Note, a lot has changed since 8.0.2, so you would want to check the 8.0.2 doc for 8.0.2 behaviour.