ITM历史数据入库检查工具

用于查看ITM的历史数据是否正常入库:

import ibm_db
import ibm_db_dbi
import time
import ConfigParser 
import datetime

DATABASE_NAME = 'warehous'
DATABASE_HOST = '158.87.1.15'
DATABASE_PORT = '50000'
DATABASE_USER = 'db2inst1'
DATABASE_PASSWORD = 'tivoli'
CONFIG_FILE = 'C:\Users\qinth\Desktop\TEMP\\target.ini'

DTTYPE = ['TDW']
TIMEOUT = 3 #Unit is Hour

TARGETS = []

class TargetException(Exception):
    def __init__(self,info):
        Exception.__init__(self)
        self.info = info

class Target():
    def __init__(self,targetname,schema,table,key,value,datetime,dttype):
        self.targetname = targetname
        self.schema = schema
        self.table = table
        self.key = key.split(' ')
        self.value = value.split(' ')
        self.datetime = datetime
        self.dttype = dttype.upper()
        if self.dttype not in DTTYPE:
            raise TargetException("DTTYPE is invalidate!")
               
    def set_maxdatetime(self,max_datetime):
        self.max_datetime = max_datetime
               
    def get_sql(self):
        sql = 'select max("%s") from "%s"."%s" where ' % (self.datetime,self.schema,self.table)
        #log(sql)
        for _,k in enumerate(self.key):
            log(self.key)
            sql += ' "%s" = %s ' % (k,self.value[_])
            if _ != (len(self.key) - 1):
                sql += "and"
        log(sql)        
        return sql

    def get_filter(self):
        filter = ""
        for _,k in enumerate(self.key):
            filter += ' "%s" = %s ' % (k,self.value[_])
            if _ != (len(self.key) - 1):
                filter += "and"
        return filter
            
    def get_status(self,max_datetime):
        status = "UNKNOWN"
        if self.dttype == DTTYPE[0]:
            log(get_format_datetime(max_datetime))
            _max_datetime = datetime.datetime.strptime(get_format_datetime(max_datetime), "%Y%m%d%H%M")
            _now_datetime = datetime.datetime.now()
            log(_max_datetime)
            log(_now_datetime)
            log((_now_datetime - _max_datetime).seconds)
            log((_max_datetime - _now_datetime).seconds)
            if (_now_datetime > _max_datetime) and ((_now_datetime - _max_datetime).seconds > (TIMEOUT * 3600)):
                status = "BAD_L"
            elif (_now_datetime < _max_datetime) and ((_max_datetime - _now_datetime).seconds > (TIMEOUT * 3600)):
                status = "BAD_G"
            else:
                status = "GOOD"
        
        return status
            
def get_format_datetime(d):
    log(d)
    year = '20%s' % d[1:3]
    month = d[3:5]
    day = d[5:7]
    hour = d[7:9]
    minute = d[9:11]
    return "%s%s%s%s%s" % (year,month,day,hour,minute)

def log(content):
    #print (str(content))
    pass
    
def log_error(content):
    print("ERROR: " + str(content))
    
def log_debug(content):
    print (str(content))
    time.sleep(5)
    
def get_conf():
    cf = ConfigParser.ConfigParser()  
    cf.read(CONFIG_FILE)
    
    sections = cf.sections()
    for section in sections:
        log("Section: " + section)
        schema = cf.get(section, 'schema')
        table = cf.get(section, 'table')
        key = cf.get(section, 'key')
        value = cf.get(section, 'value')
        datetime = cf.get(section, 'datetime')
        dttype = cf.get(section, 'dttype')
        TARGETS.append(Target(section,schema,table,key,value,datetime,dttype))
        
    
def get_DB_conn():
    dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=" + DATABASE_NAME + ";HOSTNAME=" + DATABASE_HOST + ";PORT=" + DATABASE_PORT + \
        ";PROTOCOL=TCPIP;UID=" + DATABASE_USER + ";PWD=" + DATABASE_PASSWORD + ";"
    try:
        ibm_db_conn = ibm_db.connect(dsn,'','')
        conn = ibm_db_dbi.Connection(ibm_db_conn)
        log("Connect to DB " + DATABASE_NAME + " successful.")
        return conn
    except Exception,e:
        log_error(e)
        raise Exception

def check_status(conn):
    for target in TARGETS:
        sql = target.get_sql()
        curs = conn.cursor()
        curs.execute(sql)
        rows = curs.fetchall()
        
        log("MAX Datetime: " + str(rows))
        max_datetime = rows[0][0]
        target.set_maxdatetime(max_datetime)
        if max_datetime is None:
            status = 'NORECORD'
        else:
            status = target.get_status(max_datetime)
        
        print "%s;%s;%s;%s;%s;%s" % (target.targetname,target.schema,target.table,target.get_filter(),target.max_datetime,status)
        
        curs.close()
    
    
if __name__ == '__main__':
    conn = get_DB_conn()
    
    get_conf()
    check_status(conn)
    
    conn.close()
    log("Disconnect from DB " + DATABASE_NAME + " successful.")

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*