ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

Rakesh Adhikarapu

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

Dear Team, After many trials I am finally upset with the issue. One applicaion team mate gave me a datafile that he says it was taken from Prod table and asked me to load into Dev table. I got stuck with the above error after many tries.

My results :

I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard - Data Loaded successfully.

Using same Punchcard, Loading with his datafile - Getting above error

I also tried by using Unicode/Ebcidic CCSID's, and they leading to another error.

I believe, the field structure with its position in his data file must be different from DB2 table positions.

Please let me know if you have any quick solution to this issue.

Thanks,

Rakeysh

Venkat Srinivasan

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Rakesh Adhikarapu)

If the field positions differ, did you ask him how he unloaded. The fix will be to correct the load control card in line with the sysrec.

Venkat
 
In Reply to Rakesh Adhikarapu:

Dear Team, After many trials I am finally upset with the issue. One applicaion team mate gave me a datafile that he says it was taken from Prod table and asked me to load into Dev table. I got stuck with the above error after many tries.

My results :

I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard - Data Loaded successfully.

Using same Punchcard, Loading with his datafile - Getting above error

I also tried by using Unicode/Ebcidic CCSID's, and they leading to another error.

I believe, the field structure with its position in his data file must be different from DB2 table positions.

Please let me know if you have any quick solution to this issue.

Thanks,

Rakeysh

Horacio Villa

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Rakesh Adhikarapu)
Ask him for his Punchcard.

Horacio



From: Rakesh Adhikarapu <[login to unmask email]>
To: [login to unmask email]
Date: 04/22/2016 04:34 PM
Subject: [DB2-L] - ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'



Dear Team, After many trials I am finally upset with the issue. One
applicaion team mate gave me a datafile that he says it was taken from
Prod table and asked me to load into Dev table. I got stuck with the above
error after many tries.
My results :
I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard -
Data Loaded successfully.
Using same Punchcard, Loading with his datafile - Getting above error
I also tried by using Unicode/Ebcidic CCSID's, and they leading to another
error.
I believe, the field structure with its position in his data file must be
different from DB2 table positions.
Please let me know if you have any quick solution to this issue.
Thanks,
Rakeysh


Site Links: View post online View mailing list online Start new thread
via email Unsubscribe from this mailing list Manage your subscription
** ** ** Attend the 2016 IDUG North America DB2 Tech Conference ** ** **
---> Austin, Texas, USA May 23-26, 2016 <---
http://www.idug.org/p/cm/ld/fid=791

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Carol Anne Sutfin

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Horacio Villa)
I would ask to see the JCL for his "UNLOAD" job and the SYSOUT.

You will be frightened at what some people think is "UNLOAD".


From: Horacio Villa [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 3:40 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

Ask him for his Punchcard.

Horacio



From: Rakesh Adhikarapu <[login to unmask email]<mailto:[login to unmask email]>>
To: [login to unmask email]<mailto:[login to unmask email]>
Date: 04/22/2016 04:34 PM
Subject: [DB2-L] - ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
________________________________



Dear Team, After many trials I am finally upset with the issue. One applicaion team mate gave me a datafile that he says it was taken from Prod table and asked me to load into Dev table. I got stuck with the above error after many tries.

My results :

I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard - Data Loaded successfully.

Using same Punchcard, Loading with his datafile - Getting above error

I also tried by using Unicode/Ebcidic CCSID's, and they leading to another error.

I believe, the field structure with its position in his data file must be different from DB2 table positions.

Please let me know if you have any quick solution to this issue.

Thanks,

Rakeysh



-----End Original Message-----

Chris Hoelscher

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Carol Anne Sutfin)
Are prod and test at the same release - R11 does not accept all date/time formats that R10 and earlier did )or R11 flags bad date/times that earlier releases did not)

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: cid:[login to unmask email]: humana.com
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 714-8615, (502) 476-2538

From: Carol A. Sutfin [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 5:09 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

I would ask to see the JCL for his "UNLOAD" job and the SYSOUT.

You will be frightened at what some people think is "UNLOAD".


From: Horacio Villa [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 3:40 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

Ask him for his Punchcard.

Horacio



From: Rakesh Adhikarapu <[login to unmask email]<mailto:[login to unmask email]>>
To: [login to unmask email]<mailto:[login to unmask email]>
Date: 04/22/2016 04:34 PM
Subject: [DB2-L] - ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
________________________________



Dear Team, After many trials I am finally upset with the issue. One applicaion team mate gave me a datafile that he says it was taken from Prod table and asked me to load into Dev table. I got stuck with the above error after many tries.

My results :

I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard - Data Loaded successfully.

Using same Punchcard, Loading with his datafile - Getting above error

I also tried by using Unicode/Ebcidic CCSID's, and they leading to another error.

I believe, the field structure with its position in his data file must be different from DB2 table positions.

Please let me know if you have any quick solution to this issue.

Thanks,

Rakeysh



-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.
Attachments

  • image001.jpg (<1k)

Charles Brown

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Carol Anne Sutfin)
Re: I believe, the field structure with its position in his data file must



Rakeysh,



Logon to PROD and get the DDL for the table in question

Using the DDL from above logon to DEV and perform DROP and CREATE on that
table

Now, using the CNTL file and datafile from prod submit your LOAD job.

And watch it run clean as a whistle.

//* ==========

If you're not permitted to do DROP AND CREATE in DEV

Then using this JCL( see below) generate CNTL file for table in DEV

Compare CNTL files and make adjustments to DEV CNTL file. Don't touch PROD
CNTL file



//STEP01 EXEC DB2PROC,PGM=DSNTIAUL

//SYSREC00 DD DUMMY,DCB=BLKSIZE=80

//SYSPUNCH DD DSN=DB2DEV.CUSMSTR.CNTL,

// UNIT=SYSDA,SPACE=(800,(2,2)),DISP=(,CATLG)


//SYSIN DD *

DB2DEV.CUSTOMER_MASTER_XXX


//*


Hope this helps



Chas/b

NZ DBA



From: Carol A. Sutfin [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 4:09 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'



I would ask to see the JCL for his "UNLOAD" job and the SYSOUT.



You will be frightened at what some people think is "UNLOAD".





From: Horacio Villa [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 3:40 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'



Ask him for his Punchcard.

Horacio



From: Rakesh Adhikarapu <[login to unmask email]
<mailto:[login to unmask email]> >
To: [login to unmask email] <mailto:[login to unmask email]>
Date: 04/22/2016 04:34 PM
Subject: [DB2-L] - ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

_____




Dear Team, After many trials I am finally upset with the issue. One
applicaion team mate gave me a datafile that he says it was taken from Prod
table and asked me to load into Dev table. I got stuck with the above error
after many tries.

My results :

I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard -
Data Loaded successfully.

Using same Punchcard, Loading with his datafile - Getting above error

I also tried by using Unicode/Ebcidic CCSID's, and they leading to another
error.

I believe, the field structure with its position in his data file must be
different from DB2 table positions.

Please let me know if you have any quick solution to this issue.

Thanks,

Rakeysh



-----End Original Message-----





-----End Original Message-----



-----End Original Message-----

Rakesh Adhikarapu

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Rakesh Adhikarapu)

Dear Everyone, thanks for your acknowledgement. I finally got to sortout the issue.

App team got the data file from a retail store and the columns in the file are having scrambled structure as that are present in Prod/Dev, also few columns are not present in the loadcard too. So when I used a regular punchcard its discarding and I got the Loadcard from application person and after using it, the data loaded into table.

So, in any case if we get an issue like this (having datafile in one format and our table' punchcard is different), we need a correct loadcard (what other ways a DBA can do otherthan this) ?

For eg.. A Date datatype column in load card generated by me has below structure(Load failed)

POR_DUE_DT POSITION(00127:00136) DATE EXTERNAL NULLIF(00126)=X'FF'

And the Loadcard given to me have below structure for the same column (Loaded successfully)

POR_DUE_DT POSITION(00233:00242) DATE EXTERNAL NULLIF(POR_DUE_DT='0001-01-01')

@Venkat, Horacio, Carol  - I took the Loadstructure that points to his datafile. They said it took from a frontend store.

@ Chris - Dev/Accp in V11 NFM and Prod in V11 ENFM mode.

@ Charles - The description in Dev/Prod are same. Infact I created another sample table in Dev and tried the same in a trial and error basis and the results are in error. It was corrected only after taking his Loadcard.

Rakeysh

Venkat Srinivasan

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Rakesh Adhikarapu)

The only workable way is to standardize the file format upfront. The program that writes the load file will ned to write in DCLGEN format with due adjustments for null indicators. Use DSNTIAUL / unload utility to generate the syspunch data structure and ask the programmer to write data conforming to that format.

If the file format is scrambled as you indicate, you will have to manually alter the load control card or reformat the file to match the standard syspunch. You can use SORT products to reformat data. 

Venkat


In Reply to Rakesh Adhikarapu:

Dear Everyone, thanks for your acknowledgement. I finally got to sortout the issue.

App team got the data file from a retail store and the columns in the file are having scrambled structure as that are present in Prod/Dev, also few columns are not present in the loadcard too. So when I used a regular punchcard its discarding and I got the Loadcard from application person and after using it, the data loaded into table.

So, in any case if we get an issue like this (having datafile in one format and our table' punchcard is different), we need a correct loadcard (what other ways a DBA can do otherthan this) ?

For eg.. A Date datatype column in load card generated by me has below structure(Load failed)

POR_DUE_DT POSITION(00127:00136) DATE EXTERNAL NULLIF(00126)=X'FF'

And the Loadcard given to me have below structure for the same column (Loaded successfully)

POR_DUE_DT POSITION(00233:00242) DATE EXTERNAL NULLIF(POR_DUE_DT='0001-01-01')

@Venkat, Horacio, Carol  - I took the Loadstructure that points to his datafile. They said it took from a frontend store.

@ Chris - Dev/Accp in V11 NFM and Prod in V11 ENFM mode.

@ Charles - The description in Dev/Prod are same. Infact I created another sample table in Dev and tried the same in a trial and error basis and the results are in error. It was corrected only after taking his Loadcard.

Rakeysh

Peter Vanroose

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Venkat Srinivasan)

Agreed.

It's a general guideline, not just with DB2, not just with z/OS:
"when exporting data, also export the meta-data",
that is, a description of the format details of the exported data.

In Reply to Venkat Srinivasan:

The only workable way is to standardize the file format upfront. The program that writes the load file will need to write in DCLGEN format with due adjustments for null indicators. Use DSNTIAUL / unload utility to generate the syspunch data structure and ask the programmer to write data conforming to that format. [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/html/enindex.html

Charles Brown

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Peter Vanroose)
@Venkat, @Peter -- problem is fixed. See Sat 4/23/2016 posting on this thread



From: Peter Vanroose [mailto:[login to unmask email]
Sent: Sunday, April 24, 2016 7:03 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'



Agreed.

It's a general guideline, not just with DB2, not just with z/OS:
"when exporting data, also export the meta-data",
that is, a description of the format details of the exported data.

In Reply to Venkat Srinivasan:

The only workable way is to standardize the file format upfront. The program that writes the load file will need to write in DCLGEN format with due adjustments for null indicators. Use DSNTIAUL / unload utility to generate the syspunch data structure and ask the programmer to write data conforming to that format. [...]

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/html/enindex.html



-----End Original Message-----

Peter Vanroose

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Charles Brown)

I know ;-)

Still useful to learn how other people work with data, and learn from each other.

--      Peter.



In Reply to Charles Brown:

@Venkat, @Peter -- problem is fixed. See Sat 4/23/2016 posting on this thread

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/html/enindex.html

bernd oppolzer

ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Peter Vanroose)
I like import/export tools very much, which support data exchange formats,
where the meta data and the actual data is included in the same physical
file,
so that there is no chance to run into such errors (mismatch of meta
data and
actual data).

For example some other database's export tools do it this way.

Many years ago I wrote an import/export tool of my own, which works much
the
same way as DSNTIAUL, but both directions (UNLOAD and LOAD), and it supports
different exchange formats, for example standard formats like CSV, but
there is also
one format, where the meta data (column names, data types including
length and scale,
null flags, and positions) and the actual data is included in the same
physical file.
And: the file format is text only (no binary content) and limited to
line length 80,
so that it can reside in PDS members on the mainframe, but it can be
easily transferred
across platform boundaries. Because there also exists an Oracle version
of the tool
(and DB2 versions on all platforms), data exchange between different
flavors of DB2
and Oracle are very easy task, using this tool suite.

If you are interested, contact me offline.

Kind regards

Bernd



Am 24.04.2016 um 14:03 schrieb Peter Vanroose:
>
> Agreed.
>
> It's a general guideline, not just with DB2, not just with z/OS:
> "/*when exporting data, also export the meta-data*/",
> that is, a description of the format details of the exported data.
>
> In Reply to Venkat Srinivasan:
>
> The only workable way is to standardize the file format upfront.
> The program that writes the load file will need to write in DCLGEN
> format with due adjustments for null indicators. Use DSNTIAUL /
> unload utility to generate the syspunch data structure and ask the
> programmer to write data conforming to that format. [...]
>
> -- Peter Vanroose
> /ABIS Training & Consulting,/
> / Leuven, Belgium./
> http://www.abis.be/html/enindex.html
>
>

Denise Gantz

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Chris Hoelscher)

Chris, can you please elaborate on the differences between R10 and R11?  I have a user who is executing an STP.  One of the input fields is a date parameter.  Prior to installing R11 CM, he used to be able to pass in a blank parameter for the date and get a return of all the records in the table.  Now he is getting -180 INVALID DATE/TIME  response.  Would this blank parameter be one of the differences between the versions?

I have done some searching but haven't been able to find any information on this, other than this thread.

Thank you
 
In Reply to Chris Hoelscher:

Are prod and test at the same release - R11 does not accept all date/time formats that R10 and earlier did )or R11 flags bad date/times that earlier releases did not)

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: cid:[login to unmask email]: humana.com
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 714-8615, (502) 476-2538

From: Carol A. Sutfin [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 5:09 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

I would ask to see the JCL for his "UNLOAD" job and the SYSOUT.

You will be frightened at what some people think is "UNLOAD".


From: Horacio Villa [mailto:[login to unmask email]
Sent: Friday, April 22, 2016 3:40 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'

Ask him for his Punchcard.

Horacio



From: Rakesh Adhikarapu <[login to unmask email]<mailto:[login to unmask email]>>
To: [login to unmask email]<mailto:[login to unmask email]>
Date: 04/22/2016 04:34 PM
Subject: [DB2-L] - ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
________________________________



Dear Team, After many trials I am finally upset with the issue. One applicaion team mate gave me a datafile that he says it was taken from Prod table and asked me to load into Dev table. I got stuck with the above error after many tries.

My results :

I Unloaded Prod/Dev table Sysrec(My Test file), used with its Punchcard - Data Loaded successfully.

Using same Punchcard, Loading with his datafile - Getting above error

I also tried by using Unicode/Ebcidic CCSID's, and they leading to another error.

I believe, the field structure with its position in his data file must be different from DB2 table positions.

Please let me know if you have any quick solution to this issue.

Thanks,

Rakeysh



-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Alan Gredell

RE: ERROR CODE '14 - INPUT IS INVALID FOR DATE/TIME'
(in response to Denise Gantz)

Hi, Rakeysh!  Great to hear from you!

I think you should have him perform another unload using whatever method he used (should be identical, but can be a DUMMY SYSREC if he doens't require fresher data), and KEEP that SYSPUNCH and use IT instead of yours.  I know that using a common SYSPUNCH should really work most of the time, but, short of looking in the file field-by-field, I don't know of any way to debug the type of issue you are experiencing.  I am trying very hard to convince the application staff over here to ALWAYS keep the SYSREC and the SYSPUNCH together!  Hope this helps!