473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Deleting a Global Temporary table datas

Hi ... Please help as soon as possible...


We have a Global temporary table where we will insert the datas and return the result set to front end by opening the cursor in the stored procedure... We have deletion of GTT at the start of the program.... but we are getting -913 error.. So we thought of commenting it out. If we comment that deletion , then the GTT returns the old datas along with the newly inserted rows.. But as a GTT the datas inside it must be deleted once the session is over(hope am right).. But the datas reside and we are getting that during the next call.. Can anybody give the reason why it happens and hw can we get out of it?
Feb 13 '08 #1
2 4437
sakumar9
127 Expert 100+
Hi ... Please help as soon as possible...


We have a Global temporary table where we will insert the datas and return the result set to front end by opening the cursor in the stored procedure... We have deletion of GTT at the start of the program.... but we are getting -913 error.. So we thought of commenting it out. If we comment that deletion , then the GTT returns the old datas along with the newly inserted rows.. But as a GTT the datas inside it must be deleted once the session is over(hope am right).. But the datas reside and we are getting that during the next call.. Can anybody give the reason why it happens and hw can we get out of it?

In your case, since you are returning the resultsets, the rows will be preserved. I'll try to find the reason why you are getting -913 error.

One alternative is you can delete the contents from your GTT instead of dropping it. Incase you do not want any logging of DELETE, you can use TRUNCATE stored procedure. (You can find this stored procedure in SQLLIB/samples/admin_scripts/truncate.db2)


Regards
-- Sanjay
Feb 13 '08 #2
docdiesel
297 Expert 100+
Hi,

if the data remains in the GTT it means the session somehow doesn't terminate.

However, if you need a workaround to delete some rows from that GTT, you'd better define it with the parameter NOT LOGGED. This speeds up things and avoids "transaction logs full" error:
Expand|Select|Wrap|Line Numbers
  1. DECLARE GLOBAL TEMPORARY TABLE
  2. session.mygtt (...)
  3. ON COMMIT PRESERVE ROWS
  4. NOT LOGGED
If you want/need to delete all the rows, you could load the table from /dev/null (*nix) or nul (device "nul" on Win*), respectively, or from an empty file (very fast solution):

Expand|Select|Wrap|Line Numbers
  1. LOAD FROM
  2.   emptyfile.txt
  3. OF DEL
  4. REPLACE INTO
  5.   sessin.myGTT ;
Regards,

Bernd
Feb 13 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Bernard Koninckx | last post by:
Hi everybody, The following code (putted in a inherited object from AbstractTableModel object) make some errors : public void deleteRow(int rowToDelete){ try{ Object dataObject =...
4
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB...
5
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. ...
10
by: Ranga | last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on unix version of DB2, it gave the follwing error db2 => create global temporary table temp ( OGI_SYS_NR char(8) ) DB21034E ...
6
by: Martin Bischoff | last post by:
Hi, I'm creating temporary directories in my web app (e.g. ~/data/temp/temp123) to allow users to upload files. When I later delete these directories (from the code behind), the application...
2
by: David Parker | last post by:
In our application we have a table that tracks network sessions. The usage is: 1) create a session record 2) read/update the record several times during the session 3) delete the session record...
2
by: chettiar | last post by:
I am creating a procedure A which is creating a global temporary table DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2), CustomerServiceTypeId INTEGER) WITH REPLACE ON COMMIT PRESERVE...
0
by: sgueder | last post by:
Hi there, hope anybody can help me: I want to create a temporary table, want to fill that table with data and finally I want to use that table as my base-table for running some selects on it...
5
by: Rahul B | last post by:
Hi, I have very little knowledge about creating Procedures/functions in DB2. When i tried to create the test function like CREATE FUNCTION GET_TEST (P_TEST_ID INTEGER, P_SEL_OR_SORT...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.