IBM Support

How to import data from a DB2 database table (from a CSV file)

How To


Summary

Customer would like to import data into a Controller DB2 database table, from a CSV (flat file) source.

How can they do this?

Objective

Import all the data from a single CSV file, into a specific DB2 database table.
  
Example:
The source CSV file may have come from a different database (see separate IBM Technote #0741797 for more details).

Environment

This Technote is based on:
  • Controller 10.3.1
  • DB2 10.5 & DB2 11
However, it may work for other versions/combinations.

Steps

There are many different ways to import data into a DB2 database. This Technote suggests using a DB2 command similar to:
db2 IMPORT FROM "C:\UTILS\export.csv" OF DEL INSERT INTO <table_owner>.<tablename>
    
NOTE: It is possible to use the similar DB2 command 'load' (instead of 'import').
  • However, the author does not recommend this because it can lead to the DB2 database becoming stuck in 'backup pending' status (after a failed load).
Steps:
1. Logon to the DB2 database server
2. Launch:  DB2 Command Window - Administrator
3. Run a command similar to:
db2 connect to <database_name>
4. OPTIONAL: If you want to clear the existing contents of the target table (before importing the new data), then run a command similar to:
db2 DB2 truncate table <table_owner>.<tablename>
5. Import the data by runing a command similar to:
db2 IMPORT FROM "C:\UTILS\export.csv" OF DEL INSERT INTO <table_owner>.<tablename>
Example:
db2 IMPORT FROM "C:\UTILS\export.csv" OF DEL INSERT INTO fastnet.xacq_conv
 
6. Check with IBM Support if the database table is designed in a way that requires an extra script to be run now.
Example:
If importing into 'xacq_conv' then you will need to run the following extra scripts:
DB2 SELECT MAX(ino) from FASTNET.XACQ_CONV
This will give a result for example 100000. Therefore use this number plus one (for example 1000001) in the next command:
DB2 ALTER TABLE FASTNET.XACQ_CONV ALTER COLUMN INO RESTART WITH 100001 SET INCREMENT BY 1 SET MINVALUE 1 SET MAXVALUE 2147483647 SET NO CYCLE SET CACHE 500 SET NO ORDER
image-20190627232428-1

Additional Information

Backup Pending
To check if your database is backup pending, type the following:
db2 connect to <database>
db2 get database configuration
Look for the section similar to:
Backup pending                                          = NO
If your database has 'backup pending = YES' then you can easily solve that by backing up the database.
  • TIP: The quickest type of backup is 'backup to NULL'. This causes a 'fake' backup to take place.
The command (for a 'backup to null' varies depending on what operating system your DB2 server is running:
  • Windows: db2 backup db <database> ONLINE to NUL
  • Unix/Linux: db2 backup db <database> ONLINE to /dev/null
The above create online backups. If you want an offline backup, simply remove the word 'online'.

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
27 June 2019

UID

ibm10957549

Page Feedback