NOTIFY - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

This control enables users to specify a user exit or pre-defined action when certain significant events occur. NOTIFY applies only to the SQL request that immediately follows it.

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.

Syntax





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.

Building an Exit Module

When building an exit module, consider the following general procedures 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 notify header file btnfyext.h which is included in the BTEQ package. This header file is an API which must be used as-is by employing a preprocessor include directive to include its contents in site specific Notify Exit source files.

    Since the data structures defined in this file are also used by BTEQ code, any mismatch in the definitions could lead to unexpected behavior. Make sure to use the same version of this header file which is distributed with the target BTEQ version.

  • z/OS BTEQ can use both SAS/C compiled and IBM/C compiled Notify EXIT modules.
  • When using 64-bit BTEQ with Notify Exit modules, the Notify modules also need to be compiled in 64-bit mode.

The following table summarizes how to build exit modules using different operating systems.

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's install provides three types of files to create exit modules: a sample C file, the header file required by the sample C file, and a makefile. The respective files are located in the following location:

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

For a 64-bit module:

Compile the module with the following options:

  • -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's install provides three type of files to create exit modules: a sample C file, the header file required by the sample C file, and a 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_64

To run the sample makefile, enter the following command:

make -f mkbtnfyext.linux_64

For a 32-bit module:

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's install provides three type of files to create exit modules: a sample C file, the header file required by the sample C file, and a 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's install provides three types of files to create exit modules: a sample C file, the header file required by the sample C file, and a makefile. The respective files are located 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's install provides three types of files to create exit modules: a sample C file, the header file required by the sample C file, and a 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
(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's install provides three types of files to create exit modules: a sample C file, the header file required by the sample C file, and a 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 files in the SAMPLIB PDS:
  • BTNFYXTC – sample Notify Exit C file
  • BTNFYEXT – the 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 z/OS Examples for NOTIFY, under NOTIFY Examples.

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 z/OS Examples for NOTIFY, under NOTIFY Examples.

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's install provides three types of files to create exit modules: a sample C file, the header file required by the sample C file, and a makefile. The respective files are located in the following locations:

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

NOTIFY Examples

UNIX Examples for NOTIFY

Example 1 – UNIX

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 2 – UNIX

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 3 – UNIX

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 4 – UNIX

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.

z/OS Examples for NOTIFY

Example 1 – 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 2 – 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 3 – z/OS

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

Assuming that the following PDSs exist:
  • USER.SAMPLIB
  • USER.TEXTM
  • USER.OBJM
  • USER.LOADM

Also assuming 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 4 – 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
//*

Windows Examples for NOTIFY

Example 1 – 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 2 – 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 3 – 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 4 – 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.