on 09-10-2010 9:19 AM
We are running MaxDB 7.6.03.15 as production database on Windows Server 2008.
We receive daily text files for import into the database of +- 10 million records (1 Gigabyte).
we receive an average three files per day, but at peaks we can receive 8 files
The problem:
Using standard ODBC from our program we loop through and issue an INSERT per record
- about 7 min per Million records.
- total import time about 75-85 minutes
Table we are importing to:
CDRyyyymm
(IMEI Fixed(18),
MSISDN Fixed(12),
Call_Date Date,
Call_Time Time,
RTX_ENT_DATE Date,
CALL_TYPE fixed(3),
CALL_DEST char(32),
CALL_COST fixed(10,2),
DURATION fixed(6),
LBC_DATE Date,
CALLSPONSOR char(22))
We tried to use LOADER, but found the following:
- The file contains a header record (can not suppress header record with LOADER)
- The "IF POS x <> '??????'" fail on the FIXED,DATE,TIME fields- is fine on CHAR field,
- There are three DATE formats (dd/mm/yyyy, yyyy/mm/dd and yyyy-mm-dd) -Note: Data provider does not want to change!
- The prefix "27" need to be removed from the MSISDN field -Note: Data provider does not want to change!
- The prefix "927" need to be removed from the DIALED_NUMBER field -Note: Data provider does not want to change!
- the DIALED_NUMBER field can contain Alpha charaters, that why it is defined as CHAR
If we import the data into a table that is defined as all CHAR() fields, LOADER works nice and quick (8-10 Minutes)
Is there any other solution we can try?
Any assistance would be much appreciated.
Sample Data
IMEI,MSISDN,DIALED_NUMBER,CALL_DATE,CALL_TIME,RTX_ENT_DATE,CALL_TYPE,CALL_DEST,CALL_COST,DURATION,LBC_DATE,CALLSPONSOR
,27746020489,927842080730,02/08/2010,07:17:24 ,2010/08/03,6,NATIONAL,0.00,29,2010-07-15,0
,27746020489,927844753270,02/08/2010,10:00:43 ,2010/08/03,5,NATIONAL,0.00,26,2010-07-15,0
,27746020489,927840631546,02/08/2010,12:17:03 ,2010/08/04,6,NATIONAL,0.00,34,2010-07-15,0
,27746020489,927746122770,02/08/2010,12:58:09 ,2010/08/04,5,NATIONAL,0.00,390,2010-07-15,0
,27746020489,927844146885,02/08/2010,13:09:36 ,2010/08/04,6,NATIONAL,0.00,382,2010-07-15,0
,27746020489,927847698152,02/08/2010,15:32:33 ,2010/08/04,6,NATIONAL,0.00,9,2010-07-15,0
,27746020489,927842080730,03/08/2010,07:24:47 ,2010/08/04,6,NATIONAL,0.00,239,2010-07-15,0
Regards
Pierre du Toit
Technical Director: Novum (Pty) Ltd
Hi
you can try to find solution to your problem in site: [MSISDN|http://www.msisdn.org]. You can even ask Moderator from this website.
All the best
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> We tried to use LOADER, but found the following:
> - The file contains a header record (can not suppress header record with LOADER)
Sure you can - use the <start_spec> clause for the instream specification [http://maxdb.sap.com/doc/7_7/bb/57b641969e461496b960e4b1f4d202/content.htm]
> - The "IF POS x <> '??????'" fail on the FIXED,DATE,TIME fields- is fine on CHAR field,
This needs to be converted to a MaxDB date format first.
You can do this by specifying a free date mask in the data format specification [http://maxdb.sap.com/doc/7_7/80/0cc7d61c3e46b8913fe7deaf7e5de1/content.htm]
> - There are three DATE formats (dd/mm/yyyy, yyyy/mm/dd and yyyy-mm-dd) -Note: Data provider does not want to change!
same as above
> - The prefix "27" need to be removed from the MSISDN field -Note: Data provider does not want to change!
Hmm... you may just use the import as fixed length field file - then you simply skip these two digits.
> - The prefix "927" need to be removed from the DIALED_NUMBER field -Note: Data provider does not want to change!
same as above.
> - the DIALED_NUMBER field can contain Alpha charaters, that why it is defined as CHAR
Ok, so what?
> If we import the data into a table that is defined as all CHAR() fields, LOADER works nice and quick (8-10 Minutes)
>
> Is there any other solution we can try?
Hope my hints give you some advise.
I currently don't have time to actually create the import file definition for you - but the documentation basically covers all of your requirements.
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for the reply.
1. SKIP HEADERS:
import table ....... if pos 1 <> ............. DATA IINSTREAM '...' CSV START 2 date 'dd/mm/yyyy' time 'hh:mm:ss'
Syntax error at position 150 <Unexpected keyword: DATE>
If I move the DATE TIME masks or the START x, the problem still exists.
2. There are THREE different date formats per record: 'dd/mm/yyyy' ' 'yyyy/mm/dd' and 'yyyy-mm-dd'
DATE mask sets the format per session and not per field
Pierre
Lars
Thanks for the input.
Indeed what we have ended up doing We load the data into a "temp" table with the fiirs date converted.,
Then extract into the the online table converting the remainder of the date fields .
insert into unbilled
(imei,msisdn,dialed_number,call_date,call_time,rtx_ent_date,
call_type,call_dest,
call_cost,duration,lbc_date,callsponsor)
(select
imei,msisdn,dialed_number,call_date,call_time,
substr(rtx_ent_date,1,4) & '-' & substr(rtx_ent_date,6,2) & '-' & substr(rtx_ent_date,9,2),
call_type,call_dest,
call_cost,duration,lbc_date,callsponsor from abunbilled)
This process can still take long as there are indexes on the online table.
It appears better to update with the indexes than to drop index, insert bulk, create index.
Pierre
Edited by: Pierre du Toit on Sep 15, 2010 6:18 AM
> It would be nice if the documentation was properly updated.
> In general it is still the same as the original DDB/4, Reflex documentation of 1986
Hi Pierre,
I've to agree that the documentation can always be improved.
But I also have to say that it has been largely enhanced since DDB/4, Reflex, Adabas D or however we named the database in the past.
Concerning the loader, one important aspect here is: this tool has just one single purpose in NetWeaver environments (content server system copies).
Thus it's natural that the documentation is not that extensive on it...
After all MaxDB is a database for NetWeaver. Every other use is more or less a by-product.
Just my two pence on this.
best regards,
Lars
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.