Synchonization Manager

[Former Member]

Hi All,

Just wondering if anyone has used the synchronization manager tool with MaxDB and if so, do they have any advice/suggestions/feedback on it?

I'm currently working on a custom software solution that will interface to central R3 & CRM SAP systems. We will have a number of client PC's in various sites all running local MaxDB instances for their master and transaction data which will periodically accept master data updates via XI from the central SAP instances. We also need to ensure the clients can keep each other updated on transaction data - we had originally planned to code this functionality but wondered if the synchronization aspects of MaxDB might perform this task instead.

My initial understanding is that we would need a message server set up to handle the JMS traffic from each client - would we need a J2EE server running on each client PC?

Anyone done anything like this before?

Thanks in advance,

Gareth.

[Former Member · Accepted solution]

Gareth,

my first guesses are

a)you logged in to the database as a user which has no access to table 'CUSTOMER'.

b)that the system tables (which hold catalog information) are damaged.

Typically, this can happen when the database software has been upgraded from an earlier version.

Before anything else, could you send us some informations?

1. whether or not you arrived at version 7.6.00.12 by

a software upgrade (instead of a fresh install)

(at best, send us the result of the statement

'select * from sysupgradehistory', (if possible))

2. the version of SQL Studio you are using.

("Help"->"About")

3. As which user did you connect to 'DBMASTER' with

Sql Studio?. Is it usable at all, e.g. could you

successfully submit typed in statements from the SQL

editor?

to a)

log in as 'USER_MASTER' (PW:'MASTER').

That should work (normally).

to b)

To recreate the system tables, you could use Database Manager,

"Configuration"->"Upgrade System Tables", or

alternatively from the shell:

'dbmcli -d DBMASTER -u DBADMIN,DBADMIN load_systab'

(This is harmless anyway.)

You could contact me at:

<firstname>DOT<lastname>ATsapDOTcom, or here at SDN

regards,

Markus Stausberg

[Former Member · Accepted solution]

Hi Markus,

I've installed 7.6.00.12 as a completely fresh install - I've not had any other versions of Max DB installed as I've never used it before.

I cannot log into the DBMASTER db with user USER_MASTER - instead I have used user DBADMIN. When I connect to DBMASTER in SQL studio I see the error:-

Module: CatalogView

Function: checkForLocalFolder

Error No: 52

Error: Bad file name or number

This appears to happen to my colleagues as well with the same version of SQL studio - we are using version 7.6.00.12 of SQL studio.

If I run the 'select * from sysupgradehistory' I receive

Kernel    7.6.00   Build 012-123-102-632  2006-09-06 15:26:35.632000  LOAD_SYSTAB  7.6.00   Build 012

When connecting to DBMASTER I am using user DBADMIN as mentioned above.

I have done the following:-

Installed Max DB 7.6.00.12

Installed SQL Studio 7.6.00.12

Installed Database Manager 7.6.00.12

I have copied all of the necessary .jar files from my Eclipse directory into folder "C:\Documents and Settings\All Users\Application Data\sdb\data\app\syncman\extern" as well as j2ee.jar and sapdbc.jar and the swt.dll

I've run C:\Program Files\sdb\programs\bin\syncmanjconf.cmd and pointed it at my Java executable and my J2EE.jar

I've run C:\Program Files\sdb\programs\app\syncman\example\createexampledatabases.cmd and createexampletables.cmd to get the following output

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

P:>c:

C:>cd program filessdbprogramsappsyncmanexample

C:Program Filessdbprogramsappsyncmanexample>createexampledatabases
Create database DBMASTER
    Create param file
OK
    Add devspaces
OK
    Start database DBMASTER
Create database DBCLIENT
    Create param file
OK
    Add devspaces
OK
    Start database DBCLIENT

C:Program Filessdbprogramsappsyncmanexample>createexampletables
path to java executable [C:j2sdk1.4.2_09jrebin]:
path to j2ee.jar file: C:Documents and SettingsAll UsersApplication Datasdb
dataappsyncmanextern
Application parameters:
   clientDriver: com.sap.dbtech.jdbc.DriverSapDB
   clientURL...: jdbc:sapdb://127.0.0.1/DBCLIENT
   clientUser..: DBSERVICE
   masterDriver: com.sap.dbtech.jdbc.DriverSapDB
   masterURL...: jdbc:sapdb://127.0.0.1/DBMASTER
   masterUser..: DBSERVICE

---------------------------------------------

CONNECT DBSERVICE

Database URL : jdbc:sapdb://127.0.0.1/DBMASTER
Database user: DBSERVICE
Product name : SAP DB
Product vers : KERNEL    7.6.00   BUILD 012-123-102-632
JDBC driver  : package com.sap.dbtech.jdbc, MaxDB JDBC Driver, MySQL MaxDB, 7.6.
0    Build 012-000-004-339

DROP SCHEMA MESSAGESERVICE
   --> [-4024] (at 13): Unknown name:MESSAGESERVICE

DROP SCHEMA SYNCHRONIZATIONSERVICE
   --> [-4024] (at 13): Unknown name:SYNCHRONIZATIONSERVICE

DROP USER USER_MASTER
   --> [-4003] (at 11): Unknown user name:USER_MASTER

CREATE USER USER_MASTER PASSWORD MASTER RESOURCE NOT EXCLUSIVE

COMMIT RELEASE DBSERVICE

---------------------------------------------

CONNECT DBSERVICE

Database URL : jdbc:sapdb://127.0.0.1/DBCLIENT
Database user: DBSERVICE
Product name : SAP DB
Product vers : KERNEL    7.6.00   BUILD 012-123-102-632
JDBC driver  : package com.sap.dbtech.jdbc, MaxDB JDBC Driver, MySQL MaxDB, 7.6.
0    Build 012-000-004-339

DROP SCHEMA SYNCHRONIZATIONSERVICE
   --> [-4024] (at 13): Unknown name:SYNCHRONIZATIONSERVICE

DROP USER USER1_CLIENT
   --> [-4003] (at 11): Unknown user name:USER1_CLIENT

DROP USER USER2_CLIENT
   --> [-4003] (at 11): Unknown user name:USER2_CLIENT

CREATE USER USER1_CLIENT PASSWORD CLIENT RESOURCE NOT EXCLUSIVE

CREATE USER USER2_CLIENT PASSWORD CLIENT RESOURCE NOT EXCLUSIVE

COMMIT RELEASE DBSERVICE

---------------------------------------------

CONNECT USER_MASTER

Database URL : jdbc:sapdb://127.0.0.1/DBMASTER
Database user: USER_MASTER

CREATE TABLE customer
     (cno       INTEGER NOT NULL,
      title     CHAR (10),
      firstname CHAR (20),
      name      CHAR (20),
      zip       CHAR  (5),
      city      CHAR (20),
      state     CHAR  (5),
      address   CHAR (30),
      portrait  LONG BYTE,
      PRIMARY KEY (cno))

CREATE TABLE reservation
     (rno       INTEGER NOT NULL,
      cno       INTEGER,
      type      CHAR (10),
      arrival   DATE,
      departure DATE,
      PRIMARY KEY (rno))

GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO DBSERVICE

GRANT SELECT, INSERT, UPDATE, DELETE ON reservation TO DBSERVICE

INSERT customer Porter
INSERT customer Brown
INSERT customer Datasoft
INSERT customer Brian
INSERT customer Griffith
INSERT customer Randolph
INSERT customer Smith
INSERT customer Jackson
INSERT customer Doe
INSERT customer Howe
INSERT customer Miller
INSERT customer Baker
INSERT customer Peters
INSERT customer TOOLware
INSERT customer Jenkins

INSERT reservation 100
INSERT reservation 110
INSERT reservation 120
INSERT reservation 130
INSERT reservation 150
INSERT reservation 140
INSERT reservation 160
INSERT reservation 170
INSERT reservation 180
INSERT reservation 190

COMMIT RELEASE USER_MASTER

---------------------------------------------

CONNECT USER1_CLIENT

Database URL : jdbc:sapdb://127.0.0.1/DBCLIENT
Database user: USER1_CLIENT

CREATE TABLE customer
     (cno       INTEGER NOT NULL,
      title     CHAR (10),
      firstname CHAR (20),
      name      CHAR (20),
      zip       CHAR  (5),
      city      CHAR (20),
      state     CHAR  (5),
      address   CHAR (30),
      portrait  LONG BYTE,
      PRIMARY KEY (cno))

GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO DBSERVICE

COMMIT RELEASE USER1_CLIENT

---------------------------------------------

CONNECT USER2_CLIENT

Database URL : jdbc:sapdb://127.0.0.1/DBCLIENT
Database user: USER2_CLIENT

CREATE TABLE booking
     (id        INTEGER NOT NULL,
      arrival   DATE,
      departure DATE,
      guest     INTEGER,
      PRIMARY KEY (id))

CREATE TABLE guest
     (guest     INTEGER NOT NULL,
      surname   CHAR (20),
      firstname CHAR (20),
      title     CHAR (10),
      PRIMARY KEY (guest))

GRANT SELECT, INSERT, UPDATE, DELETE ON booking TO DBSERVICE

GRANT SELECT, INSERT, UPDATE, DELETE ON guest TO DBSERVICE

COMMIT RELEASE USER2_CLIENT

C:Program Filessdbprogramsappsyncmanexample>

I've just run this entire process after performing a system restore on my machine and I can now log into SQL studio as USER_MASTER and see the correct data in the tables... Not sure what happened before!

Thanks very much for your help,

Gareth.

[Former Member]

Hello Gareth,

there is no AMEND primitive in SQL. Most probably, AMEND

(otherwise known as UPSERT) is emulated by XI's database

access layer before the proper SQL is handed to the JDBC driver.

However, the problem the error message points to is that

an update of primary key columns has occurred.

Although the error occurs in the application (XI) code,

it is generated by Synchronization Manager, namely

by special UPDATE triggers which are created when

a unit is activated and disabled when it is deactivated.

(which explains the absence of the error when

synchronization is deactivated.)

The trigger checks whether there are PK columns affected,

and if so, throws this error, since SyncMan

can not handle PK updates.

There are essentially two ways to get around this limitation:

a) modify/configure the application to replace PK updates

with DELETE/INSERT combinations.

b) modify the data model so that PKs are as small as

possible and not affected by any updates the app

performs. (Note that uniqueness can also be enforced

by means of secondary keys/indexes).

Especially, if you already have (and control) a staging

table, it may be possible to get rid of an overly

large PK.

regards,

Markus

[Former Member]

Hi Markus,

We're almost at the user acceptance testing stage of our project so we are just tidying up some of the finer details! We have written an installation routine that creates MaxDB instances and installs our Java application without user intervention. I now need to figure out if it is possible to set up our synchronization services as part of the installation routine. Whilst using the syncmangui tool is really straightforward we will have a large number of master and client DB instances on a number of client PC's, all of which could change or move, so we would ideally like to automate the process.

Have you ever done anything like this? I can see that if I use the syncmangui to create master and client replication units, entries are created within the SYNCHRONIZATIONSERVICE schema tables on my DB instance - I wondered if it is possible to create these directly on the DB using SQL statements in our installation routine? If so, is there any significance to the key fields, such as PARTICIPANT_ID, which appear to be generated numbers. Could we use our own generated key values, as long as the data integrity is correct?

Any suggestions you could give me in this area will be greatly appreciated.

Thanks,

Gareth.

[Former Member]

Hello Gareth,

I would not recommend filling the meta data tables of the synchronization manager directly with SQL. If there are changes in the synchronization meta data schema your implementation would have to be changed too.

For test purposes we have an internal command line tool which can execute commands that create synchronization untis, participants etc. The actions that are provided by the command line tool are the counterpart to the SyncManGUI actions. It is not yet in the stadium of a product.

This command line tool is not part of the installation, but for testing purposes you can try it.

The commands look like this:

add replication table "USER_MASTER" "CUSTOMER"

add column group "Customer Address" for "USER_MASTER" "CUSTOMER"

…

add master unit "ReservationUnit" inout

add client unit "CustomerClientUnit" inout maxdblocalhost database DBSERVICE SECRET for "CustomerUnit"

…

activate all units

If this fits your needs I can send you the jar file (Version 7.6.00.36) and some command examples by e-mail.

regards

Wolfgang

[Former Member]

Gareth,

we have two possibilities here:

1. there is no bug from our side, and the error description applies.

(The Exception is caused by a user transparent synchronization trigger which was

created upon unit activation.)

This means that XI tried to perform an update on

(a) column(s) of the primary key.

In this case, there seem to be not much other choices than to

configure XI to replace PKey updates with delete/insert

combinations, if that's possible (you wrote about

XI AMEND mode).

If you control the data model, you could create a modified, friendlier copy of the table, and a pair of user defined update triggers

(on the copy and the original) to keep them in sync.

2. It's our fault. In this case we would like to see

- the table's definition (including PK def)

- the code of the synchronization trigger on that

table. (Log in as DBSERVICE,SECRET (or whatever

you named the synchronization user), do a

'select * from triggers'.

there should be three rows for TABLENAME=<your table>.

DEFINITION contains the trigger definitions.

We need the def of the UPDATE trigger.

- if possible the SQL statement which caused the

problem.

regards,

Markus

[Former Member]

Hi Markus,

I think I'm getting to the bottom of the issue...

XI sends the data and tries to perform an AMEND action - typically we as SAP developers understand that this will cause the database to update any entries with a matching key and insert those entries that don't already exist into the DB. In looking at the SQL used with MaxDB I'm guessing this isn't possible as there is no AMEND command - only the UPDATE...SET...WHERE command. My guess is that the JDBC driver used by XI does not cope with this scenario very well.

Interestingly, our proposed design for the deliverable system does require us to use a seperate table as a staging table for the data coming from XI, which we will manually read and move requisite data from into our live tables as and when we need to. This will be performed by our main Java application which will be able to utilise the correct UPDATE...SET...WHERE syntax.

Also, we have found that if I deactivate the synchronization against the table then XI can successfully insert duplicate entries so the AMEND function does appear to work.

Gareth.

[Former Member]

Hello Gareth (and maybe others),

On request

(<myfirstname>PUNKT<mylastname>KLAMMERAFFEsapPUNKTcom,

refer to your platform (i.e. 32bit-linux-intel))

you could get a SyncMan upgrade package to version

7.6.01.04 which can be applied to your MaxDB installation.

Most important, this version contains a

'network failover' mechanism to overcome

physical network disruptions

(cable unplugged/cut, transition between wifi hotspots)

without need to restart syncservices or the message server.

regards,

Markus

[Former Member]

Hello Gareth,

if the NPE occurs in the syncservice,

and is reproducible, you could increase

the verbosity of syncservice's output

and/or redirect that to a file.

For this, you use the 'expert' options of

syncservice, synopsis:

syncservice start -d <database instance> [<expert option>...]

the expert option '-help' gives an overview.

('expert options' are a bit awkward, because they are

related to the actual java classes, and not

to the simplifying wrapper script 'syncservice'.)

The relevant options are the '-verbose*' ones.

Increase verbosity along this path:

'-verbose' ->

'-verbose_exception_stacktrace' ->

'-verbose_exception_stacktrace' AND

'-verbose_consumption' AND

'-verbose_capture' ->

'-verbose_exception_stacktrace' AND

'-verbose_consumption_messages' AND

'-verbose_capture_messages'.

You could increase verbosity until the stack

trace becomes visible.

to redirect output to a log file, use the shell, or

'-logfile <filename>'.

We would like to see the stacktrace of the NPE.

Please send the logfile to my address, or, if too large

put it on some webserver.

regards,

Markus

[Former Member]

Hi Markus,

We've resolved the NullPointer exception - I'd somehow managed to screw up the synchronization units and ended up with duplicate shadow/version tables on our DB instances. After de-activating and tidying up the DB instances everything was working again.

We now have a new issue!

We have an XI system sending data to one of the DB instances' tables - this table is set to synchronize two ways between 1 master and 3 client instances. The table is defined with primary key of customer number and then just 9 char(50) type fields. With the table initialised XI can send batches of data down no problem - we're sending batches of 3000 records at a time.

Then, if we try to send the same data again but with changes to the 9 character fields we get the following error:-

Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. 'JAMGT_USER.CUSTOMERDETS' (structure 'STATEMENTNAME'): com.sap.dbtech.jdbc.exceptions.DatabaseException: [-7083]: Replicated table not allowed: Primary Key Update

I understand that we cannot update the primary key of tables when they are set for synchronization but for our solution we do need to send updates from XI.

The XI interface is set to perform AMEND actions on the database so in theory this should work.

Any ideas?!

Thanks again,

Gareth.

[Former Member]

Gareth,

In the first try, you ran into a problem with

SQL Studio's local folder directory.

This is where the studio stores user-defined

statements on the local machine.

If the problem reappears, check the local folder

directory. i.e. if there are permission problems,

'unusual' characters in the path, network problems

if this is on a share, whether there are files not

generated by SQL Studio, etc..

You can find (and change) the path of this directory in SQL Studio: "Session"->"Settings"->"Catalog Manager"->

"Path for local Folder".

regards,

Markus

[Former Member]

Hi Markus,

Thanks for your response. The customer I'm working for has the My Documents folder stored on a network drive so the local folder does indeed have issues - thanks for clarifying this.

I'm now successfully replicating between my DBMASTER and a DBCLIENT on another machine on the network

I'm just testing an end to end XI posting data to my DBMASTER and then replicating to multiple DBCLIENT's - so far so good.

Thanks for all your help,

Gareth.

[Former Member]

Hi Markus,

I'm having some problems in that one of the client machines' synchronization service is throwing a Java NullPointer exception when a row is inserted into its synchronized database table. We can't figure out exactly what is causing it! It's a simple scenario in that I have a master DB instance on my machine and a client DB instance on another with just one table set to synchronize all columns.

We have a client Java app that is inserting data directly into the synced table on the client DB - this should be picked up and sent to my master DB but instead we get the exception and the client's synchronization service fails and then won't restart.

We also have another synchronization unit set up this time running in the oppositie direction so my machine is the client and the other is the master but this works ok.

First question, is there a later version of SyncMan than 7.6.00.12 that we could download and upgrade our MaxDB software as well?

Also, are there any logs or other ways of trying to determine what is causing the problem?

Thanks in advance,

Gareth.

[Former Member]

Hello Gareth,

MaxDB Synchronization manager is self-contained in that it

does not need a separate messaging system. It comes with

its own message server.

SyncMan works with all MaxDB Datbases with version >=7.6.00.12.

Databases which participate in a synchronization setup (more precisely, definable logical units therein) can operate either in unidirectional (master + readonly slaves) or bidirectional mode (data changes at slaves are sent to the master database, potential conflicts are resolved there, and the data is propagated to all other slaves).

All data changes from within one database transaction are handled in an atomic way.

We recommend to walk through the example (provided in '<indepProgramPath>/app/syncman/example/doc/SyncManExample.pdf' to get in touch with the concepts.

If you have problems to get SyncMan for your particular 7.6 version of MaxDB, just send me an email to

<firstname>.<lastname>ATsapDOTcom

regards,

Markus Stausberg

[Former Member]

Hi Markus,

Thanks for your quick response - I hadn't found the Sync Mgr Example document yet! It looks to be just the document I was looking for

Thanks again,

Gareth.

[Former Member]

Hi Markus,

I've now walked through the example as you suggested (using version 7.6.00.12 of MaxDB as no other versions seemed to have the synchronization manager included and version 3.1 of the Eclipse files.)

I've been right through the example document with no problems but have hit an issue that you may have a quick solution for. I've used the "createexampledatabases" and "createexampletables" programs to create the necessary DB objects and these appear to work correctly. Unfortunately, when I then view the databases in SQL studio I start getting errors...

If I try to view table CUSTOMER in schema USER_MASTER in database DBMASTER I get an error "An error occured while loading the table definition". If I try to view the content of the same table I get the following shown in the log

---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
 Base table not found;-4004 POS(16) Unknown table name:CUSTOMER
SELECT * FROM "USER_MASTER"."CUSTOMER"

Any suggestions as to what is wrong?!

Thanks,

Gareth.