NOTIFY - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-06-28
dita:mapPath
wmy1488824663431.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

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.

Syntax

Mainframe-Attached Systems



Workstation-Attached Systems



where the following is true:

EXIT
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.
EXIT64
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.
HIGH
Notification is issued for all events.
LOW
Notification is issued for only one event.

This option is used with the EXIT, EXIT64, MSG, and QUEUE options.

MEDIUM
Notification is issued at points of typical user interest, such as:
  • When the job finishes.
  • When the most significant event occurs.
MSG
A message is written to the following device or file:
  • Operator console (z/OS)
  • System log (UNIX OS)
  • EventLog service (Windows)
name
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.

OFF
No notification of events is included.

This is the default.

options
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.
QUEUE
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.

text
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).

Usage Notes

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:

Notify Event Number Description
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.
DBS Restart 9  
CLI Error 10  
DBS Error 11  
The set of Notify Events is subject to future expansion. Therefore, when creating Notify User Exits, ensure that the receipt of those new event codes will not result in an abnormal end.
Events That Create Notifications  
Event Notification Level Parameter
Low Medium High
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
Request Done Yes Yes Yes None
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
DBS Restart No Yes Yes None
CLI Error Yes Yes Yes CLI error code – 4-byte unsigned integer
DBS Error Yes Yes Yes DBS error code – 4-byte unsigned integer

QUEUE Notes

  • 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 an error occurs, the following happens:
  • 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.

Restart Notes

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.