Command Reference

Extract data from a table in a source database to a CSV file in the local file system.

lift extract --source-schema <srcSchema> --source-table <srcTable> --source-database <srcDbName> --source-user <srcUser> --source-password <srcPass> --source-host <srcDbHost> --source-database-port <portnumber> [options]

Required Parameters

The following parameters are required to specify a complete command.

-ss --source-schema <srcSchema>
Case-sensitive source schema name.

-st --source-table <srcTable>
 Case-sensitive source table name.

-sd --source-database <srcDbName>
Source database name.

-su --source-user <srcUser>
 Source database username.

-sp --source-password <srcPass>
Source database password.

-sh --source-host <srcDbHost>
 Source database host.

-sdp --source-database-port <portnumber>
 Source database port number.

Optional Parameters

The following parameters can be optionally provided. Required and optional parameters can be provided in any order.

-f --file <folderFilename>

The file for the extracted data. The file must have a CSV extension. The file may be relative to the current working directory or an absolute location. If the file option is not specified, the table will be extracted to the current working directory with a filename auto-generated from the table schema and table name.

-rp --replace
Specify that a local file is overwritten, if it already exists.

-sdt --source-database-type <srcType>
Source database type.

Valid Values

pda
Indicates the source is IBM PureData System for Analytics.

ias
Indicates the source is an IBM Integrated Analytics System.

db2
Indicate the source is an IBM Db2 database.

db2w
Indicates the source is an IBM Db2 Warehouse.

oracle
Indicates that the source is an Oracle database.

sqlserver
Indicates that the source is a Microsoft SQL Server database.
Default: pda

-ssl --ssl-connection
Specify that the source-database-port is used for an SSL connection.
Default: false

-owl --oracle-wallet-location <oracleWalletLocation>
The location of the Oracle Wallet, which can be specified for a SSL connection to an Oracle database.

-s --size <sizeValue>
Extracts the table into multiple CSV files, each no bigger than the specified size in Mb. By default the entire table is extracted into a single file. File splitting is required when the free space in the cloud landing zone is not sufficiently large enough to contain the extracted table as a single csv file. Each part of the extracted table must be put and loaded separately. If -f is specified, each extracted file will append _part to the filename.

-cs --column-selection <columnSelection>
Specify the list of columns to extract from the source table. By default all columns are extracted. For example, to extract only COL1, COL2, and COL4 from a table, use --column-selection COL1 COL2 COL4. The order of columns specified in "column-selection" does not influence the order of columns in the extracted file. They are always extracted in the order as defined in the source table DDL.

-wc --where-clause <whereClause>
Specify an optional where clause to constrain the rows extracted from the source table.

-q --quiet
Suppress progress messages and only report the final status and statistics.
Default: false

Common Command Options

-d --debug
Trace additional debug content for IBM Support.

-h --help
Display the command reference help.

-l --log <logFolder>
The full path to the parent folder where logs are generated, when overriding the default location. Logs are placed in a folder whose name is unique to the run (/run####).

-pf --properties-file <file>
The path and name of file containing options for the command. Each line in the file must contain key=value where the key is the long name, short name or environment variable name for the option. For keyword only options (e.g. quiet), provide quiet=true. When the option is provided on the command-line, it overrides any value provided in a property file.

A single property file can be shared between source and target commands. Properties are ignored if they are not part of the current command's definition.

The following example shows the format of files containing login credentials:
source-user=user1
source-password=pass
target-password=pass1
max-load-errors=500
When an option supports multiple values, each value must be separated by a space in the property file. The space delimiter can be changed by providing property-file-argument-delimiter=value. For example, to use a comma as a delimiter, specify:
property-file-argument-delimiter=,
column-selection=COL1,COL2

-v --verbose
Provide verbose output messages.

-c --compact
Displays the list of files generated during extract rather than descriptive output. Progress messages are suppressed when compact is enabled.

-pr --preserve-run
Indicates that the run data should be preserved. On successful run (return code 0), the run folder would normally be deleted.

Command Input

Lift CLI runs in your operating system's command prompt and thus must comply with command-line input. Special characters such as space, \, ', " and ` must be escaped or delimited when they are part of a parameter's value. For example if a filename contains a space on Mac or Linux, the filename must be wrapped in 'or', or the space must be escaped by \.
$ lift extract --file "~/Sample File.csv" ...
$ lift extract --file '~/Sample File.csv' ...
$ lift extract --file Sample\ File.csv ...
On Windows, use double quote to escape another double quote and % to escape %. For example, to specify double quote as a data-delimiter, you require four double quotes.
$ lift load --data-delimiter """" ...

Environment Options

The following options can be provided on the command-line, or alternatively by setting an operating system environment variable prior to executing the command. Environment variables or a property file can be used to specify credentials rather that providing them on the command-line.

-sd --source-database <srcDbName>
LIFT_SOURCE_DATABASE_NAME

-su --source-user <srcUser>
LIFT_SOURCE_DATABASE_USER

-sp --source-password <srcPass>
LIFT_SOURCE_DATABASE_PASSWORD

-sh --source-host <srcDbHost>
LIFT_SOURCE_DATABASE_HOST

-sdp --source-database-port <portnumber
LIFT_SOURCE_DATABASE_PORT

-sdt --source-database-type <srcType>
LIFT_SOURCE_DATABASE_TYPE

Lift sets the following environment variables after the command executes:

LIFT_RC
The return code from the command execution.

LIFT_MESSAGE
The message when the command execution fails.

LIFT_INTERNAL_RC
An additional return code from a dependent component. For example, the error code returned from the database.

LIFT_INTERNAL_MESSAGE
An additional message returned by a dependent component. For example, the error message returned from the database.

LIFT_RUN
The full path to the run folder. On successful execution, the run folder is normally deleted unless the command is executed with the --preserve-run option.

To access the environment variables, you must create a script that executes the Lift command. On Linux and Mac, the lift command must be sourced rather than executed directly as shown in the following example:
run_extract.sh:
. lift extract MYSCHEMA TABLE1
if [[ $LIFT_RC == "-3003"]]; then ...; else ...; fi

Examples

Each example assumes that unless specified, required options are provided as environment variables. If the command specifies a property file, then each line of the property contains key=value where key can be the long or short name for the option. For options without an argument, use key=true to enable the option. For example, verbose=true.
  1. Extracts data from a table in IBM Integrated Analytics System.
    $ lift extract -ss MYSCHEMA -st TABLE1 -sdt ias
  2. Extracts a 1.5 TB PDA source table in 500 MB chunks into the specified directory and file.
    The first part extracts as /foobar/footable_part1.csv, the second as /foobar/footable_part2.csv and the third as /foobar/footable_part3.csv.
    $ lift extract -ss MYSCHEMA -st TABLE1 -s 500 -f /foodir/footable.csvl
  3. Extracts a 1.5 TB PDA source table in 500 MB chunks.
    The first part extracts as /MYSCHEMA.TABLE1_part1.csv, the second as /MYSCHEMA.TABLE1_part2.csv and the third as /MYSCHEMA.TABLE1_part3.csv.
    $ lift extract -ss MYSCHEMA -st TABLE1 -s 500
  4. Extracts a PDA source table to /MYSCHEMA.TABLE1.csv
    $ lift extract -ss MYSCHEMA -st TABLE1
  5. Extracts the entire table from a PDA source to /foodir/footable.csv
    $ lift extract -ss MYSCHEMA -st TABLE1 -f /foodir/footable.csv