SYSREC and SYSRED data sets

UNLOAD PLUS always requires a SYSREC data set.

This output data set contains the unloaded rows. If you specify multiple data sets, you must specify the SYSRECnn form of the data set name. You may optionally specify a second data set (SYSRED) or second group of data sets (SYSREDnn). SYSRED data sets must have a one-to-one correspondence with your SYSREC data sets.

For information about using multiple output data sets, see Using JCL to specify multiple unload data sets.

Important

To change the installation default ddname or ddname prefix SYSREC or SYSRED, use the UNLOADDN installation or command option.

If you specify more than 99 data sets, use the UNLOADDN command option to override the default data set name of SYSREC or SYSRED, specifying a ddname prefix that results in eight characters or less after UNLOAD PLUS appends the highest data set number.

You can allocate SYSREC and SYSRED files in one of the following ways:

Have UNLOAD PLUS dynamically allocate the data sets. For more information, see Using dynamically allocated unload data sets.

Explicitly specify SYSREC and SYSRED DD statements in your JCL. Use this option when you want to control the allocation of these data sets.

We recommend that you do not combine dynamically allocated SYSREC data sets with SYSRED data sets that are allocated in your JCL.

Instead of unloading LOB or XML data directly to unload data sets (SYSREC and SYSRED), you can unload it to files that are referenced in your unload data sets. These referenced files can be partitioned data sets (PDSs), extended partitioned data sets (PDSEs), or hierarchical file systems (HFSs). In this case, your unload data sets contain the unloaded rows only from the base table.

UNLOAD PLUS always allocates PDS or PDSE referenced files dynamically. UNLOAD PLUS incorporates the OUTPUT statement and its associated options, as well as the corresponding installation options, to support dynamic allocation of these data sets to disk. (These data sets cannot be allocated to tape.) You cannot allocate these data sets in your JCL, but you can use the output descriptors to control the allocation. You must ensure that you specify the SPACE and DIR options on the OUTPUT statement to provide UNLOAD PLUS with data set sizing information. ANALYZE processing is not available for these data sets.

For HFS referenced files, UNLOAD PLUS generates the files dynamically, but the file system must be preallocated.

You must use OUTPUT statements to enable UNLOAD PLUS to dynamically allocate PDS or PDSE data sets or HFS files. For more information about specifying options to allocate these files, see Considerations when unloading LOB or XML data to referenced files.

When specifying DIRECT YES, you can unload LOB and XML data to multiple referenced files. When determining the number of referenced files to use, consider the following information:

You can define any one of the following configurations of referenced files:

One file for each LOB and XML column

One file per base table space partition for each LOB and XML column

One file per subset of base table space partitions for each LOB and XML column

A referenced file can contain data for only one LOB or XML column.

Use the following information when allocating unload data sets in your JCL.

If you are allocating a single SYSREC and SYSRED data set, you can determine the data set size if you calculate the PRIQTY as A, and the SECQTY as B - A where

The following considerations apply to allocating SYSREC and SYSRED data sets:

If you specify SPANNED YES to unload LOB or XML data to a VBS data set, do not specify SYSOUT=* on your SYSREC DD statement.

When you use a single SELECT statement on a partitioned table space, we recommend that you specify an unload data set for each partition to obtain optimal performance.

You cannot stack multiple SYSREC or SYSRED data sets on a single tape; UNLOAD PLUS opens all SYSREC and SYSRED data sets at the start of unload processing.

UNLOAD PLUS issues an informational message if you allocated the SYSREC or SYSRED data set with DISP=MOD, but the utility continues processing. UNLOAD PLUS honors the DISP=MOD specification, and appends the unloaded data to existing data in SYSREC.

UNLOAD PLUS terminates if you allocate a block size that is greater than MVS allows.

The BLKSIZE calculations for the SYSREC and SYSRED data sets must be equal.

Unless you specify one of the following command options, UNLOAD PLUS calculates the optimal record format, record size, and block size and overrides any data control block attributes that you specify in your JCL:

USELRECL, which overrides the record format and record size

SPANNED YES, which overrides the record format and record size

RECFM VB, which overrides the record format

MAXBLKSIZE, which overrides the block size

Important

Creating multiple unload data sets depends on the following factors:

The type of table space (partitioned or nonpartitioned) that UNLOAD PLUS is unloading

The number of SELECT statements in the UNLOAD command

The number of SYSREC DD statements that you specify in the JCL

To unload each partition of a partitioned table space to a separate data set, perform the following steps:

Specify a single SELECT statement in the UNLOAD command to unload the partitioned table space.

Specify a separate DD statement in the JCL for each partition.

Important

The following table describes examples of configurations for unloading partitions to separate data sets:

Example

Description

The ddname that you specify for each partition must follow the format SYSRECnn, where SYSREC is the default prefix for the unload data set and nn is the partition number. The first example illustrates unloading a partitioned table space with each partition being unloaded to a separate data set. UNLOAD PLUS unloads any partitions for which you do not specify SYSREC DD statements in the JCL to the default data set, SYSREC.

2

The second example illustrates overriding the default name, unloading two partitions to the default data set (MYDD), and writing the remaining two partitions to separate data sets.

3

The third example illustrates unloading all partitions of a partitioned table space to a single data set. In this case, you supply a single DD statement that matches the default ddname. You may use a ddname of SYSREC or SYSREC1.

4

The fourth example illustrates unloading all partitions of a partitioned table space to a single SYSREC that is directed to tape.

You can use one or multiple unload data sets when you specify multiple SELECT statements in an UNLOAD command. Specify DD statements for some or all of the SELECT statements. SELECT statements are numbered sequentially, beginning with the number 1. Specify a ddname with the format SYSRECnn, where nn is the numerical position of the SELECT statement.

The following table describes the examples of configurations for specifying multiple SELECT statements:

Example

Description

1

The first example illustrates unloading several tables within a single table space with each SELECT statement unloaded to a separate data set.

UNLOAD PLUS unloads a SELECT statement for which you do not specify SYSREC DD statements in the JCL to the default data set, SYSREC.

2

The second example illustrates unloading two SELECT statements to a single data set, and unloading two SELECT statements to separate data sets.

To unload all SELECT statements to a single data set, specify a single DD statement that matches the default ddname. You may use a ddname of SYSREC.

3

The third example illustrates unloading several tables to a single data set.

Identical rules govern data sets that you allocate using JCL DD statements and those that UNLOAD PLUS allocates using dynamic allocation and output descriptors. UNLOAD PLUS can allocate or use a secondary unload data set only when it has a corresponding primary unload data set. For example, if a default primary unload data set exists and UNLOAD PLUS uses it for all SELECT statements, UNLOAD PLUS dynamically allocates only a single default secondary unload data set. If you specify a primary unload data set for each SELECT statement, UNLOAD PLUS can dynamically allocate a secondary unload data set for each SELECT statement.

When you use the UNLOADDN and OUTPUT commands and assign an output descriptor to a SELECT statement for the primary and secondary unload data sets, the data set name determines how many data sets UNLOAD PLUS creates for that SELECT statement. The primary and secondary unload data sets must resolve to the same number of data sets.

You can activate dynamic allocation of unload data sets in the installation options module or in the command syntax:

Use one of the following methods to activate dynamic allocation:

To set the default for activating dynamic allocation of your primary and secondary unload data sets, specify UNLOADDN_ACTIVE=(YES,YES) in your installation options.

To activate dynamic allocation for primary and secondary unload data sets by using command options, specify UNLOADDN(primary,secondary) ACTIVE(YES,YES).

If either of the following conditions exists, specify either the ESTROWS, LIMIT, or SPACE command option to provide UNLOAD PLUS with data set sizing information:

DIRECT NO is in effect.

You specify the INFILE option.

Important

The SPACE installation option does not provide this information.

In all other cases, UNLOAD PLUS uses ANALYZE processing to obtain data set sizing information for dynamic allocation.

Ensure that the installation options in your $ADUOPTS macro are appropriate for your unload data sets, or use the OUTPUT command option overrides.

For example, using symbolic variables can simplify construction of data set names for dynamically allocated data sets.

If you specify dynamic allocation, the unload data sets are not associated with a DD statement that allows a subsequent LOAD step to use traditional load control cards. LOADPLUS accepts actual data set names in load control cards. When you specify CNTLCARDS BMCLOAD, UNLOAD PLUS generates INDSN(DSN,...,DSN).

If the dynamically allocated unload data set is a generation data group (GDG), UNLOAD PLUS assigns the explicit cataloged data set name.

When you use dynamic allocation with UNLOAD PLUS, symbolic variables can simplify the task of constructing data set names, as the following example shows:

Example

When you specify a primary or secondary unload data set name in an output descriptor, you can use symbolic variables to specify any or all nodes of a data set name. For a list of variables that you can use, see the description of the DSNAME command option.

The previous example showed combining a real node name with symbolic variables. The following example shows using symbolic variables for all nodes:

Example

In the following example, UNLOAD PLUS writes the data in each partition to a separate data set ('USER.P001', 'USER.P002', 'USER.P003'):

Example

You must prefix symbols for numeric variables with an alphabetic character. In the following example, the first statement is incorrect and the second statement is correct.

Incorrect:

Example

Correct:

Example

Although you can prefix a symbolic variable with an alphabetic character, you cannot append characters. For example, XX&TS is valid, but &TSXX is invalid. &TS.XX is valid.

To use symbolic variables to specify GDG data set names, append the generation number in parentheses. For example, you can use &TS(+1).

Substitution variables in the DSNAME keyword can cause UNLOAD PLUS to generate multiple files for an OUTPUT descriptor name, especially when you specify the &PART substitution variable in the DSNAME keyword for SELECT statements that unload a partitioned table.

If multiple SELECT statements share the same output descriptor that results in a single data set name, the statements share the same unload data set. Only SELECT statements that share the same dynamic allocation specification can resolve to the same data set name. If two SELECT statements each use a different output descriptor and their data set names resolve to the same real value, UNLOAD PLUS issues an error message and terminates.

Important

If you specify the nonsuffixed SYSREC DD in the JCL, UNLOAD PLUS does not dynamically allocate data sets. All SELECT statements that do not match a suffixed DD match the nonsuffixed DD.

You can use multiple output descriptors when you specify multiple SELECT statements in an UNLOAD PLUS SYSIN command stream. Specify output descriptors for some or all of the SELECT statements, which are numbered sequentially, beginning with the number 1. Specify output descriptors with the format SYSRECnn, where nn is the number of the SELECT statement that you want to match. If you do not specify a SYSRECnn, UNLOAD PLUS uses the output descriptor SYSREC if you provide it in an OUTPUT statement, or UNLOAD PLUS uses the defaults that the installation options provide.

See the following section for examples and illustrations that show how to match SELECT statements and unloaded data sets and how to use the &PART and &SELNUM substitution variables in output descriptors.

The following examples 1 through 5 describe various methods for specifying JCL when UNLOAD PLUS uses dynamic allocation.

Example 1: No JCL DD statements: multiple SELECT statements with different output descriptors

Example

UNLOAD PLUS writes SELECT statement 1 from TABLE1 and SELECT statement 2 from TABLE2 to the dynamically allocated data set 'USER.SYSREC' that OUTPUT SYSREC specifies. UNLOAD PLUS writes the third SELECT statement to the dynamically allocated data set 'USER.SYSREC03' that OUTPUT SYSREC03 specifies.This example includes multiple SELECT statements that have different output descriptors. The JCL does not include DD statements. Example 1 in Dynamic allocation examples illustrates the results.

Example 2a: No JCL DD statements: multiple SELECT statements with identical output descriptor

Example

This example includes multiple SELECT statements that have an identical output descriptor. The JCL does not include DD statements. This example demonstrates how to combine all of the SELECT statements into a single primary unload data set when UNLOAD PLUS is using dynamic allocation. UNLOAD PLUS writes all SELECT statements to the dynamically allocated data set 'USER.SYSREC'. Example 2a in Dynamic allocation examples illustrates this scenario.

Furthermore, UNLOAD PLUS can write each SELECT statement to a separate dynamically allocated primary unload data set. In the following example, UNLOAD PLUS writes each SELECT statement to separate data sets named 'USER.S001','USER.S002', and 'USER.S003'. The substitution variable &SELNUM (SELECT number) allows UNLOAD PLUS to create multiple unload data sets. Example 2b in Dynamic allocation examples illustrates this scenario.

Example 2b: No JCL DD statements: multiple SELECT statements using the &SELNUM variable

Example

Example 3: No JCL DD statements: single SELECT statement and using the &PART variable

Example

This example has only one SELECT statement and includes the &PART symbolic variable in the data set name specified on the OUTPUT statement. The JCL does not include DD statements. UNLOAD PLUS writes the data in each partition to a separate data set ('USER.P001', 'USER.P002', 'USER.P003'). Example 3 in Dynamic allocation examples illustrates this scenario.

Example 4a: Mixture of JCL and dynamic allocation: multiple SELECT statements and using the &PART variable

Example

This example includes a combination of JCL and dynamic allocation. Multiple SELECT statements are combined with the &PART variable. Example 4a illustrates this scenario.

UNLOAD PLUS assigns SELECT statement 1 from PART3TB to SYSREC01, which has a JCL-allocated DD statement. UNLOAD PLUS writes SELECT statement 2 from NONPARTTB to data set 'USER.P000', which is dynamically allocated.

An additional example reverses the order of the SELECT statements to demonstrate that SELECT statement 2 produces more than one dynamically allocated data set. Example 4b in Dynamic allocation examples illustrates this scenario.

Example 4b: Mixture of JCL and dynamic allocation: multiple SELECT statements and using the &PART variable

Example

UNLOAD PLUS assigns SELECT statement 1 from NONPARTB to SYSREC01, which has a JCL-allocated DD statement. No dynamic allocation is necessary for this SELECT statement.

UNLOAD PLUS writes SELECT statement 2 from PART3TB to dynamically allocated data sets 'USER.P001', 'USER.P002', 'USER.P003' (one for each partition that UNLOAD PLUS is unloading).

Example 5: Mixture of JCL and dynamic allocation: multiple SELECT statements with the &SELNUM variable

Example

This example includes a combination of JCL and dynamic allocation using multiple SELECT statements. The output descriptor includes the &SELNUM variable. Example 5 in Dynamic allocation examples illustrates this scenario.

UNLOAD PLUS writes SELECT statement 1 from TABLE1 to dynamically allocated data set 'USER.SYSREC.S001', SELECT statement 2 from TABLE2 to dynamically allocated data set 'USER.SYSREC.S002', and SELECT statement 3 to the data set to which the SYSREC03 DD in the JCL points.

Dynamic allocation examples

During dynamic allocation, UNLOAD PLUS uses your installation options and UNLOAD command options to choose a method for calculating the data set size of the unload data sets. UNLOAD PLUS uses the following priorities when determining the method:

If you specify SPACE on the OUTPUT statement, UNLOAD PLUS uses the SPACE parameters as you have specified them.

If you specify LIMIT, UNLOAD PLUS derives the total number of records per data set and calculates the data set size and space that it needs for allocating the data sets.

If you specify ESTROWS on the SELECT statement, UNLOAD PLUS uses your estimate to calculate the data set size.

If you specify ANALYZE DB2STATS or if you have specified the ANALYZE installation option, UNLOAD PLUS uses information about the table or partition that is available from the Db2 catalog to calculate the size of the unload data sets.

If you have not specified the SPACE, LIMIT, ESTROWS, or ANALYZE DB2STATS options, UNLOAD PLUS uses the table size or partition high-used RBA (HURBA) to allocate the unload data sets. For more information, see the description for the ANALYZE HURBA option.

If you do not specify the SPACE option, you can use the FILESZPCT command or installation option to adjust the total calculated space. See FILESZPCT for more details.

The following examples show how this hierarchy works. The examples use the following table information:

Table

Number of records

Statistics age

1

100

2

2

100

10

3

100

1

4

100

20

Dynamic allocation example 1

Example

In example 1, UNLOAD PLUS allocates:

10 cylinders of space to SYSREC01 because the OUTPUT command has a specified value for SPACE

Space for 50 records to SYSREC02 because an estimate for the number of rows is specified, but SYSREC02 does not have an assigned value for SPACE and LIMIT is not specified in the UNLOAD command

Space for 100 records (obtained from the Db2 catalog information) to SYSREC03 because SYSREC03 does not specify a value for SPACE, LIMIT, or an estimated number of rows, and the catalog information is current (age less than the specified 10 days)

Space based on the table space size (HURBA) to SYSREC04 because the statistics information for Table 4 in this example is 20 days old, and the catalog information is not current

Dynamic allocation example 2

Example

In example 2, the output from the first SELECT statement will be in the SYSREC01 data set, but the output from the other SELECT statements will be in the SYSREC data set. UNLOAD PLUS allocates

10 cylinders of space to SYSREC01 because a value is specified for SPACE

Space for 60 records to SYSREC because the LIMIT value is 20 and SYSREC contains the unloaded output for the other three tables

Dynamic allocation example 3

Example

In example 3, UNLOAD PLUS uses the table space size to allocate space for SYSREC, because the JCL does not specify a value for SPACE, LIMIT, or ESTROWS and ANALYZE HURBA was specified.

UNLOAD PLUS can detect when the storage management subsystem (SMS) will manage a data set, and adjusts the primary and secondary allocations to make optimum use of the available DASD space to reduce abends that space limitations can cause. The SMS-managed space calculations and allocation parameters differ from those that SMS does not manage. These differences are as follows:

UNLOAD PLUS always allocates SMS-managed data sets in tracks, regardless of the allocation type that you specify on the OUTPUT statement or in the installation options module.

SMS routines in UNLOAD PLUS do not handle striped data sets. UNLOAD PLUS uses the non-SMS routines for allocating those data sets.

UNLOAD PLUS allocates space in megabyte increments when the required space is greater than 1 megabyte, or in kilobyte increments when the required space is less than 1 megabyte. The smallest allocation that UNLOAD PLUS requests for tracks is 1 kilobyte.

You can specify the value of MAXPRIM in either tracks or cylinders. UNLOAD PLUS converts that value to tracks, using the default geometry specified in the SMS base configuration.

When determining the number of tracks to allocate, UNLOAD PLUS uses the following method and priorities for the maximum record size and the number of required records for the data set allocation:

If you specify LIMIT, UNLOAD PLUS sums the limit value for each table space, partition, and SELECT statement that UNLOAD PLUS will direct to the data set.

If you specify ESTROWS on a SELECT statement and the table space is partitioned, UNLOAD PLUS proportions the value of ESTROWS to each partition that is unloaded and sums the proportioned values for each partition that is directed to this data set. If the input is from an image copy (that is, you specified INFILE in the command options), UNLOAD PLUS evenly distributes the value of ESTROWS among the partitions that it is unloading.

If the table is partitioned, UNLOAD PLUS sums the number of rows for each partition that is being unloaded for each SELECT statement that is directed to this data set.

UNLOAD PLUS sums the total table value for each SELECT statement that is directed to the data set.

UNLOAD PLUS uses the total number of records and the maximum record size to calculate the total required space in kilobytes.

If you do not specify the SPACE option, UNLOAD PLUS uses the value for the FILESZPCT command or installation option to adjust the total calculated space. See FILESZPCT for more details.

After UNLOAD PLUS determines that SMS will manage a data set, UNLOAD PLUS obtains space information from the storage groups to calculate the space parameters. Because the Automatic Class Selection (ACS) routines could return more than one storage group for a specific data set, UNLOAD PLUS searches the storage groups for the first DASD group that contains sufficient space to hold the entire data set. If none of the storage groups has sufficient space, UNLOAD PLUS uses the first available storage group that is enabled.

Important

When UNLOAD PLUS requires guaranteed space because you supplied volume serial numbers on the OUTPUT statement, and the storage class specifies guaranteed space, UNLOAD PLUS considers only the volumes that you specify on the OUTPUT statement.

After determining whether it requires guaranteed space, UNLOAD PLUS selects either the standard SMS algorithm or the guaranteed space algorithm to calculate the space:

Standard SMS algorithm

UNLOAD PLUS applies the value of PCTPRIM to calculate primary and secondary allocation values in tracks. If the value of PCTPRIM is AUTO, UNLOAD PLUS initially applies a value of 100%.

If you specify MAXPRIM, UNLOAD PLUS adds the difference between the calculated primary allocation and MAXPRIM to the secondary allocation value. UNLOAD PLUS sets the value of the primary allocation to the MAXPRIM value.

When the required primary allocation is greater than the largest free extent and the value of PCTPRIM is AUTO, UNLOAD PLUS sets the value of the primary allocation to the value of the largest free extent, and adds the difference to the secondary allocation value. If the value of PCTPRIM is not AUTO, UNLOAD PLUS generates an error message and terminates the job.

If the secondary allocation is zero, UNLOAD PLUS calculates 10% of the primary allocation to use as the secondary allocation value. If the secondary allocation value is less than the average free space per volume, UNLOAD PLUS sets the unit count to 1 and continues allocating with the next step. If the secondary allocation value is greater than the average free space per volume, UNLOAD PLUS divides the secondary allocation value by the average free space per volume to get the required number of units to meet the secondary allocation. If the required primary allocation is greater than the average free space per volume, UNLOAD PLUS adds 1 to the unit count. If you specify the UNITCNT option, UNLOAD PLUS uses that value. If volume serial numbers are supplied, UNLOAD PLUS uses the number of volume serial numbers. If either of these two values is less than the calculated number of units, UNLOAD PLUS issues a warning message but continues allocation.

If the value of NBRSECD is AUTO, UNLOAD PLUS sets NBRSECD to 123 if the SMS DATACLAS contains extended format. If the DATACLAS does not specify extended format, UNLOAD PLUS sets the value of NBRSECD to 16. If the calculated unit count is 1, UNLOAD PLUS reduces the value of NBRSECD by 1.

UNLOAD PLUS divides the secondary allocation value by the value for NBRSECD to get the track value for the secondary allocation.

The secondary allocation value is checked against the value specified for the MAXSECD option. If the secondary allocation value is less than or equal to the MAXSECD value, processing continues. Otherwise, the secondary value is set to the value of MAXSECD and the number of units required is recalculated.

Guaranteed space algorithm

UNLOAD PLUS applies the value of PCTPRIM to calculate primary and secondary allocation values in tracks. If the value of PCTPRIM is AUTO, UNLOAD PLUS initially applies a value of 100%.

UNLOAD PLUS divides the value of the calculated primary allocation by the number of volume serial numbers that were supplied.

If you specify MAXPRIM, UNLOAD PLUS multiplies the difference between the calculated primary allocation and MAXPRIM by the number of volumes, and adds that value to the secondary value. The value of the primary allocation is set to the MAXPRIM value.

If the secondary allocation is zero, UNLOAD PLUS calculates 10% of the primary allocation to use as the secondary allocation value. When the required primary allocation is greater than the smallest free extent and the value of PCTPRIM is AUTO, UNLOAD PLUS sets the value of the primary allocation to the value of the smallest free extent and adds the difference multiplied by the number of volumes to the secondary allocation value. If the value of PCTPRIM is not AUTO, UNLOAD PLUS generates an error message and stops processing.

UNLOAD PLUS divides the value of the secondary allocation by the number of volume serial numbers that were supplied. If the secondary allocation value is greater than average free space per volume, UNLOAD PLUS issues a warning message but continues allocation.

If the value of NBRSECD is AUTO, UNLOAD PLUS sets NBRSECD to 123 if the SMS DATACLAS contains extended format. If the DATACLAS does not specify extended format, UNLOAD PLUS sets the value of NBRSECD to 16. If the calculated unit count is 1, UNLOAD PLUS reduces the value of NBRSECD by 1.

UNLOAD PLUS divides the secondary allocation value by the value for NBRSECD to get the tracks value for the secondary allocation.

The secondary allocation value is checked against the value specified for the MAXSECD option. If the secondary allocation value is less than or equal to the MAXSECD value, processing continues. Otherwise, the secondary value is set to the value of MAXSECD and UNLOAD PLUS issues message BMC50166W.

Enabling multitasking for performance

UNLOAD PLUS data sets

UNLOAD PLUS DD statements