var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Updating Temporary tables
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.
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;
I have tried putting DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
I got compilation error saying that --
INVALID KEYWORD "WITH";
Can you help me identify the problem.
Can you please post you entire DECLARE statement. Also you DB2 version and OS.
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)
What do you do to the temp table between when you declare it and you try to update?
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.
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;
-151 on update stmt
We are getting -151 on the update stmt.
Please post the full error.
sqlcode going into an error table
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.
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?
FYI, There is no 'WITH REPLACE' on zOS
Originally Posted by PRBR
Visit the new-look IDUG Website
, register to gain access to the excellent content.