The Director's Chair
		Advanced Technique
		FOCUS 101
		New Technique
by Walter Blood		Feature Preview
		Feature
		User Group Meeting Schedule

Structured HOLD Files: Smarter Is Better and Coming Soon What Are Structured HOLD Files? With existing FOCUS HOLD mechanisms, you capture the requested data from the original sources and perhaps, as in the case of FOCUS files, some new structure added in your request. But you lose all information about data structures and relationships in the original data sources. Structured HOLD Files will enable you to capture the same set of data without losing the information about the structural relationships between the original data sources. How Will Structured HOLD Files Be Important to Me? Consider, for example, running a large complex report request on your mainframe against a number of different data sources that you joined together. Suppose your end user likes it and asks you to run the same report, perhaps with minor adjustments, against a subset of that data. With Structured HOLD Files you can easily modify the report, making the requested changes and then schedule, run, and deliver it without returning to the original data sources. Additionally, because the structural information about the original data sources is retained in Structured HOLD files, the original generating request can be used to report from these files. This means that you can deliver the original request along with the subset of data to your end users and let them modify the original report request to generate whatever additional analysis, specialized formatting, or additional subsets of the output that they need. This will be especially useful for existing reports that you need to move to WebFOCUS. Suppose you were asked to produce a series of reports on sales figures for the last two years: By salesperson across fiscal quarter By product line across selling branch By delivering warehouse across fiscal quarter By product across fiscal quarter By product across delivering warehouse You could generate these reports individually from your mainframe data and then format and burst them individually, forwarding each to the appropriate individual(s) in your organization. Or, you could use Structured HOLD Files to deliver the data required for creating all of these reports to the same group of users, and let them use WebFOCUS to generate precisely the reports, graphs, or spreadsheets they need, applying whatever sort and selection criteria they wish. This approach is only possible when the data extract you provide also retains the structures and relationships of the original source data. In our example, for instance, the original data probably looked something like this: BRANCH DATA ------------- | PRODUCT DATA -------------------- SALES DATA | INVENTORY DATA ---------- While data about the organization, the products, and inventory management may not have been related initially, each category was related to individual sales events. With normal HOLD files, structures like this are flattened, producing records that look like this: BRANCH DATA PRODUCT DATA INVENTORY DATA SALES DATA This makes it almost impossible to reconstruct the original source structure in order to view it in a different way – say, with a different set of sort criteria, or different selection criteria, perhaps based on defined fields. By retaining structural information about the original data source(s), Structured HOLD Files provide far greater flexibility for manipulating the data, even after the data has been extracted from the original sources. How Do I Create a Structured HOLD file? Structured HOLD Files, like all other HOLD files, are created from TABLE requests. The syntax (note that syntax described herein is proposed and subject to change) for Structured HOLD Files is: ON TABLE HOLD EXTRACT [AS filename] FORMAT [format type] …where format type indicates the type of file being created: FOCUS/XFOCUS files (HFOC) Alphanumeric (HSA) sequential files Binary (HSB) sequential files Relational tables (HREL) xml (HSX) files The choice depends upon your reporting or application requirements. FOCUS syntax is generally interpreted the same way whether outputting to normal HOLD files or Structured HOLD Files, which enables use of the same request that created a Structured HOLD File to report off it. Proposed rules for Structured HOLD Files are as follows: Any field referenced in a TABLE request will be included in the Structured HOLD file except one referenced only in the selection criteria. (IF and WHERE and filters). Defined fields in the Master File or request will be treated as real fields in Structured HOLD Files. Information about how they were created will not be retained. COMPUTEs will not be included in Structured HOLD Files but all fields required to evaluate them will be retained. All fields that are not rolled-up or aggregated will be denoted in a single PRINT clause. Sorts are triggered with BY phrases, but sort behavior is slightly different. All formatting commands are ignored when creating Structured HOLD Files, as are operations requiring the internal matrix, such as IF TOTAL and WHERE TOTAL tests. Reformatting of fields in verb object lists is also not included as they are treated as COMPUTEs. How Are Aggregations and Roll-Ups Handled in Structured HOLD Files? Aggregations or roll-ups are generated in TABLE with the SUM or WRITE command. With Structured HOLD Files, we are adding a new control to allow specification of the level at which aggregation should occur. SUM field1AS fld1 field2 AS fld2 WITHIN fielda This specifies that aggregation of field1 and field2 in whatever segment they reside in will occur at the segment level that includes fielda. The aggregated value will be stored at the level of fielda with fieldnames indicated in the AS phrases (fld1, fld2). To create roll-ups at different levels, you simply specify SUM clauses with WITHIN phrases pointing to different segments. Rolled-up fields can employ most verb object operators, including AVE, FST, LST, CNT, MAX, and MIN. The percentage operators RPT, and PCT, and PCT.CNT are not available and will be ignored within Structured HOLD Files. DST. will also be ignored by the Structured HOLD process. Fields in SUM clauses are not retained in their original segments unless they are also referenced in a PRINT clause. How Is Sorting Different When Using Structured HOLD Files? With Structured HOLD Files, you are creating extracts and sorting in this domain refers to ordering multiple child segments within a single parent instance. Thus the major-to-minor sort sequence within a segment is the sequence of that segment's BY fields in the generating request. Given fields A and B, in a segment, for example, the expressions: BY A BY B and BY B BY A will produce two differently sorted output files. If A and B were in two different segments, the sorted outputs would be the same, since the structure would be retained in a Structured HOLD File. Also, with normal HOLD files, the BY phrases eliminate duplicates as long as the verb used is not PRINT. Since sorting is different in Structured HOLD files, we have added a new (NODUPES) keyword to suppress duplicates. BY field1 BY field2 NODUPES With NODUPES, the first instance is always retained. Descendants of duplicates are not lost, but are simply appended to the instance retained. Finally, duplicates will not be eliminated in segments with roll-up WITHIN fields, in which case, the NODUPES command is simply ignored to ensure that all rolled-up values are included. What Else Will Structured HOLD Files Allow Me to Do? Additional functionalities of Structured HOLD Files, including the transformation syntax and the structure of the files created will be addressed in the second installment of this article in the next TABLE FILE CAR, so stay tuned! When Will I Be Able to Use Structured HOLD Files? Delivery plans for this feature will be announced at Summit at the Mainframe Roundtable and attendees can get additional details in the Vendor room. We'll summarize the Summit announcements in our next issue.