This code is imported as a Jupyter notebook extension in any notebooks you create with DB2 code in it. Place the following line of code in any notebook that you want to use these commands with:
%run db2.ipynb
This code defines a Jupyter/Python magic command called %sql which allows you to execute DB2 specific calls to the database. There are other packages available for manipulating databases, but this one has been specifically designed for demonstrating a number of the SQL features available in DB2.
There are two ways of executing the %sql command. A single line SQL statement would use the line format of the magic command:
%sql SELECT * FROM EMPLOYEEIf you have a large block of sql then you would place the %%sql command at the beginning of the block and then place the SQL statements into the remainder of the block. Using this form of the %%sql statement means that the notebook cell can only contain SQL and no other statements.
%%sql SELECT * FROM EMPLOYEE ORDER BY LASTNAMEYou can have multiple lines in the SQL block (%%sql). The default SQL delimiter is the semi-column (;). If you have scripts (triggers, procedures, functions) that use the semi-colon as part of the script, you will need to use the -d option to change the delimiter to an at "@" sign.
%%sql -d SELECT * FROM EMPLOYEE @ CREATE PROCEDURE ... @
The %sql command allows most DB2 commands to execute and has a special version of the CONNECT statement. A CONNECT by itself will attempt to reconnect to the database using previously used settings. If it cannot connect, it will prompt the user for additional information.
The CONNECT command has the following format:
%sql CONNECT TO <database> USER <userid> USING <password | ?> HOST <ip address> PORT <port number>If you use a "?" for the password field, the system will prompt you for a password. This avoids typing the password as clear text on the screen. If a connection is not successful, the system will print the error message associated with the connect request.
If the connection is successful, the parameters are saved on your system and will be used the next time you run a SQL statement, or when you issue the %sql CONNECT command with no parameters.
In addition to the -d option, there are a number different options that you can specify at the beginning of the SQL:
One final note. You can pass python variables to the %sql command by using the {} braces with the name of the variable inbetween. Note that you will need to place proper punctuation around the variable in the event the SQL command requires it. For instance, the following example will find employee '000010' in the EMPLOYEE table.
empno = '000010'
%sql SELECT LASTNAME FROM EMPLOYEE WHERE EMPNO='{empno}'
In [2]:
#
# Set up Jupyter MAGIC commands "sql".
# %sql will return results from a DB2 select statement or execute a DB2 command
#
# IBM 2017: George Baklarz
#
import ibm_db
import pandas
import ibm_db_dbi
import json
import matplotlib.pyplot as plt
import getpass
import os
import pickle
import time
import sys
import re
from IPython.display import display, HTML, Image
from __future__ import print_function
from IPython.core.magic import (Magics, magics_class, line_magic,
cell_magic, line_cell_magic)
# Python Hack for Input between 2 and 3
try:
input = raw_input
except NameError:
pass
plt.style.use('ggplot')
db2_connect = False
db2_max = 10
db2_database = ''
db2_hostname = 'localhost'
db2_port = '50000'
db2_protocol = 'TCPIP'
db2_uid = 'DB2INST1'
db2_pwd = 'password'
db2_error_highlight = True
db2_runtime = 1
hdbc = None
hstmt = None
try:
with open('db2.pickle','rb') as f:
db2_max, db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd = pickle.load(f)
except:
try:
with open('db2.pickle','wb') as f:
pickle.dump([db2_max, db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd],f)
except:
print("Failed trying to read or write DB2 Configuration Information.")
# Load Global variables that are used across notebooks. Similar to %save but gives us more flexibility
# Returns True if everything works, False otherwise
def readGlobals():
global db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd, dh2_max, db2_connect
try:
with open('db2.pickle','rb') as f:
db2_max, db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd = pickle.load(f)
return(True)
except:
try:
# Default values for the connection
db2_max = 10
db2_database = ''
db2_hostname = 'localhost'
db2_port = '50000'
db2_protocol = 'TCPIP'
db2_uid = 'DB2INST1'
db2_pwd = 'password'
with open('db2.pickle','wb') as f:
pickle.dump([db2_max, db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd],f)
return(True)
except:
print("Failed trying to write DB2 Configuration Information.")
return(False)
# Write Global variables for use across notebooks
# Returns True if everything works, False otherwise
def writeGlobals():
global db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd, dh2_max
# Save the values for future use
try:
with open('db2.pickle','wb') as f:
pickle.dump([db2_max, db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd],f)
return(True)
except:
print("Failed trying to write DB2 Configuration Information.")
return(False)
# DB2 Connection information Help
def db2_create_sample():
create_department = """
BEGIN
DECLARE FOUND INTEGER;
SET FOUND = (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE NAME='DEPARTMENT' AND CREATOR=CURRENT USER);
IF FOUND = 0 THEN
EXECUTE IMMEDIATE('CREATE TABLE DEPARTMENT(DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),ADMRDEPT CHAR(3) NOT NULL)');
EXECUTE IMMEDIATE('INSERT INTO DEPARTMENT VALUES
(''A00'',''SPIFFY COMPUTER SERVICE DIV.'',''000010'',''A00''),
(''B01'',''PLANNING'',''000020'',''A00''),
(''C01'',''INFORMATION CENTER'',''000030'',''A00''),
(''D01'',''DEVELOPMENT CENTER'',NULL,''A00''),
(''D11'',''MANUFACTURING SYSTEMS'',''000060'',''D01''),
(''D21'',''ADMINISTRATION SYSTEMS'',''000070'',''D01''),
(''E01'',''SUPPORT SERVICES'',''000050'',''A00''),
(''E11'',''OPERATIONS'',''000090'',''E01''),
(''E21'',''SOFTWARE SUPPORT'',''000100'',''E01''),
(''F22'',''BRANCH OFFICE F2'',NULL,''E01''),
(''G22'',''BRANCH OFFICE G2'',NULL,''E01''),
(''H22'',''BRANCH OFFICE H2'',NULL,''E01''),
(''I22'',''BRANCH OFFICE I2'',NULL,''E01''),
(''J22'',''BRANCH OFFICE J2'',NULL,''E01'')');
END IF;
END"""
%sql -d -q {create_department}
create_employee = """
BEGIN
DECLARE FOUND INTEGER;
SET FOUND = (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE NAME='EMPLOYEE' AND CREATOR=CURRENT USER);
IF FOUND = 0 THEN
EXECUTE IMMEDIATE('CREATE TABLE EMPLOYEE(
EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1),
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
HIREDATE DATE,
JOB CHAR(8),
EDLEVEL SMALLINT NOT NULL,
SEX CHAR(1),
BIRTHDATE DATE,
SALARY DECIMAL(9,2),
BONUS DECIMAL(9,2),
COMM DECIMAL(9,2)
)');
EXECUTE IMMEDIATE('INSERT INTO EMPLOYEE VALUES
(''000010'',''CHRISTINE'',''I'',''HAAS'' ,''A00'',''3978'',''1995-01-01'',''PRES '',18,''F'',''1963-08-24'',152750.00,1000.00,4220.00),
(''000020'',''MICHAEL'' ,''L'',''THOMPSON'' ,''B01'',''3476'',''2003-10-10'',''MANAGER '',18,''M'',''1978-02-02'',94250.00,800.00,3300.00),
(''000030'',''SALLY'' ,''A'',''KWAN'' ,''C01'',''4738'',''2005-04-05'',''MANAGER '',20,''F'',''1971-05-11'',98250.00,800.00,3060.00),
(''000050'',''JOHN'' ,''B'',''GEYER'' ,''E01'',''6789'',''1979-08-17'',''MANAGER '',16,''M'',''1955-09-15'',80175.00,800.00,3214.00),
(''000060'',''IRVING'' ,''F'',''STERN'' ,''D11'',''6423'',''2003-09-14'',''MANAGER '',16,''M'',''1975-07-07'',72250.00,500.00,2580.00),
(''000070'',''EVA'' ,''D'',''PULASKI'' ,''D21'',''7831'',''2005-09-30'',''MANAGER '',16,''F'',''2003-05-26'',96170.00,700.00,2893.00),
(''000090'',''EILEEN'' ,''W'',''HENDERSON'' ,''E11'',''5498'',''2000-08-15'',''MANAGER '',16,''F'',''1971-05-15'',89750.00,600.00,2380.00),
(''000100'',''THEODORE'' ,''Q'',''SPENSER'' ,''E21'',''0972'',''2000-06-19'',''MANAGER '',14,''M'',''1980-12-18'',86150.00,500.00,2092.00),
(''000110'',''VINCENZO'' ,''G'',''LUCCHESSI'' ,''A00'',''3490'',''1988-05-16'',''SALESREP'',19,''M'',''1959-11-05'',66500.00,900.00,3720.00),
(''000120'',''SEAN'' ,'' '',''O`CONNELL'' ,''A00'',''2167'',''1993-12-05'',''CLERK '',14,''M'',''1972-10-18'',49250.00,600.00,2340.00),
(''000130'',''DELORES'' ,''M'',''QUINTANA'' ,''C01'',''4578'',''2001-07-28'',''ANALYST '',16,''F'',''1955-09-15'',73800.00,500.00,1904.00),
(''000140'',''HEATHER'' ,''A'',''NICHOLLS'' ,''C01'',''1793'',''2006-12-15'',''ANALYST '',18,''F'',''1976-01-19'',68420.00,600.00,2274.00),
(''000150'',''BRUCE'' ,'' '',''ADAMSON'' ,''D11'',''4510'',''2002-02-12'',''DESIGNER'',16,''M'',''1977-05-17'',55280.00,500.00,2022.00),
(''000160'',''ELIZABETH'',''R'',''PIANKA'' ,''D11'',''3782'',''2006-10-11'',''DESIGNER'',17,''F'',''1980-04-12'',62250.00,400.00,1780.00),
(''000170'',''MASATOSHI'',''J'',''YOSHIMURA'' ,''D11'',''2890'',''1999-09-15'',''DESIGNER'',16,''M'',''1981-01-05'',44680.00,500.00,1974.00),
(''000180'',''MARILYN'' ,''S'',''SCOUTTEN'' ,''D11'',''1682'',''2003-07-07'',''DESIGNER'',17,''F'',''1979-02-21'',51340.00,500.00,1707.00),
(''000190'',''JAMES'' ,''H'',''WALKER'' ,''D11'',''2986'',''2004-07-26'',''DESIGNER'',16,''M'',''1982-06-25'',50450.00,400.00,1636.00),
(''000200'',''DAVID'' ,'' '',''BROWN'' ,''D11'',''4501'',''2002-03-03'',''DESIGNER'',16,''M'',''1971-05-29'',57740.00,600.00,2217.00),
(''000210'',''WILLIAM'' ,''T'',''JONES'' ,''D11'',''0942'',''1998-04-11'',''DESIGNER'',17,''M'',''2003-02-23'',68270.00,400.00,1462.00),
(''000220'',''JENNIFER'' ,''K'',''LUTZ'' ,''D11'',''0672'',''1998-08-29'',''DESIGNER'',18,''F'',''1978-03-19'',49840.00,600.00,2387.00),
(''000230'',''JAMES'' ,''J'',''JEFFERSON'' ,''D21'',''2094'',''1996-11-21'',''CLERK '',14,''M'',''1980-05-30'',42180.00,400.00,1774.00),
(''000240'',''SALVATORE'',''M'',''MARINO'' ,''D21'',''3780'',''2004-12-05'',''CLERK '',17,''M'',''2002-03-31'',48760.00,600.00,2301.00),
(''000250'',''DANIEL'' ,''S'',''SMITH'' ,''D21'',''0961'',''1999-10-30'',''CLERK '',15,''M'',''1969-11-12'',49180.00,400.00,1534.00),
(''000260'',''SYBIL'' ,''P'',''JOHNSON'' ,''D21'',''8953'',''2005-09-11'',''CLERK '',16,''F'',''1976-10-05'',47250.00,300.00,1380.00),
(''000270'',''MARIA'' ,''L'',''PEREZ'' ,''D21'',''9001'',''2006-09-30'',''CLERK '',15,''F'',''2003-05-26'',37380.00,500.00,2190.00),
(''000280'',''ETHEL'' ,''R'',''SCHNEIDER'' ,''E11'',''8997'',''1997-03-24'',''OPERATOR'',17,''F'',''1976-03-28'',36250.00,500.00,2100.00),
(''000290'',''JOHN'' ,''R'',''PARKER'' ,''E11'',''4502'',''2006-05-30'',''OPERATOR'',12,''M'',''1985-07-09'',35340.00,300.00,1227.00),
(''000300'',''PHILIP'' ,''X'',''SMITH'' ,''E11'',''2095'',''2002-06-19'',''OPERATOR'',14,''M'',''1976-10-27'',37750.00,400.00,1420.00),
(''000310'',''MAUDE'' ,''F'',''SETRIGHT'' ,''E11'',''3332'',''1994-09-12'',''OPERATOR'',12,''F'',''1961-04-21'',35900.00,300.00,1272.00),
(''000320'',''RAMLAL'' ,''V'',''MEHTA'' ,''E21'',''9990'',''1995-07-07'',''FIELDREP'',16,''M'',''1962-08-11'',39950.00,400.00,1596.00),
(''000330'',''WING'' ,'' '',''LEE'' ,''E21'',''2103'',''2006-02-23'',''FIELDREP'',14,''M'',''1971-07-18'',45370.00,500.00,2030.00),
(''000340'',''JASON'' ,''R'',''GOUNOT'' ,''E21'',''5698'',''1977-05-05'',''FIELDREP'',16,''M'',''1956-05-17'',43840.00,500.00,1907.00),
(''200010'',''DIAN'' ,''J'',''HEMMINGER'' ,''A00'',''3978'',''1995-01-01'',''SALESREP'',18,''F'',''1973-08-14'',46500.00,1000.00,4220.00),
(''200120'',''GREG'' ,'' '',''ORLANDO'' ,''A00'',''2167'',''2002-05-05'',''CLERK '',14,''M'',''1972-10-18'',39250.00,600.00,2340.00),
(''200140'',''KIM'' ,''N'',''NATZ'' ,''C01'',''1793'',''2006-12-15'',''ANALYST '',18,''F'',''1976-01-19'',68420.00,600.00,2274.00),
(''200170'',''KIYOSHI'' ,'' '',''YAMAMOTO'' ,''D11'',''2890'',''2005-09-15'',''DESIGNER'',16,''M'',''1981-01-05'',64680.00,500.00,1974.00),
(''200220'',''REBA'' ,''K'',''JOHN'' ,''D11'',''0672'',''2005-08-29'',''DESIGNER'',18,''F'',''1978-03-19'',69840.00,600.00,2387.00),
(''200240'',''ROBERT'' ,''M'',''MONTEVERDE'',''D21'',''3780'',''2004-12-05'',''CLERK '',17,''M'',''1984-03-31'',37760.00,600.00,2301.00),
(''200280'',''EILEEN'' ,''R'',''SCHWARTZ'' ,''E11'',''8997'',''1997-03-24'',''OPERATOR'',17,''F'',''1966-03-28'',46250.00,500.00,2100.00),
(''200310'',''MICHELLE'' ,''F'',''SPRINGER'' ,''E11'',''3332'',''1994-09-12'',''OPERATOR'',12,''F'',''1961-04-21'',35900.00,300.00,1272.00),
(''200330'',''HELENA'' ,'' '',''WONG'' ,''E21'',''2103'',''2006-02-23'',''FIELDREP'',14,''F'',''1971-07-18'',35370.00,500.00,2030.00),
(''200340'',''ROY'' ,''R'',''ALONZO'' ,''E21'',''5698'',''1997-07-05'',''FIELDREP'',16,''M'',''1956-05-17'',31840.00,500.00,1907.00)');
END IF;
END"""
%sql -d -q {create_employee}
print("Sample tables [EMPLOYEE, DEPARTMENT] created.")
def db2_connect_help():
helpConnect = """
<h3>Connecting to DB2</h3>
<p>You need to fill in some connection information so that this notebook can communicate with DB2.
To use the default values, just hit return for each input line. The default values are:
<ul>
<li>DB2 Driver - IBM DB2 ODBC Driver (this requires a manual change)
<li>Database - SAMPLE
<li>Hostname - localhost (enter an IP address if you need to connect to a remote server)
<li>PORT - 50000 (this is the default but it could be different)
<li>PROTOCOL - TCPIP (You could have a local connection in Windows)
<li>Userid - DB2INST1
<li>Password - No password is provided so you have to enter a value
<li>Maximum Rows - 10 lines of output are displayed when a result set is returned
</ul>
"""
display(HTML(helpConnect))
# Prompt for DB2 Connection Settings
def db2_connect_prompt():
global db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd, dh2_max
db2_database = input("Enter the database name [SAMPLE]: ") or "SAMPLE";
db2_hostname = input("Enter the host name [localhost]: ") or "localhost";
db2_port = input("Enter the port number [50000]: ") or "50000";
db2_uid = input("Enter Userid on the DB2 system [DB2INST1]: ").upper() or "DB2INST1";
db2_pwd = getpass.getpass("Password [password]: ") or "password";
db2_max = input("Maximum rows displayed [10]: ") or "10";
db2_max = int(db2_max)
# Connect to DB2 and prompt if you haven't set any of the values yet
def db2_doConnect():
global hdbc, hstmt
global db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd, dh2_max, db2_connect
if db2_connect == False:
if len(db2_database) == 0:
db2_connect_help()
db2_connect_prompt()
dsn = (
"DRIVER={{IBM DB2 ODBC DRIVER}};"
"DATABASE={0};"
"HOSTNAME={1};"
"PORT={2};"
"PROTOCOL=TCPIP;"
"UID={3};"
"PWD={4};").format(db2_database, db2_hostname, db2_port, db2_uid, db2_pwd)
# Get a database handle (hdbc) and a statement handle (hstmt) for subsequent access to DB2
try:
hdbc = ibm_db.connect(dsn, "", "")
except Exception as err:
db2_error(False)
db2_connect = False
db2_database = ''
return
try:
hstmt = ibm_db_dbi.Connection(hdbc)
except Exception as err:
db2_error(False)
db2_connect = False
db2_database = ''
return
db2_connect = True
# db2_create_sample()
# Save the values for future use
if (writeGlobals() == False): return
print("Connection successful.")
# Parse the CONNECT statement and execute if possible
def parseConnect(inSQL):
global db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd, dh2_max, db2_connect
db2_connect = False
cParms = inSQL.split()
cnt = 0
while cnt < len(cParms):
if cParms[cnt].upper() == 'TO':
if cnt+1 < len(cParms):
db2_database = cParms[cnt+1].upper()
cnt = cnt + 1
else:
print("No database specified in the CONNECT statement")
return
elif cParms[cnt].upper() == 'USER':
if cnt+1 < len(cParms):
db2_uid = cParms[cnt+1].upper()
cnt = cnt + 1
else:
print("No userid specified in the CONNECT statement")
return
elif cParms[cnt].upper() == 'USING':
if cnt+1 < len(cParms):
db2_pwd = cParms[cnt+1]
if (db2_pwd == '?'):
db2_pwd = getpass.getpass("Password [password]: ") or "password"
cnt = cnt + 1
else:
print("No password specified in the CONNECT statement")
return
elif cParms[cnt].upper() == 'HOST':
if cnt+1 < len(cParms):
db2_hostname = cParms[cnt+1].upper()
cnt = cnt + 1
else:
print("No hostname specified in the CONNECT statement")
return
elif cParms[cnt].upper() == 'PORT':
if cnt+1 < len(cParms):
db2_port = cParms[cnt+1].upper()
cnt = cnt + 1
else:
print("No port specified in the CONNECT statement")
return
elif cParms[cnt].upper() == 'RESET':
db2_database = ''
print("Connection reset.")
return
else:
cnt = cnt + 1
db2_doConnect()
# Find a keyword in a SQL string
def findKeyword(keywords,keyword):
if len(keywords) == 0: return False
if len(keyword) == 0: return False
uKeywords = keywords.upper()
uKeyword = keyword.upper()
if uKeywords.find(uKeyword.strip()) >= 0:
return True
else:
return False
# Run a command for one second to see how many times we execute it and return the count
def sqlTimer(flag_cmd, inSQL):
global hdbc, hstmt, db2_runtime
db2Block = 2
count = 0
t_end = time.time() + db2_runtime
while time.time() < t_end:
if (flag_cmd == db2Block):
try:
stmt = ibm_db.exec_immediate(hdbc,inSQL)
except Exception as err:
db2_error(False)
return(-1)
else:
try:
stmt = ibm_db.exec_immediate(hdbc,inSQL)
while( ibm_db.fetch_row(stmt) ): pass
except Exception as err:
db2_error(False)
return(-1)
count = count + 1
return(count)
# Print out the DB2 error generated by the last executed statement
def db2_error(quiet):
if quiet == True: return
html = '<p style="border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;">'
errmsg = ibm_db.stmt_errormsg().replace('\r',' ')
errmsg = errmsg[errmsg.rfind("]")+1:].strip()
if db2_error_highlight == True:
display(HTML(html+errmsg+"</p>"))
else:
print(errmsg)
@magics_class
class DB2(Magics):
@line_cell_magic
def sql(self, line, cell=None):
# Before we event get started, check to see if you have connected yet. Without a connection we
# can't do anything. You may have a connection request in the code, so if that is true, we run those,
# otherwise we connect immediately
# If your statement is not a connect, and you haven't connected, we need to do it for you
global db2_database, db2_hostname, db2_port, db2_protocol, db2_uid, db2_pwd, dh2_max, db2_connect
global hdbc, hstmt
select = ["SELECT", "WITH", "VALUES"]
noBlock = 0
sqlBlock = 1
db2Block = 2
# If you use %sql (line) we just run the SQL. If you use %%SQL the entire cell is run.
flag_delim = ";"
flag_results = True
flag_sqlType = noBlock
flag_quiet = False
flag_json = False
flag_timer = False
flag_plot = 0
flag_cell = False
flag_output = False
flag_resultset = False
# The parameters must be in the line, not in the cell i.e. %sql -c
Parms = line.strip()
if len(Parms) == 0:
if cell == None: return
if len(cell.strip()) == 0: return
# If you issue a CONNECT statement in %sql then we run this first before auto-connecting
if findKeyword(Parms,"CONNECT") == True:
parseConnect(Parms)
return
# We need to check to see if we are connected before running any SQL
if db2_connect == False:
db2_doConnect()
if db2_connect == False: return
# Default result set size
if db2_max == -1:
pandas.reset_option('max_rows')
else:
pandas.options.display.max_rows = db2_max
# Display rows as JSON structure
if Parms.find("-j") >= 0:
flag_json = True
Parms = Parms.replace("-j"," ")
# Load sample tables for scripts
if Parms.find('-sampledata') >= 0:
db2_create_sample()
return
# Execute the SQL so that it behaves like a SELECT statement
if Parms.find("-s") >= 0:
flag_sqlType = sqlBlock
Parms = Parms.replace("-s"," ")
# Execute the SQL but return the results in a data frame (basically a two-dimensional array)
if Parms.find("-r") >= 0:
flag_resultset = True
Parms = Parms.replace("-r", " ")
# Execute the SQL so that it behaves like an INSERT, DELETE, UPDATE or no result set
if Parms.find("-n") >= 0:
flag_sqlType = db2Block
Parms = Parms.replace("-n"," ")
# Quiet execution (no errors or completed messages)
if Parms.find("-q") >= 0:
flag_quiet = True
Parms = Parms.replace("-q"," ")
# Retrieve all rows (do not use the default limit)
if Parms.find("-a") >= 0:
pandas.reset_option('max_rows')
Parms = Parms.replace("-a"," ")
# Set the delimiter to @ instead of a semi-colon for procedures, triggers, and functions
if Parms.find("-d") >= 0:
flag_delim = "@"
Parms = Parms.replace("-d"," ")
# Timer function (not that useful, but worth a try)
if Parms.find("-t") >= 0:
flag_timer = True
Parms = Parms.replace("-t"," ")
# Plot functions -pb = bar, -pp = pie, -pl = line
if Parms.find("-pb") >= 0:
flag_plot = 1
Parms = Parms.replace("-pb"," ")
if Parms.find("-pp") >= 0:
flag_plot = 2
Parms = Parms.replace("-pp"," ")
if Parms.find("-pl") >= 0:
flag_plot = 3
Parms = Parms.replace("-pl"," ")
remainder = Parms.strip()
# Split the line according to your delimiter
if cell is None:
sqlLines = [remainder]
flag_cell = False
else:
cell = re.sub('.*?--.*$',"",cell,flags=re.M)
remainder = cell.replace("\n"," ")
sqlLines = remainder.split(flag_delim)
flag_cell = True
# For each line figure out if you run it as a command (db2) or select (sql)
for sql in sqlLines:
# Split the line so we know what the first keyword is. We only look at the first one. There may
# be SQL that returns output that we may not know about
keywords = sql.split()
if len(keywords) == 0: continue
sqlcmd = keywords[0].upper()
if (flag_timer == True):
count = sqlTimer(flag_sqlType, sql)
if flag_quiet == False and count != -1:
print("Total iterations in %s second(s): %s" % (db2_runtime,count))
return(count)
elif (flag_plot != 0):
try:
df = pandas.read_sql(sql,hstmt)
except Exception as err:
db2_error(False)
return
plt.figure()
col_count = len(df.columns)
if flag_plot == 1:
# Bar Chart
if (col_count >= 2):
xlabel = df.columns.values[0]
ylabel = df.columns.values[1]
df.plot(kind='bar',x=xlabel,y=ylabel)
else:
df.plot(kind='bar')
elif flag_plot == 2:
# Pie
if (col_count >= 2):
xlabel = df.columns.values[0]
xname = df[xlabel].tolist()
yname = df.columns.values[1]
df.plot(kind='pie',y=yname,labels=xname)
else:
yname = df.columns.values[0]
df.plot(kind='pie',y=yname)
elif flag_plot == 3:
# Line Chart
if (col_count >= 2):
xlabel = df.columns.values[0]
ylabel = df.columns.values[1]
df.plot(kind='line',x=xlabel,y=ylabel)
else:
df.plot(kind='line')
else:
return
plt.show()
return
elif (flag_sqlType == sqlBlock) or (sqlcmd in select and flag_sqlType != db2Block):
if flag_json == True:
try:
stmt = ibm_db.exec_immediate(hdbc,sql);
row_count = 0
while( ibm_db.fetch_row(stmt) ):
row_count = row_count + 1
jsonVal = ibm_db.result(stmt,0)
formatted_JSON = json.dumps(json.loads(jsonVal), indent=4, separators=(',', ': '))
# Print JSON Structure
if row_count > 1: print()
print("Row: %d" % row_count)
print(formatted_JSON)
flag_output = True
except Exception as err:
db2_error(flag_quiet)
else:
if flag_resultset == True:
row_count = 0
resultSet = []
try:
stmt = ibm_db.exec_immediate(hdbc,sql)
result = ibm_db.fetch_tuple(stmt)
while (result):
row = []
for col in result:
row.append(col)
resultSet.append(row)
result = ibm_db.fetch_tuple(stmt)
return(resultSet)
except Exception as err:
db2_error(False)
else:
try:
dp = pandas.read_sql(sql, hstmt)
display(dp)
flag_output = True
except Exception as err:
db2_error(flag_quiet)
else:
try:
ibm_db.exec_immediate(hdbc,sql);
if flag_cell == False and flag_quiet == False:
print("Command completed.")
except Exception as err:
db2_error(flag_quiet)
if flag_cell == True and flag_output == False:
print("Command completed.")
# Register the Magic extension in Jupyter
ip = get_ipython()
ip.register_magics(DB2)
print ("DB2 Extensions Loaded.")
Set the table formatting to left align a table in a cell. By default, tables are centered in a cell. Remove this cell if you don't want to change Jupyter notebook formatting for tables.
In [ ]:
%%html
<style>
table {margin-left: 0 !important;}
</style>