Even if the original Master File describes several segments, the Master File resulting from the ON TABLE HOLD command is a single-table description. It contains the attributes described in Describing Tables to FOCUS. Following is a list of the generated keyword/valuepairs:

• The default FILENAME value is HOLD. If you specified a name with the AS phrase, the FILENAME value is the first eight characters of that name. On z/OS, longer Master File names are supported (see Describing Tables to FOCUS). If the name contains a period (.), the characters preceding the period are treated as the creator id. Characters following the period become the resulting file description name and FILENAME value in the Master File.
• The SUFFIX value is SQLDS for DB2, SQLDBC for Teradata, SQLIDMS for IDMS/SQL, or SQLORA for Oracle.
• The SEGNAME value is SEG01 and the SEGTYPE value is S0.
• FIELDNAME and ALIAS values from the original Master File are retained. If the original Master File does not define aliases, the original field names are also the new ALIAS values.

  If the report request contains an AS phrase to rename a field, the AS phrase name becomes the new value for FIELDNAME and the original field name becomes the new value for ALIAS. The AS phrase name is also included as a TITLE value.

  The FOCUS PRINT and LIST commands create an additional field named FOCLIST that contains internal list values.

• The resulting USAGE field formats are generally the same as the original USAGE formats. The new ACTUAL formats are converted based on original USAGE formats and certain conditions (see the charts in Extract File Conversion Chart for DB2 and DB2 for VM). If the original Master File contains ACTUAL formats, they are ignored.
• MISSING parameter settings are converted to SQL NULL statements.

The following is the generated Master File from the ON TABLE HOLD AS PRODSQL command in Converting the FOCUS PROD Database to a DB2 Table:

FILE=PRODSQL    ,SUFFIX=SQLDS,$
SEGNAME=SEG01   ,SEGTYPE=S0,$
FIELDNAME   =FOCLIST      ,FOCLIST     ,I5       ,I4    ,$
FIELDNAME   =PROD_CODE    ,PCODE       ,A3       ,A3    ,$
FIELDNAME   =PROD_NAME    ,ITEM        ,A15      ,A15   ,$
FIELDNAME   =PACKAGE      ,SIZE        ,A12      ,A12   ,$
FIELDNAME   =UNIT_COST     ,COST        ,D5.2M    ,D8    ,$

The following request creates a 15-character DB2 table named EMPLOYEEINFODB2:

TABLE FILE EMPLOYEE
PRINT EMP_ID CURR_SAL
BY DEPARTMENT
BY LAST_NAME
BY FIRST_NAME
ON TABLE HOLD AS USER1.EMPLOYEEINFODB2 FORMAT DB2
END

This request creates the following Master File:

$ VIRT=EMPLOYEEINFODB2
 
FILE=EMPLOYEEINFODB2       ,SUFFIX=SQL
SEGNAME=SEG01   ,SEGTYPE=S0
FIELDNAME   ='DEPARTMENT'       ,'DPT'         ,A10      ,A10      ,$
FIELDNAME   ='LAST_NAME'        ,'LN'          ,A15      ,A15      ,$
FIELDNAME   ='FIRST_NAME'       ,'FN'          ,A10      ,A10      ,$
FIELDNAME   =FOCLIST            ,FOCLIST       ,I5       ,I4       ,$
FIELDNAME   ='EMP_ID'           ,'EID'         ,A9       ,A9       ,$
FIELDNAME   ='CURR_SAL'          ,'CSAL'        ,D12.2M   ,D8        ,$

This request also creates the following Access File. The AS name is also the table name:

$ VIRT=EMPLOYEEINFODB2
SEGNAME=SEG01   ,
TABLENAME=USER1.EMPLOYEEINFODB2
KEYS=04  , WRITE=YES, $

When the AS name is longer than the supported length for table names in DB2 on z/OS, the following messages are generated:

>   NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 HOLDING SQL      FILE...
(FOC1400) SQLCODE IS -107 (HEX: FFFFFF95)
(FOC1414) EXECUTE IMMEDIATE ERROR.

The Master and Access Files are created. However, the table cannot be created because the table name specified in the Access File is too long and, therefore, invalid. Note that you will get a more descriptive message if you issue the following command:

SQL DB2 SET ERRORTYPE DBMS

For example:

(FOC1400) SQLCODE IS -107 (HEX: FFFFFF95)
  :  DSNT408I SQLCODE = -107, ERROR:  THE NAME name IS 
TOO
  :           LONG.  MAXIMUM ALLOWABLE SIZE IS xx  :  DSNT418I SQLSTATE   = 42622 SQLSTATE RETURN CODE
  :  DSNT415I SQLERRP    = DSNHSMUD SQL PROCEDURE DETECTING ERROR
  :  DSNT416I SQLERRD    = 0  0  0  -1  15  0 SQL DIAGNOSTIC INFORMATION
  :  DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
  :           X'FFFFFFFF'  X'0000000F'  X'00000000' SQL DIAGNOSTIC
  :           INFORMATION
(FOC1414) EXECUTE IMMEDIATE ERROR.

The Access File resulting from the ON TABLE HOLD command contains the declarations described in Describing Tables to FOCUS. For PRINT and LIST based reports, the FOCLIST field and the BY phrases determine the KEYS value and how the index is created. Following is a list of the generated keyword/value pairs:

• The SEGNAME value is SEG01.
• The TABLENAME value defaults to HOLD. If you specified a name with the AS phrase, that name becomes the TABLENAME value. If the name contains a period (.), the characters preceding it are considered the creator name for DB2, the schema name for IDMS, and the user ID for Oracle. Characters following the period become the table name.

  Note: For DB2, Teradata, and Oracle, if you do not specify a creator name, the ID specified by the SET OWNERID command becomes the creator. If you did not issue this command, your user ID becomes the creator, by default. For IDMS, an unqualified table name will be generated.

• The KEYS value and the fields that are indexed depend on the display command and whether BY sort phrases are used in the request:
  • If the request specifies the NOPRINT option for a BY phrase, that sort field is not included in the table or the calculation of the KEYS value, and it is not indexed.
  • All PRINT (or LIST) requests generate a FOCLIST field. SUM (or COUNT) requests do not.
  • If the request specifies an aggregate verb such as SUM (or COUNT) with printed BY phrases, the KEYS value equals the number of printed sort fields.
  • If the request specifies the command PRINT (or LIST) with printed BY phrases, the KEYS value equals the number of printed sort fields plus one for the generated FOCLIST field.
  • If there are no BY phrases in the request, the KEYS value is 01 regardless of the display command.
  • The adapter creates a unique index on the printed BY fields plus the FOCLIST field (if it exists). If there are no BY fields, for a PRINT (or LIST) request, the adapter creates a unique index on FOCLIST alone. For a SUM (or COUNT) request, it creates a unique index on the first column referenced in the request.
• The WRITE value is YES.

Following is the generated Access File from the ON TABLE HOLD AS PRODSQL statement in Converting the FOCUS PROD Database to a DB2 Table:

SEGNAME=SEG01   ,
TABLENAME="USER1".PRODSQL  ,
KEYS=01  , WRITE=YES, $

Three work files, FOC$HOLD MASTER, FOC$HOLD FOCTEMP, and FOCSORT, are used with an internal MODIFY procedure to create and load the output table. The FOC$HOLD Master File is a fixed-format file with a corresponding sequential data file.

• You may not use the HOLD FORMAT sqlengine option in multi-verb TABLE requests. Only one display command is allowed.
• You may not use the ACROSS sort phrase.
• Names used in AS phrases to rename fields should begin with an alphabetic character. If the name begins with a digit, SQL error -105 or SQL/DBC error 3708 results and the table is not generated. For more information on AS names, refer to your FOCUS documentation.
• The CNT.DST. prefix operator is not supported.

The following charts show original USAGE formats, conditions, and resulting USAGE and ACTUAL formats for ON TABLE HOLD. The field length is represented by n.

You can create a report output file, that is, a HOLD file, as a native DBMS temporary table. This increases performance by keeping the entire reporting operation on the DBMS server, instead of downloading data to your computer and then back to the DBMS server.

For example, if you temporarily store report output for immediate use by another procedure, storing it as a temporary table instead of creating a standard HOLD file avoids the overhead of transmitting the interim data to your computer.

The temporary table columns are created from the following report elements

• Display columns
• Sort (BY) columns
• COMPUTE columns

except for those for which NOPRINT is specified.

The temporary table that you create from your report will be the same data source type (that is, the same DBMS) as the data source from which you reported. If the data source from which you reported contains multiple tables, all must be of the same data source type and reside on the same instance of the DBMS server.

You can choose between several types of table persistence.

You can create extract files as native DBMS tables with the following adapters:

• DB2 (on z/OS, UNIX, and Windows)
• Oracle
• Teradata

ON TABLE HOLD [AS filename]FORMAT SAME_DB [PERSISTENCE persistValue]