Home > Store

DB2 Developer's Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, 6th Edition

Add To My Wish List

DB2 Developer's Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, 6th Edition

By Craig S. Mullins
Published May 1, 2012 by IBM Press. Part of the IBM Press series.

eBook

Your Price: $70.39
List Price: $87.99
Includes EPUB and PDF
About eBook Formats

This eBook includes the following formats, accessible from your Account page after purchase:

ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

Adobe Reader PDF The popular standard, used most often with the free Acrobat® Reader® software.

This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

Add to cart

Description

Sample Content

Updates

More Information

Description

Copyright 2012
Dimensions: 7-3/8" x 9-1/8"
Edition: 6th

eBook
ISBN-10: 0-13-283645-9
ISBN-13: 978-0-13-283645-6

DB2 Developer's Guide is the field's #1 go-to source for on-the-job information on programming and administering DB2 on IBM z/OS mainframes. Now, three-time IBM Information Champion Craig S. Mullins has thoroughly updated this classic for DB2 v9 and v10. Mullins fully covers new DB2 innovations including temporal database support; hashing; universal tablespaces; pureXML; performance, security and governance improvements; new data types, and much more. Using current versions of DB2 for z/OS, readers will learn how to: * Build better databases and applications for CICS, IMS, batch, CAF, and RRSAF * Write proficient, code-optimized DB2 SQL * Implement efficient dynamic and static SQL applications * Use binding and rebinding to optimize applications * Efficiently create, administer, and manage DB2 databases and applications * Design, build, and populate efficient DB2 database structures for online, batch, and data warehousing * Improve the performance of DB2 subsystems, databases, utilities, programs, and SQL stat DB2 Developer's Guide, Sixth Edition builds on the unique approach that has made previous editions so valuable. It combines: * Condensed, easy-to-read coverage of all essential topics: information otherwise scattered through dozens of documents * Detailed discussions of crucial details within each topic * Expert, field-tested implementation advice * Sensible examples



Sample Content

Preface     xxiii

PART I: SQL TECHNIQUES, TIPS, AND TRICKS

Chapter 1 The Magic Words     3
An Overview of SQL      4
SQL Tools of the Trade     13
Static SQL     42
Dynamic SQL     44
SQL Performance Factors     45

Chapter 2 Data Manipulation Guidelines     56
A Bag of Tricks     56
SQL Access Guidelines     58
Complex SQL Guidelines     90
Common Table Expressions and Recursion     110
Working with Nulls     115
Date and Time Guidelines     119
Data Modification Guidelines     125

Chapter 3 Using DB2 Functions     135
Aggregate Functions     135
Scalar Functions     141
Table Functions     159
MQSeries Built-In Functions     159
XML Built-In Functions     161
The RAISE_ERROR Function     162
The CAST Operation     163
Built-In Function Guidelines     163

Chapter 4 Using DB2 User-Defined Functions and Data Types     167
What Is a User-Defined Function?     167
Types of User-Defined Functions (UDFs)     168
What Is a User-Defined Data Type?     190
User-Defined Data Types (UDTs) and Strong Typing     191

Chapter 5 Data Definition Guidelines     200
An Overview of DB2 Database Objects     200
DB2 Databases     201
Creating and Using DB2 Table Spaces     204
DB2 Storage and STOGROUPs     239
Table Guidelines     244
General Table Guidelines     275
Normalization and Denormalization     278
Assuring Data Integrity in DB2     290
Referential Integrity     290
Views, Aliases, and Synonyms     302
Index Guidelines     313
Naming Conventions     313
Miscellaneous DDL Guidelines     322

Chapter 6 DB2 Indexing and Hashing Guidelines     324
How an Index Works     324
Creating Indexes     326
DB2 Hashing and Hash Organized Tables     337
Index and Hash Guidelines     34

Chapter 7 Database Change Management, Schema Evolution, and Database Definition On Demand     53
Online Schema Changes     354
Versioning for Online Schema Changes     370

Chapter 8 Using DB2 Triggers     373
What Is a Trigger?     373
Trigger Guidelines     388

Chapter 9 Large Objects and Object/Relational Databases     393
Defining the Term “Object/Relational”     393
What Is a Large Object?     394
LOB Guidelines     403
DB2 Extenders     407

Chapter 10 pureXML: Using XML in DB2 for z/OS     408
What Is XML?     408
pureXML     412
XML-DB2 Guidelines     425

Chapter 11 Supporting Temporal Data in DB2 for z/OS 428
The Need for Temporal Data     428
DB2 Temporal Support     430
Temporal Data Guidelines     446
Summary     447

Chapter 12 DB2 Security, Authorization, and Auditing     448
Authorization and Privileges     448
Database Auditing     476
Using External Security (for Example, RACF, ACF2,
and Top Secret)     480

PART II: DB2 APPLICATION DEVELOPMENT

Chapter 13 Using DB2 in an Application Program     486
Embedded SQL Basics     487
Embedded SQL Guidelines     489
Host Variables     504
Programming with Cursors     511
Modifying Data with Embedded SQL     525
Application Development Guidelines     527
Batch Programming Guidelines     536
Online Programming Guidelines     547
General SQL Coding Guidelines     552
Introduction to Java     554
Using REXX and DB2     563
Developing Applications Using Only SQL     565

Chapter 14 Dynamic SQL Programming     567
What Is Dynamic SQL?     567
Dynamic SQL Versus Static SQL     569
The Four Classes of Dynamic SQL     576
pureQuery     588
Making Dynamic SQL More Static and Vice Versa     589
Dynamic SQL Guidelines     594

Chapter 15 Program Preparation     601
Program Preparation Steps     601
Running a DB2 Program     608
Preparing a DB2 Program     609
What Is a DBRM?     622
What Is a Plan?     622
What Is a Package?     623
What Is a Collection?     628
Versions     629
Converting DBRM-Based Plans in DB2 V10     630
Program Preparation Objects     631
Program Preparation Guidelines     632

Chapter 16 Using DB2 Stored Procedures 65    6
What Is a Stored Procedure?     657
Implementing DB2 Stored Procedures     661
Procedural SQL     678
The Procedural DBA     683
IBM Data Studio     687

Chapter 17 DB2 and the Internet     689
The Internet Phenomenon     689
Accessing DB2 over the Internet     692
Finding DB2 Information Using the Internet     695

PART III: DB2 IN-DEPTH

Chapter 18 The Doors to DB2     704
DB2 Program Execution Basics     704
TSO (Time-Sharing Option)     706
CICS (Customer Information Control System)     726
IMS (Information Management System)     751
CAF (Call Attach Facility)     763
RRSAF (Recoverable Resource Manager Services Attach Facility)     767
Comparison of the Environments     768

Chapter 19 Data Sharing     772
Data Sharing Benefits     772
Data Sharing Requirements     774
The DB2 Coupling Facility     778
Data Sharing Naming Conventions     782
Data Sharing Administration     783
Data Sharing Application Development Guidelines     787
Data Sharing Administration Guidelines     788

Chapter 20 DB2 Behind the Scenes     792
The Physical Storage of Data     792
What Makes DB2 Tick     808
Specialty Processors     812

Chapter 21 The Optimizer     816
Physical Data Independence     817
How the Optimizer Works     818
Filter Factors     821
Screening     823
Access Path Strategies     824
Other Operations Performed by the Optimizer     868

Chapter 22 The Table-Based Infrastructure of DB2     874
The DB2 Catalog     874
The DB2 Directory     886

Chapter 23 Locking DB2 Data     889
How DB2 Manages Locking     889
Locks Versus Latches     892
Lock Duration     892
Table Space Locks     895
Table Locks     897
Page Locks     898
Row Locks     899
Lock Suspensions, Timeouts, and Deadlocks     901
Partition Independence     904
Lock Avoidance     908
Data Sharing Global Lock Management     911
LOBs and Locking     914
DB2 Locking Guidelines     916
Other DB2 Components     921
The Big Picture     922

PART IV: DB2 PERFORMANCE MONITORING
Defining DB2 Performance.     926
Types of DB2 Performance Monitoring     927

Chapter 24 DB2 Performance Monitoring     928
DB2 Traces     929
Trace Destinations     936
Using IFCIDs     937
Tracing Guidelines     938
Performance Monitoring and Reporting: Online and Batch     940
Monitoring and Reporting Strategy     967
Performance Profiles     970
Viewing DB2 Console Messages     972
Displaying the Status of DB2 Resources     977
Monitoring z/OS     979

Chapter 25 Using EXPLAIN     980
How EXPLAIN Works     980
Access Paths and the PLAN_TABLE     982
Cost Estimates and the DSN_STATEMNT_TABLE     998
Function Resolution and the DSN_FUNCTION_TABLE     1001
Additional Explain Tables     1002
Explaining the Dynamic Statement Cache     1003
EXPLAIN Guidelines     1005
Additional Tools for Managing Access Paths     1012

Chapter 26 The Five R’s     1014
Approaches to Rebinding     1014
A Best Practice Approach to Rebinding     1016

Chapter 27 DB2 Object Monitoring Using the DB2 Catalog and RTS     1021
DB2 Catalog Queries     1021
Real Time Statistics     1048
Reviewing the Rules for an Effective Monitoring Strategy     1058

PART V: DB2 PERFORMANCE TUNING

Chapter 28 Tuning DB2’s Environment     1064
Tuning the z/OS Environment     1064
Tuning the Teleprocessing Environment     1087

Chapter 29 Tuning DB2’s Components     1089
Tuning the DB2 Subsystem     1089
Tuning the Database Design     1114
Tuning the Application     1116
The Causes of DB2 Performance Problems     1137

Chapter 30 DB2 Resource Governing     1143
The Resource Limit Facility     1143

PART VI: DB2 UTILITIES AND COMMANDS

Chapter 31 An Introduction to DB2 Utilities     1152
Generating Utility JCL     1152
Monitoring DB2 Utilities     1156
The IBM DB2 Utilities     1158
Using LISTDEF and TEMPLATE     1159
Issuing SQL Statements in DB2 Utilities     1173

Chapter 32 Data Consistency Utilities     1176
The CHECK Utility     1177
The CHECK DATA Option     1177
The CHECK LOB Option     1186
The CHECK INDEX Option     1188
The REPAIR Utility     1191
The REPAIR DBD Option     1192
The REPAIR LOCATE Option     1193
The REPAIR SET Option     1196
REPAIR and Versions     1198
The REPORT Utility     1198
The DIAGNOSE Utility     1200

Chapter 33 Backup and Recovery Utilities     1201
The COPY Utility     1202
The COPYTOCOPY Utility     1215
The MERGECOPY Utility     1218
The QUIESCE Utility     1220
The RECOVER Utility     1224
The REBUILD INDEX Utility     1232
The REPAIR Utility     1235
The REPORT RECOVERY Utility     1235
Backing Up and Restoring the System     1236

Chapter 34 Data Movement and Organization Utilities     1240
The LOAD Utility     1240
The UNLOAD Utility     1260
The REORG Utility     1265

Chapter 35 Catalog Manipulation Utilities     1289
The CATENFM Utility     1289
The CATMAINT Utility     1289
The DSNJCNVB Utility     1290
The MODIFY RECOVERY Utility     1290
The MODIFY STATISTICS Utility     1293
The RUNSTATS Utility     1295
The STOSPACE Utility     1311

Chapter 36 Stand-Alone Utilities and Sample Programs     1314
The Stand-Alone Utilities     1314
DB2 Sample Programs     1332

Chapter 37 DB2 Commands     1340
DB2 Environment Commands     1340
Information-Gathering Commands     1343
Administrative Commands     1353
Environment Control Commands     1358
DSN Commands     1359
IMS Commands     1361
CICS Commands     1362
TSO Commands     1364
IRLM Commands     1364

Chapter 38 DB2 Utility and Command Guidelines     1366
Utility Guidelines     1366
The Pending States     1372

Chapter 39 DB2 Contingency Planning     1376
What Is a Disaster?     1376
DB2 Recovery Basics     1380
Additional DB2 Disaster Recovery Technologies     1387
DB2 Environmental Considerations     1388
DB2 Contingency Planning Guidelines     1390

PART VII: THE IDEAL DB2 ENVIRONMENT

Chapter 40 Components of a Total DB2 Solution     1394
DB2 Tools     1394
DB2 Tools Vendors     1420

Chapter 41 Organizational Issues     1423
Education     1423
Standards and Procedures     1429
Operational Support.     1440
Political Issues     1441
Environmental Support     1443
Tool Requirements     1443

Part VIII Distributed DB2
The Advantages of Data Distribution     1446
DB2 Data Distribution     1446
DB2 Data Warehousing     1447

Chapter 42 DRDA     1448
What Is DRDA?     1448
DRDA Functions     1449
DRDA Architectures and Standards     1451
The Five DRDA Levels     1453
Putting It All Together     1455

Chapter 43 Distributed DB2     1458
Distributing Data Using DB2    1458
DB2 Support for the DRDA Levels     1460
Methods of Accessing Distributed Data     1460
Packages for Static SQL     1465
Two-Phase Commit     1466
Miscellaneous Distributed Topics     1470

Chapter 44 DB2 Connect     1473
An Overview of IBM DB2 Connect    1473

Chapter 45 Distribution Guidelines     1485
Distribution Behind the Scenes     1485
Block Fetch     1487
Dynamic Cursor Pre-Open     1491
Distributed Performance Problems     1491
Distributed Database Design Issues     1496
Distributed Data Placement     1499
Distributed Optimization     1500
Distributed Security Guidelines     1501
Miscellaneous Distributed Guidelines     1502

Chapter 46 Data Warehousing with DB2     1506
Defining the Basic Terms     1507
Designing a Data Warehouse     1510
Populating a Data Warehouse     1513
Accessing the Data Warehouse     1519
Managing the Data Warehouse     1520
The Big Picture     1520
IBM Data Warehousing Solutions     1521
Materialized Query Tables     1522
General Data Warehouse Guidelines     1533
DB2-Specific Data Warehousing Guidelines     1538

Index     1541



Updates

Submit Errata



More Information



InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address

Corporate, Academic, and Employee Purchases