db2 load

[jrthor2]

I have a shell script I am trying to run that loads a table from a
control file. The file I am trying to load is delimited by the "}"
character. How do I set the delimeter code? Here is my load
statement:

db2 load client from /loadspace/re000000.txt of del replace into
INET.INSRP_SURP_PROP_TBL nonrecoverable
To be even more explicit, I am trying to replicate the below oracle
load command in DB2:

OPTIONS(ERRORS=10)
LOAD DATA
INFILE '/loadspace/re000000.txt'
BADFILE '/home/prgyw1/re000000.bad'
DISCARDFILE '/home/prgyw1/re000000.dsc'
REPLACE
INTO TABLE INSRP_SURP_PROP_TBL
(
insrp_prop_addr TERMINATED BY '}',
insrp_prop_cty TERMINATED BY '}',
insrp_prop_st TERMINATED BY '}',
insrp_prop_zip TERMINATED BY '}',
insrp_prop_sub_catg TERMINATED BY '}',
insrp_prop_sq_footage TERMINATED BY '}' OPTIONALLY ENCLOSED BY '.',
insrp_prop_nbr TERMINATED BY '}',
insrp_prop_stat TERMINATED BY '}',
insrp_prop_srp TERMINATED BY WHITESPACE

Thanks!
--
jrthor2
------------------------------------------------------------------------
jrthor2's Profile: http://www.dbtalk.net/m455
View this thread: http://www.dbtalk.net/t314419

[Shashi Mannepalli]

If the INPUT file is a del file type { then you use this syntax

db2 "load from <file> of <type> modified by chardel{ insert/replace
into <table>
nonrecoverable"
cheers...
Shashi Mannepalli

jrthor2 wrote:

I have a shell script I am trying to run that loads a table from a
control file. The file I am trying to load is delimited by the "}"
character. How do I set the delimeter code? Here is my load
statement:

db2 load client from /loadspace/re000000.txt of del replace into
INET.INSRP_SURP_PROP_TBL nonrecoverable
To be even more explicit, I am trying to replicate the below oracle
load command in DB2:

OPTIONS(ERRORS=10)
LOAD DATA
INFILE '/loadspace/re000000.txt'
BADFILE '/home/prgyw1/re000000.bad'
DISCARDFILE '/home/prgyw1/re000000.dsc'
REPLACE
INTO TABLE INSRP_SURP_PROP_TBL
(
insrp_prop_addr TERMINATED BY '}',
insrp_prop_cty TERMINATED BY '}',
insrp_prop_st TERMINATED BY '}',
insrp_prop_zip TERMINATED BY '}',
insrp_prop_sub_catg TERMINATED BY '}',
insrp_prop_sq_footage TERMINATED BY '}' OPTIONALLY ENCLOSED BY '.',
insrp_prop_nbr TERMINATED BY '}',
insrp_prop_stat TERMINATED BY '}',
insrp_prop_srp TERMINATED BY WHITESPACE

Thanks!
--
jrthor2
------------------------------------------------------------------------
jrthor2's Profile: http://www.dbtalk.net/m455
View this thread: http://www.dbtalk.net/t314419

[jrthor2]

I got the columns in the correct order now on my table, but when I run
the load comand, I get this in my log file:

SQL3109N The utility is beginning to load data from file
"/loadspace/re000000.txt".

SQL3500W The utility is beginning the "LOAD" phase at time
"06/21/2006
15:31:32.235126".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3137W Row "F0-1" is too short. At least one input value being
loaded to a
non-nullable column is missing. The row was not loaded.

SQL3185W The previous error occurred while processing data from row
"F0-1" of
the input file.

I get this for each row it tries to read (210 rows), then it goes to
this:

SQL3227W Record token "F0-1" refers to user record number "1".

Again, I get this for each record (210), then I get this:

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

SQL3519W Begin Load Consistency Point. Input record count = "210".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time
"06/21/2006
15:31:34.322788".

SQL3107W There is at least one warning message in the message file.
Number of rows read = 210
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 210
Number of rows deleted = 0
Number of rows committed = 210

The load does not put anything in the database. Could someone please
help me get this working.

Thanks a million!!!
--
jrthor2
------------------------------------------------------------------------
jrthor2's Profile: http://www.dbtalk.net/m455
View this thread: http://www.dbtalk.net/t314419

[Ian]

jrthor2 wrote:

I got the columns in the correct order now on my table, but when I run
the load comand, I get this in my log file:

Why don't you post 1 or 2 records from your input file. That would make
it a lot easier to help you since most of us probably don't know Oracle
SQLLDR syntax.

FYI, you probably want "modified by COLDEL}" and not "modified by chardel}"

[jrthor2]

Sure, here is 3 rows from my delimited file:

37040.00}A}Oaklawn Blvd.}HOPEWELL}VA}23860}10818.00}Shopping Center}Yes
36320.00}A}SEC Old Highway 53 & Olympic
Drive}CLEARLAKE}CA}95422}101160.00}Land}Yes
37732.00}A}3422 Main Street}MARLETTE}MI}48453}8050.00}Shopping
Center}Yes

I get these errors with coldel}, sorry.
--
jrthor2
------------------------------------------------------------------------
jrthor2's Profile: http://www.dbtalk.net/m455
View this thread: http://www.dbtalk.net/t314419

[Ian]

jrthor2 wrote:

Sure, here is 3 rows from my delimited file:

37040.00}A}Oaklawn Blvd.}HOPEWELL}VA}23860}10818.00}Shopping Center}Yes
36320.00}A}SEC Old Highway 53 & Olympic
Drive}CLEARLAKE}CA}95422}101160.00}Land}Yes
37732.00}A}3422 Main Street}MARLETTE}MI}48453}8050.00}Shopping
Center}Yes

Are those linebreaks intentional, or do the appear because your
newsreader put them in.

Anyway, coldel} should work:

load from yourfile of del modified by coldel} insert into yourtable
If this fails, what's the error?

[jrthor2]

yes, the line break is because of the editor, each line is on one line.

My log is attached with the errors in it (I had to remove a bunch of
lines because ti was too big to attach, but you'll get the idea, I'm
sure). After this runs, my table does not have any data in it.

Thanks.
+-------------------------------------------------------------------+
|Filename: inwk292a_db2.txt |
|Download: http://www.dbtalk.net/a5 |
+-------------------------------------------------------------------+

--
jrthor2
------------------------------------------------------------------------
jrthor2's Profile: http://www.dbtalk.net/m455
View this thread: http://www.dbtalk.net/t314419