Hi

Problem is that no data is dumped into the Table.

Informationtoken: "DB2 Express-C", "DB2 v9.7.100.177", "s091114", "IP23028" und FixPak "1".

I´m trying to dump a space-delimited text file into DB2 with following statement:

OS: Windows Server 2003

Table Datesinfo looks like this:
CREATE TABLE datesinfo (
calyear SMALLINT NOT NULL,
calmonth SMALLINT NOT NULL,
calday SMALLINT NOT NULL,
dayhour SMALLINT NOT NULL,
caldate date NOT NULL,
dayofweek SMALLINT NOT NULL,
id SMALLINT NOT NULL PRIMARY KEY );

Text file looks like this (Unix format):
20091201-00 2009 12 1 0 2009-12-01 2 1
20091201-01 2009 12 1 1 2009-12-01 2 2
20091201-02 2009 12 1 2 2009-12-01 2 3
..........

Getting following message:
SQL3501W Der oder die Tabellenbereiche, die die Tabelle enthalten, werden
nicht in den Status 'Backup anstehend' versetzt, da die aktualisierende
Recovery für die Datenbank nicht aktiviert wurde.

SQL3109N Das Dienstprogramm beginnt mit dem Laden von Daten aus der Datei
"D:\DateSort.txt".

SQL3500W Die Phase "LOAD" wird gestartet (Zeit: "2010-02-28
05.45.45.296927").

SQL3519W Synchronisationspunkt am Beginn des Ladevorgangs. Eingabesatzzähler:
"0".

SQL3520W Synchronisationspunkt für Ladevorgang erfolgreich.

SQL3110N Die Verarbeitung des Dienstprogramms ist abgeschlossen. Es wurden
"3" Zeile(n) aus der Eingabedatei gelesen.

SQL3519W Synchronisationspunkt am Beginn des Ladevorgangs. Eingabesatzzähler:
"3".

SQL3520W Synchronisationspunkt für Ladevorgang erfolgreich.

SQL3515W Die Phase "LOAD" wurde beendet (Zeit: "2010-02-28 05.45.45.326030").

SQL3500W Die Phase "BUILD" wird gestartet (Zeit: "2010-02-28
05.45.45.326907").

SQL3213I Der Indexierungsmodus ist "REBUILD".

SQL3515W Die Phase "BUILD" wurde beendet (Zeit: "2010-02-28
05.45.45.404151").

SQL3107W Die Nachrichtendatei enthält mindestens eine Warnung.


Anzahl gelesener Zeilen = 3
Anzahl übersprungener Zeilen = 0
Anzahl geladener Zeilen = 0
Anzahl zurückgewiesener Zeilen = 3
Anzahl gelöschter Zeilen = 0
Anzahl festgeschriebener Zeilen = 3

It´s kinda urgand so I would be really grateful if one could help me.

what is the warning in the message file ???

"METHOD P (1, 2, 3, 4, 5, 6, 7)" specification does not seem appropriate for the input file you have provided.

I have worked the whole night so sorry for overlooking that the massage file is in German.
The basic problem is that I can´t specify a white space as a delimiter.
The file I have looks like this (and not like the one above, sorry again):
2009 12 1 0 2009-12-01 2 1
2009 12 1 1 2009-12-01 2 2
2009 12 1 2 2009-12-01 2 3

So 7 values delimited by whitespace and line feed for new line (UNIX format).

I tried it with COLDEL0x20 and COLDELX'20' but both won´t work. It seems like just the first character 0 respectively X is treaded as delimiter.

Does LOAD even support whitespace as delimiter?

Now all I want to know is how to get the text file into the datesinfo table (see first post)? Since it´s a large text file (~25 gb and 1100000000 rows) I thought LOAD is the right choice to dump it into the table.

Sorry if this is a newbie question but I normally working with postgres and I am rather new to db2.

Hope my english is understandable

There is no such thing as "whitespace" in ASCII. It may be blank, or tab character, or some other unprintable character. In most cases you can specify the delimiter for DB2 use the use for the LOAD command (I assume you have a Command Reference manual to explain how to do that).

Blank space cannot be used as a delimiter in DEL files

Delimiter considerations for moving data

the options you have :
a) ask the data supplier to change the format to include a delimiter
b) process the input file before loading the data to add the delimiter
c) dump the data as a single string into a staging table column and then parse it to load into the target table.

Hi,

if you start the setup.exe of DB2 with 'setup.exe /i EN', you can install the DB2 Server in English. Then you don't need to translate the error-messages.

Also in earlier Versions the translation of error-messages diddn't make sense and were misleading. (And my personal favorite is 'Pufferpools'.)

Offtopic, but it may help you in future.

Get a manual as it is all in there

My table structure is from your ddl.

your data file
>cat 1.dat
2009 12 1 0 2009-12-01 2 1
2009 12 1 1 2009-12-01 2 2
2009 12 1 2 2009-12-01 2 3


db2 "load from 1.dat of asc method L (1 4, 6 7, 9 9, 11 11, 13 22, 24 24, 26 26) insert into xxxxxx.test (calyear, calmonth, calday, dayhour, caldate, dayofweek, id) NONRECOVERABLE"


Agent Type Node SQL Code Result
__________________________________________________ ____________________________
LOAD 000 +00000000 Success.
__________________________________________________ ____________________________
PRE_PARTITION 000 +00000000 Success.
__________________________________________________ ____________________________
RESULTS: 1 of 1 LOADs completed successfully.
__________________________________________________ ____________________________

Summary of LOAD Agents:
Number of rows read = 3
Number of rows skipped = 0
Number of rows loaded = 3
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 3


db2 "select * from xxxxxxx.test"

CALYEAR CALMONTH CALDAY DAYHOUR CALDATE DAYOFWEEK ID
------- -------- ------ ------- ---------- --------- ------
2009 12 1 0 12/01/2009 2 1
2009 12 1 1 12/01/2009 2 2
2009 12 1 2 12/01/2009 2 3

3 record(s) selected.

Cougar, The month and day part of the file may be either one or two digits ... this is the reason i did not suggest the ASC approach ...

Let's see what the OP thinks ;-)

That is true. In that case there are couple of other very simple solutions that can be implemented in order to get it to work. I am getting ready to pop a cold one and observe. Is it 4pm yet

Still working "Late in, Early out"

isn't that the norm??

Damn it. Who turned the video monitor on