• Guru: SQL Facts Of UNION And ORDER BY

  April 10, 2017 Ted Holt

  UNION and ORDER BY are powerful SQL features, but put the two together and you may get some strange and frustrating error messages. Fortunately for us DB2 for i professionals, there are easy ways to make the two collaborate and cooperate. Today is a great day to be sure we understand them.

  We need some data for examples. For some of the queries, I use a customer master table (file) and a vendor master table. These are a company’s trading partners, so the two have many attributes in common, especially names and addresses. I also use two sales history tables, …

  Read more

• Conditional SQL I/O, Take 2

  March 20, 2017 Ted Holt

  Bob writes, “Hey, Ted! I hope you can teach an old dog a new trick. I am trying to replace the CHAIN operation with SQL. I chain once to a file to read a certain record. If that record is not found, I chain again to retrieve a default record. How can I make SQL do a second read to the same file?”

  This is not a hard thing to do, as SQL has no problem joining more than once to the same table. I’ll show you two methods to retrieve your data. The first method is the easier one, …

  Read more

• SQL And Database Shine As Next Tech Refresh Approaches

  February 20, 2017 Dan Burger

  Twelve inches of fresh snow has piled up on your driveway and all you have is a shovel. That’s minimal functionality and, depending on your fitness level, your snow removal performance may be less than desirable. At best, you’re on the low end of the functionality and performance continuum and you’re wishing for better.

  So it is with the DB2 for i database and the programmers who shovel data there. Any help moving data from where it is to where it’s needed will likely be appreciated. Ditto for accelerating application behavior and gaining application functionality.

  This brings us to the …

  Read more

• Guru: SQL PL Labels

  February 13, 2017 Ted Holt

  SQL PL, the powerful, easy-to-learn procedural database language that IBM ships with all versions of DB2, allows you to identify any executable statement or compound statement with a label. I can’t think of a better day than today for us to think about why we might want to do that.

  As a minimalist programmer, I avoid useless code, and that includes unnecessary labels. In the following paragraphs, I will list only the cases that require labels.

  But first, a few ground rules.

  • As with CL, a label name is followed by a colon when used to identify a statement.

  …

  Read more

• Guru: How To Use Global Variables In SQL Scripts

  January 23, 2017 Michael Sansoterra

  Hey, Mike:

  I’m writing SQL scripts to do some administrative work. These scripts are run in iNavigator’s RunSQL scripts utility and use the special CL: directive to execute an IBM i command. I stumbled across the CREATE VARIABLE statement and wondered if I could use an SQL variable to substitute a library name within the OS commands.

  For example, if I define something like the following in iNavigator:

  CREATE OR REPLACE VARIABLE QGPL/LIB CHAR(10) DEFAULT 'JOE';
  

  Can I then use the LIB variable in a CL: command as follows?

  CL:  DSPLIB LIB(LIB) OUTPUT(*PRINT);
  

  –Guru Reader

   

  Unfortunately, GLOBAL VARIABLES cannot …

  Read more

• Handling Constraints Revisited

  March 31, 2015 Paul Tuohy

  I recently had an email from Doug, who was making use of some code I had published way back in February 2010 in my article Handling Constraint Violations in RPG. The article discussed how to trap a constraint violation caused by a WRITE/UPDATE/DELETE operation and, more importantly, how to determine the name of the constraint that caused the violation. Doug had come up with an instance where my code was not working! After going through the usual stages of denial, anger, and some expensive therapy, I read the rest of the email. Doug’s code worked fine with an RPG

  …

  Read more

Next Articles