Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

LOAD TIMESTAMP EXTERNAL: CONVERSION ERROR 14 - INVALID DATA

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Wed Jul 02, 2014 3:36 pm    Post subject: LOAD TIMESTAMP EXTERNAL: CONVERSION ERROR 14 - INVALID DATA
Reply with quote

Hi everybody

I'd like to load a ps UTF8-dataset (VB-27994) in a DB2 zOS unicode-table.
One of the target columns is timestamp.
But the load utility discards the row saying "CONVERSION ERROR FOR RESMFD.MF30T701 ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'"

The timestamp-value in the dataset is in valid external format "YYYY-MM-DD-hh.mm.ss" without any microseconds:
Code:
7----+----8----+----9----+----0
F----+----F----+----F----+----F
7----+----8----+----9----+----0
-------------------------------
0"#"2014-06-30-10.19.50"#"PRZEC
3222333323323323323323322255544
02322014D06D30D10E19E5023202A53
-------------------------------


And the load statement is coded like following
Code:
   LOAD DATA                                                         
        INDDN           SYSREC00                                     
        RESUME          YES                                         
        FORMAT          DELIMITED COLDEL  X'23'                     
                                  CHARDEL X'22'                     
                                  DECPT   X'2E'                     
        CCSID           (367, 1208, 1200)                           
        ENFORCE         NO                                           
        DISCARDDN       SYSDSC00                                     
        INTO TABLE      RESMFD.MF30T701                             
                     (  IPSD_FIRST_COL        POSITION ( 1)         
                                              CHAR       TRUNCATE   
                      , IPSD_TS_COL           POSITION ( *)         
                                              TIMESTAMP EXTERNAL (19)
                      , IPSD_LAST_COL         POSITION ( *)         
                                              CHAR MIXED TRUNCATE   
                     )


Has anybody got an idea about my mistake or about solving the problem?

Thank you for your hints
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Jul 02, 2014 3:51 pm    Post subject:
Reply with quote

Hi There,

Please try removing 19 after timestamp external and see if it works.
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Wed Jul 02, 2014 5:03 pm    Post subject:
Reply with quote

guptae wrote:
Hi There,

Please try removing 19 after timestamp external and see if it works.


No, did not work - TIMESTAMP EXTERNAL (length 26) is default.

Tried some further rows without timestamp data, one with empty string and one without:
Code:
       7----+----8----+----9----+----0---
       F----+----F----+----F----+----F---
       7----+----8----+----9----+----0---
------ ----------------------------------
000001 0"#"2014-06-30-10.19.50"#"PRZECIWW
       3222333323323323323323322255544455
       02322014D06D30D10E19E5023202A53977
------ ----------------------------------
000002 0"#""#"PRZECIWWSKAZANA PRACA OBCI.
       322222255544455544544425544424444C
       023223202A539773B1A1E10021310F2394
------ ----------------------------------
000003 0"##"PRZECIWWSKAZANA PRACA OBCI..A
       3222255544455544544425544424444C8C
       0233202A539773B1A1E10021310F239445
------ ----------------------------------
000004 0"#"2014-06-30-10.19.50.123456"#"P
       3222333323323323323323323333332225
       02322014D06D30D10E19E50E1234562320
------ ----------------------------------


Row 2 (empty string) produced the same behavior but row 3 works - with a null value in IPSD_TS_COL

Row 4 contains a timestamp with microseconds but - with length (26) - doesn't work too. Same behavior as row 1 and 2.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jul 02, 2014 8:04 pm    Post subject:
Reply with quote

makes sense. TS-EXTERNAL is not char, so it doesn't have CHARDEL.
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Wed Jul 02, 2014 8:28 pm    Post subject:
Reply with quote

GuyC wrote:
makes sense. TS-EXTERNAL is not char, so it doesn't have CHARDEL.


Unfortunately the hint brought no success:
Code:
       7----+----8----+----9----+----0-
       F----+----F----+----F----+----F-
       7----+----8----+----9----+----0-
------ --------------------------------
000001 0"#2014-06-30-10.19.50#"PRZECIWW
       32233332332332332332332255544455
       0232014D06D30D10E19E503202A53977
------ --------------------------------
000002 0"##"PRZECIWWSKAZANA PRACA OBCI.
       3222255544455544544425544424444C
       0233202A539773B1A1E10021310F2394
------ --------------------------------
000003 0"#2014-06-30-10.19.50.000000#"P
       32233332332332332332332333333225
       0232014D06D30D10E19E50E000000320
------ --------------------------------


Same behavior:
RECORD (1/2) WILL BE DISCARDED DUE TO 'IPSD_TS_COL'
CONVERSION ERROR FOR RESMFD.MF30T701
ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jul 02, 2014 8:57 pm    Post subject:
Reply with quote

Never tried but I found some documentation, see if this helps.

http://www-01.ibm.com/support/docview.wss?uid=swg27037975
http://ibmmainframes.com/about57294.html
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Fri Jul 04, 2014 8:03 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Never tried but I found some documentation, see if this helps.

http://www-01.ibm.com/support/docview.wss?uid=swg27037975
http://ibmmainframes.com/about57294.html


Hi Rohit,

thanx for the hint.
Tried so but did not work: AbEnd with »INVALID KEYWORD - TIMESTAMP_A«. Seems we don't use Utilities Enhancement Tool Update 6 yet :-(
Asked a DB2 zOS admin collegue yesterday. Let's wait and see what she can find out...
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Tue Jul 08, 2014 6:51 pm    Post subject: It's not a feature, it's a DEFECT!!!
Reply with quote

After using clause UNICODE instead of CCSID (367, 1208, 1200) the load utility produces an abend 0E4 reason '00E70005'.

The behavior is reported to Big Blue who could reproduce it.
It's accepted as a defect and now under examination.

The work around the colleague found out is to use all columns without character delimiter.
An other way is to import the ts string into a char column and in pre-processing to interpret the string using cast functions.
It depends on the use case which work around is more practical.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Jul 08, 2014 8:03 pm    Post subject:
Reply with quote

Quote:
An other way is to import the ts string into a char column and in pre-processing to interpret the string using cast functions.


This is what we do usually to cast, but yeah this would be a work around ad glad you could able to raise a defect and get things moved further.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us