db2 load problem



IBM's flagship relational database management system

db2 load problem

Postby blue_tina » Wed Feb 03, 2010 7:41 pm

I used a JCL to load some data from a ps to a db2 table. After submitted the JCL, I got the following message and the data is not loaded. Who can tell me how to deal with the problem?
DSNU334I -D931 034 21:57:24.73 DSNURCON - INPUT FIELD 'COL4',
INVALID FOR 'AA.TT',
ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

Here is the data I want to load:
111R100000 2003-03-03 2001-01-01
222R100000 2003-03-03
333R100000 2004-03-03
333R100000 2004-03-03 2001-01-01
333R100000 2004-03-03 2001-01-01

Here is the table layout:
LOAD DATA INDDN SYSREC LOG NO REPLACE NOCOPYPEND
INTO TABLE "AA"."TT"
( "COL1"
POSITION( 00003:00005) CHAR(00003)
, "COL2"
POSITION( 00006:00013) CHAR(00008)
, "COL3"
POSITION( 00014:00023) DATE EXTERNAL
, "COL4"
POSITION( 00025:00034) DATE EXTERNAL
NULLIF(00024)=X'FF'
)
blue_tina
 
Posts: 11
Joined: Fri Jan 15, 2010 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 load problem

 

Re: db2 load problem

Postby dick scherrer » Thu Feb 04, 2010 3:09 am

Hello,

How is the column defined in the table?

The load specifies "date external", but the error mentions date/time. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: db2 load problem

Postby blue_tina » Thu Feb 04, 2010 7:38 am

Hi Dick, thanks for your quick reply! The column is defined as date in the table.
CREATE TABLE AA.TT
(COL1 CHAR(3) FOR SBCS DATA NOT NULL,
COL2 CHAR(8) FOR SBCS DATA NOT NULL,
COL3 DATE NOT NULL,
COL4 DATE WITH DEFAULT NULL,
PRIMARY KEY (COL1,COL2,COL3))
IN DBS.TBS
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
blue_tina
 
Posts: 11
Joined: Fri Jan 15, 2010 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 load problem

Postby blue_tina » Thu Feb 04, 2010 7:42 am

Just my guess, is it perhaps caused by the empty value in the col4 for some lines of the sequential file?
blue_tina
 
Posts: 11
Joined: Fri Jan 15, 2010 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 load problem

Postby dick scherrer » Thu Feb 04, 2010 9:40 am

Hello,

Possibly. That in combination with the x'FF' specification.

Why does one date permit nulls and the other does not?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: db2 load problem

Postby chaat » Mon Feb 08, 2010 7:36 am

It appears that the dates are in ISO format, on the table is the date in ISO or USA format ?
chaat
 
Posts: 14
Joined: Sun Aug 16, 2009 11:07 pm
Location: St. Cloud, Minnesota
Has thanked: 0 time
Been thanked: 1 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post