DB2/400 constraints and LANSA

Can LANSA support DB2® triggers, referential integrity instead of or including the LANSA ones?

Yes, LANSA ships support for DB Triggers in EPC845 for LANSA V11 SP5 and V12.

Of course, if DB2/400 triggers and constraints are imbedded into tables externally then LANSA generated applications are subject to them.

Some business applications may use DB2/400 constraints on their files. When those files are loaded into LANSA for iSeries (i.e. as maintained by OTHER) the constraints are left intact, at DB2/400 level. LANSA does not duplicate them (i.e. the same constraints are not defined at the I/O Module level). However, when LANSA performs an INSERT, UPDATE or DELETE operation, and the DB2/400 constraints are violated, an I/O error condition occurs.

In general, the use of DB2/400 constraints on LANSA applications is not recommended:

• DB2/400 constraints are platform dependent, limiting the portability of a LANSA application.
• LANSA validation rules are functionally more powerful than DB2/400 constraints.
• The rules should be centralised in one place to simplify the maintenance and avoid redundancy.

Thus, we recommend using only LANSA validation rules.

However, in some cases DB2/400 constraints have to be handled by LANSA. For those cases, we suggest you detect I/O errors caused by DB2/400 constraints and treat them as validation errors. Following is the recommended technique:

1. Make a list of all the possible error message numbers, derived from DB2/400 constraint violations, associated to your I/O operations (e.g. for an INSERT: CPF502D, CPF502E CPF502F, etc).
2. Use the IO_ERROR parameter on the I/O commands to go to a label. On that label, use the GET_MESSAGE BIF to get the error message number. Check the retrieved message number against the ones recorded in point 1, and perform the proper actions (e.g. redisplay or abort). The following RDML section illustrates this approach:

BEGIN_LOOP
* ********* Request user inputs or corrects details
REQ REQUEST    FIELDS((#ADDDATA)) DESIGN(*DOWN) IDENTIFY(*DESC) MENU_KEY(*YES *RETURN)
* ********* Perform any program level validation here
BEGINCHECK
ENDCHECK
* ********* Clear previous messages
USE        BUILTIN(CLR_MESSAGES)
* ********* Attempt to insert data into the data base
INSERT     FIELDS((#ADDDATA)) TO_FILE(EMPMST) IO_ERROR(CHK)
GOTO       LABEL(NXT)
CHK ********** Check I/O Error
* ********* Reset returned Message text and number
CHANGE     FIELD(#MSGTXT #MSGNO) TO(*BLANKS)
* ********* Get and process message
USE        BUILTIN(GET_MESSAGE) WITH_ARGS(N) TO_GET(#RETCDE #MSGTX T #MSGNO)
IF         COND('(#MSGNO *EQ ''CPF502D'') *OR (#MSGNO *EQ ''CPF502E'')*OR (#MSGNO *EQ ''CPF502F'')')
GOTO       REQ
ELSE
ABORT      MSGTXT('I/O Error occurred. Please, see previous messages.')
ENDIF
NXT * ********* If okay, reset fields and issue accepted message
CHANGE     FIELD(#ADDDATA) TO(*DEFAULT)
MESSAGE    MSGID(DCU0011) MSGF(DC@M01) MSGDTA('''Employee Master''')
END_LOOP

The previous code is just an example is to help understanding the technique. Note that it is not convenient to treat all the I/O errors as if they were caused by constraint violations, for example some serious ones (e.g. permanent I/O error).