Fast/Unload BLOB/CLOB processing considerations - Revision history

JAL: link repair

2016-03-14T15:54:32Z

link repair

Show changes

JAL: misc cleanup

2015-03-31T19:47:23Z

misc cleanup

Show changes

JAL: add 4.6 changes

2015-03-30T21:12:12Z

add 4.6 changes

← Older revision		Revision as of 21:12, 30 March 2015
Line 3:		Line 3:
	to operate on <var>BLOB</var> and <var>CLOB</var> (collectively called "Lob") fields, which		to operate on <var>BLOB</var> and <var>CLOB</var> (collectively called "Lob") fields, which
	were introduced with V6R1 of <var class="product">Model 204</var>.		were introduced with V6R1 of <var class="product">Model 204</var>.
			<p class="note"><b>Note:</b> Processing <var>BLOB</var> or <var>CLOB</var> fields that have the <var>[[Field_design#DEFAULT-VALUE (DV) attribute\|DEFAULT-VALUE]]</var> attribute requires <var class="product">Fast/Unload</var> version 4.6 or higher. </p>

	The following <var class="product">Fast/Unload</var> 4.3 features support these fields:		The following <var class="product">Fast/Unload</var> 4.3 features support these fields:

JAL: add template

2015-03-11T19:42:17Z

add template

← Older revision		Revision as of 19:42, 11 March 2015
Line 315:		Line 315:
	<!-- .im comdate-->		<!-- .im comdate-->
	==See also==		==See also==
	[[Fast/Unload ~~overview#WIKFUN$$topics\|Fast/Unload topics]]~~		{{Template:Fast/Unload topic list}}

JAL: more conversion cleanup

2015-02-27T16:25:27Z

more conversion cleanup

← Older revision		Revision as of 16:25, 27 February 2015
Line 196:		Line 196:
	<ul>		<ul>
	<li>The <var>UNLOAD(C)</var> statement </li>		<li>The <var>UNLOAD(C)</var> statement </li>

	<li>The <var>EXISTS</var> and <var>MISSING</var> clauses of an <var>IF/ELSEIF</var> statement </li>		<li>The <var>EXISTS</var> and <var>MISSING</var> clauses of an <var>IF/ELSEIF</var> statement </li>

	<li>The <var>#IF/#ELSEIF</var> directives </li>		<li>The <var>#IF/#ELSEIF</var> directives </li>

	<li>Preceding the number sign (<tt>#</tt>) "qualifier," which		<li>Preceding the number sign (<tt>#</tt>) "qualifier," which
	specifies the number of occurrences of the field.		specifies the number of occurrences of the field.
	<p>		<p>
	For example, the following statement is valid for any type of field: </p>		For example, the following statement is valid for any type of field: </p>
Line 210:		Line 213:
	<!--Caution: <div> above-->		<!--Caution: <div> above-->

	~~<p></p>~~
	If you display field statistics in the <var class="product">Fast/Unload</var> job statistics,		If you display field statistics in the <var class="product">Fast/Unload</var> job statistics,
	the total number of pages used in Table E is shown for each Lob field		the total number of pages used in [[File architecture overview#Data tables\|Table E]] is shown for each Lob field on the second line of the field's display.
	on the second line of the field's display.		<p>
	<~~p></~~p>
	Note that the length statistics given for a Lob field, just like		Note that the length statistics given for a Lob field, just like
	other fields, is based on the field occurrence values: in this case,		other fields, is based on the field occurrence values: in this case,
	the number of bytes in Table E <b>used</b> by each field		the number of bytes in Table E <i>used</i> by each field
	occurrence value (that is, unused bytes in Table E pages are not		occurrence value (that is, unused bytes in Table E pages are not
	included in the length statistics).		included in the length statistics).</p>
	<p></p>		<p>
	The ~~<b>~~Table B~~</b>~~ usage for a Lob field is:		The Table B usage for a Lob field is: </p>
	<ul>		<ul>
	<li>27 bytes for a non-preallocated		<li>27 bytes for a non-preallocated Lob field occurrence (in addition to the
	Lob field occurrence (in addition to the		overhead, as usual, for a count byte and field code) </li>
	overhead, as usual, for a count byte and field code)
	<li>28 bytes for a preallocated Lob field occurrence		<li>28 bytes for a preallocated Lob field occurrence </li>
	</ul>		</ul>
	<~~p></~~p>		<p>
	For more information about the field statistics, see [[Fast/Unload Extraction Language (FUEL)#fst\|FSTATS [AVGTOT \| MINMAX\]].		For more information about the field statistics, see [[Fast/Unload Extraction Language (FUEL)#fst\|FSTATS [AVGTOT \| MINMAX\]]. </p>

	<div id="exlong"></div>		<div id="exlong"></div>
	==Lob field examples==		==Lob field examples==
	<!--Caution: <div> above-->		<!--Caution: <div> above-->


	<div id="crenew"></div>		<div id="crenew"></div>
	===Creating a NEW Lob field===		===Creating a NEW Lob field===
	<!--Caution: <div> above-->		<!--Caution: <div> above-->
			<p>
	~~<p>~~</p>		The following example unloads file <code>PRODFILE</code> such that, when it is
	The following example unloads file PRODFILE such that, when it is		reloaded, all occurrences of field <code>COMMENT</code> are combined into a
	reloaded, all occurrences of field COMMENT are combined into a		single Lob field named <code>ALLCOMMENTS</code>: </p>
	single Lob field named ALLCOMMENTS:
	<p class="code"><nowiki>OPEN PRODFILE		<p class="code"><nowiki>OPEN PRODFILE
	UAI OINDEX		UAI OINDEX
Line 259:		Line 258:
	END FOR		END FOR
	</nowiki></p>		</nowiki></p>
	Note that the first occurrence of COMMENT is used in each
	iteration of the FOR I loop~~; when it~~ is deleted at the tail		Note that the first occurrence of <code>COMMENT</code> is used in each
			iteration of the <code>FOR I</code> loop. When this occurrence is deleted at the tail
	of the loop, the occurrence after it becomes the first occurrence		of the loop, the occurrence after it becomes the first occurrence
	on the next iteration.		on the next iteration.
Line 266:		Line 266:
	<div id="unlexmp"></div>		<div id="unlexmp"></div>
	===Structured unload of Lob field===		===Structured unload of Lob field===
	<!--Caution: <div> above-->		<!--Caution: <div> above-->
			<p>
	~~<p>~~</p>		The following example unloads file <code>PRODFILE</code>, creating one output
	The following example unloads file PRODFILE, creating one output		record for each 255 bytes (the maximum for a <var>PUT</var> statement) of
	record for each 255 bytes (the maximum for a PUT statement) of		the Lob field named <code>ALLCOMMENTS</code>: </p>
	the Lob field named ALLCOMMENTS:		<p class="code">OPEN PRODFILE
	<p class="code"~~><nowiki~~>OPEN PRODFILE
	FOR EACH RECORD		FOR EACH RECORD
	PUT '* '		PUT '* '
Line 296:		Line 295:
	END IF		END IF
	END FOR		END FOR
	</~~nowiki~~></p>		</p>
	<b>Important notes:</b>		<blockquote class="note">
			<p>
			<b>Important notes:</b> </p>
	<ul>		<ul>
	<li>The <b>plus sign</b> (<~~code~~>+</~~code~~>) in <code>IF +%I >= %LIM</code>		<li>The <b>plus sign</b> (<tt>+</tt>) in <code>IF +%I >= %LIM</code>
	is very important — otherwise a string comparison ~~will be~~ done, which is not		is very important — otherwise a string comparison is done, which is not
	correct.		correct.
	For example, if the length is 1,000,000, the first 255 bytes would be unloaded		For example, if the length is 1,000,000, the first 255 bytes would be unloaded
	and the final PUT will fail, because then the length of <code>%X</code>		and the final <var>PUT</var> will fail, because then the length of <code>%X</code>
	would be 1,000,000-254.		would be <code>1,000,000-254</code>. </li>

	<li>The above approach is <i>vastly superior</i> to an approach that uses		<li>The above approach is <i>vastly superior</i> to an approach that uses
	something like <code>%COM = $SUBSTR(#COM, 256)</code> to repeatedly		something like <code>%COM = $SUBSTR(#COM, 256)</code> to repeatedly
	remove the first 255		remove the first 255 bytes, because that would involve unnecessary copying (approximately the square of the number of bytes in each field). </li>
	bytes, because that would involve unnecessary copying ~~on the order of~~ the square
	of the number of bytes in each field.
	</ul>		</ul>
			</blockquote>

	<!-- .im comdate-->		<!-- .im comdate-->
	==See also==		==See also==
	[[Fast/Unload overview#WIKFUN$$topics\|Fast/Unload topics]]		[[Fast/Unload overview#WIKFUN$$topics\|Fast/Unload topics]]

JAL: more conversion cleanup

2015-02-26T22:54:33Z

more conversion cleanup

Show changes

DmeWiccan: Automatically generated page update

2015-01-15T19:40:03Z

Automatically generated page update

← Older revision		Revision as of 19:40, 15 January 2015
Line 48:		Line 48:
	<p></p>		<p></p>
	As of version 4.3, the NEW statement		As of version 4.3, the NEW statement
	([[#newfld\|NEW fieldname [WITH BLOB \| CLOB\]]) lets you specify that the		([[Fast/Unload Extraction Language (FUEL)#newfld\|NEW fieldname [WITH BLOB \| CLOB\]]) lets you specify that the
	new field you are defining is either a BLOB or CLOB field.		new field you are defining is either a BLOB or CLOB field.
	This is primarily useful for a UAI type unload, allowing you to		This is primarily useful for a UAI type unload, allowing you to
Line 71:		Line 71:

	<p></p>		<p></p>
	The arguments of #CONCAT ([[#conc\|#CONCAT: Concatenate strings]]) may now be string values that		The arguments of #CONCAT ([[Fast/Unload standard functions#conc\|#CONCAT: Concatenate strings]]) may now be string values that
	exceed 255 bytes in length (as the contents of %variables or Lob fields).		exceed 255 bytes in length (as the contents of %variables or Lob fields).
	<p></p>		<p></p>
Line 89:		Line 89:

	<p></p>		<p></p>
	The first argument of #LEN ([[##len\|#LEN: Length of string]]) may now be a string value		The first argument of #LEN ([[Fast/Unload standard functions##len\|#LEN: Length of string]]) may now be a string value
	that exceeds 255 bytes in length (as the content of a %variable or Lob field).		that exceeds 255 bytes in length (as the content of a %variable or Lob field).
	<p></p>		<p></p>
Line 103:		Line 103:

	<p></p>		<p></p>
	The first argument of #SUBSTR ([[##substr\|#SUBSTR: Substring]]) may now be a string value		The first argument of #SUBSTR ([[Fast/Unload standard functions##substr\|#SUBSTR: Substring]]) may now be a string value
	that exceeds 255 bytes in length (as the content of a %variable or Lob field).		that exceeds 255 bytes in length (as the content of a %variable or Lob field).
	<p></p>		<p></p>
Line 229:		Line 229:
	</ul>		</ul>
	<p></p>		<p></p>
	For more information about the field statistics, see [[#fst\|FSTATS [AVGTOT \| MINMAX\]].		For more information about the field statistics, see [[Fast/Unload Extraction Language (FUEL)#fst\|FSTATS [AVGTOT \| MINMAX\]].

	<div id="exlong"></div>		<div id="exlong"></div>

DmeWiccan: Automatically generated page update

2015-01-14T00:49:04Z

Automatically generated page update

New page

<p></p>
As of version 4.3, <var class="product">Fast/Unload</var> includes the ability
to operate on BLOB and CLOB (collectively called "Lob") fields, which
were introduced with V6R1 of <var class="product">Model 204</var>.
<p></p>
The following <var class="product">Fast/Unload</var> 4.3 features support these fields:
<ul>
<li>NEW field declaration statements
may have a WITH CLOB or WITH BLOB designation
(for example, for creating a Lob field by concatenating "old"
non-Lob fields).
See [[#newlob|NEW statement option for Lobs]].
<li>#functions may both accept arguments and produce
results in excess of 255 bytes.
See:
<ul>
<li>[[#concat|#CONCAT supports long string arguments and result]]
<li>[[#len|#LEN supports a long string argument]]
<li>[[#substr|#SUBSTR supports a long string argument and result]]
</ul>
<li>The contexts where FUEL %variables may contain strings longer
than 255 bytes,
the statements, #functions, and directives that allow
string values longer than 255 bytes,
and the contexts where Lob fields may be used are specified
(see [[#conlong|Contexts for long strings and Lobs]]).
<li>A job statistic reports Table E page usage for each Lob field
([[#stat|Lob statistics]]).
</ul>
<p></p>
The above features are discussed in the rest of this wiki page,
at the end of which is a pair of examples
that use these features ([[#exlong|Lob field examples]]).

<div id="statfun"></div>
==Statement and #function modifications==


<p></p>
The NEW statement and three #functions are changed to define
Lob fields and work with strings longer than 255 bytes.

<div id="newlob"></div>
===NEW statement option for Lobs===


<p></p>
As of version 4.3, the NEW statement
([[#newfld|NEW fieldname [WITH BLOB | CLOB\]]) lets you specify that the
new field you are defining is either a BLOB or CLOB field.
This is primarily useful for a UAI type unload, allowing you to
create values in the new field that are loaded by LAI as Lob
occurrences.
<p></p>
<p></p>
The syntax is:
<p class="code"><nowiki>NEW fieldname WITH BLOB | CLOB
</nowiki></p>
<blockquote class="note"><b>Note:</b> Version 4.3 also introduces a change to the default attributes
that are assigned to fields defined with NEW.
As of 4.3, the default attributes are NFRV, NKEY, NCOD, UPDATE IN PLACE
(formerly, they were FRV, KEY, CODED, UPDATE AT END).
</blockquote>
<p></p>
See [[#crenew|Creating a NEW Lob field]] for an example that uses a Lob option.

<div id="concat"></div>
===#CONCAT supports long string arguments and result===


<p></p>
The arguments of #CONCAT ([[#conc|#CONCAT: Concatenate strings]]) may now be string values that
exceed 255 bytes in length (as the contents of %variables or Lob fields).
<p></p>
The result of #CONCAT may now be a string longer than 255 bytes.
<p></p>
There is no compatibility issue with previous use of the #CONCAT
function: the maximum length of an argument was 255 bytes,
and if the concatenation of the arguments exceeded 255 bytes, the
FUEL program was terminated.
<p></p>
See [[#crenew|Creating a NEW Lob field]] for an example that uses this #function with
a long string value.

<div id="len"></div>
===#LEN supports a long string argument===


<p></p>
The first argument of #LEN ([[##len|#LEN: Length of string]]) may now be a string value
that exceeds 255 bytes in length (as the content of a %variable or Lob field).
<p></p>
There is no compatibility issue with previous use of the #LEN
function, because the maximum length of an argument was 255 bytes.
<p></p>
See [[#unlexmp|Structured unload of Lob field]] for an example that uses this #function with
a long string value.

<div id="substr"></div>
===#SUBSTR supports a long string argument and result===


<p></p>
The first argument of #SUBSTR ([[##substr|#SUBSTR: Substring]]) may now be a string value
that exceeds 255 bytes in length (as the content of a %variable or Lob field).
<p></p>
The result of #SUBSTR may now be a string longer than 255 bytes.
<p></p>
There is no compatibility issue with previous use of the #SUBSTR
function, because the maximum length of an argument was 255 bytes.
<p></p>
See [[#unlexmp|Structured unload of Lob field]] for an example that uses this #function with
a long string value.

<div id="conlong"></div>
==Contexts for long strings and Lobs==


The version 4.3 <var class="product">Fast/Unload</var> accommodations for Lob fields include
allowing %variables to contain strings longer than 255 bytes
and specifying the contexts that allow such strings and Lob fields.

<div id="longstr"></div>
===%Variables containing strings longer than 255===


<p></p>
The value of a %variable may be a string longer than 255 bytes.
This can arise as the result of:
<table>
<tr><th>%v1 = %v2</th><td>assignment from another %variable which contains a string longer than 255 bytes</td></tr>
<tr><th>%v = fld</th><td>assignment from a Lob field</td></tr>
<tr><th>%v = #SUBSTR(...)</th><td>assignment from a substring of a string value longer than 255 bytes</td></tr>
<tr><th>%v = #CONCAT(...)</th><td>assignment from the concatenation of strings, whose lengths total more than 255 bytes</td></tr>
</table>
<p></p>
[[#longnot|Permitted use of long string values]] specifies the contexts in which a %variable
may be used if it contains a string longer than 255 bytes.

<div id="longnot"></div>
===Permitted use of long string values===


<p></p>
A long string value may be used in the following contexts:
<ul>
<li>as an argument of #CONCAT
<li>as the argument of #LEN
<li>as the first argument of #SUBSTR
<li>as the right hand side of an assignment to a %variable
<li>as the right hand side of a CHANGE or ADD[C] statement,
when the field on the left hand side is a Lob
</ul>
<p></p>
If the <b>value</b> of a %variable is used in any other context,
and it is a string longer than 255 bytes, the FUEL program is terminated.
For example, the following program creates one line of output, because
the PUT statement does not allow a %variable containing a string longer
than 255:
<p class="code"><nowiki>OPEN MYFILE
%X = #LEFT('ABC', 150, 'Z')
PUT %X /* Length is 150
OUTPUT
%X = #CONCAT(%X, %X) /* Length is 300
PUT %X /* FUEL program will be cancelled here
OUTPUT
FOR EACH RECORD /* Make it a legal FUEL program
END FOR
</nowiki></p>
Other examples of contexts prohibiting a %variable containing
a string longer than 255 bytes include arithmetic expressions,
comparisons in the IF statement, and more.
<p></p>
Note that, since the EXISTS and MISSING clauses of the IF and
ELSEIF statements do not reference the value of a %variable,
you may use them to test a %variable even if it contains
a string longer than 255 bytes.
That is, the following statement is acceptable in all cases:
<p class="code"><nowiki>IF %S MISSING THEN /* OK even if #LEN(%S) > 255
</nowiki></p>

<div id="lobnot"></div>
===Permitted use of Lobs===


<p></p>
The value of
a Lob field may only be used in the contexts discussed above that
allow a string longer than 255 bytes, even if the actual length of
the Lob field occurrence does not exceed 255.
Use of a Lob field in an invalid context causes the
compilation of the FUEL program to fail; it never begins execution.
<p></p>
There are four contexts in which any field, Lob or not, may be referenced:
<ul>
<li>The UNLOAD(C) statement
<li>The EXISTS and MISSING clauses of an IF/ELSEIF statement
<li>The #IF/#ELSEIF directives
<li>Preceding the number sign (<code>#</code>) "qualifier," which
specifies the number of occurrences of the field
<p></p>
For example, the following statement is valid for any type of field:
<p class="code"><nowiki>FOR I FROM 1 TO BLOB(#) /* OK for any field
</nowiki></p>
</ul>

<div id="stat"></div>
==Lob statistics==


<p></p>
If you display field statistics in the <var class="product">Fast/Unload</var> job statistics,
the total number of pages used in Table E is shown for each Lob field
on the second line of the field's display.
<p></p>
Note that the length statistics given for a Lob field, just like
other fields, is based on the field occurrence values: in this case,
the number of bytes in Table E <b>used</b> by each field
occurrence value (that is, unused bytes in Table E pages are not
included in the length statistics).
<p></p>
The <b>Table B</b> usage for a Lob field is:
<ul>
<li>27 bytes for a non-preallocated
Lob field occurrence (in addition to the
overhead, as usual, for a count byte and field code)
<li>28 bytes for a preallocated Lob field occurrence
</ul>
<p></p>
For more information about the field statistics, see [[#fst|FSTATS [AVGTOT | MINMAX\]].

<div id="exlong"></div>
==Lob field examples==


<div id="crenew"></div>
===Creating a NEW Lob field===


<p></p>
The following example unloads file PRODFILE such that, when it is
reloaded, all occurrences of field COMMENT are combined into a
single Lob field named ALLCOMMENTS:
<p class="code"><nowiki>OPEN PRODFILE
UAI OINDEX
NEW ALLCOMMENTS WITH BLOB
FOR EACH RECORD
IF COMMENT EXISTS THEN
%X = '' /* Initialize BLOB value
FOR I FROM 1 TO COMMENT(#)
%X = #CONCAT(%X, COMMENT)
DELETE COMMENT
END FOR
ADD ALLCOMMENTS = %X
END IF
UNLOAD
END FOR
</nowiki></p>
Note that the first occurrence of COMMENT is used in each
iteration of the FOR I loop; when it is deleted at the tail
of the loop, the occurrence after it becomes the first occurrence
on the next iteration.

<div id="unlexmp"></div>
===Structured unload of Lob field===


<p></p>
The following example unloads file PRODFILE, creating one output
record for each 255 bytes (the maximum for a PUT statement) of
the Lob field named ALLCOMMENTS:
<p class="code"><nowiki>OPEN PRODFILE
FOR EACH RECORD
PUT '* '
PUT CUSTOMER_ID
OUTPUT
IF ALLCOMMENTS EXISTS THEN
%COM = ALLCOMMENTS
%LENGTH = #LEN(ALLCOMMENTS)
%I = 1
%LIM = %LENGTH - 254
REPEAT
IF +%I >= %LIM THEN
LEAVE REPEAT
END IF
%X = #SUBSTR(%COM, %I, 255)
PUT %X
OUTPUT
%I = %I + 255
END REPEAT
%X = #SUBSTR(%COM, %I)
PUT %X
OUTPUT
END IF
END FOR
</nowiki></p>
<b>Important notes:</b>
<ul>
<li>The <b>plus sign</b> (<code>+</code>) in <code>IF +%I >= %LIM</code>
is very important — otherwise a string comparison will be done, which is not
correct.
For example, if the length is 1,000,000, the first 255 bytes would be unloaded
and the final PUT will fail, because then the length of <code>%X</code>
would be 1,000,000-254.
<li>The above approach is <i>vastly superior</i> to an approach that uses
something like <code>%COM = $SUBSTR(#COM, 256)</code> to repeatedly
remove the first 255
bytes, because that would involve unnecessary copying on the order of the square
of the number of bytes in each field.
</ul>


==See also==
[[Fast/Unload overview#WIKFUN$$topics|Fast/Unload topics]]