CURRENT SPUFI DEFAULTS panel

Use the CURRENT SPUFI DEFAULTS panel to specify SPUFI default values.

The following descriptions explain the information on the CURRENT SPUFI DEFAULTS panel.

1 SQL TERMINATOR

Specify the character that you use to end each SQL statement. You can specify any character except the characters listed in the following table. A semicolon (;) is the default SQL terminator.

Table 1. Invalid special characters for the SQL terminator
Name	Character	Hexadecimal representation
blank		X'40'
comma	,	X'5E'
double quote	"	X'7F'
left parenthesis	(	X'4D'
right parenthesis	)	X'5D'
single quote	'	X'7D'
underscore	_	X'6D'

Use a character other than a semicolon if you plan to execute a statement that contains embedded semicolons. For example, suppose you choose the character # as the statement terminator. Then a CREATE TRIGGER statement with embedded semicolons looks like the following statement:

CREATE TRIGGER NEW_HIRE
  AFTER INSERT ON EMP
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
  END#

A CREATE PROCEDURE statement with embedded semicolons looks like the following statement:

CREATE PROCEDURE PROC1 (IN PARM1 INT, OUT SCODE INT) 
  LANGUAGE SQL                                       
  BEGIN                                              
    DECLARE SQLCODE INT;                             
    DECLARE EXIT HANDLER FOR SQLEXCEPTION            
      SET SCODE = SQLCODE;                           
    UPDATE TBL1 SET COL1 = PARM1;                    
  END #

Be careful to choose a character for the SQL terminator that is not used within the statement.

You can also set or change the SQL terminator within a SPUFI input data set by using the --#SET TERMINATOR statement.

2 ISOLATION LEVEL

Specify the isolation level for your SQL statements.

3 MAX SELECT LINES

The maximum number of rows that a SELECT statement can return. To limit the number of rows retrieved, enter an integer greater than 0.

4 ALLOW SQL WARNINGS

Enter YES or NO to indicate whether SPUFI will continue to process an SQL statement after receiving SQL warnings:

YES: If a warning occurs when SPUFI executes an OPEN or FETCH for a SELECT statement, SPUFI continues to process the SELECT statement.
NO: If a warning occurs when SPUFI executes an OPEN or FETCH for a SELECT statement, SPUFI stops processing the SELECT statement. If SQLCODE +802 occurs when SPUFI executes a FETCH for a SELECT statement, SPUFI continues to process the SELECT statement.

You can also specify how SPUFI pre-processes the SQL input by using the --#SET TOLWARN statement.

5 CHANGE PLAN NAMES

If you enter YES in this field, you can change plan names on a subsequent SPUFI defaults panel, DSNESP07. Enter YES in this field only if you are certain that you want to change the plan names that are used by SPUFI. Consult with your Db2 system administrator if you are uncertain whether you want to change the plan names. Using an invalid or incorrect plan name might cause SPUFI to experience operational errors or it might cause data contamination.

6 SQL FORMAT

Specify how SPUFI pre-processes the SQL input before passing it to Db2. Select one of the following options:

SQL: This is the preferred mode for SQL statements other than SQL procedural language. When you use this option, which is the default, SPUFI collapses each line of an SQL statement into a single line before passing the statement to Db2. SPUFI also discards all SQL comments.
SQLCOMNT: This mode is suitable for all SQL, but it is intended primarily for SQL procedural language processing. When this option is in effect, behavior is similar to SQL mode, except that SPUFI does not discard SQL comments. Instead, it automatically terminates each SQL comment with a line feed character (hex 25), unless the comment is already terminated by one or more line formatting characters. Use this option to process SQL procedural language with minimal modification by SPUFI.
SQLPL: This mode is suitable for all SQL, but it is intended primarily for SQL procedural language processing. When this option is in effect, SPUFI retains SQL comments and terminates each line of an SQL statement with a line feed character (hex 25) before passing the statement to Db2. Lines that end with a split token are not terminated with a line feed character. Use this mode to obtain improved diagnostics and debugging of SQL procedural language.

You can also specify how SPUFI pre-processes the SQL input by using the --#SET SQLFORMAT statement.

7 SPACE UNIT

Specify how space for the SPUFI output data set is to be allocated.

TRK: Track
CYL: Cylinder

8 PRIMARY SPACE

Specify how many tracks or cylinders of primary space are to be allocated.

9 SECONDARY SPACE

Specify how many tracks or cylinders of secondary space are to be allocated.

10 RECORD LENGTH

The record length must be at least 80 bytes. The maximum record length depends on the device type you use. The default value allows a 32756-byte record.

Each record can hold a single line of output. If a line is longer than a record, the output is truncated, and SPUFI discards fields that extend beyond the record length.

11 BLOCKSIZE

Follow the normal rules for selecting the block size. For record format F, the block size is equal to the record length. For FB and FBA, choose a block size that is an even multiple of LRECL. For VB and VBA only, the block size must be 4 bytes larger than the block size for FB or FBA.

12 RECORD FORMAT

Specify F, FB, FBA, V, VB, or VBA. FBA and VBA formats insert a printer control character after the number of lines specified in the LINES/PAGE OF LISTING field on the DB2I Defaults panel. The record format default is VB (variable-length blocked).

13 DEVICE TYPE

Specify a standard z/OS® name for direct-access storage device types. The default is SYSDA. SYSDA specifies that z/OS is to select an appropriate direct access storage device.

14 MAX NUMERIC FIELD

The maximum width of a numeric value column in your output. Choose a value greater than 0. The default is 33.

15 MAX CHAR FIELD

The maximum width of a character value column in your output. DATETIME and GRAPHIC data strings are externally represented as characters, and SPUFI includes their defaults with the default values for character fields. Choose a value greater than 0. The IBM®-supplied default is 250.

16 COLUMN HEADING

You can specify NAMES, LABELS, ANY, or BOTH for column headings.

NAMES uses column names only.
LABELS (default) uses column labels. Leave the title blank if no label exists.
ANY uses existing column labels or column names.
BOTH creates two title lines, one with names and one with labels.

Column names are the column identifiers that you can use in SQL statements. If an SQL statement has an AS clause for a column, SPUFI displays the contents of the AS clause in the heading, rather than the column name. You define column labels with LABEL statements.