def ship(self):
try:
# Run commands as user SDE to compress and analyze database and system tables
sdeconnection = join(self.garage, 'UGGP', 'sde@[email protected]')
self.log.info('arcpy.env.workspace: %s', arcpy.env.workspace)
self.log.info('connection: %s', sdeconnection)
description = arcpy.Describe(sdeconnection)
self.log.info('workspace full props: %s', description.fullPropsRetrieved)
self.log.info('workspace valid: %s', 'SdeWorkspace' in getattr(description, 'workspaceFactoryProgID', ''))
self.log.info('connection exists: %s', arcpy.Exists(sdeconnection))
arcpy.env.workspace = sdeconnection
try:
arcpy.Compress_management(sdeconnection)
self.log.info('Compress Complete')
except Exception as ex:
self.log.error('compress exception: %s', ex.message, exc_info=True)
arcpy.AnalyzeDatasets_management(sdeconnection, 'SYSTEM')
self.log.info('Analyze System Tables Complete')
userconnections = [join(self.garage, 'UGGP', 'uggpadmin@[email protected]')]
for con in userconnections:
# set workspace
# the user in this workspace must be the owner of the data to analyze.
workspace = con
# set the workspace environment
arcpy.env.workspace = workspace
# NOTE: Analyze Datasets can accept a Python list of datasets.
# 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() + arcpy.ListFeatureClasses() + arcpy.ListRasters()
# Next, for feature datasets get all of the datasets and featureclasses
# from the list and add them to the master list.
for dataset in arcpy.ListDatasets('', 'Feature'):
arcpy.env.workspace = join(workspace, dataset)
dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()
#: filter out topology items
dataList = [table for table in dataList if 'topology' not in table.lower()]
# reset the workspace
arcpy.env.workspace = workspace
# Execute analyze datasets
# Note: to use the 'SYSTEM' option the workspace user must be an administrator.
if len(dataList) > 0:
arcpy.AnalyzeDatasets_management(workspace, 'NO_SYSTEM', dataList, 'ANALYZE_BASE', 'ANALYZE_DELTA',
'ANALYZE_ARCHIVE')
self.log.info('Analyze Complete')
except Exception:
self.send_email('*****@*****.**', 'Error with {}'.format(__file__), format_exc())
raise
def ship(self):
try:
#: Run commands as user SDE to compress and analyze database and system tables
sdeconnection = join(self.garage, 'UEMP',
'sde@[email protected]')
arcpy.Compress_management(sdeconnection)
self.log.info('Compress Complete')
arcpy.AnalyzeDatasets_management(sdeconnection, 'SYSTEM')
self.log.info('Analyze System Tables Complete')
userconnections = [
join(self.garage, 'UEMP',
'uempadmin@[email protected]')
]
for con in userconnections:
# set workspace
# the user in this workspace must be the owner of the data to analyze.
workspace = con
# set the workspace environment
arcpy.env.workspace = workspace
# NOTE: Analyze Datasets can accept a Python list of datasets.
# 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() + arcpy.ListFeatureClasses(
) + arcpy.ListRasters()
# Next, for feature datasets get all of the datasets and featureclasses
# from the list and add them to the master list.
for dataset in arcpy.ListDatasets('', 'Feature'):
arcpy.env.workspace = join(workspace, dataset)
dataList += arcpy.ListFeatureClasses(
) + arcpy.ListDatasets()
# reset the workspace
arcpy.env.workspace = workspace
# Execute analyze datasets
# Note: to use the 'SYSTEM' option the workspace user must be an administrator.
if len(dataList) > 0:
arcpy.AnalyzeDatasets_management(workspace, 'NO_SYSTEM',
dataList, 'ANALYZE_BASE',
'ANALYZE_DELTA',
'ANALYZE_ARCHIVE')
self.log.info('Analyze Complete')
except Exception:
self.send_email('*****@*****.**',
'Error with {}'.format(__file__), format_exc())
raise
def compress_rebuild_analyze():
try:
log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") +
"Compressing database...\n")
arcpy.Compress_management(WORKSPACE_SDE)
log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") +
"Rebuilding indexes...\n")
arcpy.RebuildIndexes_management(WORKSPACE_SDE, "SYSTEM")
log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") +
"Analyzing datasets...\n")
arcpy.AnalyzeDatasets_management(WORKSPACE_SDE, "SYSTEM")
except Exception:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = datetime.datetime.now().strftime(
"%H:%M:%S %m/%d/%Y "
) + "An error has occured.\nTraceback info:\n" + tbinfo + "Error Info:\n" + " " + str(
sys.exc_info()[1])
if arcpy.GetMessages(2) != "":
msgs = "\n\n-ARCPY ERRORS-\n" + arcpy.GetMessages(
2) + "\n\nScript finished with errors."
else:
msgs = "\n\nScript finished with errors."
log.write(pymsg)
log.write(msgs)
log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") +
"Script failed. Recreating versions...\n")
recreate_versions()
log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") +
"Reaccepting connections...\n")
clean_up()
def analyze(self):
self.current_item += 1
self.itemPrint() # ↓
try:
arcpy.AnalyzeDatasets_management(self.sde_connection, "SYSTEM", "",
"ANALYZE_BASE", "ANALYZE_DELTA",
"ANALYZE_ARCHIVE")
self.itemPrint() # ↑
maintenance_log.appendToFile([
self.maintenance_item[self.current_item],
self.maintenance_status[1]
])
except Exception as e:
self.handleException(e)
def analyze_fc(conn_string, fc_target_fullnamewdb):
##print ("Entering Analyze Feature Class----")
print(" Analyze: {0}".format(fc_target_fullnamewdb))
try:
arcpy.AnalyzeDatasets_management(conn_string, "NO_SYSTEM",
fc_target_fullnamewdb, "ANALYZE_BASE",
"ANALYZE_DELTA", "ANALYZE_ARCHIVE")
print(" --Status: Success!\n")
except Exception as error_analyze:
print(" --Status: Failure!\n")
print(error_analyze.args[0])
##print ("----Leaving Analyze Feature Class")
return
def AnalyzeDatabaseStatistics(ConnectionNames):
'''
This function takes database connections and runs through
all of the ESRI tables and feature classes to update
their statistics. This significantly improves query times.
'''
for Connection in ConnectionNames:
env.workspace = Connection
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses()
for item in dataList:
# exclude tables not registered with Geodatabase
if 'DBO' in item:
arcpy.AnalyzeDatasets_management(connection, "SYSTEM", item,
"ANALYZE_BASE",
"ANALYZE_DELTA",
"NO_ANALYZE_ARCHIVE")
print item + " has been analyzed"
else:
pass
def RebuildGisIndexes(Connections):
'''
This function takes Geodatabase connections as inputs
(in the form of a list) and rebuilds the indexes for
all ESRI registered tables in the database.
'''
for Connection in Connections:
env.workspace = Connection
arcpy.Compress_management(Connection)
print Connection + " was compressed"
DataList = arcpy.ListTables() + arcpy.ListFeatureClasses()
GisDataList = []
for item in DataList:
# Exclude non-ESRI tables from having indexes updated
if 'DBO' in item:
GisDataList.append(item)
arcpy.RebuildIndexes_management(Connection, "SYSTEM", GisDataList,
"ALL")
arcpy.AnalyzeDatasets_management(Connection, "SYSTEM", GisDataList,
"ANALYZE_BASE", "ANALYZE_DELTA",
"ANALYZE_ARCHIVE")
print "Indexes and statistics for GIS tables in " + Connection + " have been rebuilt"
print "Script ran successfully"
# Get a list of versions to pass into the ReconcileVersions tool.
versionList = arcpy.ListVersions('Database Connections/[email protected]_EGDB_20170105A.sde')
# Execute the ReconcileVersions tool.
arcpy.ReconcileVersions_management('Database Connections/[email protected]_EGDB_20170105A.sde', "ALL_VERSIONS", "sde.DEFAULT", versionList, "LOCK_ACQUIRED", "ABORT_CONFLICTS", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", r"C:/Users/daniel.scott/Desktop/log.txt")
# Compress database
arcpy.Compress_management(workspace)
print 'Compression Complete'
# Get a list of stand alone feature classes
dataList = arcpy.ListFeatureClasses()
# Add Feature classes in datasets
for dataset in arcpy.ListDatasets("", "Feature"):
arcpy.env.workspace = os.path.join(workspace, dataset)
dataList += arcpy.ListFeatureClasses()
# reset the workspace
arcpy.env.workspace = workspace
# Execute rebuild indexes
arcpy.RebuildIndexes_management(workspace, "SYSTEM", dataList, "ALL")
print 'Rebuild Complete'
# Execute analyse indexes
arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", dataList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
print 'Analyze complete'
# Allow connections.
arcpy.AcceptConnections(workspace, True)
compressMsg = '\nCompress failed: error message below.\n\r' + arcpy.GetMessages(
)
#Update statistics and idexes for the system tables
# Note: to use the "SYSTEM" option the user must be an geodatabase or database administrator.
try:
print("Rebuilding indexes on the system tables")
arcpy.RebuildIndexes_management(adminConn, "SYSTEM")
rebuildSystemMsg = 'Rebuilding of system table indexes successful.\n\r'
except:
rebuildSystemMsg = 'Rebuild indexes on system tables fail: error message below.\n\r' + arcpy.GetMessages(
)
try:
print("Updating statistics on the system tables")
arcpy.AnalyzeDatasets_management(adminConn, "SYSTEM")
analyzeSystemMsg = 'Analyzing of system tables successful.\n\r'
except:
analyzeSystemMsg = 'Analyze system tables failed: error message below.\n\r' + arcpy.GetMessages(
)
# Allow connections again.
print("Allow users to connect to the database again")
arcpy.AcceptConnections(adminConn, True)
print("Finshed gdb admin user (sde) tasks \n")
#Get a list of datasets owned by the gdb user
print("Connecting to the geodatabase as the data owner (gdb)")
# Get the user name for the workspace
# this assumes you are using database authentication.
#dataowner admin tasks
try:
ownercon = r'C:\Scripts_2016\Dans_Scripts\Connection\Dataowner@[email protected]'
write_log(
"-----------------------------------------------------------", log)
write_log("ANALYSIS (No system)", log)
write_log(
"-----------------------------------------------------------", log)
write_log(
"\t Starting analysis of feature classes starting at {0}".format(
Time), log)
for fc in analysels:
try:
write_log("\t \t Analysing {0}".format(fc), log)
arcpy.AnalyzeDatasets_management(ownercon, "NO_SYSTEM", fc,
"ANALYZE_BASE",
"ANALYZE_DELTA",
"NO_ANALYZE_ARCHIVE")
write_log("\t \t Analysis complete " + arcpy.GetMessages(),
log)
analyzeUserMsg = 'Analyzing of user datasets successful.\n\r'
except:
write_log("\t \t Unable to analyse {0}".format(fc), log)
analyzeUserMsg = 'Analyze user datasets failed: error message below.\n\r' + arcpy.GetMessages(
)
write_log(analyzeUserMsg, log)
continue
write_log("analys user msg " + analyzeUserMsg, log)
except:
write_log("Unable to analyse fcs as dataowner", log)
# Get a list of datasets owned by the SDE
def ship(self):
try:
#: Run commands as user SDE to compress and analyze database and system tables
sdeconnection = join(self.garage, 'SGID10',
'SGID_sde@[email protected]')
arcpy.Compress_management(sdeconnection)
self.log.info('Compress Complete')
#: System table analyze was giving problems so it had to go for now.
# arcpy.AnalyzeDatasets_management(sdeconnection, 'SYSTEM')
# print 'Analyze System Tables Complete'
userconnections = [
join(self.garage, 'SGID10',
'SGID_Biosciense@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Boundaries@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Cadastre@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Climate@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Demographics@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Economy@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Elevation@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Energy@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Environment@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Farming@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Geoscience@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Health@[email protected]'),
join(self.garage, 'SGID10',
'SGID_History@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Indices@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Location@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Planning@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Political@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Raster@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Recreation@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Society@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Transportation@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Utilities@[email protected]'),
join(self.garage, 'SGID10',
'SGID_Water@[email protected]')
]
for con in userconnections:
# set workspace
# the user in this workspace must be the owner of the data to analyze.
workspace = con
# set the workspace environment
arcpy.env.workspace = workspace
# NOTE: Analyze Datasets can accept a Python list of datasets.
# 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() + arcpy.ListFeatureClasses(
) + arcpy.ListRasters()
# Next, for feature datasets get all of the datasets and featureclasses
# from the list and add them to the master list.
for dataset in arcpy.ListDatasets('', 'Feature'):
arcpy.env.workspace = join(workspace, dataset)
dataList += arcpy.ListFeatureClasses(
) + arcpy.ListDatasets('', 'Feature')
# reset the workspace
arcpy.env.workspace = workspace
# Get the user name for the workspace
userName = arcpy.Describe(
workspace).connectionProperties.user.lower()
# remove any datasets that are not owned by the connected user.
userDataList = [
ds for ds in dataList
if ds.lower().find('.%s.' % userName) > -1
]
# Execute analyze datasets
# Note: to use the 'SYSTEM' option the workspace user must be an administrator.
if len(dataList) > 0:
arcpy.AnalyzeDatasets_management(workspace, 'NO_SYSTEM',
userDataList,
'ANALYZE_BASE',
'ANALYZE_DELTA',
'ANALYZE_ARCHIVE')
self.log.info('Analyze Complete')
except Exception:
self.send_email('*****@*****.**',
'Error with {}'.format(__file__), format_exc())
raise
row_count = len(
cur.execute("select * from sde.sde_state_lineages").fetchall())
#print cur.execute("select * from sde.sde_state_lineages").fetchall()
print "SDE is at State " + str(row_count - 1)
cur.close
con.close
print "Compress successfully completed!"
print "Allow users to connect to " + str(sde)
arcpy.AcceptConnections(
sde, True) #allow the database to begin accepting connections again
print "Rebuilding indexes on the system tables"
arcpy.RebuildIndexes_management(
sde, "SYSTEM") #rebuild indexes on the system tables
print "Updating statistics on the system tables"
arcpy.AnalyzeDatasets_management(sde, "SYSTEM")
print ""
print "Generating Asset IDs..."
print ""
except arcpy.ExecuteError:
print ""
print(arcpy.GetMessages(2))
print ""
raw_input("Compression failure. Press ENTER to exit.")
quit()
#Generate Asset IDs
try:
#Feature Class and infrastructure prefix lists need to be insync ordinally
fclist = [
"water_test.WSDTEST.wHydrant", "water_test.WSDTEST.wSystemValve",
logger.info('gdb not accepting connections')
arcpy.AcceptConnections(admin, False)
# Compress GDB
print('Compressing Geodatabase')
logger.info('Compressing Geodatabase')
compress_result = arcpy.Compress_management(admin)
print('Compress message: ' + compress_result.getMessage(2))
logger.info('Compress message: ' + compress_result.getMessage(2))
# Rebuild Indexes
print('Rebuilding Indexes')
logger.info('Rebuilding Indexes')
rebuild_result = arcpy.RebuildIndexes_management(admin, 'SYSTEM')
print('Rebuild result: ' + rebuild_result.getMessage(2))
logger.info('Rebuild result: ' + rebuild_result.getMessage(2))
# Update GDB statistics using Analyze
print('Updating GDB statistics')
logger.info('Updating GDB statistics')
analyze_result = arcpy.AnalyzeDatasets_management(admin, 'SYSTEM')
print('Analyze message: ' + analyze_result.getMessage(2))
logger.info('Analyze message: ' + analyze_result.getMessage(2))
# Accept connections on geodatabase
arcpy.AcceptConnections(admin, True)
print('gdb accepting connections')
logger.info('gdb accepting connections')
print('Done. Maintenance Complete')
def mainFunction(geodatabase,disconnectUsers): # Get parameters from ArcGIS Desktop tool by seperating by comma e.g. (var1 is 1st parameter,var2 is 2nd parameter,var3 is 3rd parameter)
try:
# --------------------------------------- Start of code --------------------------------------- #
# If disconnecting users
if (disconnectUsers == "true"):
# Block any new connections to the geodatabase
arcpy.AcceptConnections(geodatabase, False)
arcpy.AddMessage("Disconnecting all users from " + geodatabase + "...")
# Logging
if (enableLogging == "true"):
logger.info("Disconnecting all users from " + geodatabase + "...")
arcpy.DisconnectUser(geodatabase, "ALL")
# Compress the geodatabase
arcpy.AddMessage("Compressing geodatabase - " + geodatabase + "...")
# Logging
if (enableLogging == "true"):
logger.info("Compressing geodatabase - " + geodatabase + "...")
arcpy.env.workspace = geodatabase
arcpy.Compress_management(geodatabase)
# Load in datasets to a list
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListDatasets()
# Load in datasets from feature datasets to the list
for dataset in arcpy.ListDatasets("", "Feature"):
arcpy.env.workspace = os.path.join(geodatabase,dataset)
dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()
# Reset the workspace
arcpy.env.workspace = geodatabase
# Get the user name for the workspace
userName = arcpy.Describe(geodatabase).connectionProperties.user.lower()
# Remove any datasets that are not owned by the connected user.
userDataList = [ds for ds in dataList if ds.lower().find(".%s." % userName) > -1]
# Execute rebuild indexes
arcpy.AddMessage("Rebuilding the indexes for all tables in the database....")
# Logging
if (enableLogging == "true"):
logger.info("Rebuilding the indexes for all tables in the database....")
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.RebuildIndexes_management(geodatabase, "SYSTEM", userDataList, "ALL")
# Execute analyze datasets
arcpy.AddMessage("Analyzing and updating the database statistics....")
# Logging
if (enableLogging == "true"):
logger.info("Analyzing and updating the database statistics....")
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.AnalyzeDatasets_management(geodatabase, "SYSTEM", userDataList, "ANALYZE_BASE","ANALYZE_DELTA","ANALYZE_ARCHIVE")
# If disconnecting users
if (disconnectUsers == "true"):
# Allow any new connections to the geodatabase
arcpy.AddMessage("Allowing all users to connect to " + geodatabase + "...")
# Logging
if (enableLogging == "true"):
logger.info("Allowing all users to connect to " + geodatabase + "...")
arcpy.AcceptConnections(geodatabase, True)
# --------------------------------------- End of code --------------------------------------- #
# If called from gp tool return the arcpy parameter
if __name__ == '__main__':
# Return the output if there is any
if output:
arcpy.SetParameterAsText(1, output)
# Otherwise return the result
else:
# Return the output if there is any
if output:
return output
# Logging
if (enableLogging == "true"):
# Log end of process
logger.info("Process ended.")
# Remove file handler and close log file
logMessage.flush()
logMessage.close()
logger.handlers = []
# If arcpy error
except arcpy.ExecuteError:
# Build and show the error message
errorMessage = arcpy.GetMessages(2)
arcpy.AddError(errorMessage)
# Logging
if (enableLogging == "true"):
# Log error
logger.error(errorMessage)
# Log end of process
logger.info("Process ended.")
# Remove file handler and close log file
logMessage.flush()
logMessage.close()
logger.handlers = []
if (sendErrorEmail == "true"):
# Send email
sendEmail(errorMessage)
# If python error
except Exception as e:
errorMessage = ""
# Build and show the error message
for i in range(len(e.args)):
if (i == 0):
# Python version check
if sys.version_info[0] >= 3:
# Python 3.x
errorMessage = str(e.args[i]).encode('utf-8').decode('utf-8')
else:
# Python 2.x
errorMessage = unicode(e.args[i]).encode('utf-8')
else:
# Python version check
if sys.version_info[0] >= 3:
# Python 3.x
errorMessage = errorMessage + " " + str(e.args[i]).encode('utf-8').decode('utf-8')
else:
# Python 2.x
errorMessage = errorMessage + " " + unicode(e.args[i]).encode('utf-8')
arcpy.AddError(errorMessage)
# Logging
if (enableLogging == "true"):
# Log error
logger.error(errorMessage)
# Log end of process
logger.info("Process ended.")
# Remove file handler and close log file
logMessage.flush()
logMessage.close()
logger.handlers = []
if (sendErrorEmail == "true"):
# Send email
sendEmail(errorMessage)
""" Name: UStatsSysTbls.py Description: Updates statistics on enterprise geodatabase system tables using an existing .sde file. Author: Esri """ # Import system modules import arcpy, os # set workspace workspace = arcpy.GetParameterAsText(0) # set the workspace environment arcpy.env.workspace = workspace # Execute analyze datasets for system tables arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", "", "","","") print "Analyze Complete"
def mainFunction(
geodatabase
): # Get parameters from ArcGIS Desktop tool by seperating by comma e.g. (var1 is 1st parameter,var2 is 2nd parameter,var3 is 3rd parameter)
try:
# Log start
if logInfo == "true":
loggingFunction(logFile, "start", "")
# --------------------------------------- Start of code --------------------------------------- #
# Compress the geodatabase
arcpy.AddMessage("Compressing the database....")
arcpy.env.workspace = geodatabase
arcpy.Compress_management(geodatabase)
# Load in datsets to a list
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses(
) + arcpy.ListDatasets()
# Load in datasets from feature datasets to the list
for dataset in arcpy.ListDatasets("", "Feature"):
arcpy.env.workspace = os.path.join(geodatabase, dataset)
dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()
# Reset the workspace
arcpy.env.workspace = geodatabase
# Get the user name for the workspace
userName = arcpy.Describe(
geodatabase).connectionProperties.user.lower()
# Remove any datasets that are not owned by the connected user.
userDataList = [
ds for ds in dataList if ds.lower().find(".%s." % userName) > -1
]
# Execute analyze datasets
arcpy.AddMessage("Analyzing and updating the database statistics....")
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.AnalyzeDatasets_management(geodatabase, "SYSTEM", userDataList,
"ANALYZE_BASE", "ANALYZE_DELTA",
"ANALYZE_ARCHIVE")
# Execute rebuild indexes
arcpy.AddMessage(
"Rebuilding the indexes for all tables in the database....")
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.RebuildIndexes_management(geodatabase, "SYSTEM", userDataList,
"ALL")
# --------------------------------------- End of code --------------------------------------- #
# If called from gp tool return the arcpy parameter
if __name__ == '__main__':
# Return the output if there is any
if output:
arcpy.SetParameterAsText(1, output)
# Otherwise return the result
else:
# Return the output if there is any
if output:
return output
# Log start
if logInfo == "true":
loggingFunction(logFile, "end", "")
pass
# If arcpy error
except arcpy.ExecuteError:
# Show the message
arcpy.AddError(arcpy.GetMessages(2))
# Log error
if logInfo == "true":
loggingFunction(logFile, "error", arcpy.GetMessages(2))
# If python error
except Exception as e:
# Show the message
arcpy.AddError(e.args[0])
# Log error
if logInfo == "true":
loggingFunction(logFile, "error", e.args[0])
'*.' + 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)
# 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(connSDE, "SYSTEM", dataList, "ALL")
arcpy.AnalyzeDatasets_management(connSDE, "SYSTEM", dataList,
"ANALYZE_BASE", "ANALYZE_DELTA",
"ANALYZE_ARCHIVE")
###-------------------------------------------------------------
### Rebuild indices and update statistics for second data owner gisadmin user
###-------------------------------------------------------------
# set the workspace
env.workspace = connGISADMIN
# 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(env.workspace).connectionProperties.user
# Get a list of all the datasets the user has access to.
acquire_locks="LOCK_AQUIRED",
abort_if_conflicts="NO_ABORT",
conflict_definition="BY_OBJECT",
conflict_resolution="FAVOR_TARGET_VERSION",
with_post="POST",
with_delete="KEEP_VERSION",
out_log=os.path.join(file_loc, 'reconcile_log'))
# Compress database and reopen to outside connections
logging.info('Compressing database...')
arcpy.Compress_management(SDE)
arcpy.AcceptConnections(SDE, True)
logging.debug('Open to connections')
except:
print("There was an error!")
logging.error(
"An error has occured while reconciling and compressing versions",
exc_info=True)
arcpy.AcceptConnections(SDE, True)
# Rebuild indexes and update statistics
try:
logging.info("Rebuilding indexes...")
arcpy.RebuildIndexes_management(SDE, "SYSTEM")
logging.info("Updating statistics")
arcpy.AnalyzeDatasets_management(SDE, "SYSTEM")
except:
logging.error(
"An error has occured while rebuilding indexes and updating statistics.",
exc_info=True)
logging.info("SCRIPT COMPLETE")
oDataList = arcpy.ListTables(
'*.' + userName + '.*') + arcpy.ListFeatureClasses(
'*.' + userName + '.*') + arcpy.ListRasters('*.' + userName +
'.*')
for dataset in arcpy.ListDatasets('*.' + userName + '.*'):
oDataList += arcpy.ListFeatureClasses(feature_dataset=dataset)
LogFile.write("Tables owned by " + userName + ":," + str(oDataList) +
",\n")
arcpy.AddMessage("Rebuilding indexes for " + DB + "\n")
LogFile.write("Rebuilding indexes for," + DB + "\n")
arcpy.RebuildIndexes_management(arcpy.env.workspace, "NO_SYSTEM",
oDataList, "ALL")
arcpy.AddMessage("Analyzing data for " + DB + "\n")
LogFile.write("Analyzing data for," + DB + "\n")
arcpy.AnalyzeDatasets_management(arcpy.env.workspace, "NO_SYSTEM",
oDataList, "ANALYZE_BASE",
"ANALYZE_DELTA", "ANALYZE_ARCHIVE")
except arcpy.ExecuteError:
msgs = arcpy.GetMessages(2)
arcpy.AddError(msgs)
arcpy.AddMessage(msgs)
LogFile.write(msgs)
ErrorCount = ErrorCount + 1
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(
sys.exc_info()[1])
msgs = arcpy.GetMessages(2) + "\n"
arcpy.AddError(pymsg)
arcpy.AddError(msgs)
LogMessage("Running compress")
arcpy.Compress_management(adminConn)
#if the compress is successful add a message.
LogMessage('Compress was successful.')
except:
#If the compress failed, add a message.
LogMessage('\nCompress failed: error message below.' + arcpy.GetMessages())
#Update statistics and idexes for the system tables
# Note: to use the "SYSTEM" option the user must be an geodatabase or database administrator.
try:
LogMessage("Rebuilding indexes on the system tables")
arcpy.RebuildIndexes_management(adminConn, "SYSTEM")
LogMessage('Rebuilding of system table indexes successful.')
except:
LogMessage(
'Rebuild indexes on system tables fail: error message below.\n\r' +
arcpy.GetMessages())
try:
LogMessage("Updating statistics on the system tables")
arcpy.AnalyzeDatasets_management(adminConn, "SYSTEM")
LogMessage('Analyzing of system tables successful.')
except:
LogMessage('Analyze system tables failed: error message below.\n\r' +
arcpy.GetMessages())
# Allow connections again.
LogMessage("Allow users to connect to the database again")
arcpy.AcceptConnections(adminConn, True)
LogMessage("Finshed gdb admin user (sde) tasks \n")
else:
slashsyntax = "/"
if os.environ.get("TMP") == None:
temp = "/usr/tmp"
else:
temp = os.environ.get("TMP")
Connection_File_Name = temp + slashsyntax + "connection.sde"
# Check for the .sde file and delete it if present
if os.path.exists(Connection_File_Name):
os.remove(Connection_File_Name)
#Variable defined within the script; other variable options commented out at the end of the line
saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
print "Creating ArcSDE Connection File..."
# Create ArcSDE Connection File
# Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server,
service, database,
account_authentication, username,
password, saveUserInfo, version,
saveVersionInfo)
# Update statistics on system tables
arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM", "", "", "",
"")
print "Analyze Complete"
#compress/rebuild/analyze SDE Databases import arcpy db='C:\SDEConnectionFiles\GIS\[email protected]' arcpy.env.workspace = 'C:\SDEConnectionFiles\GIS\[email protected]' ##arcpy.AcceptConnections(db, False) ##arcpy.DisconnectUser(db, "ALL") ##arcpy.Compress_management(db) ##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() + arcpy.ListFeatureClasses() + arcpy.ListRasters() exceptions = [u'gis.transportation.bicyclecounts',u'gis.transportation.pedestriancounts',u'gis.transportation.trafficcounts',u'gis.transportation.lts_for_ruti',u'gis.transportation.tc_app'] data = set(dataList) - set(exceptions) d = list(data) ### 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(): ## dataList += arcpy.ListFeatureClasses(feature_dataset=dataset) arcpy.RebuildIndexes_management(db, "SYSTEM", d, "ALL") arcpy.AnalyzeDatasets_management(db, "SYSTEM", d, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE") ## #arcpy.AcceptConnections(db, True)
versions[i], "PUBLIC")
log.write(
str(time.asctime()) + ": " + fullNames[i] + " version created.\n")
# Rebuild indexes
arcpy.RebuildIndexes_management(
sewerman, "NO_SYSTEM", "SDE.SEWERMAN.MANHOLES_VIEW; "
"SDE.SEWERMAN.SEWERS_VIEW; "
"SDE.SEWERMAN.ProjectAreas; "
"SDE.SEWERMAN.REPAVING", "ALL")
log.write(str(time.asctime()) + ": Indexes rebuilt.\n")
# Analyze datasets
arcpy.AnalyzeDatasets_management(
sewerman, "NO_SYSTEM", "SDE.SEWERMAN.MANHOLES_VIEW; "
"SDE.SEWERMAN.SEWERS_VIEW; "
"SDE.SEWERMAN.ProjectAreas; "
"SDE.SEWERMAN.REPAVING", "ANALYZE_BASE", "NO_ANALYZE_DELTA",
"ANALYZE_ARCHIVE")
log.write(str(time.asctime()) + ": Analyze complete.\n")
# Send "success" email.
successSubj = "Script Completed"
successContent = "Compress script completed"
successPitcher = "*****@*****.**"
successCatchers = "*****@*****.**"
send_email(successPitcher, successCatchers, successSubj, successContent)
except Exception, e:
log.write(
str(time.asctime()) + ": " + str(e) + "- Script failed to complete.\n")
arcpy.AcceptConnections(sdeWorkspace, True)
