UNLOAD block
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-'