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]

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(Platform, DB2 version/release, so on)

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

[LouiseR]

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(Platform, DB2 version/release, so on)

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

[Mark A]

"LouiseR" <u43744@uwewrote in message news:847f1893eac99@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]

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....m-db2/200805/1