topic Re: SDE compression and One way replication in Data Management Questions

SDE compression and One way replication

CharlesHarris — Wed, 16 Mar 2011 21:03:35 GMT

Hi-

I'm having a problem compressing an SDE database that is setup for one way replication. I have a script that is setup to edit a business table in sql on a nightly basis and then update a dissolved feature class from a spatial view that uses that business table, it follows the proper versioned/replication workflow. All replicated data is registered as versioned and has globalids

The workflow is performed in these steps in a batch file

1. - kill all connections to the database
2. - create multiversioned view on the replicated business table and replicated dissolved feature class
3. - Run SQL script to load a csv file containing the new business table data as external table in oracle
4. - Run SQL script to create a new version(mvedits) in database, set it as current version, open it for editing, delete records in business table, insert new records from external table, commit edits and close edit session.
5. - Run Python script to dissolve the spatial view that uses the business table and produce a temporary dissolved feature class
6. - Run SQL script to open the edit version for editing, delete records in replicated dissolve feature class, insert new records from temp dissolve feature class, commit edits and close edit session.
7. - Delete both multiversioned views, external table and temp dissolve feature class
8. - Run python script to reconcile the edit version to the default version and post changes.
9. - Run python script to synchronize the replica to the child database (which is supposed to synchronize to the child, delete the replica and create a new one based on the new default version of the database)
10. - Update dbms stats on edited tables and sde tables
11. - compress database
12. - Update dbms stats on edited table and sde tables
13. - Delete the edit version created in step 4

I have followed the workflow for versioning right out of the help and followed the workflow for compressing a database with one-way replication found here http://downloads2.esri.com/support/whitepapers/ao_/J9842_GDB_Compress_With_Replicas.pdf

All of the edits get pushed to the child database during the synchronization and the tables in that database show up properly. The problem is that I cannot compress the state tree back to 0 in the parent database which means that the spatial view that is based off of the buisness table that gets edited does not reflect the proper data (still the old data before the edit). It seems that the replica synchronization process is not actually deleting the replica and recreating it as it should because if I unregister it manually and recreate it, I can then compress the state tree to 0.

How can I get this to work properly??!

Re: SDE compression and One way replication

VishalPahuja — Wed, 16 Mar 2011 22:58:15 GMT

It sounds like you need acknowledgements from your child replica. Until you get those, the state tree would be pinned with those internal synch versions.

Re: SDE compression and One way replication

CharlesHarris — Thu, 17 Mar 2011 13:36:22 GMT

It sounds like you need acknowledgements from your child replica. Until you get those, the state tree would be pinned with those internal synch versions.

I thought when performing connected replication that the acknowledgement happened automatically?

Re: SDE compression and One way replication

HeatherMcCracken — Thu, 17 Mar 2011 19:10:58 GMT

9. - Run python script to synchronize the replica to the child database (which is supposed to synchronize to the child, delete the replica and create a new one based on the new default version of the database)

Can you describe this step more? Is this your own custom synchronize or are you using the Synchronize Changes gp tool? If the latter, the Synchronize Changes gp tool does not drop and recreate the replica as part of the synchronization.

Re: SDE compression and One way replication

CharlesHarris — Thu, 17 Mar 2011 20:59:49 GMT

Can you describe this step more? Is this your own custom synchronize or are you using the Synchronize Changes gp tool? If the latter, the Synchronize Changes gp tool does not drop and recreate the replica as part of the synchronization.

I am using the Synchronize Changes gp tool. I was under the understanding that it removed the existing system replica version and created a new one based on this verbage found on page 6(10 in pdf) of the white paper referenced above.

"Next, Replica1 is synchronized where changes are sent from this parent geodatabase to the relative replica geodatabase.The synchronization process sends edit 1 and edit 3 to the relative replica, since these changes were applied to the replica version (DEFAULT) after the replica was created. In this example, a connected synchronization is performed where an acknowledgment is automatically applied after the relative replica receives the changes. Upon receiving the acknowledgment message, the existing system version is removed and a new one is created from DEFAULT."

Re: SDE compression and One way replication

HeatherMcCracken — Mon, 21 Mar 2011 15:40:18 GMT

I wonder if you could be running into Nim039250? Are you working with ArcGIS 9.3? If so, installing 9.3 SP1 will fix this issue.

See the KB article which discusses this issue.
http://resources.arcgis.com/content/kbase?fa=articleShow&d=35652

Also, just for clarification of terms - the compression paper describes that it's the replica system version which is deleted and recreated, not the replica itself. The replica itself does not get recreated with each sync. This is why I got confused. So your original statement would be more accurate if it included the words "system version" in it... as below.

9. - Run python script to synchronize the replica to the child database (which is supposed to synchronize to the child, delete the replica system version and create a new one based on the new default version of the database)

Let me know if you have any follow up questions on the differences between replica system versions and the replica itself - or need any clarification.

Is it possible you are running into Nim039250?

thanks,
Heather

Re: SDE compression and One way replication

CherylCleghorn — Mon, 21 Mar 2011 22:50:10 GMT

Hi

The problem may be another issue which we've recently discovered where the system sync version is not being deleted when synchronizing with Python script. Can you please let us know what your system configurarion is (i.e. database and ArcGIS/ArcSDE versions)? Would you be able to log an incident with esri support services so that we can investigate if this is what you are encountering?

Thanks
Cheryl

Re: SDE compression and One way replication

CharlesHarris — Tue, 22 Mar 2011 20:31:03 GMT

Ah, I'm sorry Heather. It seems I overlooked the proper terminology in my haste. I understand the difference and appreciate you clearing that up for me. I can use the Export data changes gp tool to get all versions to the same state and then compress to 0 but it does not delete the "old" system version.

Hi

The problem may be another issue which we've recently discovered where the system sync version is not being deleted when synchronizing with Python script. Can you please let us know what your system configurarion is (i.e. database and ArcGIS/ArcSDE versions)? Would you be able to log an incident with esri support services so that we can investigate if this is what you are encountering?

Thanks
Cheryl

Thanks for the reply Cheryl. I've found out that, the problem is just that...the system sync version isn not being deleted which poses a problem. We are able to write some basic sql to go in and delete it from the SDE.VERSIONS table after the replication but before the compression which enables us to compress to State 0. However, It's cumbersome and poses problems when trying to edit/sync/replicate/compress in a manual fashion.

We do not have a support contract setup, so if that is required to setup an incident report, we may not directly be able to setup an incident report. However, our client does have a support contract so we may be able to have them setup an incident report.

We are using Oracle 11g dbms with ArcSDE 9.3.1 sp1 installed

Re: SDE compression and One way replication

CherylCleghorn — Wed, 23 Mar 2011 13:14:12 GMT

Hi

Some of the system versions are required for the replica to continue to function properly, so writing a script to manually delete them would be risky. Determining what can be deleted depends on the generation numbers.
We tested the issue with versions 9.3.1 and 10; the problem described has been resolved at version 10.

Cheers
Cheryl

Re: SDE compression and One way replication

CharlesHarris — Wed, 23 Mar 2011 15:05:34 GMT

Hi

Some of the system versions are required for the replica to continue to function properly, so writing a script to manually delete them would be risky. Determining what can be deleted depends on the generation numbers.
We tested the issue with versions 9.3.1 and 10; the problem described has been resolved at version 10.

Cheers
Cheryl

So since our client is still at 9.3.1 it seems that our options are as I've stated above, correct? Those options would be use the Export Data Change Messages tool and then compress to state 0 while leaving the "old" system version in the SDE.VERSIONS table, which will give us an increasing number of orphaned records in that table as we continue to replicate, or run the replication, query the table to determine which of our system versions that is owned by the replica owner still references state 0, delete that system version and then compress all other versions to state 0.

If we decide to use the gp tool and leave the orphaned records in the table, when our client upgrades it's SDE database to version 10 will those records be deleted the first time we replicate and compress?

Re: SDE compression and One way replication

CherylCleghorn — Wed, 23 Mar 2011 20:29:37 GMT

Jeremy

I cannot advise on deleting system versions without investigation. I forgot to mention a possible workaround at 9.3.1: after you've synchronized your changes (even with python), can you try synchronizing again, with no additional edits. This should clear out the system versions that are no longer needed.

Also after upgrading to 10, synchronizing with 10 will also clear the accumulated sync versions.

Regards
Cheryl

Re: SDE compression and One way replication

CharlesHarris — Wed, 30 Mar 2011 14:48:32 GMT

Sorry for the delay...the extra synchronization did not work for us. We have noticed that the pattern that seems to develop in the SDE.VERSIONS table during the synchronization process is as follows.

*Replica is created -> system version SEND_SYNC_X_0 is created
*Replica is synchronized (1st time) -> SEND_SYNC_X_0 is deleted & SEND_SYNC_X_1 is created and remains after the synchronization (it references the same state as default which is the only other version present and therefore the database can be compressed to state 0)
*Replica is synchronized (any other time than the first) -> SEND_SYNC_X_1 is not deleted, SEND_SYNC_X_2 & SEND_SYNC_X_3 are created and remain after synchronization. So there three versions that remain after synchronization, we run the Export Data Change Messages gp tool and are able to get SEND_SYNC_X_2 AND SEND_SYNC_X_3 to reference the same state as the default version. However, we are not able to get SEND_SYNC_X_1 to reference the same state as the others (it still reference state 0 from the last compression) and therefore cannot compress to state 0.

The only viable option that we've found is to use the Export Data Change Messages gp tool to align the states of the two new versions and the default version then implement a bit of sql that checks to see if there are more than 3 records is the SDE.VERSIONS table and deletes the one that references state_id 0 and has a parent_version_id of 1.

Using this workflow we are able to compress to state 0 successfully.

Re: SDE compression and One way replication

HusseinNasser — Mon, 01 Jul 2013 07:27:38 GMT

With One way replication this is a known problem in 10.1 we are in contact with ESRI to resolve it but nothing yet.

Until they do here is a workaround this seems to compress the tree to state 0 and move all delta edits to the base tables.

We do this workaround on a weekly basis

Re: SDE compression and One way replication

JessicaKirby — Tue, 20 Aug 2013 18:28:07 GMT

With One way replication this is a known problem in 10.1 we are in contact with ESRI to resolve it but nothing yet.

Until they do here is a workaround this seems to compress the tree to state 0 and move all delta edits to the base tables.

We do this workaround on a weekly basis

Any luck finding a solutions to this problem?

Re: SDE compression and One way replication

DanMcCoy — Fri, 23 Aug 2013 23:29:35 GMT

With One way replication this is a known problem in 10.1 we are in contact with ESRI to resolve it but nothing yet.

Hussein,

Do you have the bug number for this issue? Any idea if it's been resolved in 10.2?

Thanks,

Dan