WIDTH - 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

Specifies the printing limit for each line in a report.

Syntax



where the following is true:

n
The printing limit, which may be in bytes, characters, or printwidth spaces, depending upon the platform, charset, and possibly the REPORTALIGN setting.
The minimum allowed width is 20; the maximum is 1048575.

Usage Notes

The WIDTH command can be used after using the EXPORT command to change the width of the exported file. Upon EXPORT RESET command use, the WIDTH setting is reset to its original value, the value that was in effect before that export.

The WIDTH command can be used in a Teradata SQL macro.

For mainframe-attached systems:

  • The default value is 75 for interactive mode and 254 for batch mode.
  • The WIDTH specification relates to printable characters; it does not include carriage control characters.
  • The WIDTH specification must be equal to or less than the LRECL. The report lines are truncated if the WIDTH specification is greater than the LRECL for the destination file. For Unicode UTF-8 and UTF-16 sessions, LRECL must make available up to 4 bytes per character of the WIDTH.)
  • Setting a variable RECFM (V, VA, VB, VBA) is recommended whenever a multibyte session character set is used, since a single character could be multiple bytes.
  • Carriage control for REPORT and DIF format export files is not supported for Unicode on BTEQ running under z/OS.

For workstation-attached systems the default value is 75 for both interactive and batch modes.

Use the following table to help understand what the WIDTH value is based on for mainframe-attached systems.

Type of Output Character Set REPORTALIGN Setting WIDTH Value
SYSOUT All - Bytes
Text exports UTF8 or UTF16 COMPATIBLE Bytes
Text exports UTF8 or UTF16 EQUALWIDTH Characters
Text exports UTF8 or UTF16 COLUMNS Printwidth spaces*
Text exports Non-Unicode - Bytes
* Wide and full-width characters take up two physical spaces, while all other characters take up one physical space.

Use the following table to help understand what the WIDTH value is based on for workstation-attached systems. These values apply both for stdout and text exports.

Character Set I/O Encoding REPORTALIGN Setting WIDTH Value
UTF8 or UTF16 UTF8 or UTF16 COMPATIBLE Bytes
UTF8 or UTF16 UTF8 or UTF16 EQUALWIDTH Characters
UTF8 or UTF16 UTF8 or UTF16 COLUMNS Printwidth spaces*
Non-Unicode - - Bytes
* Wide and full-width characters take up two physical spaces, while all other characters take up one physical space.

DBS Export Width

When using BTEQ to format non-UTF-16 query results containing multibyte characters, a WIDTH setting (or the LRECL for the SYSPRINT dataset) should be specified to test the DBS Export Width rules. If the WIDTH setting is too small, the bytes for a particular character might get truncated. For example, if a multibyte string is truncated or wrapped to the next line of a report because a line is too long, the SI character might be lost, or a multibyte character might be broken. See the International Character Set Support (B035-1125) reference for further details on DBS Export Width.

Example 1 – WIDTH

The following interactive example returns the selected information first with the WIDTH specification set at the screen default (75), then with it set at 40 and 20:

database workforce;   
.defaults    
.format on    
.set foldline on all  
.set sidetitles on    
.skipline on 4    
.heading 'Width Off'    
select   deptname (title 'Department') 
,loc (title 'Loc') 
,mgrno (title 'Manager No')   
from     department 
order by loc   
;   
.defaults    
.format on   
.set foldline on   
.set sidetitles on  
.heading 'width 40'  
.width 40    
= 1   
.defaults   
.format on   
.set foldline on   
.sidetitles    
.heading 'width 20'    
.width 20   
= 1   
.defaults    
.format off   
.logoff    
.exit

BTEQ Response

*** Query completed. 5 rows found. 3 columns returned.
                       Width Off                       
Department   Engineering      Loc ATL    Manager No 10012
Department   Manufacturing    Loc CHI    Manager No 10007
Department   Marketing        Loc  NYC   Manager No 10021
Department   Administrati0n   Loc NYC    Manager No 10011
Department   Exec Office      Loc NYC    Manager No 10018
*** Query completed. 5 rows found. 3 columns returned.
   
   
                width 40                
Department  Engineering
Loc  ATL    Manager No   10012
Department  Manufacturing
Loc  CHI    Manager No   10007
Department  Marketing
Loc  NYC    Manager No   10021
Department  Administration
Loc  NYC    Manager No   10011
Department  Exec Office
Loc  NYC    Manager No   10018
   
   
*** Query completed. 5 rows found. 3 columns returned.
      width 20      
Department Engineeri
       Loc ATL   
Manager No 10012
Department Manufactu
       Loc CHI   
Manager No 10007
Department Marketing
       Loc NYC   
Manager No 10021
Department Administr
       Loc NYC   
Manager No 10011
Department Exec Offi
       Loc NYC   
Manager No 10018

If an invalid WIDTH specification is entered, BTEQ displays an error message as shown in the following example:

.set width 1048576     
*** Error:  Width value must be in the 20..1048575 range.     

Example 2 – WIDTH instigated by a macro

The Example 1 WIDTH command in a Teradata SQL macro appears as follows:

ECHO '.SET WIDTH 60';