Using Python scripting to batch reconcile and post traditional 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 traditional versions.

Note:

This workflow is specific to traditional versioning. Branch versioning does not have adds and deletes tables like traditional versioning and therefore does not require many of the same administration tasks described in this topic.

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("C:\\Projects\\MyProject\\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('C:\\Projects\\MyProject\\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 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('C:\\Projects\\MyProject\\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.da.ListVersions('C:\\Projects\\MyProject\\admin.sde')

# Execute the ReconcileVersions tool.
arcpy.ReconcileVersions_management('C:\\Projects\\MyProject\\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('C:\\Projects\\MyProject\\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('C:\\Projects\\MyProject\\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, and then update the indexes and statistics as 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 feature classes 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. In the example above, ('*.' + userName + '.*') will work for SQL Server, PostgreSQL, or DB2. For Oracle, the following wildcard can be used: (userName + '.*').

Complete code example

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

• 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 email.
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.da.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

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.