resize table (OpenInsight 32-bit) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight 32-bit" author:"Gerry Van Niekerk" author:"Donald Bakke" author:"Barry Stevens" author:"D Harmacek" author:"bob carten"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== resize table (OpenInsight 32-bit) ====
=== At 26 AUG 2020 09:08:26PM Gerry Van Niekerk wrote: ===
 
 
<QUOTE>
Having a Blank moment



Is there a way I can resize a Current Table?



File access is very slow and the table needs to be resized..

OI 9 



Thx



Gerry


</QUOTE>
----

=== At 26 AUG 2020 10:14PM Donald Bakke wrote:  ===

<QUOTE>
<QUOTE>Having a Blank moment



Is there a way I can resize a Current Table?



File access is very slow and the table needs to be resized..

OI 9 



Thx



Gerry

</QUOTE>



I don't think you can resize a current table. I'm pretty sure you have to create a new table (presizing it to what you want) and then move the data over. You will probably want to presize the new table until you are finished copying the data over. You'll use Fix_LH for that.



[url=mailto:dbakke@srpcs.com]Don Bakke[/url]

[url=http://www.srpcs.com]SRP Computer Solutions, Inc.[/url]

</QUOTE>

----

=== At 26 AUG 2020 10:19PM Gerry Van Niekerk wrote:  ===

<QUOTE>Thanks Don,



I thought so but also thought I did it in the past..



Slow process but will have to do 



Gerry

</QUOTE>

----

=== At 26 AUG 2020 11:39PM Barry Stevens wrote:  ===

<QUOTE>
<QUOTE>Thanks Don,



I thought so but also thought I did it in the past..



Slow process but will have to do 



Gerry</QUOTE>





[url=https://www.revelation.com/index.php?option=com_content&view=article&id=610:linear-hash-reporting-and-resizing-tool-v2-for-openinsight-v9-4&catid=26&Itemid=197]Maybe this will help[/url]

</QUOTE>

----

=== At 26 AUG 2020 11:45PM Gerry Van Niekerk wrote:  ===

<QUOTE>Thanks..

</QUOTE>

----

=== At 27 AUG 2020 06:36AM D Harmacek wrote:  ===

<QUOTE>I have found the RTI_LH_STATISTICS utility useful. Although it takes a long time to run on a large/old system you get a lot of statistics. And, you can resize any table inside the utility.



On a system I save the report to Excel. Then, I order by-dsnd on the OV/LK Ratio as first candidates for reszing. Review the AvgSize to see if the Framsize should also be changed. 



My notes stress this

NOTE: You should be the only user when re-sizing tables.  Please turn off background indexing, shut down engine servers and keep out other users.  Always take a backup of your data tables before re-sizing.  



There also a RTI_REMAKETABLE and RTI_SET_SIZELOCK to consider independently.



RTI_REMAKETABLE creates a temp table with the new sizes, copies the rows into that table, then edits the revmedia to point to the new table instead of the old. This means that:



a) Nobody else should be using the table when you remake it, lest their changes be lost.



b) the revXXXX.lk numbers will change, so you if you are used to restoring individual tables by rev number you will need to pay attention.



Perhaps a Works Forum from Ed Keeman of 28 JUN 2018 will be of help.



Dave Harmacek - Harmacek Database Systems - near Boston, MA USA

</QUOTE>

----

=== At 27 AUG 2020 06:40AM D Harmacek wrote:  ===

<QUOTE>Didn't find that Form section I referenced. Email me and I will send you my copy of it (in Evernote).



Dave Harmacek - Harmacek Database Systems - near Boston, MA USA

</QUOTE>

----

=== At 27 AUG 2020 09:18AM bob carten wrote:  ===

<QUOTE>As Dave Harmacek has said, there is a window namde RTI_LH_STATISTICS which you can execute to  display the file sizes, reccounts, OV/LH ratio of all attached files.  The window takes a long time to open because it scans a sample of the data from each file to calculate the average size and the OV/LK ratio.  Tables with OV/LK gt 2 or so are candidates for resizing. 



You can resize tables using that window.  The window calls an undocumented routine named RTI_REMAKETABLE.  You an call that routine directly, passing in the name of the table you want to resize and optionally parameters to use.  If you do not pass in file sizing parameters the program will sample the file to determine an framesize, avg record size and reccount. It will set the threshold to 50%



The new table will not have the same REVxxxx number as the old.  RTI_REMAKETABLE will fix the revmedia to point to the new file, but any existing OI session will either break or update the wrong file. Thus, you must be the only user when running the routine.  The routine uses some routines such as RTI_LH_INFO which have been fixed in recent releases, so you are best off using a 9,4 system will all patches installed.  




<code>


 TableName                   = "CUSTOMERS"

 tableParams                  = "" ; *  framesize:@fm:threshold:@fm:avgsize:@fm:reccounte



call RTI_RemakeTable(TableName, tableParams)




</code>


</QUOTE>

----

=== At 27 AUG 2020 05:13PM Gerry Van Niekerk wrote:  ===

<QUOTE>Thanks everybody 

When databases grow we don’t always keep an eye on file sizes

Only when the client complains about speed do we check files etc



Also remember to update the dbt if need be.



Gerry

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=EEA9C73437085848BAF8D94AE2EF9450|View this thread on the Works forum...]]