DB2 Examples
FastBCP exports IBM Db2 in two ways:
| Connection type | Client software | Output | When to use it |
|---|---|---|---|
adbc_db2 (since 1.7.0) | None: the ArpeDb2 driver is embedded | Parquet | Db2 for LUW (and Db2 for i queries) to Parquet, the fastest path |
odbc | IBM Db2 ODBC / CLI driver | CSV, TSV, JSON, Parquet, … | Other output formats, or Db2 for z/OS |
Native ADBC Connector (adbc_db2)
--connectiontype adbc_db2 reads Db2 through the embedded ArpeDb2 driver: no IBM client to install. --database is required; the port defaults to 50000. See Connection Parameters for TLS and the supported targets.
Single-threaded export:
.\FastBCP.exe `
--connectiontype "adbc_db2" `
--server "db2server.company.corp:50000" `
--database "SAMPLE" `
--user "db2user" `
--password "password" `
--sourceschema "DB2ADMIN" `
--sourcetable "ORDERS" `
--directory "C:\temp" `
--fileoutput "db2_orders.parquet" `
--parquetcompression "Zstd" `
--parallelmethod "None"
Parallel export with Ntile method (Linux):
./FastBCP \
--connectiontype "adbc_db2" \
--server "db2server.company.corp:50000" \
--database "SAMPLE" \
--user "db2user" \
--password "password" \
--sourceschema "DB2ADMIN" \
--sourcetable "ORDERS" \
--directory "/data/export" \
--fileoutput "db2_orders.parquet" \
--parallelmethod "Ntile" \
--distributekeycolumn "ORDER_ID" \
--paralleldegree 8 \
--merge "False"
One export per data partition with DBPartition (Db2 for LUW range-partitioned table; a table that is not range partitioned falls back to None):
.\FastBCP.exe `
--connectiontype "adbc_db2" `
--server "db2server.company.corp:50000" `
--database "SAMPLE" `
--user "db2user" `
--password "password" `
--sourceschema "DB2ADMIN" `
--sourcetable "SALES_HISTORY" `
--directory "C:\temp\sales_history" `
--fileoutput "sales_history.parquet" `
--parallelmethod "DBPartition" `
--paralleldegree 4
TLS (Db2's TLS runs on the server's SSL port, here 50001), with a connection string:
./FastBCP \
--connectiontype "adbc_db2" \
--sourceconnectstring "Server=db2server.company.corp;Port=50001;Database=SAMPLE;User Id=db2user;Password=password;SSL Mode=verify-full;SSL Root Cert=/etc/ssl/db2/ca.pem" \
--query "SELECT * FROM DB2ADMIN.ORDERS WHERE ORDER_DATE >= CURRENT DATE - 30 DAYS" \
--directory "/data/export" \
--fileoutput "db2_orders_last30.parquet" \
--parallelmethod "None"
Since FastBCP 1.7.0, a --query on Db2 (adbc_db2, or odbc when the driver reports a Db2 server) accepts Db2 special registers written as several words (CURRENT DATE, CURRENT TIMESTAMP(6), CURRENT SCHEMA, …) and labeled durations (CURRENT DATE - 30 DAYS, ORDER_DATE + 1 MONTH). Up to 1.6 these queries failed to parse.
ODBC: Connection String
With --connectiontype odbc, install the IBM Db2 ODBC driver, then provide either a DSN (--dsn) or a full connection string (--sourceconnectstring) as shown below.
On a Windows machine whose regional settings use a decimal comma (French, German, …), IBM's Db2 ODBC / CLI driver returned DECIMAL values multiplied by 10^scale (173665.47 read as 17366547), with no error. Since FastBCP 1.7.0, FastBCP adds Patch2=15 to the connection when it detects IBM's driver on a Db2 server, keeping the Patch2 values you give, and logs the resulting list. Nothing to change in your commands.
Single-threaded export:
.\FastBCP.exe `
--connectiontype "odbc" `
--sourceconnectstring "Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=db2server.company.corp;Port=25010;Protocol=TCPIP;Uid=db2user;Pwd=password;CurrentSchema=DB2ADMIN" `
--query "SELECT * FROM DB2ADMIN.ORDERS" `
--directory "C:\temp" `
--fileoutput "db2_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "None"
Parallel export with Ntile method:
.\FastBCP.exe `
--connectiontype "odbc" `
--sourceconnectstring "Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=db2server.company.corp;Port=25010;Protocol=TCPIP;Uid=db2user;Pwd=password;CurrentSchema=DB2ADMIN" `
--query "SELECT * FROM DB2ADMIN.ORDERS" `
--directory "C:\temp" `
--fileoutput "db2_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "Ntile" `
--distributekeycolumn "ORDER_ID" `
--paralleldegree -2 `
--merge "False"
Parallel export with Random method:
.\FastBCP.exe `
--connectiontype "odbc" `
--sourceconnectstring "Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=db2server.company.corp;Port=25010;Protocol=TCPIP;Uid=db2user;Pwd=password;CurrentSchema=DB2ADMIN" `
--query "SELECT * FROM DB2ADMIN.ORDERS" `
--directory "C:\temp" `
--fileoutput "db2_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "Random" `
--distributekeycolumn "ORDER_ID" `
--paralleldegree 8 `
--merge "False"
DSN Connection
If a DSN is already configured on the machine, you can use --dsn instead of --sourceconnectstring.
.\FastBCP.exe `
--connectiontype "odbc" `
--dsn "DB2_DSN" `
--query "SELECT * FROM DB2ADMIN.ORDERS" `
--directory "C:\temp" `
--fileoutput "db2_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "None"
If your DB2 schema or table name contains special characters (e.g. HCR_BI."APER@XPSAHISTO"), keep the double quotes around the identifier inside your --query value so DB2 treats it as a delimited identifier.
Build your command with the Wizard