Using Python scripting to batch reconcile and post versions

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.

Many administrators will want to ensure that, when they are running their reconciliation, there are no other users connected to the database. The ArcPy functions ListUsers and DisconnectUser can be used 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. You must 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 will prevent 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 15 minutes. The time module in Python can be used to give a 15-minute grace period before the connected users will be 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, the 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

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. 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('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 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 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 = 'Database Connections/admin.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.
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)

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 = "Database Connections/user1.sde"

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

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

Complete code example

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

• 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 = 'Database Connections/admin.sde'

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

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

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

# 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
server = smtplib.SMTP(SERVER)
server.sendmail(FROM, TO, MESSAGE)
server.quit()

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

# Wait 15 minutes
time.sleep(900)

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

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

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

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

# Get a list of datasets owned by the admin user

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

Automated scheduling of the script

Once the script is completed, it can be scheduled to run 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.