Frank G. Soltis, IBM AS/400 Chief Scientist and Prof. Computer Engineering, Univ. of Minnesota
A practical database design and SQL programming textbook specifically for AS/400 application developers.


Kent Milligan, DB2 & BI Solutions, AS/400 Partner World for Developers
This book is where you should start learning SQL on the AS/400.


Sharon L. Hoffman, Senior Analyst, TechReflections
Both newcomers to the AS/400 and experienced AS/400 programmers will find SQL/400 Developer's Guide an excellent resource.


Bernadette Cinkoske, Assistant Professor, Ivy Tech State College
A comprehensive, indispensable resource for students, programmers, and database administrators who need to learn about the AS/400 database.


Book Description
Paul Conte and Mike Cravitz have written the must-have guide to SQL for AS/400 application developers. SQL/400 Developer's Guide is an authoritative book that 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.


From the Author
Structured Query Language/400 is IBM’s strategic language for defining and manipulating DB2 UDB for AS/400 databases. IBM has recently stated that the proprietary Data Description Specifications and built-in RPG and COBOL I/O extensions are not being enhanced to support new database features. That means if you plan to take full advantage of the AS/400 integrated database, you need to know SQL. And, if you plan to use Java, you need to know SQL because JDBC (Java Database Connectivity) uses SQL to access the AS/400 database. Finally, if you plan to keep your application programming skills current, you need to know SQL because it’s the database language used on all major platforms. This book provides a comprehensive explanation of SQL/400 to students and practicing programmers. Once you master the topics covered in SQL/400 Developer’s Guide, you’ll be able to design and create professional level databases for real applications. This book also provides developers wi! th extensive advice on database design, as well as programming. We cover SQL data definition language (DDL) and data manipulation (DML) in detail. But we go beyond that to explain essential areas such as security, transaction integrity (commitment control), object and row locking, stored procedures, and trigger programs. The book includes numerous examples, tables, appendices, and detailed technical explanations so in most cases, you won’t need any AS/400 manual to create, manage, and use UDB/400 tables and other objects. This edition is based on Version 4 Release 5 of UDB/400, RPG IV, and ILE COBOL/400. This is an ideal book for programmers coming from other database platforms, such as Oracle, Microsoft’s SQL Server 2000, or other versions of DB2 UDB (e.g., on IBM mainframes). If that’s your situation, you should be able to blast through most of the chapters and get a very rapid start with UDB/400.


From the Inside Flap
SQL/400 Developer’s Guide provides start-to-finish coverage of SQL/400, IBM's strategic language for the AS/400's integrated database. This essential text provides comprehensive explanations and numerous examples to help you: Create new databases, tables, views, and indexes with SQL/400. Define primary key, unique, foreign key, and check constraints to enforce data integrity. Master the Select, Update, Insert, and Delete statements from an introductory level to complex queries and transactions. Use advanced SQL features such as user-defined data types, user-defined functions, and stored procedures to speed application development. Protect transaction integrity using DB2 UDB/400 commitment control. Exploit SQL/400 extensions to increase database performance. Implement applications following "best practices" for efficiency and maintainability. Code easy-to-maintain trigger programs. Enforce tight, but flexible, security to protect data. Use Distributed Relational Database Architecture to retrieve and update data on distributed AS/400s. Model and design databases using entity relationship diagrams. With this book, you’ll be able to design and create professional-level databases for production applications. Paul Conte and Mike Cravitz are leading DB2 UDB/400 authorities with extensive application development experience. They’ve joined forces to create a book that will help new, as well as experienced, AS/400 developers design and code with speed and confidence. Along with in-depth instruction and advice on implementation, the book includes an excellent introduction to practical database design techniques. You get a clear explanation of the relational database model, which underlies the whole DB2 UDB/400 architecture and the SQL language. On that foundation, the book explains a step-by-step method of modeling an organization’s database requirements and developing a design for the necessary database tables and views. This text is designed for professional application developers and college-level students who want to become developers. All the essential SQL/400 and related DB2 UDB/400 topics are covered from an introductory to an advanced level. Each chapter has numerous examples and exercises, including two complete RPG IV and ILE COBOL/400 sample programs that illustrate all the major programming techniques necessary for a production SQL/400 application. Extensive appendices provide quick references on SQL/400 statements and database-related Command Language commands. You’ll find SQL/400 Developer’s Guide immediately useful, whether you’re just beginning to learn SQL/400 or you’re an experienced developer. And you’ll turn to this book time and time again for quick answers to the best way to design and program AS/400 database applications.


About the Author
Paul Conte is president of PCES, an educational and consulting company in Eugene, Oregon. He's also a senior technical editor for iSeries NEWS magazine. Paul is a widely published expert on database systems, database design, software engineering, and programming languages. He's considered a leading authority on DB2 UDB for AS/400, and his articles have appeared in iSeries NEWS, DB2 Magazine, Database Programming & Design, and numerous other publications. He has authored or co-authored four other books, including Database Design and Programming for DB2/400 (29th Street Press, 1997) and SQL Server 2000 Developer's Guide (Osborne/McGraw-Hill, 2000). Paul has a B.A. in phychology from Georgia State University and an M.S. in computer science from the University of Oregon. Mike Cravitz is a technical editor for iSeries NEWS with more than 30 years of application development experience. A consultant, instructor; and highly regarded author on SQL, RPG, Cobol, and ILE topics, Mike was formerly a systems engineer with IBM marketing in support of the IBM midrange. He holds a B.A. in mathematics from California State University at Northridge and an M.A. in mathematics from the univeristy of California at Los Angeles. Mike is also the author if the book ILE by Example (29th Street Press, 2002).


Excerpted from SQL/400 Developer's Guide by Paul Conte, Mike Cravitz. Copyright © 2000. Reprinted by permission. All rights reserved
Introduction to UDB/400, the AS/400’s Integrated Database Chapter Overview: This chapter introduces the concept of a database and describes DB2 Universal Database for AS/400 (UDB/400), the database management system that runs on the AS/400. You’ll get a general idea of how the AS/400 and its operating system, OS/400, are architected. You’ll also get an overview of UDB/400 physical and logical files and how they are used with Structured Query Language (SQL).

FROM THE PUBLISHER

SQL/400 Developer's Guide provides start-to-finish coverage of SQL/400, IBM's strategic language for the AS/400's integrated database. This essential text provides comprehensive explanations and numerous examples to help you: Create new databases, tables, views, and indexes with SQL/400. Define primary key, unique, foreign key, and check constraints to enforce data integrity. Master the Select, Update, Insert, and Delete statements from an introductory level to complex queries and transactions. Use advanced SQL features such as user-defined data types, user-defined functions, and stored procedures to speed application development. Protect transaction integrity using DB2 UDB/400 commitment control. Exploit SQL/400 extensions to increase database performance. Implement applications following "best practices" for efficiency and maintainability. Code easy-to-maintain trigger programs. Enforce tight, but flexible, security to protect data. Use Distributed Relational Database Architecture to retrieve and update data on distributed AS/400s. Model and design databases using entity relationship diagrams. With this book, you'll be able to design and create professional-level databases for production applications. Paul Conte and Mike Cravitz are leading DB2 UDB/400 authorities with extensive application development experience. They've joined forces to create a book that will help new, as well as experienced, AS/400 developers design and code with speed and confidence. Along with in-depth instruction and advice on implementation, the book includes an excellent introduction to practical database design techniques. You get a clear explanation of the relational database model, which underlies the whole DB2 UDB/400 architecture and the SQL language. On that foundation, the book explains a step-by-step method of modeling an organization's database requirements and developing a design for the necessary database tables and views. This text is designed for professional application developers and college-level students who want to become developers. All the essential SQL/400 and related DB2 UDB/400 topics are covered from an introductory to an advanced level. Each chapter has numerous examples and exercises, including two complete RPG IV and ILE COBOL/400 sample programs that illustrate all the major programming techniques necessary for a production SQL/400 application. Extensive appendices provide quick references on SQL/400 statements and database-related Command Language commands. You'll find SQL/400 Developer's Guide immediately useful, whether you're just beginning to learn SQL/400 or you're an experienced developer. And you'll turn to this book time and time again for quick answers to the best way to design and program AS/400 database applications.

From the Authors: Structured Query Language/400 is IBM's strategic language for defining and manipulating DB2 UDB for AS/400 databases. IBM has recently stated that the proprietary Data Description Specifications and built-in RPG and COBOL I/O extensions are not being enhanced to support new database features. That means if you plan to take full advantage of the AS/400 integrated database, you need to know SQL. And, if you plan to use Java, you need to know SQL because JDBC (Java Database Connectivity) uses SQL to access the AS/400 database. Finally, if you plan to keep your application programming skills current, you need to know SQL because it's the database language used on all major platforms. This book provides a comprehensive explanation of SQL/400 to students and practicing programmers. Once you master the topics covered in SQL/400 Developer's Guide, you'll be able to design and create professional level databases for real applications. This book also provides developers with extensive advice on database design, as well as programming. We cover SQL data definition language (DDL) and data manipulation (DML) in detail. But we go beyond that to explain essential areas such as security, transaction integrity (commitment control), object and row locking, stored procedures, and trigger programs. The book includes numerous examples, tables, appendices, and detailed technical explanations so in most cases, you won't need any AS/400 manual to create, manage, and use UDB/400 tables and other objects. This edition is based on Version 4 Release 5 of UDB/400, RPG IV, and ILE COBOL/400. This is an ideal book for programmers coming from other database platforms, such as Oracle, Microsoft's SQL Server 2000, or other versions of DB2 UDB (e.g., on IBM mainframes). If that's your situation, you should be able to blast through most of the chapters and get a very rapid start with UDB/400.

SYNOPSIS

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.

ACCREDITATION

Paul Conte is president of PCES, an educational and consulting company in Eugene, Oregon. He's also a senior technical editor for NEWS/400 magazine. Paul is a widely published expert on database systems, database design, software engineering, and programming languages. He's considered a leading authority on DB2 UDB for AS/400, and his articles have appeared in NEWS/400, DB2 Magazine, Database Programming & Design, and numerous other publications. He has authored or co-authored four other books, including Database Design and Programming for DB2/400 (29th Street Press, 1997) and SQL Server 2000 Developer's Guide (Osborne/McGraw-Hill, 2000). Paul has a B.A. in psychology from Georgia State University and an M.S. in computer science from the University of Oregon.