In the previous article, I showed how EXISTS can be used to check for quality issues. On this one, I’ll focus on how another under-utilized tool can be used to find another common issue on DB2 databases: lack of consistency.

Written by Rafael Victoria-Pereira

A few articles ago, when I first discussed JOINS, I mentioned the exception join as something I’d discuss later, because it was a bit unusual. If you read the previous article, you know how the EXISTS predicate works and have a good grip on the whole INNER/OUTER JOIN thing; explaining the exception join will be a breeze.

Like many IBM i databases in the real world, this database has no true structure. In many cases, this is the result of legacy code, originally written with the concept of database consistency enforced via RPG code. This database is just a set of tables, informally joined by fields with the same name, or similar names, in different tables. The actual table relationships are kept by the RPG programs that use the database. Naturally, this can potentially lead to database inconsistencies and loss of database integrity. A typical example in our little fictional database would be a class with the wrong course name. Let’s create that scenario by inserting a record on the Classes table with a course name that doesn’t exist on the Courses table:

INSERT INTO UMADB_CHP4.PFCLM

   (CLNM, CLCY, CLCN, CLSN, CLSA, CLSE, CLSC)

VALUES('Macbeth meets Friedrich Nietzsche'

      , 2016

      , 'Philosophy Studies'

      , 'Dalton, Joe'

      , '52 SloMo Boulevard, San Fernando, California'

      , This email address is being protected from spambots. You need JavaScript enabled to view it.'

      , '1'

   )

;

To confirm that an inconsistency exists, let’s run a SELECT using your newly acquired knowledge regarding EXISTS:

SELECT      CLASSES.NAME

FROM  UMADB_CHP4.TBL_CLASSES CLASSES

WHERE NOT EXISTS (

                  SELECT      1

                  FROM  UMADB_CHP4.TBL_COURSES COURSES

                  WHERE COURSES.NAME = CLASSES.COURSE_NAME

                  )

;

This will return a single row, matching the “Macbeth meets Friedrich Nietzsche” class that was created moments ago. Now let’s rewrite this query using EXCEPTION JOIN instead of EXISTS:

SELECT                  CLASSES.NAME

FROM              UMADB_CHP4.TBL_CLASSES CLASSES

EXCEPTION JOIN    UMADB_CHP4.TBL_COURSES COURSES

ON    CLASSES.COURSE_NAME = COURSES.NAME

;

It looks like a regular JOIN, similar to the ones discussed in previous articles, but the functionality is totally different. In fact, it’s similar to the Query/400 “unmatched records with primary file” option. It offers a simple way to check for data problems that’s unique to the IBM i’s DB2 – in other database engines, EXCEPTION JOIN doesn’t even exist. To emulate it on Oracle, for instance, you’d have to use NOT EXISTS or a LEFT JOIN with a NULL filter to get the same result. It’s true that you can use EXISTS/NOT EXISTS to implement similar functionality, at least most of the time, but the EXCEPTION JOIN’s compact and “natural” syntax is faster to write and easier to maintain. There’s a catch: writing an EXCEPTION JOIN with three or four tables might not produce the results you’re looking for. If you use more than one EXCEPTION JOIN within the same SQL statement (especially chained together) or try using it in correlated subqueries, the results are hard to predict. There will be times when an EXISTS predicate will yield better results, even if it’s not as readable as an EXCEPTION JOIN. Anyway, this is a new tool to add to your kit, and, as with most things, there will be appropriate times to use it, and other times you will want to look elsewhere for the solution for a specific problem.

To keep our fictional database as “clean” as possible, let’s delete this anomalous record:

DELETE     

FROM  UMADB_CHP4.TBL_CLASSES

WHERE NAME = 'Macbeth meets Friedrich Nietzsche'

;

I think it goes without saying that instead of chasing the issue, you should, as much as possible, avoid it by strengthening your database with proper consistency checks inside the database instead of legacy RPG code. That’s what I’ll be discussing in the next subseries of SQL 101!

Time for Some (More) Practice

Now that I’ve shown you an example of the EXCEPTION JOIN, how about rolling up your sleeves and writing the necessary SELECT statements to check the consistency of the entire database? Or, even better, create a few views that you can use from time to time, just to check whether the database is still fully consistent? This will be a temporary measure, because I’ll show you in later articles how you can (and should) maintain your database consistency using referential integrity and other constraints. For the moment, let me give you a hand and explain how the tables are linked to one another.

• The Courses table links with the Teachers table twice, via the Course_Director_Name and the Course_TA_Name columns. Note that these links to the Teachers’ Name column are independent from one another, which means you’ll have to write two very similar SELECT statements.
• The Classes table links with the Courses table, as shown before, but also with the Students table. The Classes Student_Name column must match the Students Name column;
• Finally, the Grades table links separately to the Students table, via Student_Name, and the Classes table, via the Class_Name/Class_Year combination, which match the Name and Year columns of the Classes table, respectively.

However, it might be even more interesting (and useful) to perform the same sort of exercise on your own database. Pick a few tables on which you’ve had consistency problems in the past or you suspect might be vulnerable to it due to sketchy consistency rules, implemented via RPG only, and give it a go. If you have questions, just use this article’s comments, and I or your fellow readers here at MC Press will certainly help out.

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale