Creating a Structured HOLD File

How to:

Reference:

Structured HOLD Files facilitate migration of data sources and reports between operating environments.

Other HOLD formats capture data from the original sources and may retain some implicit structural elements from the request itself. However, they do not propagate most of the information about the original data sources accessed and their inter-relationships to the HOLD Master File or data source. Structured HOLD files, however, extract the data to a structure that parallels the original data sources. Subsequent requests against the HOLD file can use these retained data relationships to recreate the same types of relationships in other environments or in other types of data sources.

A Structured HOLD File can be created in ALPHA, BINARY, or FOCUS format:

In all cases the HOLD file contains all of the original segment instances required to provide the complete report based on the TABLE request itself. Regardless of the display command used in the original request (PRINT, LIST, SUM, COUNT), the Structured HOLD File is created as if the request used PRINT. Aggregation is ignored.

The HOLD file contains either all of the fields in the structure identified by the request that are used to satisfy the request, or all of the display fields and BY fields. The file does not contain DEFINE fields not specifically referenced in the request. It does contain all fields needed to evaluate any DEFINE fields referenced in the request.

Structured HOLD files are only supported for TABLE and TABLEF commands. They can be created anywhere a HOLD file is supported. You must activate Structured HOLD files in a specific request by issuing the ON TABLE SET EXTRACT command in the request prior to creating the Structured HOLD File.

Syntax: How to Activate Structured HOLD Files for a Request 

ON TABLE SET EXTRACT {ON|*|OFF}

where:

ON
Activates Structured HOLD Files for this request and extracts all fields mentioned in the request.
*
Activates Structured HOLD Files for this request and indicates that a block of extract options follows. For example, you can exclude specific fields from the Structured HOLD File. For information, see Specify Options for Generating Structured HOLD Files.
OFF
Deactivates Structured HOLD files for this request. OFF is the default value.

Syntax: How to Create a Structured HOLD File

Before issuing the HOLD command, activate Structured HOLD Files for the request by issuing the ON TABLE SET EXTRACT command described in Activate Structured HOLD Files for a Request. Then issue the HOLD command to create the Structured HOLD File: 

[ON TABLE] {HOLD|PCHOLD} [AS name] FORMAT {ALPHA|BINARY|FOCUS}

where:

name
Is the name of the HOLD file. If omitted, the name becomes HOLD by default.
FORMAT
Is ALPHA, BINARY or FOCUS.

Note: You can issue a SET command to set the default HOLD format to either ALPHA or BINARY: 

SET HOLDFORMAT=ALPHA
SET HOLDFORMAT=BINARY

Syntax: How to Specify Options for Generating Structured HOLD Files

To specify options for creating the extract, such excluding specific fields, use the * option of the SET EXTRACT command: 

ON TABLE SET EXTRACT *
EXCLUDE = (fieldname1, fieldname2, fieldname3 , ..., fieldnamen),$
FIELDS={ALL|EXPLICIT},$
ENDEXTRACT
ON TABLE HOLD AS name FORMAT {ALPHA|BINARY|FOCUS}

where:

EXCLUDE=(fieldname1, fieldname2, fieldname3,..., fieldnamen)
Excludes the specified fields from the HOLD file.
,$
Is required syntax for delimiting elements in the extract block.
ALL
Includes all real fields and all DEFINE fields that are used in running the request.
EXPLICIT
Includes only those real fields and DEFINE fields that are in the display list or the BY sort field listing. DEFINE fields that are not explicitly referenced, and fields that are used to evaluate DEFINEs, are not included.
ENDEXTRACT
Ends the extract block.

Example: Creating a Structured HOLD File in ALPHA Format 

TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME JOBCODE ED_HRS
BY DEPARTMENT
BY HIGHEST SALARY
ON TABLE SET EXTRACT ON
ON TABLE HOLD FORMAT ALPHA
END

This request produces the following HOLD Master File: 

FILENAME=HOLD    , SUFFIX=FIX     , $
  SEGMENT=EMPINFO, SEGTYPE=S0, $
    FIELDNAME=RECTYPE, ALIAS=R, USAGE=A3, ACTUAL=A3, $
    FIELDNAME=LAST_NAME, ALIAS='LN', USAGE=A15, ACTUAL=A15, $
    FIELDNAME=FIRST_NAME, ALIAS='FN', USAGE=A10, ACTUAL=A10, $
    FIELDNAME=DEPARTMENT, ALIAS='DPT', USAGE=A10, ACTUAL=A10, $
    FIELDNAME=ED_HRS, ALIAS='OJT', USAGE=F6.2, ACTUAL=A06, $
  SEGMENT=PAYINFO, SEGTYPE=S0, PARENT=EMPINFO, $
    FIELDNAME=RECTYPE, ALIAS=1, USAGE=A3, ACTUAL=A3, $
    FIELDNAME=SALARY, ALIAS='SAL', USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=JOBCODE, ALIAS='JBC', USAGE=A3, ACTUAL=A03, $

Note the RECTYPE field generated for ALPHA or BINARY Structured HOLD files. Each record in the HOLD file begins with the RECTYPE to indicate the segment to which it belonged in the original structure. The root segment has RECTYPE=R. The RECTYPEs for other segments are sequential numbers assigned in top to bottom, left to right order.

Following are the first several records in the HOLD file: 

R  STEVENS        ALFRED    PRODUCTION 25.00
1      11000.00A07
1      10000.00A07
R  SMITH          MARY      MIS        36.00
1      13200.00B14
R  JONES          DIANE     MIS        50.00
1      18480.00B03
1      17750.00B02
R  SMITH          RICHARD   PRODUCTION 10.00
1       9500.00A01
1       9050.00B01

Example: Creating a Structured HOLD File in TIBCO FOCUS Format 

TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME JOBCODE ED_HRS
BY DEPARTMENT
BY HIGHEST SALARY
ON TABLE SET EXTRACT ON
ON TABLE HOLD FORMAT FOCUS
END

This request produces the following HOLD Master File: 

FILENAME=HOLD    , SUFFIX=FOC     , $
  SEGMENT=EMPINFO, SEGTYPE=S0, $
    FIELDNAME=LAST_NAME, ALIAS='LN', USAGE=A15, $
    FIELDNAME=FIRST_NAME, ALIAS='FN', USAGE=A10, $
    FIELDNAME=DEPARTMENT, ALIAS='DPT', USAGE=A10, $
    FIELDNAME=ED_HRS, ALIAS='OJT', USAGE=F6.2, $
  SEGMENT=PAYINFO, SEGTYPE=S0, PARENT=EMPINFO, $
    FIELDNAME=SALARY, ALIAS='SAL', USAGE=D12.2M, $
    FIELDNAME=JOBCODE, ALIAS='JBC', USAGE=A3, $

Example: Reconstituting a Structured HOLD File

The following request reconstitutes the original FOCUS data source from the Structured HOLD File created in the example named Creating a Structured HOLD File in ALPHA Format: 

TABLE FILE HOLD
PRINT LAST_NAME FIRST_NAME JOBCODE ED_HRS
BY DEPARTMENT
BY HIGHEST SALARY
ON TABLE SET EXTRACT ON
ON TABLE HOLD AS RECONST FORMAT FOCUS
END

This request produces the following Master File: 

FILENAME=RECONST    , SUFFIX=FOC     , $
  SEGMENT=EMPINFO, SEGTYPE=S0, $
    FIELDNAME=LAST_NAME, ALIAS='LN', USAGE=A15, $
    FIELDNAME=FIRST_NAME, ALIAS='FN', USAGE=A10, $
    FIELDNAME=DEPARTMENT, ALIAS='DPT', USAGE=A10,
    FIELDNAME=ED_HRS, ALIAS='OJT', USAGE=F6.2, $
  SEGMENT=PAYINFO, SEGTYPE=S0, PARENT=EMPINFO, $
    FIELDNAME=SALARY, ALIAS='SAL', USAGE=D12.2M, $
    FIELDNAME=JOBCODE, ALIAS='JBC', USAGE=A3, $

The following request prints the report output: 

TABLE FILE RECONST
PRINT LAST_NAME FIRST_NAME JOBCODE ED_HRS
BY DEPARTMENT
BY HIGHEST SALARY
END

The output is:

DEPARTMENT           SALARY  LAST_NAME        FIRST_NAME  JOBCODE  ED_HRS
----------           ------  ---------        ----------  -------  ------
MIS              $27,062.00  CROSS            BARBARA     A17       45.00
                 $25,775.00  CROSS            BARBARA     A16       45.00
                 $21,780.00  BLACKWOOD        ROSEMARIE   B04       75.00
                 $18,480.00  JONES            DIANE       B03       50.00
                             MCCOY            JOHN        B02         .00
                 $17,750.00  JONES            DIANE       B02       50.00
                 $13,200.00  SMITH            MARY        B14       36.00
                  $9,000.00  GREENSPAN        MARY        A07       25.00
                  $8,650.00  GREENSPAN        MARY        B01       25.00
PRODUCTION       $29,700.00  BANNING          JOHN        A17         .00
                 $26,862.00  IRVING           JOAN        A15       30.00
                 $24,420.00  IRVING           JOAN        A14       30.00
                 $21,120.00  ROMANS           ANTHONY     B04        5.00
                 $16,100.00  MCKNIGHT         ROGER       B02       50.00
                 $15,000.00  MCKNIGHT         ROGER       B02       50.00
                 $11,000.00  STEVENS          ALFRED      A07       25.00
                 $10,000.00  STEVENS          ALFRED      A07       25.00
                  $9,500.00  SMITH            RICHARD     A01       10.00
                  $9,050.00  SMITH            RICHARD     B01       10.00

Example: Excluding Fields From Structured HOLD Files

This request excludes the SALARY field used for sequencing. 

TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME JOBCODE ED_HRS
BY DEPARTMENT
BY HIGHEST SALARY
ON TABLE SET EXTRACT *
EXCLUDE=(SALARY),$
ENDEXTRACT
ON TABLE HOLD FORMAT FOCUS
END

This request produces the following HOLD Master File: 

FILENAME=HOLD   , SUFFIX=FOC     , $
  SEGMENT=EMPINFO, SEGTYPE=S0, $
    FIELDNAME=LAST_NAME, ALIAS='LN', USAGE=A15, $
    FIELDNAME=FIRST_NAME, ALIAS='FN', USAGE=A10, $
    FIELDNAME=DEPARTMENT, ALIAS='DPT', USAGE=A10, $
    FIELDNAME=ED_HRS, ALIAS='OJT', USAGE=F6.2, $
  SEGMENT=PAYINFO, SEGTYPE=S0, PARENT=EMPINFO, $
    FIELDNAME=JOBCODE, ALIAS='JBC', USAGE=A3, $

Reference: Elements Included in a Structured HOLD File

Structured HOLD files contain all original segment instances required to complete the TABLE or TABLEF request. Regardless of the display command used in the original request (PRINT, LIST, SUM, or COUNT), the structured HOLD file will be created as if the command was PRINT.

Specifically, the extract file contains the following elements:

  • All real fields named in the request such as display objects, sort fields, and fields used in selection criteria (WHERE/IF tests).

    Note that fields referenced multiple times in a request are included only once in the HOLD file.

  • Fields used in FILTER FILE condition.
  • Prefix operators are ignored except for ALL. (which just affects the amount of data collected and does not imply a calculation).
  • Field based reformatting (FIELD1/FIELD2=) causes the original field and the format field to be included.
  • A GROUP field if referenced explicitly or when all of its members are referenced in the request.

    Note: If a group member is specifically excluded (EXCLUDE) or not referenced, its GROUP is not added to the extract Master File (this applies to nested and overlapping groups, as well). If a GROUP and its elements are all named in a request, the GROUP is not added as a real field in the extract HOLD file.

  • For FIELDS=ALL, all DEFINE fields used in the request become real fields in the structured HOLD File and are included along with other fields used in the DEFINE expression (including other DEFINE fields). Use EXCLUDE to reduce the number of fields included in the EXTRACT output.
  • For FIELDS=EXPLICIT, display objects and sort fields are included. DEFINE fields become real fields if referenced in the request, but fields used to create them will not be included unless referenced explicitly. This reduces the number of fields returned in the request.

Reference: Elements Not Included in a Structured HOLD File

  • Prefix operators on WHERE fields are evaluated in data selection but not included in the extract output.
  • Prefix operators on display objects are ignored (except ALL).
  • Using Structured HOLD File syntax in MATCH, MORE, and GRAPH requests produces error messages and exits the procedure.
  • WHERE/IF TOTAL tests are not supported in Structured HOLD File requests and result in cancellation of the request.
  • Reformatting of real fields is ignored (only the real field is included).
  • Computed fields are not included, but fields used in COMPUTE expressions are included in the extract file.

Reference: Structural and Behavioral Notes

  • Structured HOLD File requests are subject to the same limitations on number and size of fields as any other TABLE request.

Structural Notes

  • The following SET parameters are turned off or ignored in Structured HOLD File requests:
    • AUTOINDEX
    • AUTOPATH
    • AUTOSTRATEGY
    • EXTHOLD
    • EXTSORT
    • HOLDATTR
    • All SET and ON TABLE SET commands used to control output format are ignored in creating the extract file.
  • Alternate views are respected and reflected in the structure of the extract file.
  • Indexed views specified in the request are respected and reflected in the structure of the output file.
  • If a request would generate a file containing two independent orphan segments because the parent segment is specifically excluded, a dummy system segment is created in the to act as parent of the two unrelated segments. There is only one instance of data for that segment. Both orphan segments refer to that system segment as parent. If the parent is missing because it was not mentioned in the request, it is activated during the request and included as the parent the segments.
  • In the event that two unique (U) segments are included without the parent segment, the unique segments are converted to segments with SEGTYPE S0 that reference the system segment as parent.
  • JOIN and JOIN WHERE structures are supported.

SQL Optimization Notes

  • SQL optimization for aggregation must be turned off for EXTRACT requests.

BY/ACROSS/FOR Notes

  • BY and ACROSS sort fields become additional display objects.
  • BY. . .ROWS and ACROSS . . .COLUMNS function only as implicit WHEREs to limit field values included.
  • FOR fields are included.
  • RECAP fields are excluded (like COMPUTEs).
  • Summarization fields referencing previously identified fields are ignored in creating Structured HOLD Files. These include: SUMMARIZE, RECOMPUTE, SUBTOTAL, SUB-TOTAL ACROSS-TOTAL, ROW-TOTAL and COLUMN-TOTAL.

Formatting Notes

  • Structured HOLD File processing ignores all formatting elements, including: IN, OVER, NOPRINT, SUP-PRINT, FOLD-LINE, SKIP-LINE, UNDER-LINE, PAGE-BREAK, TABPAGENO, AS, and column title justification. However, fields referenced within formatting commands, such as HEADING, FOOTING, SUBHEAD, and SUBFOOT, and any WHEN expressions associated with them, are included.
  • All STYLE and STYLESHEET commands are ignored in producing extract output.
  • AnV and AnW fields are supported. TX fields are exported in FOCUS files only.
  • In the event that the FIELDNAME and ALIAS are the same for a real field and that field is redefined as itself (possibly with a different format), two fields are created in the HOLD Master File with identical field names and aliases. In this situation, the second version of the field can never be accessed if referenced by name. You can use FIELDS=EXPLICIT to include only the second version of the field. The following DEFINE illustrates and example of creating a duplicate field name and alias:
    DEFINE FILE CAR
COUNTRY/A25=COUNTRY;
END

DBA Notes

  • DBA controls on source files are respected when running Structured HOLD File requests with the exception of RESTRICT=NOPRINT, where fields named in a request are not displayed (such fields cannot be exported and should be specifically EXCLUDED).
  • DBA restrictions do not carry over to the HOLD Master File.

Reconstituting Extract Files

  • To reconstitute a FOCUS or flat file from a Structured HOLD file, you use the same syntax used to generate the Structured HOLD File. The ON TABLE SET EXTRACT syntax must be used to preserve multipath structures.
  • All reconstituted FOCUS segments are SEGTYPE=S0, as neither KEY nor INDEX information is retained. An INDEX can be reinserted using REBUILD INDEX.