Getting the most benefit out DB2 for i requires keeping the concept of "set at time" in mind and making use of all the latest features and functions.

By Mike Cain

In Dan Cruikshank's August 2008 article "Working with Data Sets," Dan illuminated the power of SQL "set at a time" processing, and he used some of the new DB2 for i 6.1 OLAP functions to do it. In this article, I'll expand on this concept and demonstrate some additional uses for OLAP functions, as well as reiterate the reasons that SQL can be so powerful for the data-centric programmer. We'll also take a look at some of the performance considerations for more-complex SQL requests.

Why are the examples for SQL always so lame? It's time to see what you can do with SQL on a real-world database.

By Joe Pluta

Seriously, how often do you change the price in an entire price file by 15 percent? I don't know of a single situation in my career where that has happened. Yet that's the same lame example we see in every "SQL for business" book. Yes, SQL is great for handling sets of data, but more often than not, in the real world you have to do some analysis, some aggregation, some extraction, and then finally some manipulation. The good news is that SQL gives you lots of tools to do just that. The better news is that this article will show you some practical examples on how to use those tools.

Inner joins, outer joins, exception joins, cross joins... Do you know how to take full advantage of all the types of joins?

By Kevin Forsythe

Editor's Note: In this excerpt from SQL for eServer i5 and iSeries, author Kevin Forsythe explains how to use the various joins available in SQL.

In SQL, the basic SELECT statement displays one or more columns from a single file. When columns must be merged from two or more files, a join should be used. There are two ways to accomplish this. A join can be written either with or without the JOIN keyword. Creating simple joins is easy enough, but as they get more complex, using the JOIN keyword becomes more attractive.  

There's no reason not to forge ahead with new techniques such as SQL as long as you have the right tools in place.

By Ray Bernardi

Structured Query Language (SQL) is a keyword-oriented language, which makes it easy to learn and use, and it's an excellent application development tool. You can define data, manipulate it, and query it to get fast results.

SQL is also an industry standard, so learning it on the IBM i means you've learned it for other platforms as well. And that means you can increase your application's portability, yet keep the way you access the database standard. That simplifies maintenance of your systems, and it makes development easier, which in turn leads to productivity. SQL is required if you want to move data between platforms effectively and efficiently. 

Let's look back at the various ways we processed data sets using RPG and compare that to the new V6R1 SQL grouping sets capability.

By Daniel Cruikshank

Like many midrangers, I began my career as a computer operator, loading card decks into the Multi-Function Card Mangler (MFCM) attached to an IBM System 3 Model 10 utilizing RPG/2. It was my first job after completing school, and I was eager to become a programmer. In school, we were taught Basic Assembler Language (BAL), and the RPG language was merely a footnote. Each day, I pestered management for a chance to write a program. In my spare time, I read RPG manuals.

Format and name derived columns when you use SQL to do ad hoc analysis of information in an SQL table.

By Skip Marchesani

I frequently use Interactive SQL or Run SQL Scripts in Navigator to do ad hoc analysis of information in an SQL table, which often results in one or more new columns being derived or created based on existing columns. When I first began doing these types of queries, I was frustrated with the way SQL identified and formatted a derived column in the result set. 

Learn how to use host variables to get the most from your embedded SQL statements.

by Adam Glauser

In its simplest form, embedded SQL in RPGLE is not much different from the type of SQL you can run using the RUNSQLSTM command. You can use DDL to create and drop tables; you can even insert and delete rows. However, you will soon find that you will want to have the SQL statement affect different rows, depending on user input or other criteria. That's where host variables can help.