General guidelines for writing field procedures

The following considerations must be considered when writing field procedures:

  • Must be an ILE *PGM object. *SRVPGMs, OPM *PGMs, and JAVA objects are not supported.
  • Authority to the field procedure *PGM object is checked when the field procedure is added to the table. Authority checking is not done when the field procedure is invoked.
    • Create the field procedure program so that it runs under the user profile of the user who created it. In this way, users who do not have the same level of authority to the program will not encounter errors.
    • Create the program with USRPRF(*OWNER) and *EXCLUDE public authority. Do not grant authorities to the field procedure program to USER(*PUBLIC). Avoid having the field procedure program altered or replaced by other users.
  • No SQL is allowed in a field procedure.
  • The field procedure will not be called if the data to be encoded or decoded is the null value.
  • On an encode operation, packed decimal and zoned decimal values will be converted to the preferred sign prior to calling the user field procedure program.
  • The field procedure must be deterministic. For SQE, caching of results will occur based on the QAQQINI FIELDPROC_ENCODED_COMPARISON.
  • The field procedure must be parallel capable and capable of running in a multi-threaded environment. For RPG, this means the THREAD(*SERIALIZE) control specification must be specified.
  • Must be capable of running in both a fenced and non-fenced environment.
  • The program cannot be created with ACTGRP(*NEW). If the program is created with ACTGRP(*CALLER), the program will run in the default activation group.
  • Field procedure programs are expected to be short running. It is recommended that the field procedure program avoid commitment control and native database operations.
  • Create the program in the physical file's library.
  • If an error occurs or is detected in the field procedure program, the field procedure program should set the SQLSTATE and message text parameters. If the SQLSTATE parameter is not set to indicate an error, database assumes that the field procedure ran successfully. This might cause the user data to end up in an inconsistent state.

Warning: Field procedures are a productive way both to provide application functions and to manage information. However, field procedure programs could provide the ability for someone with devious intentions to create a "Trojan horse"1 on your system. This is why it is important to restrict who has the authority to alter a table. If you are managing object authority carefully, the typical user will not have sufficient authority to add a field procedure program.

1 In history, the Trojan horse was a large hollow wooden horse that was filled with Greek soldiers. After the horse was introduced within the walls of Troy, the soldiers climbed out of the horse and fought the Trojans. In the computer world, a program that hides destructive functions is often called a Trojan horse.