Unanswered: global temp table + user temp tablespace
The global temp table apparently works in the User Temprary Tablespace. The more the data in the GlblTmpTable the bigger the UsrTmpTablespace seems to need to be. And the MAX size for the User Temp Tablespace is 32K pagesize. Does this mean that we cannot have a Global Temp Tablespace beyond a certain size? For 1000 records 16K was not enough so I changed it to 32K. Now if I had 10,000 records and 32K was not sufficient, is there a workaround or are Global Temp Tables merely meant to be used only for limited number of rows??
Its a shame I cant find the DDL and the dba that created it for me sits on another planet. But here is what I see from the Control center for this UserTempTablespace:
Page size 32KB
Buffer Pool - some abcPool that says 'Page size=32' and 'Size in 32KB pages=1000'
Extent size=32 32 KB pages
Prefetch size=32 32 KB pages
Is this enough info or else I can contact the other planet.
I now see I was confused about the tablespace size Vs the page size. I just dont know how to make sure I dont have trouble if my global temptable were to have 10,000 records. I noticed the error 1st time when I had additional columns (I think) and a 2nd time when I had additional rows.
You also have the option to create buffer pools or table spaces that have an 8 KB, 16 KB, or 32 KB page size. All tables created within a table space of a
particular size have a matching page size. A single table or index object can be
as large as 512 GB, assuming a 32 KB page size.
You can have a maximum of
1012 columns when using an 8 KB, 16 KB, or 32 KB page size.
number of columns is 500 for a 4 KB page size.
Maximum row lengths also vary, depending on page size:
When the page size is 4 KB, the row length can be up to 4005 bytes.
When the page size is 8 KB, the row length can be up to 8101 bytes.
When the page size is 16 KB, the row length can be up to 16 293 bytes.
When the page size is 32 KB, the row length can be up to 32 677 bytes.