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).
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
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'.
Related Information
Document Location
Worldwide
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"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