LucidDbSysRoot EXPORT SCHEMA TO FILE

From Eigenpedia

Jump to: navigation, search

Contents

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.

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 

CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE( 'LOCALDB', 'SALES', false, null, 'LE%',
  null, '/path/to/where/I/want/my/files', true, true, ',', '.csv')
Retrieved from "http://pub.eigenbase.org/wiki/LucidDbSysRoot_EXPORT_SCHEMA_TO_FILE"
Personal tools