def run_test_220(self):
if sys.platform == 'zos':
conn = ibm_db.pconnect(config.database,'','')
else:
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def summation(q, txtBefore, txtAfter):
sum_str = q[2]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if sum_stmt == False:
raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False:
raise Usage("Failed to execute the sum query")
sum = ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH + "/output.txt", "a")
f.write(txtBefore + str(sum) + txtAfter)
f.close()
except IOError, e:
raise Usage("Failed to manipulate output.txt.\n")
def summation(q):
sum_str = q[2]
# Connect to DB
start=time.time()
#sys.stderr.write("Start sum %s\n"%(start))
for n in range(int(round(NBSWAPS/NBSWAPTHREADS))):
#sys.stderr.write("Sum %d"%n)
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if (sum_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query")
sum= ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH, 'a')
f.write(str(sum)+'\n')
# f.close
except IOError, e:
raise Usage("Failed to manipulate sum.sql.\n")
finally:
def run_test_007(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0].strip()
data = ibm_db.fetch_both(stmt)
print ""
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0].strip()
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def _connect(self) -> ibm_db.IBM_DBConnection:
"""
Connecting to the host using required parameters and returning the `Connection` object
Returns:
a connection object that will be used in order to execute queries
"""
conn: ibm_db.IBM_DBConnection = None
try:
if self.use_persistent:
demisto.info("Initializing Persistent connection")
conn = ibm_db.pconnect(self.create_url,
"",
"",
conn_options=self._options())
else:
demisto.info("Initializing Non-Persistent connection")
conn = ibm_db.connect(self.create_url,
"",
"",
conn_options=self._options())
except Exception:
demisto.error(f"Connection State:\n{ibm_db.conn_error}")
demisto.error(f"Connection Error:\n{ibm_db.conn_errormsg()}")
raise DemistoException(
f"DB2 Connection Failed:\n{ibm_db.conn_errormsg()}")
return conn
def experiment(query_str,g):
# generate nb of parameters for query
matchList = re.findall('\?', query_str)
nbParams = len(matchList)
if (len(ATTLIST) != nbParams): raise Usage("Attribute missing (add appropriate -a option)")
# Connect to DB
conn = ibm_db.pconnect('DRIVER={IBM DB2 ODBC DRIVER};DATABASE='+DATABASE+';HOSTNAME='+HOSTNAME+';PORT='+str(PORT)+'; PROTOCOL=TCPIP;UID='+USERNAME+';PWD='+PASSWORD+';','','')
if conn is None: raise Usage(ibm_db.conn_errormsg())
# Prepare statement
query_stmt = ibm_db.prepare(conn, query_str)
if (query_stmt == False): raise Usage("Failed to prepare query")
# Execute statement
for i in range(NBQUERIES):
if (nbParams == 0):
if ibm_db.execute(query_stmt) == False:
raise Usage("Failed to execute the query")
else:
t = g.getWrite(i)
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(query_stmt, tuple(u)) == False:
raise Usage("Failed to execute the query")
nbtuples = 0
while (ibm_db.fetch_tuple(query_stmt) != False):
nbtuples += 1
print "Query"+str(i)+": "+str(nbtuples)+" fetched."
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def run_test_007(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
print("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def db2connection():
# connectionBBB = pyodbc.connect('DSN=SOO;UID=db2adminyang;PWD=Alan5799')
# connectionBBB = pyodbc.connect('DSN = SO;UID = db2adminyang;PWD = Alan5799')
# connectionBBB = pyodbc.connect('Provider=IBMOLEDB.DB2COPY1;Info=TRUE;ID=db2adminyang;Password=Alan5799;Source = SOGDB')
# connectionBBB = pyodbc.connect('DRIVER={IBM DB2 ODBC DRIVER - DB2COPY1};SERVER=127.0.0.0;DATABASE=SOGDB;UID=db2adminyang;PWD=Alan5799')
# connectionBBB = pyodbc.connect(
# DRIVER='{ODBC}',
# server='localhost',
# DATABASE='SOO',
# uid='db2adminyang',
# pwd='Alan5799')
conn = ibm_db.pconnect(
"DATABASE=SOGDB;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2adminyang;PWD=Alan5799",
"", "")
sql = "SELECT * from DB2ADMINYANG.MST_CUSTOMER"
stmt = ibm_db.exec_immediate(conn, sql)
c2 = ibm_db.fetch_both(stmt)
# while dictionary != False:
# print
# "The ID is : ", dictionary["EMPNO"]
# print
# "The Name is : ", dictionary[1]
# dictionary = ibm_db.fetch_both(stmt)
# c2 = connectionBBB.cursor()
return c2
def write(q,data):
# initialize vars
write_str = q
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
if TL:
ret = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
for t in data:
# execute insertN statement
if (WRITE_MODE == 'insertN'):
if ibm_db.execute(write_stmt, t) == False:
raise Usage("Failed to execute insertN statement")
elif (WRITE_MODE == 'updateN'):
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(write_stmt, tuple(u)) == False:
raise Usage("Failed to execute updateN statement")
if (TRANS_MODE == 'N'):
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
ibm_db.commit(conn)
#
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def connection():
try:
connect = ibm_db.pconnect(
"DATABASE=funder;HOSTNAME=dione.is.inf.uni-due.de;PORT=50008;PROTOCOL=TCPIP;UID=dbp008;PWD=quee3ahm;",
"", "")
except Exception as e:
print(e)
return connect
def run_test_220(self):
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def get_connection(dbname, hostname, port, userid, password):
conn_str = (
"DATABASE={};"
"HOSTNAME={};"
"PORT={};"
"PROTOCOL=TCPIP;"
"UID={};"
"PWD={};"
).format(dbname, hostname, port, userid, password)
logger.debug("DB2 Connection: {}".format(conn_str))
return ibm_db.pconnect(conn_str, "", "")
def __enter__(self):
try:
self._conn = ibm_db.pconnect(self.app.config['DBNAME']
, self.app.config['DBUSER']
, self.app.config['DBPW'])
except:
log.error("Database connection failed.")
log.error(ibm_db.conn_errormsg())
raise
else:
log.debug("Connected to {dbname} user {dbuser} using ****".format(dbname = self.app.config['DBNAME']
, dbuser = self.app.config['DBUSER']))
return self
def run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user, config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
def run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user, config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
def executeSelect(statement):
connection = ibm_db.pconnect(url, '', '')
statement = ibm_db.prepare(connection, statement)
ibm_db.execute(statement)
data = ibm_db.fetch_tuple(statement)
result = []
while (data):
result.append(data)
data = ibm_db.fetch_tuple(statement)
ibm_db.free_stmt(statement)
ibm_db.close(connection)
return result
def connect(self, config=None):
"""Returns a new database connection.
When `DB2_POOL_CONNECTIONS` is true a connection supporting pooling
is used.
:param config: configuration object used to establish connections.
current_app.config is used by default.
"""
if config is None:
config = current_app.config
conn_string = self._connection_string(config)
if config['DB2_POOL_CONNECTIONS']:
connection = ibm_db.pconnect(conn_string, '', '')
else:
connection = ibm_db.connect(conn_string, '', '')
return ibm_db_dbi.Connection(connection)
def conectaDB2():
try:
conn = ibm_db.pconnect(
"DATABASE=" + database + "; HOSTNAME=" + hostname + "; PORT=" +
port + "; PROTOCOL=" + protocol + "; UID=" + userid + "; PWD=" +
password + ";", '', '')
except:
print('Connection fault with DATABASE!\nError Code: {0}'.format(
ibm_db.conn_error()))
sys.exit()
info = ibm_db.client_info(conn)
print(
'CONNECTED! DATA_SOURCE_NAME: {0} CONN_CODEPAGE: {3} DRIVER_NAME: {1} DRIVER_ODBC_VER: {2} ODBC_VER: {4}'
.format(info.DATA_SOURCE_NAME, info.DRIVER_NAME, info.DRIVER_ODBC_VER,
info.CONN_CODEPAGE, info.ODBC_VER))
return conn
def connect(self, config=None):
"""Returns a new database connection.
When `DB2_POOL_CONNECTIONS` is true a connection supporting pooling
is used.
:param config: configuration object used to establish connections.
current_app.config is used by default.
"""
if config is None:
config = current_app.config
conn_string = self._connection_string(config)
if config['DB2_POOL_CONNECTIONS']:
connection = ibm_db.pconnect(conn_string, '', '')
else:
connection = ibm_db.connect(conn_string, '', '')
return ibm_db_dbi.Connection(connection)
def write(q,index,mutex):
# initialize vars
write_str = q[0]
matchList = re.findall('\?', write_str)
nbParams = len(matchList)
write_data = q[1]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
ret2 = None
if TL:
ret2 = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
print "Lock: %s"%ret
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
# Perform insertions/updates
for i in range(NBWRITES/NBTHREADS):
mutex.acquire()
v = index.value
index.value -= 1
mutex.release()
t = write_data.getWrite(v)
if v >= 0 and t != None:
# execute insertN statement
if (WRITE_MODE == 'insertN'):
if ibm_db.execute(write_stmt, t) == False:
raise Usage("Failed to execute insertN statement")
elif (WRITE_MODE == 'updateN'):
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(write_stmt, tuple(u)) == False:
raise Usage("Failed to execute updateN statement")
if (TRANS_MODE == 'N'):
print "commit"
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
print "commit"
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def update1(q):
write_str = q[0]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(write_stmt) == False:
raise Usage("Failed to execute the sum query")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def __init__(self, connection, configuration):
# connection and configuration are passed in the arguments for transmit
# ex: python main.py transmit db2 '{"host":"localhost", "port":"6603"}' '{"auth": {"mysql_username": "******","mysql_password": "******", "mysql_hostname": "localhost", "mysql_database": "sampledb" } }' pin
self.auth = configuration.get('auth')
try:
self.client = ibm_db.pconnect(
"DATABASE=" + self.auth.get("mysql_database") + ";HOSTNAME=" +
connection.get("host", "") + ";PORT=" +
connection.get("port", "31490") + ";UID=" +
self.auth.get("mysql_username") + ";PWD=" +
self.auth.get("mysql_password"), "", "")
self.state = ibm_db.active(self.client)
# handle exceptions later TODO
except Exception as e:
raise e
def swap(q):
swap1_str = q[0]
swap2_str = q[1]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare Statements
swap1_stmt = ibm_db.prepare(conn, swap1_str)
if swap1_stmt == False:
raise Usage("Failed to prepare swap1 query")
swap2_stmt = ibm_db.prepare(conn, swap2_str)
if swap2_stmt == False:
raise Usage("Failed to prepare swap2 update")
# Execute Statements
nbrep = int(round(NBSWAPS / NBSWAPTHREADS))
for i in range(nbrep):
x = random.randint(RANGE_LOW, RANGE_UP / 2)
y = random.randint(x, RANGE_UP)
if ibm_db.execute(swap1_stmt, (x,)) == False:
raise Usage("Failed to execute the swap1 query (x)")
valX = ibm_db.fetch_tuple(swap1_stmt)
if valX == False:
raise Usage("Failed to iterate over the swap1 result set (x)")
if ibm_db.execute(swap1_stmt, (y,)) == False:
raise Usage("Failed to execute the swap1 query (y)")
valY = ibm_db.fetch_tuple(swap1_stmt)
if valY == False:
raise Usage("Failed to iterate over the swap1 result set (y)")
time.sleep(0.1)
if ibm_db.execute(swap2_stmt, (valY[0], x)) == False:
raise Usage("Failed to execute the swap2 query (x, valY)")
if ibm_db.execute(swap2_stmt, (valX[0], y)) == False:
raise Usage("Failed to execute the swap1 query (y, valX)")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False:
raise Usage("Failed to close db connection.\n")
def connect_db(self, db, pconnect=True, trytimes=3):
try:
if pconnect:
conn = ibm_db.pconnect(db, "", "")
else:
conn = ibm_db.connect(db, "", "")
except Exception:
if pconnect:
self.log.error('Database persistent connect failted('
+ str(trytimes) + '): ' + traceback.format_exc())
else:
self.log.error('Database connect failted('
+ str(trytimes) + '): ' + traceback.format_exc())
try:
if ibm_db.active(conn):
self.server_info = self.get_server_info(conn)
return conn
except Exception:
pass
if pconnect:
self.log.error('Database persistent connect inactive(' + str(trytimes) + ')')
else:
self.log.error('Database connect inactive(' + str(trytimes) + ')')
if trytimes > 0:
try:
ibm_db.close(conn)
except Exception:
pass
self.log.info('Database connect try again(' + str(trytimes) + ')')
trytimes -= 1
return self.connect_db(db, pconnect=False, trytimes=trytimes)
return False
else:
userid = args.dbname
if args.schema:
schema = args.schema
else:
schema = userid
conn_str = (
"DATABASE={};"
"HOSTNAME={};"
"PORT={};"
"PROTOCOL=TCPIP;"
"UID={};"
"PWD={};"
).format(args.dbname, args.hostname, args.port, userid, args.password)
logger.debug("DB2 Connection: {}".format(conn_str))
conn = ibm_db.pconnect(conn_str, "", "")
if args.output_file:
fout = open(args.output_file, 'w')
else:
fout = sys.stdout
outputcsv = bool(distutils.util.strtobool(args.outputcsv))
report_changes_for_contexts(schema, fout, outputcsv)
endtime = datetime.utcnow()
logger.info("End of Script: {}".format(endtime))
print("Script ran for: {}".format(endtime - starttime))
except Exception as e:
conn_error = ibm_db.conn_error()
stmt_error = ibm_db.stmt_error()
if conn_error != '':
print("Error Code: {} Msg: {}".format(conn_error, ibm_db.conn_errormsg()))
elif stmt_error != '':
def main(self):
args = self.get_option()
dbname = args.dbname # args1
input_file = args.input_file # args2
rows = args.rows # args3
username = connect_conf.username
password = connect_conf.password
schema = username.upper()
l_list = []
g_list = []
col_types = []
col_sizes = []
col_size_fractions = []
col_num = 0
count = 0
COMMIT_ROW = 1000
# connect
try:
conn = ibm_db.pconnect(dbname, username, password)
except Exception as e:
print("Connection failed")
traceback.print_exc()
return
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# get the sql
try:
insert_sql = self.get_sql(input_file)
except Exception as e:
print("Cannot read sqlfile.")
traceback.print_exc()
return
stmt = ibm_db.prepare(conn, insert_sql)
if not stmt:
print("Failed in prepare.")
return
# get the table name from sql, and check
tabname = re.split("[ ]*INSERT INTO[ ]+", insert_sql)[1]
tabname = re.split("[ ]+VALUES", tabname)[0]
if tabname is None:
print("Error in contents of sqlfile.")
return
# verify whether the table exist
rc = self.chk_table(conn, schema, tabname)
if not rc:
print("table name " + tabname + " is not found in " + dbname)
return
# get the number of the columns
result = ibm_db.columns(conn, None, None, tabname)
col = ibm_db.fetch_tuple(result)
while (col):
col_num = col_num + 1
col = ibm_db.fetch_tuple(result)
# list of column type and column size
col_info = ibm_db.columns(conn, None, None, tabname)
col = ibm_db.fetch_tuple(col_info)
while (col):
col_types.append(col[5])
col_sizes.append(col[6])
col_size_fractions.append(col[8])
col = ibm_db.fetch_tuple(col_info)
for i in range(rows):
count = 0
random.seed()
for j in range(len(col_types)):
count = count + 1
if self.chk_par_mark(count, insert_sql):
if col_types[j] == "CHAR":
param = self.get_random_char(col_sizes[j])
elif col_types[j] == "VARCHAR":
param = self.get_random_char(col_sizes[j])
elif col_types[j] == "SMALLINT":
param = self.get_random_smallint()
elif col_types[j] == "INTEGER":
param = self.get_random_integer()
elif col_types[j] == "BIGINT":
param = self.get_random_long()
elif col_types[j] == "REAL":
param = self.get_random_real()
elif col_types[j] == "FLOAT":
param = self.get_random_double()
elif col_types[j] == "DOUBLE":
param = self.get_random_double()
elif col_types[j] == "DECIMAL":
digit = col_sizes[j] - col_size_fractions[j]
param = self.get_random_decimal(digit)
elif col_types[j] == "NUMERIC":
digit = col_sizes[j] - col_size_fractions[j]
param = self.get_random_decimal(digit)
elif col_types[j] == "DATE":
param = self.get_random_date()
elif col_types[j] == "TIME":
param = self.get_random_time()
elif col_types[j] == "TIMESTAMP":
param = self.get_random_timestamp()
elif col_types[j] == "BLOB":
param = self.get_random_byte(col_sizes[j])
elif col_types[j] == "CLOB":
param = self.get_random_char(col_sizes[j])
else:
param = ''
# set the parameter to the list
self.set_param_list(param, l_list)
# end of the columns
if count == col_num:
self.concat_list(g_list, l_list)
l_list = []
if ((i + 1) % COMMIT_ROW == 0):
#print g_list
rc = ibm_db.execute_many(
stmt, tuple(tuple(x) for x in g_list))
rc = ibm_db.commit(conn)
g_list = []
if len(g_list) != 0:
print g_list
rc = ibm_db.execute_many(stmt, tuple(tuple(x) for x in g_list))
rc = ibm_db.commit(conn)
ibm_db.close(conn)
def run_test_trusted_context_pconnect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn,
sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print "Connection failed."
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
config.database, config.hostname, config.port, config.auth_user,
config.auth_pass)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.pconnect(dsn, "", "", options)
if tc_conn:
print "Trusted connection succeeded."
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print "User has been switched."
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print ibm_db.stmt_errormsg()
ibm_db.close(tc_conn)
else:
print "Trusted connection failed."
# Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections)
for i in xrange(10):
tc_conn = ibm_db.pconnect(dsn, "", "")
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print "Explicit Trusted Connection succeeded."
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print "Connection succeeded."
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print "Connection failed."
def connect_db():
return ibm_db.pconnect("DATABASE=ctddev;HOSTNAME=192.168.1.246;PORT=50010;UID=db2dev;PWD=db2dev","","")
def get_connection():
conn = ibm_db.pconnect(st.secrets['dsn'], st.secrets['user'], st.secrets['pws'])
return conn
def connectioncheck_handler(): global connection,url if (active(connection) == False): connection = ibm_db.pconnect(url ,'' ,'') return None
connString += ";PORT=" + portNum # Required To Connect To A Server
connString += ";PROTOCOL=TCPIP" # Required To Connect To A Server
connString += ";UID=" + userID
connString += ";PWD=" + passWord
# Display A Status Message Indicating An Attempt To Establish Ten Connections To A Remote
# Db2 Server Is About To Be Made
print("\nEstablishing 10 connections to the \'" + hostName +
"\' server ... \n")
# Establish Ten Connections To The Db2 Server Specified
for loopCounter in range(10):
# Attempt To Establish A Db2 Server Connection
try:
svrConnection[loopCounter] = ibm_db.pconnect(connString, '', '')
except Exception:
pass
# If A Connection Could Not Be Established, Display An Error Message And Continue
if svrConnection[loopCounter] is None:
print("\nERROR: Unable to connect to the \'" + hostName + "\' server.")
continue
# Otherwise, Display A "Connection Ready" Status Message
else:
print(" Connection {:>2} ready!".format(loopCounter + 1))
# Add A Blank Line To The End Of The List Of Connections Created
print()
def __init__(self):
with open('env/dash_db.json') as config:
self.cred = json.load(config)
self.conn = ibm_db.pconnect(self.cred["dsn"], self.cred["username"],
self.cred["password"])
def run_test_trusted_context_pconnect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print("Connection failed.")
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.pconnect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Trusted connection failed.")
# Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections)
for i in range(10):
tc_conn = ibm_db.pconnect(dsn, "", "")
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("Explicit Trusted Connection succeeded.")
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print("Connection succeeded.")
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print("Connection failed.")
import ibm_db
import ibm_db_dbi
import config
import pprint
p = pprint.PrettyPrinter(indent=4)
ibm_conn = ibm_db.pconnect(
'DATABASE={dbna};HOSTNAME={host};PORT={port};PROTOCOL={prot};UID={user};PWD={pasw}'
.format(dbna=config.DATABASE,
host=config.HOSTNAME,
port=config.PORTNUMB,
prot=config.PROTOCOL,
user=config.USERNAME,
pasw=config.PASSWORD), '', '')
conn = ibm_db_dbi.Connection(ibm_conn)
cursor = conn.cursor()
# Command authority Checker (can be used inside of commands)
class AuthorityCheck:
def __init__(self,
connection,
bot_roles_table='bot_roles',
actions='actions',
permissions='permissions',
guild_roles='guild_roles',
debug=False,
debug_verbose=False):
# Database connection
self.conn = connection
def connectioncheck_handler(): global connection,url if (active(connection) == False): connection = ibm_db.pconnect(url ,'' ,'') return None
log.loc[log[prefix+'timestamp'].notna(),prefix+'month'] = \
log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[:2].astype(int)
log.loc[log[prefix+'timestamp'].notna(),prefix+'year'] = \
log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[6:10].astype(int)
log.loc[log[prefix+'timestamp'].notna(),prefix+'hour'] = \
log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[10:12].astype(int)
log.loc[log[prefix+'timestamp'].notna(),prefix+'min'] = \
log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[13:15].astype(int)
log.loc[log[prefix+'timestamp'].notna(),prefix+'sec'] = \
log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[17:].astype(int)
#DB2 database credentials
credencial_db2 = {}
# Establish conection
db2 = ibm_db.pconnect(credencial_db2['dsn'], "", "")
# Parameters for different executions
# Dropping table
drop = False
if drop:
q_drop = "DROP TABLE gen_info;"
# Deleting table
delete = True
if delete:
q_delete = "DELETE * FROM gen_info"
ibm_db.exec_immediate(db2, q_drop)
# Creating table
create = True
f.close()
###############################################################################
# Check that two file names were entered
if len(sys.argv) is not 3 or sys.argv[2][sys.argv[2].rfind(".")+1:] != "cfg":
print "[*] Usage: python main.py [ddl] [config.cfg] - see README for config format"
sys.exit()
# read in config sections
node1 = config('node1', sys.argv[2])
node2 = config('node2', sys.argv[2])
catalog = config('catalog', sys.argv[2])
# make persistant connections to distributed databases
db1 = ibm_db.pconnect(node1['hostname'], node1['username'],node1['passwd'])
db2 = ibm_db.pconnect(node2['hostname'], node2['username'],node2['passwd'])
cat = ibm_db.pconnect(catalog['hostname'], catalog['username'],catalog['passwd'])
# create catalog if doesn't exist
create_catalog(cat,catalog)
# list of nodes and queries to iterate through
nodes = [db1,db2]
querys = readDDL(sys.argv[1])
# foreach DDL, execute queries on all nodes
for query in querys:
for node in nodes:
Thread(target=exec_query,args=(node,query,)).start()
if node is db1:
def pconnDb(dbname, dbuser, dbpasswd, host='182.29.0.1', port=51001):
array = { db2.SQL_ATTR_AUTOCOMMIT : db2.SQL_AUTOCOMMIT_OFF }
conn = db2.pconnect("HOSTNAME=%s;PROTOCOL=TCPIP;\
PORT=%d;DATABASE=%s;UID=%s;PWD=%s;" \
%(host, port, dbname, dbuser, dbpasswd), '', '',array)
return conn
sys.exit()
# read in config sections
node1 = config_extract(sys.argv[1], 'node1')
node2 = config_extract(sys.argv[1], 'node2')
catalog = config_extract(sys.argv[1], 'catalog')
# Create strings for connections
#db1_info = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s" % (node1["hostname"],node1["ip"],node1["port"],node1["username"],node1["passwd"])
#db2_info = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s" % (node2["hostname"],node2["ip"],node2["port"],node2["username"],node2["passwd"])
#catalog_info = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s" % (catalog["hostname"],catalog["ip"],catalog["port"],catalog["username"],catalog["passwd"])
#print db1_info
# make persistant connections to distributed databases
db1 = ibm_db.pconnect(node1["hostname"],node1["username"],node1["passwd"])
db2 = ibm_db.pconnect(node2["hostname"],node2["username"],node2["passwd"])
cat = ibm_db.pconnect(catalog["hostname"],catalog["username"],catalog["passwd"])
# create catalog if doesn't exist
create_catalog(cat,catalog)
# list of nodes and queries to iterate through
nodes = [db1,db2]
querys = readDDL(sys.argv[2])
# foreach DDL, execute queries on all nodes
for query in querys:
query = query.rstrip()
if query == ';':
continue
