|
We are upgrading sqlremote replicating databases from 9.0.2 to 12.0.1. During the process which includes 100 + remotes located throughout the world we are replicating between 9.0.2 remotes and the converted 12.0.1 consolidated. No problems so far. The process of upgrading all remotes will take 1 to 2 years due to availability. During this period we need to continue operations which includes passthrough modes for application updates. The problem is when passing through the 9.0.2 statement INSERT INTO ABC.XYZ ON EXISTING UPDATE ASA 12 changes this to INSERT INTO ABC.XYZ ON EXISTING UPDATE DEFAULTS OFF which then fails on syntax error when applied at the 9.0.2 remote. "SQL statement failed: (-131) Syntax error near 'defaults' on line x" "Skipping" |
|
You'll often run into problems when syntax changes from one version to another and you are trying to execute statements in passthrough on version "X" that will be executed on version "Y". The problem is even worse with the INSERT ON EXISTING statement, since the database engine is adding the defaults clause into the transaction log no matter what. While not ideal, you can work around the problem by changing your passthrough script from : passthrough for subscription to cons.p1; insert into cons.Admin on existing update values ( 20, 'Reg' ); insert into cons.Admin on existing update values ( 21, 'Amy' ); passthrough stop; to : passthrough for subscription to cons.p1; create procedure drop_me() begin execute immediate 'insert into cons.Admin on existing update values ( 20, ''Reg'' )'; execute immediate 'insert into cons.Admin on existing update values ( 21, ''Amy'' )'; end; call drop_me(); commit; drop procedure drop_me; passthrough stop; By putting the SQL into a string and doing an execute immediate (which can only be done in an SP), you are preventing the database engine from parsing the string and changing the insert statement to include the DEFAULTS clause in the transaction log. |
Reg,
Thanks very much I was hoping there may be a database option which turns off the syntax changes for passthrough statements. We are working around this problem as you suggest, however the effort is significant on large passthrough statements. Passing through the syntax used seems a good option since the ASA 12 server will automatically alter the syntax when applied as needed. We also ran across this issue with the modify vs. alter syntax on a table change.
Thanks again
Jim Diaz
FWIW, after similar experiences, I dared to make an according product suggestion here...