Introduction

In my previous article, I wrote about how to access a SQLite database and output the SQL resultset in a neat table. If you need a recap, my previous article can be found at this link.

In this article I will show you how to access a DB2 for i table, and like my previous article send the data to a table.

As we will be accessing a DB2 table, I will be using ODBC for SQL access.

So, my setup is as follows.

My python program will be developed and run locally on my PC, then using ODBC it will connect to my Power server and access the DB2 for i table.

If you need any information on how to setup your Power server to allow ODBC access, I have produced a YouTube video on all the required steps. It can be found at this link.

Additionally, a complete overview of the ODBC setup can be found on IBMs website. The page can be found at this link.

Graphical Flow

The graphic below shows all the stages our python program will go through.

Personally, I always prefer to draw out the stages, a flow diagram.

As I was always taught ‘The quicker you start coding, the more problems you will have’

Still great advice for today’s programmers, or should I call them developers these days!

Python

Let us get on with the coding.

Firstly, let me import all the external modules we will need for this example.

We need pyodbc to perform all our database connectivity, then we use the PrettyTable module to output our database results to.

Finally we need the os module as we will be using environment variables to store our sensitive data in.

And onto the next part.

The next part of the coding deals with the database side of things.

So, that has got the records we require we now need to display them in a fancy table.

And that is it.

A quick test and see what it outputs.

All looking good!

For completeness, the whole script is shown at the end of this article.

Troubleshooting

Here are some common troubleshooting steps for ODBC connection issues:

Check ODBC Driver Installation:

Ensure that the correct ODBC driver is installed for your database. Sometimes, updating to the latest driver version can resolve issues.

Verify Connection String:

Double-check the connection string for any typos or incorrect parameters. Ensure that all required fields (server, database, user ID, password) are correctly specified.

Test the Connection:

Use the ODBC Data Source Administrator tool to test the connection. This tool can help identify if the issue is with the connection string or the driver configuration.

Check Network Connectivity:

Ensure that the client machine can reach the server over the network. You can use tools like ping or telnet to verify connectivity.

Firewall and Security Settings:

Ensure that firewalls or security settings are not blocking the ODBC connection. Sometimes, adjusting firewall rules or security settings can resolve connectivity issues.

Review Error Messages:

Carefully review any error messages returned by the ODBC driver. These messages often provide clues about what might be wrong (e.g., authentication issues, network problems, etc.).

Check for Configuration Issues:

Verify that the ODBC data source is correctly configured. This includes checking the DSN (Data Source Name) settings and ensuring that the correct driver is selected.

Update System and Drivers:

Ensure that your operating system and ODBC drivers are up to date. Sometimes, system updates can resolve underlying issues.

SSL/TLS Configuration:

If using encrypted connections, ensure that the SSL/TLS settings are correctly configured. This includes verifying certificates and ensuring that the server name matches the certificate.

Consult Documentation and Support:

Refer to the IBM documentation for specific ODBC driver and database issues. If these issues persist, consider reaching out to the support team for your database or ODBC driver.

Complete Code Example

'''

Use ODBC to connect to the DB2 for i database running on our Power server.

© Copyright 2024 FormaServe Systems Ltd. (https://www.formaServe.co.uk)

See our disclaimer at https://powerwire.eu/disclaimer or https://www.formaServe.co.uk/examples.php 

'''
import pyodbc
from prettytable import PrettyTable
import os

try:
    '''
    Try block to help capture any database errors using ODBC.

    The prettytable module will be used to output the relevant SQL resultsets in a neat format

    '''

    # Retrieve the username and password from environment variables
    username = os.getenv('DB_USER')
    password = os.getenv('DB_PASSWORD')

    # server details
    server = 'Galatea'
    database = 'Galatea'

    # Hardcoded library and table names
    library = 'QIWS'
    table = 'QCUSTCDT'

    # SQL Where values
    customer_balance_threshold = 500.00
    customer_city = 'London'

    # Connection string
    connection_string = f'DRIVER={{IBM i Access ODBC Driver}};SYSTEM={server};DATABASE={database};UID={username};PWD={password}'

    # Establishing the connection creating a connection object
    conn = pyodbc.connect(connection_string)
    print('DB connection established')

    # Creating a cursor object using the connection
    cursor = conn.cursor()

    # build sql string
    sql = f"SELECT * FROM {library}.{table} WHERE BALDUE < ? AND CITY = ?"

    # execute the sql string passing in the parameter markers
    cursor.execute(sql, (customer_balance_threshold, customer_city))

    # Fetching the results
    rows = cursor.fetchall()

    # Creating a PrettyTable object
    table = PrettyTable()

    # Adding column names
    table.field_names = [column[0] for column in cursor.description]

    # Adding rows to the table
    for row in rows:
        table.add_row(row)

    # Printing the table
    print(table)

except Exception as e:
    print("Exception: " + str(e))

except pyodbc.Error as e:
    print(f"Error: {e}")

finally:
    # Closing the connection
    conn.close()