cancel
Showing results for 
Search instead for 
Did you mean: 

MaxDB : LOADER

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

> 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

Former Member
0 Kudos

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

Former Member
0 Kudos

OK figured out the format for START:

The documentation does indicate that START requires param1 but that param2 is optional - param2 is required.

START must be the last command in the string, ie.

IMPORT TABLE ............... START x y

lbreddemann
Active Contributor
0 Kudos

HI Pierre,

well, I still don't have much time to put into this...

I tried out some things with your test data but I've to admit: the easiest (and only) thing I got to work was to bulk load the data into CHAR fields and perform the type conversion then in the database.

regards,

Lars

Former Member
0 Kudos

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

Former Member
0 Kudos

By the way:

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

lbreddemann
Active Contributor
0 Kudos

> 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