def get_job_stream_dependency(self, sequence_name):
recursive_job_list = []
conf = ReadConfig()
job_list = conf.Read_job_list()
for job in job_list:
if job['DEPENDENCY_JOB'] == sequence_name:
recursive_job_list.append(job['JOB_NAME'])
if job['JOB_TYPE'] == 'Sequence':
job_list_2 = conf.Read_job_list()
for job_2 in job_list_2:
if job_2['DEPENDENCY_JOB'] == job['JOB_NAME']:
recursive_job_list.append(job_2['JOB_NAME'])
print("Dependent job list" + str(recursive_job_list))
return recursive_job_list
def get_dependency_job_list(sequence_name):
conf = ReadConfig()
job_list = conf.Read_job_list()
job_stream_list = []
for job in job_list:
if job['DEPENDENCY_JOB'] == sequence_name:
'''there's defect that we not consider the jobs which has no job_id'''
if job['JOB_ID'] != '' and job['ASCA_CONTROL_POINT_ID'] != '':
job_stream = job['JOB_NAME'] + "@" + job['JOB_ID'] + "@" + job[
'ASCA_CONTROL_POINT_ID']
else:
job_stream = job['JOB_NAME']
job_stream_list.append(job_stream)
return job_stream_list
def iwefresh_positive_test(self, iwrefresh_db_node, iwrefresh_uid,
iwrefresh_pwd):
'''read config file for the job stream'''
conf = ReadConfig()
iwrefresh_db_conf = conf.Read_db_config(iwrefresh_db_node)
job_list = conf.Read_job_list()
job_status_report = conf.Read_job_status_report()
job_iw_control_report = []
iw_refresh_failed_count = 0
for job in job_list:
data_group = job['DATAGROUP_ID']
iw_refresh_control_status = dict()
if data_group != '':
job_name = job['JOB_NAME']
job_run_status = job_status_report[job_name]['Job Status']
job_start_time = self.job_status_time_transfer(
job_status_report[job_name]['Job Start Time'])
job_end_time = self.job_status_time_transfer(
job_status_report[job_name]['Last Run Time'])
'''step 1 validate if the parallel job show pass'''
print("step 1: validate the run status of job: %s ..." % (job_name) +" \nstart time:" +str(job_start_time)+"\nend time:"\
+ str(job_end_time))
print(
"----------------------------------------------------------------------------------"
)
if job_run_status == 'RUN OK (1)' or job_run_status == 'RUN OK (1)':
print(
" the job status is %s,the status check passed,go to next step"
% job_run_status)
else:
print("The DataGroup Related job status is failed")
raise TestException.IWRefreshError()
'''step 2 validate if the IW Refresh record shows pass and the IW Refresh start time big than job start time,
the end time is less than the end time '''
'''get the bludb conf, and trige the db to run the sql'''
print("step2: Get iw refresh status from iwrefresh db...")
print(
"----------------------------------------------------------------------------------"
)
print("Data group: %s" % data_group)
sql = "select B.DATAGROUP_NM as DATAGROUP_NM,A.STAT as STAT,A.LOAD_START as LOAD_START," \
"A.LOAD_END as LOAD_END from XLSSTG.LOAD_STATUS A inner join XLSSTG.DATA_GROUP B on " \
"A.DATAGROUP_ID = B.DATAGROUP_ID " \
"where B.DATAGROUP_NM= '{}\' AND A.LOAD_START > '{}' ".format(data_group, job_start_time)
rs = db_connect.exec_sql_common(iwrefresh_db_node,
iwrefresh_uid, iwrefresh_pwd,
sql)
print("RUNNING QUERY:%s" % sql)
if len(rs) != 0:
iwrefresh_status = rs[0]['STAT']
iwrefresh_start_time = rs[0]['LOAD_START']
iwrefresh_end_time = rs[0]['LOAD_END']
'''gather info to report'''
iw_refresh_control_status['JOB_NM'] = job_name
iw_refresh_control_status['JOB_START_TIME'] = str(
job_start_time)
iw_refresh_control_status['JOB_END_TIME'] = str(
job_end_time)
iw_refresh_control_status['DATA_GROUP_NM'] = data_group
iw_refresh_control_status['IWREFRESH_START_TIME'] = str(
iwrefresh_start_time)
iw_refresh_control_status['IWREFRESH_END_TIME'] = str(
iwrefresh_end_time)
iw_refresh_control_status[
'IWREFRESH_STATUS_'] = iwrefresh_status
job_iw_control_report.append(iw_refresh_control_status)
print("IWRefresh status: %s" % iwrefresh_status)
'''1. check the iw refresh status'''
if iwrefresh_status == 'COMPLETE':
print(
'''\nWhen the job %s run finished, The data group \"%s\" shows \"%s\" ,the IW Refresh test passed
''' % (job_name, data_group, iwrefresh_status))
else:
iw_refresh_failed_count += 1
print(
'''When the job %s run finished, The data group \"%s\" shows \"%s\" ,
the IW Refresh test passed''' %
(job_name, data_group, iwrefresh_status))
else:
iw_refresh_failed_count += 1
print(
"The IW Refresh control not be triggered when the job %s start, the IW Refresh test failed"
% (job_name))
'''generate the iw refresh report'''
iw_report = Generate_report()
iw_report.write_iwefresh_status_to_report(job_iw_control_report)
iw_report.generate_iwrefresh_positive_report()
if iw_refresh_failed_count != 0:
print("one or more table's IWRefresh control failed, "
"check the iw_refresh_positive_test_report.xls for detail")
raise TestException.IWRefreshError()
def asca_test(self,asca_db_node,zos_user,zos_pwd):
'''read config file for the job stream'''
conf = ReadConfig()
job_list = conf.Read_job_list()
job_status_report = conf.Read_job_status_report()
asca_control_test_report =[]
'''Get asca_control_record through jdbc, store the result to the asca_control_dict'''
'''1. get the asca control id list'''
asca_control_id_list =[]
for job in job_list:
if job['ASCA_CONTROL_POINT_ID'] != '':
asca_control_id_list.append(job['ASCA_CONTROL_POINT_ID'])
asca_control_id_string = str(asca_control_id_list).strip('[').strip(']')
'''2.generate the sql query'''
print("Step 1: Get asca control result from ASCA.ASCA_CNTROL_RECORD table")
query = "select JOB_ID,ASCA_CNTL_PT_ID,SRC_ROW_CNT,TRGT_ROW_CNT,\
SRC_CNTL_AMT,TRGT_CNTL_AMT,ASCA_CNTL_REC_ID,CNTL_STAT,\
ASCA_CNTL_RUN_DT,ASCA_CNTL_TMS from(\
SELECT RANK() OVER(PARTITION BY ASCA_CNTL_PT_ID ORDER BY ASCA_CNTL_TMS DESC)\
AS RANK_NUM,JOB_ID,ASCA_CNTL_PT_ID,SRC_ROW_CNT,TRGT_ROW_CNT,\
SRC_CNTL_AMT,TRGT_CNTL_AMT,ASCA_CNTL_REC_ID,CNTL_STAT,\
ASCA_CNTL_RUN_DT,ASCA_CNTL_TMS from ASCA.ASCA_CONTROL_RECORD) AA WHERE AA.RANK_NUM=1\
and AA.ASCA_CNTL_PT_ID in({})".format(asca_control_id_string)
print("\tQuery:"+query)
'''3. Trigger jdbc driver to query the data'''
asca_control_result = db_connect.exec_sql_with_jdbc(asca_db_node, zos_user, zos_pwd, query)
#print(asca_control_result)
print("\tQuery running completed")
print("Step 2: start the validation the asca control result...")
'''For each job, link the job name with the asca_control_result,perform validation and generate the report'''
for job in job_list:
if job['ASCA_CONTROL_POINT_ID'] != '':
job_name = job['JOB_NAME']
job_asca_cntl_pt_id =job['ASCA_CONTROL_POINT_ID']
job_run_status = job_status_report[job_name]['Job Status']
job_last_run_time = self.job_status_time_transfer(job_status_report[job_name]['Last Run Time'])
print("\tValidated Job Name:"+job_name)
'''step 1 validate if the parallel job show Complete'''
if job_run_status == 'RUN OK (1)' or job_run_status == 'RUN OK (1)':
print("\t\tJob Status:" + job_run_status)
else :
print("Job Status:" + job_run_status)
print("The parallel status validate date is failed,the test case not pass")
raise TestException.ASCAControlError()
'''step 2 validate if the ASCA Control record shows pass and the ASCA CONTORL TMS is after the job last run time'''
''' get the asca control result from jdbc_query,with the same asca_control_pt_id'''
exist_flag = False
for asca_control_record in asca_control_result:
if asca_control_record['ASCA_CNTL_PT_ID'] == job_asca_cntl_pt_id:
#print(asca_control_record['ASCA_CNTL_PT_ID']+"vs"+job_asca_cntl_pt_id )
asca_control_test_report_row = dict()
exist_flag = True
asca_control_tms = datetime.datetime.strptime(asca_control_record['ASCA_CNTL_TMS'][0:19], "%Y-%m-%d %H:%M:%S")
if asca_control_tms > job_last_run_time:
asca_control_test_report_row['ASCA_CNTL_PT_ID'] = job_asca_cntl_pt_id
asca_control_test_report_row['JOB_NAME'] = job_name
asca_control_test_report_row['JOB_STATUS'] = job_run_status
asca_control_test_report_row['JOB_LAST_RUN_TIME'] = str(job_last_run_time)
asca_control_test_report_row['SOURCE_ROW_COUNT'] = asca_control_record['SRC_ROW_CNT']
asca_control_test_report_row['TARGET_ROW_COUNT'] = asca_control_record['TRGT_ROW_CNT']
asca_control_test_report_row['ASCA_CONTROL_STATUS'] = asca_control_record['CNTL_STAT']
asca_control_test_report_row['ASCA_CONTROL_TMS'] = asca_control_record['ASCA_CNTL_TMS']
asca_control_test_report_row['ASCA_TEST_RESULT'] = asca_control_record['CNTL_STAT']
else:
asca_control_test_report_row['ASCA_CNTL_PT_ID'] = job_asca_cntl_pt_id
asca_control_test_report_row['JOB_NAME'] = job_name
asca_control_test_report_row['JOB_STATUS'] = job_run_status
asca_control_test_report_row['JOB_LAST_RUN_TIME'] = str(job_last_run_time)
asca_control_test_report_row['SOURCE_ROW_COUNT'] = 'NULL'
asca_control_test_report_row['TARGET_ROW_COUNT'] = 'NULL'
asca_control_test_report_row['ASCA_CONTROL_STATUS'] = 'NULL'
asca_control_test_report_row['ASCA_CONTROL_TMS'] = 'NULL'
asca_control_test_report_row['ASCA_TEST_RESULT'] = 'FAIL'
asca_control_test_report.append(asca_control_test_report_row)
print("\t\tASCA_CONTROL_POINT_ID:" + asca_control_test_report_row['ASCA_CNTL_PT_ID'])
print("\t\tSOURCE_TABLE_ROW_COUNT:" + asca_control_test_report_row['SOURCE_ROW_COUNT'])
print("\t\tTARGET_TABLE_ROW_COUNT" + asca_control_test_report_row['TARGET_ROW_COUNT'])
print("\t\tRow Count Validate result:" + asca_control_test_report_row['ASCA_TEST_RESULT'])
#print("When the control id is"+job_asca_cntl_pt_id+ "asca_control_test_report"+str(asca_control_test_report))
if exist_flag == False:
asca_control_test_report_row = dict()
asca_control_test_report_row['ASCA_CNTL_PT_ID'] = job_asca_cntl_pt_id
asca_control_test_report_row['JOB_NAME'] = job_name
asca_control_test_report_row['JOB_STATUS'] = job_run_status
asca_control_test_report_row['JOB_LAST_RUN_TIME'] = str(job_last_run_time)
asca_control_test_report_row['SOURCE_ROW_COUNT'] = 'NULL'
asca_control_test_report_row['TARGET_ROW_COUNT'] = 'NULL'
asca_control_test_report_row['ASCA_CONTROL_STATUS'] = 'NULL'
asca_control_test_report_row['ASCA_CONTROL_TMS'] = 'NULL'
asca_control_test_report_row['ASCA_TEST_RESULT'] = 'FAIL'
asca_control_test_report.append(asca_control_test_report_row)
print("\t\tASCA_CONTROL_POINT_ID:" + asca_control_test_report_row['ASCA_CNTL_PT_ID'])
print("\t\tSOURCE_TABLE_ROW_COUNT:" + asca_control_test_report_row['SOURCE_ROW_COUNT'])
print("\t\tTARGET_TABLE_ROW_COUNT" + asca_control_test_report_row['TARGET_ROW_COUNT'])
print("\t\tRow Count Validate result:" + asca_control_test_report_row['ASCA_TEST_RESULT'])
####################After all the records inserted to the asca_control_test_report
'''Write dict to json file, then generate the xls report file through json file'''
#print(asca_control_test_report)
gen_asca = Generate_report()
gen_asca.write_asca_status_to_json(asca_control_test_report)
gen_asca.generate_asca_control_test_report()
'''validate the test case result'''
failed_count=0
for item in asca_control_test_report:
if item['ASCA_TEST_RESULT'] == 'FAIL':
failed_count += 1
if failed_count > 0:
print("One or more jobs' asca control not got pass, "
"check the asca_control_test_report.xls for detail")
raise TestException.ASCAControlError()
else:
print("All jobs' asca control result got pass, ASCA Control test passed.")
def rowcount_test(asca_db_node, zos_user, zos_pwd):
'''Step 1 get source/target table list from asca.asca_control_record'''
conf = ReadConfig()
job_list = conf.Read_job_list()
'''Get asca_control_record through jdbc, store the result to the asca_control_dict'''
'''1. get the asca control id list'''
asca_control_id_list = []
for job in job_list:
if job['ASCA_CONTROL_POINT_ID'] != '':
asca_control_id_list.append(job['ASCA_CONTROL_POINT_ID'])
asca_control_id_string = str(asca_control_id_list).strip('[').strip(
']')
'''2.generate the sql query'''
print(
"Step 1: Get asca control result from ASCA.ASCA_CNTROL_RECORD table"
)
query = "select SRC_OBJ_NM,TRGT_TBL_NM from ASCA.ASCA_control_point\
WHERE ASCA_CNTL_PT_ID in ({})".format(asca_control_id_string)
print("\tQuery:" + query)
'''3. Trigger jdbc driver to query the data'''
source_target_mapping = db_connect.exec_sql_with_jdbc(
asca_db_node, zos_user, zos_pwd, query)
'''Store the table mapping to a temp file'''
file_name = os.path.join(conf.read_temp_dir(),
'source_target_mapping.tmp')
print(file_name)
with open(file_name, 'w') as f:
json.dump(source_target_mapping, f)
print(source_target_mapping)
print("\tQuery running completed")
print("Step 2: start the get source table row count...")
'''generate query'''
source_db_node = conf.Read_source_db_node()
query_str = ''
for i in range(len(source_target_mapping)):
if i < len(source_target_mapping) - 1:
query_str += "select '" + source_target_mapping[i][
'SRC_OBJ_NM'] + "' as TABLE_NM, count(*) as ROWCOUNT from " + source_target_mapping[
i]['SRC_OBJ_NM'] + " union "
else:
query_str += "select '" + source_target_mapping[i][
'SRC_OBJ_NM'] + "' as TABLE_NM, count(*) as ROWCOUNT from " + source_target_mapping[
i]['SRC_OBJ_NM']
print(query_str)
'''run the query '''
source_rowcount = db_connect.exec_sql_with_jdbc(
source_db_node, zos_user, zos_pwd, query_str)
print(source_rowcount)
print("Step 3: start get target table row count...")
'''generate target query'''
target_query = ''
for i in range(len(source_target_mapping)):
if i < len(source_target_mapping) - 1:
target_query += "select '" + source_target_mapping[i][
'TRGT_TBL_NM'] + "' as TABLE_NM, count(*) as ROWCOUNT from " + source_target_mapping[
i]['TRGT_TBL_NM'] + " union "
else:
target_query += "select '" + source_target_mapping[i][
'TRGT_TBL_NM'] + "' as TABLE_NM, count(*) as ROWCOUNT from " + source_target_mapping[
i]['TRGT_TBL_NM']
print(target_query)
'''get target db node'''
target_db_node = conf.Read_target_db_node()
db_conf = conf.Read_db_config(target_db_node)
db_driver = db_conf['driver']
print(db_driver)
if db_driver == 'com.ibm.db2.jcc.DB2Driver':
'''use jdbc to run query'''
target_rowcount = db_connect.exec_sql_with_jdbc(
target_db_node, zos_user, zos_pwd, target_query)
else:
'''use common driver to run query'''
target_rowcount = db_connect.exec_sql_common(
target_db_node, 'siwsit', 'SIWJul2019JulSIW', target_query)
print(target_rowcount)
'''Step 4: validation'''
print("Step 4: validation")
Rowcount_test_result = []
for item in source_target_mapping:
rowcount_record = {}
rowcount_record['SOURCE_TABLE'] = item['SRC_OBJ_NM']
rowcount_record['TARGET_TABLE'] = item['TRGT_TBL_NM']
for element in source_rowcount:
if element['TABLE_NM'] == item['SRC_OBJ_NM']:
rowcount_record['SOURCE_ROWCOUNT'] = str(
element['ROWCOUNT'])
for element in target_rowcount:
if element['TABLE_NM'] == item['TRGT_TBL_NM']:
rowcount_record['TARGET_ROWCOUNT'] = str(
element['ROWCOUNT'])
rowcount_record['TEST_RESULT'] = "PASS" if (
rowcount_record['SOURCE_ROWCOUNT']
== rowcount_record['TARGET_ROWCOUNT']) else "FAIL"
print("Source table name:" + rowcount_record['SOURCE_TABLE'])
print("Target table name:" + rowcount_record['TARGET_TABLE'])
print("Source table rowcount:" +
rowcount_record['SOURCE_ROWCOUNT'])
print("Target table rowcount:" +
rowcount_record['TARGET_ROWCOUNT'])
print("Row count test result:" + rowcount_record['TEST_RESULT'])
Rowcount_test_result.append(rowcount_record)
print(Rowcount_test_result)
'''generate report'''
gen_rowcount = Generate_report()
gen_rowcount.write_row_count_status_to_json(Rowcount_test_result)
gen_rowcount.generate_row_count_test_report()
'''validate the test case result'''
failed_count = 0
for item in Rowcount_test_result:
if item['TEST_RESULT'] == 'FAIL':
failed_count += 1
if failed_count > 0:
print(
"One or more tables' rowcount between source and target mismatch, row count test failed "
"check the row_count_test_report.xls for detail")
raise TestException.RowcountError()
else:
print(
"All tables' row count between source and target matched,the row count test passed."
)