Temp Tables/DBSpaces

Temp Tables/DBSpaces

Post by Porteous, Li » Thu, 16 Jan 2003 01:46:02



IDS 7.30.UC6
Solaris 2.6 (105181-21)

Can anyone help out with this one?

Our development staff are running a 4ge to extract various types/amounts of
data into a couple of temp tables, one of which has a row size of 1041
bytes.  We have 3 temp dbspaces configured, each 1Gb in size.  These are
specified in the ONCONFIG parameter as DBSPACETEMP
tmpdbs01:tmpdbs02:tmpdbs03.  The temp tables are being created in the code
using the CREATE TEMP TABLE statement (WITH NO LOG).

From previous usage of temp tables, I thought that the server would
distribute the data evenly over these temp dbspaces, e.g. the number of free
pages shown within each temp dbspace when running onstat -d would go down
evenly across the 3 dbspaces as the program ran and populated the temp
table.  However, what seems to be happening is that the temp table with
rowsize 1041 bytes is being stored exclusively in the first temp dbspace.
Once it reaches 509670 records, it continues to process but doesn't insert
any more records into the temp table (this figure matches the number of free
pages in tmpdbs01 before the program starts).  The temp table doesn't expand
into either of the remaining temp dbspaces.

Is this right?  

I was going to suggest changing the structure of the table to try and get
the row size under 1000 bytes to allow at least 2 rows per page.  Would this
help?

We're going to be doing a lot of large data extracts of this type, so this
is a problem that we'll probably hit again if we don't get it right this
time.

If you need any further information, let me know.  Any help/advice you can
offer will be greatly appreciated.

Liz Porteous
Network Services

Scottish Friendly Assurance Society Limited
Scottish Friendly House, 16 Blythswood Square, Glasgow G2 4HJ
Customer Enquiries: 08456 00 54 33   Switchboard: 0141 275 5000
Fax: 0141 221 4864   Internet: www.scottishfriendly.co.uk

The Scottish Friendly Group of Companies is
regulated by the Financial Services Authority.
Member of ABI and AFS

Internet communications are not secure and therefore
the Scottish Friendly Group of Companies does not
accept legal responsibility for the contents of this message.
Any views or opinions presented are solely those of the
author and do not necessarily represent those of
the Scottish Friendly Group of Companies.

All emails entering and leaving the Scottish Friendly Group of
Companies are scanned to ensure they are free from all
known viruses.

 
 
 

Temp Tables/DBSpaces

Post by Zev Berezi » Thu, 16 Jan 2003 03:06:25


Liz,

   Have you tried to make one temp dbspace and add 2 additional
chunks to it rather than having 3 temp dbspaces? I remember there
was a discussion on this issue a few months ago, but I don't recall
the outcome.

HTH,

B&H Photo         Web: www.bhphoto.com


Quote:> IDS 7.30.UC6
> Solaris 2.6 (105181-21)

> Can anyone help out with this one?

> Our development staff are running a 4ge to extract various
> types/amounts of data into a couple of temp tables, one of which has a
> row size of 1041 bytes.  We have 3 temp dbspaces configured, each 1Gb
> in size.  These are specified in the ONCONFIG parameter as DBSPACETEMP
> tmpdbs01:tmpdbs02:tmpdbs03.  The temp tables are being created in the
> code using the CREATE TEMP TABLE statement (WITH NO LOG).

> From previous usage of temp tables, I thought that the server would
> distribute the data evenly over these temp dbspaces, e.g. the number
> of free pages shown within each temp dbspace when running onstat -d
> would go down evenly across the 3 dbspaces as the program ran and
> populated the temp table.  However, what seems to be happening is that
> the temp table with rowsize 1041 bytes is being stored exclusively in
> the first temp dbspace. Once it reaches 509670 records, it continues
> to process but doesn't insert any more records into the temp table
> (this figure matches the number of free pages in tmpdbs01 before the
> program starts).  The temp table doesn't expand into either of the
> remaining temp dbspaces.

> Is this right?  

> I was going to suggest changing the structure of the table to try and
> get the row size under 1000 bytes to allow at least 2 rows per page.
> Would this help?

> We're going to be doing a lot of large data extracts of this type, so
> this is a problem that we'll probably hit again if we don't get it
> right this time.

> If you need any further information, let me know.  Any help/advice you
> can offer will be greatly appreciated.

> Liz Porteous
> Network Services

> Scottish Friendly Assurance Society Limited
> Scottish Friendly House, 16 Blythswood Square, Glasgow G2 4HJ
> Customer Enquiries: 08456 00 54 33   Switchboard: 0141 275 5000
> Fax: 0141 221 4864   Internet: www.scottishfriendly.co.uk

> The Scottish Friendly Group of Companies is
> regulated by the Financial Services Authority.
> Member of ABI and AFS

> Internet communications are not secure and therefore
> the Scottish Friendly Group of Companies does not
> accept legal responsibility for the contents of this message.
> Any views or opinions presented are solely those of the
> author and do not necessarily represent those of
> the Scottish Friendly Group of Companies.

> All emails entering and leaving the Scottish Friendly Group of
> Companies are scanned to ensure they are free from all
> known viruses.


 
 
 

Temp Tables/DBSpaces

Post by rkusene » Thu, 16 Jan 2003 04:20:14



> IDS 7.30.UC6
> Solaris 2.6 (105181-21)

> Can anyone help out with this one?

> Our development staff are running a 4ge to extract various types/amounts of
> data into a couple of temp tables, one of which has a row size of 1041
> bytes.  We have 3 temp dbspaces configured, each 1Gb in size.  These are
> specified in the ONCONFIG parameter as DBSPACETEMP
> tmpdbs01:tmpdbs02:tmpdbs03.  The temp tables are being created in the code
> using the CREATE TEMP TABLE statement (WITH NO LOG).

> From previous usage of temp tables, I thought that the server would
> distribute the data evenly over these temp dbspaces

only implicit temp tables are fragmented automatically over all dbspsaces.

For e.g.
select tabid from systables into temp tmp_systables;
The implicit tmp_systables will be fragmented over all temp dbspaces.

However an explicit temp table like
CREATE TEMP TABLE TMP_SYSTABLES
( tabid integer
) with no log ;

is not fragmented. The server picks up any of the dbspaces mentioned in
DBSPACETEMP and creates the temp there in that dbspace only.

If you want it to be fragmented, then change the CREATE TEMP TABLE

CREATE TEMP TABLE TMP_SYSTABLES
( tabid integer
) with no log
  FRAGMENTED BY ROUND ROBIN IN tmpdbs01,tmpdbs02,tmpdbs03

ravi

 
 
 

Temp Tables/DBSpaces

Post by Rajib Sarka » Thu, 16 Jan 2003 07:23:10


Usually, if you do the following the table will be fragmented across the
TEMP DBSPACES automatically.

a) select * from <table> into <temp table> with no log
b) create temp table t1 (c1 int) fragment by round robin / expression

But, if you just create the temp table using:

create temp table t1(c1 int) with no log;

it'll create the table using the first available temp dbspace and won't be
able to spread itself onto the other TEMP dbspaces.

HTH

Thanx much,

Rajib Sarkar
Advisory Support Engineer (Wells Fargo Bank)
IBM Data Management Group
Ph :  (602)-217-2100
Fax:  (602)-217-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi

                      "rkusenet"                                                                                                      

                      co.ca>                    cc:                                                                                    
                      Sent by:                  Subject: Re: Temp Tables/DBSpaces                                                      
                      owner-informix-li                                                                                                

                      01/14/2003 12:20                                                                                                
                      PM                                                                                                              
                      Please respond to                                                                                                
                      "rkusenet"                                                                                                      


Quote:

> IDS 7.30.UC6
> Solaris 2.6 (105181-21)

> Can anyone help out with this one?

> Our development staff are running a 4ge to extract various types/amounts
of
> data into a couple of temp tables, one of which has a row size of 1041
> bytes.  We have 3 temp dbspaces configured, each 1Gb in size.  These are
> specified in the ONCONFIG parameter as DBSPACETEMP
> tmpdbs01:tmpdbs02:tmpdbs03.  The temp tables are being created in the
code
> using the CREATE TEMP TABLE statement (WITH NO LOG).

> From previous usage of temp tables, I thought that the server would
> distribute the data evenly over these temp dbspaces

only implicit temp tables are fragmented automatically over all dbspsaces.

For e.g.
select tabid from systables into temp tmp_systables;
The implicit tmp_systables will be fragmented over all temp dbspaces.

However an explicit temp table like
CREATE TEMP TABLE TMP_SYSTABLES
( tabid integer
) with no log ;

is not fragmented. The server picks up any of the dbspaces mentioned in
DBSPACETEMP and creates the temp there in that dbspace only.

If you want it to be fragmented, then change the CREATE TEMP TABLE

CREATE TEMP TABLE TMP_SYSTABLES
( tabid integer
) with no log
  FRAGMENTED BY ROUND ROBIN IN tmpdbs01,tmpdbs02,tmpdbs03

ravi

 
 
 

1. Temp Table/dbspace problem

I am having a problem with an Informix application.  The DBSpace that the
application uses gets filled seemingly when large queries are performed.
After the error occurs we have examined the DBSpace via TBMonitor and it is
full.  However when we add the sizes of all the tables reported to exist in
that DBSpace only about half the space is accounted for.

Within our application, any temporary table we create is created using
'CREATE TEMP TABLE' and 'WITH NO LOG'.  Our understanding is that these
tables would be created in the DBRoot's DBSpace.  This space does not fill
up.

So I guess my question is does Informix create temporary tables in the
application's DBSpace beyond those that we explicitly create?  If so, how
can we view them (using TBMonitor or anything)?  Even more important, how
do we flush them to recover our dataspace?


Thanks!
 - George

2. How to make a informationmodel for a database?

3. temp table in temp dbspace

4. System 10 Hangs

5. temp tables in root dbspace despite DBSPACETEMP setting

6. Table Existence and Exception Handling

7. Temp table vs Global Temp table

8. Administrative Permissions SQL 6.5 ?

9. HDR and Temp dbspaces

10. onstat -D, temp dbspaces, spreading load

11. Multiple Temp Dbspaces

12. Disabled Temp DBspace

13. Temp DBSPACES