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
See Also¶
- Source code
- EXPORT_SCHEMA table function
- LOAD_TABLE scalar function
- LOAD_SCHEMA table function
- LOAD (built-in command)
- EXPORT (build-in command)