Automate reconcile and post operations for sync-enabled data

ArcGIS geodatabase administrators can use Python scripting to automate many tasks that are normally performed using multiple geoprocessing tools. This topic discusses the process an administrator might go through to run a scheduled nightly reconciliation of versions after fieldworkers have synchronized edits made to data that participates in traditional versioning.

Identify geodatabase versions based on service name

First, create a list of geodatabase versions that need to be reconciled.

# Create a list of user names that will be used to find versions.
userList = ['???', '###']

# Get a list of versions for the service named '???' and '###' to pass into the ReconcileVersions tool.
versions = arcpy.da.ListVersions('//connectionLocation/admin.sde')

# Create an empty list that will be used to hold version names that you want to reconcile.
verReconcileList = []

# Loop through the versions list to look for versions with appropriate names.
# if these names are found append them to the verReconcileList.
for user in userList:
    for version in versions:
        if user.lower() in version.name.lower():
            verReconcileList.append(version.name)

Batch reconcile versions and post changes

You can use the Reconcile Versions tool to reconcile and post all traditional versions in an enterprise geodatabase. This tool provides options to reconcile all versions in the geodatabase to a target version (ALL_VERSIONS) or only versions that are blocking the target version from compressing (BLOCKING_VERSIONS). This tool is a means to achieve an effective compression, as it allows multiple versions to be reconciled and posted at once in an appropriate order. Following the reconcile or post operations, you can delete the versions by providing the DELETE_VERSION keyword.

In this example, versions are not deleted (the KEEP_VERSION keyword is provided), and the tool is run as the geodatabase administrator. Connecting as the geodatabase administrator provides the ability to reconcile and post all versions in the geodatabase, even private or protected versions owned by other users.

# Get a list of versions to pass into the ReconcileVersions tool.
versionList = arcpy.ListVersions('//connectionLocation/admin.sde')

# Execute the ReconcileVersions tool.
arcpy.ReconcileVersions_management('//connectionLocation/admin.sde', "ALL_VERSIONS", "sde.DEFAULT", verReconcileList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", "c:/temp/reconcilelog.txt")

Compress the geodatabase

After reconciling and posting changes, it is important to compress the geodatabase to remove any redundant information and move edits into the base tables.

# Run the compress tool. 
arcpy.Compress_management('//connectionLocation/admin.sde')

Rebuild indexes and update statistics

After performing a compression operation, it is recommended that you rebuild indexes and update statistics. These steps can be performed using the Rebuild Indexes and Analyze Datasets tools. These tools allow you to input a list of datasets to perform their functions on all the datasets at once. These tools also update statistics and rebuild indexes for appropriate system tables when run as the geodatabase administrator. The first part to this process is to get a list of data and the users who own the data. Indexes and statistics can only be updated by the owner of the data.

# set the workspace 
arcpy.env.workspace = '//connectionLocation/dataOwner.sde'

# Get the user name for the workspace
# this assumes you are using database authentication.
# OS authentication connection files do not have a 'user' property.
userName = arcpy.Describe(arcpy.env.workspace).connectionProperties.user

# Get a list of all the datasets the user has access to.
# First, get all the stand-alone tables, feature classes and rasters owned by the current user.
oDataList = arcpy.ListTables('*.' + userName + '.*') + arcpy.ListFeatureClasses('*.' + userName + '.*') + arcpy.ListRasters('*.' + userName + '.*')

# Next, for feature datasets owned by the current user
# get all of the featureclasses and add them to the master list.
for dataset in arcpy.ListDatasets('*.' + userName + '.*'):
    oDataList += arcpy.ListFeatureClasses(feature_dataset=dataset)

Note:

The wildcard tokens used to limit datasets owned by the user are operating system specific. In the example above, ('*.' + userName + '.*') will work for geodatabases in Microsoft SQL Server, PostgreSQL, and IBM Db2. For geodatabases in Oracle, the following wildcard can be used: (userName + '.*').

Once the list of data owned by each user is identified, use that information in the Rebuild Indexes and Analyze Datasets tools.

If you have multiple data owners, generate a data list for each data owner, and run the Rebuild Indexes and Analyze Datasets tools using database connection files (.sde) that connect to the geodatabase as each user.

# Execute rebuild indexes and analyze datasets
# Note: to use the "SYSTEM" option, the user must be an administrator.

workspace = "//connectionLocation/user1.sde"

arcpy.RebuildIndexes_management(workspace, "NO_SYSTEM", oDataList, "ALL")

arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", oDataList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")

Delete reconciled versions

The final step in the process is to delete the versions that have been reconciled and posted. You got the list of the reconciled versions when you ran the versionList statement earlier. In the following code, you'll loop through this list to delete any versions for which it is safe to do so.

Note:

If you're using geodatabase replication, it is important to avoid deleting any versions that are still referenced by a replica. These are the versions from which geodatabase replicas are created. Use the ListReplicas function to list replicas and the version name that the replica references. Attempting to delete a replica version should result in a failure, so it is best to avoid this.

In the following example, you'll get a list of the replica versions by iterating through all of the replicas. Next, remove any of these replica versions from the list of versions that have been reconciled. Finally, loop through the remainder of the list of versions that have been reconciled and delete those versions.

# set the workspace 
workspace = '//connectionLocation/admin.sde'

# Get a list of all the replica versions in the geodatabase
replicaVersions = [replica.version for replica in arcpy.da.ListReplicas(workspace)]

# Loop through this replica version list and remove these version names from the list of versions you reconciled earlier.
# You want to remove versions from this list that are still being referenced by a replica
for replicaVer in replicaVersions:
  if replicaVer in verReconcileList:
    verReconcileList.remove(replicaVer)

# Loop through the verReconcileList and delete versions. 
# These versions are no longer being referenced by a replica so you can assume it's safe to delete them.
if len(versionsList) > 0:
  for version in verReconcileList:
  		arcpy.DeleteVersion_management(workspace, version)

Complete code example

The code example below puts most of the pieces above together to perform the following operations:

• Identify geodatabase versions.
• Reconcile versions and post changes.
• Compress the geodatabase.
• Rebuild indexes and update statistics on system tables.
• Rebuild indexes and update statistics on user tables.

Note:

The following script contains additional logic when compared to the other code samples in this topic. The additional logic helps to better handle error cases. Also note that some of the comments and variable names have been changed in the script.

import arcpy

try:
    # Set the workspace 
    arcpy.env.workspace = 'Database Connections/admin.sde'
    
    # Set variables
    workspace = arcpy.env.workspace
    arcpy.env.overwriteOutput = True
    targetVersion = 'sde.DEFAULT'
    
    # Create a list of user names that will be used to find versions.
    userList = ['???', '###']
    
    # Get a list of versions for the service named '???' and '###' to pass into the ReconcileVersions tool.
    versions = arcpy.da.ListVersions(workspace)
    
    # Create an empty list to hold version names to reconcile.
    verReconcileList = []
    
    # Loop through the list to look for versions with our user names in their name where the parent version is the target version.
    # if these names are found, append them to the verReconcileList.
    for user in userList:
        for version in versions:
            if user.lower() in version.name.lower():
                if version.parentVersionName.lower() == targetVersion.lower():
                    verReconcileList.append(version.name)
    
    # Perform maintenance if versions are found; otherwise, there is no maintenance to perform.
    if len(verReconcileList)>0:
        
        # Execute the ReconcileVersions tool.
        arcpy.ReconcileVersions_management(workspace, "ALL_VERSIONS", targetVersion, verReconcileList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", "c:/temp/reconcilelog.txt")
        
        # Run the compress tool. 
        arcpy.Compress_management(workspace)
        
        # Rebuild indexes and analyze the states and states_lineages system tables
        arcpy.RebuildIndexes_management(workspace, "SYSTEM", "", "ALL")
        
        arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", "", "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
        
        
        '''
        *********************
        Data Owner(s) Section
        *********************
        '''
        # Get a list of datasets owned by the data owner user (requires second connection file)
        
        # Set the workspace 
        arcpy.env.workspace = 'Database Connections/dataOwner.sde'
        
        # Set a variable for the workspace
        workspace = arcpy.env.workspace
        
        # Get the user name for the workspace
        # this assumes you are using database authentication.
        # OS authentication connection files do not have a 'user' property.
        userName = arcpy.Describe(arcpy.env.workspace).connectionProperties.user
        
        # Get a list of all the datasets the user has access to.
        # First, get all the stand alone tables, feature classes and raster datasets owned by the current user.
        oDataList = arcpy.ListTables('*.' + userName + '.*') + arcpy.ListFeatureClasses('*.' + userName + '.*') + arcpy.ListRasters('*.' + userName + '.*')
        
        # Next, for feature datasets owned by the current user
        # get all of the feature classes and add them to the master list.
        for dataset in arcpy.ListDatasets('*.' + userName + '.*'):
            oDataList += arcpy.ListFeatureClasses(feature_dataset=dataset)
        
        # Rebuild indexes and analyze the data owner tables
        arcpy.RebuildIndexes_management(workspace, "NO_SYSTEM", oDataList, "ALL")
        
        arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", oDataList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
        
        '''
        *************************
        End Data Owner(s) Section
        *************************
        '''
        
        # set the workspace back to the geodatabase administrator workspace
        workspace = 'Database Connections/admin.sde'
        
        # Get a list of all the replica versions in the geodatabase
        replicaVersions = [replica.version for replica in arcpy.da.ListReplicas(workspace)]
        
        '''
        - We now have a list of versions that were created by taking a map offline (verReconcileList)
        - We also have a list of replica versions (replicaVersions)
        - The versions that we were reconciling are ready to be deleted if they are not currently pointing to a version
        - We are going to loop through the reconcile versions list and remove any versions that are still pointing to a replica
        - The versions remaining in the reconcile list are ready to be cleaned (deleted) up because there are no maps/replicas pointing to them. 
        '''
        
        # Use the list of versions associated with users/maps that we reconciled earlier. Remove any versions from the list that are still being used by a replica. 
        for replicaVer in replicaVersions:
            if replicaVer in verReconcileList:
                verReconcileList.remove(replicaVer)
        
        # Loop through the versionsList and delete versions that are no longer being referenced by a replica. 
        # Since these versions are no longer being referenced by a replica, we can assume it's safe to delete them.
        if len(verReconcileList) > 0:     #check to see that the list is not empty
            for version in verReconcileList:
                try:
                    arcpy.DeleteVersion_management(workspace, version)
                except:
                    print("Failed to delete version.")
                    print(arcpy.GetMessages(2))
        else:
            print("No versions to delete.")
    
    else:
        print("No versions to reconcile, aborting version maintenance routine.")

except:
    print(arcpy.GetMessages(2))
    
print("Done.")