Foundation Db2 and Python: Access Db2 with Module-Based API Examples Using Python

✍ Scribed by W. David Ashley

• Publisher: Apress
• Year: 2021
• Tongue: English
• Leaves: 223
• Edition: 1
• Category: Library

✦ Synopsis

Work with Db2 to write SQL and access databases using optimized code for the fastest response. This book will give you complete documentation on DB2 via Python for the IBM_db module and provide a number of examples for the usage of each module API.

Begin by getting your free version of Db2 for Linux and Windows. While the book concentrates more on the Linux version of Db2, it also covers enough of the Windows version so that you're comfortable with obtaining and installing Db2 on your version of Windows. Next, you'll see how to install the sample database that comes with Db2, and take some data from the web to design a database around it, including tables and indexes.

For Db2 to be really useful you need to use strong SQL expressions. This book provides specific examples of how to avoid using poor ones that can cause extra processing time for the query. Lastly, you'll look at each API in the ibm_db and ibm_db_dbi module. This module is not sponsored by IBM and must be installed separately from the Db2 database.

After reading Foundation Db2 and Python you'll be able to install Db2 on Windows or Linux, and perform backups and restore data. 

What You'll Learn

• Obtain and install Db2 properly on Linux and Windows
• Create databases and load them on Db2
• Work with ibm_db and ibm_db_dbi API modules for Python
• Write SQL for Db2
• Review the future of the ibm_db Python module 

Who This Book Is For

Python programmers and DB2 administrators interested in building application with Python and DB2

✦ Table of Contents

Table of Contents
About the Author
About the Technical Reviewer
Chapter 1: Introduction to Db2
What Is a Relational Database?
The Relational Model
Domains
Keys and Indexes
Relationships
Transactions
Stored Procedures
Constraints
Normalization
SQL
Data Definition Language (DDL)
Data Query Language (DQL)
Data Control Language (DCL)
Data Manipulation Language (DML)
The ibm_db Project
Summary
Chapter 2: Installing Db2
My Development Environment
Installation Prerequisites
Planning the Db2 Install
Installing Db2
Db2 Post-install Tasks
Installing the Db2 Sample Database
Summary
Chapter 3: Db2 Management
Db2 Instances
db2ilist
Instance Environment Commands
Creating an Instance
Arranging a Communication Port and Host for an Instance
Updating an Instance
Upgrading an Instance
Dropping an Instance
Using Other Commands with an Instance
Databases
Db2 Catalog Views
Locking Event Monitor
Tablespace Information
Storage Group Control Files
Global Configuration File
History Files
Logging Files
Automated Storage Containers
Creating a Database
Listing Databases
Activating a Database
Deactivating a Database
Connecting to a Database
Dropping a Database
Tables
Table Types
Built-in Data Types
Creating a Table
Alter a Table
Other Table SQL Statements
Dropping a Table
Summary
Chapter 4: Database Physical Design
Phase 1: Data Gathering and Normalization
Data Gathering
Data Normalization
First Normal Form
Second Normal Form
Third Normal Form
Fourth Normal Form
Business Rules
Phase 2: Physical Design of the Database
Backups
Summary
Chapter 5: Db2 Utilities
Backup Command
What Is a Backup?
Backup Verification
Advanced Backup Options
Backup Syntax
Command Syntax
How It Works
Usage Notes
Export Command
Command syntax
Usage Notes
Import Command
Command Syntax
Load Command
Command Syntax
Restore Command
Command Syntax
Summary
Chapter 6: Business Rules and Constraints
NOT NULL Attribute
Primary Key
Indexes
Foreign Keys
CHECK and Unique Constraints
DEFAULT Constraint
Triggers
Summary
Chapter 7: Writing Good SQL for Db2
Relational Theory
Reduce Passes Through Data
Using Indexes to Increase Performance
Sorting and Grouping
Programs Containing SQL
Use Db2 Utilities Where Possible
Db2 Functions
Multiple Ways to Code SQL
Summary
Chapter 8: Python and ibm_db
Your First Python ibm_db Program
Using Parameter Markers
More on Parameter Markers
Producing Multiple Reports with Parameter Markers
Using Parameter Markers Without Binding Variables
Joining Tables
Inner Joins
Outer Joins
Inserts, Updates, and Deletes
Some Other ibm_db APIs
Creating Database Objects
Obtaining Attributes of an Existing Table
Obtaining Attributes of a Result Set
ibm_db_dbi and Python
Where Is the ibm_db Module Going?
The ibm_db_dbi Module
The Django Database Interface
The SQLAlchemy Adapter
The Alembic Adapter
The Future
Summary
Appendix A: Python ibm_db API
ibm_db APIs
ibm_db.active
Description
Example
ibm_db.autocommit
Description
Example
ibm_db.bind_param
Description
Parameters
Return Values
Example
ibm_db.callproc
Description
Example
ibm_db.client_info
Description
Return Values
Example
ibm_db.close
Description
Example
ibm_db.column_privileges
Description
Return Values
Example
ibm_db.columns
Description
Parameters
Return Values
Example
ibm_db.commit
Description
Example
ibm_db.conn_error
Description
Example
ibm_db.conn_errormsg
Description
Example
ibm_db.connect
Description
Parameters
Return Values
Example 1
Sample Configuration in db2dsdriver.cfg
ibm_db.createdb
Description
Example
ibm_db.createdbNX
Description
Example
ibm_db.cursor_type
Description
Return Values
Example
ibm_db.dropdb
Description
Example
ibm_db.exec_immediate
Description
Parameters
Return Values
Example
ibm_db.execute
Description
Example
ibm_db.execute_many
Description
Example
ibm_db.fetch_tuple
Description
Example
ibm_db.fetch_assoc
Description
Example
ibm_db.fetch_both
Description
Example
ibm_db.fetch_row
Description
Example
ibm_db.field_display_size
Description
Example
ibm_db.field_name
Description
Example
ibm_db.field_num
Description
Parameters
Return Values
Example
ibm_db.field_precision
Description
Example
ibm_db.field_scale
Description
Example
ibm_db.field_type
Description
Example
ibm_db.field_width
Description
Example
ibm_db.foreign_keys
Description
Parameters
Return Values
Example
ibm_db.free_result
Description
Example
ibm_db.free_stmt
Description
ibm_db.get_option
Description
Example
ibm_db.next_result
Description
Example
ibm_db.num_fields
Description
Example
ibm_db.num_rows
Description
Example
ibm_db.pconnect
Description
Example
ibm_db.prepare
Description
Parameters
Return Values
Example
ibm_db.primary_keys
Description
Return Values
Example
ibm_db.procedure_columns
Description
Return Values
Example
ibm_db.procedures
Description
Return Values
Example
ibm_db.recreatedb
Description
Example
ibm_db.result
Description
Example
ibm_db.rollback
Description
Example
bm_db.server_info
Description
Return Values
Example
ibm_db.set_option
Description
Example
ibm_db.special_columns
Description
Parameters
Return Values
Example
ibm_db.statistics
Description
Return Values
Example
ibm_db.stmt_error
Description
Example
ibm_db.stmt_errormsg
Description
Example
ibm_db.table_privileges
Description
Return Values
Example
ibm_db.tables
Description
Parameters
Return Values
Example
Summary
Index