IBM DB2 for i: Code example

Referential Integrity with RESTRICT Rule

      * This program is intended to illustrate a program       *
      * that has changed to use a referential integrity        *
      * RESTRICT rule rather than checking itself.             *
      * Verify that adding an employee to the EMPLOYEE file    *
      * is assigned to a valid department from the DEPARTMENT  *
      * file .                                                 *
      *                                                        *
      * This program will only work for files that do not      *
      * contain null values. If a field contains null values,  *
      * SQL must be used to write to the file.                 *
      *                                                        *
      *                                                        *
      *           DBGVIEW(*SOURCE) ALWNULL(*YES)               *
      *                                                        *
      * ADDPFCST FILE(CORPDATA/DEPARTMENT)                     *
      *          TYPE(*PRIKEY) KEY(DEPTNO)                     *
      *                                                        *
      * ADDPFCST FILE(CORPDATA/EMPLOYEE)                       *
      *          TYPE(*REFCST) KEY(WORKDEPT)                   *
      *          CST(VERIFY_WORKDEPT)                          *
      *          DLTRULE(*RESTRICT) UPDRULE(*RESTRICT)         *
      *                                                        *
      *                                                        *
      * File definition used for insert.                       *
     FEMPDSP    CF   E             WORKSTN
     F                                     INFDS(FDBF)
      * The file information data structure will be used       *
      * to deterimine if the referential restrict rule is      *
      * violated.                                              *
     DFDBF             DS
     D  STATUS           *STATUS
     D  MSGID                 46     52A
      * Get a new employee record from the display.            *
      * Loop until the department is valid.                    *
     C                   MOVE      *ON           *IN61
     C                   DOW       *IN61
     C                   EXFMT     A
      * Write to the EMPLOYEE file. Referential integrity will *
      * check to see if a department for the employee exists   *
      * If it does not, return a message to the screen.        *
      * In V3R6, this same technique can be used to detect a   *
      * trigger error via 01023 (*BEFORE) or 01024 (*AFTER)    *
     C                   WRITE     EMPFMT                   61
     C     STATUS        IFEQ      1022
     C                   MOVE      *ON           *IN60
     C                   ELSE
     C                   MOVE      *OFF          *IN60
     C                   ENDIF
     C                   ENDDO
     C                   RETURN

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information