TABLES MODIFIERS

When considering the processing of a whole tablespace or of a whole database, there can be one FORMAT clause specified only and applying to all the underlying tables. As a result, the modifiers specified into such a FORMAT clause behave on the processing of all these tables, and there is no ability of specifying different sets of modifiers from a table to another one. This can be a limitation if at least a modifier is not suitable for at least one table. For such a global processing, the purpose of the TABLES MODIFIERS clause is to get more flexibility by offering the ability of specifying a list of modifiers which can change from a table to another one.

The TABLES MODIFIERS clause is a general way to specify a list of Db2 Load modifiers associated to a given set of table unloads. It allows to specify distinct sets of modifiers applying to various table unloads. Such ability is not available through the use of the MODIFIED BY option of the FORMAT clause.

The TABLES MODIFIERS clause is taken into account only if the output format considered is one compatible with the Db2 Load utility, these formats being the ASC, DEL, DELIMITED and IXF ones. It is ignored instead.

It must be specified as an option associated to an UNLOAD block without any SELECT statement specified into it, and it only applies to this block.

The list of modifiers that can be specified can be found here: List of modifiers usable in the TABLE MODIFIERS and TABLES MODIFIERS clauses

Syntax: TABLES MODIFIERS ( ( modifiers ) [ FOR|EXCEPT ( tablename | qualifier.tablename ) ] ); TABLES MODIFIERS IN "path/filename"
Variables: modifiers
A list of modifiers separated by commas.; tablename
Name of a table.; qualifier.tablename
Names of a table and its qualifier.; "path/filename"
File containing the modifiers and tables associations specifications.

There are two ways to specify a TABLES MODIFIERS clause:

either with an entire explicit specification of the lists of modifiers,
or with the specification of an external file containing the lists of modifiers.

With an entire explicit specification

This clause can contain one or several lists of modifiers separated by commas. A list of modifiers can be related to a list of table names separated by commas, through the specification of the FOR or EXCEPT option. If the FOR option is used, the list of modifiers applies to all the tables specified into the associated list. If the EXCEPT option is used, the list of modifiers applies to all the tables not being specified into the associated list. If there is no FOR or EXCEPT option used, the list of modifiers applies to all the tables not being addressed by any list of tables associated to another list of modifiers.

With the specification of an external file

This clause can refer to a file containing the specification of one or several lists of modifiers. Each list of modifiers into this file must be specified on its own and separate entry, and it cannot be split into several lines. There can be as many entries as necessary specified in the file. Each entry must be formatted according to the following models:

modifier1,...,modifiern;for:table1,...,tablen
modifier1,...,modifiern;except:table1,...,tablen
modifier1,...,modifiern;

The first model is for a list of modifiers related to a list of table names by the FOR option. The second model is for a list of modifiers related to a list of table names by the EXCEPT option. And the third model is for a list of modifiers not related to any list of table names.

The # character can be used to add a comment to the file.

An entry must contain one semicolon character (';'), in order to separate its two different components (modifiers, tables), even if it is not necessary to specify table names. The list of modifiers is mandatory, but the list of table names is an optional one. The items into these lists must be separated by comma characters (‘,’). When it is specified, a list of table names must follow the ‘for’ or ‘except’ keyword and a colon character (‘:’).

Using such an external file is an alternative of specifying a whole TABLES MODIFIERS clause in a control file. As a result, for any entry into it, the meaning of its two components (modifiers, tables) is exactly the same one as the respective two components of an entire TABLES MODIFIERS clause described above.

Rules

There cannot be several lists of modifiers specified without any list of tables.

There cannot be several lists of modifiers specified with a list of tables associated to an EXCEPT option.

There cannot be several lists of modifiers specified with a list of tables associated to a FOR option, with the same table name being referred by several of these tables lists.

When a list of modifiers is specified with a list of tables associated to an EXCEPT option, any table name mentioned into this list must be mentioned too in a list of tables associated to another list of modifiers with the FOR option.

A list of tables associated to a list of modifiers cannot refer several times to the same table name.

Use of the TABLES MODIFIERS clause with an entire specification of it in a control file

Here is the result of the Optim™ High Performance Unload execution:

[i1111@lat117 Test]$ db2hpu -f SYSIN50 -i i1111
INZM031I Optim High Performance Unload for Db2 06.05.00.002.05(220318) 
         64 bits 04/01/2022 (Linux lat117 3.10.0-957.21.3.el7.x86_64 #1 SMP Fri Jun 14 02:54:29 EDT 2019 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
000001 GLOBAL CONNECT TO SAMPLE;
000002 UNLOAD DATABASE SAMPLE
000003 ONLY TABLES (EMPLOYEE, DEPARTMENT, INVENTORY)
000004 TABLES MODIFIERS ((DECPLUSBLANK) FOR (EMPLOYEE),
000005                   (DELPRIORITYCHAR, ANYORDER) EXCEPT (DEPARTMENT, EMPLOYEE),
000006                   (FASTPARSE))
000007 OUTFILE("outfile")
000008 LOADFILE("loadfile")
000009 FORMAT DELIMITED
000010 ;

INZU462I HPU control step start: 04/01/2022 16:52:46.486.
INZU463I HPU control step end  : 04/01/2022 16:52:46.997.
INZU464I HPU run step start    : 04/01/2022 16:52:46.998.
INZU410I HPU utility has unloaded 14 rows on lat117 host for I1111.DEPARTMENT in outfile_USERSPACE1_I1111_DEPARTMENT.
INZU442I HPU utility has generated the LOAD file loadfile_USERSPACE1_I1111_DEPARTMENT (LRECL=0).
INZU410I HPU utility has unloaded 42 rows on lat117 host for I1111.EMPLOYEE in outfile_USERSPACE1_I1111_EMPLOYEE.
INZU442I HPU utility has generated the LOAD file loadfile_USERSPACE1_I1111_EMPLOYEE (LRECL=0).
INZU410I HPU utility has unloaded 4 rows on lat117 host for I1111.INVENTORY in outfile_IBMDB2SAMPLEXML_I1111_INVENTORY.
INZU442I HPU utility has generated the LOAD file loadfile_IBMDB2SAMPLEXML_I1111_INVENTORY (LRECL=0).
INZU465I HPU run step end      : 04/01/2022 16:52:47.019.
INZI441I HPU successfully ended: Real time -> 0m0.532771s
User time -> 0m0.115937s : Parent -> 0m0.115937s, Children -> 0m0.000000s
Syst time -> 0m0.049399s : Parent -> 0m0.049399s, Children -> 0m0.000000s

Content of the files generated with Db2 Load commands:

[i1111@lat117 Test]$ cat loadfile_USERSPACE1_I1111_DEPARTMENT
LOAD FROM "outfile_USERSPACE1_I1111_DEPARTMENT" OF DEL
MODIFIED BY CODEPAGE=1208 FASTPARSE
INSERT INTO "I1111"."DEPARTMENT" ("DEPTNO","DEPTNAME","MGRNO","ADMRDEPT","LOCATION");
[i1111@lat117 Test]$
[i1111@lat117 Test]$ cat loadfile_USERSPACE1_I1111_EMPLOYEE
LOAD FROM "outfile_USERSPACE1_I1111_EMPLOYEE" OF DEL
MODIFIED BY CODEPAGE=1208 DATEFORMAT="YYYYMMDD" DECPLUSBLANK
INSERT INTO "I1111"."EMPLOYEE" ("EMPNO","FIRSTNME","MIDINIT","LASTNAME","WORKDEPT","PHONENO","HIREDATE","JOB","EDLEVEL","SEX","BIRTHDATE","SALARY","BONUS","COMM");
[i1111@lat117 Test]$
[i1111@lat117 Test]$ cat loadfile_IBMDB2SAMPLEXML_I1111_INVENTORY
LOAD FROM "outfile_IBMDB2SAMPLEXML_I1111_INVENTORY" OF DEL
MODIFIED BY CODEPAGE=1208 ANYORDER DELPRIORITYCHAR
INSERT INTO "I1111"."INVENTORY" ("PID","QUANTITY","LOCATION");

Use of the TABLES MODIFIERS clause referring to an external file

Here is the result of the Optim High Performance Unload execution:

[i1111@lat117 Test]$ db2hpu -f SYSIN51 -i i1111
INZM031I Optim High Performance Unload for Db2 06.05.00.002.05(220318)
         64 bits 04/01/2022 (Linux lat117 3.10.0-957.21.3.el7.x86_64 #1 SMP Fri Jun 14 02:54:29 EDT 2019 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
000001 GLOBAL CONNECT TO SAMPLE;
000002 UNLOAD DATABASE SAMPLE
000003 ONLY TABLES (EMPLOYEE, DEPARTMENT, INVENTORY)
000004 TABLES MODIFIERS IN "/home/i1111/Test/modifiers.txt"
000005 OUTFILE("outfile")
000006 LOADFILE("loadfile")
000007 FORMAT DELIMITED
000008 ;

INZU462I HPU control step start: 04/01/2022 17:01:44.353.
INZU463I HPU control step end  : 04/01/2022 17:01:45.022.
INZU464I HPU run step start    : 04/01/2022 17:01:45.023.
INZU410I HPU utility has unloaded 14 rows on lat117 host for I1111.DEPARTMENT in outfile_USERSPACE1_I1111_DEPARTMENT.
INZU442I HPU utility has generated the LOAD file loadfile_USERSPACE1_I1111_DEPARTMENT (LRECL=0).
INZU410I HPU utility has unloaded 42 rows on lat117 host for I1111.EMPLOYEE in outfile_USERSPACE1_I1111_EMPLOYEE.
INZU442I HPU utility has generated the LOAD file loadfile_USERSPACE1_I1111_EMPLOYEE (LRECL=0).
INZU410I HPU utility has unloaded 4 rows on lat117 host for I1111.INVENTORY in outfile_IBMDB2SAMPLEXML_I1111_INVENTORY.
INZU442I HPU utility has generated the LOAD file loadfile_IBMDB2SAMPLEXML_I1111_INVENTORY (LRECL=0).
INZU465I HPU run step end      : 04/01/2022 17:01:45.038.
INZI441I HPU successfully ended: Real time -> 0m0.685587s
User time -> 0m0.139254s : Parent -> 0m0.139254s, Children -> 0m0.000000s
Syst time -> 0m0.084809s : Parent -> 0m0.084809s, Children -> 0m0.000000s

Here is the content of the external file used:

[i1111@lat117 Test]$ cat modifiers.txt
decplusblank;for:employee
delprioritychar,anyorder;except:employee,department
fastparse;

The content of this external file corresponds to the entire explicit specification of the TABLES MODIFIERS clause in the previous example. As a result, the Db2 Load commands generated are exactly the same ones as the ones obtained for this previous example.