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

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.

dbremote sql-remote sybase-central

asked 04 Feb '21, 07:49

MichaelMange...
125●5●6●15
accept rate: 0%

edited 04 Feb '21, 10:02

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

(04 Feb '21, 08:56) Reg Domaratzki

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

(04 Feb '21, 09:48) MichaelMange...

Replies hidden

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

(04 Feb '21, 10:04) Reg Domaratzki

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.

(04 Feb '21, 10:32) MichaelMange...

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

(05 Feb '21, 14:49) Reg Domaratzki

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

(08 Feb '21, 03:13) MichaelMange...

showing 2 of 6 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sybase-central ×128
sql-remote ×103
dbremote ×78

question asked: 04 Feb '21, 07:49

question was seen: 765 times

last updated: 08 Feb '21, 03:13

SAP SQL Anywhere

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

Follow this question

Related questions