Question Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice in Technology Q&A

SQL Anywhere 17: Dbremote import/export importing certain tables twice

Former Member — Thu, 04 Feb 2021 07:49:29 GMT

Hello

We are currently migrating from Sybase 11 to Sybase 17. We use file replication with dbremote (SQL Remote, publications..).

We have managed to upgrade our Sybase 17. Now we are testing our replication but we are running into errors here.

Export command:

C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbremote.exe" -c "eng=dev;dbn=dev;LINKS=tcpip(HOST=<SERVER>;PORT=<PORT>);uid=db;pwd=db;COMMLINKS=TCPIP;CharSet=utf-8" -b -qc -s -os 50M -o "D:\\Sync\\dbremote_messages.log" -l 100000 -t -v "D:\\DATABASES\\DEV"

Import command (our remote databases are still Sybase 11):

"%SQLANY11%\\Bin32\\dbremote.exe" -c "eng=local01;dbn=local01;uid=db;pwd=db;CharSet=utf-8;COMMLINKS=TCPIP,shmem" -b -qc -r -os 50M -o "D:\\Sync\\dbremote_messages.log" -l 100000 -t -v "D:\\Databases\\local"

Export is successful, but in comparison to Sybase 11 it has created about double the amount of files, is that normal? But when trying to import it's trying to import the same table twice:

    ...
    I. 2021-02-04 12:03:12. 96 rows synchronized
    I. 2021-02-04 12:03:12. SELECT SystemSettingID, Attribute, CurrentValue, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Rep_Key FROM dba.SystemSetting
    I. 2021-02-04 12:03:12. 61 rows synchronized
    I. 2021-02-04 12:03:12. SELECT SystemSettingID, Attribute, CurrentValue, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Rep_Key FROM dba.SystemSetting
    E. 2021-02-04 12:03:12. SQL statement failed: (-193) Primary key for table 'SystemSetting' is not unique : Primary key value ('777000000001')

Afterwards the imports stops, deletes all the replication files and no data has imported. Am I missing something here, what could possibly be going wrong?

Thank you.

Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice

regdomaratzki — Thu, 04 Feb 2021 08:56:29 GMT

Double the number of messages files being created by v17 SQL Remote does not sound correct. I'm suspicious that you may have ended up with two remote users using the same address somehow.

Are you running a SYNCHRONIZE SUBSCRIPTION on the consolidated for a single remote user or multiple remote users when the problem occurs?

Could you please post the exact SYNCHRONIZE SUBSCRIPTION command(s) you are executing at the consolidated database and the results of the following SQL statement run against both the consolidated database (i.e. eng=dev;dbn=dev) and the remote database (eng=local01;dbn=local01) that is generating primary key errors :

select user_name,consolidate,type_name,address 
from sysremoteusers 
union 
select CURRENT PUBLISHER, 'P', type_name, publisher_address 
from sysremotetypes 
where publisher_address <> ''

Thanks, Reg

Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice

Former Member — Thu, 04 Feb 2021 09:48:50 GMT

Hello Reg

Thank you for the quick reply.

Query result again consolidated (we have many remote databases, 0777 is the one we have newly created / I am testing with now).

user_name,consolidate,type_name,address
'icatcentral','P','FILE','icatcentral'
'icat2660','N','FILE','icat2660'
'icat1874','N','FILE','icat1874'
'icat1869','N','FILE','icat1869'
'icat1861','N','FILE','icat1861'
'icat1847','N','FILE','icat1847'
'icat1840','N','FILE','icat1840'
'icat1839','N','FILE','icat1839'
'icat1830','N','FILE','icat1830'
'icat1818','N','FILE','icat1818'
'icat1808','N','FILE','icat1808'
'icat1717','N','FILE','icat1717'
'icat1587','N','FILE','icat1587'
'icat1369','N','FILE','icat1369'
'icat0911','N','FILE','icat0911'
'icat0887','N','FILE','icat0887'
'icat0777','N','FILE','icat0777'
'icat0746','N','FILE','icat0746'
....

I have checked the whole result and icat0777 is only there once. I cannot post the complete result because it is too big.

Remote (0777)

user_name,consolidate,type_name,address
'icatcentral','Y','FILE','icatcentral'
'icat0777','P','FILE','icat0777'

Full script we are running against the consolidated database (This is for ALL subscriptions, but this creates files in different folders).

"C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbremote.exe" -c "eng=icatdev;dbn=icatdev;LINKS=tcpip(HOST=<SERVER>;PORT=<PORT>);uid=db;pwd=db;COMMLINKS=TCPIP;CharSet=utf-8" -b -qc -s -os 50M -o "D:\\ApplicationData\\ICAT\\Sync\\SqlRemoteLogs\\icatcentral\\dbremote_messages.log" -l 100000 -t -v "D:\\DATABASES\\iCat\\DEV"

That is the only command we run in order to start the export.

Thank you

Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice

regdomaratzki — Thu, 04 Feb 2021 10:04:17 GMT

I'm interested in the SYNCHRONIZE SUBSCRIPTION statement(s) you executed against the consolidated database, not the dbremote command line you use against the consolidated database.

https://help.sap.com/viewer/93079d4ba8e44920ae63ffb4def91f5b/17.0/en-US/817b5e2f6ce210148aecd787a63222ee.html

Thanks, Reg

Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice

Former Member — Thu, 04 Feb 2021 10:32:09 GMT

Is this what you are looking for?

When adding a new remote installation we execute the following scripts:

/* For adding a new remote site AT HQ a subscription for the user (remote site) needs to be created. */
EXECUTE IMMEDIATE ('GRANT CONNECT TO "' + cast (@ReplicationKey as varchar) 
                                        + '" IDENTIFIED BY "' + cast (@ReplicationKey as varchar) + '"');

EXECUTE IMMEDIATE ('GRANT REMOTE TO "'  + cast (@ReplicationKey as varchar) 
                                        + '" TYPE FILE' 
                                        + ' ADDRESS ''' + cast (@ReplicationKey as varchar) + '''');

EXECUTE IMMEDIATE ('CREATE SUBSCRIPTION TO "icatcentral"."icatHQPub" (''' + cast (@ReplicationKey as varchar) + ''') FOR "' 
                                                                               + cast (@ReplicationKey as varchar) + '"');

EXECUTE IMMEDIATE ('SYNCHRONIZE SUBSCRIPTION TO "icatcentral"."icatHQPub" (''' + cast (@ReplicationKey as varchar) + ''') ' 
                                        + ' FOR "' + cast (@ReplicationKey as varchar) + '"');

EXECUTE IMMEDIATE ('START SUBSCRIPTION TO "icatcentral"."icatHQPub" (''' + cast (@ReplicationKey as varchar) + ''') ' 
                                        + ' FOR "' + cast (@ReplicationKey as varchar) + '"');

But we only execute these once.

Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice

regdomaratzki — Fri, 05 Feb 2021 14:49:02 GMT

I see that you also have a technical support issue open for this and are dealing with Ken. We'd be interested in getting information from you that will be difficult to post in the forum (you've already needed to truncate the results of something we've asked for), so I think it's best if we follow up through the tech support incident instead, where there are better methods for sharing large amounts of information.

If anyone other than Michael is following along at home, please respond to this post, and I'll be sure to post the eventual solution in the forum.

Reg

Re: SQL Anywhere 17: Dbremote import/export importing certain tables twice

Former Member — Mon, 08 Feb 2021 03:13:53 GMT

Thank you, we will continue this in the support issue.