ArcGIS Help 10.1 - 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 database.

Finding connected users

The first step toward disconnecting users is to determine who is connected to the database. The ListUsers function is used, passing in an administrator connection.

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

Parsing the list of connected users

Once the list of connected users has been created, it can be used to notify users that they need to disconnect. This can be done by getting a list of users and their associated e-mail addresses.

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

# get a list of usernames 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]

Generating and sending an e-mail

Once an e-mail list has been generated, an e-mail can be sent from Python. This example uses the smtplib module from Python, but there are other options for sending e-mails through nonstandard modules.

Note:

For more information on the smtplib module, see the Python documentation at

http://docs.python.org/.

import smtplib
SERVER = "mailserver.yourcompany.com"
FROM = "SDE Admin <python@yourcompany.com>"
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()

Blocking connections to the database

The workflow to block connections to the database through scripting is to use the ArcPy function AcceptingConnections. 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.

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

Pausing 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

Disconnecting users

The workflow to disconnect users through scripting is to use the ArcPy function DisconnectUser. 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:

Alternatively, if you want only specific users to be disconnected, the ID returned from the ListUsers function or connected users dialog box can be provided as a string or Python list of strings to disconnect appropriate users.

Batch reconcile

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 or just versions that are pinning default. 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.

Learn more about the Reconcile Versions geoprocessing tool

# 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, it is important to compress the database to remove any redundant information and move edits into the business tables.

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

Rebuilding indexes and updating 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 a list of input datasets to be entered and will perform their functions on all the datasets at once. These tools also update statistics and rebuild indexes for appropriate system tables. The first part to this process is to get a list of data that the user owns. Indexes and statistics can only be updated by the owner of the data or users who have been granted certain admin privileges.

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

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

Note:

For simplicity this script assumes that the administrative (admin) user is also the data owner. If you have multiple data owners, a data list would need to be generated for each data owner and passed to the RebuildIndexes and AnalyzeDatasets tools.

Note:

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

# Execute rebuild indexes and analyze datasets
# Note: to use the "SYSTEM" option the user must be an administrator.

workspace = "Database Connections/admin.sde"

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

arcpy.AnalyzeDatasets_management(workspace, "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:

Identifies connected users
Sends an e-mail notification
Prevents the database from accepting new connections
Disconnects users
Reconciles versions
Compresses the database
Rebuilds indexes and gathers statistics
Allows the database to begin accepting new connections

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 usernames of users currently connected and make email addresses
emailList = [u.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 <python@yourcompany.com>"
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

# 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.
dataList = arcpy.ListTables('*.' + userName + '.*') + arcpy.ListFeatureClasses('*.' + userName + '.*') + arcpy.ListRasters('*.' + userName + '.*')

# Next, for feature datasets get all of the featureclasses
# from the list 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 admin to the rebuild indexes and analyze datasets tools
# Note: to use the "SYSTEM" option the user must be an administrator.
arcpy.RebuildIndexes_management(workspace, "SYSTEM", dataList, "ALL")

arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", dataList, "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.

7/30/2013