NAME

xbcp - Bulk copy for Sybase or Oracle

SYNOPSIS

xbcp [[database.]owner|schema.]table {in|out} [file] [options]

 Options:
   -S     server
   -D     database
   -r     row terminator (default: "\n")
   -t     field terminator (default: "|")
   -T     database type (Oracle|Sybase)
   -env   environment
   -U     username
   -P     password
   -H     column header row exists in first row of file
   -h     # of column headers at beginning of file
   -A     network packet size
   -O     arbitrary list of options to pass through to bcp
   -file  file containing list of tables to process (See Table File Format section below)
   -skip  skip bcp of initial table/file, only bcp for '-2' options
   -proc  number of concurrent processes (default: 5, max: 20)
   -dblib Database library to use (default: DBIX_BULKUTIL_LIB env variable or "DBIx::BulkUtil")
   -d     debug
   -help  brief help message
   -man   full documentation

  BCP in options:
   -a      A=Append(default),R=Replace,T=Truncate (Oracle or Sybase)
   -commit Commit size in rows
   -errcnt Max # of errors allowed before abort
   -C      Comma separated list of columns in file (or
           this argument may appear multiple times, once
           for each column).
   -F      Comma separated list of filler columns in file which
           will be ignored and not loaded (or this argument
           may appear multiple times, once for each column).

  BCP in (Oracle only) options:
   -blank preserve blanks in char/varchar columns
   -p     Use direct path load (default: conventional)
   -dp    Use direct path parallel load (does not rebuild
          indexes after load).
   -dfmt  Date format (default: YYYY-MM-DD HH24:MI:SS.FF3)
   -q     fields optionally enclosed by quotes
   -nls_lang NLS lang to use in control file
   -check  just generate control file (implies -d)
   -const  set constant in control file(e.g. fieldname:value)
           (must be literal value)
   -fconst set function constant in control file(e.g. fieldname:value)
           (e.g. asof_date:TO_DATE('2012-01-01', 'YYYY-MM-DD'))
   -when   Specify a WHEN clause, e.g., to skip comments, "(1:1) != '#'"

  Sybase BCP out && Oracle BCP in options:
   -n     Sybase native date format
          (This is the default when BOTH bcp'ing
           out of Sybase AND using the "bcp into
           second database" options. Overrides -dfmt).
          When exporting a large amout of data
          from Sybase (w/native bcp) it can take a
          long time when NOT using this option.

  BCP out options:
   -f      SQL where clause
   -c      column list (does not use native Sybase bcp)
   -tmpdb  Database to use for creating temporary views (default: scratchdb).
   -nobulk Do not use bcp for Sybase export.
   -blobsz max read size for BLOBs (default: 1MB).
   -date_fmt        Date format (except for native Sybase bcp).
                    For Oracle, default is 'YYYY-MM-DD HH:MI:SS'.
                    For Sybase, default is 'ISO' (see L<DBD::Sybase|DBD::Sybase>.
   Oracle only:
   -datetime_fmt    Datetime format (default: 'YYYY-MM-DD HH:MI:SS.FF')
   -datetime_tz_fmt Datetime tz format (default: 'YYYY-MM-DD HH:MI:SS.FF')
                    datetime_tz_formate will first default to datetime_fmt, and
                    then to 'YYYY-MM-DD HH:MI:SS.FF'.
                    When exporting from Oracle, and into Sybase,
                    default will be 'YYYY-MM-DD HH:MI:SS.FF3'.

  BCP into a second database:
   -check  check column names and order and
           generate control file (bcp file must exist)
   -verify just check column names and order.
   -2S     server
   -2D     database
   -2SCH   schema
   -2T     db_type
   -2env   environment
   -2t     table
   -2U     user
   -2P     password
   -2A     network packet size
   -2C     List of columns in file (See -C option).
   -2F     List of filler columns in file (See -F option).
   -2O     arbitrary list of bcp options

DESCRIPTION

xbcp will load data to a table from a file, or extract data from a table to a file.

If a server is provided, the database type is assumed to be Sybase. If no server but a database is provided, then the default database type is Oracle. Otherwise the server defaults to the environment variable DSQUERY.

If no file is provided, it is assumed to be the table name with a ".bcp" extension. If no table is provided, it is assumed to be the file name without the extension.

If the file name has a ".csv" extension, the default field terminator is ",".

The where clause for the -f option is just appended to the default SQL statement, so it must include the keyword "where". When the -f option is used, the native Sybase bcp is not used, and the -n option is ignored.

If you bcp a large amount of data out of Sybase, and the native Sybase bcp is used, you should SERIOUSLY CONSIDER using the -n option. Otherwise, your process will appear to hang for a long time after the actual bcp is done.

When loading into Oracle, and any data for for any date column(s) is found within the first 1000 rows, xbcp will try to automatically determine the date format of the column(s). If no data is found within the first 1000 rows, or if xbcp can not determine the format, then the default date format will be used (also see the -dfmt and -n options).

If either of the -P or -2P options are equal to "-", then the password(s) will be read from STDIN.

Table File Format

If the -file option is used to supply a list of tables for bcp, then no source/target table nor any bcp file name should be on the command line, and the format of the file is:

  # Comments
  src_table1 [tgt_table1] [options]
  src_table2 [tgt_table2] [options]

The target table name is optional. Options are in the format -option=value or -option="some value with spaces". Options in the table file will override any options on the command line for that table. The allowed options are const, fconst, f, and a.