def _cursor( self, connection, kwargs ):
if not connection:
kwargsKeys = kwargs.keys()
if ( kwargsKeys.__contains__( 'port' ) and
kwargsKeys.__contains__( 'host' ) ):
kwargs['dsn'] = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;" % (
kwargs.get( 'database' ),
kwargs.get( 'host' ),
kwargs.get( 'port' )
)
else:
kwargs['dsn'] = kwargs.get( 'database' )
# Setting AUTO COMMIT off on connection.
conn_options = {Database.SQL_ATTR_AUTOCOMMIT : Database.SQL_AUTOCOMMIT_OFF}
kwargs['conn_options'] = conn_options
if kwargsKeys.__contains__( 'options' ):
kwargs.update( kwargs.get( 'options' ) )
del kwargs['options']
if kwargsKeys.__contains__( 'port' ):
del kwargs['port']
pconnect_flag = True
if kwargsKeys.__contains__( 'PCONNECT' ):
pconnect_flag = kwargs['PCONNECT']
del kwargs['PCONNECT']
if pconnect_flag:
connection = Database.pconnect( **kwargs )
else:
connection = Database.connect( **kwargs )
connection.autocommit = connection.set_autocommit
return connection, DB2CursorWrapper( connection )
else:
return DB2CursorWrapper( connection )
def root():
sorting = request.forms.get('sorting') or '""'
reset_parm = 'YES' if request.forms.get('reset') == 'true' else 'NO'
show_columns_all = (
'JOB_NAME', 'AUTHORIZATION_NAME', 'JOB_TYPE', 'FUNCTION_TYPE',
'FUNCTION', 'JOB_STATUS', 'ELAPSED_INTERACTION_COUNT',
'ELAPSED_TOTAL_RESPONSE_TIME', 'ELAPSED_TOTAL_DISK_IO_COUNT',
'ELAPSED_ASYNC_DISK_IO_COUNT', 'ELAPSED_SYNC_DISK_IO_COUNT',
'ELAPSED_CPU_PERCENTAGE', 'ELAPSED_CPU_TIME',
'ELAPSED_PAGE_FAULT_COUNT' )
hide_columns = ( 'ELAPSED_TIME', )
show_columns = tuple(request.forms.getall('selected_columns')) if request.method == 'POST' else show_columns_all
column_dict = dict([(column, column in show_columns) for column in show_columns_all])
all_columns = show_columns + hide_columns
columns_string = ",".join(all_columns)
query = "select %s from table(qsys2.active_job_info(RESET_STATISTICS => ?)) x where job_type <> 'SBS'" % columns_string
conn = dbi.pconnect(dsn=None, database='*LOCAL', user=None, password=None)
cur = conn.cursor()
cur.execute(query, (reset_parm, ))
elapsed_time = 0
row_data = []
for row in cur:
row_data.append(row[0 : len(show_columns)])
elapsed_time = row[-1]
headers = [capwords(column.replace('_', ' ')) for column in show_columns]
return template('task8', rows=row_data, headers=headers, elapsed_time=elapsed_time, sorting=sorting, columns=show_columns_all, column_info=column_dict)
def root():
query = "select subsystem, job_name, function_type, function, elapsed_time from table(qsys2.active_job_info()) x"
conn = dbi.pconnect(dsn=None, database='*LOCAL', user=None, password=None)
cur = conn.cursor()
cur.execute(query)
return template('task4', rows=cur)
def root():
query = "select subsystem, job_name, function_type, function, job_type, elapsed_time from table(qsys2.active_job_info()) x where job_type <> 'SBS'"
conn = dbi.pconnect(dsn=None, database='*LOCAL', user=None, password=None)
cur = conn.cursor()
cur.execute(query)
headers = [descr[0] for descr in cur.description]
return template('task5', rows=cur, headers=headers)
def root():
reset_parm = 'YES' if request.forms.get('reset') == 'true' else 'NO'
query = "select subsystem, job_name, function_type, function, job_type, elapsed_time from table(qsys2.active_job_info(RESET_STATISTICS => ?)) x where job_type <> 'SBS'"
conn = dbi.pconnect(dsn=None, database='*LOCAL', user=None, password=None)
cur = conn.cursor()
cur.execute(query, (reset_parm, ))
headers = [descr[0] for descr in cur.description]
return template('task5_final', rows=cur, headers=headers)
def get_new_connection(self, kwargs):
kwargsKeys = kwargs.keys()
if (kwargsKeys.__contains__('port')
and kwargsKeys.__contains__('host')):
kwargs[
'dsn'] = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;" % (
kwargs.get('database'), kwargs.get('host'),
kwargs.get('port'))
else:
kwargs['dsn'] = kwargs.get('database')
# Before Django 1.6, autocommit was turned OFF
if (djangoVersion[0:2] >= (1, 6)):
conn_options = {
Database.SQL_ATTR_AUTOCOMMIT: Database.SQL_AUTOCOMMIT_ON
}
else:
conn_options = {
Database.SQL_ATTR_AUTOCOMMIT: Database.SQL_AUTOCOMMIT_OFF
}
kwargs['conn_options'] = conn_options
if kwargsKeys.__contains__('options'):
kwargs.update(kwargs.get('options'))
del kwargs['options']
if kwargsKeys.__contains__('port'):
del kwargs['port']
pconnect_flag = False
if kwargsKeys.__contains__('PCONNECT'):
pconnect_flag = kwargs['PCONNECT']
del kwargs['PCONNECT']
if pconnect_flag:
connection = Database.pconnect(**kwargs)
else:
connection = Database.connect(**kwargs)
connection.autocommit = connection.set_autocommit
return connection
def get_new_connection(self, kwargs):
kwargsKeys = kwargs.keys()
if ( kwargsKeys.__contains__( 'port' ) and
kwargsKeys.__contains__( 'host' ) ):
kwargs['dsn'] = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;" % (
kwargs.get( 'database' ),
kwargs.get( 'host' ),
kwargs.get( 'port' )
)
else:
kwargs['dsn'] = kwargs.get( 'database' )
# Before Django 1.6, autocommit was turned OFF
if ( djangoVersion[0:2] >= ( 1, 6 )):
conn_options = {Database.SQL_ATTR_AUTOCOMMIT : Database.SQL_AUTOCOMMIT_ON}
else:
conn_options = {Database.SQL_ATTR_AUTOCOMMIT : Database.SQL_AUTOCOMMIT_OFF}
kwargs['conn_options'] = conn_options
if kwargsKeys.__contains__( 'options' ):
kwargs.update( kwargs.get( 'options' ) )
del kwargs['options']
if kwargsKeys.__contains__( 'port' ):
del kwargs['port']
pconnect_flag = False
if kwargsKeys.__contains__( 'PCONNECT' ):
pconnect_flag = kwargs['PCONNECT']
del kwargs['PCONNECT']
if pconnect_flag:
connection = Database.pconnect( **kwargs )
else:
connection = Database.connect( **kwargs )
connection.autocommit = connection.set_autocommit
return connection
def get_new_connection(self, kwargs):
SchemaFlag= False
scrollable_cursor = False
kwargsKeys = list(kwargs.keys())
if ( kwargsKeys.__contains__( 'port' ) and
kwargsKeys.__contains__( 'host' ) ):
kwargs['dsn'] = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;" % (
kwargs.get( 'database' ),
kwargs.get( 'host' ),
kwargs.get( 'port' )
)
else:
kwargs['dsn'] = kwargs.get( 'database' )
if ( kwargsKeys.__contains__( 'currentschema' )):
kwargs['dsn'] += "CurrentSchema=%s;" % ( kwargs.get( 'currentschema' ))
del kwargs['currentschema']
if ( kwargsKeys.__contains__( 'security' )):
kwargs['dsn'] += "security=%s;" % ( kwargs.get( 'security' ))
del kwargs['security']
if ( kwargsKeys.__contains__( 'sslclientkeystoredb' )):
kwargs['dsn'] += "SSLCLIENTKEYSTOREDB=%s;" % ( kwargs.get( 'sslclientkeystoredb' ))
del kwargs['sslclientkeystoredb']
if ( kwargsKeys.__contains__( 'sslclientkeystoredbpassword' )):
kwargs['dsn'] += "SSLCLIENTKEYSTOREDBPASSWORD=%s;" % ( kwargs.get( 'sslclientkeystoredbpassword' ))
del kwargs['sslclientkeystoredbpassword']
if ( kwargsKeys.__contains__( 'sslclientkeystash' )):
kwargs['dsn'] += "SSLCLIENTKEYSTASH=%s;" % ( kwargs.get( 'sslclientkeystash' ))
del kwargs['sslclientkeystash']
if ( kwargsKeys.__contains__( 'sslservercertificate' )):
kwargs['dsn'] += "SSLSERVERCERTIFICATE=%s;" % ( kwargs.get( 'sslservercertificate' ))
del kwargs['sslservercertificate']
# Before Django 1.6, autocommit was turned OFF
if ( djangoVersion[0:2] >= ( 1, 6 )):
conn_options = {Database.SQL_ATTR_AUTOCOMMIT : Database.SQL_AUTOCOMMIT_ON}
else:
conn_options = {Database.SQL_ATTR_AUTOCOMMIT : Database.SQL_AUTOCOMMIT_OFF}
kwargs['conn_options'] = conn_options
if kwargsKeys.__contains__( 'options' ):
kwargs.update( kwargs.get( 'options' ) )
if (ibm_db.SQL_ATTR_CURSOR_TYPE in kwargs.get('conn_options') and
kwargs.get('conn_options')[ibm_db.SQL_ATTR_CURSOR_TYPE] == ibm_db.SQL_CURSOR_KEYSET_DRIVEN):
scrollable_cursor = True
del kwargs['options']
if kwargsKeys.__contains__( 'port' ):
del kwargs['port']
pconnect_flag = False
if kwargsKeys.__contains__( 'PCONNECT' ):
pconnect_flag = kwargs['PCONNECT']
del kwargs['PCONNECT']
if pconnect_flag:
connection = Database.pconnect( **kwargs )
else:
connection = Database.connect( **kwargs )
connection.autocommit = connection.set_autocommit
if SchemaFlag:
schema = connection.set_current_schema(currentschema)
if scrollable_cursor:
# The documentation of ibm_db.connect indicates that you could pass
# a dictionary of options
# https://github.com/ibmdb/python-ibmdb/wiki/APIs#ibm_dbconnect .
# However, the dictionary of options gets ignored. Therefore,
# the options need to be set AFTER the connection is established.
#
# You can see the code preceeding setting
# AUTOCOMMIT and CURRENTSCHEMA options in much the same way and
# probably working around the same bug.
connection.set_option(
{ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
return connection
if __name__ == '__main__':
# process command line arguments
# - library name can be passed if you want to use something other than QGPL
if len(sys.argv) > 1:
library_name = sys.argv[1].upper()
else:
library_name = 'IBMIOSS'
# unzip the names.zip file
zf = zipfile.ZipFile('contacts.zip')
zf.extractall()
# connect to the local IBM i database
conn = dbi.pconnect('DATABASE=*LOCAL')
c = conn.cursor()
# try to create a library
itool = iToolKit()
itool.add(iCmd('crtlib', "CRTLIB LIB(%s) TEXT('IBMIOSS Test Data')" % (library_name)))
itool.call(config.itransport)
# delete the table - will fail if table doesn't exist
itool = iToolKit()
print ('Change current library...')
itool.add(iCmd('chgcurlib', "CHGCURLIB CURLIB(%s)" % (library_name)))
print ('End journaling...')
itool.add(iCmd('endjrn', "ENDJRNPF FILE(*CURLIB/CONTACT)"))
print ('Delete journals and receivers...')
itool.add(iCmd('dltjrn', "DLTJRN JRN(*CURLIB/IBMIOSSJRN)"))
def get_new_connection(self, kwargs):
SchemaFlag = False
kwargsKeys = kwargs.keys()
if (kwargsKeys.__contains__('port')
and kwargsKeys.__contains__('host')):
kwargs[
'dsn'] = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;" % (
kwargs.get('database'), kwargs.get('host'),
kwargs.get('port'))
else:
kwargs['dsn'] = kwargs.get('database')
if (kwargsKeys.__contains__('currentschema')):
kwargs['dsn'] += "CurrentSchema=%s;" % (
kwargs.get('currentschema'))
currentschema = kwargs.get('currentschema')
SchemaFlag = True
del kwargs['currentschema']
if (kwargsKeys.__contains__('security')):
kwargs['dsn'] += "security=%s;" % (kwargs.get('security'))
del kwargs['security']
if (kwargsKeys.__contains__('sslclientkeystoredb')):
kwargs['dsn'] += "SSLCLIENTKEYSTOREDB=%s;" % (
kwargs.get('sslclientkeystoredb'))
del kwargs['sslclientkeystoredb']
if (kwargsKeys.__contains__('sslclientkeystoredbpassword')):
kwargs['dsn'] += "SSLCLIENTKEYSTOREDBPASSWORD=%s;" % (
kwargs.get('sslclientkeystoredbpassword'))
del kwargs['sslclientkeystoredbpassword']
if (kwargsKeys.__contains__('sslclientkeystash')):
kwargs['dsn'] += "SSLCLIENTKEYSTASH=%s;" % (
kwargs.get('sslclientkeystash'))
del kwargs['sslclientkeystash']
if (kwargsKeys.__contains__('sslservercertificate')):
kwargs['dsn'] += "SSLSERVERCERTIFICATE=%s;" % (
kwargs.get('sslservercertificate'))
del kwargs['sslservercertificate']
# Before Django 1.6, autocommit was turned OFF
if (djangoVersion[0:2] >= (1, 6)):
conn_options = {
Database.SQL_ATTR_AUTOCOMMIT: Database.SQL_AUTOCOMMIT_ON
}
else:
conn_options = {
Database.SQL_ATTR_AUTOCOMMIT: Database.SQL_AUTOCOMMIT_OFF
}
kwargs['conn_options'] = conn_options
if kwargsKeys.__contains__('options'):
kwargs.update(kwargs.get('options'))
del kwargs['options']
if kwargsKeys.__contains__('port'):
del kwargs['port']
pconnect_flag = False
if kwargsKeys.__contains__('PCONNECT'):
pconnect_flag = kwargs['PCONNECT']
del kwargs['PCONNECT']
if pconnect_flag:
connection = Database.pconnect(**kwargs)
else:
connection = Database.connect(**kwargs)
connection.autocommit = connection.set_autocommit
if SchemaFlag:
schema = connection.set_current_schema(currentschema)
return connection
# This is asimple service examples that returns json array of arrays of data
# Objects is not created for simplicity reasons
# Niels Liisberg 2019.11.01, System & Method - Project Sitemule
# --------------------------------------------------------------------------
# Importing flask module in the project is mandatory
# An object of Flask class is our WSGI application.
from flask import Flask
from flask import jsonify
import ibm_db_dbi as db
# Flask constructor takes the name of
# current module (__name__) as argument.
app = Flask(__name__)
#conn = db.pconnect (dsn=None, user='******', password='******' , database='*LOCAL',conn_options=None)
conn = db.pconnect(database='*LOCAL')
# The route() function of the Flask class is a decorator,
# which tells the application which URL should call
# the associated function.
# http://dksrv131:5000/list_users_by_proc
@app.route('/list_users_by_proc')
def list_users_by_proc():
cur = conn.cursor()
cur.callproc('microdemo.user_list')
rows = cur.fetchall()
cur.close()
return jsonify(rows)
