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

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

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

Can you please post you entire DECLARE statement. Also you DB2 version and OS.

Andy

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

What do you do to the temp table between when you declare it and you try to update?

Andy

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.

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

We are getting -151 on the update stmt.

Please post the full error.

Andy

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

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

FYI, There is no 'WITH REPLACE' on zOS

Cheers
sathyaram