Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: DB2 LOAD Problem

    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:

    Code:
    CONNECT TO DB2BENCH;
    LOAD FROM "D:\DateSort.txt" OF DEL MODIFIED BY COLDELOx20 FASTPARSE NOROWWARNINGS 
    USEDEFAULTS METHOD P (1, 2, 3, 4, 5, 6, 7) MESSAGES "D:\db2benchmark\Statusnachrichten" 
    INSERT INTO RICHTER.DATESINFO (CALYEAR, CALMONTH, CALDAY, DAYHOUR, CALDATE, DAYOFWEEK, ID)
    NONRECOVERABLE INDEXING MODE REBUILD LOCK WITH FORCE;
    CONNECT RESET;
    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.
    Last edited by nafox; 02-28-10 at 04:52.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    what is the warning in the message file ???
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    "METHOD P (1, 2, 3, 4, 5, 6, 7)" specification does not seem appropriate for the input file you have provided.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2010
    Posts
    2
    I have worked the whole night so sorry for overlooking that the massage file is in German.
    The basic problem is that I cant 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 wont 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 its 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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2010
    Posts
    335
    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.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ;-)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by sathyaram_s View Post
    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
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Cougar8000 View Post
    Is it 4pm yet
    Still working "Late in, Early out"

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    isn't that the norm??
    Quote Originally Posted by db2girl View Post
    Still working "Late in, Early out"
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by db2girl View Post
    Still working "Late in, Early out"
    Damn it. Who turned the video monitor on
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •