Results 1 to 8 of 8

Thread: Load Issue

  1. #1
    Join Date
    Dec 2004
    Posts
    54

    Unanswered: Load Issue

    Hi,

    When I try to load ascii postion file by DB2 LOAD I am getting the following error:

    SQL3119W The field value in row "1" and column "116" cannot be converted
    to an INTEGER value. A null was loaded.


    Problem is my table has only 20 columns, from where the column 116 is coming?

    Thanks,

  2. #2
    Join Date
    Dec 2004
    Posts
    54
    Can anybody help on this issue?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 may be reading your input file as one record. Check the manual or the Control Center for options that may interpret you file with line feeds after each record.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    may be posting the load statement and a sample input row helps as well.
    Juliane

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by juliane26
    may be posting the load statement and a sample input row helps as well.
    Export of 1 row of data in ASCII...

    HTML Code:
    $ db2 "export to int.del of del select * from db2inst1.int fetch first 1 rows only"
    SQL3104N  The Export utility is beginning to export data to file "int.del".
    
    SQL3105N  The Export utility has finished exporting "1" rows.
    
    
    Number of rows exported: 1
    LOAD ASCII data into table...

    PHP Code:
    $  db2 "load from int.del of del insert into test.int"
    SQL3501W  The table space(sin which the table resides will not be placed in
    backup pending state since forward recovery is disabled 
    for the database.

    SQL3039W  The memory available to LOAD for DATA BUFFER prohibits full LOAD
    parallelism
    .  Load parallelism of "4" will be used

    SQL3109N  The utility is beginning to load data from file 
    "/tmp/int.del".

    SQL3500W  The utility is beginning the "LOAD" phase at time "12-19-2005
    15:34:00.721942"
    .

    SQL3519W  Begin Load Consistency PointInput record count "0".

    SQL3520W  Load Consistency Point was successful.

    SQL3110N  The utility has completed processing.  "1" rows were read from the
    input file
    .

    SQL3519W  Begin Load Consistency PointInput record count "1".

    SQL3520W  Load Consistency Point was successful.

    SQL3515W  The utility has finished the "LOAD" phase at time "12-19-2005
    15:34:01.323259"
    .


    Number of rows read         1
    Number of rows skipped      
    0
    Number of rows loaded       
    1
    Number of rows rejected     
    0
    Number of rows deleted      
    0
    Number of rows committed    
    1


    HTH.

  6. #6
    Join Date
    Oct 2005
    Posts
    109
    well, I actually meant the row itself ...
    Anyway:

    What is your problem now ?
    Juliane

  7. #7
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by juliane26
    well, I actually meant the row itself ...
    Anyway:

    What is your problem now ?

    Check this out...

    HTML Code:
    $  db2 "create table nam(id int, first varchar(10))"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into nam values(1, 'jack')"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into nam values(2, 'jill')"
    DB20000I  The SQL command completed successfully.
    $ db2 "export to nam.del of del select * from nam"
    SQL3104N  The Export utility is beginning to export data to file "nam.del".
    
    SQL3105N  The Export utility has finished exporting "2" rows.
    
    
    Number of rows exported: 2
    
    $ more nam.del
    1,"jack"
    2,"jill"
    $ db2 "create table na(id int, first varchar(10))"
    DB20000I  The SQL command completed successfully.
    $ db2 "load from nam.del of del insert into na"
    SQL3501W  The table space(s) in which the table resides will not be placed in
    backup pending state since forward recovery is disabled for the database.
    
    SQL3039W  The memory available to LOAD for DATA BUFFER prohibits full LOAD
    parallelism.  Load parallelism of "4" will be used
    
    SQL3109N  The utility is beginning to load data from file "/tmp/nam.del".
    
    SQL3500W  The utility is beginning the "LOAD" phase at time "12-19-2005
    18:16:55.406990".
    
    SQL3519W  Begin Load Consistency Point. Input record count = "0".
    
    SQL3520W  Load Consistency Point was successful.
    
    SQL3110N  The utility has completed processing.  "2" rows were read from the
    input file.
    
    SQL3519W  Begin Load Consistency Point. Input record count = "2".
    
    SQL3520W  Load Consistency Point was successful.
    
    SQL3515W  The utility has finished the "LOAD" phase at time "12-19-2005
    18:16:56.056472".
    
    
    Number of rows read         = 2
    Number of rows skipped      = 0
    Number of rows loaded       = 2
    Number of rows rejected     = 0
    Number of rows deleted      = 0
    Number of rows committed    = 2
    
    $

    HTH.

  8. #8
    Join Date
    Jan 2004
    Posts
    49
    Try this way,

    While doing Export:
    db2 "export to <<path & file Name>> of <<file type>> modified by <<col del>> <<char del>> <<Select query>> "


    While doing Load:
    db2 load from <<path & file Names>> of <<file type>> modified by <<col del>> <<char del>> insert into <<tablename>>

    other syntax will be the same.

Posting Permissions

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