Multiple copies of temp tables

(With apologies for formatting quirks of Deja.com) Hi Family. In investigating an anomaly with temp tables (it seems to have gone away after a reboot so never mind what it was..) I have run across another strangeness. I have run onstat -g sql on every user session and captured the output. Here is an exerpt of one session: partnum tabname rowsize 100254 tt_wkbk_id 4 10024a tt_ordered 13 10025b tt_wkbk_id 4 100259 tt_ordered 13 10025b temp_alpha_avg 8 100259 temp_cls_store_ord 76 100258 temp_ean_ord_wkbk 14 100255 tmp_order_details 168 100243 tt_alpha_summary 10 100243 tt_alpha_summary 10 100255 tmp_choose_wkbk 68 100258 tmp_order_details 168 Note that some temp table names are repeated. Some of the repeated pairs (tt_alpha_summary in this example) share the partnum, and some do not. I let spreadsheet sort them by tabname and here is some of that result: partnum tabnme 100255 tmp_order_details 100258 tmp_order_details 100243 tt_alpha_summary 100243 tt_alpha_summary 10024a tt_ordered 100259 tt_ordered 100254 tt_wkbk_id 10025b tt_wkbk_id How is this possible? If anyone were to try creating two temp tables of the same name, you would get the error that says temp table already exists. And how can two separate entries have the same partnum? Now, you think that's weird? Consider the following list: User-created Temp tables : partnum tabname rowsize 10026c tmp_wkbk 391 500025 temp_ean_store 122 500025 temp_ean_store 84 400040 temp_ean_store 122 100209 tt_alpha_summary 21 100208 t_alpha_summary 21 100207 tmp_alpha_sum_upd 9 100206 tmp_alpha_sum_dtl 13 100205 tmp_alpha_sum 12 Note that the two copies of temp_ean_store have the same partnum (weird enought) but the entries have different row sizes! (As it happens, dbspaces #4 and #5 are temp dbspaces, so the 122-row-size tblspaces are reasonable.) As I have always understood the engine's behavior with respect to temp tables, the above should be impossible. Since it is, I would ask someone to show me the error of my assumptions and explain how two different temp tblspaces in one session can have the same partnum and even have different row sizes. I am stumped. 8-( Thanks. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

Jacob - I thought Informix was designed so that each individual database connection has a separate name space for temp tables. Therefore, each connection can have temp tables with the same name as any other connection. This is not only not a problem, but is in fact a good thing - I exploit this as a feature thousands of times a day. Otherwise, how would you be able to create a temp table in a stored procedure? ;-) Rich Jacob Salomon wrote: > (With apologies for formatting quirks of Deja.com) > > Hi Family. > In investigating an anomaly with temp tables (it seems to have gone > away after a reboot so never mind what it was..) I have run across > another strangeness. I have run onstat -g sql on every user session > and captured the output. Here is an exerpt of one session: > partnum tabname rowsize > > 100254 tt_wkbk_id 4 > 10024a tt_ordered 13 > 10025b tt_wkbk_id 4 > 100259 tt_ordered 13 > 10025b temp_alpha_avg 8 > 100259 temp_cls_store_ord 76 > 100258 temp_ean_ord_wkbk 14 > 100255 tmp_order_details 168 > 100243 tt_alpha_summary 10 > 100243 tt_alpha_summary 10 > 100255 tmp_choose_wkbk 68 > 100258 tmp_order_details 168 > > Note that some temp table names are repeated. Some of the repeated > pairs (tt_alpha_summary in this example) share the partnum, and some do > not. I let spreadsheet sort them by tabname and here is some of that > result: > > partnum tabnme > 100255 tmp_order_details > 100258 tmp_order_details > 100243 tt_alpha_summary > 100243 tt_alpha_summary > 10024a tt_ordered > 100259 tt_ordered > 100254 tt_wkbk_id > 10025b tt_wkbk_id > > How is this possible? If anyone were to try creating two temp tables > of the same name, you would get the error that says temp table already > exists. And how can two separate entries have the same partnum? > > Now, you think that's weird? Consider the following list: > > User-created Temp tables : > > partnum tabname rowsize > 10026c tmp_wkbk 391 > 500025 temp_ean_store 122 > 500025 temp_ean_store 84 > 400040 temp_ean_store 122 > 100209 tt_alpha_summary 21 > 100208 t_alpha_summary 21 > 100207 tmp_alpha_sum_upd 9 > 100206 tmp_alpha_sum_dtl 13 > 100205 tmp_alpha_sum 12 > > Note that the two copies of temp_ean_store have the same partnum (weird > enought) but the entries have different row sizes! (As it happens, > dbspaces #4 and #5 are temp dbspaces, so the 122-row-size tblspaces are > reasonable.) > > As I have always understood the engine's behavior with respect to temp > tables, the above should be impossible. Since it is, I would ask > someone to show me the error of my assumptions and explain how two > different temp tblspaces in one session can have the same partnum and > even have different row sizes. I am stumped. 8-( > Thanks. > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- Richard C. Auslander Manager of Database Services AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

In article <8bbbtp$oad$1@nnrp1.deja.com>, JSalomon@bn.com wrote: > (With apologies for formatting quirks of Deja.com) > > Hi Family. > In investigating an anomaly with temp tables (it seems to have gone > away after a reboot so never mind what it was..) I have run across > another strangeness. I have run onstat -g sql on every user session > and captured the output. Here is an exerpt of one session: > partnum tabname rowsize > > 100254 tt_wkbk_id 4 > 10024a tt_ordered 13 > 10025b tt_wkbk_id 4 > 100259 tt_ordered 13 > 10025b temp_alpha_avg 8 > 100259 temp_cls_store_ord 76 > 100258 temp_ean_ord_wkbk 14 > 100255 tmp_order_details 168 > 100243 tt_alpha_summary 10 > 100243 tt_alpha_summary 10 > 100255 tmp_choose_wkbk 68 > 100258 tmp_order_details 168 > > Note that some temp table names are repeated. Some of the repeated > pairs (tt_alpha_summary in this example) share the partnum, and some do > not. I let spreadsheet sort them by tabname and here is some of that > result: > > partnum tabnme > 100255 tmp_order_details > 100258 tmp_order_details > 100243 tt_alpha_summary > 100243 tt_alpha_summary > 10024a tt_ordered > 100259 tt_ordered > 100254 tt_wkbk_id > 10025b tt_wkbk_id > > How is this possible? If anyone were to try creating two temp tables > of the same name, you would get the error that says temp table already > exists. And how can two separate entries have the same partnum? > > Now, you think that's weird? Consider the following list: > > User-created Temp tables : > > partnum tabname rowsize > 10026c tmp_wkbk 391 > 500025 temp_ean_store 122 > 500025 temp_ean_store 84 > 400040 temp_ean_store 122 > 100209 tt_alpha_summary 21 > 100208 t_alpha_summary 21 > 100207 tmp_alpha_sum_upd 9 > 100206 tmp_alpha_sum_dtl 13 > 100205 tmp_alpha_sum 12 > > Note that the two copies of temp_ean_store have the same partnum (weird > enought) but the entries have different row sizes! (As it happens, > dbspaces #4 and #5 are temp dbspaces, so the 122-row-size tblspaces are > reasonable.) > > As I have always understood the engine's behavior with respect to temp > tables, the above should be impossible. Since it is, I would ask > someone to show me the error of my assumptions and explain how two > different temp tblspaces in one session can have the same partnum and > even have different row sizes. I am stumped. 8-( > Thanks. Jacob, Could you please share the results of this post with me. I am interested of the outcome. Having said that I do believe that even though temp tables are created I think there is a pid or a session id that is associated with the temp table names making it unique. That is as I understand is the reason that allows the same user logged in on 2 different sessions to execute a query that creates temp tables possible. HTH Thanks Ram S. > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8bbg22$t8a$1@nnrp1.deja.com>, ram_cnc <rsivaguru@my-deja.com> wrote: --- SNIP --- > Having said that I do believe that even though temp tables are created > I think there is a pid or a session id that is associated with the > temp table names making it unique. > > That is as I understand is the reason that allows the same user logged > in on 2 different sessions to execute a query that creates temp tables > possible. Ram, thanks for offering help. Somehow, I failed to make it clear in my original post that each excerpt was info on a single session. e.g. The multiple copies of tt_alpha_summary with the same partnum were both within the same session. The two copies of tmp_order_details were also in the same session. If there are 49 users running any application, using SPL or not, I would expect to find 49 copies of any temp table the application creates. I would not expect to find two copies of any non-fragmented temp table in the output of "onstat -g sql" of any single session. This is what I had found. Thanks. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8bbbtp$oad$1@nnrp1.deja.com>, JSalomon@bn.com wrote: > (With apologies for formatting quirks of Deja.com) > > Hi Family. > In investigating an anomaly with temp tables (it seems to have gone > away after a reboot so never mind what it was..) I have run across > another strangeness. I have run onstat -g sql on every user session > and captured the output. Here is an exerpt of one session: > partnum tabname rowsize > > 100254 tt_wkbk_id 4 > 10024a tt_ordered 13 > 10025b tt_wkbk_id 4 > 100259 tt_ordered 13 > 10025b temp_alpha_avg 8 > 100259 temp_cls_store_ord 76 > 100258 temp_ean_ord_wkbk 14 > 100255 tmp_order_details 168 > 100243 tt_alpha_summary 10 > 100243 tt_alpha_summary 10 > 100255 tmp_choose_wkbk 68 > 100258 tmp_order_details 168 > > Note that some temp table names are repeated. Some of the repeated > pairs (tt_alpha_summary in this example) share the partnum, and some do > not. I let spreadsheet sort them by tabname and here is some of that > result: > > partnum tabnme > 100255 tmp_order_details > 100258 tmp_order_details > 100243 tt_alpha_summary > 100243 tt_alpha_summary > 10024a tt_ordered > 100259 tt_ordered > 100254 tt_wkbk_id > 10025b tt_wkbk_id > > How is this possible? If anyone were to try creating two temp tables > of the same name, you would get the error that says temp table already > exists. And how can two separate entries have the same partnum? > > Now, you think that's weird? Consider the following list: > > User-created Temp tables : > > partnum tabname rowsize > 10026c tmp_wkbk 391 > 500025 temp_ean_store 122 > 500025 temp_ean_store 84 > 400040 temp_ean_store 122 > 100209 tt_alpha_summary 21 > 100208 t_alpha_summary 21 > 100207 tmp_alpha_sum_upd 9 > 100206 tmp_alpha_sum_dtl 13 > 100205 tmp_alpha_sum 12 > > Note that the two copies of temp_ean_store have the same partnum (weird > enought) but the entries have different row sizes! (As it happens, > dbspaces #4 and #5 are temp dbspaces, so the 122-row-size tblspaces are > reasonable.) > > As I have always understood the engine's behavior with respect to temp > tables, the above should be impossible. Since it is, I would ask > someone to show me the error of my assumptions and explain how two > different temp tblspaces in one session can have the same partnum and > even have different row sizes. I am stumped. 8-( > Thanks. > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Jacob, my experiment shows that if in one session you're creating temp table, dropping it and then creating again (with the same name) then engine will use the same partition number for that table. Run this in dbaccess and check if it's true in your case --drop table test1; --drop table test2; create table test1 ( c1 serial, c2 int ); insert into test1 values ( 0, 1 ); insert into test1 values ( 0, 2 ); create table test2 ( c1 serial, c2 int, c3 char(20) ); insert into test2 values ( 0, 1, 'aaaaa' ); insert into test2 values ( 0, 2, 'bbbbb' ); select * from test1 into temp ttt111; select * from test2 into temp ttt222; onstat -g ses output Last parsed SQL statement : select * from test2 into temp ttt222 User-created Temp tables : partnum tabname rowsize 100067 ttt222 28 100066 ttt111 8 drop table ttt111; select * from test2 into temp ttt111; onstat -g ses output Last parsed SQL statement : select * from test2 into temp ttt111 User-created Temp tables : partnum tabname rowsize 100066 ttt111 28 100067 ttt222 28 100066 ttt111 8 Regards Vardan P.S. I'm using 7.30.TC3 on NT 4.0 at home -- Vardan Aroustamian vaar@geocities.com Sent via Deja.com http://www.deja.com/ Before you buy.

> In article <8bbbtp$oad$1@nnrp1.deja.com>, > JSalomon@bn.com wrote: > > (With apologies for formatting quirks of Deja.com) > > > > Hi Family. > > In investigating an anomaly with temp tables (it seems to have gone > > away after a reboot so never mind what it was..) I have run across > > another strangeness. I have run onstat -g sql on every user session > > and captured the output. Here is an exerpt of one session: > > partnum tabname rowsize --- SNIP (Expermintal data; just repeat to bottom line) -- > > As I have always understood the engine's behavior with respect to > > temp tables, the above should be impossible. Since it is, I would > > ask someone to show me the error of my assumptions and explain how > > two different temp tblspaces in one session can have the same > > partnum and even have different row sizes. I am stumped. 8-( > > Thanks. > > -- In article <8bc98s$oe8$1@nnrp1.deja.com>, Vardan Aroustamian <vaar@geocities.com> replied: > Jacob, > > my experiment shows that if in one session you're > creating temp table, dropping it and then creating > again (with the same name) then engine will use the > same partition number for that table. > > Run this in dbaccess and check if it's true in your case > > --drop table test1; > --drop table test2; > create table test1 ( c1 serial, c2 int ); > insert into test1 values ( 0, 1 ); > insert into test1 values ( 0, 2 ); > create table test2 ( c1 serial, c2 int, c3 char(20) ); > insert into test2 values ( 0, 1, 'aaaaa' ); > insert into test2 values ( 0, 2, 'bbbbb' ); > select * from test1 into temp ttt111; > select * from test2 into temp ttt222;> > onstat -g ses output> > Last parsed SQL statement : > select * from test2 into temp ttt222> > User-created Temp tables : > partnum tabname rowsize > 100067 ttt222 28 > 100066 ttt111 8 > > drop table ttt111; > select * from test2 into temp ttt111;> > onstat -g ses output> > Last parsed SQL statement : > select * from test2 into temp ttt111> > User-created Temp tables : > partnum tabname rowsize > 100066 ttt111 28 > 100067 ttt222 28 > 100066 ttt111 8 Jake here again: Vardan! That was beautiful!! My user had told me already that the apps and (mainly) the stored procedures do create, drop and recreate temp tables many times in one job/session. I would guess that the engine might not necessarily reuse the same partnum for the next incarnation of the temp table; it might have been appropriated by another session between my drop and recreation. I tried your experiment and got the results as you predicted them. I guess information about the temp table stays in a session-related structure even after it has been dropped. Sounds like a minor, low priority, insignificant, scarcely relevant bug. (I'm really tryng to make it feel inferior!:-) Thanks very much! -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.