Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Update on temporary table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vinay_care

New User


Joined: 22 Sep 2006
Posts: 17

PostPosted: Tue Aug 12, 2008 12:41 pm    Post subject: Update on temporary table
Reply with quote

Hi,

I need a quick suggestion/resolution, please guide.
I have declare a temporary table 'on commit preserve rows' and inserting a set of records. this records will be read and updated with new values in the same program. now the updated values are read/selected for further application. but the values updated are not reflected, only the values inserted initally are reflected.

I'm commiting the rows after every update but still the values doesn't reflect.
please suggest is their a way to commit the updated values in the temporary table. And does update works on temporary table?


Cheers,

Vinu
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Aug 12, 2008 1:07 pm    Post subject:
Reply with quote

As per the below article, it seems UPDATE doesn't work on Temporary tables. Instead, you can update the row in your program and then insert into the temp table.

http://www.craigsmullins.com/dbu_0802.htm
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Aug 12, 2008 2:58 pm    Post subject:
Reply with quote

Vinay,

According to the article Srihari references, UPDATE does work for a DECLARED temp table as opposed to a CREATED temp table:

Quote:

Temporary Tables: Declared and Created
by Craig S. Mullins

Created Temporary Tables ...

· Created temporary tables can not be specified as the object of an UPDATE statement.

Declared Temporary Tables ....

· You can issue UPDATE statements and positioned DELETE statements against a declared temporary table.




Rick
Back to top
View user's profile Send private message
vinay_care

New User


Joined: 22 Sep 2006
Posts: 17

PostPosted: Tue Aug 12, 2008 3:45 pm    Post subject:
Reply with quote

I have declared the temporary table, only then inserting the records initially and then trying to update the values of those records, but this is not happening.
update is not supported ;-(
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Aug 12, 2008 4:05 pm    Post subject:
Reply with quote

Yes Vinay. Looking at the following link, UPDATE is not supported on both declared and created temporary tables.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.intro/db2z_tabletypedistinctions.htm
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Aug 12, 2008 5:05 pm    Post subject:
Reply with quote

You folks really need to read thru the info thoroughly ...

Srihari: In the link you provided, look under the column heading Declared temporary tables:

Quote:

Indexes and other SQL statement support Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported.


Rick
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Aug 12, 2008 5:20 pm    Post subject:
Reply with quote

Rick, You are right. Excuse me for the oversight..:-)
Back to top
View user's profile Send private message
vinay_care

New User


Joined: 22 Sep 2006
Posts: 17

PostPosted: Tue Aug 12, 2008 5:44 pm    Post subject:
Reply with quote

Rick can you suggest what might be the possible flaws or thing I might missed out or need to take care?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 12, 2008 9:21 pm    Post subject:
Reply with quote

Are you sure your update is successful?
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Aug 12, 2008 10:04 pm    Post subject:
Reply with quote

Hi Vinay,

Please "Code" and paste any compile/runtime messages you are receiving, along with your SQL and any relevant code. What version of DB2 are you using? Srihari has provided you with a couple of excellent references.

Rick
Back to top
View user's profile Send private message
vinay_care

New User


Joined: 22 Sep 2006
Posts: 17

PostPosted: Mon Aug 18, 2008 11:21 am    Post subject:
Reply with quote

Thanks guys, and sorry for delayed response.

Has such I'm creating the temporary table in my application, the update is not supporting, even though sqlcode shows zero after the query is executed, the values does not get updated.
I did change the logic of array usage and only 'insert' on temporary table. this resolved my issue.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts File Tailoring -- Temporary dataset i... samzee71 CLIST & REXX 3 Fri Dec 01, 2017 3:12 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us