EXPORT_SCHEMA table function¶
Generates EXPORT commands for all tables in the specified schema, including or excluding generated and/or identity columns as requested.
Prototypes¶
EXPORT_SCHEMA(ASCHEMA VARCHAR(128), INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_SCHEMA(INCLUDE_GENERATED VARCHAR(1), INCLUDE_IDENTITY VARCHAR(1))
EXPORT_SCHEMA()
RETURNS TABLE(
TABSCHEMA VARCHAR(128),
TABNAME VARCHAR(128),
SQL VARCHAR(8000)
)
Description¶
This table function can be used to generate a script containing EXPORT commands for all tables (not views) in the specified schema or the current schema if the ASCHEMA parameter is omitted. This is intended to be used in scripts for migrating databases or generating ETL scripts.
The generated EXPORT commands will target an IXF file named after the table,
e.g. if ASCHEMA is DATAMART, and the table is COUNTRIES the file would
be named "DATAMART.COUNTRIES.IXF". The export command will explicitly name
all columns in the table. Likewise, LOAD_SCHEMA table function generates LOAD commands
with explicitly named columns. This is to ensure that if the target database’s
tables are not declared in exactly the same order as the source database, the
transfer will still work if, for example, columns have been added to tables in
the source but in the table declaration, they were not placed at the end of the
table.
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.
Parameters¶
- ASCHEMA
- If provided, the schema containing the tables to generate EXPORT commands for. If omitted, defaults to the value of the CURRENT SCHEMA special register.
- INCLUDE_GENERATED
- If this parameter is
'Y'then any columns defined as GENERATED in the source tables will be included in the result. Contrariwise, if'N', generated columns will be excluded. 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 tables will be included in the result. Contrariwise, if'N', identity columns will be excluded (regardless of the value of INCLUDE_GENERATED). Defaults to'Y'if omitted.
Returns¶
The function returns one row per table present in the source schema. Note that the function does not filter out invalidated or inoperative tables. The result table contains three columns:
- TABSCHEMA
- Contains the name of the schema containing the table named in TABNAME.
- TABNAME
- Contains the name of the table that will be exported by the command in the SQL column.
- SQL
- Contains the text of the generated EXPORT command.
The purpose of including the (otherwise redundant) TABSCHEMA and TABNAME columns is to permit the result to be filtered further without having to dissect the SQL column.
Examples¶
Generated EXPORT commands for all tables in the current schema, excluding all generated columns:
SELECT SQL FROM TABLE(EXPORT_SCHEMA('N', 'N'))
SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPORT TO "DB2INST1.CL_SCHED.IXF" OF IXF SELECT CLASS_CODE,DAY,STARTING,ENDING FROM DB2INST1.CL_SCHED
EXPORT TO "DB2INST1.DEPARTMENT.IXF" OF IXF SELECT DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION FROM DB2INST1.DEPARTMENT
EXPORT TO "DB2INST1.ACT.IXF" OF IXF SELECT ACTNO,ACTKWD,ACTDESC FROM DB2INST1.ACT
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
EXPORT TO "DB2INST1.EMP_PHOTO.IXF" OF IXF SELECT EMPNO,PHOTO_FORMAT,PICTURE FROM DB2INST1.EMP_PHOTO
EXPORT TO "DB2INST1.EMP_RESUME.IXF" OF IXF SELECT EMPNO,RESUME_FORMAT,RESUME FROM DB2INST1.EMP_RESUME
EXPORT TO "DB2INST1.PROJECT.IXF" OF IXF SELECT PROJNO,PROJNAME,DEPTNO,RESPEMP,PRSTAFF,PRSTDATE,PRENDATE,MAJPROJ FROM DB2INST1.PROJECT
EXPORT TO "DB2INST1.PROJACT.IXF" OF IXF SELECT PROJNO,ACTNO,ACSTAFF,ACSTDATE,ACENDATE FROM DB2INST1.PROJACT
EXPORT TO "DB2INST1.EMPPROJACT.IXF" OF IXF SELECT EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE FROM DB2INST1.EMPPROJACT
EXPORT TO "DB2INST1.IN_TRAY.IXF" OF IXF SELECT RECEIVED,SOURCE,SUBJECT,NOTE_TEXT FROM DB2INST1.IN_TRAY
EXPORT TO "DB2INST1.ORG.IXF" OF IXF SELECT DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION FROM DB2INST1.ORG
EXPORT TO "DB2INST1.STAFF.IXF" OF IXF SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM FROM DB2INST1.STAFF
EXPORT TO "DB2INST1.SALES.IXF" OF IXF SELECT SALES_DATE,SALES_PERSON,REGION,SALES FROM DB2INST1.SALES
EXPORT TO "DB2INST1.STAFFG.IXF" OF IXF SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM FROM DB2INST1.STAFFG
EXPORT TO "DB2INST1.EMPMDC.IXF" OF IXF SELECT EMPNO,DEPT,DIV FROM DB2INST1.EMPMDC
EXPORT TO "DB2INST1.PRODUCT.IXF" OF IXF SELECT PID,NAME,PRICE,PROMOPRICE,PROMOSTART,PROMOEND,DESCRIPTION FROM DB2INST1.PRODUCT
EXPORT TO "DB2INST1.INVENTORY.IXF" OF IXF SELECT PID,QUANTITY,LOCATION FROM DB2INST1.INVENTORY
EXPORT TO "DB2INST1.CUSTOMER.IXF" OF IXF SELECT CID,INFO,HISTORY FROM DB2INST1.CUSTOMER
EXPORT TO "DB2INST1.PURCHASEORDER.IXF" OF IXF SELECT POID,STATUS,CUSTID,ORDERDATE,PORDER,COMMENTS FROM DB2INST1.PURCHASEORDER
EXPORT TO "DB2INST1.CATALOG.IXF" OF IXF SELECT NAME,CATLOG FROM DB2INST1.CATALOG
EXPORT TO "DB2INST1.SUPPLIERS.IXF" OF IXF SELECT SID,ADDR FROM DB2INST1.SUPPLIERS
EXPORT TO "DB2INST1.PRODUCTSUPPLIER.IXF" OF IXF SELECT PID,SID FROM DB2INST1.PRODUCTSUPPLIER
Generate EXPORT commands for all tables in the DB2INST1 schema whose names
begin with 'EMP', including generated columns which aren’t also identity
columns:
SELECT SQL
FROM TABLE(EXPORT_SCHEMA('DB2INST1', 'Y', 'N'))
WHERE TABNAME LIKE 'EMP%'
SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
EXPORT TO "DB2INST1.EMPMDC.IXF" OF IXF SELECT EMPNO,DEPT,DIV FROM DB2INST1.EMPMDC
EXPORT TO "DB2INST1.EMPPROJACT.IXF" OF IXF SELECT EMPNO,PROJNO,ACTNO,EMPTIME,EMSTDATE,EMENDATE FROM DB2INST1.EMPPROJACT
EXPORT TO "DB2INST1.EMP_PHOTO.IXF" OF IXF SELECT EMPNO,PHOTO_FORMAT,PICTURE FROM DB2INST1.EMP_PHOTO
EXPORT TO "DB2INST1.EMP_RESUME.IXF" OF IXF SELECT EMPNO,RESUME_FORMAT,RESUME FROM DB2INST1.EMP_RESUME
See Also¶
- Source code
- EXPORT_TABLE scalar function
- LOAD_TABLE scalar function
- LOAD_SCHEMA table function
- LOAD (built-in command)
- EXPORT (build-in command)