Unloading data with dbunload

You use the dbunload command to unload data from IBM® Db2® Warehouse systems into a file. Depending on the location of the output data file directory, you can use the dbunload command in local mode or remote mode, when the files must be transferred over the network.

About this task

Data can be unloaded using local or remote mode:

Local mode means that the output data file is directly available for the database server on its filesystem. You would usually run the dbunload command from the Db2 Warehouse container.
Remote mode is used if dbunload is invoked from a client machine, that is you use the dbunload command from the client container. In this case, data is unloaded into a file stored on the client filesystem so it needs to be transferred over the network.

By default, the mode is identified automatically depending on the value of the –host parameter: if host is localhost or 127.0.0.1, then the transfer is local, otherwise it is remote. You can also control it with the -remoteSource <YES|GZIP> parameter. When set, remote mode is forced. With the GZIP option, data is compressed before it is transferred, and then decompressed. This might improve performance if network is bottleneck.

Note: Ensure that the output file directory has the correct permissions set, that is, when unloading data to a particular directory, the user which authenticates to a database must have write permissions on that directory. For example, when unloading data as bluadmin when the output file directory is a home directory of the db2inst1 user, then that directory must have 777 mode set.

Procedure

Use the dbunload command in the Db2 Warehouse container to unload data in local mode:
1. Ensure that the output data file directory is located in a directory specified by the parameter EXTBL_LOCATION of database configuration.
  The path is different for Db2 Warehouse and for Integrated Analytics System. Ensure you set dbunload -df option to a proper directory. To find out EXTBL_LOCATION run the following command:
```
db2 get db cfg | grep -i extbl_location
Strict I/O for EXTBL_LOCATION (EXTBL_STRICT_IO) = NO
Allowed paths for external tables (EXTBL_LOCATION) = /mnt/blumeta0/home
```
  Default paths are:
  - Db2 Warehouse: /mnt/blumeta0/home
  - Integrated Analytics System: /scratch/home
  Note that these are the paths of the container filesystem.
2. Set -host option to localhost.
3. Do not specify -remoteSource.
Example:
```
dbunload -host localhost -port 50000 -u bluadmin -db bludb -schema test 
-t t1 -df /mnt/blumeta0/home/unloaded_data/t1.dat -delim '|' -verbose
```
Use the dbunload command in the client container to unload data in remote mode:
1. Ensure that the output data file directory is located in the local filesystem.
2. Set -host option to Db2 Warehouse host. Remote mode is set automatically.
Example:
```
dbunload -host db_host -port 50000  -u bluadmin -pw bluadmin -db bludb 
-schema test -t t1 -df /tmp/data/t3.dat -delim '|' -verbose  
```
If a client container is deployed on the same host machine as the Db2 Warehouse container, you can run dbunload from this client container and perform data unload in local mode, but this requires additional configuration steps:
1. The output data file directory must be in a directory specified by the parameter EXTBL_LOCATION of database configuration. The path is different for Db2 Warehouse and for Integrated Analytics System. Ensure you set dbunload -df option to a proper directory. To find out EXTBL_LOCATION run the following command:
```
db2 get db cfg | grep -i extbl_location
Strict I/O for EXTBL_LOCATION (EXTBL_STRICT_IO) = NO
Allowed paths for external tables (EXTBL_LOCATION) = /mnt/blumeta0/home
```
2. When deploying the client container, you must add an option to the docker run command to mount a directory used by Db2 Warehouse database as EXTBL_LOCATION, for example -v /mnt/clusterfs/home:/mnt/blumeta0/home
3. Set -host to localhost and do not specify -remoteSource.
Example:
```
dbunload -host localhost -port 50000  -u bluadmin -pw bluadmin -db bludb 
-schema test -t t1 -df /mnt/blumeta0/home/unloaded_data/t1.dat -delim '|’ -verbose
```