MFT database logger tables

When you have installed and configured the logger, a number of database tables are created.

MFT Logger database schema updates

From IBM MQ Version 9.1, certain data types have been modified in the database schema, causing a change to the widths of columns in those tables:

Db2 schema

LONG VARCHAR in the following tables has been modified to VARCHAR in the Db2 schema, with a fixed length of 2000 bytes, or 256 characters.

• SCHEDULE_ACTION
• TRANSFER_ITEM
• SCHEDULE_ITEM
• TRIGGER_CONDITION
• CALL_ARGUMENT
• CALL
• CALL_REQUEST
• TRANSFER
• CALL_RESULT
• MONITOR_METADATA
• MONITOR_EXIT_RESULT
• MONITOR_ACTION
• AUTH_EVENT
• FILE_SPACE_ENTRY

By default, LONG VARCHAR allowed you to store 32700 bytes, but VARCHAR(Size) limits the modified column size to 2000 characters, or 256 characters.

See Migrating a Db2 database to the new schema for more information on migrating a Db2 database to the new schema.

Oracle schema

NCLOB in the following tables has been modified to NVARCHAR(Size), where Size can be 2000 bytes or 256 bytes:

• SCHEDULE_ACTION
• TRANSFER_ITEM
• SCHEDULE_ITEM
• TRIGGER_CONDITION
• CALL_ARGUMENT
• CALL
• CALL_REQUEST
• TRANSFER
• CALL_RESULT
• MONITOR_METADATA
• MONITOR_EXIT_RESULT
• MONITOR_ACTION
• AUTH_EVENT
• FILE_SPACE_ENTRY

By default, NVARCHAR2 allows you to store only 4000 bytes. We must set the MAX_STRING_SIZE property to extended for the database to extend the storage to 32767 bytes.

See Migrating an Oracle database to the new schema for more information on migrating an Oracle database to the new schema.

In the SOURCE_FILENAME and DESTINATION_FILENAME columns, in the TRANSFER_ITEM and SCHEDULE_ITEM tables, a datatype of 2000 characters,(VARCHAR(2000) brings commonality in both the Db2 and Oracle schemas.

AUTH_EVENT

An event related to authority checking, typically the rejection of a request due to insufficient privileges.

• ID: Row ID.
• ACTION: The type of action that took place.
• COMMAND_ID: The IBM MQ message ID of the original message that requested the event. In the case of a transfer request, this will also be the transfer ID.
• TIME: The time at which the event occurred.
• ORIGINATOR_MQ_USER: The user ID contained in the IBM MQ message, against which the authority check was performed.
• AUTHORITY: The authority that was required for the requested action.
• ORIGINAL_XML_REQUEST: The payload of the command message, indicating what action was refused.
• RESULTCODE: The numeric code identifying the result.
• RESULT_TEXT: A message explaining the result of the authority event.

CALL

The remote running of an operating system command, or Ant script, or z/OS JCL job, managed by Managed File Transfer. Calls can be embedded in transfers, or referred to by call_request rows.

A CALL (that is, a row in this table) can either be part of a normal transfer (in which case TRANSFER_CALLS is used to link it to the relevant entry in TRANSFERS) or it can be a stand-alone managed call on its own (available only from Ant or by directly inserting messages). In the latter case, the CALL_REQUEST table is used instead of the TRANSFERS table; an equivalent to TRANSFER_CALLS is not needed because there can be only one call per call request.

• ID: Row ID.
• COMMAND: The command that was run. This field does not include any arguments passed to the command or the path where the command is located.
• TYPE: The type of command, such as Ant or JCL.
• RETRIES: The number of retries that were requested.
• RETRY_WAIT: The interval to wait between retries as originally requested, in seconds.
• SUCCESS_RC: The return code that indicates a successful completion of the command. If any other code is received, the run is reported to have failed.
• EXECUTED_COMMAND: The full name of the command that was run, including path.
• CAPPED_RETRIES: The number of retries available; this number might be less than requested if the retry limit of the agent is lower than the number of retries requested.
• CAPPED_RETRY_WAIT: The interval between retries that is used; this number might be less than requested if the configured limit of the agent is lower than the retry wait requested.
• OUTCOME: Whether the call was successful overall. If there were multiple tries the outcome of each one is recorded separately in the CALL_RESULT table.

CALL_ARGUMENT

An argument or parameter supplied to a command that is called.

• ID: Row ID.
• CALL_ID: The call that the argument is associated with.
• KEY: Where the argument is of a key-value-pair kind, the key, or name.
• TYPE: The type of the argument: some are position parameters to operating system commands and others are named properties used with Ant.
• VALUE: The value of the argument.

CALL_REQUEST

The vehicle for a command call that is not part of a file transfer. We can submit ManagedCall messages using Ant and using direct XML injection.

• ID: The hexadecimal ID of the managed call request.
• CALL_ID: The database ID of the row in the CALL table describing this call.
• ACTION_TIME: The time that the action occurred.
• AGENT: The agent that the command is run on.
• AGENT_QM: The queue manager used by the agent that the command is run on.
• ARCHITECTURE: The machine architecture of the system that the agent runs on.
• OS_NAME: The name of the operating system that the agent is running on.
• OS_VERSION: The version of the operating system.
• ORIGINATOR_HOST: The host name of the machine that the call request was submitted from.
• ORIGINATOR_USER: The name of the user who submitted the call request, as reported in the request XML.
• ORIGINATOR_MQ_USER: The name of the user who submitted the call request, as contained in the IBM MQ message descriptor of the request.
• JOB_NAME: A user-specified job name.
• RESULTCODE: The overall result code for the call.
• RESULTTEXT: The overall result message for the call.

CALL_RESULT

The detailed result of calling a command. A call can have multiple results if retries were enabled.

• ID: Row ID.
• CALL_ID: The database ID of the row in the CALL table that this result applies to.
• SEQUENCE: Which attempt this result applies to, where there have been multiple attempts.
• OUTCOME: The outcome (for example, success or failure) of the command.
• RETURN_CODE: The command return code.
• TIME: The time that the command completed.
• STDOUT: The standard output stream from the command, if it was started.
• STDERR: The standard error stream from the command, if it was started.
• ERROR: If the command could not be started, an error message produced by Managed File Transfer explaining the problem.

FILE_SPACE_ENTRY

Each row represents a file that has been sent to the named file space.

• ID: The ID of the file space entry.
• FILE_SPACE_NAME: The name of the file space. This is the name of the user that the file space belongs to.
• TRANSFER_ITEM_ID: The ID of the transfer item that this row relates to.
• ALIAS: The alias name for this file space entry. Typically this alias name is the name of the source file for the transfer.
• DELETED: The time when the file was deleted from the file space. If the file has not been deleted the value is null.

METADATA

Metadata associated with a transfer.

• ID: Row ID.
• TRANSFER_EVENT_ID: The transfer_event row that this metadata is associated with, if it relates to a transfer. This field is null if the metadata is associated with a stand-alone managed call.
• STANDALONE_CALL_ID: If the metadata is associated with a stand-alone managed call, the ID of the managed call request concerned.
• KEY: The name of the metadata item.
• VALUE: The value of the metadata item.

MONITOR

Resource monitors that trigger Managed File Transfer operations based on external conditions.

• AGENT: The agent that the monitor runs on.
• ID: The hexadecimal ID of the monitor.
• NAME: The name of the monitor.
• QMGR: The queue manager of the agent where the monitor runs.

MONITOR_ACTION

Each row represents an action (for example, creation and triggering) occurring in respect of a monitor

• ID: Row ID.
• ACTION: The type of action that took place.
• JOB_NAME: The name of the submitted job, where applicable.
• MONITOR: The monitor that this action occurred on. Might be null if the action failed because it was requested for a monitor that does not exist.
• ORIGINAL_XML_REQUEST: If this action was a create or triggerSatisfied action, the XML request that is started when the monitor is triggered.
• ORIGINATOR_MQ_USER: The user ID contained in the IBM MQ message that initiated the action
• ORIGINATOR_USER: The user name that submitted the request to perform the action.
• ORIGINATOR_HOST: The machine from which the user submitted the request to perform the action.
• TIME: The time that the action occurred.
• UPDATED_XML_REQUEST: If the action is triggerSatisfied, the XML request that was started. This request might vary from the XML request that was originally made because of variable substitution.

MONITOR_EXIT_RESULT

The result of running a resource monitor exit.

• ID: Row ID.
• ACTION_ID: The monitor action that the result is associated with.
• EXIT_NAME: The name of the exit that produced this result.
• RESULTCODE: The value that the exit returned, either cancel or proceed.
• RESULTTEXT: The text output from the exit, if provided.

MONITOR_METADATA

Items of metadata associated with a resource monitor.

• ID: Row ID.
• ACTION_ID: The monitor_action that the metadata is associated with.
• KEY: The name of the metadata item.
• PHASE: Whether this metadata item represents the data that was originally submitted or the updated version after variable substitution.
• VALUE: The value of the metadata item.

SCHEDULE

A transfer schedule registered with an agent.

• AGENT: The name of the agent that has this schedule.
• CREATION_DATE: The point in time that this schedule was created.
• ID: The unique database (not agent) ID for the schedule.
• ID_ON_AGENT: The ID that the agent uses for the database ID. This ID is not unique across agents and might not even be unique in an agent if the persistent state of the agent is reset.
• LATEST_ACTION: The most recent action that modified the state of this schedule.

SCHEDULE_ACTION

When an event occurs that modifies the schedule state, an action is recorded.

• ACTION_TYPE: The action that occurred.
• ID: Row ID
• ORIGINATOR_HOST: The machine that the request that caused the change was submitted from.
• ORIGINATOR_USER: The user whose name the request that caused the change was submitted in.
• SCHEDULE_ID: The schedule that this action applies to.
• SPEC_AFTERWARDS: The schedule_spec that represents the state of this schedule after the action occurred.
• STATUS_CODE: A numeric return code describing the outcome of the action
• STATUS_TEXT: A text description of the outcome of the action. Typically null if the action succeeded.
• TIME: The point in time that the action occurred

SCHEDULE_SPEC

The details of an individual scheduled transfer.

• ID: Row ID.
• DESTINATION_AGENT: The agent that the files are transferred to.
• DESTINATION_QM: The queue manager used by the destination agent.
• REPEAT_COUNT: How many times to repeat if the schedule repeats and is bound by the number of occurrences rather than an end time.
• REPEAT_FREQUENCY: How many repeat_intervals there are between scheduled transfers.
• REPEAT_INTERVAL: If the transfer repeats, what interval to repeat at (for example, minutes or weeks).
• SOURCE_AGENT: The agent that the files are transferred from.
• SOURCE_QM: The queue manager used by the source agent.
• START_TIME: The time that the first transfer in the schedule will take place.
• START_TIMEBASE: The time base for the times associated with the transfer. For example, whether to operate from the time zone of the agent or the time zone of the administrator.
• START_TIMEZONE: The time zone that the time base corresponds to and which will be used in operating the schedule.

SCHEDULE_ITEM

Each file (or pattern to match at transfer time) is represented by a schedule_item.

• ID: Row ID.
• CHECKSUM_METHOD: How the checksum for the file is calculated
• DESTINATION_EXISTS_ACTION: What action the destination agent takes if the file already exists at the destination.
• DESTINATION_FILENAME: The file or directory that the files are transferred into.
• DESTINATION_QUEUE: The destination queue name for a file-to-message transfer.
• DESTINATION_TYPE: Whether the destination_filename column refers to a file or directory.
• DESTINATION_TYPE: Whether the destination_filename column refers to a file, directory, or data set.
• FILE_MODE: The mode (for example, text or binary) that the file is transferred in.
• RECURSIVE: When the agent creates the transfer according to the schedule, whether the agent recurses (Y) or not (N) the source directory.
• SCHEDULE_SPEC_ID: The schedule_spec that this item is associated with.
• SOURCE_DISPOSITION: What action to perform on source files after the transfer completes.
• SOURCE_FILENAME: The source file, directory name, or pattern.
• SOURCE_QUEUE: The source queue name for a message-to-file transfer

TRANSFER

A single transfer of one or more files.

• TRANSFER_ID: The hexadecimal ID for the transfer.
• JOB_NAME: A user-specified job name for the transfer.
• SCHEDULE_ID: If this transfer is the result of a schedule, the database row ID of the schedule concerned.
• START_ID: The row ID of the transfer_event that represents the start of the transfer.
• COMPLETE_ID: The row ID of the transfer_event that represents the end of the transfer.
• RESULTCODE: The overall result code for the transfer. The possible values for this column are listed in the following topic: Return codes for MFT. These codes apply to the transfer as a whole; see TRANSFER_ITEM.RESULTCODE for the status of each individual item.
• RESULTTEXT: The overall result text for the transfer, if any.
• STATUS: The status of a transfer. The possible values for this column are started, success, partial success, failure, and cancelled.
• RELATED_TRANSFER_ID: The hexadecimal ID of a previous transfer that is related to this transfer. For example, if the transfer is a file download , this field will refer to the transfer that uploaded the file.

TRANSFER_CALLS

Links runnable command calls to transfers

• ID: Row ID.
• POST_DESTINATION_CALL: The call made at the destination after the transfer is complete.
• POST_SOURCE_CALL: The call made at the source agent after the transfer is complete.
• PRE_DESTINATION_CALL: The call made at the destination agent before the transfer starts.
• PRE_SOURCE_CALL: The call made at the source agent before the transfer starts.
• TRANSFER_ID: The transfer that the calls in this row are associated with.

TRANSFER_CD_NODE

Information about Connect:Direct nodes that are used in a transfer.

• PNODE: The primary node in the transfer.
• SNODE: The secondary node in the transfer.
• BRIDGE_IS_PNODE: Character indicating which node is the node that is part of the Connect:Direct bridge. If this value is Y, the primary node is the bridge node. If this value is N, the secondary node is the bridge node.
• ID: The ID of this row.

TRANSFER_CORRELATOR

Each row contains a correlation string and a number associated with a transfer item.

• CORRELATION_BOOLEAN: A boolean correlation value. Represented by a single character of Y for true and N for false.
• CORRELATION_STRING: A string correlation value.
• CORRELATION_NUMBER: A numeric correlation value.
• ID: The ID of this row.

TRANSFER_EVENT

An event (start or end) related to a transfer.

• ID: Row ID.
• ACTION_TIME: The time that the transfer action took place.
• SOURCE_AGENT: The name of the agent that the files are transferred from.
• SOURCE_AGENT_TYPE: The type of agent that the files are transferred from. The following values are possible: 1 = STANDARD, 2 = BRIDGE, 3 = WEB_GATEWAY, 4 = EMBEDDED, 5 = CD_BRIDGE, 6 = SFG.Note: From Version 9.0, Managed File Transfer does not support the Web Gateway or web agents.
• SOURCE_QM: The queue manager used by the source agent.
• SOURCE_ARCHITECTURE: The machine architecture of the system hosting the source agent.
• SOURCE_OS_NAME: The operating system of the source agent machine.
• SOURCE_OS_VERSION: The version of operating system of the source agent machine.
• SOURCE_BRIDGE_URL: If the source agent is a protocol bridge agent, the URL of the data source to which it forms a bridge.
• SOURCE_CD_NODE_ID: The Connect:Direct node that is the source of the transfer.
• DESTINATION_AGENT: The name of the agent that the files are transferred to.
• DESTINATION_AGENT_TYPE: The type of agent that the files are transferred to. The following values are possible: 1 = STANDARD, 2 = BRIDGE, 3 = WEB_GATEWAY, 4 = EMBEDDED, 5 = CD_BRIDGE, 6 = SFG.Note: From Version 9.0, Managed File Transfer does not support the Web Gateway or web agents.
• DESTINATION_QM: The queue manager used by the destination agent.
• DESTINATION_BRIDGE_URL: If the destination agent is a bridge agent, the URL of the data source to which it forms a bridge.
• DESTINATION_CD_NODE_ID: The Connect:Direct node that is the destination of the transfer.
• ORIGINATOR_HOST: The host name of the machine that the transfer request was submitted from.
• ORIGINATOR_USER: The name of the user who submitted the transfer request, as reported by the fteCreateTransfer command.
• ORIGINATOR_MQ_USER: The name of the user who submitted the transfer request, as contained in the IBM MQ message descriptor of the request.
• TRANSFERSET_TIME: The time that the transfer set was created.
• TRANSFERSET_SIZE: The number of items being transferred.
• TRIGGER_LOG: For transfer definitions involving a trigger, whether to log trigger evaluations that did not result in a transfer.

TRANSFER_EXIT

Each row represents a transfer exit which was executed as part of a file transfer.

• ID: Row ID.
• EXIT_NAME: The name of the exit.
• TRANSFER_ID: The ID of the completed or canceled transfer that this exit applies to.
• TYPE: The type of exit. This can be one of the following values: SourceStart, SourceEnd, DestinationStart or DestinationEnd.
• STATUS: The value that the exit returned. This can be cancel or proceed.
• SUPPLEMENT: An optional message explaining the status of the exit.

TRANSFER_ITEM

Each row represents a file that is sent as part of the transfer.

• DESTINATION_CHECKSUM_METHOD: The algorithm used to calculate a checksum of the destination file. Might be null if no checksum was calculated because the transfer did not complete successfully.
• DESTINATION_CHECKSUM_VALUE: The checksum value of the destination file. The value might be null if checksumming was disabled.
• DESTINATION_ENCODING: The character encoding used on the destination file, if the destination file is transferred as text.
• DESTINATION_EXISTS_ACTION: The action to perform if the file exists at the destination.
• DESTINATION_FILE_SIZE: The size of the file name or data set name to use at the destination.
• DESTINATION_FILENAME: The file name or data set name to use at the destination.
• DESTINATION_LINEEND: The line-end format used in the destination file, if the destination file is transferred as text.
• DESTINATION_MESSAGE_QUEUE_NAME: The destination queue for the messages that are produced from the source file during a file to message transfer.
• DESTINATION_MESSAGE_GROUP_ID: If more than one message is produced, the group ID used for the messages that are produced from the source file during a file to message transfer.
• DESTINATION_MESSAGE_MESSAGE_ID: If only one message is produced, The message ID of the message that is produced from the source file during a file to message transfer.
• DESTINATION_MESSAGE_COUNT: The number of messages that the source file was split into during a file to message transfer.
• DESTINATION_MESSAGE_LENGTH: The length of the message that is produced from the source file during a file to message transfer, in bytes. This value is only set if we specify a length for the output messages, for example by using the -qs option of the fteCreateTransfer command. If you specify -qs 20K and the size of our source file is 50 KB, the resulting three messages are 20 KB, 20 KB, and 10 KB in size. In this case the value of DESTINATION_MESSAGE_LENGTH is set to 20480.
• DESTINATION_CORRELATOR_ID: The ID of the correlator information for the destination.
• FILE_MODE: The file transfer mode, for example text or binary.
• ID: Row ID
• RESULTCODE: A numeric code indicating the outcome of the transfer of this item. The possible values for this column are listed in the following topic: Return codes for files in a transfer. These codes apply to the individual items in the transfer; see TRANSFER.RESULTCODE for the result of the transfer as a whole.
• RESULT_TEXT: A textual explanation of the result of the transfer. Typically null if the transfer was successful.
• SOURCE_CHECKSUM_METHOD: The algorithm used to calculate a checksum of the source file.
• SOURCE_CHECKSUM_VALUE: The checksum value of the source file. The value might be null if checksumming was disabled.
• SOURCE_DISPOSITION: The action to perform on the source file when the transfer is complete.
• SOURCE_ENCODING: The character encoding used on the source file, if the source file is transferred as text.
• SOURCE_FILE_SIZE: The size of the file name or data set name to use at the source.
• SOURCE_FILENAME: The source file name or data set name .
• SOURCE_LINEEND: The line-end format used in the source file, if the source file is transferred as text.
• SOURCE_MESSAGE_QUEUE_NAME: The source queue for the messages that are included in the destination file for a message to file transfer.
• SOURCE_MESSAGE_GROUP_ID: The group ID of the messages that are included in the destination file for a message to file transfer.
• SOURCE_MESSAGE_COUNT: The number of messages that are included in the destination file for a message to file transfer.
• SOURCE_CORRELATOR_ID: The ID of the correlator information for the source.
• TRANSFER_ID: The transfer that this item is part of.
• TRUNCATE_RECORDS: Indicates whether over length data set records are to be truncated or wrapped.

TRANSFER_STATS

A set of statistics generated at the end of a transfer.

• ID: Row ID.
• TRANSFER_ID: The transfer to which the statistics refer.
• START_TIME: The time at which the transfer started. In a system that is busy or has intermittent connectivity, this time might be later than the time reported in the Started message, as that time represents the point at which initial processing began rather than the point at which the successful transfer of data began.
• RETRY_COUNT: The number of times that the transfer had to be retried because of load or availability issues.
• FILE_FAILURES: The number of files that failed to be transferred.
• FILE_WARNINGS: The number of files that had warnings reported for them when they were transferred.

TRIGGER_CONDITION

One condition in a basic Managed File Transfer conditional transfer. For example, ''file example.file exists''.

• ID: Row ID.
• TRANSFER_EVENT_ID: The transfer event that the trigger is related to.
• CONDITION_TYPE: The type of check used in the trigger. For example, the existence of a file or the size of a file.
• COMPARISON: The specific comparison to make. For example "greater than or equal to".
• VALUE: The value to compare against.
• FILENAME: The file name to examine.

• Db2 entity relationship diagram
  A diagram showing the relationship of the entities in a Db2 database.
• Migrating a Db2 database to the new schema
  How we migrate a database with the existing schema to the new schema, by using the sample SQL script file.
• Oracle entity relationship diagram
  A diagram showing the relationship of the entities in an Oracle database.
• Migrating an Oracle database to the new schema
  How we migrate a database with the existing schema to the new schema, by using the sample SQL script file.

Parent topic: Managed File Transfer administration reference

Related reference

• fteStartLogger: start an MFT logger
• fteModifyLogger: run an MFT logger as a Windows service
• fteStopLogger: stop an MFT logger

Related information

• Configure an MFT logger