W. David Ashley

Foundation Db2 and Python

Access Db2 with Module-Based API Examples Using Python

1st ed.
W. David Ashley
Austin, TX, USA
ISBN 978-1-4842-6941-1e-ISBN 978-1-4842-6942-8
https://doi.org/10.1007/978-1-4842-6942-8
© W. David Ashley 2021
Apress Standard
The use of general descriptive names, registered names, trademarks, service marks, etc. in this publication does not imply, even in the absence of a specific statement, that such names are exempt from the relevant protective laws and regulations and therefore free for general use.
The publisher, the authors and the editors are safe to assume that the advice and information in this book are believed to be true and accurate at the date of publication. Neither the publisher nor the authors or the editors give a warranty, expressed or implied, with respect to the material contained herein or for any errors or omissions that may have been made. The publisher remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

This Apress imprint is published by the registered company APress Media, LLC part of Springer Nature.

The registered company address is: 1 New York Plaza, New York, NY 10004, U.S.A.

This book is dedicated to teachers everywhere, but especially all those who had me as a student.

Table of Contents
Chapter 1:​ Introduction to Db21
What Is a Relational Database?​3
The Relational Model3
Domains4
Keys and Indexes4
Relationships4
Transactions5
Stored Procedures5
Constraints6
Normalization6
SQL6
Data Definition Language (DDL)7
Data Query Language (DQL)7
Data Control Language (DCL)7
Data Manipulation Language (DML)7
The ibm_​db Project8
Summary8
Chapter 2:​ Installing Db29
My Development Environment9
Installation Prerequisites10
Planning the Db2 Install11
Installing Db212
Db2 Post-install Tasks19
Installing the Db2 Sample Database20
Summary21
Chapter 3:​ Db2 Management23
Db2 Instances23
db2ilist25
Instance Environment Commands25
Creating an Instance26
Arranging a Communication Port and Host for an Instance26
Updating an Instance27
Upgrading an Instance28
Dropping an Instance29
Using Other Commands with an Instance29
Databases30
Db2 Catalog Views31
Locking Event Monitor31
Tablespace Information32
Storage Group Control Files33
Global Configuration File33
History Files33
Logging Files33
Automated Storage Containers33
Creating a Database34
Listing Databases35
Activating a Database36
Deactivating a Database36
Connecting to a Database36
Dropping a Database36
Tables37
Table Types39
Built-in Data Types39
Creating a Table41
Alter a Table43
Other Table SQL Statements43
Dropping a Table44
Summary44
Chapter 4:​ Database Physical Design45
Phase 1:​ Data Gathering and Normalization46
Data Gathering46
Data Normalization48
Business Rules51
Phase 2:​ Physical Design of the Database51
Backups52
Summary53
Chapter 5:​ Db2 Utilities55
Backup Command56
What Is a Backup?​56
Backup Verification57
Advanced Backup Options57
Backup Syntax57
Export Command59
Command syntax60
Usage Notes60
Import Command61
Command Syntax61
Load Command62
Command Syntax62
Restore Command63
Command Syntax64
Summary65
Chapter 6:​ Business Rules and Constraints67
NOT NULL Attribute67
Primary Key68
Indexes69
Foreign Keys70
CHECK and Unique Constraints71
DEFAULT Constraint72
Triggers72
Summary76
Chapter 7:​ Writing Good SQL for Db277
Relational Theory77
Reduce Passes Through Data78
Using Indexes to Increase Performance81
Sorting and Grouping81
Programs Containing SQL82
Use Db2 Utilities Where Possible83
Db2 Functions84
Multiple Ways to Code SQL86
Summary86
Chapter 8:​ Python and ibm_​db87
Your First Python ibm_​db Program89
Using Parameter Markers96
More on Parameter Markers98
Producing Multiple Reports with Parameter Markers100
Using Parameter Markers Without Binding Variables103
Joining Tables106
Inserts, Updates, and Deletes109
Some Other ibm_​db APIs112
Creating Database Objects116
Obtaining Attributes of an Existing Table120
Obtaining Attributes of a Result Set126
ibm_​db_​dbi and Python130
Where Is the ibm_​db Module Going?​133
The ibm_​db_​dbi Module134
The Django Database Interface134
The SQLAlchemy Adapter135
The Alembic Adapter136
The Future137
Summary137
Appendix A:​ Python ibm_​db API139
ibm_​db APIs139
ibm_​db.​active139
ibm_​db.​autocommit140
ibm_​db.​bind_​param141
ibm_​db.​callproc143
ibm_​db.​client_​info144
ibm_​db.​close146
ibm_​db.​column_​privileges147
ibm_​db.​columns148
ibm_​db.​commit151
ibm_​db.​conn_​error152
ibm_​db.​conn_​errormsg153
ibm_​db.​connect154
ibm_​db.​createdb157
ibm_​db.​createdbNX158
ibm_​db.​cursor_​type159
ibm_​db.​dropdb160
ibm_​db.​exec_​immediate160
ibm_​db.​execute162
ibm_​db.​execute_​many163
ibm_​db.​fetch_​tuple165
ibm_​db.​fetch_​assoc166
ibm_​db.​fetch_​both167
ibm_​db.​fetch_​row168
ibm_​db.​field_​display_​size169
ibm_​db.​field_​name169
ibm_​db.​field_​num170
ibm_​db.​field_​precision172
ibm_​db.​field_​scale173
ibm_​db.​field_​type174
ibm_​db.​field_​width174
ibm_​db.​foreign_​keys175
ibm_​db.​free_​result178
ibm_​db.​free_​stmt179
ibm_​db.​get_​option179
ibm_​db.​next_​result181
ibm_​db.​num_​fields183
ibm_​db.​num_​rows184
ibm_​db.​pconnect185
ibm_​db.​prepare187
ibm_​db.​primary_​keys188
ibm_​db.​procedure_​columns190
ibm_​db.​procedures192
ibm_​db.​recreatedb194
ibm_​db.​result194
ibm_​db.​rollback195
bm_​db.​server_​info196
ibm_​db.​set_​option199
ibm_​db.​special_​columns201
ibm_​db.​statistics203
ibm_​db.​stmt_​error206
ibm_​db.​stmt_​errormsg206
ibm_​db.​table_​privileges207
ibm_​db.​tables209
Summary210
Index211
About the Author
W. David Ashley
../images/504747_1_En_BookFrontmatter_Figb_HTML.png

is a technical writer for Skillsoft where he specializes in open source, particularly Linux. As a member of the Linux Fedora documentation team, he recently led the Libvirt project documentation and wrote the Python programs included with it. He has developed in 20 different programming languages during his 30 years as a software developer and IT consultant, including more than 18 years at IBM and 12 years with American Airlines.

 
About the Technical Reviewer
Sourav Bhattacharjee
../images/504747_1_En_BookFrontmatter_Figc_HTML.png

is a senior technical member for Oracle Cloud Infrastructure. As part of IBM Watson Health Lab, he has developed many scalable systems, published a few research papers, and applied some patents to USPTO. He has an ample amount of hands-on experience in Python, Java, machine learning, and many database systems. He earned his master’s degree from the Indian Institute of Technology, Kharagpur, India.

 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.32.230