David M. Uptmor


 View book pages:
 Buy this book:
  MC Press Online
  




SQL for eServer i5 and iSeries

by Kevin Forsythe

Bibliographic information

TitleSQL for eServer i5 and iSeries
AuthorKevin Forsythe
PublisherMC Press Online
Publication DateOctober 2004
SubjectDatabase: SQL
Pages369


Description 

In this book, you will learn about Structured Query Language (SQL), but with an IBM eServer i5/iSeries twist. The future seems clear on this point: SQL is the direction of the future for IBM's Universal Database. While it is true that the majority of existing databases on the i5 and iSeries were created with DDS, it's still undeniable that DDS will slowly fade from use, much like the RPG program cycle.

With a practical "how-to" style, SQL for eServer i5 and iSeries first introduces you to the basics essential for any developer to build a solid foundation. Then the book examines--in detail--the many ways in which SQL functions on the iSeries. All possible environments are explored: the traditional 5250 green-screen applications, the SQL scripting tool within iSeries Navigator, and the methods of accessing iSeries databases from within Visual Basic.

Learn through real-life examples of using SQL to interact with DB2 databases. You'll learn the ins and outs of sub-selects, joins, unions, and views, as well as tips and techniques for interacting with legacy applications, and so much more. Though intended for programmers already familiar with SQL, beginners will appreciate the book's handy tutorial Appendix to get started with SQL basics.

After reading this book, you'll see that the i5/iSeries platform is far more than the green-screen application server it's often perceived as and that it is indeed one of the finest systems available for use as a business server today. Make your database access cutting-edge with SQL for eServer i5 and iSeries.

In this book you will learn to:
  • Incorporate SQL into existing iSeries business and client-side applications
  • Embed SQL within RPG programs, merging the flexibility of SQL with the power of RPG
  • Implement referential constraints, primary and unique key indexes, and check constraints
  • Journal physical files, and find out why this task is so important
  • Extend your database with user-defined functions and procedures
  • Use new GUI development tools for running, debugging, and tuning SQL statements




About the Author 

Kevin Forsythe ---

Kevin Forsythe has over 19 years of experience on the iSeries and other platforms as programmer, analyst, consultant, and instructor. He blends a wide range of experiences with a passion for teaching and is the author of numerous articles. Kevin has developed AS/Credentials Courses for Interactive SQL and Embedded SQL. He is a frequent and award-winning speaker at COMMON conferences and other iSeries user conferences.




Contents 

CONTENTS
Acknowledgments
Preface

Chapter 1: Interactive SQLs
Examples of Basic SELECTs
Getting Started with STRSQL
Common Problems
Tricks in the Interactive Environment
Summary

Chapter 2: SQL in iSeries Navigator
Installing the Database Component of iSeries Navigator
Getting Started with the SQL Scripting Tool
Using Visual Explain

Chapter 3: Embedded SQL
Why Embed SQL within RPG IV?
How to Embed SQL within RPG IV
Prepared SELECT Statements
Updating the Data in the Result Set
Compile Options
Summary

Chapter 4: Advanced SQL Examples
Joins
Handling Multimember Files with Aliases
Using Sub-SELECTs
Using CASE to Handle Tricky Situations
Using Commitment Control
Summary

Chapter 5: Optimizing SQL
Using Debug to Optimize SQL Statements
A Bit about Indexes
Query Attributes File QAQQINI
Optimizing SELECTs
Views on Views
SETOBJACC and Loading Files into Memory
Traditional or Legacy Files and Index Issues
Database Statistics
Parallel Processing
GUI Tools
SQL Performance Monitor
Optimize the Machine or Optimize the People?
Suggestions
Summary

Chapter 6: Creating Database Objects
Schemas, Collections, Libraries, and Databases
Constraints
Working with Varchars
Working with Datalinks
Working with BLOBs
Working with User-Defined Data Types
Views
Aliases and Multimember Processing
Catalogs
Journaling
Altering Your Database
Adding Comments to Your Database
Build Procedures
Summary

Chapter 7: Creating Functions
Creating Functions with RPGLE
Creating Functions with SQL
Creating Sourced Functions
Summary

Chapter 8: Creating Stored Procedures
Creating a Stored Procedure to Post Inventory Transactions
Processing a Transaction File
Scheduling with Procedures
Archiving Data with Procedures
Calling a CL Program as a Stored Procedure
Archiving Data
Calling Stored Procedures with Embedded SQL
Summary

Chapter 9: Creating SQL-based Tools
Using QM Query
Using RPGLE to Create SQL-based Tools
Summary

Chapter 10: Using SQL from within Microsoft Office
How to Access iSeries Data from a PC
How to Access iSeries Data from Excel
Using Forms with ADO
Uploading Data to the iSeries
Integrating iSeries Data into Word Using ADO
Calling a Stored Procedure through ADO
Summary

Appendix: SQL Basics for Beginners
SELECT Statement
Field List
FROM Clause
WHERE Clause
ORDER BY Clause
Column Functions
Summary Functions
GROUP BY Clause
HAVING Clause
INSERT Statement
UPDATE Statement
DELETE Statement



Other related titles: MC Press Online

Database Design and SQL for DB2Database Design and SQL for DB2
Free-Format RPG IV, 1st EditionFree-Format RPG IV, 1st Edition
Mastering IBM iMastering IBM i
Modern RPG IV Language, TheModern RPG IV Language, The
Programmer's Guide to iSeries NavigatorProgrammer's Guide to iSeries Navigator
Programming in RPG IVProgramming in RPG IV
SQL Built-In Functions and Stored ProceduresSQL Built-In Functions and Stored Procedures


 Other related titles:
Database Design and SQL for DB2Database Design and SQL for DB2
Free-Format RPG IV, 1st EditionFree-Format RPG IV, 1st Edition
Mastering IBM iMastering IBM i
Modern RPG IV Language, TheModern RPG IV Language, The
Programmer's Guide to iSeries NavigatorProgrammer's Guide to iSeries Navigator
Programming in RPG IVProgramming in RPG IV
SQL Built-In Functions and Stored ProceduresSQL Built-In Functions and Stored Procedures

 Already viewed books:
SQL for eServer i5 and iSeriesSQL for eServer i5 and iSeries