NOTIFY Command | Basic Teradata Query - NOTIFY (NO) - Basic Teradata Query

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

The DEFAULTS command does not affect NOTIFY settings.

The current NOTIFY values are displayed by SHOW CONTROLS.

NOTIFY is not valid in an 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:

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

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

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

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

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 request
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 previous 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

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.

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.