EXPORT_TABLE scalar function

Generates an EXPORT command for the specified table including or excluding generated and/or identity columns as requested.

Prototypes

EXPORT_TABLE(ASCHEMA VARCHAR(128), ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_TABLE(ATABLE VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_TABLE(ATABLE VARCHAR(128))

RETURNS VARCHAR(8000)

Description

This function generates an EXPORT command for the specified table in the specified schema or the current schema if ASCHEMA is omitted. If the optional INCLUDE_GENERATED parameter is 'Y' (the default), GENERATED ALWAYS columns will be included, otherwise they are excluded. GENERATED BY DEFAULT columns are always included. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns will be included, otherwise they are excluded.

See EXPORT_SCHEMA table function for more information on the generated command.

Parameters

ASCHEMA
If provided, the schema containing the table to generate an EXPORT command for. If omitted, defaults to the value of the CURRENT SCHEMA special register.
ATABLE
The name of the table to generate an EXPORT command for.
INCLUDE_GENERATED
If this parameter is 'Y' then any columns defined as GENERATED in the source table will be included in the export. Contrariwise, if 'N', generated columns will be excluded from the command. Defaults to 'Y' if omitted.
INCLUDE_IDENTITY
If this parameter is 'Y' (and INCLUDE_GENERATED is 'Y' given that identity columns are by definition generated) then any columns defined as IDENTITY in the source table will be included in the export. Contrariwise, if 'N', identity columns will be excluded from the command (regardless of the value of INCLUDE_GENERATED). Defaults to 'Y' if omitted.

Examples

Generate an EXPORT command for the EMPLOYEE table in the standard SAMPLE database:

VALUES EXPORT_TABLE('EMPLOYEE')
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------...
EXPORT TO "DB2INST1.EMPLOYEE.IXF" OF IXF SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM FROM DB2INST1.EMPLOYEE

Generate an EXPORT command for the PEOPLE table (DDL included) excluding IDENTITY columns:

CREATE TABLE PEOPLE (
    ID      INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    NAME    VARCHAR(100) DEFAULT '' NOT NULL,
    GENDER  CHAR(1) NOT NULL,
    DOB     DATE NOT NULL,
    TITLE   VARCHAR(10) NOT NULL GENERATED ALWAYS AS (
        CASE GENDER
            WHEN 'M' THEN 'Mr.'
            WHEN 'F' THEN 'Ms.'
        END
    ),
    CONSTRAINT GENDER_CK CHECK (GENDER IN ('M', 'F'))
);

VALUES EXPORT_TABLE('PEOPLE', 'Y', 'N');
1
----------------------------------------------------------------------------------------...
EXPORT TO "DB2INST1.PEOPLE.IXF" OF IXF SELECT NAME,GENDER,DOB,TITLE FROM DB2INST1.PEOPLE