Modifying the Estimate Database Size Mechanism

By default, the Estimate Database Size mechanism uses standard algorithms to calculate the sizes of tablespaces, tables, columns, and indexes and adds them together to provide an indication of the size that the database will require. You can override the algorithm for one or more of these types of objects or include additional objects in the calculation by adding the GetEstimatedSize event handler to the appropriate object in the Profile category and entering a script to calculate its size.

1. Select Database > Edit Current DBMS to open the DBMS definition file, and expand the profile category.
2. Right-click the metaclass for which you want to provide a script to calculate the object size, select New > Event Handler to open a selection dialog, select the GetEstimatedSize event handler, and then click OK to add it under the metaclass.
3. Click the Event Handler Script tab in the right pane and enter appropriate code to calculate the size of your chosen object.
   
   
   

   In the following example, we look at extracts of a GetEstimatedSize event handler defined on the Table metaclass to estimate the size of the database by calculating the size of each table as the total size of all its columns plus the total size of all its indexes.

   Note: For examples of the GetEstimatedSize event handler in use on the Table and other metaclasses, see the Sybase IQ v15.2 and HP Neoview R2.4 DBMS definition files.

   In this first extract from the script, the GetEstimatedSize function opens and the size of each table is obtained by looping through the size of each of its columns. The actual work of calculating the column size is done by the line:

   ColSize = C.GetEstimatedSize(message, false)

   , which calls the GetEstimatedSize event handler on the Column metaclass (see Calling the GetEstimatedSize Event Handler on Another Metaclass):

   Function %GetEstimatedSize%(obj, ByRef message)
   
   ' First compute global database setting variable we will need.
         
   ' Get table size and keep column size for future use
      Dim ColSizes, TblSize, ColSize, C
      Set ColSizes = CreateObject("Scripting.Dictionary")
   
      TblSize = 0 ' May be changed to take into account table definition initial size.
   
     for each C in obj.Columns
   
   	 ' Start browsing table columns and use event handler defined on column metaclass (if it exists).
         ColSize = C.GetEstimatedSize(message, false)
   
   		' Store column size in the map for future use in indexes.
         ColSizes.Add C, ColSize
   
   		' Increase the table global size.
         TblSize = TblSize + ColSize
      next
      Dim RawDataSize
      RawDataSize = BlockSize * int(obj.Number * TblSize / BlockSize)
   	' At this point, the RawDataSize is the size of table in database.

   Next the size of the table indexes is calculated directly in the script without making a call to an event handler on the Index metaclass, the line outputting index sizes is formatted and the size of the indexes added to the total database size:

   ' Now calculate index sizes. Set up variables to store indexes sizes.
      Dim X, XMsg, XDataSize
      XMsg = ""
      for each X in obj.Indexes
         XDataSize = 0
         ' Browsing index columns and get their size added in XDataSize
         For each C in X.IndexColumns
            XDataSize = XDataSize + ColSizes.Item(C.Column)
         next
         XDataSize = BlockSize * int(obj.Number * XDataSize / BlockSize)
   
   		' Format the display message in order to get size information in output and result list.
         XMsg = XMsg & CStr(XDataSize) & "|" & X.ObjectID & vbCrLf
   
         ' Add the index size to table size.
         RawDataSize = RawDataSize + XDataSize
      next
   

   Finally the size information is formatted for output (see Formatting the Database Size Estimation Output). Each table is printed on a separate line in both the Output and Result List windows, and its total size including all columns and indexes is given:

   	' set the global message to table size and all indexes (separate with carriage return).
      message = CStr(RawDataSize) & "||" & obj.ShortDescription & vbCrLf & XMsg
      
      %GetEstimatedSize% = RawDataSize
      
   End Function

   Once all the tables have been processed, PowerDesigner calculates and prints the total estimated size of the database.

• Calling the GetEstimatedSize Event Handler on Another Metaclass
  You can call a GetEstimatedSize event handler defined on another metaclass to use this size in your calculation. For example, you may define GetEstimatedSize on the Table metaclass, and make a call to GetEstimatedSize defined on the Column and Index metaclasses to use these sizes to calculate the total size of the table.
• Formatting the Database Size Estimation Output
  You can format the output for your database size estimation. Sub-objects (such as columns and indexes) contained in a table are offset, and you can print additional information after the total.