Defining Hashed File Output Data

When you extract data from a hashed file, the Hashed File stage has an output link. The properties of this link and the column definitions of the data are defined on the Outputs page in the Hashed File Stage dialog box.

The Outputs page has the following two fields and three tabs:

• Output name. The name of the output link. Choose the link you want to edit from the Output name list. This list displays all the output links from the Hashed File stage.
• Normalize on. This list allows you to normalize (or unnest) data. You can normalize either on an association or on a single unassociated multivalued column. The Normalize on list is only enabled for nonreference output links where metadata has been defined that contains multivalued fields.
• General. Displayed by default. Contains the following fields and options:
  • File name. The name of the file the data is read from. You can use a job parameter to represent the file created during run time or choose the file from the File name list. This list contains all the files defined in the Table Definitions > Hashed►Account name folder in the repository, where Account name is the name of the account chosen on the Stage page. If the file you want is not listed, you need to define a table definition.
  • Record level read. Select this to force the file to be read record by record. This is slower, but is necessary if you want to read and write the hashed file at the same time. If you specify a select statement on the Selection tab, the file is read at the record level anyway and this check box is selected but grayed out.
  • Pre-load file to memory. You can use these options to improve performance if the output link is a reference input to a Transformer stage. If you select Enabled, the hashed file is read into memory when the job is run (Disabled is selected by default). The remaining two options are for specialist use and cater for situations where you need to modify a lookup table as a job runs. If Enabled, Lock for Updates is selected the hashed file is read into memory when the job is run. If a lookup is not found in memory, the job looks in the file on disk. If the lookup is still not found, an update lock is taken in the knowledge that the record will subsequently be written in the hashed file by this job. The operation for Disabled, Lock for Updates is similar, except that the hashed file is not read into memory.
  • Description. Contains an optional description of the output link.
• Columns. Contains the column definitions for the data on the chosen output link.

  You should be aware of the following issues when outputting data from a hashed file:

  • Key fields should be identified by selecting the Key boxes. (If you fail to do this be warned that the first column will be treated as the key, which might lead to undesired results).
  • By default other columns are ordered according to their position in the file. You can also use the hashed file stage to reorder columns as they are read in. Do this by specifying the column order in the Position field. The columns will then be written to the output link in that order, although they retain the same column names. If you use this feature you should identify the key column or columns by setting their Position field to 0.
  • Do not reorder the column definitions in the grid unless you are certain you understand the consequences of your action. You should be especially wary of using the Position field to reorder columns and then saving the definition as a table definition in the repository for subsequent reuse. In particular, if you use this column definition to write to the same hashed file, you will be reordering the file itself.
  • You can output an entire record as a single column if required. Do this by inserting a value of -1 in the Position field of the record field's column definition. (The key column Position field should be 0.)
• Selection. Contains optional SELECT clauses for the conditional extraction of data from a file. This tab is only available if you have specified the hashed file by account name, rather than directory path, on the Stage page.

Click View Data... to open the Data Browser. This enables you to look at the data associated with the output link.

If you intend to read and write from a hashed file at the same time, you must either set up a selection on the Selection tab, or you should select the Record level read check box on the General tab. This ensures the file is read in records rather than in groups, and that record locks can operate. Note, however, that this mode of operation is much slower and should only be used when there is a clear need to read and write the same file at the same time.