NOTIFY - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
15.00
Language
English (United States)
Last Update
2018-09-25
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

NOTIFY

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  

For Mainframe-Attached Systems:

For Workstation-Attached Systems:

where:

 

Syntax Element

Specification

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” on page 231 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).

    Using NOTIFY

    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

     

    Note: 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.

     

    Table 7: 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:

  • 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.

    Building an Exit Module

    When building an exit module, there are general procedures to consider that are constant across all operating systems:

  • The exit must be named _dynamn
  • Success is indicated by the return of a 0 (32-bit signed integer format)
  • Failure is indicated by the return of a nonzero value (32-bit signed integer format). Use different integers to indicate different errors.
  • The only parameter to the procedure is a pointer to a variable record structure.
  • A C prototype example for an exit procedure might be as follows:
  • Int32 _dynamn(BTNotifyExitParm *P)
  • The C structure that is passed to user exits by BTEQ for NOTIFY can be obtained from the sample notify header file btnfyext.h which is included in the BTEQ package.
  • z/OS BTEQ can use both SAS/C compiled and IBM/C compiled Notify EXIT modules.
  • Table 8 summarizes how to build exit modules using different operating systems:

     

    Table 8: Building Exit Modules Using Different Operating Systems  

    Operating System

    Procedure

    Sun Solaris

    Compile the module with the following options: -K PIC -G.

    The resulting module is in shared library format.

    The exit must be specified using a filesystem path descriptor unless the environment variable LD_LIBRARY_PATH is set to point to the module at run time.

    BTEQ installs three sample files: the sample C file, include file, and makefile. The respective files are located in the following locations:

    * /opt/teradata/client/<version>/sample/btnfyext.c
    * /opt/teradata/client/<version>/include/btnfyext.h
    * /opt/teradata/client/<version>/sample/mkbtnfyext.unx

    To run the sample makefile, enter the following command:

    make -f mkbtnfyext.unx

    Red Hat Enterprise Linux

    Compile the module with the following options: -m32 -fPIC -shared.

    The resulting module is in shared library format.

    The exit must be specified using a filesystem path descriptor unless the environment variable LD_LIBRARY_PATH is set to point to the module at run time.

    BTEQ installs three sample files: the sample C file, include file, and makefile. The respective files are located in the following locations:

    * /opt/teradata/client/<version>/sample/btnfyext.c
    * /opt/teradata/client/<version>/include/btnfyext.h
    * /opt/teradata/client/<version>/sample/mkbtnfyext.linux

    To run the sample makefile, enter the following command:

    make -f mkbtnfyext.linux

    z/Linux

    Compile the module using the following options:

  • -m31
  • -fPIC
  • -shared
  • The resulting module is in shared library format.

    The exit must be specified using a filesystem path descriptor unless the environment variable LD_LIBRARY_PATH is set to point to the module at runtime.

    BTEQ installs three sample files, which can be found in the following locations:

  • C source file
    /opt/teradata/client/<version>/sample/btnfyext.c
  • Header (include) file
    /opt/teradata/client/<version>/include/btnfyext.h
  • Makefile
    /opt/teradata/client/<version>/sample/mkbtnfyext.suselinuz-390.32
  • To run the sample makefile, enter the following command:

    make -f mkbtnfyext.suselinux-390.32

    IBM AIX

    Compile the module with the following option: -G

    The resulting module is in shared library format.

    The exit must be specified using a file system path descriptor unless the environment variable LD_LIBRARY_PATH is set to point to the module at run time.

    BTEQ installs three sample files: the sample C file, include file, and makefile. The respective files are located in the following locations:

    * /opt/teradata/client/<version>/sample/btnfyext.c
    * /opt/teradata/client/<version>/include/btnfyext.h
    * /opt/teradata/client/<version>/sample/mkbtnfyext.aix

    To run the sample makefile, enter the following command:

    make -f mkbtnfyext.aix

    Hewlett-Packard HP-UX PA-RISC

    Compile the module with the following options: +z +u1

    The resulting module is in shared library format.

    The exit must be specified using a file system path descriptor unless the environment variable SHLIB_PATH is set to point to the module at run time.

    BTEQ installs three sample files: the sample C file, include file, and makefile. The respective files are located in the following locations:

    * /opt/teradata/client/<version>/sample/btnfyext.c
    * /opt/teradata/client/<version>/include/btnfyext.h
    * /opt/teradata/client/<version>/sample/mkbtnfyext.hpux

    To run the sample makefile, enter the following command:

    make-f mkbtnfyext.hpux

    (ia64) Hewlett-Packard HP-UX

    Compile the module with the following options: +u1 -D_REENTRANT +DD64

    The resulting module is in shared library format.

    The exit must be specified using a file system path descriptor unless the environment variable LD_LIBRARY_PATH/SHLIB_PATH is set to point to the module at run time.

    BTEQ installs three sample files: the sample C file, include file, and makefile. The respective files are located in the following locations:

    * /opt/teradata/client/<version>/sample/btnfyext.c
    * /opt/teradata/client/<version>/include/btnfyext.h
    * /opt/teradata/client/<version>/sample/mkbtnfyext.hpux-ia64

    To run the sample makefile, enter the following command:

    make-f mkbtnfyext.hpux-ia64

    IBM z/OS

    z/OS BTEQ installs two Notify EXIT sample files in the SAMPLIB PDS:

    BTNFYXTC - sample Notify Exit C file

    BTNFYEXT - sample Notify Exit Header file required by the C file

    User-loadable Notify EXIT modules must exist in a load library whose location is identified in one of the DD names specified by the JOBLIB or STEPLIB in the JCL.

    Using the SAS/C compiler:

    1 Compile and linkedit the Notify sample file with SAS/C using LC370B and LINKEDIT in a JCL script.

    2 The C and Header files should be accessed from SAMPLIB PDS.

    3 The Notify Exit module should be created in a load library.

    4 The resulting module is loadable by the IBM/C runtime.

    5 For a sample JCL to build SAS/C Notify Exit load module, see “Example 7- z/OS” on page 240.

    Using the IBM/C compiler:

    Compile the Notify sample file using IBM/C (from USS) as follows:

    cc -o “//’<load_library>(<module_name>)’” -W c,dll,expo -W l,dll <filename>

    The resulting module is created as a DLL in the specified load library and is loadable by the IBM/C runtime.

    Example:

    cp “//’USER.SAMPLIB(BTNFYEXT)‘” btnfyext.h
    cp “//’USER.SAMPLIB(BTNFYXTC)‘” btnfyext.c
    cc -o “//’USER.LOAD(BTNFYEXT)’” -W c,dll,expo -W l,dll btnfyext.c

    For a sample JCL to build IBM/C Notify Exit load module using JCL, see “Example 8- z/OS” on page 241.

    Microsoft Windows

    A sample exit module makefile (btnfyext.mak) is included. Modify btnfyext.c as necessary and compile by issuing:

    nmake -f btnfyext.mak

    This produces btnfyext.dll. Copy the DLL to where bteq.exe resides c:\Program Files\Teradata\Client\<version>\Bin (by default).

    Apple OS X

    Compile the module with the following options:

    -arch x86_64 -fPIC -shared

    The resulting module is in shared library format.

    The exit must be specified using a file system path descriptor unless the environment variable DYLD_LIBRARY_PATH is set to point to the module at run time.

    BTEQ installs three sample files: the sample C file, include file, and makefile.

    The respective files are located in:

    * %dest_root%/sample/btnfyext.c
    * %dest_root%/include/btnfyext.h
    * %dest_root%/sample/mkbtnfyext.macosx-x86_64

    where %dest_root% is Library/Application Support/Teradata/Client/<version>

    To run the sample makefile, specify:

    make -f mkbtnfyext.macosx-x86_64

    Example - UNIX OS

    This example uses a user-loadable module called btnfyext.so. This module is built from the file btnfyext.c, which is included along with a makefile in the BTEQ distribution as an example.

    Before calling BTEQ, set the environmental variable LD_LIBRARY_PATH so that the module can be found:

    Script started on Wed Jan 24 18:19:12 1996
    app002-0:/home/cme/src/notify/clientsw/src/bteqsrc > export LD_LIBRARY_PATH=/home/cme/src/notify/clientsw/src/bteqsrc
    app002-0:/home/cme/src/notify/clientsw/src/bteqsrc > bteq
     Teradata BTEQ 3.1 for UNIX5. Enter your logon or BTEQ command:
    .logon dbc
    ....
     
     BTEQ -- Enter your SQL request logon or BTEQ command:
    .set notify low exit btnfyext.so
    .set notify low exit btnfyext.so
     BTEQ -- Enter your SQL request logon or BTEQ command: 
    sel * from sessiontbl;
    sel * from sessiontbl;
     *** Query completed. 5 rows found. 24 columns returned. 
     *** Total elapsed time was 1 second.
     HostNo      SessionNo IFPNo LogonDate   LogonTime LogonCollation LogonData
    ------- -------------- ----- --------- ----------- -------------- ----
         97             -2   4-3  96/01/24 15:25:29.56 H              TDPUSER
         93             -2   3-4  96/01/09 17:13:25.24 H              TDPUSER
        100          1,012   4-4  96/01/24 18:22:18.20 H             DBC
         93             -3   3-4  96/01/09 17:13:25.25 H              TDPUSER
         97             -3   3-3  96/01/24 15:25:29.57 H              TDPUSER
    .....

    The user-loadable module writes the parameters it passed to a file called NFYEXT.OUT. After the parameter sequence, the file contains the following line:

    exit called @ bteq request complete.

    Example - UNIX OS

    This example uses the MEDIUM option. The BTEQ output is not included because it is similar to the output from Example 1.

    The NFYEXT.OUT output file contains two lines:

    exit called @ bteq request complete.
    exit called @ bteq notify out of scope: return code 0.

    Example - UNIX OS

    This example uses the HIGH option. Every event is used, and the exit is called six times:

    exit called @ bteq init.
    	Version Id:	Notify Version 01.00.00.
    	Version Id Len:	23.
    	Utility Id:	4.
    	Utility Name:	BTEQ 14.00.00.00.
    	Utility Name Len: 16.
    	User Name:	dbc.
    	User Name Len:	3.
    exit called @ bteq start request: 'sel * from sessiontbl;'.
    exit called @ bteq request complete.
    exit called @ bteq fetch: statement 1, request 1 activity 5.
    exit called @ bteq request processing complete: 1 requests
    exit called @ bteq notify out of scope: return code 0.

    Example - UNIX OS

    This example uses the MSG option.

    The STDOUT from BTEQ is as follows:

    BTEQ 3.1 Wed Jan 24 20:05:10 1996
     
    +---------+---------+---------+---------+---------+----
    .logon dbc,
     *** Logon successfully completed.
     *** Transaction Semantics are BTET.
     *** Character Set Name is ASCII.
     *** Total elapsed time was 1 second.
     
    +---------+---------+---------+---------+---------+----
    .set notify high msg 'This is a UNIX msg';
    +---------+---------+---------+---------+---------+----
    sel * from sessiontbl;
    *** Query completed. 5 rows found. 24 columns returned. 
     *** Total elapsed time was 1 second.
     HostNo SessionNo IFPNo LogonDate   LogonTime LogonCollation LogonData
    ------- --------- ----- --------- ----------- -------------- ---------
        97         -2   4-3  96/01/24 15:25:29.56 H              TDPUSER
        93         -2   3-4  96/01/09 17:13:25.24 H              TDPUSER
        93         -3   3-4  96/01/09 17:13:25.25 H              TDPUSER
       100      1,013   4-4  96/01/24 20:02:59.95 H              DBC
        97         -3   3-3  96/01/24 15:25:29.57 H              TDPUSER
    +-----+--------_-+------+---------+-----------+-----------------------
    .quit
     *** You are now logged off from the DBC.
     *** Exiting BTEQ...
     *** RC (return code) = 0 

    For UNIX systems, the MSG option requires BTEQ to write a message using the standard syslog (system log) system.

    On the test system, the default location for the file is /var/adm/usererr/
    error.01-24
    . However, the location of this file can vary.

    The above job produces the following lines in the system log:

    Jan 24 20:05:10 app002-0 unix: syslog: This is a UNIX msg - BTEQ notify processed.
    Jan 24 20:05:10 app002-0 unix: syslog: This is a UNIX msg - BTEQ request issued: 'sel * from sessiontbl;'.
    Jan 24 20:05:11 app002-0 unix: syslog: This is a UNIX msg - BTEQ request complete.
    Jan 24 20:05:11 app002-0 unix: syslog: This is a UNIX msg - BTEQ ready to fetch request 1, statement 1: activity count 5.
    Jan 24 20:05:11 app002-0 unix: syslog: This is a UNIX msg - BTEQ fetches complete: 1 requests.
    Jan 24 20:05:11 app002-0 unix: syslog: This is a UNIX msg - BTEQ notify out of scope RC = 0.

    Example - z/OS

    This example uses the ENQUEUE/DEQUEUE function, which is supported only by
    z/OS.

    The SYSPRINT is as follows:

    ********************** TOP OF DATA *******************************
    TERADATA CORPORATION            BTEQ 5.3.0 Thu Jan 18 15:03:41 1996             
                                                                                    
    +---------+---------+---------+---------+---------+----
    /***************************************/                                       
    /* just a little test program...       */                                       
    /***************************************/                                       
    .WIDTH 80                                                                       
    +---------+---------+---------+---------+---------+----
    .LOGON tdri/cme,                                                                
     *** Logon successfully completed.                                              
                                                                                    
     *** Total elapsed time was 0.75 seconds.                                       
                                                                                    
    +---------+---------+---------+---------+---------+----
    .show versions;                                                                 
     BTEQ Version 5.3.0 for IBM OS/370                                              
     BTQMain   : H3_03                                                              
     BTQUtil   : H3_05                                                              
     BTQResp   : H3_03                                                              
     BTQParse  : H3_02                                                              
     BTQNotfy  : H3_00                                                              
     CapAAUtl  : H3_01                                                              
     CapCLUtl  : H3_02                                                              
     CapIOUtl  : H3_03                                                              
     CapLogW   : H3_00                                                              
     CapMVSQ   : H3_00                                                              
     CapNfy    : H3_00                                                              
     CapLoadM  : H3_00                                                              
     MOSIDEP   : H0_02                                                              
     CapTypes  : H2_01                                                              
     CapDBC    : H3_00                                                              
     ITBDBC    : H3_02                                                              
     MOSIIF    : H2_01                                                              
     BTEQ linking date is 01/03/96                                                  
    +---------+---------+---------+---------+---------+---- 
    .set notify low queue rname 'sighsighsighsigh';                                 
    +---------+---------+---------+---------+---------+----
    .if errorcode <> 0 then .quit errorcode;                                        
    +---------+---------+---------+---------+---------+----
    sel * from cme.foo;                                                            
     *** Query completed. 4 rows found. One column returned.                        
     *** Total elapsed time was 0.27 seconds.                                       
    96/01/18                      sel * from cme.foo;                     Page    1 
                                                a                                   
                                      -----------                                   
                                                2                                   
                                                4                                   
                                                1                                   
                                                3                                   
    +---------+---------+---------+---------+---------+----
    .LOGOFF                                                                         
     *** You are now logged off from the DBC.                                       
    +---------+---------+---------+---------+---------+----
     *** Exiting BTEQ...                                                            
     Ending BTEQ  Thu Jan 18 15:03:42 1996       

    The JES2 LOG output follows. Note the second, third and fourth lines. The three messages are written when a queue is used:

  • TRDF0001I, means that the job is waiting for a queue whose QNAME is 'TRDFEQUE' and whose RNAME is 'sighsighsighsigh'.
  • TRDF0002I, means that the queue is now held by the job. It is possible that the job might block for an indefinite period of time between the TRDF000 and TRDF0002I messages.
  • TDRF0003I, means that the job is releasing the queue. These messages can help users of the QUEUE feature debug their jobs.
  •                                    
    J E S 2  J O B  L O G  --  S Y S T E M  M V S 2  --  N O D E  T E R M V S 2
                                                                                    
    15.03.29 JOB 3207  $HASP373 BTEQTEST STARTED - INIT 42 - CLASS A - SYS MVS2     
    15.03.41 JOB 3207  +TRDF0001I BTEQTEST ENQ - Q=TRDFEQUE R=sighsighsighsigh      
    15.03.41 JOB 3207  +TRDF0002I BTEQTEST ENQ - Q=TRDFEQUE R=sighsighsighsigh      
    15.03.42 JOB 3207  +TRDF0003I BTEQTEST DEQ - Q=TRDFEQUE R=sighsighsighsigh      
    15.03.42 JOB 3207  BTEQTEST.STEP01  .         RC=ZERO T=0000 D=0210 MT=.17S ET=012S
    15.03.42 JOB 3207  BTEQTEST ENDED.  NAME=CHARLES EUBANKS      TOTAL MT=.17S ET=012S
    15.03.42 JOB 3207  $HASP395 BTEQTEST ENDED                                      
    ------ JES2 JOB STATISTICS ------                                               
        18 JAN 96 JOB EXECUTION DATE                                                
               38 CARDS READ                                                        
              163 SYSOUT PRINT RECORDS                                              
                0 SYSOUT PUNCH RECORDS                                              
                9 SYSOUT SPOOL KBYTES                                               
             0.21 MINUTES EXECUTION TIME                                            
     
     *** Query completed. 5 rows found. 24 columns returned. 

    Example - z/OS

    This example uses the MSG option.

    The SYSPRINT is as follows:

    TERADATA CORPORATION            BTEQ 5.3.0 Thu Jan 18 15:05:11 1996             
                                                                                    
    +---------+---------+---------+---------+---------+----
    /***************************************/                                       
    /* just a little test program...       */                                       
    /***************************************/                                       
    .WIDTH 80                                                                       
    +---------+---------+---------+---------+---------+----
    .LOGON tdri/cme,                                                                
     *** Logon successfully completed.                                              
                                                                                    
     *** Total elapsed time was 0.65 seconds.                                       
                                                                                    
    +---------+---------+---------+---------+---------+----
    .set notify low msg 'this is a test' ;                                       
    +---------+---------+---------+---------+---------+----
    .if errorcode <> 0 then .quit errorcode;                                        
    +---------+---------+---------+---------+---------+----
    sel * from cme.foo;                                                             
     *** Query completed. 4 rows found. One column returned.                       
     *** Total elapsed time was 0.17 seconds.                                      
    96/01/18                      sel * from cme.foo;                     Page    1
                                                a                                  
                                      -----------                                  
                                                2                                  
                                                4                                  
                                                1                                  
                                                3                                  
    +---------+---------+---------+---------+---------+----
    .LOGOFF                                                                        
     *** You are now logged off from the DBC.                                      
    +---------+---------+---------+---------+---------+----
     *** Exiting BTEQ...                                                           
     Ending BTEQ  Thu Jan 18 15:05:12 1996                                         
                                                                                   

    The JES2 LOG output follows. The TRDF0010I message is the MSG output, thus it contains the user-provided string “this is a test”:

    J E S 2  J O B  L O G  --  S Y S T E M  M V S 2  --  N O D E  T E R M V S 2
    15.05.06 JOB 3214  $HASP373 BTEQTEST STARTED - INIT 42 - CLASS A - SYS MVS2     
    15.05.11 JOB 3214  +TRDF0010I this is a test - BTEQ request complete.' 
    15.05.12 JOB 3214  BTEQTEST.STEP01  .         RC=ZERO T=0000 D=0214 MT=.16S ET=5.5S
    15.05.12 JOB 3214  BTEQTEST ENDED.  NAME=CHARLES EUBANKS      TOTAL MT=.16S ET=5.6S
    15.05.12 JOB 3214  $HASP395 BTEQTEST ENDED 
    ------ JES2 JOB STATISTICS ------                                               
        18 JAN 96 JOB EXECUTION DATE                                                
               38 CARDS READ                                                        
              182 SYSOUT PRINT RECORDS                                              
                0 SYSOUT PUNCH RECORDS                                              
                9 SYSOUT SPOOL KBYTES                                               
             0.10 MINUTES EXECUTION TIME

    Example - z/OS

    This example shows how to build a SAS/C Notify Exit load module.

    Assuming that these PDSs exist:

  • USER.SAMPLIB
  • USER.TEXTM
  • USER.OBJM
  • USER.LOADM
  • and the sample Notify C-file BTNFYXTC and H-file BTNFYEXT exist in the USER.SAMPLIB PDS, then the following JCL can be used to build the sample Notify load module USER.LOADM(BTNFYEXT) using SAS/C compiler:

    //************************************************************
    //* ACTN: COMPNFY - JCL for compiling.
    //************************************************************
    //COMPNFY  EXEC PGM=LC370B
    //STEPLIB  DD DSN=SYS2.SASLIB.LOAD,DISP=SHR
    //         DD DSN=SYS2.SASLIB.LINKLIB,DISP=SHR
    //SYSTERM  DD SYSOUT=*
    //SYSPRINT DD SYSOUT=*
    //SYSUT1   DD UNIT=SYSDA,SPACE=(TRK,(10,10))
    //SYSLIB   DD DSN=SYS2.SASLIB.MACLIBC,DISP=SHR
    //H        DD DSN=USER.SAMPLIB,DISP=SHR
    //SYSLIN   DD DSN=USER.TEXTM(BTNFYEXT),DISP=SHR
    //SYSIN    DD DSN=USER.SAMP(BTNFYXTC),DISP=SHR
    //*
    //************************************************************
    //* ACTN: COOLNFY - JCL for pre-linking object file.
    //************************************************************
    //COOLNFY  EXEC CMCOOL#,SREL=700C
    //SYSLIN   DD  DISP=OLD,DSN=USER.OBJM(BTNFYEXT)
    //SYSIN    DD  DISP=SHR,DSN=USER.TEXTM(BTNFYEXT)
    //*
    //************************************************************
    //* ACTN: LINKNFY - JCL for linking object file.
    //************************************************************
    //LINKNFY  EXEC PGM=IEWL,PARM='LIST,MAP,XREF,LET,RMODE=24,AMODE=31'
    //SYSPRINT DD  SYSOUT=*
    //SYSUT1   DD  UNIT=SCR,SPACE=(CYL,(2,2))
    //SYSUT2   DD  UNIT=SCR,SPACE=(CYL,(2,2))
    //SYSLIN   DD  DSN=USER.OBJM(BTNFYEXT),DISP=SHR
    //         DD  DDNAME=SYSIN
    //SYSLIB   DD  DISP=SHR,DSN=SYS2.SASC700C.STDLIB
    //         DD  DISP=SHR,DSN=SYS2.SASC700C.BASELIB
    //SYSLMOD  DD  DSN=USER.LOADM(BTNFYEXT),DISP=SHR
    //SYSIN    DD  DATA,DLM='##'
     NAME BTNFYEXT(R)
    ##

    Example - z/OS

    This example shows how to build a IBM/C Notify Exit load module using JCL.

    Assuming the sample Notify C-file BTNFYXTC and H-file BTNFYEXT exist in the USER.SAMPLIB PDS, following JCL can be used to build the sample Notify Exit load module USER.LOAD(BTNFYEXT) using IBM/C compiler:

    //BNFYIBMC EXEC EDCCL,
    //* THE SAMPLE NOTIFY EXIT C-PROGRAM BTNFYXTC.C
    //         INFILE='USER.SAMPLIB(BTNFYXTC)',
    //* THE OUTPUT NOTIFY EXIT LOAD MODULE
    //         OUTFILE='USER.LOAD(BTNFYEXT),DISP=SHR',
    //         CPARM='DLL,RENT,LONGNAME,SOURCE,EXPO',
    //         LPARM='DYNAM=DLL' 
    //* THE LOCATION OF REQUIRED HEADER FILE BTNFYEXT.H
    //SYSLIB   DD DSN=USER.SAMPLIB,DISP=SHR
    //*

    Example - Windows

    This example uses a user-loadable module called btnfyext.dll. The module is built from btnfyext.c, which is included with a makefile in the BTEQ distribution as an example.

    c:\program files\teradata\client\14.10\bin>bteq
    Teradata BTEQ 14.10.00.00 for WIN32.
    Copyright 1984-2012, Teradata Corporation. ALL RIGHTS RESERVED.
    Enter your logon or BTEQ command:
    .logon dbc
    .logon dbc
    Password:
    ***Logon successfully completed.
    ***Teradata Database Release is 14.10.00.00
    ***Teradata Database Version is 14.10.00.00
    ***Transaction Semantics are BTET.
    ***Character Set Name is ASCII.
    ***Total elapsed time was 2 seconds.
    BTEQ -- Enter your SQL request or BTEQ command:
    .set notify low exit64 btnftext.dll
    BTEQ -- Enter your SQL request or BTEQ command:
    sel * from dbc.sessioninfo;
    ***Query completed. 7 rows found. 15 columns returned.
    ***Total elapsed time was 1 second.
    UserName     AccountName     Session
    DBC          DBC             1,2
    DBC          DBC             1,2
    DBC          DBC             1,2
    DBC          DBC             1,0
    DBC          DBC             1,2
    DBC          DBC             1,2
    DBC          DBC             1,2 

    This user-loadable module writes the parameters it passed to a file called NFYEXIT.OUT. After the parameter sequence, the file contains one line:

    exit called @ bteq init.

    Example - Windows

    This example uses the MEDIUM option. The BTEQ output is not included because it is similar to the output from EXAMPLE 7.

    The NFYEXIT.OUT output file contains two lines:

    exit called @ bteq request complete
    exit called @ bteq notify out of scope: return code 0

    Example - Windows

    This example uses the HIGH option, and every event that can be used. The exit is called six times:

    exit called @ bteq init.
    	Version Id:	Notify Version 01.00.00.
    	Version ID Len:	23.
    	Utility Id:	4.
    	Utility Name:	BTEQ 14.00.00.00.
    	Utility Name Len:	16.
    	User Name:	weekly.
    	User Name Len:	6.
    exit called @ bteq start request: 'sel * from dbc.sessioninfo;'.
    exit called @ bteq request complete.
    exit called @ bteq fetch: statement 1, request 1 activity 7.
    exit called @ bteq request processing complete: 1 requests.
    exit called @ bteq notify out of scope: return code 0.

    Example - Windows

    This example uses the MSG option. The STDOUT from BTEQ is as follows:

    c:\teradata\bin>bteq
    Teradata BTEQ 04.00.01.00 for WIN32. Enter your logon or BTEQ command:
    .logon weekly
    .logon weekly
    Password:
    ***Logon successfully completed.
    ***Transaction Semantics are BTET.
    ***Character Set Name is ASCII.
    ***Total elapsed time was 41seconds.
    BTEQ -- Enter your SQL request logon or BTEQ command:
    .set notify high msg 'this is a NT msg';
    .set notify high msg 'this is a NT msg';
    BTEQ -- Enter your SQL request logon or BTEQ command:
    sel * from dbc.sessioninfo;
    sel * from dbc.sessioninfo;
    ***Query completed. 7 rows found. 15 columns returned.
    ***Total elapsed time was 9 seconds.
    UserName     AccountName     Session
    WEEKLY       DBC             1,0
    WEEKLY       DBC             1,2
    WEEKLY       DBC             1,2
    WEEKLY       DBC             1,2
    WEEKLY       DBC             1,2
    WEEKLY       DBC             1,2
    WEEKLY       DBC             1,2
    BTEQ -- Enter your SQL request logon or BTEQ command:
    quit;
    quit;
    ***You are now logged off from the DBC.
    ***Exiting BTEQ...
    ***RC (return code) = 0

    For Windows, the MSG option requires BTEQ to issue an event with a message to EventLog service. This means that the message is trapped and can be viewed by Event Viewer under Application log.