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 WRKDSKSTS 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 so this example you can see on your own machine as it displays quite a lot of sensitive information regarding our system.

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.

I've built this on MAC so you will need to use pyodbc to connect - to view where your ODBC.ini file is on your mac run the command 'odbcinst -j' and modify the ODBC.ini file identified there.

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 https://www.kdpsoftware.co.uk/contact-us

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 + '; UID='+ username1 + '; PWD=' + 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()