TABLE MODIFIERS

Use the TABLE MODIFIERS clause to specify Db2® Load modifiers applying to the unload of one table.

The TABLE MODIFIERS clause is a more dedicated way to specify a list of Db2 Load modifiers associated to a given table unload than specifying them within the MODIFIED BY option of the FORMAT clause.

The TABLE 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 a specific SELECT statement, and it only applies to it. It can contain a list of modifiers separated by commas.

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: TABLE MODIFIERS ( modifiers )
Variable: modifiers
A list of modifiers separated by commas.

Use of the TABLE MODIFIERS clause

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

[i1111@lat117 Test]$ db2hpu -f SYSIN57 -i i1111
INZM031I Optim High Performance Unload for Db2 06.05.00.002.07(221110) 
         64 bits (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 TABLESPACE
000003 SELECT * FROM EMPLOYEE;
000004 TABLE MODIFIERS (DECPLUSBLANK, ANYORDER)
000005 OUTFILE("outfile")
000006 LOADFILE("loadfile")
000007 FORMAT DELIMITED
000008 ;
INZU462I HPU control step start: 11/16/2022 10:21:57.115.
INZU463I HPU control step end  : 11/16/2022 10:21:57.257.
INZU464I HPU run step start    : 11/16/2022 10:21:57.842.
INZU410I HPU utility has unloaded 42 rows on lat117 host for I1111.EMPLOYEE in outfile.
INZU442I HPU utility has generated the LOAD file loadfile (LRECL=0).
INZU465I HPU run step end      : 11/16/2022 10:21:57.852.
INZI441I HPU successfully ended: Real time -> 0m0.737675s
User time -> 0m0.056730s : Parent -> 0m0.056730s, Children -> 0m0.000000s
Syst time -> 0m0.031299s : Parent -> 0m0.031299s, Children -> 0m0.000000s

Content of the file generated with the Db2 Load command:

[i1111@lat117 Test]$ cat loadfile
LOAD FROM "outfile" OF DEL
MODIFIED BY CODEPAGE=1208 DATEFORMAT="YYYYMMDD" DECPLUSBLANK ANYORDER
INSERT INTO "I1111"."EMPLOYEE" ("EMPNO","FIRSTNME","MIDINIT","LASTNAME","WORKDEPT","PHONENO","HIREDATE","JOB","EDLEVEL","SEX","BIRTHDATE","SALARY","BONUS","COMM");