Fast/Unload with an external sort package: Difference between revisions

From m204wiki
Jump to navigation Jump to search
(Automatically generated page update)
m (more conversion cleanup)
Line 1: Line 1:
<!-- Page name: Fast/Unload with an external sort package-->
<!-- Page name: Fast/Unload with an external sort package-->
<p></p>
Instead of having data for a particular output stream go directly to
Instead of having data for a particular output stream go directly to
an output data set, it can be passed first to a SORT routine.
an output data set, it can be passed first to a <code>SORT</code> routine.
This has the following advantages over sorting that data in a separate step:
This has the following advantages over sorting that data in a separate step:
<ul>
<ul>
<li>A total time savings, because sort processing and record extraction
<li>A total time savings, because sort processing and record extraction
processing are overlapped.
processing are overlapped.</li>
 
<li>A disk space savings, because the intermediate output data set that
<li>A disk space savings, because the intermediate output data set that
would be used to pass data from <var class="product">Fast/Unload</var> to your sort package is eliminated.
would be used to pass data from <var class="product">Fast/Unload</var> to your sort package is eliminated.</li>
</ul>
</ul>
<p></p>
 
Prior to version 4.1, any SORT
Prior to version 4.1, any <var>SORT</var>
directives apply to the single output stream, FUNOUT, and there is no
directives apply to the single output stream, <var>FUNOUT</var>, and there is no
"TO destination" qualifier on such directives.
<var>TO <i>destination</i></var> qualifier on such directives.
<p></p>
 
As of version 4.1 and the advent of multiple output streams in a single
As of version 4.1 and the advent of multiple output streams in a single
FUEL program, a SORT directive may indicate the output stream to which it
FUEL program, a <var>SORT</var> directive may indicate the output stream to which it
applies:
applies:
<p class="code"><nowiki>SORT TO destination ...
<p class="code">SORT TO <i>destination</i> ...
</nowiki></p>
</p>
<p></p>
 
The <b>destination</b> ties the SORT directive to the output stream
The <var class="term">destination</var> ties the <var>SORT</var> directive to the output stream
that has the same destination declared in an OUT TO or UAI TO directive.
that has the same destination declared in an <var>OUT TO</var> or <var>UAI TO</var> directive.
These directives can occur in any order in your FUEL program.
These directives can occur in any order in your FUEL program.
==Specifying the sort==
==Specifying the sort==
For a UAI stream, you indicate that the output is to be sorted by using the
For a <var>UAI</var> stream, you indicate that the output is to be sorted by using the
SORT keyword on the UAI statement (see [[Fast/Unload Extraction Language (FUEL)#uai|UNLOAD ALL INFORMATION or UAI]]).
<var>SORT</var> keyword on the <var>UAI</var> statement (see [[Fast/Unload Extraction Language (FUEL)#uai|UNLOAD ALL INFORMATION or UAI]]).
The only independent SORT directives you may use are the
The only independent <var>SORT</var> directives you may use are the
OPTION statement (at most one per stream) and the PGM statement
<var>OPTION</var> statement (at most one per stream) and the <var>PGM</var> statement
(at most one per program).
(at most one per program).
<p></p>
 
For a non-UAI stream (one declared with an "OUT TO destination" directive),
For a non-UAI stream (one declared with an <var>OUT TO <i>destination</i></var> directive),
to pass data to a SORT routine you must code two or more SORT statements
to pass data to a SORT routine you must code two or more <var>SORT</var> statements
before the start of the FOR EACH RECORD loop in your FUEL program.
before the start of the <var>FOR EACH RECORD</var> loop in your FUEL program.
<var class="product">Fast/Unload</var> interprets the information on a SORT directive as a control statement to
<var class="product">Fast/Unload</var> interprets the information on a <var>SORT</var> directive as a control statement to
be passed to your SORT package (not including the TO destination qualifier).
be passed to your SORT package (not including the <var>TO <i>destination</i></var> qualifier).
<p></p>
 
The SORT FIELDS ([[#srtflds|Using SORT FIELDS]]) and SORT RECORD ([[#srtrec|Using SORT RECORD]])
The <var>SORT FIELDS</var> ([[#srtflds|Using SORT FIELDS]]) and <var>SORT RECORD</var> ([[#srtrec|Using SORT RECORD]])
statements are required for <var class="product">Fast/Unload</var>
statements are required for <var class="product">Fast/Unload</var>
to pass data to a SORT routine.
to pass data to a SORT routine.
In addition to these required SORT statements, <var class="product">Fast/Unload</var> also supports
In addition to these required <var>SORT</var> statements, <var class="product">Fast/Unload</var> also supports
the following statements:
the following statements:
<ul>
<ul>
Line 53: Line 54:
<li>INREC
<li>INREC
</ul>
</ul>
<p></p>
 
In addition:
In addition:
<ul>
<ul>
Line 59: Line 60:
sort package,
sort package,
any other sort statement accepted by your sort package can be used.
any other sort statement accepted by your sort package can be used.
For more information on using 31-bit parameter lists
For more information on using 31-bit parameter lists,
see the documentation on the SORTP parameter and also see [[Fast/Unload customization of defaults]].
see the documentation on the <var>[[Fast/Unload program parameters#sortp|SORTP]]</var> parameter, and also see [[Fast/Unload customization of defaults]].</li>
 
<li>You can identify your sort program
<li>You can identify your sort program
by using the following form of the SORT statement:
by using the following form of the <var>SORT</var> statement:
<p class="code"><nowiki>SORT PGM=pgmname
<p class="syntax">SORT PGM = <span class="term">pgmname</span>
</nowiki></p>
</p>
where <i>pgmname</i> is the name of your sort program
Where <var class="term">pgmname</var> is the name of your sort program
(the default name is SORT).
(the default name is <var>SORT</var>).
This form of the SORT statement does not allow
This form of the <var>SORT</var> statement does not allow
the "TO destination" qualifier, and it may occur at most
the <var>TO <i>destination</i></var> qualifier, and it may occur at most
once in a FUEL program.
once in a FUEL program.
You can also customize <var class="product">Fast/Unload</var> to change the default SORT program name (see
You can also customize <var class="product">Fast/Unload</var> to change the default SORT program name (see [[Fast/Unload customization of defaults#dfspgm|Default SORT program name]]).
[[Fast/Unload customization of defaults#dfspgm|Default SORT program name]]).
</ul>
</ul>
   
   
Line 77: Line 78:
==Using SORT FIELDS==
==Using SORT FIELDS==
<!--Caution: <div> above-->
<!--Caution: <div> above-->
Since <var>SORT FIELDS</var> is a sort statement,
Since SORT FIELDS is a sort statement,
one would expect to have to
one would expect to have to
code the <var class="product">Fast/Unload</var> version of this as &oq.SORT [TO destination] SORT FIELDS,&cq.
code the <var class="product">Fast/Unload</var> version of this as <code>SORT [TO destination] SORT FIELDS</code>, where the first <code>SORT</code>
where the first &oq.SORT&cq.
indicates to <var class="product">Fast/Unload</var> that what follows is a sort statement, and the second <code>SORT</code> is part of the actual sort statement.
indicates to <var class="product">Fast/Unload</var> that what follows is a sort statement, and the second
&oq.SORT&cq. is part of the actual sort statement.
While this is, in fact, permitted,
While this is, in fact, permitted,
<var class="product">Fast/Unload</var> will also allow you to specify simply &oq.SORT FIELDS&cq. as a shorthand.
<var class="product">Fast/Unload</var> also lets you specify simply <code>SORT FIELD</code> as a shorthand.
<p></p>
 
In addition to the standard form of the SORT FIELDS statement
In addition to the standard form of the <var>SORT FIELDS</var> statement
(explicitly specifying start position, length, and format),
(explicitly specifying start position, length, and format),
<var class="product">Fast/Unload</var> provides a shorthand for specifying field positions in an
<var class="product">Fast/Unload</var> provides a shorthand for specifying field positions in an
output record:
output record:
If you specify a fieldname as part of the SORT FIELDS
If you specify a field name as part of the <var>SORT FIELDS</var>
statement, <var class="product">Fast/Unload</var> will replace the name(s) of the field(s) with the
statement, <var class="product">Fast/Unload</var> will replace the name(s) of the field(s) with the
starting position, length, and format in the output record.
starting position, length, and format in the output record.
<p></p>
 
In addition, you can specify a %variable or a special variable (except
In addition, you can specify a %variable or a special variable (except
for #RECOUT, #OUTPOS, #OUTLEN, and #UPARM) as a shorthand for specifying
for <var>#RECOUT</var>, <var>#OUTPOS</var>, <var>#OUTLEN</var>, and <var>#UPARM</var>) as a shorthand for specifying the position of that %variable or special variable in an output record.
the position of that %variable or special variable in an output record.
<p class="note"><b>Note:</b> This shorthand is designed for relatively simple FUEL programs.
<blockquote class="note"><b>Note:</b> This shorthand is designed for relatively simple FUEL programs.
It is <b>not</b> available if the FUEL program
It is <b>not</b> available if the FUEL program
contains more than one OUT TO stream.
contains more than one <var>OUT TO</var> stream.
In that case, you must use the standard form of SORT FIELDS.
In that case, you must use the standard form of <var>SORT FIELDS</var>.
</blockquote>
</p>
<p></p>
 
<p></p>
For example, if you code <p class="code">SORT FIELDS=(FIELD1,A)
For example, if you code
</p>
<p class="code"><nowiki>SORT FIELDS=(FIELD1,A)
</nowiki></p>
and later in your program you code
and later in your program you code
<p class="code"><nowiki>PUT FIELD1 AT 15 AS STRING(10)
<p class="code">PUT FIELD1 AT 15 AS STRING(10)
</nowiki></p>
</p>
<var class="product">Fast/Unload</var> will pass
<var class="product">Fast/Unload</var> will pass
<p class="code"><nowiki>SORT FIELDS=(15,10,CH,A)
<p class="code"><nowiki>SORT FIELDS=(15,10,CH,A)
Line 120: Line 115:
The <code>19</code> position in the statement above also illustrates the fact
The <code>19</code> position in the statement above also illustrates the fact
that <var class="product">Fast/Unload</var> does not
that <var class="product">Fast/Unload</var> does not
consider the RDW (Record Descriptor Word) as part of the output record, while
consider the <code>RDW</code> (Record Descriptor Word) as part of the output record, while
sorts do consider the RDW as part of the input record.
sorts do consider the <code>RDW</code> as part of the input record.
<p></p>
 
Constant occurrence
Constant occurrence numbers are valid with the field names in the <var>SORT FIELDS</var> statement, while (loop control variable or %variable) variable occurrences are not.
numbers are valid with the field names in the SORT FIELDS statement, while
(loop control variable or %variable) variable occurrences are not.
   
   
<div id="srtrec"></div>
<div id="srtrec"></div>
==Using SORT RECORD==
==Using SORT RECORD==
<!--Caution: <div> above-->
<!--Caution: <div> above-->
<var class="product">Fast/Unload</var> uses the <var>SORT RECORD</var> statement to determine the output record format and length.
<var class="product">Fast/Unload</var> uses the SORT RECORD statement to determine the output record format
 
and length.
For example, <var class="product">Fast/Unload</var> produces fixed-length records with a length of 300 if you code:
<p></p>
For example,
<var class="product">Fast/Unload</var> produces fixed length records with a length of 300
if you code:
<p class="code"><nowiki>SORT RECORD TYPE=F,LENGTH=(300)
<p class="code"><nowiki>SORT RECORD TYPE=F,LENGTH=(300)
</nowiki></p>
</nowiki></p>
<p></p>
 
The blocksize
The blocksize used is the largest valid blocksize less than or equal to 4096,
used is the largest valid blocksize less than or equal to 4096,
or it is the record length, if the record length exceeds 4096.
or it is the record length, if the record length exceeds 4096.
For example, if <code>TYPE=F,LENGTH=(300)</code>,
For example, if <code>TYPE=F,LENGTH=(300)</code>,
Line 148: Line 136:
is 4096.
is 4096.
If <code>TYPE=V,LENGTH=8000</code>, the blocksize is 8000.
If <code>TYPE=V,LENGTH=8000</code>, the blocksize is 8000.
<p></p>
 
Data passed between <var class="product">Fast/Unload</var> and the sort package is
Data passed between <var class="product">Fast/Unload</var> and the sort package is
buffered, and the number of buffers used is set by the value of NOBUFF.
buffered, and the number of buffers used is set by the value of <var>NOBUFF</var>.
 
==Sample code==
==Sample code==
The following program is an example of the use of an external sort package with
The following program is an example of the use of an external sort package with
<var class="product">Fast/Unload</var>.
<var class="product">Fast/Unload</var>:
<p class="code"><nowiki>OPEN BIGFILE
<p class="code"><nowiki>OPEN BIGFILE
SORT TO SBIGFILE FIELDS=(KEY1,A,KEY3,D),EQUALS
SORT TO SBIGFILE FIELDS=(KEY1,A,KEY3,D),EQUALS
Line 168: Line 157:
END FOR
END FOR
</nowiki></p>
</nowiki></p>
In this example, the SORT FIELDS statement passed to the sort would be
In this example, the <var>SORT FIELDS</var> statement passed to the sort would be
<p class="code"><nowiki>SORT FIELDS=(1,10,CH,A,21,4,BI,D),EQUALS
<p class="code">SORT FIELDS=(1,10,CH,A,21,4,BI,D),EQUALS
</nowiki></p>
</p>
 
==See also==
==See also==
[[Fast/Unload overview#WIKFUN$$topics|Fast/Unload topics]]
[[Fast/Unload overview#WIKFUN$$topics|Fast/Unload topics]]

Revision as of 21:16, 23 February 2015

Instead of having data for a particular output stream go directly to an output data set, it can be passed first to a SORT routine. This has the following advantages over sorting that data in a separate step:

  • A total time savings, because sort processing and record extraction processing are overlapped.
  • A disk space savings, because the intermediate output data set that would be used to pass data from Fast/Unload to your sort package is eliminated.

Prior to version 4.1, any SORT directives apply to the single output stream, FUNOUT, and there is no TO destination qualifier on such directives.

As of version 4.1 and the advent of multiple output streams in a single FUEL program, a SORT directive may indicate the output stream to which it applies:

SORT TO destination ...

The destination ties the SORT directive to the output stream that has the same destination declared in an OUT TO or UAI TO directive. These directives can occur in any order in your FUEL program.

Specifying the sort

For a UAI stream, you indicate that the output is to be sorted by using the SORT keyword on the UAI statement (see UNLOAD ALL INFORMATION or UAI). The only independent SORT directives you may use are the OPTION statement (at most one per stream) and the PGM statement (at most one per program).

For a non-UAI stream (one declared with an OUT TO destination directive), to pass data to a SORT routine you must code two or more SORT statements before the start of the FOR EACH RECORD loop in your FUEL program. Fast/Unload interprets the information on a SORT directive as a control statement to be passed to your SORT package (not including the TO destination qualifier).

The SORT FIELDS (Using SORT FIELDS) and SORT RECORD (Using SORT RECORD) statements are required for Fast/Unload to pass data to a SORT routine. In addition to these required SORT statements, Fast/Unload also supports the following statements:

  • MODS
  • DEBUG
  • ALTSEQ
  • SUM
  • INCLUDE
  • OMIT
  • OPTION
  • OUTREC
  • INREC

In addition:

  • When using a 31-bit extended parameter list to pass data to the sort package, any other sort statement accepted by your sort package can be used. For more information on using 31-bit parameter lists, see the documentation on the SORTP parameter, and also see Fast/Unload customization of defaults.
  • You can identify your sort program by using the following form of the SORT statement:

    SORT PGM = pgmname

    Where pgmname is the name of your sort program (the default name is SORT). This form of the SORT statement does not allow the TO destination qualifier, and it may occur at most once in a FUEL program. You can also customize Fast/Unload to change the default SORT program name (see Default SORT program name).

Using SORT FIELDS

Since SORT FIELDS is a sort statement, one would expect to have to code the Fast/Unload version of this as SORT [TO destination] SORT FIELDS, where the first SORT indicates to Fast/Unload that what follows is a sort statement, and the second SORT is part of the actual sort statement. While this is, in fact, permitted, Fast/Unload also lets you specify simply SORT FIELD as a shorthand.

In addition to the standard form of the SORT FIELDS statement (explicitly specifying start position, length, and format), Fast/Unload provides a shorthand for specifying field positions in an output record: If you specify a field name as part of the SORT FIELDS statement, Fast/Unload will replace the name(s) of the field(s) with the starting position, length, and format in the output record.

In addition, you can specify a %variable or a special variable (except for #RECOUT, #OUTPOS, #OUTLEN, and #UPARM) as a shorthand for specifying the position of that %variable or special variable in an output record.

Note: This shorthand is designed for relatively simple FUEL programs. It is not available if the FUEL program contains more than one OUT TO stream. In that case, you must use the standard form of SORT FIELDS.

For example, if you code

SORT FIELDS=(FIELD1,A)

and later in your program you code

PUT FIELD1 AT 15 AS STRING(10)

Fast/Unload will pass

SORT FIELDS=(15,10,CH,A)

to the sort package if the record format is fixed, or

SORT FIELDS=(19,10,CH,A)

if the format is variable. The 19 position in the statement above also illustrates the fact that Fast/Unload does not consider the RDW (Record Descriptor Word) as part of the output record, while sorts do consider the RDW as part of the input record.

Constant occurrence numbers are valid with the field names in the SORT FIELDS statement, while (loop control variable or %variable) variable occurrences are not.

Using SORT RECORD

Fast/Unload uses the SORT RECORD statement to determine the output record format and length.

For example, Fast/Unload produces fixed-length records with a length of 300 if you code:

SORT RECORD TYPE=F,LENGTH=(300)

The blocksize used is the largest valid blocksize less than or equal to 4096, or it is the record length, if the record length exceeds 4096. For example, if TYPE=F,LENGTH=(300), the blocksize is 3900. If TYPE=V,LENGTH=(300), the blocksize is 4096. If TYPE=V,LENGTH=8000, the blocksize is 8000.

Data passed between Fast/Unload and the sort package is buffered, and the number of buffers used is set by the value of NOBUFF.

Sample code

The following program is an example of the use of an external sort package with Fast/Unload:

OPEN BIGFILE SORT TO SBIGFILE FIELDS=(KEY1,A,KEY3,D),EQUALS SORT TO SBIGFILE RECORD TYPE=F,LENGTH=(100) SORT TO SBIGFILE ALTSEQ - CODE=(F0B0,F1B1,F2B2,F3B3,F4B4,F5B5,F6B6,F7B7,F8B8,F9B9) OUT TO SBIGFILE DEFAULT FOR EACH RECORD PUT KEY1 AS STRING 10 PUT KEY2 AS STRING 10 PUT KEY3 AS FIXED 4 PUT DATA(*) AS STRING(10) OUTPUT END FOR

In this example, the SORT FIELDS statement passed to the sort would be

SORT FIELDS=(1,10,CH,A,21,4,BI,D),EQUALS

See also

Fast/Unload topics