DB2 Timestamp question?

sgogula · #1 (**permalink**) 05-19-09, 12:00

I am moving data from one table to another table using the below query:

insert into tableA_Archive(Id, request_time) select id, request_time from tableA.

I get the below error when i run the above query:

com.ibm.db2.jcc.a.in: Value "2009-05-19-10.15.14.918826" is too long.. SQLCODE=-433, SQLSTATE=22001, DRIVER=3.52.90

On both the tables request_time is timestamp. How do i insert timestamp from one table to another table?

norbertogf · #2 (**permalink**) 05-19-09, 14:19

Are you really sure your columns have the same data type?
If you didn't do it yet, please issue a describe table for each table.

Are those tables in the same physical database, or are they in federated databases?

sgogula · #3 (**permalink**) 05-21-09, 10:51

Quote:

Originally Posted by norbertogf

Are you really sure your columns have the same data type?
If you didn't do it yet, please issue a describe table for each table.

Are those tables in the same physical database, or are they in federated databases?

they are within the same physical database. columns are same as i created the second table by importing the first database columns.

this is the table structure, exactly same in both tables

Code:

 CREATE TABLE "ADMUSR"."BUILD_REQUEST_TEMP" (
		"BUILD_ID" INTEGER NOT NULL,
		"APPLICATION_ID" VARCHAR(10) NOT NULL,
		"REQUEST_TIME" TIMESTAMP NOT NULL,
		"BUILD_END_TIME" TIMESTAMP,
		"BUILD_REQUEST_TIME" TIMESTAMP,
		"BUILD_START_TIME" TIMESTAMP,
		"BUILD_STATUS" VARCHAR(10),
		"DEPLOY_START_TIME" TIMESTAMP,
		"DEPLOY_STATUS" VARCHAR(10),
	)
	DATA CAPTURE NONE 
	IN "USERSPACE1";

rahul_s80 · #4 (**permalink**) 05-21-09, 11:03

Strange ! which tool you are using that is giving this error ?
Also can you post the DDL of the other table too and the exact Ins stmy you are firing

sgogula · #5 (**permalink**) 05-21-09, 11:31

Quote:

Originally Posted by rahul_s80

Strange ! which tool you are using that is giving this error ?
Also can you post the DDL of the other table too and the exact Ins stmy you are firing

I am using IBM Data Studio Developer 1.2.

It is exactly the same as i generated again with the different table name.

Stealth_DBA · #6 (**permalink**) 05-21-09, 12:27

sgogula, I noticed that the first 3 columns are defined as NOT NULL but your Insert statement is only supplying 2 columns.

Is one of the 2 columns an Identity column (or other type that DB2 will generate)? If not, the Insert needs 3 columns (minimum) to insert into the table.

rdutton · #7 (**permalink**) 05-21-09, 12:27

I don't use Data Studio, but I believe it is written in Java. Java only handles the first 3 digits of the microseconds. Maybe that is what it is "too long". Just a guess.

nidm · #8 (**permalink**) 05-21-09, 12:54

Quote:

Originally Posted by rdutton

I don't use Data Studio, but I believe it is written in Java. Java only handles the first 3 digits of the microseconds. Maybe that is what it is "too long". Just a guess.

first, I don't think there is anything related with Java or any application. Because the INSERT (with subselect) stmt is totally operated inside DB2.

2nd, someone mention the NULL definition maybe a clue here.

sgogula · #9 (**permalink**) 05-21-09, 12:56

Thanks for all your help. It worked now, i do not know what was the mistake i was making..

this is the working query

Code:

 

INSERT INTO BUILD_REQUEST_ARCHIVE (BUILD_ID, APPLICATION_ID, REQUEST_TIME,
  BUILD_END_TIME, BUILD_REQUEST_TIME, BUILD_START_TIME, BUILD_STATUS,
  DEPLOY_START_TIME, DEPLOY_STATUS)
  SELECT BUILD_ID, APPLICATION_ID, REQUEST_TIME, BUILD_END_TIME,
  BUILD_REQUEST_TIME, BUILD_START_TIME, BUILD_STATUS, DEPLOY_START_TIME,
  DEPLOY_STATUS
    FROM BUILD_REQUEST_TEMP

Internet Services

Web Development

Digital Marketing

Consumer Tech