The UNLOAD block is required for each unload and provides many of the basic controls for
an unload. This block can occur more than once in a control file.
The following syntax diagram shows the UNLOAD block.
Attention: This block must end with a semicolon.
UNLOAD block
>>---+-UNLOAD--+--+-DATABASE--+---------+---+------------------->
'-MIGRATE-' | '-db_name-' |
'-TABLESPACE--+---------+-'
'-ts_name-'
>--+-------------------------------+---------------------------->
| |
| .-ALL-----------. |
| | .-,---------. | |
| | V | | |
'-PART -(--+---+-nn----+-+-+--)-'
'-nn:nn-'
>--+----------------------------------------------------+------->
| .-,-----------------------. |
| V | |
'-ONLY TABLES--+-(----+-tablename-----------+-+--)-+-'
| '-qualifier.tablename-' |
'-IN--"filename"--------------------'
>--+------------------------------------------------------+----->
| .-,-----------------------. |
| V | |
'-EXCEPT TABLES--+-(----+-tablename-----------+-+--)-+-'
| '-qualifier.tablename-' |
'-IN--"filename"--------------------'
>--+----------------------------------+--+-----------------+---->
'-HEADER--+-FIRST----------------+-' '-HIDDEN--+-NO--+-'
+-NONE-----------------+ '-YES-'
+-ALL------------------+
'-EXTERNAL--"filename"-'
>--+------------------------------------------+----------------->
'-LOADFILE--(--+-"path/filename"------+--)-'
'-"host:path/filename"-'
>--+--------------------------------------------------------+--><
'-LOADMODE--+-load_mode--------------------------------+-'
'-(--load_mode--+---------------------+--)-'
'-WITH--dpf_load_mode-'
Continuation of the unload block:
>>-+-------------------------------------------------------------------------+-->
| .-,------------------------------------------------. |
| V | |
'-LOADOPT----(----+----------------------------------------------+-+--)---'
+-SAVECOUNT--nn--------------------------------+
+-ROWCOUNT--nn---------------------------------+
+-WARNINGCOUNT--nn-----------------------------+
+-MESSAGES--"filename"-------------------------+
+-TEMPFILES PATH--"pathname"-------------------+
+-STATISTICS--+-USE PROFILE-+------------------+
| '-NO----------' |
+-load_recovery_option-------------------------+
+-DATA BUFFER--nn------------------------------+
+-SORT BUFFER--nn------------------------------+
+-CPU_PARALLELISM--nn--------------------------+
+-DISK_PARALLELISM--nn-------------------------+
+-INDEXING MODE--+-AUTOSELECT--+---------------+
| +-REBUILD-----+ |
| +-INCREMENTAL-+ |
| '-DEFERRED----' |
+-ALLOW--+-NO ACCESS--------------------+------+
| '-READ ACCESS--+-------------+-' |
| '-USE--tsname-' |
+-SET INTEGRITY PENDING CASCADE--+-IMMEDIATE-+-+
| '-DEFERRED--' |
'-LOCK WITH FORCE------------------------------'
>--+-----------------------------+--+--------------+------------>
'-LOADTEMPFILES--(--"path"--)-' '-INTERVAL--nn-'
>--+--------------+--+---------------+-------------------------->
'-DB2--+-YES-+-' '-format_clause-'
'-NO--'
>--+---------------------------+--+----------------+------------>
| .-----------------------. | '-outfile_clause-'
| V | |
'---SELECT--select_clause-+-'
>--+---------------+--+----------------------------+------------>
'-LOCK--+-YES-+-' '-FLUSH BUFFERPOOLS--+-YES-+-'
'-NO--' '-NO--'
>--+-------------------------+---------------------------------><
'-SIZELIMIT--+-integerK-+-'
+-integerM-+
'-integerG-'
Continuation of the unload block:
>>-+---------------------+-------------------------------------->
'-SCHEMA--schema_name-'
>--+-------------------------------------+---------------------->
'-SHARED_DATAPART_PROCESSING--+-NO--+-'
'-YES-'
>--+-------------------------------------------------------------------------+-->
'-TARGET--KEYS--(--+-DEFAULT-+--+------------------------------------+--)-'
'-CURRENT-' | .-ALL-----------. |
| | .-,---------. | |
| | V | | |
'-PARTS--+-(--+---+-nn----+-+-+--)-+-'
| '-nn:nn-' |
'-IN--”filename”----------'
>--+----------+------------------------------------------------->
'-SKIP--nn-'
>--+------------------------------------------------------------------+-->
| .-,----------------------------------------------. |
| V | |
'-LOB IN--(----"lob_path_with_template"--+------------------+-+--)-'
+-FOR_PARTS--------+
'-for_parts_clause-'
>--+---------------------------------------------------+-------->
| .-,------------------------------. |
| V | |
'-LOBFILE--(----"base_file_name_with_template"-+--)-'
>--+------------------------------------------------------------------+-->
| .-,----------------------------------------------. |
| V | |
'-XML IN--(----"xml_path_with template"--+------------------+-+--)-'
+-FOR_PARTS--------+
'-for_parts_clause-'
>--+---------------------------------------------------+-------->
| .-,------------------------------. |
| V | |
'-XMLFILE--(----"base_file_name-with-template"-+--)-'
>--+---------------------------+--+----------------+------------>
'-FILEMAXSIZE--+-integerK-+-' '-options_clause-'
+-integerM-+
'-integerG-'
>--+---------------------------------------------+-------------->
+-CCSID--(--ccsid_mixed--+---------------+--)-+
| '-,--ccsid_dbcs-' |
+-ASIS----------------------------------------+
+-ASCII---------------------------------------+
+-UNICODE-------------------------------------+
+-EBCDIC--------------------------------------+
'-LOCALE--------------------------------------'
>--+---------------------------+--+---------------+------------->
'-+-database_backup block-+-' '-MAXTHREADS--n-'
'-copy block------------'
>--+----------------------------------------------------------------------------------------------------+-->
'-TARGET--ENVIRONMENT--(--target_db2nodes_specification--+------------+--+----------------------+--)-'
'-IN--dbname-' '-REPART--+-NO-------+-'
+-PMAP_4K--+
'-PMAP_32K-'
>--+--------------------------------------------------------------------+-->
'-WORKING IN--(--"dirname"--+------------+--+-------------------+--)-'
'-WITH FILES-' '-+-UNIX----+--PATH-'
'-WINDOWS-'
>--+-----------------------+-----------------------------------><
'-replace_string_clause-'
Continuation of the unload block:
>>-+-----------------------------------------+------------------>
'-DDLFILE--(--+-"path/filename"------+--)-'
'-"host:path/filename"-'
>--+-------------------------------------------------------+---->
| .-,-----------------------------------. |
| V | |
'-DDLOPT--(----+-WITH LIKE CLAUSE----------------+-+--)-'
| .-,-----------------. |
| V | |
'-IN--(----'tablespace_name'-+--)-'
>--+--------------------+--+--------------------+--------------->
'-LOBINLINESIZE--nnK-' '-into_tables_clause-'
>--+-------------------------------------------+---------------->
'-XMLSERIALIZE--(--xmlserialization_spec--)-'
>--+---------------------------------------------------+-------->
| .-,-----------------------------. |
| V | |
+-OVERRIDE--(----"original_dir"--BY--"new_dir"-+--)-+
'-SEARCH--(--"search_dir"--)------------------------'
>--+-------------+---------------------------------------------->
'-MAXROWS--nn-'
>--+--------------------------------------------------------------------------------------+-->
'-LOADDEST--(--+-DB2--+------------------------+--+--------------------+----------+--)-'
| +-REMOTE--+--------+-----+ '-WITH STANDARD AUTH-' |
| | '-"node"-' | |
| '-WAREHOUSE--+---------+-' |
| '-"alias"-' |
+-NOSQL_DB--+-+-CLOUDANT-+--+---------+--+--------------------+--+-+
| | '-COUCHDB--' '-"alias"-' '-WITH STANDARD AUTH-' | |
| '-MONGODB--+---------+--+--------------------------+-' |
| '-"alias"-' '-WITH--+-KERBEROS-+--AUTH-' |
| '-STANDARD-' |
+-HADOOP--+-HBASE-----+--+---------+--+--------------------+-------+
| +-HDFS------+ '-"alias"-' '-WITH KERBEROS AUTH-' |
| +-HIVE------+ |
| '-MAPREDUCE-' |
'-OBJECT_STORAGE--+-AWS_S3--+---------+------------------------+---'
| '-"alias"-' |
'-SWIFT--+---------+--+--------------------+-'
'-"alias"-' '-WITH STANDARD AUTH-'
>--;-----------------------------------------------------------><
Continuation of the TARGET ENVIRONMENT clause:
target_db2nodes_specification:
>>-INSTANCE--"instance_name"--+--------------------------------+-->
+-ON--"hostname"-----------------+
'-FILE--"filename_with_template"-'
>--+------------------------------------+----------------------><
| .-,---------. |
| V | |
'-EXCEPT--PARTS--(----+-nn----+-+--)-'
'-nn:nn-'
Continuation of the LOADMODE clause:
load_mode:
>>-+-INSERT-----------------------+----------------------------><
+-REPLACE--+-----------------+-+
| +-KEEPDICTIONARY--+ |
| '-RESETDICTIONARY-' |
+-RESTART----------------------+
'-TERMINATE--------------------'
Continuation of the LOADMODE clause for DPF environments:
dpf_load_mode:
>>-+-LOAD_ONLY-------------+-----------------------------------><
+-LOAD_ONLY_VERIFY_PART-+
+-PARTITION_ONLY--------+
'-PARTITION_AND_LOAD----'
Continuation of the LOADOPT clause:
load_recovery_option:
>>-+-NONRECOVERABLE----------------------------------------------------------+-><
'-COPY--+-NO------------------------------------------------------------+-'
'-YES--(--+-USE--TSM--+--------------------+---------------+--)-'
| '-OPEN--nn--SESSIONS-' |
| .-,-----------. |
| V | |
+-TO----"directory"-+----------------------------+
'-LOAD--"shared library"--+--------------------+-'
'-OPEN--nn--SESSIONS-'
Continuation of the INTO TABLES clause:
tables_pair_spec:
>>-+-src_tbname---------------+--:------------------------------>
'-src_qualifier.src_tbname-'
>--+-tgt_tbname---------------+--------------------------------><
'-tgt_qualifier.tgt_tbname-'
Continuation of the XMLSERIALIZE clause:
xmlserialization_spec:
>>-+---+-CHARACTER-+--+---------------+----------------------------+-->
| '-CHAR------' '-(--integer--)-' |
+---+-VARCHAR----------------+--(--integer--)-------------------+
| '-+-CHARACTER-+--VARYING-' |
| '-CHAR------' |
+---+-CLOB------------------------+--+----------------------+---+
| '-+-CHARACTER-+--LARGE OBJECT-' '-(--integer--+-K-+--)-' |
| '-CHAR------' +-M-+ |
| '-G-' |
'---+-BLOB-----------------+--+----------------------+----------'
'-BINARY--LARGE OBJECT-' '-(--integer--+-K-+--)-'
+-M-+
'-G-'
>--+-------------------------------+---------------------------><
'-+-EXCLUDING-+--XMLDECLARATION-'
'-INCLUDING-'