def get_process():
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
hstip = hst.retHostIP()
sqlstmt = 'select NUMBEROFTHREADS from STATSPROCESSINGMACHINE where IPADDRESS=\'' + hstip + '\''
result = oracon.execSql(oraconn, sqlstmt)
p_count = ''
for count in result:
p_count = count[0]
oraconn.close()
return p_count
def check_purpose():
global machine_id
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
hstip = hst.retHostIP()
sqlstmt = 'select purpose,machineid from STATSPROCESSINGMACHINE where IPADDRESS=\'' + hstip + '\''
purrec = oracon.execSql(oraconn, sqlstmt)
purpose = ''
for prec in purrec:
purpose = prec[0]
machine_id = prec[1]
oraconn.close()
return purpose
def splitrunner():
fl = statslog.logcreate('log/splitrunner.log')
try:
ipadd = hostlib.retHostIP()
sqlstmt = 'select number_of_files_per_run from statsprocessingmachine where ipaddress=\'' + ipadd + '\' and enable =1'
oraconn = oracon.openconnect(constr)
numthreadrec = oracon.execSql(oraconn, sqlstmt)
for numthread in numthreadrec:
files_perrun = numthread[0]
numthreadrec.close()
oraconn.close()
while (1):
pythonRuns = commands.getoutput(
"ps -ef | grep parser | grep -v grep | wc -l")
if int(pythonRuns) < files_perrun + 1:
p = Process(target=splitter)
p.start()
time.sleep(900)
except:
statslog.logwrite(fl, "Error reported: " + str(sys.exc_info()[1]),
'splitrunner')
def tabber():
oraconn = oracon.openconnect(constr)
fl = statslog.logcreate('log/parser.log')
try:
statslog.logwrite(fl, 'Getting structure.....', 'tabber')
sqlstmt = 'select distinct statsname from statsstructure'
statsrec = oracon.execSql(oraconn, sqlstmt)
statList = []
dtct = 0
for strec in statsrec:
statList.append(strec[0])
statslog.logwrite(fl, 'Getting IP.....', 'tabber')
ip = hostlib.retHostIP()
statslog.logwrite(fl, 'Getting machine configuration.....', 'tabber')
sqlstmt = 'select machineid,numberofthreads,dataprocessing_folder from statsprocessingmachine where ipaddress=\'' + ip + '\''
macrec = oracon.execSql(oraconn, sqlstmt)
mid = 0
num_threads = 0
datafolder = ''
for mrec in macrec:
mid = mrec[0]
num_threads = mrec[1]
datafolder = mrec[2]
statslog.logwrite(fl, 'Getting files to process.....', 'tabber')
sqlstmt = 'select stats_splitfile_id,stats_splitfile_path,stats_splitfile_name,stats_split_file_type,stats_first_row_version from stats_split_files a,'
sqlstmt += '(select distinct statsname,stats_first_row_version from statsstructure) b where machineid=' + str(
mid)
sqlstmt += ' and stats_split_file_type in (\'' + string.join(
statList, '\',\''
) + '\') and a.stats_split_file_type=b.statsname and a.File_process_status=0 '
sqlstmt += ' AND ROWNUM <=' + str(
num_threads / 2) + ' order by stats_splitfile_id'
procfiles = oracon.execSql(oraconn, sqlstmt)
statslog.logwrite(fl, 'Recieved list of files.....', 'tabber')
for procrec in procfiles:
flpthlist = string.split(procrec[2], '.')
inservserial = flpthlist[1]
datepart = str(flpthlist[2]) + ' ' + str(flpthlist[3])
statslog.logwrite(fl, 'Starting file process for ' + procrec[2],
'tabber')
p = Process(target=tabFile,
args=(
procrec[0],
procrec[1],
procrec[2],
procrec[3],
inservserial,
datepart,
procrec[4],
datafolder,
))
p.daemon = True
p.start()
sqlstmt = 'begin Update stats_split_files set FILE_PROCESS_STATUS=2 where stats_splitfile_id=' + str(
procrec[0]) + '; commit; end;'
oracon.execSql(oraconn, sqlstmt)
except:
statslog.logwrite(fl, "Error reported: " + str(sys.exc_info()[1]),
'tabber')
def splitter():
oraconn = oracon.openconnect(constr)
lconn = lconnect.connectMysql('ods', 'procuser', 'c@llhome', 'localhost')
lcurr = lconn.cursor()
fl = statslog.logcreate('log/splitter.log')
try:
statslog.logwrite(fl, 'Getting Host IP...\n')
ipadd = hostlib.retHostIP()
statslog.logwrite(fl, 'Getting number of threads...\n')
sqlstmt = 'select machineid,numberofthreads,dataprocessing_folder,splitterloc from statsprocessingmachine where ipaddress=\'' + ipadd + '\' and enable =1'
numthreadrec = oracon.execSql(oraconn, sqlstmt)
for numthread in numthreadrec:
numthreads = numthread[1]
machineid = numthread[0]
datafolder = numthread[3]
numsplitterthreads = int(numthreads / 2)
statslog.logwrite(fl, 'Getting number of datatypes...\n')
sqlstmt = 'select distinct datatype from stat_split_file_lookup'
datatyprec = oracon.execSql(oraconn, sqlstmt)
dtct = 0
for dt in datatyprec:
dtct += 1
numsplitthread = int(numsplitterthreads / dtct)
datatyprec = oracon.execSql(oraconn, sqlstmt)
lconn = lconnect.connectMysql('ods', 'procuser', 'c@llhome',
'localhost')
lcurr = lconn.cursor()
for dtype in datatyprec:
sqlstmt = 'SELECT COUNT(1) FROM STATSPROCESSTRANSACT WHERE FILE_PROCESS_STATUS=0 and DATATYPE=\'' + dtype[
0] + '\''
lcurr.execute(sqlstmt)
reccount = lcurr.fetchall()
numInMySql = 0
for rec in reccount:
numInMySql = rec[0]
numsplitterthreads = numsplitthread - numInMySql
if numsplitterthreads > 0:
statslog.logwrite(fl, 'Processing for... :' + dtype[0])
sqlstmt = 'SELECT STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE FROM STATSPROCESSTRANSACT '
sqlstmt += 'WHERE FILE_PROCESS_STATUS=0 AND MACHINEID=' + str(
machineid)
sqlstmt += ' AND ROWNUM <=' + str(
numsplitterthreads
) + ' AND DATATYPE= \'' + dtype[0] + '\' ORDER BY STATS_FILEID'
filerec = oracon.execSql(oraconn, sqlstmt)
statslog.logwrite(fl, 'Inserting data into mysql...\n')
sqlstmt = 'INSERT INTO STATSPROCESSTRANSACT (STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE) VALUES '
sql = []
for flrec in filerec:
sql.append('(' + str(flrec[0]) + ',\'' + flrec[1] +
'\',\'' + flrec[2] + '\',\'' + flrec[3] + '\')')
sqlst = 'begin Update STATSPROCESSTRANSACT set FILE_PROCESS_STATUS=2 where STATS_FILEID=' + str(
flrec[0]) + '; commit; end;'
oracon.execSql(oraconn, sqlst)
if len(sql) > 0:
sqlstmt = sqlstmt + string.join(sql, ',')
lcurr.execute(sqlstmt)
lconn.close()
oraconn.close()
lconn = lconnect.connectMysql('ods', 'procuser', 'c@llhome',
'localhost')
lcurr = lconn.cursor()
statslog.logwrite(fl, 'Querying mysql...')
sqlstmt = 'SELECT STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE FROM STATSPROCESSTRANSACT where ifnull(FILE_PROCESS_STATUS,0)=0 LIMIT ' + str(
numsplitthread)
lcurr.execute(sqlstmt)
ctr = 0
statrow = lcurr.fetchall()
pythonRuns = commands.getoutput(
"ps -ef | grep python | grep -v grep | wc -l")
if int(pythonRuns) - 3 < numsplitthread:
residual = (numsplitthread - int(pythonRuns)) + 3
sqlstmt = 'SELECT STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE FROM STATSPROCESSTRANSACT where ifnull(FILE_PROCESS_STATUS,0)=0 LIMIT ' + str(
residual)
lcurr.execute(sqlstmt)
ctr = 0
statrow = lcurr.fetchall()
for statrec in statrow:
statslog.logwrite(fl, 'Splitting file...:' + statrec[1])
flpthlist = string.split(statrec[2], '/')
inservserial = flpthlist[len(flpthlist) - 2]
statslog.logwrite(fl, 'Starting process for ... ' + statrec[1])
p = Process(target=splitFile,
args=(
statrec[0],
statrec[1],
statrec[2],
statrec[3],
inservserial,
datafolder + '/splitter',
machineid,
))
p.daemon = True
p.start()
sqlstmt = 'Update STATSPROCESSTRANSACT set FILE_PROCESS_STATUS=2 where STATS_FILEID=' + str(
statrec[0])
lcurr.execute(sqlstmt)
ctr += 1
lcurr.close()
lconn.close()
os.remove('log/splitter.log')
except:
statslog.logwrite(fl, "Error reported: " + str(sys.exc_info()[1]))
def splitter():
oraconn = oracon.openconnect(constr)
lconn = lconnect.connectMysql('ods', 'procuser', 'c@llhome', 'localhost')
lcurr = lconn.cursor()
try:
sqlstmt = 'update STATSPROCESSTRANSACT set FILE_PROCESS_STATUS=0 where FILE_PROCESS_STATUS=2'
lcurr.execute(sqlstmt)
ipadd = hostlib.retHostIP()
sqlstmt = 'select machineid,numberofthreads,dataprocessing_folder,splitterloc,number_of_files_per_run,delay_seconds from statsprocessingmachine where ipaddress=\'' + ipadd + '\' and enable =1'
numthreadrec = oracon.execSql(oraconn, sqlstmt)
for numthread in numthreadrec:
numthreads = numthread[1]
machineid = numthread[0]
datafolder = numthread[3]
files_perrun = numthread[4]
delay_seconds = numthread[5]
nummysqltreads = int(numthreads)
numsplitterthreads = int(numthreads)
sqlstmt = 'select distinct datatype from stat_split_file_lookup'
datatyprec = oracon.execSql(oraconn, sqlstmt)
#dtct=0;
datatypes = []
for dt in datatyprec:
#dtct+=1;
datatypes.append(dt[0])
#numsplitthread=int(numsplitterthreads/dtct);
#datatyprec=oracon.execSql(oraconn,sqlstmt);
#lconn = lconnect.connectMysql('ods','procuser','c@llhome','localhost');
#lcurr=lconn.cursor();
sqlstmt = 'select count(1) from statsprocesstransact where machineid=' + str(
machineid
) + ' and FILE_PROCESS_STATUS=0 and datatype in (\'' + string.join(
datatypes, '\'' + ',' + '\'') + '\')'
datatypct = oracon.execSql(oraconn, sqlstmt)
for dtctrec in datatypct:
tot = dtctrec[0]
datatypelist = []
sqlstmt = 'select datatype,count(1) from statsprocesstransact where machineid=' + str(
machineid
) + ' and FILE_PROCESS_STATUS=0 and datatype in (\'' + string.join(
datatypes, '\'' + ',' + '\'') + '\') group by datatype'
datatyprec = oracon.execSql(oraconn, sqlstmt)
for dt in datatyprec:
recperdatatype = int(
((dt[1] * 1.0) / (tot * 1.0)) * numsplitterthreads)
datatypelist.append(dt[0] + ',' + str(recperdatatype))
for dtype in datatypelist:
datatype = string.split(dtype, ',')
sqlstmt = 'SELECT COUNT(1) FROM STATSPROCESSTRANSACT WHERE IFNULL(FILE_PROCESS_STATUS,0)=0 AND DATATYPE= \'' + datatype[
0] + '\''
lcurr.execute(sqlstmt)
reccount = lcurr.fetchall()
numInMySql = 0
for rec in reccount:
numInMySql = rec[0]
numsplitterthreads = int(datatype[1]) - int(numInMySql)
if numsplitterthreads > 0:
sqlstmt = 'SELECT STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE FROM STATSPROCESSTRANSACT '
sqlstmt += 'WHERE FILE_PROCESS_STATUS=0 AND MACHINEID=' + str(
machineid)
sqlstmt += ' AND ROWNUM <=' + str(
numsplitterthreads) + ' AND DATATYPE= \'' + datatype[
0] + '\' ORDER BY STATS_FILEID'
filerec = oracon.execSql(oraconn, sqlstmt)
sqlstmt = 'INSERT INTO STATSPROCESSTRANSACT (STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE) VALUES '
sql = []
for flrec in filerec:
sql.append('(' + str(flrec[0]) + ',\'' + flrec[1] +
'\',\'' + flrec[2] + '\',\'' + flrec[3] + '\')')
sqlst = 'begin Update STATSPROCESSTRANSACT set FILE_PROCESS_STATUS=2 where STATS_FILEID=' + str(
flrec[0]) + '; commit; end;'
oracon.execSql(oraconn, sqlst)
if len(sql) > 0:
sqlstmt = sqlstmt + string.join(sql, ',')
lcurr.execute(sqlstmt)
lconn.close()
oraconn.close()
lconn = lconnect.connectMysql('ods', 'procuser', 'c@llhome',
'localhost')
lcurr = lconn.cursor()
ctr = 0
pythonRuns = commands.getoutput(
"ps -ef | grep parser | grep -v grep | wc -l")
if int(pythonRuns) - 3 < nummysqltreads:
residual = (nummysqltreads - int(pythonRuns))
sqlstmt = 'SELECT COUNT(1) FROM STATSPROCESSTRANSACT where ifnull(FILE_PROCESS_STATUS,0)=0'
lcurr.execute(sqlstmt)
totalcurr = lcurr.fetchall()
for lcur in totalcurr:
total_recs = lcur[0]
sqlstmt = 'SELECT DATATYPE,COUNT(1) FROM STATSPROCESSTRANSACT where ifnull(FILE_PROCESS_STATUS,0)=0 GROUP BY DATATYPE'
lcurr.execute(sqlstmt)
datrec = lcurr.fetchall()
for lcur in datrec:
datatype = lcur[0]
numrecs = int(
round((lcur[1] * 1.0 / total_recs * 1.0) * residual * 1.0,
0))
sqlstmt = 'SELECT distinct STATS_FILEID,STATS_FILE_NAME,STATS_FILE_PATH,DATATYPE FROM STATSPROCESSTRANSACT where ifnull(FILE_PROCESS_STATUS,0)=0 and DATATYPE=\'' + datatype + '\' LIMIT ' + str(
numrecs)
lcurr.execute(sqlstmt)
ctr = 0
statrow = lcurr.fetchall()
for statrec in statrow:
flpthlist = string.split(statrec[2], '/')
inservserial = flpthlist[len(flpthlist) - 2]
p = Process(target=splitFile,
args=(
statrec[0],
statrec[1],
statrec[2],
statrec[3],
inservserial,
datafolder + '/splitter',
machineid,
))
p.daemon = True
p.start()
while ctr > files_perrun:
time.sleep(delay_seconds)
sqlstmt = 'SELECT COUNT(1) FROM STATSPROCESSTRANSACT WHERE FILE_PROCESS_STATUS=2'
lcurr.execute(sqlstmt)
rowRec = lcurr.fetchall()
for rec in rowRec:
ctr = rec[0]
sqlstmt = 'Update STATSPROCESSTRANSACT set FILE_PROCESS_STATUS=2 where STATS_FILEID=' + str(
statrec[0]) + ' and ifnull(FILE_PROCESS_STATUS,0)=0'
lcurr.execute(sqlstmt)
ctr += 1
lcurr.close()
lconn.close()
#os.remove('log/splitter.log');
except:
fl = statslog.logcreate('log/splitter.log')
statslog.logwrite(fl, "Error reported: " + str(sys.exc_info()[1]))
#!/usr/bin/env python
import hostlib as hst
import oracleconnect as oracon
import decimal
constr = 'ods/ods@callhomeods:1521/callhomeods'
oraconn = oracon.openconnect(constr)
print "Begining to add machine for processing ........................"
splitterloc = raw_input("Splitter processing location:")
tabberloc = raw_input("Processed file location:")
purpose = raw_input("Purpose of the machine:")
hstname = hst.retHostName()
hstip = hst.retHostIP()
hstcpu = hst.retcpuCount()
hstmem = hst.retphyMem()
hstthread = int((round((hstmem - 4) / 2, 0)) * hstcpu) * 100
sqlstmt = 'BEGIN DATALOAD.MACHINEADD(P_HOSTNAME=>\'' + hstname + '\',P_IPADDRESS=>\'' + hstip + '\',P_NUMBEROFTHREADS=>' + str(
hstthread
) + ',P_SPLITTERLOC=>\'' + splitterloc + '\',P_PROCESSLOC=>\'' + tabberloc + '\',P_PURPOSE=>\'' + purpose + '\'); END;'
oracon.execSql(oraconn, sqlstmt)
print "Machine successfully added.........."
#!/usr/bin/env python
import oracleconnect as oracon
import mysqlconnect as mconnect
import string
import time
import hostlib as hst
import os
import statslog
import sys
constr = 'ods/ods@callhomeods:1521/callhomeods'
lconn = mconnect.connectMysql('ods', 'procuser', 'c@llhome', 'localhost')
lcurr = lconn.cursor()
hstname = hst.retHostName()
ipadd = hst.retHostIP()
SplitConStr = 'ods/ods@callhomedb03:1521/callhomedb03'
ProcConStr = 'ods/ods@callhomedb04:1521/callhomedb04'
sqlArr = []
MysqlArr = []
oraconn = oracon.openconnect(constr)
sql = 'select purpose from statsprocessingmachine where ipaddress=\'' + ipadd + '\''
recon = oracon.execSql(oraconn, sql)
for rec in recon:
purpose = rec[0]
oraconn.close()
try:
def procExec():
try:
oraconn = oracon.openconnect(constr)
lconn = mysqlconn.connectMysql('ods', 'procuser', 'c@llhome',
'localhost')
lcurr = lconn.cursor()
ipadd = hostlib.retHostIP()
sqlstmt = 'select machineid,numberofthreads,processloc,number_of_files_per_run,delay_seconds from statsprocessingmachine where ipaddress=\'' + ipadd + '\' and enable =1'
numthreadrec = oracon.execSql(oraconn, sqlstmt)
for numthread in numthreadrec:
numthreads = numthread[1]
machineid = numthread[0]
datafolder = numthread[2]
files_per_run = numthread[3]
delay_seconds = numthread[4]
#statslog.logwrite(fl,str(numthreads)+':'+str(files_per_run)+':'+str(delay_seconds));
numthreads = int(numthreads)
sqlstmt = 'select distinct statsname from STATSSTRUCTURE a,PROCESSLOGIC b where a.STATS_STRUCTURE_ID = b.STATS_STRUCTURE_ID'
lcurr.execute(sqlstmt)
recc = lcurr.fetchall()
statList = []
for rec in recc:
statList.append('\'' + rec[0] + '\'')
statsStr = string.join(statList, ',')
sqlstmt = 'update STATS_SPLIT_FILES set FILE_PROCESS_STATUS=0 WHERE FILE_PROCESS_STATUS=2'
lcurr.execute(sqlstmt)
sqlstmt = 'select count(1) from STATS_SPLIT_FILES where ifnull(FILE_PROCESS_STATUS,0)=0 and STATS_SPLIT_FILE_TYPE in (' + statsStr + ')'
lcurr.execute(sqlstmt)
recc = lcurr.fetchall()
numRec = 0
for rec in recc:
numRec = rec[0]
numthreadstocopy = numthreads - numRec
#statslog.logwrite(fl,str(numthreadstocopy));
if numthreadstocopy > 0:
sqlstmt = ''
rows = 1000
totrows = rows
while totrows <= numthreadstocopy:
sqlstmt = 'select STATS_FILEID,STATS_SPLITFILE_ID,STATS_SPLITFILE_NAME,STATS_SPLITFILE_PATH,STATSID,STATS_SPLIT_FILE_TYPE from STATS_SPLIT_FILES '
sqlstmt += ' where FILE_PROCESS_STATUS=0 and MACHINEID=' + str(
machineid
) + ' and ROWNUM <=' + str(
rows
) + ' and STATS_SPLIT_FILE_TYPE in (' + statsStr + ') order by STATS_SPLITFILE_ID desc'
#statslog.logwrite(fl,sqlstmt);
FileRec = oracon.execSql(oraconn, sqlstmt)
sqlstmt = 'INSERT INTO STATS_SPLIT_FILES (STATS_FILEID,STATS_SPLITFILE_ID,STATS_SPLITFILE_NAME,STATS_SPLITFILE_PATH,STATSID,STATS_SPLIT_FILE_TYPE) VALUES '
sql = []
for flrec in FileRec:
sql.append('(' + str(flrec[0]) + ',' + str(flrec[1]) +
',\'' + flrec[2] + '\',\'' + flrec[3] + '\',' +
str(flrec[4]) + ',\'' + flrec[5] + '\')')
sqlst = 'begin Update STATS_SPLIT_FILES set FILE_PROCESS_STATUS=2 where STATS_SPLITFILE_ID=' + str(
flrec[1]) + '; commit; end;'
oracon.execSql(oraconn, sqlst)
if len(sql) > 0:
sqlstmt += string.join(sql, ',')
lcurr.execute(sqlstmt)
totrows = totrows + rows
pythonRuns = commands.getoutput(
"ps -ef | grep parsetabber | grep -v grep | wc -l")
while int(pythonRuns) - 3 <= files_per_run:
sqlstmt = 'SELECT a.STATS_SPLITFILE_ID,a.STATS_SPLITFILE_NAME,a.STATS_SPLITFILE_PATH,STATS_SPLIT_FILE_TYPE from STATS_SPLIT_FILES a where ifnull(FILE_PROCESS_STATUS,0)=0 and STATS_SPLIT_FILE_TYPE in (' + statsStr + ') LIMIT ' + str(
files_per_run - int(pythonRuns))
#statslog.logwrite(fl,sqlstmt);
if (files_per_run - int(pythonRuns)) > 0:
ctr = 0
lcurr = lconn.cursor()
lcurr.execute(sqlstmt)
filelist = lcurr.fetchall()
for flrec in filelist:
flpthlist = string.split(flrec[2], '/')
inservserial = flpthlist[len(flpthlist) - 2]
p = Process(target=processFile,
args=(
flrec[0],
flrec[1],
flrec[2],
flrec[3],
inservserial,
datafolder,
machineid,
))
p.daemon = True
p.start()
ctr += 1
sqlstmt = 'Update STATS_SPLIT_FILES set FILE_PROCESS_STATUS=2 where STATS_SPLITFILE_ID=' + str(
flrec[0])
lcurr.execute(sqlstmt)
lcurr.close()
if ctr == 0:
break
pythonRuns = commands.getoutput(
"ps -ef | grep parsetabber | grep -v grep | wc -l")
while int(pythonRuns) > files_per_run:
time.sleep(delay_seconds)
pythonRuns = commands.getoutput(
"ps -ef | grep parsetabber | grep -v grep | wc -l")
pythonRuns = commands.getoutput(
"ps -ef | grep parsetabber | grep -v grep | wc -l")
lconn.close()
except:
fl = statslog.logcreate("log/parser.log")
statslog.logwrite(fl, "Error reported: " + str(sys.exc_info()[1]))