Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    7

    Unanswered: DB2 Timestamp question?

    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?

  2. #2
    Join Date
    Oct 2003
    Location
    Curitiba - PR - Brazil
    Posts
    18
    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?
    Norberto Gasparotto Filho

    IBM Cert. DB2 v9 DBA
    IBM Cert. DB2 v9 App. Developer
    SCJP 5

  3. #3
    Join Date
    May 2009
    Posts
    7
    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";

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    May 2009
    Posts
    7
    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.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Dec 2008
    Posts
    76
    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.

  8. #8
    Join Date
    May 2003
    Posts
    113
    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.

  9. #9
    Join Date
    May 2009
    Posts
    7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •