Use Python scripting to batch reconcile and post versions

ArcGIS geodatabase administrators can use Python scripting to automate many version administration 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.

Many administrators will want to ensure that, when they are running their reconciliation, there are no other users connected to the database. Use the ArcPy functions ListUsers and DisconnectUser to ensure that only the administrator is connected to the geodatabase.

Find connected users

First, use the ListUsers function to determine who is connected to the geodatabase. Provide a geodatabase administrator connection to run the ListUsers function.

# get a list of connected users.
userList = arcpy.ListUsers("Database Connections/admin.sde")

Parse the list of connected users

Once you have a list of connected users, you can notify those users that they need to disconnect from the geodatabase. This can be done by getting a list of users and their associated email addresses.

For simplicity, this example assumes that each user connected to the geodatabase has the same base name as his or her email address. This example could be altered to have other methods for determining email addresses.

# get a list of user names from the list of named tuples returned from ListUsers
userNames = [u.Name for u in userList]

# take the userNames list and make email addresses by appending the appropriate suffix.
emailList = [name +  '@company.com' for name in userNames]

Generate and send an email

Use the email list to send emails to the users from Python to inform them they need to disconnect from the geodatabase. This example uses the smtplib module from Python, but there are other options for sending emails through nonstandard modules.

import smtplib
SERVER = "mailserver.yourcompany.com"
FROM = "SDE Admin <[e-mail]>"
TO = emailList
SUBJECT = "Maintenance is about to be performed"
MSG = "Auto generated Message.\n\rServer maintenance will be performed in 15 minutes. Please log off."

# Prepare actual message
MESSAGE = """\
From: %s
To: %s
Subject: %s

%s
""" % (FROM, ", ".join(TO), SUBJECT, MSG)

# Send the mail
server = smtplib.SMTP(SERVER)
server.sendmail(FROM, TO, MESSAGE)
server.quit()

Block connections to the geodatabase

Use the ArcPy function AcceptConnections to block connections to the geodatabase through scripting. This function is only available through Python scripting.

This prevents any new users from connecting to the geodatabase. Existing connections will still exist.

#block new connections to the database.
arcpy.AcceptConnections('Database Connections/admin.sde', False)

Note:

It is not necessary to block connections to the database or to disconnect all users to perform this maintenance. If your organization can accommodate having all connections disconnected, the compress process may be more efficient.

Pause the script

To give users time to finish up their work before disconnecting them, the script needs to pause for a specified amount of time. In this example, the time module in Python is used to give a 15-minute grace period before users are disconnected.

import time
time.sleep(900)#time is specified in seconds

Disconnect users

Use the ArcPy function DisconnectUser to disconnect users through scripting. This function is only available through Python scripting.

After the users have been notified and the script has paused for 15 minutes, all remaining users are disconnected.

#disconnect all users from the database.
arcpy.DisconnectUser('Database Connections/admin.sde', "ALL")

Note:

If you want only specific users to be disconnected, provide a string or Python list of strings of the connection IDs for those user connections. These IDs are returned from the ListUsers function.

Batch reconcile versions and post changes

Use the Reconcile Versions tool 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 achieves an effective compression, as it allows multiple versions to be reconciled and posted at once in an appropriate order. In this example, 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('Database Connections/admin.sde')

# Execute the ReconcileVersions tool.
arcpy.ReconcileVersions_management('Database Connections/admin.sde', "ALL_VERSIONS", "sde.DEFAULT", versionList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_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 (base) tables.

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

Allow connections to the geodatabase

Now that you have finished reconciling and posting versions and compressed the geodatabase, you can allow users to connect.

# Allow new connections to the database.
arcpy.AcceptConnections('Database Connections/admin.sde', True)

Rebuild indexes and update statistics

After performing a compression operation, it is recommended to 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 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. Note that in some database management systems, rebuilding indexes also updates the statistics. If the database management system you're using does this, you only need to rebuild indexes.

The first part to this process is to get a list of data and the users who own the data. This is necessary because indexes and statistics can only be updated by the data owner.

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.

# set the workspace 
arcpy.env.workspace = "C:\\Projects\\MyProject\\user1.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(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.
dataList = 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 + '.*'):
    dataList += arcpy.ListFeatureClasses(feature_dataset=dataset)

# Pass in the list of datasets owned by the connected user to the rebuild indexes 
# and update statistics on the data tables
arcpy.RebuildIndexes_management(workspace, "NO_SYSTEM", dataList, "ALL")
arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", dataList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE"

Note:

The wildcard tokens used to limit datasets owned by the user are database 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 + '.*').

Complete code example

The code example below puts all the above pieces together to perform the following operations as the geodatabase administrator:

• Identify connected users.
• Send an email notification.
• Prevent the geodatabase from accepting new connections.
• Disconnect users.
• Reconcile versions and post changes.
• Compress the geodatabase.
• Allow the geodatabase to begin accepting new connections.
• Rebuild indexes and update statistics on system tables.

import arcpy, time, smtplib

# Set the workspace 
arcpy.env.workspace = 'C:\\Projects\\MyProject\\admin.sde'

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

# Get a list of connected users.
userList = arcpy.ListUsers(adminConn)

# Get a list of user names of users currently connected and make email addresses
emailList = [user.Name + "@yourcompany.com" for user in arcpy.ListUsers(adminConn)]

# Take the email list and use it to send an email to connected users.
SERVER = "mailserver.yourcompany.com"
FROM = "SDE Admin <[e-mail]>"
TO = emailList
SUBJECT = "Maintenance is about to be performed"
MSG = "Auto generated Message.\n\rServer maintenance will be performed in 15 minutes. Please log off."

# Prepare actual message
MESSAGE = """\
From: %s
To: %s
Subject: %s

%s
""" % (FROM, ", ".join(TO), SUBJECT, MSG)

# Send the mail
print("Sending email to connected users")
server = smtplib.SMTP(SERVER)
server.sendmail(FROM, TO, MESSAGE)
server.quit()

# Block new connections to the database.
print("The database is no longer accepting connections")
arcpy.AcceptConnections(adminConn, False)

# Wait 15 minutes
time.sleep(900)

# Disconnect all users from the database.
print("Disconnecting all users")
arcpy.DisconnectUser(adminConn, "ALL")

# Get a list of versions to pass into the ReconcileVersions tool.
# Only reconcile versions that are children of Default
print("Compiling a list of versions to reconcile")
verList = arcpy.ListVersions(adminConn)
versionList = [ver.name for ver in verList if ver.parentVersionName == 'sde.DEFAULT']

# Execute the ReconcileVersions tool.
print("Reconciling all versions")
arcpy.ReconcileVersions_management(adminConn, "ALL_VERSIONS", "sde.DEFAULT", versionList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_VERSION", "c:/temp/reconcilelog.txt")

# Run the compress tool. 
print("Running compress")
arcpy.Compress_management(adminConn)

# Allow the database to begin accepting connections again
print("Allow users to connect to the database again")
arcpy.AcceptConnections(adminConn, True)

# Update statistics and indexes for the system tables
# Note: to use the "SYSTEM" option the user must be an geodatabase or database administrator.
# Rebuild indexes on the system tables
print("Rebuilding indexes on the system tables")
arcpy.RebuildIndexes_management(adminConn, "SYSTEM")

# Update statistics on the system tables
print("Updating statistics on the system tables")
arcpy.AnalyzeDatasets_management(adminConn, "SYSTEM")

print("Finished.")

Automated scheduling of the script

You can schedule to run the complete script at set intervals at a specific time using the operating system's task scheduler.

For instructions on setting up a scheduled task to run on Windows, see Scheduling a Python script to run at prescribed times.