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."
)
def sample_data_test(self, source_db_node, source_user, source_pwd,
target_db_node, target_user, target_pwd):
print("Get necessary metadata from source and target")
"""Step 1 get source/target table list from source target table mapping"""
db2_metadata_query = "SELECT NAME as COLUMN_NAME,TBNAME as TABLE_NAME,TBCREATOR AS TABLE_SCHEMA,COLNO AS COLUMN_NUMBER,COLTYPE AS COLUMN_TYPE,LENGTH AS COLUMN_LENGTH,KEYSEQ AS KEY_SEQ \
FROM SYSIBM.SYSCOLUMNS \
WHERE UPPER(TBNAME) IN (SELECT UPPER(NAME) FROM SYSIBM.SYSTABLES WHERE TYPE = 'T') AND \
UPPER(TBCREATOR) in ({}) \
AND UPPER (TBNAME) in ({}) order by COLNO "
pda_metadata_query = "SELECT ATTNAME AS COLUMN_NAME,NAME AS TABLE_NAME,SCHEMA AS TABLE_SCHEMA,ATTNUM AS COLUMN_NUMBER,FORMAT_TYPE AS COLUMN_TYPE,ATTCOLLENG AS COLUMN_LENGTH,'0' AS KEY_SEQ \
FROM _V_RELATION_COLUMN \
WHERE UPPER(TYPE) = 'TABLE' AND \
UPPER(SCHEMA) in ({}) \
AND UPPER(NAME) in ({}) "
conf = ReadConfig()
source_target_table_mapping = conf.read_source_target_table_mapping()
print(source_target_table_mapping)
'''Get source and target db metadata'''
print("Step 1: Get source table list.")
print("Step 2: Get source tables' column list to file")
source_schema_list = []
target_schema_list = []
source_table_list = []
target_table_list = []
for item in source_target_table_mapping:
source_schema_list.append(item['SRC_OBJ_NM'].split('.')[0])
source_table_list.append(item['SRC_OBJ_NM'].split('.')[1])
target_schema_list.append(item['TRGT_TBL_NM'].split('.')[0])
target_table_list.append(item['TRGT_TBL_NM'].split('.')[1])
source_schema_list = list(set(source_schema_list))
target_schema_list = list(set(target_schema_list))
source_table_list = list(set(source_table_list))
target_table_list = list(set(target_table_list))
print("Step 3: Get target table list.")
'''get source tables' metadata'''
source_db_driver = conf.Read_db_config(source_db_node)['driver']
#db_driver = db_node['driver']
if source_db_driver == '{IBM DB2 ODBC DRIVER}' or source_db_driver == 'com.ibm.db2.jcc.DB2Driver':
source_query = db2_metadata_query.format(
str(source_schema_list).strip('[').strip(']'),
str(source_table_list).strip('[').strip(']'))
print(source_query)
else:
source_query = pda_metadata_query.format(
str(source_schema_list).strip('[').strip(']'),
str(source_table_list).strip('[').strip(']'))
print(source_query)
if source_db_driver == 'com.ibm.db2.jcc.DB2Driver':
source_metadata = db_connect.exec_sql_with_jdbc(
source_db_node, source_user, source_pwd, source_query)
else:
source_metadata = db_connect.exec_sql_common(
source_db_node, source_user, source_pwd, source_query)
'''table to map'''
source_table_columns_dict = {}
for item in source_metadata:
source_table_columns = item['TABLE_SCHEMA'].strip(
) + "." + item['TABLE_NAME']
column_dict = {}
column_dict['COLUMN_NAME'] = item['COLUMN_NAME']
column_dict['COLUMN_NUMBER'] = item['COLUMN_NUMBER']
column_dict['COLUMN_TYPE'] = item['COLUMN_TYPE']
column_dict['COLUMN_LENGTH'] = item['COLUMN_LENGTH']
column_dict['KEY_SEQ'] = item['KEY_SEQ']
if source_table_columns_dict.__contains__(source_table_columns):
source_table_columns_dict[source_table_columns].append(
column_dict)
else:
column_list = []
column_list.append(column_dict)
source_table_columns_dict[source_table_columns] = column_list
print(source_table_columns_dict)
'''Store the table mapping to a temp file'''
file_name = os.path.join(conf.read_temp_dir(), 'source_metadata.tmp')
print(file_name)
with open(file_name, 'w') as f:
json.dump(source_table_columns_dict, f)
print("Step 4: Get target tables' column list.")
'''get target tables' metadata'''
target_db_driver = conf.Read_db_config(target_db_node)['driver']
print('target db driver:' + target_db_driver)
if target_db_driver == '{IBM DB2 ODBC DRIVER}' or target_db_driver == 'com.ibm.db2.jcc.DB2Driver':
target_query = db2_metadata_query.format(
str(target_schema_list).strip('[').strip(']'),
str(target_table_list).strip('[').strip(']'))
print(target_query)
else:
target_query = pda_metadata_query.format(
str(target_schema_list).strip('[').strip(']'),
str(target_table_list).strip('[').strip(']'))
print(target_query)
if target_db_driver == 'com.ibm.db2.jcc.DB2Driver':
target_metadata = db_connect.exec_sql_with_jdbc(
target_db_node, target_user, target_pwd, target_query)
else:
target_metadata = db_connect.exec_sql_common(
target_db_node, target_user, target_pwd, target_query)
'''table to map'''
target_table_columns_dict = {}
for item in target_metadata:
target_table_columns = item['TABLE_SCHEMA'].strip(
) + "." + item['TABLE_NAME']
column_dict = {}
column_dict['COLUMN_NAME'] = item['COLUMN_NAME']
column_dict['COLUMN_NUMBER'] = item['COLUMN_NUMBER']
column_dict['COLUMN_TYPE'] = item['COLUMN_TYPE'].split('(')[0]
column_dict['COLUMN_LENGTH'] = item['COLUMN_LENGTH']
column_dict['KEY_SEQ'] = item['KEY_SEQ']
if target_table_columns_dict.__contains__(target_table_columns):
target_table_columns_dict[target_table_columns].append(
column_dict)
else:
column_list = []
column_list.append(column_dict)
target_table_columns_dict[target_table_columns] = column_list
print(target_table_columns_dict)
'''Store the target metadata a temp file'''
file_name = os.path.join(conf.read_temp_dir(), 'target_metadata.tmp')
print(file_name)
with open(file_name, 'w') as f:
json.dump(target_table_columns_dict, f)
'''Build source_target_column_mapping'''
print("step 5: get source/target tables column mapping")
source_target_column_mapping = []
for item in source_target_table_mapping:
source_table = item['SRC_OBJ_NM']
target_table = item['TRGT_TBL_NM']
source_columns = source_table_columns_dict[source_table]
target_columns = target_table_columns_dict[target_table]
for src_col in source_columns:
for tar_col in target_columns:
if tar_col['COLUMN_NUMBER'] == src_col['COLUMN_NUMBER']:
source_target_column_mapping.append({"SOURCE_TABLE": source_table, "TARGET_TABLE": target_table,\
"SOURCE_COLUMN": src_col['COLUMN_NAME'],\
"TARGET_COLUMN": tar_col['COLUMN_NAME'],\
"SOURCE_COLUMN_NUMBER": src_col['COLUMN_NUMBER'],\
"TARGET_COLUMN_NUMBER": tar_col['COLUMN_NUMBER']})
print(source_target_column_mapping)
'''Store to temp'''
file_name = os.path.join(conf.read_temp_dir(),
'source_target_column_mapping.tmp')
print(file_name)
with open(file_name, 'w') as f:
json.dump(source_target_column_mapping, f)
'''For each source build key_value mapping of columns'''
source_target_column_mapping_dict = {}
one_table_src_tgt_col_mapping_dict = {}
for items in source_target_column_mapping:
if source_target_column_mapping_dict.__contains__(
items['SOURCE_TABLE']):
one_table_src_tgt_col_mapping_dict[
items['SOURCE_COLUMN']] = items['TARGET_COLUMN']
source_target_column_mapping_dict[
items['SOURCE_TABLE']] = one_table_src_tgt_col_mapping_dict
else:
one_table_src_tgt_col_mapping_dict = {}
one_table_src_tgt_col_mapping_dict[
items['SOURCE_COLUMN']] = items['TARGET_COLUMN']
source_target_column_mapping_dict[
items['SOURCE_TABLE']] = one_table_src_tgt_col_mapping_dict
print("source_target_column_mapping_dict" +
str(source_target_column_mapping_dict))
print("For each source table get source table sample data")
for item in source_target_table_mapping:
source_table = item['SRC_OBJ_NM']
target_table = item['TRGT_TBL_NM']
print("Source table name:" + source_table)
source_key = []
source_column_list = []
target_column_list = []
source_where_condition = conf.Read_where_condition(source_table)
for row in source_table_columns_dict[source_table]:
source_column_list.append(row['COLUMN_NAME'])
if row['KEY_SEQ'] != '0':
source_key.append(row['COLUMN_NAME'])
print('source_column_list:' + str(source_column_list))
print('source_key:' + str(source_key))
for row in target_table_columns_dict[target_table]:
target_column_list.append(row['COLUMN_NAME'])
print("Target_column_list:" + str(target_column_list))
source_column_str = str(source_column_list).strip('[').strip(
']').replace("'", '')
target_column_str = str(target_column_list).strip('[').strip(
']').replace("'", '')
print('Source Column str:' + source_column_str)
print('Target Column str:' + target_column_str)
source_sample_query_run_flag = False
target_sample_query_run_flag = False
if source_where_condition != 'NULL':
source_sample_query = "select {} from {} {}".format(
source_column_str, source_table, source_where_condition)
print("source_sample_query:" + source_sample_query)
target_where_condition = self.source_condition_transfer(
source_table, source_where_condition)
target_sample_query = "select {} from {} {}".format(
target_column_str, target_table, target_where_condition)
print("target_sample_query" + target_sample_query)
elif len(source_key) != 0:
source_sample_query = "with a as (select RAND()*50 as RANDOM_KEY, {} from {} \
order by RANDOM_KEY fetch first 10 rows only) select {} from a order by {} asc" \
.format(source_column_str, source_table, source_column_str,
str(source_key).strip('[').strip(']').replace("'", ''))
print(source_sample_query)
if source_db_driver == 'com.ibm.db2.jcc.DB2Driver':
source_sample_data = db_connect.exec_sql_with_jdbc(
'siwdb2_jdbc', 'pdaetlg', 'sep09sep',
source_sample_query)
else:
source_sample_data = db_connect.exec_sql_common(
'xx', 'xx', 'xx', source_sample_query)
source_sample_query_run_flag = True
'''format timestamp'''
source_sample_data_formated = eval(
self.Date_time_format_transfer(str(source_sample_data)))
#print(type(source_sample_data_formated),type(source_sample_data_formated[0]),source_sample_data_formated)
file_name = os.path.join(conf.read_temp_dir(),
source_table + "_sample.tmp")
with open(file_name, 'w') as f:
json.dump(source_sample_data_formated, f)
target_condition_str = " where "
target_key_list = []
for item in source_key:
target_key = ''
primary_key_value_list = []
for row in source_target_column_mapping:
if row['SOURCE_COLUMN'] == item and row[
'SOURCE_TABLE'] == source_table:
target_key = row['TARGET_COLUMN']
target_key_list.append(target_key)
for row in source_sample_data:
primary_key_value_list.append(row[item])
if item == source_key[-1]:
target_condition_str = target_condition_str + target_key + " in ({})".format(
str(primary_key_value_list).strip('[').strip(']'))
else:
target_condition_str = target_condition_str + target_key + " in ({}) and ".format(
str(primary_key_value_list).strip('[').strip(']'))
target_condition_str += "order by {} asc".format(
str(target_key).strip('[').strip(']').replace("'", ''))
print(str(target_condition_str))
target_sample_query = "select {} from {} {}".format(
target_column_str, target_table, target_condition_str)
print(target_sample_query)
else:
source_sample_query = "select {} from {}".format(
source_column_str, source_table)
target_sample_query = "select {} from {}".format(
target_column_str, target_table)
if source_sample_query_run_flag == False:
print("Source table name:" + source_table)
source_db_driver = 'com.ibm.db2.jcc.DB2Driver'
if source_db_driver == 'com.ibm.db2.jcc.DB2Driver':
source_sample_data = db_connect.exec_sql_with_jdbc(
'siwdb2_jdbc', 'pdaetlg', 'sep09sep',
source_sample_query)
else:
source_sample_data = db_connect.exec_sql_common(
'xx', 'xx', 'xx', source_sample_query)
'''format timestamp'''
source_sample_data_formated = eval(
self.Date_time_format_transfer(str(source_sample_data)))
#print(type(json.loads(source_sample_data_formated)),json.loads(source_sample_data_formated))
file_name = os.path.join(conf.read_temp_dir(),
source_table + "_sample.tmp")
with open(file_name, 'w') as f:
json.dump(source_sample_data_formated, f)
if target_sample_query_run_flag == False:
print("Target table name:" + target_table)
if target_db_driver == 'com.ibm.db2.jcc.DB2Driver':
target_sample_data = db_connect.exec_sql_with_jdbc(
'xx', 'xx', 'xx', target_sample_query)
else:
target_sample_data = db_connect.exec_sql_common(
'siwodspda', 'siwgit', 'SIWJul2019JulSIW',
target_sample_query)
print(target_sample_data)
file_name = os.path.join(conf.read_temp_dir(),
target_table + "_sample.tmp")
with open(file_name, 'w') as f:
json.dump(target_sample_data, f)
'''validation'''
source_diff_list = []
target_diff_list = []
for source_row in source_sample_data_formated:
for target_row in target_sample_data:
compare_flag = False
for k, v in source_target_column_mapping_dict[
source_table].items():
if target_row[v] == source_row[k]:
compare_flag = True
else:
compare_flag = False
break
if compare_flag == True:
break
if compare_flag == False:
source_diff_list.append(source_row)
else:
pass
for target_row in target_sample_data:
for source_row in source_sample_data_formated:
compare_flag = False
for k, v in source_target_column_mapping_dict[
source_table].items():
if source_row[k] == target_row[v]:
compare_flag = True
else:
compare_flag = False
break
if compare_flag == True:
break
if compare_flag == False:
target_diff_list.append(target_row)
else:
pass
print("source_diff_list:" + str(source_diff_list))
print("target_diff_list:" + str(target_diff_list))
