SCOPE - Advanced SQL Engine - Teradata Database

Purpose

The SCOPE command defines the scope for subsequent COMPRESS, DEFRAGMENT, PACKDISK, SCANDISK, SHOWAMPRECOVERY, SHOWBLOCKS, SHOWCOMPRESS, SHOWCYLALLOC, SHOWFSP, SHOWSPACE, SHOWWHERE, and UNCOMPRESS commands. It defines the class of tables, range of tables, vprocs/maps (AMPs), and cylinders, or the WAL log to be used as parameters with these Ferret commands.

Each SCOPE command defines a new scope and is not a continuation of the last one.

Syntax Rules

The Ferret SCOPE syntax is unusual. The following rules apply.

IF you specify a …	THEN …
single_syntax_element	commas and spaces are not required. Parentheses are optional.
list_of_syntax_elements	each syntax element must be separated by either a comma or space. Parentheses are required.

The following are valid examples of the SCOPE command:

scope vproc 1
scope vproc (1)
scope vproc (1,3)
scope vproc (1, 3)

The following are invalid examples of the SCOPE command:

scope vproc 1,3
scope vproc 1 3

Syntax

Not all combinations of acceptable syntax are shown.

SCOPE {
  { class | cylinder | table | vproc | map } [,...] |
  ALL
}

class

CLASS {
  ( { JRNL |
      { PERMANENT | P } |
      { TEMPORARY | TEMP } |
      PSPOOL |
      SPOOL
    } [[,]...]
  ) |
  ALL
}

cylinder

{ CYLINDER | CYL } { ( cylid [[,]...] ) | cylid | ALL }

table

TABLE { ( tid [[,]...] ) | tid | ALL }

vproc

VPROC {
  vproc_number |
  ( { vproc_number | vproc_number TO vproc_number } [[,]...] ) |
  ALL
}

map

MAP { "mapname" | ( "mapname" [[,]...] ) | ALL }

CLASS

The class of tables for the scope of a subsequent command.

Classes are subtable ranges, so if a command requires tables in the recorded scope, you may specify a CLASS on the SCOPE command.

JRNL specifies the permanent journal tables containing non-visible user data.
PERMANENT specifies the permanent tables containing visible user data.
TEMPORARY specifies the temporary worktables (global temporary table instances) containing non-permanent data.
PSPOOL specifies the work tables containing non-permanent data that persist across restarts.
SPOOL specifies the intermediate worktables containing non-permanent data.
ALL specifies all the table classes in the configuration.

CYLINDER

The cylinders that are to be acted upon only by a subsequent DEFRAGMENT command. No other command uses the CYLINDER SCOPE.

A SCOPE command with CYLINDER arguments must also include VPROC arguments to be valid.

cylid specifies the cylinder ID number, a 16-character hexadecimal value.
ALL specifies all the cylinders in the vproc.

VPROC

The range of AMP vproc ID numbers, or all AMPs in the current configuration that are to be acted upon by a subsequent command.

vproc_number specifies a single AMP vprocID number or a range of numbers.
Valid AMP vproc ID numbers are from 0 to 16199. There is no default. If you type a vproc identifier that is assigned to a non-AMP vproc, Ferret issues an error.

Ferret also informs you when you select a vproc number assigned to a down AMP.
ALL specifies all the AMP vprocs in the configuration. VPROC ALL is synonymous with MAP ALL.

MAP

Similar to VPROC, however it scopes to the AMPs included in the specified contiguous maps. Map names must be enclosed in double-quotation marks. MAP ALL is synonymous with VPROC ALL.

If you try to set SCOPE to a sparse map, the effective Ferret scope is set to the range of AMPs in the parent contiguous map of the sparse map.

TABLE

The subtables that are to be acted upon by a subsequent command.

tid specifies the subtable to process.
The typeandindex component of tid is required. For more information on tid formatting, refer to Using Ferret Parameters.
ALL specifies all the subtables in the configuration.

WAL

The WAL log.

ALL

to reset SCOPE to the default startup settings of Ferret. The initial scope will consist, as appropriate to each command, of all tables, all cylinders, all vprocs, and the WAL log.

Usage Notes

The following table shows how scopes are interpreted.

Scope Type	Interpretation
Table	Specified subtables, which can be selected subtables, all subtables, or classes. Although disk space allocated for TJ and WAL records is charged against the table 0 26, no actual TJ or WAL records are found in the subtables of this table. Instead, these records are in the WAL log. The only row that exists in any subtable of the table 0, 26 is the table header in subtable 0. Table scopes imply cylinder scopes, and all-table scopes imply free CIs.
Cylinder	All the specified cylinders. Cylinder scopes can be specified by implication as subtable scopes. For the DEFRAGMENT command, this means all cylinders containing the specified subtables. An explicit cylinder specification is only meaningful to the DEFRAGMENT command. If you specify a free cylinder in CYLINDER cylid, Ferret SCANDISK will respond with an indication that the cylinder did not need to be defragmented.
Vproc	All subtables, all free CIs, and the WAL log.
Map	The AMPs associated with the specified maps.
WAL	The entire WAL log. Although disk space allocated for TJ and WAL records is charged against the table 0 26, no actual TJ or WAL records are found in the subtables of this table. Instead, these records are in the WAL log. The only row that exists in any subtable of the table 0, 26 is the table header in subtable 0.

The Ferret commands utilize SCOPE as follows, where classes are collections of tables and result in Table on AMP scopes.

Command	Description
DEFRAGMENT	The command uses Table on AMP scopes, or Cylinder on AMP or MAP scopes. The scope can contain tables only, both tables and vprocs or maps, or both vprocs/maps and cylinders. For more information, see DEFRAGMENT.
PACKDISK	The command uses AMP scopes, or Table on AMP scopes. The scope selected must include either vprocs/maps or tables, but not both. For more information, see PACKDISK.
SCANDISK	The command uses AMP scopes, or Table on AMP scopes, and WAL scope. The scope can contain tables only, vprocs/maps only, both tables and vprocs/maps, or the WAL log. For more information, see SCANDISK.
SHOWBLOCKS	The command uses Table on AMP scopes and WAL scope. The scope can contain tables only or the WAL log. For more information, see SHOWBLOCKS.
SHOWFSP	The command uses AMP scopes, or Table on AMP scopes. The scope can include one or more tables, one or more vprocs/maps, or the entire system. For more information, see SHOWFSP.
SHOWSPACE	The command uses AMP scopes, or Table on AMP scopes, and WAL scope. Although disk space allocated for TJ and WAL records is charged against the table 0 26, no actual TJ or WAL records are found in the subtables of this table. Instead, these records are in the WAL log. The only row that exists in any subtable of the table 0, 26 is the table header in subtable 0. The scope can contain tables only, vprocs/maps only, both tables and vprocs/maps, or the WAL log. For more information, see SHOWSPACE.

All scopes are not applicable to all commands. When a command is executed and the scope is not applicable to the command, either the command is rejected or the inapplicable portions of the scope are ignored.

The SHOWDEFAULTS command displays the various components of a recorded scope, which are interpreted individually by each of the commands: COMPRESS, DEFRAGMENT, PACKDISK, SCANDISK, SHOWBLOCKS, SHOWCOMPRESS,SHOWCYLALLOC, SHOWFSP, SHOWSPACE, SHOWWHERE, and UNCOMPRESS.

The scopes appear in the SHOWDEFAULTS output as tables on vprocs, cylinders on vprocs, vprocs, maps, or the WAL log. For more information, see SHOWDEFAULTS.

Example: SCOPE command examples

The following command examples are representative of how the SCOPE command is normally used:

Command	Action
Scope vproc ALL	Select all the AMPs on the system.
Scope vproc 4, vproc 6 or Scope vproc (4,6)	Select vprocs 4 and 6.
Scope Table 400H 0 400H	Select table 400H 0 400H on all vprocs.
Scope Class (P, JRNL) or Scope Class P, Class JRNL	Select all the Permanent and Journal tables on all vprocs.
Scope Class P, Class JRNL, vproc 4	Select all the Permanent and Journal tables on vproc 4.
Scope Cyl (000100000000003C 0001000000000043)	Select cylinders 000100000000003C through 0001000000000043. All cylinders specified in a single SCOPE command must belong to the same AMP.

Example: Scoping Ferret to fallback tables

Assume that table T4 is a table in database XYZ and has a table number of 0 1198.

One of the following commands would place all T4 fallback subtables under scope.
- SCOPE “TABLE XYZ.T4 F”*
- SCOPE TABLE 0 1198 F*
One of the following commands would place all T4 subtables under scope.
- SCOPE TABLE “XYZ.T4” *
- SCOPE TABLE 0 1198 *

Example: Resetting Ferret scope to the default

The following example resets the scope to the default Ferret startup settings. Some of the output is omitted to condense the example.

scope all

showd

Scope for the Defrag command is :
  All Cylinders
  On all AMP vprocs

Scope for the Packdisk command is :
  All of the AMP vprocs

Scope for the Scandisk command is :
  All of the AMP vprocs
 
Scope for the Showblocks command is :
  All tables 
  WAL Log
  On all AMP vprocs

Scope for the Showspace command is :
  All of the AMP vprocs

Scope for the Showwhere command is :
  All tables
  On all AMP vprocs

Example: Scoping Ferret to one AMP

The following example shows a whole AMP scope, which includes the WAL log.

SCOPE VPROC 1

The SCOPE has been set

Ferret => SHOWD

The current setting of the Input Radix is Decimal
The current setting of the Output Radix is Decimal

Scope for the Defrag command is :
  All Cylinders
  On AMP vproc(s) 1

Scope for the Packdisk command is :
  AMP vproc(s) 1

Scope for the Scandisk command is :
  AMP vproc(s) 1

Scope for the Showblocks command is :
  All tables
  WAL Log
  On AMP vproc(s) 1

Scope for the Showspace command is :
  AMP vproc(s) 1

Scope for the ShowFSP command is :
  AMP vproc(s) 1

Scope for the Showwhere command is :
  AMP vproc(s) 1

Example: Scoping Ferret to a map

scope map "td_map1"

The SCOPE has been set

Ferret  ==>
> showd
showd

The current setting of the Input Radix is Decimal
The current setting of the Output Radix is Decimal
Ferret is running with Script Mode -- Disabled


Scope for the Defrag command is :
   Invalid, because no tables or cylinders are specified

Scope for the Packdisk command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the Scandisk command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the Showblocks command is :
   Invalid because there are no tables or log records selected

Scope for the Showspace command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the ShowFSP command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the Showwhere command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the Showcylalloc command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the Force command is :
  Ignores the map scope set by scope command

Scope for the Showcompress command is :
   MAP TD_MAP1 ( vproc(s) 0-3 )

Scope for the [un]Compress command is :
  Ignores the map scope set by scope command

Example: Scoping Ferret to the WAL log on all AMPs

The following example shows the scope set to the WAL log on all AMPs.

Only SCANDISK, SHOWBLOCKS, SHOWSPACE, and SHOWWHERE work with this scope.

SCOPE WAL

The SCOPE has been set

Ferret => SHOWD

The current setting of the Input Radix is Decimal
The current setting of the Output Radix is Decimal

Scope for the Defrag command is :
  On all AMP vprocs

Scope for the Packdisk command is :
  On all AMP vprocs

Scope for the Scandisk command is :
  WAL Log
  On all AMP vprocs

Scope for the Showblocks command is :
  WAL Log
  On all AMP vprocs

Scope for the Showspace command is :
  WAL Log
  On all AMP vprocs

Scope for the ShowFSP command is :
  Invalid, because it contains WAL

Scope for the Showwhere command is :
  WAL Log
  On all AMP vprocs

Example: Scoping Ferret to exclude the WAL log

The following example shows the scope set to all the tables on all amps, but not the WAL log.

scope class all

showd

Scope for the Defrag command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs 
 
Scope for the Packdisk command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs 
 
Scope for the Scandisk command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs 
 
Scope for the Showblocks command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs

Scope for the Showspace command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs 

Scope for the ShowFSP command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs

Scope for the Showwhere command is :
  Table(s) 0 0 0  TO  65535 65535 65535  
  On All AMP vprocs