Hi,

in this article you will find out how to take advantage of internal scripting to estimate database size. I will show you how to enable Expert Mode and how to write and execute a script that will iterate through all entities and process some entitity properties.

As you probably know, in both Physical and Logical models you can specify Size on the Entity Properties dialog. In my example, I will use the Videorental sample - physical model for Oracle 10g.

Step 1: Open Sample Model
Click File | Open Samples… and select the Videorental model.

Step 2: Define Sizes for several entities
Edit several entities and define SIZE.

Step 3: Enable Expert Mode
a) Click Settings | Options and select tab General.
b) Enable the Expert Mode checkbox.

Step 4: Open Scripting Window
a) Click Tools | Scripting Window.
b) Click View | Show Registered Objects.

On the left side, you can see a list of currently opened models. Only the Videorental sample is available in my example. Let’s select the Videorental item and click the Right Arrow button to move it to the box on the right.

Step 5: Change Name in Script
Click the Name in Script item and rename it to ‘Model’.

Step 6: Define the Function main() content

Define function main() this way:

function main()
{
var e, Entity, dbSize;
dbSize = 0;
Model.Lock();
for (e=0; e<Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);
Entity.Lock();
dbSize += Entity.Size;
Entity.UnLock();
}
Model.UnLock();
Log.Information("Estimated database size is: "+dbSize + "MB");
}

Script explanation:

• function main() is the only executable function. You can write sevelar functions but only function main() will be executed after clicking on the green Execute Script icon.
• On line 3, you can find definition of three variables. Variable ‘e’ will be used for iteration, to ‘Entity’ I will assign particular entity (object) and dbSize will represent the final estimated size of my database.
• On line 6, you can see ‘for’ loop where the number of entities is accessed via Model.Entities.Count. Where to find out how to access the number of entities? - In Reference Guide. Click Help | Reference and see PERModel class. PER = Physical Entity Relationship.

Model has Entities. Entities is a List. Click the List link to see properties of the List class.

List class has the Count property. The Reference Guide document contains all accessible objects, its functions, properties and some other valuable data. Back to our script….

• On line 10, the value of SIZE property of particular iterated entity is added to the current value of the dbSize variable. Again, how will you find out that Entity.Size can be used for getting the value of SIZE property? Open the Reference Guide and select the PEREntity class. You will find there the ’size’ property.

Note: If you want to access property specific for Oracle 10g database, select PEREntityOR10 class etc.

• The rest of the script is, I believe, self-explanatory.

Step 7: Execute Script
Click the Execute Script icon (green arrow) and see the result.

Of course, you can modify the script e.g. to filter entities by Schema and output subtotals and so on.

I look forward to your feedback.

Regards,

Vaclav

Tags: database, scripting, size, tdm

This entry was posted on Thursday, July 17th, 2008 at 7:05 am and is filed under Customize, General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.