04
Mon, Nov
5 New Articles

Referential Integrity Error Trapping

General
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Referential integrity is a major enhancement to DB2/400 announced in V3R1. It allows you to define the relationship between two files-a parent file and a dependent file. For example, the business rule that requires a valid customer number in every order header record can be enforced using referential integrity.

A referential constraint defines the relationship between a parent file and a dependent file. The constraint specifies a rule for insert (add) transactions, a rule for update (change) transactions, and a rule for delete transactions. (For more background information on referential integrity and how referential constraints are defined in OS/400, see "DB2/400 Referential Integrity," MC, September 1995 and the references listed at the end of this article.)

Defining referential constraints is only the first step in the design process; the next question is what to do when a transaction does not conform with the referential constraint. The solution is entirely application dependent. For example, you could issue an error message to the user, you could attempt to correct the problem and reapply the transaction, or you could create a special audit report for transactions that fail. No matter what you choose to do with the error, the first step is to trap it and avoid a system error message.

Once a referential constraint is established with the Add Physical File Constraint (ADDPFCST) command, it is strictly enforced by OS/400. If any application attempts to insert, update, or delete a record that does not conform to the rules of the referential constraint, the transaction will not be completed. This condition is a referential constraint violation.

The mechanisms available to trap referential constraint violations are similar to those used to trap other database errors, such as duplicate keys. If the referential constraint violation is not trapped, a system error message is issued. I'll illustrate referential integrity error trapping with a simple Integrated Language Environment (ILE) RPG program and discuss the support that is available in other development environments.

Trapping referential integrity constraint violations in a well-designed application is usually redundant. Just as you check for duplicate keys before attempting to add a record to a uniquely keyed file, your application can check the business rule enforced by the referential constraint before you actually attempt to update the file. Using the order header relationship with the customer master file as an example, the order entry program chains to the customer master file to determine whether or not the user has entered a valid customer number. If the customer number is invalid, an error message is displayed and the user has an opportunity to correct it. In this case, the program never attempts to create an invalid record and the constraint is never violated.

So why bother to trap referential constraint violations? It's a fail-safe mechanism. Referential integrity will be enforced regardless of any application error. It seems important to at least notify the user that the transaction did not complete. This fail-safe code is similar to including a "Customer not found" message in an open order report. In theory, every order should have a valid customer number. In practice, programmers try to provide accurate information even if the data contains errors. If you issue an error message when a referential constraint is violated, you can avoid confusing the user.

In addition to providing a final check on insert and update transactions, referential integrity error trapping may be the best way to handle the complexity of referential constraint rules for delete transactions. Delete transactions are always enforced from the perspective of the parent file. 1 briefly summarizes the valid rules for a delete constraint.

In addition to providing a final check on insert and update transactions, referential integrity error trapping may be the best way to handle the complexity of referential constraint rules for delete transactions. Delete transactions are always enforced from the perspective of the parent file. Figure 1 briefly summarizes the valid rules for a delete constraint.

To illustrate how referential integrity can simplify your code for delete transactions, suppose that you've specified a delete rule of No Action for the order header file relationship with the customer master file. If you attempt to delete a customer master record for a customer who has outstanding orders, the transaction fails. By trapping the referential constraint violation in the customer maintenance program, you can issue an error message without the overhead of defining the order header file and attempting to read records for the customer. If you use a delete rule of Cascade, you can issue a message if the transaction completes successfully. For example, "All outstanding orders for this customer have been deleted."

No matter what design decisions bring you to the point of trapping referential constraint violations, the process itself is very simple. In RPG, you can trap the file information data structure (INFDS) status code. I'm assuming that you are familiar with the error processing provided by the INFDS. (For more information, see the ILE RPG manuals). Other languages provide similar generic error processing.

To illustrate referential integrity error trapping, I've written an ILE RPG program that attempts to update a record in the order header file. I've defined a referential constraint that requires a valid customer number in the order header file. The program uses a display file that makes it easy to see what happens when a constraint is violated. 2 contains the RPG program, which has been stripped down to the basics for illustration purposes. For example, it doesn't even issue an error message if an invalid order number is keyed.

To illustrate referential integrity error trapping, I've written an ILE RPG program that attempts to update a record in the order header file. I've defined a referential constraint that requires a valid customer number in the order header file. The program uses a display file that makes it easy to see what happens when a constraint is violated. Figure 2 contains the RPG program, which has been stripped down to the basics for illustration purposes. For example, it doesn't even issue an error message if an invalid order number is keyed.

The important features of this program are the INFDS assigned to the ORDHDR file and the processing for referential integrity errors in the error handling subroutine. The error indicator on the UPDATE op code traps any type of system error on this operation, but the program ignores all errors except referential constraint violations. If a referential constraint is violated, the INFDS status code (assigned to field STATUS) will contain either 01222 or 01022. If either of these codes is returned, the program displays an error message.

In a real application, once you trap the status code, you must decide what to do with the information. For example, you can call the customer master maintenance program, allow the user to create a new customer, and rerun the update transaction. Alternatively, you can display an error message. You could even ignore the referential constraint violation. By trapping it in the RPG program, you avoid a hard halt issued by OS/400, and the user will not know that the transaction failed unless your program provides that information. I do not recommend ignoring the error. At a minimum, you should notify the user that the transaction failed.

I chose ILE RPG for the sample program because it has the ability to trap specific referential constraint violation status codes. Original Program Model (OPM) RPG will return a generic file error status code (01299) instead. This creates a problem since many other conditions could potentially cause this file error. Since referential integrity, like ILE RPG, is delivered beginning with V3R1, I recommend that you convert programs that need to handle referential constraint violations to ILE RPG.

The same rule applies to COBOL. ILE COBOL has the ability to isolate referential integrity constraints with a file status of 9R; OPM COBOL returns file status 90.

SQL provides a series of error codes for referential constraint violations. Based on a simple test, these codes will be returned to an environment that uses SQL to access DB2/400. In the test, a client/server application attempted to write a record that violated a referential constraint. SQL code 530 was returned to the application. As with my RPG example, it remains the application developer's responsibility to trap the error and return information to the end user.

Doctors attempt to prevent disease before it happens-with education, sanitation, and vaccinations. When disease does break out, they apply more drastic measures-antibiotics and even surgery. In designing applications, similar goals apply. Referential integrity is not a panacea for a poorly designed database or sloppy programming. Consider it a safety net-if an application fails to trap a referential constraint violation, the database is still protected.

The simple error trapping illustrated in this article can help you protect users from system error messages. You may even find that there are cases when you want to use a referential constraint to help validate data. By choosing a development environment that returns specific errors when a constraint is violated and gracefully trapping those errors, you can expand your design options to take advantage of referential integrity.

Sharon Hoffman is the editor of Midrange Computing. She can be reached by E-mail at This email address is being protected from spambots. You need JavaScript enabled to view it..

REFERENCES

DB2/400 Advanced Database Functions Redbook (GG24-4249, CD-ROM GG244249).

ILE RPG/400 Programmer's Guide (SC09-1525, CD-ROM QBKAQD00).

ILE RPG/400 Reference (SC09-1526, CD-ROM QBKAQE00).

OS/400 DB2/400 Database Programming V3R1 (SC41-3701, CD-ROM QBKAUC00).


Referential Integrity Error Trapping

Figure 1: Delete Constraint Rules

Rule Description

No Action Prevents deletion of the parent file record if there are any records in the dependent file that reference that record. Constraint violations are checked after other database events.

Restrict Same as No Action except for the timing of the check for dependencies. Constraint violations are checked before other database events.

Cascade Deletes all the records in the dependent file that are based on the parent file record being deleted.

Set Default Sets the value of the foreign key (the field in the dependent file that references the parent file) to its default. A parent file record with the default key must exist.

Set Null Sets the value of the foreign key fields that allow nulls to nulls. The foreign key must contain at least one null-capable field.


Referential Integrity Error Trapping

Figure 2: ILE RPG Error Trapping Example

 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 * * Illustration of referential integrity error handling * FORDHDR UF E K DISK INFDS(INFDS) FDB001DF CF E WORKSTN * Define the INFDS to capture status information DINFDS DS DSTATUS *STATUS C DOW *IN03 <> *ON * Interactive example to show errors on screen C EXFMT RITEST C EVAL *IN98=*OFF C MOVE *BLANK ERRMSG C OHNUM CHAIN ORDHDR 99 C *IN99 IFNE *ON C EVAL OHCUST=NWCUST * If any error on update 98 will be set on C UPDATE OHHDR 98 C ENDIF * Call the ERROR subroutine explicitly C *IN98 IFEQ *ON C EXSR ERROR C ENDIF C ENDDO C MOVE *ON *INLR * Error processing C ERROR BEGSR * Ignore all errors except RI errors C STATUS IFEQ 01222 C EVAL ERRMSG='RI error 01222' C ELSE C STATUS IFEQ 01022 C EVAL ERRMSG='RI error 01022' C ENDIF C ENDIF C ENDSR *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: