Пример #1
0
    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
Пример #2
0
    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()
Пример #3
0
    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."
            )
Пример #4
0
    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'