Need help - TEMP dbspace

Hi all, IDS 7.30.UC5 SUN Solaris 2.6 Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 00:06:18 -- 1587776 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 56ece150 1 1 1 1 N informix rootdbs 57a17c28 2 1 2 2 N informix llogdbs 57a17ce8 3 1 3 1 N informix tempdbs 57a17da8 4 1 4 15 N informix datdbs 57a17e68 5 1 14 8 N informix dwdbs 57a17f28 6 1 18 1 N informix cycdbs 57ae8018 7 1 23 1 N informix phydbs 57ae80d8 8 2001 30 1 N T informix tempdbs2 57ae8198 9 2001 31 1 N T informix tempdbs3 57ae8258 10 1 32 1 N informix fksdbs 10 active, 2047 maximum Recently I created additional 2 temp dbspace (tempdbs2 & tempds3) in order to reduce the IO and also defined at onconfig file. Initially, I have only one temp dbspace which is tempdbs (without T flag). After the additional temp dbspace create, I'm facing some problem. In order to give you all a clear picture, I will seperate it into 3 scenario. First Scenario: When I create a temporary table on the temp dbspace with no log, the row id was not created. If I create a temporary table on the temp dbspace without no log, then I can get the row id on that particular table. Why? Second Scenario: I drop all the temp dbspace and re-create them with T flag. I'm having the same problem on the first scenario. Why? Third Scenario: I drop all the temp dbspace and re-create them without T flag. I could not get the row id at all, either with no log or without no log. Why? Besides, I was not able to create index on the temporary table in the above mentioned scenarioes. Why? I'm not having these problems before adding the temp dbspaces. Any idea on this??? How to resolve these problems with more than one temp dbspaces? Thanks & Regards, Jason

JasonYLPang@pg.SLR.com wrote: > > Hi all, > > IDS 7.30.UC5 > SUN Solaris 2.6 > > Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 00:06:18 -- > 1587776 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > 56ece150 1 1 1 1 N informix rootdbs > 57a17c28 2 1 2 2 N informix llogdbs > 57a17ce8 3 1 3 1 N informix tempdbs > 57a17da8 4 1 4 15 N informix datdbs > 57a17e68 5 1 14 8 N informix dwdbs > 57a17f28 6 1 18 1 N informix cycdbs > 57ae8018 7 1 23 1 N informix phydbs > 57ae80d8 8 2001 30 1 N T informix tempdbs2 > 57ae8198 9 2001 31 1 N T informix tempdbs3 > 57ae8258 10 1 32 1 N informix fksdbs > 10 active, 2047 maximum > > Recently I created additional 2 temp dbspace (tempdbs2 & tempds3) in order > to reduce the IO and also defined at onconfig file. Initially, I have only > one temp dbspace which is tempdbs (without T flag). After the additional > temp dbspace create, I'm facing some problem. In order to give you all a > clear picture, I will seperate it into 3 scenario. No so mysterious. Your first dbspace, tempdbs, is NOT a temp dbspace just because you named it so and placed it in DBSPACETEMP. While it IS useful to have one or more non-temp dbspaces in DBSPACETEMP they will only be used for logged temp table creation if there are any type 'T' dbspaces listed (actually if no type 'T' dbspaces are listed then all temp tables are logged regardless of the NO LOG clause). Now, you are running 7.3x, IDS 7.3x and latter will fragment ALL temp tables across all appropriate dbspaces listed. When you only had one dbspace the temp tables were all non-fragmented and so had rowids. Once you added two non-logged temp dbspaces non-logged temp tables were created fragmented across both of these, however, logged temp tables were still constrained to be created in the one logged temp dbspace, tempdbs and so had rowids. When you dropped the logged tempdb dbspace and created a non-logged tempdb temp dbspace you now have three non-logged temp dbspaces and all non-logged temp tables are being created fragmented across all three and again fragmented tables do not have rowids. However, now you do not have a designated logged temp dbspace so the engine creates logged temp tables in rootdb, again one dbspace so not fragmented and so they have rowids. Your third schenario, all of the temp dbspaces are logged so there are again no such thing as a non-logged temp table and all temp tables are fragmented across the three spaces and so with or without the NO LOG clause they have no rowids. You can read the rules for how and where temp tables are created in the Administrator's Guide. Art S. Kagel > First Scenario: > When I create a temporary table on the temp dbspace with no log, the row id > was not created. If I create a temporary table on the temp dbspace without > no log, then I can get the row id on that particular table. Why? > > Second Scenario: > I drop all the temp dbspace and re-create them with T flag. I'm having the > same problem on the first scenario. Why? > > Third Scenario: > I drop all the temp dbspace and re-create them without T flag. I could not > get the row id at all, either with no log or without no log. Why? > > Besides, I was not able to create index on the temporary table in the above > mentioned scenarioes. Why? > > I'm not having these problems before adding the temp dbspaces. Any idea on > this??? How to resolve these problems with more than one temp dbspaces? > > Thanks & Regards, > Jason