Why SQL? Why Now?

January 14, 2004 Raymond Everhart

SQL has been available on the AS/400 and iSeries for years. When SQL was first introduced, it was not widely embraced, because of limitations and performance concerns. Since then, IBM has improved SQL on the AS/400 and iSeries so dramatically that in some cases it outperforms “native” RPG and COBOL record-level access. In spite of the many improvements, many programmers have not yet explored what SQL can do for them.

What about you? Can you be persuaded, in just a few short paragraphs, to use SQL? Let me start by saying that SQL is just another programming language. Like all programming languages, SQL has its share of strengths and weaknesses. SQL’s primary strength is its ability to combine and extract records from databases. Let’s examine three reasons for using SQL.

SQL LETS YOU DO MORE WITH LESS

SQL lets you extract, combine, manipulate, and organize data with less programming than traditional methods. The first way that this is accomplished is with built-in functions, or BIFs. SQL currently provides more built-in functions than RPG IV. These functions include advanced math operations like sine, cosine, and tangent. SQL has a set of aggregate, or summary, functions just like you’re used to in Query/400. SQL also has string and date/time built-in functions. And just in case you can’t find the function you need, you can add your own built-in functions using RPG or COBOL code. Just think of it: All of those calculations that you currently code in every sales analysis report could be coded as a reusable function in SQL. No more worrying if you remembered to change every report during your last upgrade. (For additional information about writing your own user-defined functions, see Michael Sansoterra’s article “Scribble on SQL’s Scratchpad.”)

SQL also allows files to be joined together in ways that aren’t easily accomplished in RPG or COBOL. Programmers have the ability to code inner joins, outer joins (left and right), and exception joins. An inner join is probably the most familiar type of join. Any record that exists in both files and meets the join criteria is included in the result. An outer join includes all records from one file and just the records that meet the join criteria from the other file. All fields that have no value, because there was no record found, are represented as nulls. An exception join includes any record from one file where the join conditions are not satisfied by any record in the other file. In addition, records can be selected based on the value of a summary function or lookup in another table.

SQL allows for more complex record sets to be created but requires less coding. Since SQL is a text-based programming language, the SQL statement can be constructed at runtime to give your programs amazing flexibility. Even the file and field names can be specified “ad hoc.” Imagine a subfile program that allows the user to select the file, the fields, and the order in which they will appear–all from within the same program that displays the data.

Lastly, SQL allows you to produce working code faster. Since SQL can be used interactively, you can develop and test your record-selection logic in one pass. You can modify the SQL statement and run it again until you get the results that you want. No more stepping through a debug session of your RPG or COBOL program only to realize that you aren’t even getting the records that you thought you would.

SQL IS A CROSS-PLATFORM STANDARD

Early in my career in IT, I made a choice to pursue programming in the midrange environment rather than in the PC world. I liked the idea that I would be one of a few who could work on these systems. The S/34 and the S/36 were basically islands unto themselves. However, the introduction and evolution of AS/400 and iSeries servers has opened the midrange platform to developers with backgrounds in other systems. SQL may be relatively new to the AS/400 and iSeries, but it has been in use on other hardware platforms for years. In most cases, the same SQL statement can be used on a PC, a Unix server, an iSeries, or a mainframe. The last time I checked, DDS and OPNQRYF were only in use on the iSeries. If you go one step further and add Java, you can now program on any platform. SQL is not just for DB2. SQL also works with Oracle, Microsoft SQL Server, Sybase, Microsoft Access, IBM Lotus Notes, and Informix, just to name a few. Lastly, SQL can be used from within most programming languages. Whether you code in Basic or Java, SQL is the method of choice for accessing data.

SQL MAKES YOU A MORE VALUABLE RESOURCE

It only makes sense that if you can do more, you’re more valuable. The employment situation in the midrange market is not what it was just a few years ago. As the iSeries gains more capabilities, more will be required of those who support it. The once-clear lines of separation between systems are beginning to disappear, and more skills will be required to support the retrieval of information that is contained in a distributed database. The IT marketplace and its tools will continue to change at a pace that is ever quickening. These tools let you define your database and the business rules that apply to the data. These tools can maximize developer productivity across many systems, and they generate SQL statements. As you evaluate what your future in IT will be like, it will most certainly include SQL. So why not learn it now?

HOW TO GET STARTED USING SQL

With all that said, what route should you take to get from here to there? I’m a big believer in on-the-job experience. If I can’t use it as a regular part of my job, or the job that I want to have, then I need to learn something else. You can go to a class, but unless you put what you’ve learned into practice immediately, you won’t get much more than a certificate to hang on your wall. What I propose is something simpler. Here are 12 easy steps to start using SQL.

Step 1: Learn a few different names.

In SQL, a field is called a column, a record is called a row, a file is called a table, and a library is called a schema or collection.

Step 2: Get some SQL manuals.

I prefer the IBM manuals because they are free, but there are many good books out there about SQL. Keep in mind that SQL is a programming language, and therefore the examples that you see reflect the style of the author and may not necessarily be the way that you would have accomplished the same results. Experiment with the language until you feel comfortable with its nuances.

Here are the books that I have downloaded and keep nearby for quick reference. (Please note that the links require Adobe Acrobat reader to be installed on your desktop.)

• DB2 Universal Database for iSeries SQL Reference provides information about DB2 UDB for iSeries statements and their parameters. It also includes an appendix describing the SQL communications area (SQLCA) and SQL description area (SQLDA).
  
• SQL Programming Concepts provides an overview of how to design, write, run, and test DB2 UDB for iSeries statements. It also describes interactive SQL.
  
• SQL Programming with Host Languages provides examples of how to write SQL statements in COBOL, ILE COBOL/400, ILE RPG/400, ILE C/400, and PL/I programs.
  
• SQL Messages and Codes provides information about SQL messages and codes, including SQLSTATEs, SQLCODEs, and Message IDs.

Step 3: Stop using Query/400.

Instead of STRQRY, try STRSQL. If you can use Query/400, you can use SQL. It will take a little longer at first, but hang in there. A little time now will pay off big later. The most frequently used command in SQL is SELECT. If you are using the interactive SQL editor (STRSQL), the SELECT command will return the data that you specify to the screen by default. Use it every time you would normally use Query/400 to browse a table. To display all columns and all rows in a table, type this:

SELECT * from YourLib/YourFile

Yes, it’s that simple. There is even a prompt function available by pressing the F4 key that will step you through selecting a library, a table, and the columns that are available.

Step 4: Add the WHERE clause to a SELECT statement.

The WHERE clause lets you determine which rows will be returned in your record set.

Select * from YourLib/YourFile
WHERE FieldName = Value

Step 5: Add the ORDER BY clause.

The ORDER BY clause lets you sort your output by any field that you specify.

Select * from YourLib/YourFile
WHERE FieldName = Value
ORDER BY FieldName

Step 6: Add the GROUP BY clause.

The GROUP BY clause is used when you want to return a subtotal row. GROUP BY is the equivalent of a level break. The addition of this clause to your statement requires some changes to the columns selected by the SELECT clause. Each field selected must either be listed in the GROUP BY clause or be part of an aggregate function like sum or count.

Select System_Column_Name, Count(*) From Syscolumns
Where    System_Column_Name like 'SRC%' and
         System_Table_Schema = 'QGPL'
Group By System_Column_Name

The example above will return the field name and the number of times it is used in the tables located in the QGPL library where the field name begins with SRC.

Step 7: Add the JOIN clause to get data from other tables.

The simplest type of join to understand is the inner join. The inner join returns a row for each time a row from each table meets the join criteria specified. One example of this type of join is to select an order header row and all of the line items for the order. An inner join looks like this:

SELECT * from FileLib/OrdHdr JOIN FileLib/OrdDtl 
   on OrdHdr.OrderNo = OrdDtl .OrderNo

Notice that the field name OrderNo is qualified with the table OrdHdr and OrdDtl. If any field has the same name in multiple tables, it must be qualified whenever it is used. When you use the F4 key to select field names, they are automatically inserted with the table name qualifier. If you would rather not type the entire table name, you can assign your own qualifier:

SELECT * from FileLib/OrdHdr H JOIN FileLib/OrdDtl D 
   on H.OrderNo = D.OrderNo

In the example above, the qualifier H is used for the order header table and the qualifier D is used for the order detail table. Just as in Query/400, joining tables together is the hardest part of reliably extracting the data. If your SQL statement is experiencing poor performance, the way that the tables are joined together is the most likely reason. Just like Query/400, it is possible to design an SQL statement that is inefficient and resource intensive. The good news is that the problem can be quickly and easily identified and corrected. However, SQL performance tuning is a topic of its own and will not be covered in this article.

Step 8: Add built-in functions.

There are built-in functions for just about every operation imaginable. The best way to learn them is to look through the manual whenever you want to try something new. The basic format of built-in functions is the same. The built-in function’s name is followed by a parenthesis, commas separate the operands, and the function is terminated with a close parenthesis. The substring function, one of the more commonly used built-in functions, as shown below, will return the first five characters from the Custname column.

Select Substr(Custname,1,5) from FileLib/CustFile

Step 9: Start using the UPDATE clause.

The UPDATE clause lets you change every row in your record set. This command must be used with great caution. It is highly recommended that you try this command for the first time in your development environment only. The UPDATE clause looks like this:

UPDATE FileLib/CustFile Set SalesCode = 'A'

The UPDATE clause alone will affect every row in your table. When you add a WHERE clause to your UPDATE clause, you limit the changes to just the rows in your record set. The easiest way to check an UPDATE statement before running it is to code a SELECT statement first (line 1), verify that you’ve selected the right rows, then copy the WHERE clause to the UPDATE clause (line 2).

Line 1. SELECT SalesCode from FileLib/CustFile 
   Where LastSale < '1-1-2003'

Line 2. UPDATE FileLib/CustFile Set SalesCode = 'A' 
   Where LastSale < '1-1-2003'

Step 10: Start using the DELETE clause.

The DELETE clause lets you delete every row in your record set. This command must be used with even greater caution. Once again, verify your row selection before you run the DELETE statement.

Step 11: Start using the INSERT clause.

The INSERT clause is used to add rows to a table. The column values can be constants or the results of a SELECT clause. The INSERT clause can populate all columns or just the ones named in the column list. The INSERT statement below adds one row to the table and sets the value of the Color column equal to “Blue.”

INSERT INTO FileLib/WidgetColors (Color) VALUES ('Blue')

The INSERT Statement below uses a SubSelect to retrieve a set of rows to be inserted in the WidgetColors table. The statement inserts a row for each row returned, placing the value of the column BaseColor into the Color column when the Type column is equal to Primary.

INSERT INTO FileLib/WidgetColors (Color) 
SELECT BaseColor from ColorTable WHERE Type = 'Primer'

Based on the sample data shown below, two rows would be inserted into the WidgetColors table.

Step 12: Start using the CREATE clause.

The CREATE clause lets you create schemas, tables, indexes, constraints, stored procedures, and views.

Before SQL, database creation on the AS/400 and iSeries was always accomplished with a data description specification (DDS). This meant that developers on other platforms were limited in what they could do on an iSeries until they learned DDS. Likewise, an iSeries developer had no idea how to create a table in an Oracle database. Using SQL to create and maintain database objects lets the developer write platform-independent code. Creating a table is as simple as this:

Create Table FileLib/AnyTable
        (Empno INTEGER,
         Name CHAR (45),
         Dept CHAR (10),
         Salary DEC (11, 2))

Creating an index using the Empno column in the table created above is accomplished with the following statement:

CREATE INDEX FileLib/AnyTableL1 On 
   FileLib/AnyTable (EMPNO)

To delete the objects that we just created, type Drop Index FileLib/AnytableL1 and Drop Table FileLib/AnyTable.

DON’T STOP NOW, YOU’RE JUST GETTING STARTED!

Learning SQL is an ongoing process. So give yourself plenty of time to explore all that it is capable of. Once you can use SQL interactively, you are ready to begin using it inside your programs. Embedding SQL inside your programs is an easy way to have the best of all worlds. You can create, populate, and delete tables from within your application without having to compile DDS. Your SQL statements can even be created dynamically while your program is executing. When you are comfortable using SQL, you can take the next step and start using WebSphere Development Studio client (WDSc) to develop and test your SQL statements. Using WDSc and SQL will open a whole new set of opportunities to you by allowing you to develop, not just on the iSeries but on other platforms and databases as well.

Raymond Everhart has over 18 years of IT experience with IBM midrange computers. He was a consultant, programmer, and educator for an IBM premier business partner in Upstate New York for 10 years, before relocating to the Dallas/Ft. Worth area in 1998. Since then, Raymond has been helping local companies implement new technologies with his consulting, programming and mentoring services. E-mail: reverhart@itjungle.com.