QuickStart Guide to Db2 Development with Python

by Roger E. Sanders

Overview

This book brings Python, SQL, and Db2 application development together as never before, to show how these three technologies can successfully be used with each other. By reading this book, you will receive:

• An introduction to Db2
• An overview of SQL and how it is used
• An introduction to Python and the Python libraries/drivers available for Db2 application development
• A step-by-step guide for setting up a Python-Db2 development environment (on RedHat or Ubuntu Linux)
• In-depth information on how to structure and build Python applications that interact with Db2 (along with the link to a GitHub site that contains over 70 sample programs and 60 Jupyter Notebooks)

Whether you're a Python developer who wants to build applications that work with Db2, or you're a Db2 user who wants to know how to build Python applications that interact with Db2 servers and databases, you'll find this book a must-read.

Product Details

ISBN-13:	9781583478899
Publisher:	MC Press, LLC
Publication date:	06/14/2019
Sold by:	Barnes & Noble
Format:	eBook
Pages:	64
File size:	825 KB

About the Author

ROGER E. SANDERS is a Field Enablement Professional focused on Machine Learning on IBM Z Systems at IBM. He has worked with Db2 since it was first introduced and is the author of 25 books on relational database technology (six focused on application development), and many articles and tutorials on database topics. He lives in Fuquay Varina, North Carolina.

Read an Excerpt

CHAPTER 1

What Is IBM Db2?

In 1969, while working at IBM's San Jose Research Laboratory in San Jose, California, Edgar Frank "Ted" Codd introduced a new concept for managing information in a paper titled "A Relational Model of Data for Large Shared Data Banks." And, over the next four years, a group of researchers at IBM worked to create a data management system prototype that was based on the relational model described in Codd's paper. (This prototype was named System R — short for System Relational.) Recognizing that a new language was needed to interact with System R, Codd published a second paper titled "A Data Base Sublanguage Founded on Relational Calculus" in 1971; this paper became the foundation for a new language called DSL/Alpha, which was quickly renamed SEQUEL (for Structured English QUEry Language) and later shortened to SQL — an acronym for Structured Query Language.

As part of an effort to port the System R prototype to its System/370 mainframe, IBM began work on a new product called DATABASE 2 (or DB2) in 1980. And, on June 7, 1983, DB2 was made available to a limited number of customers. Two years later it became generally available to customers running the Multiple Virtual Storage (MVS) operating system on System/370 servers.

Thus, IBM Db2 is an RDBMS that is based on Codd's relational model; it is a data management system that consists of a set of formally described data objects that are used to store and link data values by establishing some type of relationship between them. Typically, data is organized into tables, L which are user-defined objects that present data as a collection of unordered rows L with a fixed number of columns. L (Each column contains values of the same data type, and each row contains a set of values for one or more columns; the representation of a row is called a record, L the representation of a column is called a field, L and the intersection of a row and column is referred to as a value L or cell. L) This results in a very efficient way to store data, as values only have to be stored once: data in tables can be accessed and assembled in a variety of ways to facilitate any number of operations.

Since its introduction in 1983, new features and functionality have been added in every new release (and many times, with fix packs as well). As a result, Db2 has become an enterprise-level, high-performant RDBMS that delivers both the four basic functions of persistent storage (create, retrieve, update, and delete, or CRUD) and the four properties (atomicity, consistency, isolation and durability, or ACID) that guarantee data validity, even when unexpected incidents occur.

From the Mainframe to Distributed Platforms and the IBM Cloud

In 1987, DB2 arrived on the personal computer (PC) in the form of an offering called Database Manager, which was one of two special add-on products that came with the "Extended Edition" version of OS/2 (a new operating system for PCs that IBM released that same year). A year later, a version for IBM's new AS/400 server emerged in the form of SQL/400. And, by 1992, DB2 had become a standalone product for OS/2 (that was renamed DB2/2). In 1993, DB2 became available to customers running AIX on IBM RS/6000 series servers. Initially, this port was known as DB2/6000, but DB2/2 and DB2/6000 were quickly merged to create a single product that was christened DB2 for Common Servers. This flavor of DB2 arrived on HP-UX and Solaris servers in 1994, on Windows servers in 1995, and on Linux servers in 1999. Along the way the name changed yet again (to DB2 Universal Database or simply DB2 UDB). And, when Version 9 was made generally available in mid-2006, the "Universal Database" moniker was replaced with the names of the predominant operating systems the DB2 software ran on, leaving essentially just two flavors of DB2: DB2 for z/OS and DB2 for Linux, UNIX, and Windows (otherwise known as DB2 LUW).

In 2014, IBM launched a fully managed, cloud-based DB2 data warehouse offering called dashDB, along with a hosted database service named DB2 on Cloud. And, a fully managed, transactional version of dashDb named dashDb for Transactions soon followed.

Db2 Today

On June 22, 2017, as part of the release of Version 11.1, Modification Pack 2, Fix Pack 2 (v11.1.2.2), IBM rebranded all of its DB2 and dashDB offerings to create the following set of products:

• Db2 (formerly DB2 LUW)

• Db2 for z/OS (formerly DB2 for z/OS)

• Db2 Hosted (formerly DB2 on Cloud)

• Db2 on Cloud (formerly dashDB for Transactions)

• Db2 Event Store (a new an in-memory database optimized for eventdriven data processing)

• Db2 Warehouse on Cloud (formerly dashDB)

• Db2 Warehouse (formerly dashDB Local)

• IBM Integrated Analytics System (a new a hardware and software platform that combines the analytic performance and functionality of the IBM PureData® System with IBM Netezza®)

Data can be processed by any number of applications running concurrently against these products. And, while the manipulation of data in a Db2 database is still accomplished primarily with SQL — either by embedding it in a C, C++, or Java source code file, or by running it using Db2-specific tools like the Db2 Command Line Processor (CLP) — a variety of driver interfaces can be used to develop Db2 applications, including:

• Google Go

• Python — the language, the Python Database Interface (Python DBI), Django, and SQLAlchemy

• Node.js

• Sequelize Object Relational Mapping (ORM) for Node.js

• Java Database Connectivity (JDBC)

• PHP: Hypertext Preprocessor (PHP)

• Ruby on Rails

• Practical Extraction and Report Language Database Interface (Perl DBI)

• Microsoft .NET

• The Open Group Call Level Interface (CLI)

• Microsoft Open Database Connectivity (ODBC)

• Microsoft Active X Data Objects (ADO)

• Microsoft Object Linking and Embedding for Databases (OLE DB)

• Open Data Protocol (OData) Representational State Transfer (REST) application program interface (API)

Because the Db2 offerings available today — with the exception of Db2 for z/OS — share a common SQL engine, applications created for one platform (such as Db2) can be ported to another (for instance, Db2 Warehouse on Cloud) without requiring code modifications.

CHAPTER 2

Structured Query Language (SQL)

Structured Query Language (SQL) is a standardized language that is used to work with database objects and the data they contain. SQL consists of several different statements that can be used to define, alter, and delete database objects as well as insert, update, delete, and retrieve data values. Like other programming languages, SQL has a specific syntax and its own set of language elements. However, because SQL is non-procedural by design, it is not an actual programming language. (SQL statements are executed by an RDBMS engine, not the operating system.) Consequently, most applications that use SQL are constructed by combining the decision and sequence control of a high-level programming language (interpreted or compiled) with the data storage, manipulation, and retrieval capabilities SQL provides.

SQL statements are frequently categorized according to the function they have been designed to perform; five different categories are typically used:

• Embedded SQL Application Construct statements: Used solely for constructing embedded SQL applications. Some of the embedded SQL application construct statements that are recognized by Db2 are BEGIN DECLARE SECTION, END DECLARE SECTION, INCLUDE, and WHENEVER.

• Data Control Language (DCL) statements: Used to give (grant) and take away (revoke) authorities and privileges. Authorities convey the right to perform high-level administrative and maintenance/utility operations on a Db2 instance or database; privileges convey the right to perform certain actions against specific database objects (like tables, indexes, and views). The most common DCL statements are GRANT and REVOKE.

• Data Definition Language (DDL) statements: Used to create, alter, and delete individual database objects. The DDL statements that are used the most are CREATE, ALTER, and DROP.

• Data Manipulation Language (DML) statements: Used exclusively to store data in, modify data in, remove data from, and obtain data from select tables and/or views. The most common DML statements used are INSERT, UPDATE, DELETE, and SELECT.

• Transaction Management statements: Used to establish and terminate database connections and transactions. A transaction (also known as a unit of work) is a sequence of one or more SQL operations that are grouped together as a single unit. Such a unit is considered atomic (from the Greek word meaning "not able to be cut") because it is indivisible: either all of a transaction's work is carried out, or none of its operations performed are made permanent. The transaction management statements that are recognized by Db2 are CONNECT, ROLLBACK, and COMMIT.

One of the simplest ways to construct applications that require SQL is to use a methodology known as embedded SQL programming. As the name implies, embedded SQL applications are built by coding SQL statements directly into high-level programming language source code files. Unfortunately, this approach has some drawbacks. For one thing, high-level programming language compilers do not recognize, and therefore cannot interpret, SQL statements. And, some RDBMS engines cannot work directly with high-level programming language variables: instead, special variables known as host variables must be used to move data between an application and the RDBMS engine. (This is the case with Db2.) Therefore, source code files containing embedded SQL statements must be preprocessed with an SQL precompiler before they can be compiled.

Furthermore, embedded SQL applications lack interoperability. Because RDBMS vendors do not strictly adhere to SQL language standards, embedded SQL applications developed for one RDBMS will, in all likelihood, have to be modified before they can be used with another.

Fortunately, there is another way to construct applications that must perform SQL operations. And, while the process may not be as straightforward as embedding SQL statements in a source code file, it is almost as simple.

CHAPTER 3

The Db2 Call Level Interface

To overcome many of the challenges associated with embedded SQL programming, the X/Open Company, together with the SQL Access Group (SAG), which is now a part of X/Open, jointly developed a standard specification for a callable SQL interface in the early 1990s. This interface, known as the X/Open Call-Level Interface (or X/Open CLI), defined, in a consistent way, how an application should send SQL to a RDBMS for processing and how it should handle any result sets returned. Its primary purpose was to increase the portability of database applications by allowing them to become independent of any one RDBMS's SQL language.

In 1992, Microsoft Corporation developed its own callable SQL interface, known as Open Database Connectivity (ODBC). Based on a preliminary draft of the X/Open CLI specification but providing more functionality and capability, ODBC utilizes an architecture in which data source-specific ODBC libraries (known as drivers) are dynamically loaded and unloaded at application runtime by a component known as the ODBC Driver Manager. Each driver is responsible for processing ODBC API function calls, submitting SQL requests to a data source, returning results from that data source, and if appropriate, modifying an application's request so it conforms to the SQL syntax supported by the data source used.

Applications call ODBC APIs to submit SQL statements and retrieve results, and API calls are sent to the ODBC Driver Manager, where they are examined and routed to the appropriate ODBC driver for processing. It's important to note that ODBC APIs are used in two places: between an application and the ODBC Driver Manager, and between the ODBC Driver Manager and a driver. Consequently, an application written for one RDBMS can be executed against another without having to be altered: it simply has to establish a connection to the new RDBMS (which will cause the driver for the new RDBMS to be loaded, dynamically). Because multiple drivers and data sources can exist, a single application can interact with multiple data sources, simultaneously. And, since ODBC is not limited to Microsoft operating systems (other implementations are available on a variety of platforms), applications running on one platform can easily work with an RDBMS that is running on another.

The Db2 Call Level Interface (or Db2 CLI) is IBM's callable SQL interface to its Db2 product offerings. Written in C and C++, it is an application programming interface (API) for database access that uses function calls to pass dynamic SQL statements (as arguments) to a Db2 data source for processing. The Db2 CLI is based on both the Microsoft ODBC specification and the International Standard for SQL/CLI (specifically, ISO/IEC 90753:2003). These specifications were chosen in an effort to follow industry standards and to provide a shorter learning curve for application developers who are already familiar with using one of these interfaces. Thus, the Db2 CLI driver provides support for all of the ODBC 3.51 core APIs (except the SQLDrivers() API), all ODBC Level 1 and Level 2 functions, some X/Open CLI-specific functions that are not supported by ODBC, and some Db2-specific APIs that enable application developers to take advantage of features and functionality that are only available with Db2.

Applications that only work with Db2 data sources can link directly to the Db2 CLI driver. And, any ODBC Driver Manager can dynamically load this library as if it were an ODBC driver. However, it is important to note that when an application uses the Db2 CLI driver independently, it cannot communicate with anything other than Db2 data sources. Figure 3.1 illustrates how applications use the Db2 CLI driver in a Db2-only environment; Figure 3.2 illustrates how applications use the Db2 CLI driver in an ODBC Driver Manager environment.

Because Db2 CLI/ODBC applications rely on a standardized set of APIs to execute SQL statements and perform database-related operations, the way in which they are constructed differs from when embedded SQL is used:

• Db2 CLI/ODBC applications do not require the explicit declaration and use of host variables; any variable can be used to send data to or retrieve data from a connected data source.

• Cursors do not have to be explicitly created (declared) by Db2 CLI/ODBC applications. Instead, they are automatically generated whenever they are needed. (When a SELECT statement is executed from within an application program, Db2 uses a mechanism known as a cursor to retrieve data values from the result data set produced. The name "cursor" probably originated from the blinking cursor found on early computer screens. And just as that cursor indicated the current position on the screen, a Db2 cursor points to the current position in a result data set — i.e., the current row).

• Cursors do not have to be explicitly opened in Db2 CLI/ODBC applications; they are implicitly opened the moment they are created.

• Db2 CLI/ODBC functions treat environment (server), connection, and SQL statement-related information as abstract data objects. This eliminates the need to use RDBMS product-specific data structures (such as the Db2 SQLCA data structure).

• Db2 CLI/ODBC applications inherently have the ability to establish multiple connections to multiple data sources or to the same data source, simultaneously.

• Because ROLLBACK and COMMIT statements can be dynamically prepared by some data sources but not others, they are not used in Db2 CLI/ODBC applications. Instead, Db2 CLI/ODBC applications must use an API to end active transactions (unless AUTOCOMMIT behavior has been enabled).

(Continues…)

---

Excerpted from "QuickStart Guide to Db2 Development with Python"
by .
Copyright © 2019 Roger E. Sanders.
Excerpted by permission of MCPress Online, LLC.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Table of Contents

1 What Is IBM Db2? 1

From the Mainframe to Distributed Platforms and the IBM Cloud 3

Db2 Today 3

2 Structured Query Language (SQL) 5

3 The Db2 Call Level Interface 9

4 Python and Db2 15

Installing the Python Interpreter 17

IBM Db2 Support for Python 18

5 Setting Up a Python-Db2 Development Environment 21

A Word About My Development Platform: the UDOO X86 22

Downloading the Db2 Software 23

Enabling root User Access (Ubuntu Linux Setup Only) 25

Preparing a Linux Server for Db2 Installation 25

Installing the Db2 Software 27

Building the SAMPLE Database 36

Installing the Db2 Python Library (Driver) 38

Summary 40

6 Building Python Applications That Work with Db2 41

Functionality Provided by the ibm_db and ibm_db_dbi Python Libraries 42

Special Objects Used by Db2-Python Applications 47

Establishing a Db2 Server or Database Connection 48

Transaction Processing: Executing SQL Statements 51

Transaction Processing: Retrieving Query Results 53

Transaction Processing: Obtaining Result Set Information 56

Transaction Processing: Terminating the Current Transaction 58

Calling Stored Procedures 60

Terminating a Db2 Server or Database Connection 61

Obtaining Information About a Data Source and Setting Driver Options 62

Diagnostics and Error Handling 63