By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,606 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,606 IT Pros & Developers. It's quick & easy.

How can I bulk-load a typed table?

DTV12345
P: 5
Hello!

I have been assigned to bulk-load data into several typed tables of our ODS. I am not clear as to whether it can be done.

Here's my homework ===>>>

The easy way is to perform INSERT statements manually, like this

Expand|Select|Wrap|Line Numbers
  1. insert into 
  2. callbacks
  3. (
  4. account_info, nbr_clbk, load_date
  5. values (
  6. account_obj() ..account_number('123456') ..account_status('ACTV') ..dma_id('803') ..dma_name('Los Angeles') ..state('CA') ..zipcode('90245') 
  7. , 1, '2007-02-20');
This works fine. The challenge is that I have to load about 500K rows daily for about 23 tables. The other challenge is that the source data I have to load is contained in "flat", normal tables.

I have tried several things. My first approach was to dump data from one of the flat tables and import/load back:

a) Import utility
Expand|Select|Wrap|Line Numbers
  1. IMPORT FROM "Z:\clbk_data.del" 
  2. OF DEL MODIFIED BY COLDEL, DATESISO METHOD P (2, 3, 4) 
  3. MESSAGES "Z:\clbk_data_load.out" 
  4. replace into callbacks  (account_info, nbr_clbk, load_date);
It fails:
Expand|Select|Wrap|Line Numbers
  1. SQL3037N  An SQL error "-408" occurred during Import processing.
b) Load utility
Expand|Select|Wrap|Line Numbers
  1. LOAD CLIENT FROM "Z:\clbk_data.del" OF DEL MESSAGES "Z:\clbk_data_load.out" INSERT INTO callbacks COPY NO INDEXING MODE AUTOSELECT
It fails:
Expand|Select|Wrap|Line Numbers
  1. SQL3529N  The "LOAD" operation encountered the unsupported data type "Structured Data Type" in column "1".
c) I also tried (what I call) "derived" INSERT.

Expand|Select|Wrap|Line Numbers
  1. insert into 
  2. callbacks (account_info, nbr_clbk, load_date) 
  3. select 
  4. account_number, account_status, dma_id, dma_name, state, zipcode, nbr_clbk, load_date from flat_table.
It fails....

Can somebody please help? If I have to write a program, I will but I'd like to know if this can be avoided...

My apologies for the long message. TIA for helping,

Al.

PS: My configuration is:
IBM DB v9.1
Linux RH
Jun 14 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.