RE: Re-organisation of tablespace

Steven, Let me try to address your questions. 1) SAP tries to isolate DBAs from many of the "how-to" details. This approach has advantages and disadvantages. The advantages include vendor-supplied tools for many administrative tasks. The tools make the DBA's work is easier, but they make it more difficult to understand what is really happening. If you are interested in knowing how Informix works and what the tools actually do, I recommend the Informix classes. They also provide a good baseline, when dealing with Tech Support and other Informix professionals. 2) Before performing reorganizations with "sapdba", you should always download the latest version, which is currently "4.6B+". New features of SAPDBA 46B+ include: <> Rename chunk path <> This new SAPDBA is mandatory for all IDS versions >= 7.31 and further for NT/Intel and NT/Alpha platforms, because it fixes the bugs * Overwrite of chunks (NT only, Hotnew 188831) * problem with sysptnhdr * alter fragment problem at reorg for IDS >= 7.31UC4 3) Informix uses the term "dbspace" to represent a logical collection of tables. The term "tablespace" represents space allocated to a table. 4) Dbspace "psapes40b" contains SAP source code. It is unlikely that you deleted data from this dbspace. Therefore, it is unlikely that a reorganization will free-up much space in this dbspace. 5) When you use "sapdba" to reorganize a dbspace, you specify a threshold. Sapdba does not revise allocation parameters (EXTENT SIZE and NEXT SIZE) for tables which are smaller than this threshold. It does, however, use ALTER FRAGMENT to reorganize these tables. For tables larger than the threshold, it allocates a new table with revised extent sizes and uses INSERT/SELECT syntax to populate the new table. Both approaches will consolidate freespace within the tablespace and reduce the number of extents. 6) The "maxnext" column in the sapdba screens refer to the largest NEXT SIZE value for all tables in a dbspace. This value represents the amount of space to be requested when the space allocated to a table is filled and it needs to grow. Does this explanation address your questions? Rick Bernstein -----Original Message----- From: Steven To: informix-list@iiug.org Sent: 12/22/99 6:51 PM Subject: Re: Re-organisation of tablespace Hope I can get the details right... SAP provides a tool "sapdba" to assist in managing the database. In my case, I noticed that it uses "ALTER FRAGMENT.." as shown onscreen details. The freespace results were Time Period psapclu(Data+Indices) psapes40b(Data) Before 412770KB 961446KB After 735550KB 875444KB There is a column mentioning "maxnext" which I had gathered from my reference to be the next extent size, and both are at "10240KB". Threshold settings for the reorg was only available, under sapdba, for the table size, and it was set to 10MB. There wasnt a choice on the schema, I would assume it was with default settings from the system. The system are currently running on 7.24UC4X1 on HP-UX 10.20. Appreciate very much the help given. Thanks Steven PS. Would the courses by Informix be a good way to improve my basic knowledge? Of course, would coupled the courses with experiments on my development machine. 8-) "Art S. Kagel" wrote: > Steven wrote: > > > > Hi, > > I have just done a re-org of 2 of my tablespaces, and noticed that for > > one of them, the freespace decreased after the re-org, whereas for the > > other, it increased. I had thought that after a re-org, some space > > should typically be freed up. > > > > This is in my SAP implementation, and the 2 tablespaces are psapclu & > > psapes40b. Had done the re-org as I had deleted off quite a fair bit of > > data in my DB (~6GB). > > Details Steven. HOW did you reorg? ALTER FRAGMENT...INIT? > Dbexport/drop/create/dbimport? If the latter, or something similar, did you > create the indexes before or after loading the data? What is you setting > for FILLFACTOR? What was/is the EXTENT SIZE and NEXT SIZE? How was the > schema file created (ie if you used myschema did you pass the -a option to > calculate ACTUAL extent size or did you let the default set the current > size)? Don't forget platform and version info either. > > Art S. Kagel