SDE compression and One way replication

CharlesHarris · ‎03-16-2011

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

VishalPahuja · ‎03-16-2011

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.

CharlesHarris · ‎03-17-2011

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?

HeatherMcCracken · ‎03-17-2011

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.

CharlesHarris · ‎03-17-2011

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."

HeatherMcCracken · ‎03-21-2011

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

CherylCleghorn · ‎03-21-2011

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

CharlesHarris · ‎03-22-2011

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

CherylCleghorn · ‎03-23-2011

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

CharlesHarris · ‎03-23-2011

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?