def run_test_120(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals") name1 = ibm_db.field_name(stmt, 1) name2 = ibm_db.field_name(stmt, 2) name3 = ibm_db.field_name(stmt, 3) name4 = ibm_db.field_name(stmt, 4) name6 = ibm_db.field_name(stmt, 8) name7 = ibm_db.field_name(stmt, 0) if (server.DBMS_NAME[0:3] == 'IDS'): name5 = ibm_db.field_name(stmt, "id") name8 = ibm_db.field_name(stmt, "WEIGHT") else: name5 = ibm_db.field_name(stmt, "ID") name8 = ibm_db.field_name(stmt, "weight") print("string(%d) \"%s\"" % (len(name1), name1)) print("string(%d) \"%s\"" % (len(name2), name2)) print("string(%d) \"%s\"" % (len(name3), name3)) print("%s" % name4) print("string(%d) \"%s\"" % (len(name5), name5)) print("%s" % name6) print("string(%d) \"%s\"" % (len(name7), name7)) print("%s" % name8) else: print("Connection failed.")
def run_test_231(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result) + 1): print( str(i) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i)))) print("\n-----") for i in range(0, ibm_db.num_fields(result2)): print( str(i) + ":" + ibm_db.field_type(result2, ibm_db.field_name(result2, i))) print("\n-----") for i in range(0, 3): print( str(i) + ":" + ibm_db.field_type(result3, ibm_db.field_name(result3, i))) print("\n-----") print("region:%s" % ibm_db.field_type(result, 'region')) print("5:%s" % ibm_db.field_type(result2, 5))
def run_test_124(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id") cols = ibm_db.num_fields(result) j = 0 row = ibm_db.fetch_both(result) while ( row ): for i in range(0, cols): field = ibm_db.field_name(result, i) value = row[ibm_db.field_name(result, i)] if (value == None): value = '' print("%s:%s" % (field, value)) print("---------") j += 1 if (j == 10): break row = ibm_db.fetch_both(result) ibm_db.close(conn) print("done") else: print(ibm_db.conn_errormsg())
def run_test_240(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result)): print str(i) + ":" + str(ibm_db.field_width(result, i)) print "\n-----" for i in range(0, ibm_db.num_fields(result2)): print str(i) + ":" + str( ibm_db.field_width(result2, ibm_db.field_name(result2, i))) print "\n-----" for i in range(0, 3): print str(i) + ":" + str(ibm_db.field_width( result3, i)) + "," + str(ibm_db.field_display_size(result3, i)) print "\n-----" print "region:%s" % ibm_db.field_type(result, 'region') print "5:%s" % ibm_db.field_type(result2, 5)
def run_test_064(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.tables(conn, None, 't') else: result = ibm_db.tables(conn, None, 'T') for i in range(0, ibm_db.num_fields(result)): print "%s, " % ibm_db.field_name(result, i) print print i = 0 row = ibm_db.fetch_tuple(result) while (row): ibm_db.num_fields(result) if (i < 4): print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n" i = i + 1 row = ibm_db.fetch_tuple(result) ibm_db.free_result(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_064(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.tables(conn, None, 't') else: result = ibm_db.tables(conn, None, 'T') for i in range(0, ibm_db.num_fields(result)): print("%s, " % ibm_db.field_name(result, i)) print() print() i = 0 row = ibm_db.fetch_tuple(result) while ( row ): ibm_db.num_fields(result) if (i < 4): print(", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n") i = i + 1 row = ibm_db.fetch_tuple(result) ibm_db.free_result(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_232(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") for i in range(0, ibm_db.num_fields(result) + 1): field_name = ibm_db.field_name(result,i) field_type = ibm_db.field_type(result, ibm_db.field_name(result,i)) print(str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i)))) print("-----") t = ibm_db.field_type(result,99) print(t) t1 = ibm_db.field_type(result, "HELMUT") print(t1)
def run_test_232(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") for i in range(0, ibm_db.num_fields(result) + 1): field_name = ibm_db.field_name(result,i) field_type = ibm_db.field_type(result, ibm_db.field_name(result,i)) print str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i))) print "-----" t = ibm_db.field_type(result,99) print t t1 = ibm_db.field_type(result, "HELMUT") print t1
def run_test_065(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.tables(conn, None, '%', "t3") else: result = ibm_db.tables(conn, None, '%', "T3") columns = ibm_db.num_fields(result) for i in range(0, columns): print("%s, " % ibm_db.field_name(result, i)) print("\n\n") row = ibm_db.fetch_tuple(result) while (row): final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , " row = ibm_db.fetch_tuple(result) print(final) ibm_db.free_result(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_065(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.tables(conn, None, '%', "t3") else: result = ibm_db.tables(conn, None, '%', "T3") columns = ibm_db.num_fields(result) for i in range(0, columns): print "%s, " % ibm_db.field_name(result, i) print "\n\n" row = ibm_db.fetch_tuple(result) while ( row ): final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , "; row = ibm_db.fetch_tuple(result) print final ibm_db.free_result(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def main(): conn = None stmt = None if not conf.has_option('passwd'): conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8')) try: conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'), conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '') stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8')) try: result = ibm_db.fetch_tuple(stmt) except: rows = ibm_db.num_rows(stmt) if rows != -1: print u'Обработано строк %d' % rows else: print u'Команда выполнена' return if result: column_conv = [] head = u'' underline=u'' for i in xrange(len(result)): if i != 0: head += u'|' underline += u'+' name = ibm_db.field_name(stmt, i) size = ibm_db.field_display_size(stmt, i) if len(name) > size: size = len(name) if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size: size = len(u'NULL') type_field = ibm_db.field_type(stmt, i) if type_field == 'float' or type_field == 'real' or type_field == 'decimal': column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float}) elif type_field == 'int' or type_field == 'bigint': column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int}) else: column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert}) head += name.center(size) underline += u'-' * size print head print underline while( result ): print conv(result, column_conv) result = ibm_db.fetch_tuple(stmt) else: print u'Результата не возвращено' except Exception as e: print >> sys.stderr, e sys.exit(-1) finally: if stmt: ibm_db.free_result(stmt) if conn: ibm_db.close(conn)
def getColumns(stmt): columns = [] types = [] colcount = 0 try: colname = ibm_db.field_name(stmt, colcount) coltype = ibm_db.field_type(stmt, colcount) while (colname != False): columns.append(colname) types.append(coltype) colcount += 1 colname = ibm_db.field_name(stmt, colcount) coltype = ibm_db.field_type(stmt, colcount) return columns, types except Exception as err: db2_error() return None
def run_test_125(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) result = ibm_db.exec_immediate(conn, "SELECT * FROM sales") result2 = ibm_db.exec_immediate(conn, "SELECT * FROM staff") for i in range(0, ibm_db.num_fields(result)): print("%d:%s" % (i, ibm_db.field_name(result,i))) print("-----") for i in range(0, ibm_db.num_fields(result2)): print("%d:%s" % (i, ibm_db.field_name(result2,i))) print("-----") if (server.DBMS_NAME[0:3] == 'IDS'): print("Region:%s" % ibm_db.field_name(result, 'region')) else: print("Region:%s" % ibm_db.field_name(result, 'REGION')) print("5:%s" % ibm_db.field_name(result2, 5))
def run_test_125(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) result = ibm_db.exec_immediate(conn, "SELECT * FROM sales") result2 = ibm_db.exec_immediate(conn, "SELECT * FROM staff") for i in range(0, ibm_db.num_fields(result)): print("%d:%s" % (i, ibm_db.field_name(result, i))) print("-----") for i in range(0, ibm_db.num_fields(result2)): print("%d:%s" % (i, ibm_db.field_name(result2, i))) print("-----") if (server.DBMS_NAME[0:3] == 'IDS'): print("Region:%s" % ibm_db.field_name(result, 'region')) else: print("Region:%s" % ibm_db.field_name(result, 'REGION')) print("5:%s" % ibm_db.field_name(result2, 5))
def run_test_241(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result)): print(str(ibm_db.field_width(result,i))) print("\n-----") for i in range(0, ibm_db.num_fields(result2)): print(str(ibm_db.field_width(result2,ibm_db.field_name(result2,i))))
def run_test_231(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result) + 1): print(str(i) + ":" + str(ibm_db.field_type(result,ibm_db.field_name(result,i)))) print("\n-----") for i in range(0, ibm_db.num_fields(result2)): print(str(i) + ":" + ibm_db.field_type(result2,ibm_db.field_name(result2,i))) print("\n-----") for i in range(0, 3): print(str(i) + ":" + ibm_db.field_type(result3,ibm_db.field_name(result3,i))) print("\n-----") print("region:%s" % ibm_db.field_type(result,'region')) print("5:%s" % ibm_db.field_type(result2,5))
def run_test_240(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result)): print str(i) + ":" + str(ibm_db.field_width(result,i)) print "\n-----" for i in range(0, ibm_db.num_fields(result2)): print str(i) + ":" + str(ibm_db.field_width(result2,ibm_db.field_name(result2,i))) print "\n-----" for i in range(0, 3): print str(i) + ":" + str(ibm_db.field_width(result3,i)) + "," + str(ibm_db.field_display_size(result3,i)) print "\n-----" print "region:%s" % ibm_db.field_type(result,'region') print "5:%s" % ibm_db.field_type(result2,5)
def run_test_122(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "drop table ftest" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "create table ftest ( \"TEST\" integer, \"test\" integer, \"Test\" integer )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO ftest values (1,2,3)" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.exec_immediate(conn, "SELECT * FROM ftest") num1 = ibm_db.field_name(stmt, 0) num2 = ibm_db.field_name(stmt, 1) num3 = ibm_db.field_name(stmt, 2) num4 = ibm_db.field_name(stmt, "TEST") num5 = ibm_db.field_name(stmt, 'test') num6 = ibm_db.field_name(stmt, 'Test') print("string(%d) \"%s\"" % (len(num1), num1)) print("string(%d) \"%s\"" % (len(num2), num2)) print("string(%d) \"%s\"" % (len(num3), num3)) print("string(%d) \"%s\"" % (len(num4), num4)) print("string(%d) \"%s\"" % (len(num5), num5)) print("string(%d) \"%s\"" % (len(num6), num6)) else: print("Connection failed.")
def run_test_122(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "drop table ftest" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "create table ftest ( \"TEST\" integer, \"test\" integer, \"Test\" integer )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO ftest values (1,2,3)" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.exec_immediate(conn, "SELECT * FROM ftest") num1 = ibm_db.field_name(stmt, 0) num2 = ibm_db.field_name(stmt, 1) num3 = ibm_db.field_name(stmt, 2) num4 = ibm_db.field_name(stmt, "TEST") num5 = ibm_db.field_name(stmt, 'test') num6 = ibm_db.field_name(stmt, 'Test') print "string(%d) \"%s\"" % (len(num1), num1) print "string(%d) \"%s\"" % (len(num2), num2) print "string(%d) \"%s\"" % (len(num3), num3) print "string(%d) \"%s\"" % (len(num4), num4) print "string(%d) \"%s\"" % (len(num5), num5) print "string(%d) \"%s\"" % (len(num6), num6) else: print "Connection failed."
exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Result set information:\n") print("COLUMN NAME MAXIMUM VALUE SIZE (IN BYTES)") print("_____________ _____________________________") # As Long As There Is Column Information, ... for loopCounter in range(0, numColumns): # Get The Name Of The Current Column colName = ibm_db.field_name(resultSet, loopCounter) # Get The Size Of The Data Allowed In The Current Column colWidth = ibm_db.field_width(resultSet, loopCounter) # Format And Display The Data Retrieved if (not colName is False) and (not colWidth is False): print("{:<13} {:>29}".format(colName, colWidth)) # Add A Blank Line To The End Of The Report print() # Close The Database Connection That Was Opened Earlier conn.closeConnection() # Return Control To The Operating System
def run_test_121(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed") name1 = ibm_db.field_name(stmt, 0) name2 = ibm_db.field_name(stmt, 1) name3 = ibm_db.field_name(stmt, 2) name4 = ibm_db.field_name(stmt, 3) if (server.DBMS_NAME[0:3] == 'IDS'): name5 = ibm_db.field_name(stmt, "breed") name6 = ibm_db.field_name(stmt, 7) name7 = ibm_db.field_name(stmt, '"nUMBER"') name8 = ibm_db.field_name(stmt, "number") else: name5 = ibm_db.field_name(stmt, "BREED") name6 = ibm_db.field_name(stmt, 7) name7 = ibm_db.field_name(stmt, '"Number"') name8 = ibm_db.field_name(stmt, "NUMBER") print("string(%d) \"%s\"" % (len(name1), name1)) print("string(%d) \"%s\"" % (len(name2), name2)) print("%s" % name3) print("%s" % name4) print("string(%d) \"%s\"" % (len(name5), name5)) print("%s" % name6) print("%s" % name7) print("string(%d) \"%s\"" % (len(name8), name8)) ibm_db.rollback(conn) else: print("Connection failed.")
def run_test_121(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.exec_immediate( conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed" ) name1 = ibm_db.field_name(stmt, 0) name2 = ibm_db.field_name(stmt, 1) name3 = ibm_db.field_name(stmt, 2) name4 = ibm_db.field_name(stmt, 3) if (server.DBMS_NAME[0:3] == 'IDS'): name5 = ibm_db.field_name(stmt, "breed") name6 = ibm_db.field_name(stmt, 7) name7 = ibm_db.field_name(stmt, '"nUMBER"') name8 = ibm_db.field_name(stmt, "number") else: name5 = ibm_db.field_name(stmt, "BREED") name6 = ibm_db.field_name(stmt, 7) name7 = ibm_db.field_name(stmt, '"Number"') name8 = ibm_db.field_name(stmt, "NUMBER") print "string(%d) \"%s\"" % (len(name1), name1) print "string(%d) \"%s\"" % (len(name2), name2) print "%s" % name3 print "%s" % name4 print "string(%d) \"%s\"" % (len(name5), name5) print "%s" % name6 print "%s" % name7 print "string(%d) \"%s\"" % (len(name8), name8) ibm_db.rollback(conn) else: print "Connection failed."
def run_test_064(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.tables(conn, None, 't') else: result = ibm_db.tables(conn, None, 'T') for i in range(0, ibm_db.num_fields(result)): print "%s, " % ibm_db.field_name(result, i) print print i = 0 row = ibm_db.fetch_tuple(result) while ( row ): ibm_db.num_fields(result) if (i < 4): print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n" i = i + 1 row = ibm_db.fetch_tuple(result) ibm_db.free_result(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4') #__END__ #__LUW_EXPECTED__ #TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, # #, T, T1, TABLE, , #, T, T2, TABLE, , #, T, T3, TABLE, , #, T, T4, TABLE, , #__ZOS_EXPECTED__ #TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, # #, T, T1, TABLE, , #, T, T2, TABLE, , #, T, T3, TABLE, , #, T, T4, TABLE, , #__SYSTEMI_EXPECTED__ #TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, # #, T, T1, TABLE, , #, T, T2, TABLE, , #, T, T3, TABLE, , #, T, T4, TABLE, , #__IDS_EXPECTED__ #table_cat, table_schem, table_name, table_type, remarks, # #, t, t1, TABLE%s, , #, t, t2, TABLE%s, , #, t, t3, TABLE%s, , #, t, t4, TABLE%s, ,
def schedule_tasks(conn, database, in_coll_lvl, env): print("Scheduling tasks ...") # enable Admin Task Scheduler print(" Setting Admin Task Scheduler registry variable ...") subprocess.check_call("db2set DB2_ATS_ENABLE=YES", shell=True) # load task_details.json file print(" Reading from task_details.json file ...") with open('task_details.json') as json_file: tasks = json.load(json_file) # insert tasks into IBMHIST.TAB_TASKS and schedule with Admin Task Scheduler for task in tasks: # get paramaters collection_name = task['collection_name'] collection_freq = task['collection_freq'] collection_class = task['collection_class'] collection_command = task['collection_command'] coll_lvl = int(task['collection_level']) collection_condition = task['collection_condition'] # check collection_level constraint if in_coll_lvl and coll_lvl > in_coll_lvl: continue # check collection_condition constraint skip_task = False for condition, fulfilled in env.items(): if condition in collection_condition and fulfilled is False: skip_task = True if skip_task: continue print(" Scheduling task: %s" % (collection_name)) # check command and generate header if collection_class == "SQL": # try sql command try: stmt = ibm_db.exec_immediate(conn, collection_command) except Exception as e: print(" Skipping due to error: %s" % e) continue # generate header for sql command as comma seperated field names header = [ ibm_db.field_name(stmt, i) for i in range(ibm_db.num_fields(stmt)) ] header = ",".join(header) + '\n' elif collection_class == "SYS": # try sys command try: devnull = open(os.devnull, 'w') subprocess.check_call(collection_command, shell=True, stdout=devnull, stderr=devnull) except Exception as e: print(" Skipping due to error: %s" % e) continue # generate header for sys command as collection name header = collection_name + '\n' # insert task into IBMHIST.TAB_TASKS ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_TASKS VALUES ('%s', '%s', '%s', '%s') " % (collection_name, collection_class, collection_command, header)) # schedule IBMHIST.PROC_COLLECT (database, collection_name) task ibm_db.exec_immediate( conn, "CALL SYSPROC.ADMIN_TASK_ADD ( '%s', NULL, NULL, NULL, '%s', 'IBMHIST', 'PROC_COLLECT', 'values (''%s'', ''%s'')', NULL, 'IBMHIST Collection' )" % (collection_name, collection_freq, database, collection_name)) # schedule archive task print(" Scheduling task: ARCHIVE") # schedule IBMHIST.PROC_ARCHIVE (database) task ibm_db.exec_immediate( conn, "CALL SYSPROC.ADMIN_TASK_ADD ( 'ARCHIVE', NULL, NULL, NULL, '*/10 * * * *', 'IBMHIST', 'PROC_ARCHIVE', 'values (''%s'')', NULL, 'IBMHIST Archival' )" % (database)) # collection will begin in approximately 5 minutes print(" Collection will begin in approximately 5 minutes ...")