MESSAGE_FILE_DATA view

The MESSAGE_FILE_DATA view returns one row for each message in a message file.

The information is similar to what is returned by the Display Message Description (DSPMSGD) CL command and the Retrieve Message (QMHRTVM) API.

Authorization: The caller must have:
  • *EXECUTE authority to the library containing the message file, and
  • *USE authority to the message file.

The following table describes the columns in the view. The system name is MSGF_DATA. The schema is QSYS2.

Table 1. MESSAGE_FILE_DATA view
Column Name System Column Name Data Type Description
MESSAGE_FILE_LIBRARY MSGF_LIB VARCHAR(10) The library containing the message file.
MESSAGE_FILE MSGF VARCHAR(10) The message file.
MESSAGE_ID MSGID CHAR(7) The message identifier.
MESSAGE_TEXT MSG_TEXT VARGRAPHIC(132) CCSID 1200 The text of the message.
MESSAGE_SECOND_LEVEL_
  TEXT
 SECLVL VARGRAPHIC(3000) CCSID 1200
Nullable
 The second-level message text of the message.

Contains the null value if no second-level text is defined.
SEVERITY SEVERITY INTEGER The severity of the message.
MESSAGE_DATA_COUNT MSGDATACNT INTEGER The number of variables defined in MESSAGE_DATA.
MESSAGE_DATA MSGDATA VARCHAR(2078)
Nullable
 A string containing all the message data fields, also referred to as substitution variables, for the message. The format of the string is described following this table.

Contains the null value if no message data fields are defined.
LOG_PROBLEM LOGPRB VARCHAR(4) The log problem value for the message.
*NO
Problems are not logged.
*YES
Problems are logged.
CREATION_DATE CRT_DATE DATE The date the message was created.
CREATION_LEVEL CRT_LEVEL INTEGER The level number of the message. This is a value from 1 to 99.
MODIFICATION_DATE MOD_DATE DATE The date the message was modified.
MODIFICATION_LEVEL MOD_LEVEL INTEGER The modification level number of the message. This is a value from 1 to 99.
CCSID CCSID INTEGER The CCSID that applies to the stored values of MESSAGE_TEXT and MESSAGE_SECOND_LEVEL_TEXT.
DEFAULT_PROGRAM_LIBRARY DFT_PGMLIB VARCHAR(10)
Nullable
 The library specified for the default program. Can contain the following special values:
*CURLIB
The current library is used.
*LIBL
The program is found using the library list.

Contains the null value if no default program is defined.
DEFAULT_PROGRAM DFT_PGM VARCHAR(10)
Nullable
 The name of the program called to take default action if this message is sent as an escape message to a program or procedure that is not monitoring for it.

Contains the null value if no default program is defined.
REPLY_TYPE REPLY_TYPE VARCHAR(6)
Nullable
 The type of valid values that can be made to an inquiry or notify message.
*ALPHA
Only an alphabetic string is valid. Blanks are not allowed.
*CHAR
Any character string is valid. If it is a quoted character string, the apostrophes are passed as part of the character string.
*DEC
Only a decimal number is a valid reply.
*NAME
Only a simple name is a valid reply. The name does not have to be an object name, but it must start with an alphabetic character; the remaining characters must be alphanumeric.

Contains the null value if there is no reply type.
REPLY_LENGTH REPLY_LEN INTEGER
Nullable
 The maximum length of a reply to an inquiry or notify message.

Contains the null value if there is no reply type.
REPLY_DECIMAL_POSITIONS REPLY_DEC INTEGER
Nullable
 The maximum number of decimal positions allowed in the message reply.

Contains the null value if there is no reply type or if REPLY_TYPE is not *DEC.
DEFAULT_REPLY DFT_REPLY VARCHAR(132)
Nullable
 The default reply for the message.

Contains the null value if no default reply is defined.
VALID_REPLY_VALUES_COUNT REPLY_CNT INTEGER The number of entries returned in VALID_REPLY_VALUES.
VALID_REPLY_VALUES REPLY_VALS VARCHAR(659)
Nullable
 The list of valid reply values. Each value is a character string with a length of 32. One blank separates values.

Contains the null value if no valid reply values are defined.
VALID_REPLY_LOWER_LIMIT LOWERLIMIT VARCHAR(32)
Nullable
 The lower value limit for a valid reply.

Contains the null value if no range values for replies are defined.
VALID_REPLY_UPPER_LIMIT UPPERLIMIT VARCHAR(32)
Nullable
 The upper value limit for a valid reply.

Contains the null value if no range values for replies are defined.
VALID_REPLY_RELATIONSHIP_
  OPERATOR
 REL_OP CHAR(3)
Nullable
 The relational operator for a relational test entry.
*EQ
Equal to
*GE
Greater than or equal to
*GT
Greater than
*LE
Less than or equal to
*LT
Less than
*NE
Not equal to

Contains the null value if no relationship for valid replies is defined.
VALID_REPLY_RELATIONSHIP_
  VALUE
 REL_VALUE VARCHAR(32)
Nullable
 The value to be compared to the reply entered for a relational test entry.

Contains the null value if no relationship for valid replies is defined.
SPECIAL_REPLY_VALUES_COUNT SPECIALCNT INTEGER The number of entries returned in SPECIAL_REPLY_VALUES.
SPECIAL_REPLY_VALUES SPECIALVAL VARCHAR(1319)
Nullable
 The list of special reply values. Each pair of values consists of the from-value followed by a colon (:) followed by the to-value. One blank separates each pair of values.

Contains the null value if no special reply values are defined.
DUMP_LIST_COUNT DUMP_COUNT INTEGER The number of entries returned in DUMP_LIST.
DUMP_LIST DUMP_LIST VARCHAR(815)
Nullable
 The list of data items to be dumped when the message is sent as an escape message to a program that is not monitoring for it. The list contains entries separated by a single blank.
1-99
The number of the message data field that is to be dumped.
*JOB
The job information produced by the Display Job (DSPJOB) command is printed.
*JOBDMP
The data areas of the job are dumped as specified by the Dump Job (DMPJOB) command.
*JOBINT
The internal machine data structures related to the machine process in which the job is running are dumped to the machine error log.

Contains the null value if there is no dump list for this message.
ALERT_OPTION ALERTOPT VARCHAR(9) Whether an alert is sent for the message.
*DEFER
An alert is sent after local problem analysis.
*IMMED
An alert is sent immediately when the message is sent to a message queue that has the allow alerts attribute set to *YES.
*NO
No alert is sent.
*UNATTEND
An alert is sent immediately when the system is running in unattended mode.
ALERT_INDEX ALERTINDEX INTEGER
Nullable
 The number of the message data field that is passed with the alert.

Contains the null value if no alert is sent or no message data field is passed with the alert.
The MESSAGE_DATA column contains all of the substitution variables formatted as follows. A single blank separates each variable attribute. There is one blank between each variable definition. The order of the attributes is:
  1. Variable identifier, such as &1.
  2. Data type of the variable.
    *BIN
    A binary value formatted in the message as a signed decimal value.
    *CCHAR
    A convertible character string.
    *CHAR
    A character string formatted without enclosing apostrophes.
    *DEC
    A packed decimal number that is formatted in the message as a signed decimal value with a decimal point.
    *DTS
    An 8-byte field that contains a system date/time stamp and is formatted in the message as the date followed by one blank and then the time.
    *HEX
    A string of bytes formatted as a hexadecimal value.
    *ITV
    An 8-byte binary field that contains the time interval (in seconds) for wait time-out conditions.
    *QTDCHAR
    A character string formatted with enclosing apostrophes.
    *SPP
    A 16-byte space pointer to data in a space object.
    *SYP
    A 16-byte system pointer to a system object.
    *UBIN
    A binary value formatted in the message as an unsigned decimal value
    *UTC
    An 8-byte field that contains a system date/time stamp in Coordinated Universal Time (UTC) and is formatted in the message as the date followed by one blank and then the time. Before the output formatting the date/time stamp is adjusted from UTC using the time zone specified for the job.
    *UTCD
    An 8-byte field that contains a system date/time stamp in Coordinated Universal Time (UTC) and is formatted in the message as a date with no time. Before the output formatting the date/time stamp is adjusted from UTC using the time zone specified for the job.
    *UTCT
    An 8-byte field that contains a system date/time stamp in Coordinated Universal Time (UTC) and is formatted in the message as a time with no date. Before the output formatting the date/time stamp is adjusted from UTC using the time zone specified for the job.
  3. Length, if applicable.
    • *VARY or the length of the substitution variable.
  4. Additional length information, if applicable.
    • Fractional digits for a *DEC variable
    • 2 or 4 when the length is *VARY
For example, when there are two substitution variables, one a varying character and one a four byte integer, the string might look like this.
&1 *CHAR *VARY 2 &2 *BIN 4

Example

Find any messages in the APPLIB/APPMSGS message file that contain the word VALUE in upper case, lower case, or mixed case in either the message text or the second level message text.
SELECT * FROM QSYS2.MESSAGE_FILE_DATA 
  WHERE MESSAGE_FILE_LIBRARY = 'APPLIB' AND MESSAGE_FILE = 'APPMSGS' AND
      (UPPER(MESSAGE_TEXT) LIKE '%VALUE%' OR 
       UPPER(MESSAGE_SECOND_LEVEL_TEXT) LIKE '%VALUE%');