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.

import arcpy, time, smtplib

# Set the workspace 
arcpy.env.workspace = '//connectionLocation/admin.sde'

# Set a variable for the workspace
workspace = arcpy.env.workspace

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

# 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")

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

# 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")


# Get a list of datasets owned by the data owner user (requires second connection file)

# Set the workspace 
arcpy.env.workspace = '//connectionLocation/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")

# set the workspace back to the admin 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 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:
        arcpy.DeleteVersion_management(workspace, version)