SQL for eServer i5 and iSeries - Softcover

Forsythe, Kevin

Softcover

ISBN 10: 1583470484 ISBN 13: 9781583470480

Verlag: MC Press, 2004

Inhaltsangabe

This examination of how SQL functions on the iSeries considers at all possible environments, including the traditional 5250 green screen applications, the SQL scripting tool within iSeries Navigator, and Visual Basic. Database management from creation to deletion is covered in detail. Programmers will learn how to implement referential constraints and primary and unique key indexes, check constraints, and journal physical files. How to extend a database with user-defined functions and procedures and how the new GUI development tools provide simplified methods for running, debugging, and tuning SQL statements are also explained.

Über diesen Titel

Über die Autorin bzw. den Autor

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.

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

SQL for eServer i5 and iSeries

By Kevin Forsythe

MC Press

Copyright © 2008 MC Press Online, LP
All rights reserved.

ISBN: 978-1-58347-048-0

Contents

Title Page, 
Copyright Page, 
Acknowledgments, 
Preface, 
1 - Interactive SQLs, 
2 - SQL in iSeries Navigator, 
3 - Embedded SQL, 
4 - Advanced SQL Examples, 
5 - Optimizing SQL, 
6 - Creating Database Objects, 
7 - Creating Functions, 
8 - Creating Stored Procedures, 
9 - Creating SQL-based Tools, 
10 - Using SQL from within Microsoft Office, 
A - SQL Basics for Beginners, 

CHAPTER 1

Interactive SQLs

SQL is a common database language available on most if not all major computing platforms. However, it has only recently begun receiving widespread use on the iSeries platform. Legacy applications typically contain databases that were defined with Data Definition Specifications (DDS) rather than SQL. Using SQL to interact with these legacy databases on the iSeries involves some unique concerns.

First, those users new to SQL must familiarize themselves with the available tools,such as STRSQL and iSeries Navigator. Then, the language itself must be learned. The single most important command in SQL is the SELECT statement. This statement performs the majority of the data retrieval work, and learning its syntax makes learning other statements much easier.

Beyond the basic syntax of the SELECT statement, interacting with legacy databases often involves the unpleasant task of assembling a date from one or more columns that are simply stored as either numeric or character data. Or even worse, working with database designs that are older than DB2. Such databases often contain data in a packed format that must be manually unpacked to use.

Examples of Basic SELECTs

The SELECT statement is the backbone of SQL. It retrieves one or more columns of data from one or more files in the database. "File" is the traditional iSeries term for what is often called a "Table" on other systems. The DB2 UDB database is integrated into the OS/400 operating system of the iSeries. This integration offers tremendous advantages in stability, security, and performance over systems in which the database is simply an application that runs on the operating system. Learning to extract data from the database using SELECT will improve productivity; these same SELECT statements can be combined with other SQL statements to perform even more powerful functions. We review database updates later in this chapter. For now, let us focus on the SELECT statement.

Tool of Choice

Before we can run the SELECT statement, we need to discuss the tools that can be used to execute SQL statements on the iSeries. The STRSQL command is a popular choice for running interactive SQL statements on the iSeries, but it requires the installation of the DB2 Query Mgr and SQL DevKit. This licensed program also provides support for embedding SQL within programs written in RPGIV and other languages. In recent releases, IBM has updated the base operating system to include the capability to run SQL statements. For example, the RUNSQLSTM command can be used to execute SQL statements that have been prewritten in source members. This command has been around a while but, prior to V5R1, it was not in the base operating system. iSeries Navigator now includes a GUI SQL script processor, which provides a free interactive SQL tool with excellent features. We look at all these tools and more in this book, but for now, let's get started on the traditional "green screen" or 5250 data stream environment and execute the STRSQL command. (If you do not have the DB2 Query Mgr and SQL DevKit, use the Run SQL Script program in iSeries Navigator instead.)

Sample Database

The database is the foundation upon which all SQL statements are built. A poorly designed database will make all your SQL efforts more complicated. You can do things to overcome those complications. Some are discussed later in this section, and others are covered in the remaining chapters of this book. For the purposes of this book, the database is kept as simple as possible.

Before you can successfully use SQL, you must first understand the library and file structure of your applications. In this book, all the sample files exist in the same library. Production applications, however, often utilize files in multiple libraries. As long as all the files referenced exist once and only once within the library list being used when the SQL statements are executed, there is no need to qualify the file names with their associated libraries.

In Figure 1.1, CUSTFLAT illustrates a simple customer master file that is typical of applications developed in the SYS/36 environment on the iSeries or on older systems. It is not an externally described database file, but rather a program-described "flat file." On the iSeries, even program-described files have rudimentary field definitions.

The field definitions for CUSTFLAT are shown in Figure 1.2. Such field definitions can be used to extract data from the file with a little extra effort or — in some cases — a lot of extra effort!

The data in this type of file is stored in long strings of text that the programmer must know how to map or parse into the proper fields (see Figure 1.3). Because no system object contains information on how to map the fields, accurate documentation is critical for working with program described files.

Figure 1.4 shows the CUSTMAST file, which is an externally described file containing the same information as CUSTFLAT. The field layout of CUSTMAST can be found by running the command:

[ILLUSTRATION OMITTED]

The standardized field definitions for the file are displayed in Figure 1.5 and make using this file within SQL statements far easier than program-described files.

INVFILE is an externally described database file (Figure 1.6) that contains information about inventory items that are stored in a warehouse. Its field layout is illustrated in Figure 1.7 and can be found by running the command:

[ILLUSTRATION OMITTED]

These files are somewhat simplified compared to typical production database files, but for the purpose of illustrating the techniques and concepts involved in using SQL on the iSeries, they are well designed.

Getting Started with STRSQL

When STRSQL executes, it displays the SQL command interface shown in Figure 1.8

Use this interface to type in an SQL statement, press Enter, and the results will be displayed. To get started let's run a simple statement:

SELECT * FROM KPFSQL/CUSTMAST

This statement displays every column and every row in CUSTMAST, as shown in Figure 1.9. Use the "Position to line" and "Shift to column" fields on the display to change the rows and columns displayed. When finished, press Enter or F12 to return to the previous screen.

You may not wish to see every record in the table. The WHERE clause is used to select specific records. One example might be to filter out only the records for customers in Ohio:

SELECT * FROM KPFLIB/CUSTMAST WHERE CUSTST = 'OH'

Figure 1.10 shows the result of the select. All the columns in the table are included in the result, just as in the previous example, but this time only two records are included.

In the previous two examples, we used qualified file names. If the library KPFLIB exists in our library list, and the CUSTMAST file only exists in one...

Details zur Ausgabe

• Verlag: MC Press
• Erscheinungsdatum: 2004
• Sprache: Englisch
• ISBN 10: 1583470484
• ISBN 13: 9781583470480
• Einband: Taschenbuch
• Anzahl der Seiten: 368
• Kontakt zum Hersteller: Nicht verfügbar
• Verantwortliche Person: Nicht verfügbar