Performs a user exit or pre-defined action when certain significant events occur.
The NOTIFY command is for use in an operator-free environment, where job scheduling relies heavily on automation to optimize system performance. The function includes hooks that system programmers can use to automate BTEQ job streams so they can be integrated with third party vendor products, removing the need to parse the output of BTEQ scripts.
For example, by writing an exit in C (without using CLIv2) and using the NOTIFY...EXIT option, a programmer can create a means for an automation package to detect that a BTEQ request either succeeds or fails, how many blocks are returned in a successful request, what the return code is for a failed request, and so on.
NOTIFY applies to only the request which immediately follows it.
where the following is true:
- A user-written exit is called at the appropriate time. When verbosity is HIGH, the activity count passed to the user exit function is an unsigned 32-bit value, and the request count is a signed 32-bit value.
- A user-written exit is called at the appropriate time. When verbosity is HIGH, the activity count and request count that are passed to the user exit function are defined as unsigned 64-bit values.
- Notification is issued for all events.
- Notification is issued for only one event.
This option is used with the EXIT, EXIT64, MSG, and QUEUE options.
- Notification is issued at points of typical user interest, such as:
- When the job finishes.
- When the most significant event occurs.
- A message is written to the following device or file:
- Operator console (z/OS)
- System log (UNIX OS)
- EventLog service (Windows)
- Specifies the name of a user-supplied library with an entry point named _dynamn
The name of the library to be used varies by operating system. The user routine must be written in C or in a language with a runtime environment that is compatible with C.
- No notification of events is included.
This is the default.
- One of the following z/OS QUEUE parameter options:
- RNAME – a quoted string of up to 255 characters. The default is TRDUSER.
- SCOPE – SYSTEMS: Specifies that the QUEUE is global to all computers in the complex. This is the default. SYSTEM: Specifies that the QUEUE is global to the computer on which it is running. JOB: Specifies that the QUEUE is local to the job (including all job steps).
- NOBLOCK – Specifies that if the ENQ blocks for any reason, it must return an error instead. This is a fatal error for the job.
- Specifies that a queue is to be manipulated using ENQ or DEQ. See QUEUE Notes later in this section for more details.
This option is only valid for z/OS.
- Specifies a 254-byte identifier which appears in the output messages. The string is truncated if it is longer than 254 bytes. Note that, on z/OS, this field is limited to 134 bytes, the size of the JES JOB log. The JES JOB log includes the date, JOB number, and BTEQ message (includes MSG text and event notification text).
NOTIFY is not affected by SET DEFAULT.
The current NOTIFY values are displayed by SHOW CONTROLS.
NOTIFY is not valid in a Teradata SQL macro.
The data type for the activity count value passed to the user exit is UInt32 (unsigned 32-bit integer) when the EXIT option is used, and is UInt64 (unsigned 64-bit integer) when the EXIT64 option is used. Similarly, the request count value passed to the user exit is Int32 (signed 32-bit integer) when the EXIT option is used, and is UInt64 (unsigned 64-bit integer) when the EXIT64 option is used. If the EXIT option is used, it is possible that very large activity count or request count values could be truncated.
To pass a user text to a user exit module, use the NOTIFY MSG command immediately preceding the NOTIFY EXIT or EXIT64 command. For example:
.SET NOTIFY HIGH MSG ‘user-text’
.SET NOTIFY HIGH EXIT btnfyext
This user text remains in effect until the next NOTIFY MSG command is executed, resulting in all user exit functions receiving the same user text. Use the SHOW CONTROLS command to display the user text that is set for the NOTIFY MSG command.
To reset the results of the last action, use the NOTIFY MSG command without text.
The following table shows Notify Events and their unique numbers:
|Initialization||0||Called when NOTIFY is processed.|
|Request Start||42||Called each time a request is issued.|
|Request Done||43||Called when the first request completes.|
|Fetch Start||44||Called multiple times for a multi-statement or repeated request.|
|Fetch Start II||46||Same as Fetch Start, but with different sized parameters.|
|Complete||45||Fetched all answer sets; Request successful.|
|Complete II||47||Same as Complete. but with a different sized parameter.|
|Exit||12||BTEQ exits or NOTIFY goes out of scope.|
|Initialization||No||No||Yes||Version ID length – 4-byte unsigned integer
Version ID string – 32-character (maximum) array
Utility ID – 4-byte unsigned integer
Utility name length – 4-byte unsigned integer
Utility name string – 32-character (maximum) array
User name length – 4-byte unsigned integer
User name string – 64-character (maximum) array
Optional user string length – 4-byte unsigned integer
Optional user string – 256-character (maximum) array
|Request Start||No||No||Yes||SQL Request – null terminated character string|
|Fetch Start||No||No||Yes||Request number – 4-byte signed integer
Statement number – 4-byte signed integer
Activity count – 4-byte unsigned integer
|Fetch Start II||No||No||Yes||Request number – 4-byte signed integer
Statement number – 4-byte signed integer
Activity count – 8-byte unsigned integer
|Complete||No||No||Yes||Request count – 4-byte signed integer|
|Complete II||No||No||Yes||Request count – 8-byte unsigned integer|
|Exit||No||Yes||Yes||Return code – 4-byte signed integer|
|CLI Error||Yes||Yes||Yes||CLI error code – 4-byte unsigned integer|
|DBS Error||Yes||Yes||Yes||DBS error code – 4-byte unsigned integer|
- When NOTIFY is processed, it performs an ENQ upon a QUEUE with RNAME of 'TRDUSER' and a scope of 'SYSTEMS'. This call blocks until it acquires the QUEUE.
- After the job gets the QUEUE, it continues until it reaches a specific point (such as the request completes) where it releases the QUEUE by performing a DEQ.
Error Handling Notes
- When NOTIFY is processed, the subsystems used by BTEQ are initialized, and if necessary, any user exits are loaded and a call is made to initialize the system log (or an ENQ is performed).
If initialization fails, a warning message is issued and processing continues. The exception to this rule is the case where a NOTIFY... QUEUE is issued. If initialization (the ENQ call) fails, it is a fatal error.
- If anything fails after initialization, the request fails. If a user exit returns anything other than 0, a failure is indicated and the job stops.
If an application ends abnormally or unsuccessfully, it can be restarted and some NOTIFY-related activities are re-executed. This is an important issue with respect to writing user exits.
If an application ends abnormally or unsuccessfully while it is holding a queue (using the QUEUE type parameter in z/OS), the application releases the queue before exiting the job. Therefore, when the job restarts, ensure that the application acquires the queue again before processing continues.