LOAD_TABLE scalar function

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

Prototypes

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

RETURNS VARCHAR(8000)

Description

This function generates a LOAD 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 are assumed to be included in the source file, and the LOAD command will utilize GENERATEDOVERRIDE, otherwise the LOAD command will utilize GENERATEDMISSING. GENERATED BY DEFAULT columns are treated as ordinary columns. If the optional INCLUDE_IDENTITY parameter is 'Y' (the default), IDENTITY columns are assumed to be included in the source file, and the LOAD command will utilize IDENTITYOVERRIDE, otherwise the LOAD command will utilize IDENTITYMISSING.

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

Parameters

ASCHEMA
If provided, the schema containing the table to generate a LOAD command for. If omitted, defaults to the value of the CURRENT SCHEMA special register.
ATABLE
The name of the table to generate a LOAD command for.
INCLUDE_GENERATED
If this parameter is 'Y' then the routine assumes generated columns are included in the source file, and the LOAD command will include the GENERATEDOVERRIDE modifier. Otherwise, if 'N', the GENERATEDMISSING modifier will be used instead. Defaults to 'Y' if omitted.
INCLUDE_IDENTITY
If this parameter is 'Y' then the routine assumes identity columns are included in the source file, and the LOAD command will include the IDENTITYOVERRIDE modifier. Otherwise, if 'N', the IDENTITYMISSING modifier will be used instead. Defaults to 'Y' if omitted.

Examples

Generate a LOAD command for the EMPLOYEE table in the standard SAMPLE database:

VALUES LOAD_TABLE('EMPLOYEE')

1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------...
LOAD FROM "DB2INST1.EMPLOYEE.IXF" OF IXF METHOD N (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) REPLACE INTO DB2INST1.EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM)

Generate a LOAD 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 LOAD_TABLE('PEOPLE', 'Y', 'N');

1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOAD FROM "DB2INST1.PEOPLE.IXF" OF IXF MODIFIED BY GENERATEDOVERRIDE,IDENTITYMISSING METHOD N (NAME,GENDER,DOB,TITLE) REPLACE INTO DB2INST1.PEOPLE (NAME,GENDER,DOB,TITLE)