Multi-row insert timestamp error

[LouiseR]

I just recently tried to use a multi-row insert on a table that contains a
timestamp column, but I get SQLCODE -180 even though I'm positive that the
value that I'm trying to insert is correct. The error message is : " THE DATE,
TIME, OR TIMESTAMP VALUE *N IS INVALID". Any ideas?

[Tonkuma]

Re: Multi-row insert timestamp error

1) How did you make sure "the value that I'm trying to insert is
correct" ?

2) It would be better to show more practical concrete explanations.
For example:
- SQL statement to use your multi-row insert
- DDLs
- Whole(or comlete) error message
- Environments(Pl atform, DB2 version/release, so on)

3) I feel strange that message include "... *N ...".
What is "*N" ?

[LouiseR]

Re: Multi-row insert timestamp error

1) To do a multi-row insert, I declared an array with 10 occurrences in the
working storage of my Cobol program, and displayed the contents of the fields
in the array prior to doing the multi-row insert, so that's how I know the
timestamp is valid.
2) It would take too much room to show the actual SQL statement because I'm
working with very large tables with many columns. I use the mainframe
platform and DB2 version 8. The error message that I received is complete as
shown in my question.
3) the '*N' is a standard error message within DB2, so it is not really the
contents of the column, but a default error message that DB2 displays. It
doesn't mean much to me either which is why I hoped that I could get more
meaningful information from the GET DIAGNOSTICS statement.


Tonkuma wrote:

>1) How did you make sure "the value that I'm trying to insert is
>correct" ?
>
>2) It would be better to show more practical concrete explanations.
>For example:
>- SQL statement to use your multi-row insert
>- DDLs
>- Whole(or comlete) error message
>- Environments(Pl atform, DB2 version/release, so on)
>
>3) I feel strange that message include "... *N ...".
>What is "*N" ?

[Mark A]

Re: Multi-row insert timestamp error

"LouiseR" <u43744@uwewrot e in message news:847f1893ea c99@uwe...

1) To do a multi-row insert, I declared an array with 10 occurrences in
the
working storage of my Cobol program, and displayed the contents of the
fields
in the array prior to doing the multi-row insert, so that's how I know the
timestamp is valid.

Just to humor us, why don't you post the contents of your timestamp, and the
data type it is stored in, when you are inserting.

[LouiseR via DBMonster.com]

Re: Multi-row insert timestamp error

Mark A wrote:

>1) To do a multi-row insert, I declared an array with 10 occurrences in
>the
>working storage of my Cobol program, and displayed the contents of the
>fields
>in the array prior to doing the multi-row insert, so that's how I know the
>timestamp is valid.

>
>Just to humor us, why don't you post the contents of your timestamp, and the
>data type it is stored in, when you are inserting.

I'll be happy to oblige. Below is the data area where I saved the timestamp
value, and which I used in the INSERT statement.
10 CQCMRACM-UPD-TMS PIC X(26) OCCURS 10.
And below is the result of the DISPLAY statement of this field just before
the INSERT:
CQCMRACM-UPD-TMS(1)=2008-05-20-15.25.13.792038
Here's the funny thing though. I asked a colleague's opinion about my issue,
and he made one small change in my code which resolved the issue. As you can
see above, I declared an array with 10 occurrences in Working Storage,
populated only 3 of those occurrences, and then did the INSERT specifying
"FOR 10 ROWS". The INSERT failed on the first row (according to GET
DIAGNOSTICS) with a -180 SQLCODE. My colleague suggested changing the "FOR 10
ROWS" to "FOR :WS-SUB ROWS", where WS-SUB contains the number 3 since that is
how many rows were populated in the working storage array. It worked! Thanks
for your interest!

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1