Chapter 1. The Magic Words

IN THIS CHAPTER

• An Overview of SQL

• SQL Tools of the Trade

• Static SQL

• Dynamic SQL

• SQL Performance Factors

Once upon a time there was a kingdom called Userville. The people in the kingdom were impatient and wanted to know everything about everything ”they could never get enough information. Life was difficult and the people were unhappy because data was often lost, and even when it was available, it was often inaccurate and not easy to access.

The King decided to purchase DB2, an advanced tool for storing and retrieving data. With DB2 the Users could process their data and turn it into information. "This," he thought, "should keep the people happy. DB2 will solve all my problems." But he soon found out that special knowledge was necessary to make DB2 work its wonders. Nobody in Userville knew how to use it properly.

Luckily, a grand Wizard living in a nearby kingdom knew many mystical secrets for retrieving data. These secrets were a form of magic called SQL. The King of Userville summoned the Wizard, offering him many great treasures if only he would help the poor Users in Userville.

The Wizard soon arrived, determined to please . Armed with nothing more than SQL and a smile, the Wizard strode to the terminal and uttered the magic words:

 SELECT E.EMPNO, E.FIRSTNME, E.LASTNAME, D.DEPTNO, D.DEPTNAME FROM   DSN8810.DEPT  D,        DSN8810.EMP   E WHERE  E.WORKDEPT = D.DEPTNO; 

A crowd gathered and applauded as the desired information began pumping out of the terminal. "More, more," shouted the data-starved masses. The Wizard gazed into the screen, and with amazing speed effortlessly produced report after report. The King was overheard to say, "You know, this is just too good to be true!" Everybody was happy. The Users had their share of information, the King had a peaceful kingdom, and the Wizard had his treasures and the respect of the Users.

For many months, the Users were satisfied with the magic of the great Wizard. Then, one day, the Wizard disappeared in a jet to the West Coast for 130 grand a year ”and a bunch of stock options. The people of the kingdom began to worry. "How will we survive without the magic of the Wizard? Will we have to live, once again, without our precious information?" The Wizard's apprentice tried to silence the crowd by using his magic, but it wasn't the same. The information was still there, but it wasn't coming fast enough or as effortlessly. The apprentice was not yet as skilled as the great Wizard who had abandoned the kingdom. But, as luck would have it, one day he stumbled upon the great Wizard's diary. He quickly absorbed every page and soon was invoking the Wizard's magic words. And all was well again.

Well, life is not always that simple. Departing Wizards do not often leave behind documentation of their secrets. The first part of this book can be used as a "Wizard's diary" for efficient SQL. This chapter is an overview of SQL, not from a syntactic viewpoint, but from a functional viewpoint. This chapter is not intended to teach SQL, but to provide a framework for the advanced issues discussed in the remainder of this text. This framework delineates the differences between SQL and procedural languages and outlines the components and types of SQL. Chapters 2 through 10 delve into the performance and administrative issues surrounding the effective implementation of SQL for DB2.

So continue and take the next step toward becoming a DB2 Wizard