SHOW CONTROLS - 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

Prints current values for one or all BTEQ settings to the standard output stream.

Syntax



where:

<cmd> is
CHARSET LOGONPROMPT RTITLE
DECIMALDIGITS MAXERROR RUN
ECHOREQ MESSAGEOUT SEPARATOR
ENCRYPTION NOTIFY SESSION CHARSET
ERRORLEVEL NULL SESSION RESPBUFLEN
EXPORT OMIT SESSION SQLFLAG
EXPORTEJECT PACK SESSION TRANSACTION
FOLDLINE PAGELENGTH SESSION TWORESPBUF
FOOTING PREPAREMODE SESSIONS
FORMAT PAGEBREAK SIDETITLES
FORMCHAR QUIET SKIPDOUBLE
FULLYEAR RECORDLENGTH SKIPLINE
HEADING RECORDMODE STATEMENTINDEPENDENCE
IMPORT REPEAT SUPPRESS
INDICATORMODE REPEATSTOP TDP
LARGEDATAMODE REPORTALIGN TIMEMSG
LIMITS RETCANCEL TITLEDASHES
LOGMECH RETLIMIT UNDERLINE
LOGON RETRY WIDTH

Usage Notes

Use the SHOW CONTROLS command to verify the BTEQ format settings that users control. (The QUIET command does not suppress the SHOW CONTROLS display.)

The command SHOW, used without any options, displays the last SQL statement.

The SHOW CONTROLS command can be used in a Teradata SQL macro.

Optionally, the SHOW CONTROLS command can be used at the settings level.

For example, SHOW CONTROLS <cmd>; where "cmd" can be any setting that is reflected in the show controls as [SET] <cmd> = <value>.

The keyword "LIMITS" can be used at the settings level with the SHOW CONTROLS command. The output lists predefined defaults, minimums and maximums for applicable settings like Maximum Sessions or Maximum Instruction Bytes. Some items expose database limits, for which "NA" is displayed if a database connection has not yet been established.

The line “Client Platform Byte Order” in the output of the SHOW CONTROLS command depicts the endianness type of the client machine. The key phrase “byte order” used with the SHOW CONTROLS command displays the output at the settings level.

Example 1 – SHOW CONTROLS

To see the current value of all controls, specify:

.SHOW CONTROLS

BTEQ Response

Mainframe- and workstation-attached systems return different information.

Following is an example of the information returned when using BTEQ for z/OS:

.show controls ;
  Maximum Instruction Bytes           = 1048500                                 
  Maximum Record Bytes                = 1024000                                 
  Maximum Request Buffer Bytes        = 7340032                                
  Maximum Response Buffer Bytes       = 16775168 
  Maximum Packed Records              = 64000                               
  Activity Count Bytes                = 8                                       
  Maximum Sessions                    = 200                                     
  Maximum Repeat Factor               = 2147483647                              
  Maximum String Length               = 254                                     
  Maximum Report Columns              = 65535                                   
  Maximum WITH Clauses for a Report   = 10                                      
  Maximum Report Title Lines          = 10                                      
  Maximum Report Page Lines           = 2147483407                              
  Minimum Report Width                = 20                                      
  Maximum Report Width                = 1048575                                 
                                                                                
  Maximum Notify MSG Text Bytes       = 254                                     
  Maximum Bytes Saved for SUPPRESS,                                             
  PAGEBREAK, SKIPLINE, UNDERLINE,                                               
  and SKIPDOUBLE Commands, and for                                              
  &n Substitutions                    = 256                                     
                                                                                
  Client Platform Byte Order          = BIG ENDIAN                              
                                                                                
  EXPORT  RESET                                                                 
  IMPORT  RESET                                                                 
  LOGMECH                 = default                                             
  LOGON   
  MESSAGEOUT  RESET                                                          
  REPEAT                  = 1                                                   
  RUN                                                                           
  [SET] AUTOKEYRETRIEVE   = OFF
  [SET] BRANCHMSG         = VERBOSE                                                 
  [SET] DECIMALDIGITS     = 0 (SPB DEFAULT)                                     
  [SET] ECHOREQ           = ON                                                  
  [SET] ENCRYPTION        = OFF                                                 
  [SET] ERRORLEVEL        = ON                                                  
  [SET] ERROROUT          = STDOUT                                              
  [SET] EXPORTEJECT       = ON                                                  
  [SET] FOLDLINE          = OFF ALL                                             
  [SET] FOOTING           = NULL                                                
  [SET] FORMAT            = OFF                                                 
  [SET] FORMCHAR          = DEFAULT                                             
  [SET] FULLYEAR          = OFF                                                 
  [SET] HEADING           = NULL                                                
  [SET] INDICATORMODE (alias INDICDATA) = OFF                                   
  [SET] LARGEDATAMODE     = OFF                                                 
  [SET] MAXERROR          = OFF                                                 
  [SET] NOTIFY            = OFF                                                 
  [SET] NULL              = ?                                                   
  [SET] OMIT              = OFF ALL                                             
  [SET] PACK              = 0                                                   
  [SET] PAGEBREAK         = OFF ALL                                             
  [SET] PAGELENGTH        = 55                                                  
  [SET] PREPAREMODE       = OFF                                                 
  [SET] QUIET             = OFF                                                 
  [SET] RECORDLENGTH      = Import/Export: MAX64  SYSPRINT: MAX1MB              
  [SET] RECORDMODE        = OFF                                                 
  [SET] REPEATSTOP        = OFF
  [SET] REPORTALIGN       = EQUALWIDTH                                             
  [SET] RETCANCEL         = OFF                                                 
  [SET] RETLIMIT          = Rows: No Limit  Columns: 100                        
  [SET] RETRY             = ON                                                  
  [SET] RTITLE            = NULL                                                
  [SET] SEPARATOR         = two spaces                                          
  [SET] SESSION CHARSET        = EBCDIC                                         
        import/export encoding = EBCDIC                                         
        stdin/stdout encoding  = EBCDIC                                         
  [SET] SESSION RESPBUFLEN  = 32767                                             
  [SET] SESSION SQLFLAG   = NONE                                                
  [SET] SESSION TRANSACTION = BTET                                              
  [SET] SESSION TWORESPBUFS = ON                                                
  [SET] SESSIONS          = 1                                                   
  [SET] SIDETITLES        = OFF for the normal report.                          
        And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
  [SET] SKIPDOUBLE        = OFF ALL                                             
  [SET] SKIPLINE          = OFF ALL                                             
  [SET] STATEMENTINDEPENDENCE = OFF                                             
  [SET] SUPPRESS          = OFF ALL                                             
  [SET] TDP               = NULL                                                
  [SET] TIMEMSG           = DEFAULT                                             
  [SET] TITLEDASHES       = ON for the normal report.                           
        And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
  [SET] TRIMTRAILINGSPACES = ON                                                 
  [SET] UNDERLINE         = OFF ALL                                             
  [SET] WIDTH             = 75

Reseting Defaults by Logging Off

The LOGON and LOGOFF commands do not reset the format command default values. If logging off from the initial BTEQ session and then using another LOGON command, the resulting sessions inherit the format command values from the prior session. The DEFAULTS command must be used to reset the format commands to their initial default values.

The only times the format commands are at the values listed above are when the first LOGON command is executed after invoking BTEQ, and when the DEFAULTS command is used.

Example 2 – SHOW CONTROLS instigated by a macro

The Example 1 SHOW CONTROLS command in a Teradata SQL macro for a workstation-attached system appears as:

ECHO '.SHOW CONTROLS';

*** Echo accepted.
*** Total elapsed time was 1 second.

Maximum Instruction Bytes           = 1048500
Maximum Record Bytes                = 1024000
Maximum Request Buffer Bytes        = 7340032
Maximum Response Buffer Bytes       = 16775168
Maximum Packed Records              = 64000
Activity Count Bytes                = 8
Maximum Sessions                    = 200
Maximum Repeat Factor               = 2147483647
Maximum String Length               = 254
Maximum Report Columns              = 65535
Maximum WITH Clauses for a Report   = 10
Maximum Report Title Lines          = 10
Maximum Report Page Lines           = 2147483647
Minimum Report Width                = 20
Maximum Report Width                = 1048575

Maximum Notify MSG Text Bytes       = 254
Maximum Bytes Saved for SUPPRESS
PAGEBREAK, SKIPLINE, UNDERLINE
and SKIPDOUBLE Commands, and for
n Substitutions                     = 256

Client Platform Byte Order          = LITTLE ENDIAN

EXPORT  RESET
IMPORT  RESET
LOGMECH                 = default
LOGON
MESSAGEOUT  RESET                              
REPEAT                  = 1
RUN
[SET] AUTOKEYRETRIEVE   = OFF
[SET] BRANCHMSG         = VERBOSE
[SET] DECIMALDIGITS     = 0 (SPB DEFAULT)
[SET] ECHOREQ           = OFF
[SET] ENCRYPTION        = OFF
[SET] ERRORLEVEL        = ON
[SET] ERROROUT          = STDERR
[SET] EXITONDELAY       = OFF
[SET] FOLDLINE          = OFF ALL
[SET] FOOTING           = NULL
[SET] FORMAT            = OFF
[SET] FORMCHAR          = OFF
[SET] FULLYEAR          = OFF
[SET] HEADING           = NULL
[SET] INDICATORMODE (alias INDICDATA) = OFF
[SET] LARGEDATAMODE     = OFF
[SET] LOGONPROMPT       = ON
[SET] MAXERROR          = OFF
[SET] NOTIFY            = OFF
[SET] NULL              = ?
[SET] OMIT              = OFF ALL
[SET] PACK              = 0
[SET] PAGEBREAK         = OFF ALL
[SET] PAGELENGTH        = 55
[SET] PREPAREMODE       = OFF
[SET] QUIET             = OFF
[SET] RECORDLENGTH      = Import/Export: MAX64  Stdout: MAX1MB
[SET] RECORDMODE        = OFF
[SET] REPEATSTOP        = OFF
[SET] REPORTALIGN       = COMPATIBLE
[SET] RETCANCEL         = OFF
[SET] RETLIMIT          = Rows: No Limit  Columns: 100
[SET] RETRY             = ON
[SET] RTITLE            = NULL
[SET] SEPARATOR         = two spaces
[SET] SESSION CHARSET        = ASCII
      import/export encoding = ASCII
      stdin/stdout encoding  = ASCII
[SET] SESSION RESPBUFLEN  = 8192
[SET] SESSION SQLFLAG   = NONE
[SET] SESSION TRANSACTION = BTET
[SET] SESSION TWORESPBUFS = ON
[SET] SESSIONS          = 1
[SET] SIDETITLES        = OFF for the normal report.
      And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
[SET] SKIPDOUBLE        = OFF ALL
[SET] SKIPLINE          = OFF ALL
[SET] STATEMENTINDEPENDENCE = OFF
[SET] SUPPRESS          = OFF ALL
[SET] TDP               = dbc
[SET] TIMEMSG           = DEFAULT
[SET] TITLEDASHES       = ON for the normal report.
      And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
[SET] TMSMMSG           = OFF
[SET] TRIMTRAILINGSPACES = ON
[SET] UNDERLINE         = OFF ALL
[SET] WIDTH             = 75