IBMi SQL Services with Open Source and Python

IBMi SQL services are nothing new, IBM are creating more functionality within SQL and DB2 in the next TR.

In order to get at these with ease, I have created a very simple dashboard written in python (procedural for the time being) to demonstrate how easy this is to access. I prefer to demonstrate working solutions rather than code snippets as examples. You get to see how it all fits together.

Our simple dashboard uses Tkinter in python to show these views. It uses pyodbc to connect to an IBMi server and renders well on Windows and Mac, all from exactly the same code base.

You can update/filter the results just by changing the SQL code that is called.

Here is a look at what is generated.

Rendered in Windows

Rendered in macOS Catalina

As Tkinter comes as standard in Python the same code works well on Linux, Windows and macOS. The rest of the examples will be windows based but work the same in any environment.

The first example shows remote connections to the IBMi using the NETSTAT_INFO view. I have masked the remote address which can be unmasked by removing one line of code, I've marked it in the comments.

The second example shows Journal information across the whole system using the JOURNAL_INFO view, making it easy to identify those disk hogs.

The next service basically shows the WRKDSKINF using the SYSDISKSTAT view. Unit type 0 is not a solid state disk(spinning) and unit type 1 is a solid state disk.

Finally a service that displays information regarding user profiles using the USER_INFO view. Some relevant security information is displayed.

If you need to log on as a different user I've added the Log-off button which clears the connection and disables all the buttons except of course for the Log-in Button.

To get this example to run you will need Data Source Names configured on your system - I have installed ACS on both my mac and windows machine which will require configuration unless you have already done so. The default DNS name can be changed quite easily with the source.

If you would like something else built then message me via our contact page or LinkedIn we are starting to include python development into the services that we offer.

If you like this or find it useful then let me know. Come back to our website and I will post updates with source of course.

Show me what you've done to it and I'll include it here.

 

Here is the source code..

#---------------------------------------------------------
# This program is free software, you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by
# the Free Software Foundation. See GNU General Public License for detail.
#
# Copyright (C) 2020 KDP Software Limited
#
# KDP Software makes no representations about the suitability of this software for any
# Purpose. It is provided "as is" without express or implied warranty.
#
# KDP Software Limited DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING ALL
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO EVENT SHALL KDP Software Limited
# BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER
# RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
# OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF
# THIS SOFTWARE.
#
# Contact This email address is being protected from spambots. You need JavaScript enabled to view it. or https://www.kdpsoftare.co.uk using the contact page

from tkinter import *
from tkinter import ttk
import pyodbc

def login():
global login_screen
global dsnname_verify
global username_verify
global password_verify
global username_login_entry
global password_login_entry

login_screen = Toplevel(main_screen)
login_screen.title("IBMi Login")
login_screen.geometry("350x250")

Label(login_screen, text="Enter Log-in details").grid(column=1, row=0, columnspan=2)

username_verify = StringVar()
password_verify = StringVar()
dsnname_verify = StringVar()

Label(login_screen, text="DSN Name * ").grid(column=0, row=2)
dsn_name = Entry(login_screen, textvariable=dsnname_verify)
dsn_name.insert(0,"LS089DSN")
dsn_name.grid(column=1, row=2)

Label(login_screen, text="Username * ").grid(column=0, row=3)
username_login_entry = Entry(login_screen, textvariable=username_verify)
username_login_entry.focus_set()
username_login_entry.grid(column=1, row=3)

Label(login_screen, text="Password * ").grid(column=0, row=4)
password_login_entry = Entry(login_screen, textvariable=password_verify, show= '*')
password_login_entry.grid(column=1, row=4)

Label(login_screen, text="").grid(column=0, row=5)
Button(login_screen, text="Login", width=10, height=1, command = login_verify).grid(column=1, row=6, columnspan=2, sticky="ew")

login_screen.bind("<Return>", login_verify)


def login_verify(*args):

global errormsg
global conn

dsnname1 = dsnname_verify.get()
username1 = username_verify.get()
password1 = password_verify.get()
#username_login_entry.delete(0, END)
#password_login_entry.delete(0, END)

if username1 != "" and password1 != '':

try:
conn = pyodbc.connect('DSN=' + dsnname1 + '; USERID='+ username1 + '; PASSWORD=' + password1)

login_screen.destroy()
btn_login["state"] = "disabled"
btn_login["text"] = "Logged-in"
btn_run1["state"] = "normal"
btn_run2["state"] = "normal"
btn_run3["state"] = "normal"
btn_run4["state"] = "normal"
btn_run5["state"] = "normal"
btn_logoff["state"] = "normal"

except pyodbc.InterfaceError as e :
print("Connection failed - pyodbc.InterfaceError")
print(e)
errormsg = e
login_error()
except pyodbc.OperationalError as e :
print("Connection failed - pyodbc.OperationalError")
print(e)
errormsg = e
login_error()
except pyodbc.DatabaseError as e:
print("pyodbc.DatabaseError")
print(e)
errormsg = e
login_error()

def login_error():
global login_error_screen
global errormsg

username_login_entry.delete(0, END)
password_login_entry.delete(0, END)

login_error_screen = Toplevel(login_screen)
login_error_screen.title("Error")
login_error_screen.geometry("850x200")
Label(login_error_screen, text="Login Error").grid()
err_screen = Text(login_error_screen, height=10, width=120)
err_screen.grid()
err_screen.insert(END,errormsg)
Button(login_error_screen, text="OK", command=delete_login_error_screen).grid()

def delete_login_error_screen():
login_error_screen.destroy()

def service1():
#
tree1 = ttk.Treeview(main_screen, columns=('0','1','2','3','4','5','6','7','8'), show='headings')
tree1.column(0, width=110, anchor='w')
tree1.column(1, width=110, anchor='w')
tree1.column(2, width=100, anchor='center')
tree1.column(3, width=150, anchor='center')
tree1.column(4, width=100, anchor='center')
tree1.column(5, width=150, anchor='center')
tree1.column(6, width=150, anchor='center')
tree1.column(7, width=100, anchor='center')
tree1.column(8, width=150, anchor='center')

tree1.heading(0, text="Remote Address")
tree1.heading(1, text="Local Address")
tree1.heading(2, text="Local Port")
tree1.heading(3, text="Local Name")
tree1.heading(4, text="TCP State")
tree1.heading(5, text="Bytes Sent Remotely")
tree1.heading(6, text="Bytes Received Locally")
tree1.heading(7, text="Idle Time")
tree1.heading(8, text="No. Of Associated Jobs")

tree1.grid(row=0, column=1, sticky="N, W,S,E")

s = ttk.Scrollbar(main_screen, orient=VERTICAL, command=tree1.yview)
s.grid(column=8, row=0, sticky=(N,S))
tree1['yscrollcommand'] = s.set

# clear the tree view
x = tree1.get_children()
for item in x:
tree1.delete(item)

c1 = conn.cursor()
work = """
SELECT REMOTE_ADDRESS,
LOCAL_ADDRESS,LOCAL_PORT,LOCAL_PORT_NAME,TCP_STATE,
BYTES_SENT_REMOTELY,BYTES_RECEIVED_LOCALLY,
IDLE_TIME,NUMBER_OF_ASSOCIATED_JOBS
FROM QSYS2.NETSTAT_INFO
WHERE CONNECTION_TYPE='IPV4'
AND REMOTE_ADDRESS NOT IN ('0','0.0.0.0','127.0.0.1')
AND REMOTE_ADDRESS NOT LIKE '10.10.%' AND REMOTE_ADDRESS NOT LIKE '192.168.%' AND NUMBER_OF_ASSOCIATED_JOBS > 0"""

c1.execute(work)

records = c1.fetchall()

for row in records:
ipaddress = str(row[0])
# remove the next line to show the real IP address
ipaddress = "999.999.9.9"
tree1.insert("", "end", values=(ipaddress, row[1], row[2],row[3],row[4],row[5],[6],row[7],row[8]))
c1.close()

def service2():
#
tree2 = ttk.Treeview(main_screen, columns=('0','1','2','3','4','5','6','7'), show='headings')
tree2.column(0, width=110, anchor='w')
tree2.column(1, width=110, anchor='w')
tree2.column(2, width=150, anchor='w')
tree2.column(3, width=150, anchor='w')
tree2.column(4, width=100, anchor='center')
tree2.column(5, width=150, anchor='center')
tree2.column(5, width=100, anchor='w')
tree2.column(5, width=100, anchor='w')

tree2.heading(0, text="Journal Name")
tree2.heading(1, text="Journal Library")
tree2.heading(2, text="Attached RCVR Name")
tree2.heading(3, text="Attached RCVR Library")
tree2.heading(4, text="Journal Type")
tree2.heading(5, text="Journal State")
tree2.heading(6, text="Number of JRN RCV's")
tree2.heading(7, text="Total Size JRN RCV's")


tree2.grid(row=0, column=1, sticky="N, W,S,E")

s = ttk.Scrollbar(main_screen, orient=VERTICAL, command=tree2.yview)
s.grid(column=8, row=0, sticky=(N,S))
tree2['yscrollcommand'] = s.set


# clear the tree view
x = tree2.get_children()
for item in x:
tree2.delete(item)

c1 = conn.cursor()
work = """
select JOURNAL_NAME,JOURNAL_LIBRARY,ATTACHED_JOURNAL_RECEIVER_NAME,ATTACHED_JOURNAL_RECEIVER_LIBRARY,
JOURNAL_TYPE,JOURNAL_STATE,NUMBER_JOURNAL_RECEIVERS,TOTAL_SIZE_JOURNAL_RECEIVERS
from QSYS2.JOURNAL_INFO"""

c1.execute(work)

records = c1.fetchall()

for row in records:
tree2.insert("", "end", values=(row[0], row[1], row[2],row[3],row[4],row[5],row[6],row[7]))
c1.close()

def service3():
#
tree3 = ttk.Treeview(main_screen, columns=('0','1','2','3','4','5','6','7','8'), show='headings')
tree3.column(0, width=110, anchor='center')
tree3.column(1, width=110, anchor='center')
tree3.column(2, width=150, anchor='center')
tree3.column(3, width=150, anchor='center')
tree3.column(4, width=100, anchor='center')
tree3.column(5, width=150, anchor='center')
tree3.column(6, width=150, anchor='center')
tree3.column(7, width=150, anchor='center')


tree3.heading(0, text="ASP Number")
tree3.heading(1, text="Disk Type")
tree3.heading(2, text="Disk Model")
tree3.heading(3, text="Unit Number")
tree3.heading(4, text="Unit Type")
tree3.heading(5, text="Storage Capacity")
tree3.heading(6, text="Space available")
tree3.heading(7, text="Percentage Used")

tree3.grid(row=0, column=1, sticky="N, W,S,E")

s = ttk.Scrollbar(main_screen, orient=VERTICAL, command=tree3.yview)
s.grid(column=8, row=0, sticky=(N,S))
tree3['yscrollcommand'] = s.set


# clear the tree view
x = tree3.get_children()
for item in x:
tree3.delete(item)

c1 = conn.cursor()
work = """
select ASP_NUMBER,DISK_TYPE,DISK_MODEL,UNIT_NUMBER,
UNIT_TYPE,UNIT_STORAGE_CAPACITY,UNIT_SPACE_AVAILABLE,
PERCENT_USED,UNIT_MEDIA_CAPACITY
from QSYS2.SYSDISKSTAT"""

c1.execute(work)

records = c1.fetchall()

for row in records:
tree3.insert("", "end", values=(row[0], row[1], row[2],row[3],row[4],row[5],row[6],row[7]))
c1.close()

def service4():
#
tree4 = ttk.Treeview(main_screen, columns=('0','1','2','3'), show='headings')
tree4.column(0, width=180, anchor='w')
tree4.column(1, width=60, anchor='center')
tree4.column(2, width=160, anchor='center')
tree4.column(3, width=120, anchor='w')

tree4.heading(0, text="Authorization Name")
tree4.heading(1, text="ASP Group")
tree4.heading(2, text="Maximum Storage Allowed")
tree4.heading(3, text="Storage Used")

tree4.grid(row=0, column=1, sticky="N, W,S, E ")

s = ttk.Scrollbar(main_screen, orient=VERTICAL, command=tree4.yview)
s.grid(column=8, row=0, sticky=(N,S))
tree4['yscrollcommand'] = s.set


# clear the tree view
x = tree4.get_children()
for item in x:
tree4.delete(item)

c1 = conn.cursor()
work = """
SELECT
AUTHORIZATION_NAME,ASPGRP,MAXIMUM_STORAGE_ALLOWED,STORAGE_USED FROM
QSYS2.USER_STORAGE WHERE STORAGE_USED > 0 ORDER BY STORAGE_USED DESC
"""

c1.execute(work)

records = c1.fetchall()

for row in records:
tree4.insert("", "end", values=(row[0], row[1], row[2],row[3]))
c1.close()

def service5():
#
tree5 = ttk.Treeview(main_screen, columns=('0','1','2','3','4','5','6','7'), show='headings')
tree5.column(0, width=120, anchor='w')
tree5.column(1, width=100, anchor='center')
tree5.column(2, width=160, anchor='center')
tree5.column(3, width=140, anchor='w')
tree5.column(4, width=120, anchor='center')
tree5.column(5, width=120, anchor='w')
tree5.column(6, width=80, anchor='center')
tree5.column(7, width=120, anchor='w')

tree5.heading(0, text="User ID")
tree5.heading(1, text="PREVIOUS_SIGNON")
tree5.heading(2, text="STATUS")
tree5.heading(3, text="PASSWORD_CHANGE_DATE")
tree5.heading(4, text="NO_PASSWORD_INDICATOR")
tree5.heading(5, text="SPECIAL_AUTHORITIES")
tree5.heading(6, text="LIMIT_CAPABILITIES")
tree5.heading(7, text="LAST_USED_TIMESTAMP")

tree5.grid(row=0, column=1, sticky="N, W,S, E ")

s = ttk.Scrollbar(main_screen, orient=VERTICAL, command=tree5.yview)
s.grid(column=8, row=0, sticky=(N,S))
tree5['yscrollcommand'] = s.set


# clear the tree view
x = tree5.get_children()
for item in x:
tree5.delete(item)

c1 = conn.cursor()
work = """
SELECT AUTHORIZATION_NAME,PREVIOUS_SIGNON,STATUS,PASSWORD_CHANGE_DATE,NO_PASSWORD_INDICATOR,
SPECIAL_AUTHORITIES,LIMIT_CAPABILITIES,LAST_USED_TIMESTAMP

FROM QSYS2.USER_INFO
"""

c1.execute(work)

records = c1.fetchall()

for row in records:
userid = str(row[0])
userid = userid.strip()
tree5.insert("", "end", values=(userid, row[1], row[2],row[3],row[4],row[5],row[6],row[7]))
c1.close()

def logoff():
btn_login["state"] = "active"
btn_login["text"] = "Log-in to IBMi"
btn_run1["state"] = "disabled"
btn_run2["state"] = "disabled"
btn_run3["state"] = "disabled"
btn_run4["state"] = "disabled"
btn_run5["state"] = "disabled"
btn_logoff["state"] = "disabled"
conn.close()


def main_account_screen():
global main_screen
global display_screen
global btn_login
global btn_run1
global btn_run2
global btn_run3
global btn_run4
global btn_run5
global btn_logoff

main_screen = Tk()
main_screen.geometry()
main_screen.title("IBMi Services in Python")
# configure the main panel
main_screen.rowconfigure(0, minsize=800, weight=1 )
main_screen.columnconfigure(1, minsize=1000, weight=1)

side_bar = Frame(main_screen, borderwidth=5, background="lightgrey")
side_bar.grid(column=0, row=0, sticky="N,W,S")

btn_login = Button(side_bar, text="Log-in to IBMi",command=login)
btn_login.grid(row=0, column=0, sticky="ew", padx=5, pady=5)

btn_run1 = Button(side_bar, text="Check Remote Connections",command=service1)
btn_run1.grid(row=1, column=0, sticky="ew", padx=5, pady=5)

btn_run2 = Button(side_bar, text="Journal Info",command=service2)
btn_run2.grid(row=2, column=0, sticky="ew", padx=5, pady=5)

btn_run3 = Button(side_bar, text="Disk Info",command=service3)
btn_run3.grid(row=3, column=0, sticky="ew", padx=5, pady=5)

btn_run4 = Button(side_bar, text="User Storage",command=service4)
btn_run4.grid(row=4, column=0, sticky="ew", padx=5, pady=5)

btn_run5 = Button(side_bar, text="User Info",command=service5)
btn_run5.grid(row=4, column=0, sticky="ew", padx=5, pady=5)

btn_logoff = Button(side_bar, text="Log-off",command=logoff)
btn_logoff.grid(row=9, column=0, sticky="ew", padx=5, pady=5)

display_screen = Frame(main_screen)
display_screen.grid(column=1, row=0, sticky="N,W,S,E")
# start off disabled = correct login activates these buttons
btn_run1["state"] = "disabled"
btn_run2["state"] = "disabled"
btn_run3["state"] = "disabled"
btn_run4["state"] = "disabled"
btn_run5["state"] = "disabled"
btn_logoff["state"] = "disabled"


main_screen.mainloop()

main_account_screen()

 

 

IBM i Security, Tools, development, Change Control and Support 

DISCLAIMER: Please read our terms for the use of this website, if you do not agree to them please do not use our website. If you do use our website you agree to the terms Terms Of Use