SQL/400 Developer's Guide

Paul Conte, Mike Cravitz

508 pages, parution le 01/12/2000

Ajouter à une liste

Indisponible

Résumé

Paul Conte and Mike Cravitz have written the must-have guide for AS/400 application developers. This new book will help you create and manipulate comprehensive, complex, professional-quality SQL/400 databases. The textbook covers database and SQL fundamentals, SQL/400 Data Definition Language (DDL) and Data Manipulation Language (DML), and database modeling and design. Coding suggestions reinforce the topics covered and provide practical advice on how to produce robust, well-functioning code.

Contents

Preface		vii
Chapter 1 Introduction to UDB/400, the AS/400?s Integrated Database		1
Chapter 2 Introduction to SQL on the AS/400		23
Chapter 3 Collections, Tables, and Indexes		41
Chapter 4 Database Constraints		69
Chapter 5 Select Statement Basics		81
Chapter 6 Views		109
Chapter 7 Insert, Update, and Delete Statements		121
Chapter 8 Advanced DML Statement Features		131
Chapter 9 Database Security and the Grant and Revoke Statements		157
Chapter 10 Static and Dynamic Embedded Statements		185
Chapter 11 SQL Cursors		207
Chapter 12 The Create SQL Program Commands		221
Chapter 13 Additional SQL/400 Programming Techniques		229
Chapter 14 Creating an RPG IV Program to Update the Customer Table		281
Chapter 15 Creating a Cobol Program to Update the Customer Table		293
Chapter 16 Additional Database Topics		307
Chapter 17 Introduction to Data Modeling and Database Design		337
Chapter 18 The Relational Database Model		345
Chapter 19 Logical Data Modeling		371
Chapter 20 Entity Relationship Diagramming		385
Chapter 21 Physical Database Design		397
Appendix A SQL/400 Statement Syntax		415
Appendix B CL Command Descriptions for SQL and Related Objects
Appendix C Using Programming Development Manager (PDM)
Appendix D Using Source Entry Utility (SEU)
Appendix E Annotated Bibliography
Glossary

*Part I: Database and SQL Fundamentals*
Chapter 1 Introduction to UDB/400, the AS/400?s Integrated Database		1
Chapter Overview		1
Databases and Database Management Systems		1
The AS/400 Integrated File System (IFS)		2
OS/400 Objects, Libraries, and User Profiles		4
QSys Objects, Libraries, and User Profiles		5
Files, Record Formats, and Members		7
Physical and Logical Files		9
File and Field Descriptions		11
Access Paths and Indexes		12
Creating Files		13
Creating Files with SQL		13
Creating Files with Operations Navigator		15
Creating Files with DDS		15
Accessing Files from HLL Programs		17
Chapter Summary		18
Key Terms		19
Additional Resources [sidebar]		20
Exercises		20
Chapter 2 Introduction to SQL on the AS/400		23
Chapter Overview		23
Introduction to SQL		23
Entering SQL/400 Statements		26
Using RunSQLStm to Enter SQL/400 Statements		26
Using Interactive SQL		27
Saving Statements to a Source Member		30
Changing Session Attributes		32
Exiting ISQL		34
Operations Navigator		35
The SQL Catalog		36
Coding Suggestions		38
Chapter Summary		39
Key Terms		39
Exercises		39
Chapter 3 Collections, Tables, and Indexes		41
Chapter Overview		41
Creating a Collection		41
Creating a Table		42
SQL/400 Naming Options		43
Column Definitions		44
Column Data Types		48
String Data Types		48
Numeric Data Types		50
Date, Time, and Timestamp Data Types		52
Datalink Data Types		54
SQL and System Table Names		54
SQL and System Column Names		55
Descriptive Text and Column Headings		55
Creating and Using User-Defined Types		57
Using Standard Data Types		58
Adding, Dropping, or Changing Table Columns		59
Creating an Index		59
Creating an Alias		61
Dropping Collections, Tables, Distinct Types, Indexes, and Aliases		61
Guidelines for SQL/400 Names		62
Coding Suggestions		64
Chapter Summary		65
Key Terms		66
Exercises		66
Chapter 4 Database Constraints		69
Chapter Overview		69
Introduction to Database Constraints		69
Primary Key Constraints		70
Unique Constraints		71
Foreign Key Constraints		71
Additional Foreign Key Constraint Considerations		74
Check Constraints		75
Adding and Removing Constraints from an Existing Table		76
Foreign Key and Check Constraint States		77
Coding Suggestions		78
Chapter Summary		78
Key Terms		79
Exercises		79
Chapter 5 Select Statement Basics		81
Chapter Overview		81
Introduction to DML		81
Dynamic vs. Static Execution		82
Retrieving Rows with the Select Statement		83
Search Conditions and Predicates		86
Literals, Expressions, and Scalar Functions		88
Date and Time Arithmetic		94
Column Functions		96
Group By and Having Clauses		97
The Order By Clause		100
Case Expressions		101
More About Predicates		102
Coding Suggestions		105
Chapter Summary		105
Key Terms		106
Exercises		107
Chapter 6 Views		109
Chapter Overview		109
Creating a View		109
Components of the Create View Statement		110
With Check Option and With Local Check Option		114
More View Examples		115
Retrieving Rows from a View with a Select Statement	117
Coding Suggestions		118
Chapter Summary		118
Key Terms		118
Exercises		119
Chapter 7 Insert, Update, and Delete Statements		121
Chapter Overview		121
Using DML to Modify Table Data		121
The Insert Statement		121
The Update Statement		123
Set Clause Variations		124
The Delete Statement		125
Coding Suggestions		127
Chapter Summary		128
Key Terms		128
Exercises		128
Chapter 8 Advanced DML Statement Features		131
Chapter Overview		131
Casting Between Data Types		131
Subqueries		133
Basic Predicate with Subquery		134
In Predicate with Subquery		134
Exists Predicate and Correlated Subquery		135
Quantified Predicate		136
Joins		137
Joining More Than Two Tables		143
Combining Subqueries and Joins		145
The SQL Union Operator		146
Table Expressions		149
An Introduction to User-Defined Functions: Sourced Functions		151
Coding Suggestions		152
Chapter Summary		153
Key Terms		154
Exercises		154
Chapter 9 Database Security and the Grant and Revoke Statements		157
Chapter Overview		157
Security Basics		157
Object Ownership		158
OS/400 Authorities		159
Public Authority		163
Default Public Authority for New Objects Created with an OS/400 CrtXxx Command		163
Default Public Authority for New Objects Created with an SQL Create Statement		164
SQL/400 Privileges		164
Accessing the Main Database Objects		168
Collections		168
Tables and Views		168
Distinct Types		168
Stored Procedures, User-Defined Functions, and Packages		169
Programs and Service Programs		169
Column-Level Privileges		169
The Grant and Revoke Statements		169
Granting Table and View Privileges		170
Revoking Table and View Privileges		171
Granting and Revoking Privileges on Other Database Objects		172
Group Profiles		173
Authorization Lists		175
Program Adopted Authority		177
Database Security Principles and Guidelines		180
Chapter Summary		181
Key Terms		182
Exercises		182
*Part II: SQL Programming*
Chapter 10 Static and Dynamic Embedded Statements		185
Chapter Overview		185
Introduction to Embedded SQL		185
Embedded SQL Error Handling		192
Static Statements		194
Static Select Into Statement		195
Other Static DML Statements		196
Dynamic Embedded Statements		198
Coding Suggestions		203
Chapter Summary		204
Key Terms		204
Exercises		205
Chapter 11 SQL Cursors		207
Chapter Overview		207
Basic Elements of SQL Cursors		207
Opening and Closing the Cursor		211
The Fetch Statement		211
Reading by Key Value		213
The Optimize Clause		214
Scrollable Cursors		214
Positioned Update and Delete Statements		215
Dynamic Cursors		217
Coding Suggestions		218
Chapter Summary		219
Key Terms		219
Exercises		219
Chapter 12 The Create SQL Program Commands		221
Chapter Overview		221
Creating SQL/400 Programs		221
Specifying Additional HLL Program Creation Parameters		224
Specifying SQL Character String Delimiters for Cobol Programs		225
The SQL/400 Translation Process		225
Coding Suggestions		227
Chapter Summary		228
Key Terms		228
Exercises		228
Chapter 13 Additional SQL/400 Programming Techniques		229
Chapter Overview		229
The Include Statement		229
The SQL Communication Area		230
Host Structures		233
Host Structure Arrays		236
Object and Row Locks and the Lock Table Statement		239
Object Locks		240
Types of Object Locks		240
The Lock Table Statement		243
Row Locks		245
Transaction Integrity and the Commit and Rollback Statements		245
Determining Which Commitment Control Environment Is Used		247
The Commit and Rollback Statements		248
Isolation Levels		250
None		250
Read Uncommitted, Read Write		250
Read Committed		250
Repeatable Read		250
Serializable		250
Row Locking with Commitment Control		251
Stored Procedures		253
SQL Procedure Language		257
SPL Language Features		259
User-Defined Functions		266
A Guide to SQL/400 Naming		268
SQL/400 Object Names		268
Qualified Names and Uniqueness		270
Using Qualified or Unqualified Names		270
Choosing the System or SQL Naming Option		271
Specifying a Default Collection		272
Specifying the SQL Path		273
Running SQL Programs Under the Correct User Profile		275
Using an SQL Options Source Member		275
Additional Considerations		276
Chapter Summary		276
Key Terms		278
Exercises		278
Chapter 14 Creating an RPG IV Program to Update the Customer Table		281
Chapter Overview		281
The UpdCust Program		281
Coding Suggestions		291
Exercises		291
Chapter 15 Creating a Cobol Program to Update the Customer Table		293
Chapter Overview		293
The UpdCust Program		293
Coding Suggestions		304
Exercises		305
Chapter 16 Additional Database Topics		307
Chapter Overview		307
File Overrides		307
Override Scope		309
Merging Overrides		310
Deleting and Displaying Overrides		311
Introduction to Database Triggers		311
Coding a Trigger Program		313
Soft Coding the Trigger Buffer		320
Considerations for Using Trigger Programs		324
Interaction of Triggers with Constraints		325
Interaction of Insert Triggers with Constraints		325
Interaction of Update Triggers with Constraints		326
Interaction of Delete Triggers with Constraints		327
Distributed Database and DRDA		329
Accessing Remote Objects with SQL		329
ODBC		331
JDBC		333
Net.Data		334
Coding Suggestions		334
Chapter Summary		334
Key Terms		336
Exercises		336
*Part III: Database Modeling and Design*
Chapter 17 Introduction to Data Modeling and Database Design		337
Chapter Overview		337
The Importance of Modeling and Design		337
Logical Data Modeling		339
Physical Database Design		340
Relational Concepts As the Foundation		342
Chapter Summary		343
Key Terms		344
Exercises		344
Chapter 18 The Relational Database Model		345
Chapter Overview		345
Background: Database Management Systems		345
Conventional File System vs. DBMS		346
Database Models		349
Relational Model: Data Structure		349
Normal Forms		353
Data Integrity		358
Data Manipulation		359
Chapter Summary		366
Key Terms		367
Exercises		368
Chapter 19 Logical Data Modeling		371
Chapter Overview		371
Introduction		371
Step 1. Establish a Naming Standard and a Data Dictionary		372
Step 2. Record End Users? Views and Identify Entity Types		373
Step 3. Determine the Most Important Entity Properties		374
Step 4. Determine the Primary Key for Each Entity Type		375
Step 5. Determine the Relationships Between Entity Types		376
Step 6. Determine the Foreign Key Rules for Each Relationship		377
Step 7. Determine Additional Integrity Constraints		378
Step 8. Determine Security Rules		380
Step 9. Integrate Multiple Users? Views into an Overall Schema		381
Step 10. Eliminate Redundancy in Schema Base Tables		382
Step 11. Determine Subschema Views		382
Chapter Summary		383
Key Terms		384
Exercises		384
Chapter 20 Entity Relationship Diagramming		385
Chapter Overview		385
Introduction		385
Basic ERD Concepts and Symbols		385
Adding Properties to ERDs		388
Multivalued Properties		389
Representing Associations		390
Exclusive Relationships		391
Entity Subtypes		392
When to Use ERDs		393
Chapter Summary		395
Key Terms		395
Exercises		395
Chapter 21 Physical Database Design		397
Chapter Overview		397
Introduction		397
Step 1. Specify an SQL Table for Each Entity Type		399
Step 2. Specify Primary, Candidate, and Foreign Key Implementation		402
Step 3. Specify Domain Implementation		403
Step 4. Specify Implementation of Other Integrity Rules		404
Step 5. Specify View Implementation		406
Step 6. Specify Security Implementation		406
Step 7. Specify Additional Indexes for Performance		408
Step 8. Introduce Controlled Redundancy		409
Step 9. Merge Base Tables		410
Step 10. Adjust the Database Design to Anticipate Changes		411
Chapter Summary		412
Key Terms		412
Exercises		412
Appendix A SQL/400 Statement Syntax		415
Appendix B CL Command Descriptions for SQL and Related Objects
Appendix C Using Programming Development Manager (PDM)
Appendix D Using Source Entry Utility (SEU)
Appendix E Annotated Bibliography
Glossary
Index

L'auteur - Paul Conte

Paul Conte is the president of Picante Software and co-author, with Michael Otey, of SQL Server 7 Developer's Guide. He is also a co-author of SQU400 Developer's Guide from 29`" Street Press and author of several other computer books.

Caractéristiques techniques

	PAPIER
Éditeur(s)	29th Street Press
Auteur(s)	Paul Conte, Mike Cravitz
Parution	01/12/2000
Nb. de pages	508
Format	21,3 x 25,4
Couverture	Broché
Poids	1125g
Intérieur	Noir et Blanc
EAN13	9781882419708

Avantages Eyrolles.com

Livraison à partir de 0,01 € en France métropolitaine

Paiement en ligne SÉCURISÉ

Livraison dans le monde

Retour sous 15 jours

+ d'un million et demi de livres disponibles

SQL/400 Developer's Guide

Résumé

L'auteur - Paul Conte

Caractéristiques techniques

Consultez aussi