SDE compression and One way replication

[CharlesHarris]

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??!

[CherylCleghorn]

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

[CharlesHarris]

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.

[HusseinNasser]

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

[JessicaKirby]

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?

[DanMcCoy]

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