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
asked 23 Jan '12, 17:26
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.
answered 25 Jan '12, 07:55
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.
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...
answered 25 Jan '12, 08:29
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...
answered 25 Jan '12, 12:48