LucidDbSysRoot EXPORT SCHEMA TO FILE

Syntax

CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE( catalog, schema, exclude, table_list, table_pattern,
directory, with_bcp, delete_failed_file, field_delimiter, file_extension )

CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE( catalog, schema, exclude, table_list, table_pattern,
directory, with_bcp, delete_failed_file, field_delimiter, file_extension , date_format,
time_format, timestamp_format )

Purpose

Exports the tables from a local schema to flat files.

The exported files can be read back into LucidDB tables using the Flat File Wrapper found here: LucidDbFlatFileWrapper

Inputs

• catalog: [VARCHAR(128)] name of the catalog where schema resides, if null, uses default/current catalog name
• schema: [VARCHAR(128)] name of the local schema
• exclude: [BOOLEAN] if true, tables matching either the table_list of the table_pattern will be excluded. if false, tables will be included.
• table_list: [VARCHAR(65535)] comma separated list of tables or null value if table_pattern is being used
• table_pattern: [VARCHAR(65535)] table name pattern where '_' represents any single character and '%' represents any sequence of zero or more characters
• directory: [VARCHAR(65535)] the directory where exported flat files are placed
• with_bcp: [BOOLEAN] indicates whether BCP files should be created. If true, BCP files will be created. If false, they will not be created
• delete_failed_file: [BOOLEAN] indicates whether incomplete csv files for tables which failed export should be deleted. If true, failed files will be deleted, if false they will not
• field_delimiter: [VARCHAR(2)] the character used as a delimiter for the table column fields within each of the flat files created. Common delimiters could be commas ',' or tabs '\t' Field delimiters are limited to 2 characters
• file_extension: [VARCHAR(5)] the file extension to use for each of the flat files created. Common file extensions could be '.txt' or '.csv' File extension is limited to 5 characters. (ex. '.xxxx')
• date_format: [VARCHAR(50)] string parameter for formating date
• time_format: [VARCHAR(50)] string parameter for formating time
• timestamp_format: [VARCHAR(50)] string parameter for formating timestamp

See Java SimpleDateFormat for a description of the possible values for the date, time, and timestamp format parameters.

Output

• flat files of the tables and optionally bcp files for those tables placed under the directory specified
• a summary log of which tables it attempted to export, whether the table export completed successfully, and how long was spent If session parameter logDir is set, then logs will be placed into that directory, otherwise logs will be placed into the same directory as the flat files.

Example

Tab separated file, log to logDir

Note that as of the writing of this page, session parameter logDir is only available when using LucidDb.

ALTER SESSION SET "logDir"='/path/to/where/I/want/my/logs'

CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE( 'LOCALDB', 'SALES', false, 'PRODUCT,SALES',
  null, '/path/to/where/I/want/my/files', true, true, '\t', '.txt')

CSV file, log to same directory as exported files

The example below would read from the SALES schema contained in the LOCALDB catalog. It causes all tables whose names start with LE to be saved to /path/to/where/I/want/my/files while also deleting all intermediate files if things go wrong, but otherwise creating control (.bcp) files, using comma as a field delimiter and saving the table dumps with .csv extensions.

CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE( 'LOCALDB', 'SALES', false, null, 'LE%',
  '/path/to/where/I/want/my/files', true, true, ',', '.csv')