Creating Tables Using the HOLD Command

Using TABLE syntax, you can create extract files (tables) in the RDBMS. You can then use these tables, like any other RDBMS table, for both read-only and read-write operations. In fact, with the HOLD FORMAT SQL, DB2, SQLIDMS, SQLDBC, or SQLORA option, you can create RDBMS tables from any FOCUS-readable file. This feature facilitates data migration and leaves the original source unaffected.

In this section: Master Files Generated by HOLD Access Files Generated by HOLD Other Files Generated by HOLD Usage Restrictions for HOLD Extract File Conversion Charts Example: Converting the FOCUS PROD Database to a DB2 Table

In order to create RDBMS tables and indexes, you must have an adequate level of RDBMS authority. Your site must also have enabled WRITE access and native SQL support when installing the data adapter. Contact your site DBA for more information.

To extract data and convert it to an RDBMS table, issue the HOLD command with the FORMAT SQL, DB2, SQLDBC, SQLIDMS, or SQLORA option either in the report request or after the report has printed. The data adapter generates a single-table Master and Access File, and it creates and loads one RDBMS table. If the report request uses the verbs PRINT or LIST, it also creates both a FOCLIST field with internal list values and a unique index on all BY fields and the FOCLIST field. If the request uses the verb SUM, the data adapter creates a unique index on any BY fields.

If you attempt to issue the HOLD FORMAT sqlengine command without first installing the data adapter, the following error messages are generated:

(FOC1488)			SQL INTERFACE IS NOT INSTALLED

(FOC1479)			ERROR CONNECTING TO SQL DATABASE

Note: Environmental Commands, describes how to control index space parameters with the DB2, IDMS/SQL, or Oracle Data Adapter SET IXSPACE command.

The RDBMS table name that results from the extract must not already exist. (See Environmental Commands, for a discussion of data adapter environmental commands that allow you to control where the table is placed.)

Within the report request, the syntax is:

ON TABLE HOLD [ AS name ] FORMAT sqlengine

At the command level, the syntax is

HOLD [ AS name ] FORMAT sqlengine

where:

name

Is a name for the extract file; the default name is HOLD. Maximum depends on the table name length and format for each data adapter (including a period to separate the creator ID from the table name). All characters become the TABLENAME value in the Access File. On VM, The first 8 characters following the period become the resulting file description name and the FILENAME value in the Master File. On MVS, long Master File names are supported. If the HOLD command includes a long AS name, both the Master and Access File names will be long. The Master and Access Files will be named according to the procedure described in Describing Tables to FOCUS.

The AS name also becomes the table name and is specified in the Access File. If the AS name specified in the HOLD command is longer than the table name length supported by the RDBMS, the table cannot be created.

Note:

sqlengine

Identifies the type of RDBMS table created. Valid values are:

SQL or DB2 indicates that the output data source is stored as a table in either DB2 or DB2 for VM. DB2 is a synonym for SQL.

SQLDBC indicates that the output data source is stored as a Teradata table.

SQLIDMS indicates that the output data source is stored as a CA-IDMS table.

SQLORA indicates that the output data source is stored as an Oracle table.

Unless you give them an AS name, file descriptions are temporary and exist only for the current session. All HOLD tables are permanent and must be explicitly dropped.

To make the HOLD file descriptions permanent, specify an AS name in the HOLD command, and consider the following:

• On MVS, the generated Master and Access Files are created as members of the partitioned data sets allocated to DDNAMEs HOLDMAST and HOLDACC. If you do not allocate those DDNAMEs, they are allocated dynamically and deleted at the end of the session. To permanently retain the new file descriptions, allocate HOLDMAST to a permanent partitioned data set and HOLDACC to a second permanent partitioned data set. If you allocate HOLDMAST and HOLDACC, do not specify DCB parameters.
• On VM, by default the generated Master and Access Files are named HOLD MASTER and HOLD FOCSQL. They reside on the A disk by default, any writable disk specified with the FOCUS SET TEMP command, or on the writable disk with the most space.

Example: Converting the FOCUS PROD Database to a DB2 Table

This example converts the FOCUS PROD database to a DB2 table:

sql db2 set dbspace public.space0
>
table file prod
print *
on table hold as user1.prodsql format sql
end
 
    NUMBER OF RECORDS IN TABLE=       14  LINES=     14
 
 
    HOLDING SQLDS    FILE...

Master Files Generated by HOLD

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

Example: Sample Master File Generated by HOLD Creating a DB2 Table With a Long Name on OS/390

• 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 VM. On MVS 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 or DB2 for VM, SQLDBC for Teradata, SQLIDMS for IDMS, 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 fieldname 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.

Example: Sample Master File Generated by HOLD

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    ,$

Example: Creating a DB2 Table With a Long Name on OS/390

The following request creates a 15 character DB2 table named EMPLOYEEINFODB2 (18 is the maximum table name length for DB2):

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, $

Consider what happens when the AS name is longer than the 18 characters supported for table names in DB2 on OS/390:

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

Running this request generates the following messages:

>   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 EMPLOYEEINFORMATIONDB2 IS TOO
  :           LONG.  MAXIMUM ALLOWABLE SIZE IS 18
  :  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.

Access Files Generated by HOLD

The Access File resulting from the ON TABLE HOLD extract 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:

Example: Sample Access File Generated by HOLD

• The SEGNAME value is SEG01.
• The TABLENAME value defaults to HOLD. For DB2, DB2 for VM, and IDMS SQL it may be up to 27 characters (including a period). For Teradata and Oracle, it may be up to 61 characters (including a period). 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 and DB2 for VM, 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 data 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 data 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.

Example: Sample Access File Generated by HOLD

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, $

Other Files Generated by HOLD

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.

Usage Restrictions for HOLD

• 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.

Extract File Conversion Charts

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.

Reference: Extract File Conversion Chart for DB2 and DB2 for VM Extract File Conversion Chart for Teradata Extract File Conversion Chart for IDMS SQL Extract File Conversion Chart for Oracle

Reference: Extract File Conversion Chart for DB2 and DB2 for VM

Non-SQL USAGE	Conditions	HOLD USAGE	HOLD ACTUAL
An	none	An	An
Dn	none	Dn	D8
Fn	none	Fn	F4
In	n EQ 1 or 2 n GT 2 MISSING=ON	In In In	I2 I4 I4
Pn.m	n LE 31 MISSING=ON	Pn.m Pn, n£15	P(trunc((n+2)/2)) P8
date	Date format	date	DATE
TXnn	TEXT field	TXnn	TX
HYYMDm	Timestamp field	HYYMDm	HYYMDm
HHIS	Time format	HHIS	HHIS

Note:

• USAGE field types A, D, F, HYYMDm, HHIS, and TX from the original Master File remain the same; their generated ACTUAL formats vary slightly.
• USAGE field types I and P from the original Master File are converted based on the original length and on whether null values are permitted (MISSING=ON).
• USAGE values for FOCUS date formats remain the same; they are converted to the ACTUAL format DATE.
• To create SMALLINT NOT NULL columns with the CREATE FILE command, HOLD FORMAT DB2, or HOLD FORMAT SQL, use an ACTUAL attribute of I2, and then change the ACTUAL attribute to I4. In all other cases, use I4 for the ACTUAL attribute.

Reference: Extract File Conversion Chart for Teradata

Non-DBC/SQL USAGE	Conditions	HOLD USAGE	HOLD ACTUAL
An	none	An	An
Dn	none	Dn	D8
Fn	none	Dn	D8
In	n EQ 1 or 2 n GT 2 MISSING=ON	In In In	I2 I4 I4
Pn.m	n < 18 n = 18	Pn.m Pn.m	P8 P10
date	date format	date	DATE
TXnn	TEXT field	TXnn	TX

Note:

• USAGE field types I and P from the original Master File are converted based on the original length and on whether null values are permitted (MISSING=ON).
• USAGE field type F is converted to HOLD USAGE and ACTUAL formats of D.
• FOCUS date formats as USAGE values remain the same; they are converted to the ACTUAL format DATE.

Reference: Extract File Conversion Chart for IDMS SQL

Non-SQL USAGE	Conditions	HOLD USAGE	HOLD ACTUAL
An	none	An	An
Dn	none	Dn	D8
Fn	none	Fn	F4
In	n EQ 1 or 2 n GT 2 MISSING=ON	In In In	I2 I4 I4
Pn.m	n LE 31 MISSING=ON	Pn.m Pn.m, n£15	P(trunc((n+2)/2)) P8
date	Date format	date	DATE
TXnn	TEXT field	TXnn	TX

Note:

• USAGE field types A, D, F, and TX from the original Master File remain the same; their generated ACTUAL formats vary slightly.
• USAGE field types I and P from the original Master File are converted based on the original length and on whether null values are permitted (MISSING=ON).
• USAGE values for FOCUS date formats remain the same; they are converted to the ACTUAL format DATE.
• To create SMALLINT NOT NULL columns with the CREATE FILE command or HOLD FORMAT SQLIDMS, use an ACTUAL attribute of I2, and then change the ACTUAL attribute to I4. In all other cases, use I4 for the ACTUAL attribute.

Reference: Extract File Conversion Chart for Oracle

Non-SQL USAGE	Conditions	HOLD USAGE	HOLD ACTUAL
An	none	An	An
Dn	none	D20.2	D8
In	none	I11	I4
Pn.m	n LE 31 MISSING=ON	Pn.m Pn, n£15	P(trunc((n+2)/2)) P8
date	Date format	HYYMDS	H08
TXnn	TEXT field	TXnn	TX