![SQL/400 Developer's Guide](http://servimg.eyrolles.com/static/media/9708/9781882419708_internet_w290.jpg)
Résumé
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
Consultez aussi
- Les meilleures ventes en Graphisme & Photo
- Les meilleures ventes en Informatique
- Les meilleures ventes en Construction
- Les meilleures ventes en Entreprise & Droit
- Les meilleures ventes en Sciences
- Les meilleures ventes en Littérature
- Les meilleures ventes en Arts & Loisirs
- Les meilleures ventes en Vie pratique
- Les meilleures ventes en Voyage et Tourisme
- Les meilleures ventes en BD et Jeunesse