A contact of mine uses DB2 and suggested the following (I know nothing anymore about how DB2 runs)
>You may need administrative access and there are other prerequisites that need to be installed on your local instance
+ First option:
Link to the Excel workbook
Connecting Excel to IBM Db2 Warehouse (copied from their website incase it goes down)
In the web console, go to the Run SQL page.
Type one or more SELECT statements in the editor text box.
Click one of the Run options.
Click Excel ODC File.
Download and open the BLUExcel.odc file in Excel.
If a security notice is displayed, click Enable.
Click Open to connect to the Db2 Warehouse database. The Connect To DB2 Database dialog box opens.
Type the user ID and password that you use to log in to Db2 Warehouse. To obtain the user ID and password, click Connect in the web console or Connect > Connection Information in the web console.
Ensure that the connection mode is Share, and then click OK.
Results
+Second option:
+ Use "Save As" to convert the excel file to CSV format.
+ Add temporary table to the DB2 schema set to hold the import, strongly advises AGAINST direct import to a production table - bad things can and do happen to existing datasets when appending from an outside source.
+
Follow the instructions as given at the IBM-DB2 support page (click here) (Copied and pasted below incase their site is down... )
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 - on the ibm site shows the command line interface - didn't import due to possible copyright)
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'.
1
