Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2006
    Posts
    6

    Unanswered: Updating Temporary tables

    Hi

    I am trying to update a column from a temporary table in db2 v8. I am getting -151 error.

    I have defined the temporary table as
    DECLARE GLOBAL TEMPORARY TABLE SESSION.abcd(col1,col2,col3)

    my update stmt is
    update session.abcd set col2 = (select ah.col2 from xyz ah
    where ah.col1 = 10)

    Is it possible to update like above.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes. I think your problem is how you created your temp table.
    Try something like this:

    DECLARE GLOBAL TEMPORARY TABLE abcd(col1 int,col2 int,col3 int) on commit preserve rows not logged;

    HTH

    Andy

  3. #3
    Join Date
    Mar 2006
    Posts
    6
    Hi,

    I have tried putting DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

    I got compilation error saying that --
    ";" REQUIRED
    INVALID KEYWORD "WITH";

    Can you help me identify the problem.

    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you please post you entire DECLARE statement. Also you DB2 version and OS.

    Andy

  5. #5
    Join Date
    Mar 2006
    Posts
    6

    declare stmt

    It is DB2 V8 on Zos

    DECLARE GLOBAL TEMPORARY TABLE SESSION.ACCESS_ROWS AS (
    SELECT AH.ACCESS_TIMESTAMP AS ACCESS_TIME,
    AH.CUSTOMER_NO AS CUST_NO
    FROM HISTORY AH)
    DEFINITION ONLY ON COMMIT PRESERVE ROWS;


    I have tried the above statement - it compiled and we got --151 on the update statement.

    Update statement is as follows:

    update access_rows set cust_no = (select customer_no from history ah where ah.abcd = 10)

    Thanks

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you do to the temp table between when you declare it and you try to update?

    Andy

  7. #7
    Join Date
    Mar 2006
    Posts
    6

    insert into temp table

    Between declaring the temp table and updating it, I am inserting into the temp table as follow

    insert into session.access_rows select abcd, xyz from customer where xyz = 10;

    Then we are updating the temp table using the update statement.

    Thanks.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What happens if you declare the table like this:

    DECLARE GLOBAL TEMPORARY TABLE SESSION.ACCESS_ROWS (ACCESS_TIME timestamp, CUST_NO int)
    ON COMMIT PRESERVE ROWS not logged with replace;

    Andy

  9. #9
    Join Date
    Mar 2006
    Posts
    6

    -151 on update stmt

    We are getting -151 on the update stmt.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please post the full error.

    Andy

  11. #11
    Join Date
    Mar 2006
    Posts
    6

    sqlcode going into an error table

    Hi,

    We are inserting the sqlcode into an error table. We are checking the error table after executing the stored procedure.

    In the sqlcode field -151 is there with comments in another field. This is how, I am saying that the update stmt is failing.

    Compilation and bind process are going through successfully.

    Execution of the stored procedure is returning the result cursor in the update statement error log area.

    Thanks

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not at all familiar with ZOS, so my help may not be of any use. What happens when you try the commands in the CLP?

    Andy

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    FYI, There is no 'WITH REPLACE' on zOS

    Cheers
    sathyaram

    Quote Originally Posted by PRBR
    Hi,

    I have tried putting DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

    I got compilation error saying that --
    ";" REQUIRED
    INVALID KEYWORD "WITH";

    Can you help me identify the problem.

    Thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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