Example #1
0
 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 )
Example #2
0
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)
Example #3
0
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)
Example #4
0
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)
Example #6
0
    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
Example #7
0
 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)"))
Example #10
0
    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)