def generate_source_target_column_map(self,id,pwd,source_schema,source_table,target_schema,target_table): db2_column_sample_sql = ''' SELECT NAME as COL_NM,COLNO as COL_NO FROM SYSIBM.SYSCOLUMNS WHERE UPPER(TBNAME) IN (SELECT UPPER(NAME) FROM SYSIBM.SYSTABLES WHERE TYPE = 'T') AND UPPER(TBCREATOR) = UPPER('{}') -- Schema Name AND UPPER (TBNAME) = UPPER('{}') ORDER BY COLNO ''' pda_column_sample_sql = """ SELECT ATTNAME as COL_NM,ATTNUM as COL_NO FROM _V_RELATION_COLUMN WHERE UPPER(TYPE) = 'TABLE' AND UPPER(SCHEMA) = UPPER('BDWDB') -- Schema Name AND UPPER(NAME) = UPPER('{}') ORDER BY ATTNUM; """ print(db2_column_sample_sql) print(pda_column_sample_sql) '''read source data dic''' conf = ReadConfig() source_db_node = conf.Read_source_db_node() target_db_node = conf.Read_target_db_node() source_db = conf.Read_db_config(source_db_node) target_db = conf.Read_db_config(target_db_node) if source_db['db_type'] == 'db2': source_db_sql = db2_column_sample_sql.format(source_schema,source_db) elif source_db['db_type'] == 'pda': source_db_sql = db2_column_sample_sql.format(source_schema,source_db) else: source_db_sql = None print("The db type is valid") if target_db['db_type'] == 'db2': target_db_sql = db2_column_sample_sql.format(source_schema,source_db) elif target_db['db_type'] == 'pda': target_db_sql = db2_column_sample_sql.format(source_schema,source_db) else: target_db_sql = None print("The db type is valid") ''' run under source to get source columns''' print(source_db_sql) print(target_db_sql) source_target_mapping = dict() rs_source = db_connect.exec_sql_common(source_db_node, id, pwd, source_db_sql) rs_target = db_connect.exec_sql_common(target_db_node, id, pwd, target_db_sql) for src_line in rs_source: source_column_nm = src_line['COL_NM'] source_column_no = src_line['COL_NO'] for tgt_line in rs_target: if tgt_line['COL_NO'] == source_column_no: source_target_mapping[source_column_nm] = tgt_line['COL_NM'] return source_target_mapping
def rowcount_test(self, source_id, source_pwd, target_id,target_pwd): '''read config file for the table list''' conf = ReadConfig() table_list = conf.Read_table_list() '''define source db,target db''' source_db_node = conf.Read_source_db_node() target_db_node = conf.Read_target_db_node() rowcount_report = [] fail_count = 0 for table in table_list: rowcount_result = dict() source_table = table['SOURCE_SCHEMA'] + '.' + table['SOURCE_TABLE'] target_table = table['TARGET_SCHEMA'] + '.' + table['TARGET_TABLE'] source_sql = 'select count(*) as rowcount from {}'.format(source_table) target_sql = 'select count(*) as rowcount from {}'.format(target_table) '''connect to source and target DB to run the sql''' '''run sql in target first''' rs_source = db_connect.exec_sql_common(source_db_node, source_id, source_pwd, source_sql) source_rowcount = rs_source[0].rowcount rs_target = db_connect.exec_sql_common(target_db_node, target_id, target_pwd, target_sql) target_rowcount = rs_target[0].rowcount if source_rowcount == target_rowcount: status = 'PASS' else: status = 'FAIL' fail_count += 1 rowcount_result['SOURCE_TABLE'] = source_table rowcount_result['TARGET_TABLE'] = target_table rowcount_result['SOURCE_ROWCOUNT'] = source_rowcount rowcount_result['TARGET_ROWCOUNT'] = target_rowcount rowcount_result['STATUS'] = status rowcount_report.append(rowcount_result) with open("tmp/rowcountreport.csv", "w") as f: f.write(str(rowcount_report)) if fail_count > 0: print("The rowcount test passed,detail see the rowcount report") else: print("The rowcount test failed,detail see the rowcount report") raise TestException.RowcountError()
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, id, pwd): '''read conf''' conf = ReadConfig() table_list = conf.Read_table_list() source_db_node = conf.Read_source_db_node() target_db_node = conf.Read_target_db_node() sample_data_report = [] failed_sample_report = [] fail_cell_count = 0 for table in table_list: '''get the source target column mapping''' source_target_column_mapping = self.generate_source_target_column_map(id, pwd,table['SOURCE_SCHEMA'],\ table['SOURCE_TABLE'], table['TARGET_SCHEMA'], table['TARGET_TABLE']) source_table_nm = table['SOURCE_SCHEMA']+'.'+table['SOURCE_TABLE'] target_table_nm = table['TARGET_SCHEMA'] + '.' + table['TARGET_TABLE'] sample_source_condition = conf.Read_where_condition(table['SOURCE_TABLE']) sample_target_condition = conf.Read_where_condition(table['TARGET_TABLE']) source_sql = "select * from {} where {}".format(source_table_nm,sample_source_condition) target_sql = "select * from {} where {}".format(target_table_nm,sample_target_condition) rs_source = db_connect.exec_sql_common(source_db_node, id, pwd, source_sql) rs_target = db_connect.exec_sql_common(target_db_node, id, pwd, target_sql) source_row_count = len(rs_source) target_row_count = len(rs_target) source_column_count = len(rs_source[0]) target_column_count = len(rs_target[0]) print("source table: %s" % source_table_nm) print("target table: %s" % target_table_nm) '''step 1 compare the row number between source and target''' if source_row_count == target_row_count: print("The sample sql returns the same row count") else: print("The sample sql returns the different row count,the test validate failed") raise TestException.SampleDataError '''step 2 compare the column number between source and target''' if source_column_count == target_column_count + 3: print("The sample sql return the same column count") else: print("The sample sql returns the different row count,the test validate failed") raise TestException.SampleDataError '''step 3 loop to compare the result from source and target''' for i in range(source_row_count): for k, v in source_target_column_mapping.items(): sample_compare_dict = dict() source_value = rs_source[i][k] target_value = rs_target[i][v] sample_compare_dict['SOURCE_TABLE_NM'] = source_table_nm sample_compare_dict['SOURCE_COLUMN_NM'] = k sample_compare_dict['SOURCE_COLUMN_VALUE'] = source_value sample_compare_dict['TARGET_TABLE_NM'] = target_table_nm sample_compare_dict['TARGET_COLUMN_NM'] = v sample_compare_dict['TARGET_COLUMN_VALUE'] = target_value sample_compare_dict['STATUS'] = ('PASS' if(source_value == target_value) else 'FAIL') sample_data_report.append(sample_compare_dict) if sample_compare_dict['STATUS'] == 'FAIL': failed_sample_report.append[sample_compare_dict] '''write failed record to failed file ''' with open("tmp/failed_sample_data_report.json",'w',encoding ='UTF-8') as f: json.dump(failed_sample_report, f) '''write all the record to report file''' with open("tmp/sample_data_report.json",'w',encoding ='UTF-8') as f: json.dump(sample_data_report, f) if len(failed_sample_report) > 0: print("There are some cell values not equal, the test failed,check the failed report file for detail") raise TestException.SampleDataError() else: print("The sample data test run passed") return 'PASS'