Load Issue

allian · #1 (**permalink**) 12-17-05, 12:37

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,

allian · #2 (**permalink**) 12-17-05, 20:01

Can anybody help on this issue?

Marcus_A · #3 (**permalink**) 12-17-05, 20:09

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.

juliane26 · #4 (**permalink**) 12-19-05, 04:37

may be posting the load statement and a sample input row helps as well.

ggnanaraj · #5 (**permalink**) 12-19-05, 05:08

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(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/int.del".



SQL3500W  The utility is beginning the "LOAD" phase at time "12-19-2005

15:34:00.721942".



SQL3519W  Begin Load Consistency Point. Input 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 Point. Input 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.

juliane26 · #6 (**permalink**) 12-19-05, 05:13

well, I actually meant the row itself ...
Anyway:

What is your problem now ?

ggnanaraj · #7 (**permalink**) 12-19-05, 07:44

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.

bala_e · #8 (**permalink**) 12-22-05, 02:08

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech