def __call__(self):
"""
Query PA DB for jobs
"""
Session.set_database(dbConfig)
Session.connect()
Session.start_transaction()
sqlStr1 = \
"""
SELECT COUNT(id) FROM we_Job
WHERE job_type="Processing" AND status='inProgress';
"""
sqlStr2 = \
"""
SELECT COUNT(id) FROM we_Job
WHERE job_type="Merge" AND status='inProgress';
"""
sqlStr3 = \
"""
SELECT COUNT(id) FROM we_Job
WHERE job_type="CleanUp" AND status='inProgress';
"""
sqlStr4 = \
"""
SELECT COUNT(id) FROM we_Job
WHERE job_type="LogCollect" AND status='inProgress';
"""
sqlStr5 = \
"""
SELECT COUNT(id) FROM we_Job
WHERE job_type="Repack" AND status='inProgress';
"""
Session.execute(sqlStr1)
numProcessing = Session.fetchone()[0]
Session.execute(sqlStr2)
numMerge = Session.fetchone()[0]
Session.execute(sqlStr3)
numClean = Session.fetchone()[0]
Session.execute(sqlStr4)
numLog = Session.fetchone()[0]
Session.execute(sqlStr5)
numRepack = Session.fetchone()[0]
Session.close_all()
total = numProcessing + numMerge + numRepack
self['Total'] = total
self['Processing'] = numProcessing
self['Merge'] = numMerge
self['CleanUp'] = numClean
self['LogCollect'] = numLog
self['Repack'] = numRepack
return
def __directInsertJobSpecsWithSites(self, sitesList, *jobSpecDicts):
"""
__directInsertJobSpecs_
Insert entire list of job specs into DB
Kept private to be called by insertJobSpecs which
breaks list into manageable chunks
"""
sqlStr = \
"""INSERT INTO jq_queue (job_spec_id,
job_spec_file,
job_type,
workflow_id,
priority) VALUES
"""
numberOfJobs = len(jobSpecDicts)
for job in jobSpecDicts:
sqlStr += """( "%s", "%s", "%s", "%s", %s ) """ % (
job["JobSpecId"], job['JobSpecFile'],
job['JobType'], job['WorkflowSpecId'],
job['WorkflowPriority']
)
if job == jobSpecDicts[-1]:
sqlStr += ";"
else:
sqlStr += ",\n"
Session.execute(sqlStr)
Session.execute("SELECT LAST_INSERT_ID()")
firstJobIndex = Session.fetchone()[0]
sqlStr2 = "INSERT INTO jq_site (job_index, site_index) VALUES\n"
lastJobIndex = firstJobIndex + numberOfJobs
for jobIndex in range(firstJobIndex, lastJobIndex):
for siteIndex in sitesList:
sqlStr2 += " (%s, %s)" % (jobIndex, siteIndex)
if jobIndex == lastJobIndex -1:
if siteIndex == sitesList[-1]:
sqlStr2 += ";"
else:
sqlStr2 += ",\n"
else:
sqlStr2 += ",\n"
logging.debug(sqlStr2)
Session.execute(sqlStr2)
return
def getSiteForReleasedJob(self, job_spec_id):
"""
get resourceControl site id for given released jobspec
"""
# sqlStr = """SELECT jq_site.site_index FROM jq_queue, jq_site
# WHERE jq_queue.job_index = jq_site.job_index
# AND jq_queue.status = 'released'
# AND jq_queue.job_spec_id = '%s'""" % job_spec_id
sqlStr = """SELECT released_site FROM jq_queue
WHERE job_spec_id = '%s'""" % job_spec_id
Session.execute(sqlStr)
# by definition job can only be released for one site
return Session.fetchone()[0]
def countJobsForSite(self, siteIndex):
"""
_countJobsForSite_
Return a count of the number of jobs for a given site index
"""
sqlStr = \
"""
SELECT DISTINCT COUNT(jobQ.job_index) FROM jq_queue jobQ
LEFT OUTER JOIN jq_site siteQ
ON jobQ.job_index = siteQ.job_index WHERE status = 'new'
AND siteQ.site_index = %s """ % siteIndex
Session.execute(sqlStr)
result = Session.fetchone()[0]
return int(result)
def countJobsForWorkflow(self, workflow, jobType = None):
"""
_countJobsForWorkflow_
Return total job count for a given workflow, optionally
counts by type if provided.
"""
sqlStr = \
"""
SELECT DISTINCT COUNT(job_index) FROM jq_queue WHERE
workflow_id = \"%s\" AND status = 'new' """ % workflow
if jobType != None:
sqlStr += " AND job_type=\"%s\" " % jobType
sqlStr += ";"
Session.execute(sqlStr)
result = Session.fetchone()[0]
return int(result)
def selecOneNodeWithLeastJob():
"""
_selecOneNodeWithLeastJob_
Returns one node from jobEM_node_info table which contain the least number of running jobs
(HostID, HostName, number_jobs)
"""
sqlStr = \
"""
SELECT * FROM jobEM_node_info order by number_jobs ASC, host_id
"""
Session.execute(sqlStr)
result = Session.fetchone()
return result
def queueLength(self, jobType = None):
"""
_queueLength_
Return the total number of pending jobs of the type provided.
If type is not set, then all types are included
"""
sqlStr = \
"""
SELECT COUNT(job_index) FROM jq_queue WHERE status = 'new'
"""
if jobType != None:
sqlStr += " AND job_type=\"%s\" " % jobType
sqlStr += ";"
Session.execute(sqlStr)
result = Session.fetchone()
return int(result[0])
def queryNodeInfoByNodeID(hostID):
"""
_queryNodeInfoByNodeID_
Returns a node information. The item consists of the
following tuple:
(host_id, host_name, number_jobs)
"""
sqlStr = \
"""
SELECT * FROM jobEM_node_info WHERE host_id = %d
""" % hostID
Session.execute(sqlStr)
result = Session.fetchone()
#result = [ (x[0], x[1], x[2], x[3], x[4]) for x in result ]
return result
def migrateJobs():
connection = connect()
dbCur = connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)
#get list of known workflows
Session.execute("SELECT DISTINCT workflow_name FROM prodmon_Workflow", sessionID=db_id)
workflows = [removeTuple(workflow) for workflow in Session.fetchall(sessionID=db_id)]
dbCur.execute("""SELECT workflow_spec_id, job_spec_id, exit_code, status, site_name,
host_name, se_name, events_read, events_written, job_type, job_index, time FROM st_job_success""")
jobs = dbCur.fetchall()
dbCur.execute("""SELECT workflow_spec_id, job_spec_id, exit_code, status, site_name,
host_name, se_name, job_type, job_index, time FROM st_job_failure""")
jobs += dbCur.fetchall()
for job in jobs:
#save index for later queries
job_index = job["job_index"]
#create jobStatistics object and fill with values
stats = JobStatistics()
stats["workflow_spec_id"] = job["workflow_spec_id"]
stats["job_spec_id"] = job["job_spec_id"]
stats["exit_code"] = job["exit_code"]
stats["status"] = job["status"]
stats["site_name"] = job["site_name"]
stats["host_name"] = job["host_name"]
stats["se_name"] = job["se_name"]
stats["ce_name"] = "Unknown"
stats["job_type"] = job["job_type"]
stats["insert_time"] = job["time"]
#events missing for failures - set to 0
stats["events_read"] = job.get("events_read", 0)
stats["events_written"] = job.get("events_written", 0)
#verify job_spec_id exists
if stats["job_spec_id"] in (None, "", "None", "Unknown"):
continue
#if workflow_spec_id missing replace from job_spec_id
if stats["workflow_spec_id"] in (None, "", "None", "Unknown"):
#for merges remove sename-job-id
if stats["job_spec_id"].find("mergejob") > 0:
stats["workflow_spec_id"] = \
"-".join(stats["job_spec_id"].split("-")[:-3])
#handle node names that contain a - themselves
if stats["workflow_spec_id"].split("-")[-1].isalpha():
stats["workflow_spec_id"] = \
"-".join(stats["workflow_spec_id"].split("-")[:-1])
else:
#for normal jobs remove last -id field
stats["workflow_spec_id"] = \
"-".join(stats["job_spec_id"].split("-")[:-1])
#skip if from unknown workflow
if stats["workflow_spec_id"] not in workflows:
print "Skipping %s, workflow %s unknown" % (stats["job_spec_id"], \
stats["workflow_spec_id"])
continue
#get timings (for successful jobs)
if stats["exit_code"] == 0:
Session.execute("""SELECT attr_name, attr_value FROM st_job_attr WHERE
attr_class = "timing" AND job_index = %s;""" % addQuotes(job_index), sessionID=db_id)
timings = Session.fetchall()
for type, value in timings:
stats["timing"][type] = int(value.tostring()) #an array for some reason
#get job type
if stats["job_type"] in (None, "None"):
if stats["job_spec_id"].find("merge") > 0:
stats["job_type"] = "Merge"
else:
stats["job_type"] = "Processing"
#fake dashboard_id
#leave to be handled in exportToDashboard()
#add errors if a failure
if stats["exit_code"] != 0:
Session.execute("""SELECT error_type, error_desc FROM st_job_failure
WHERE job_index = %s""" % addQuotes(job_index), sessionID=db_id)
result = Session.fetchone(sessionID=db_id)
if result != None and result[0] != None and result[1] != None:
stats["error_type"] = result[0]
stats["error_desc"] = result[1].tostring()
#self.setdefault("task_name", None)
#save jobStatistics object
#print str(stats)
stats.insertIntoDB()
dbCur.close()
