|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi There,
Please try removing 19 after timestamp external and see if it works. |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| makes sense. TS-EXTERNAL is not char, so it doesn't have CHARDEL. |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
|
|
