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 field workers have synchronized edits made to versioned data.

Identify geodatabase versions based on service name

# 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 we 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

The Reconcile Versions tool can be used to reconcile and post all versions in an enterprise geodatabase. This tool provides options to reconcile all versions in the geodatabase to a target version (ALL_VERSIONS) or just 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 the versions can also be deleted by providing the DELETE_VERSION keyword. In this example, the tool is being 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 business 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 indexes are rebuilt and statistics are updated. These steps can be performed by using the Rebuild Indexes and Analyze Datasets tools. These tools allow you to input a list of datasets and will 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. The example above ('*.' + userName + '.*') will work for SQL Server, PostgreSQL, or DB2. For Oracle, the following wildcard can be used: (userName + '.*'). For Informix, the following wildcard can be used: ('*:' + userName + '.*') .

Once the list of data owned by the user is identified, it can be passed to the Rebuild Indexes and Analyze Datasets tools.

If you have multiple data owners, a data list would need to be generated for each data owner, and the Rebuild Indexes and Analyze Datasets tools would be run while connected 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 versions that are no longer referenced by a replica

# 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 we reconciled earlier.
# We 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 we 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 all the above pieces 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 is meant to better handle error cases. Also note that some of the comments and variable names have been changed in the following 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 that will be used to hold version names that we want 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 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)
        
        # 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 admin 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. 
        '''
        
        # Using 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.")