Kuljit Sangha's blog

Migrating your BMF Repository

Kuljit Sangha — Wed, 30 Dec 2009 23:39:00 GMT

This blog will give you the necessary steps to migrate your MySQL BMF repository to another database of your choice. The following databases are supported by BMF: Oracle, SQL Server, MySQL and Sybase. We will be using the Data Migration Wizard that is provided to us by BMF.

The first step is to decide which database platform you would like to do the migration for. Once that is decided, we can begin.

Click Next. On this next screen you will select the source of the repository. Most people will choose the BMF ODBC Connection since they are migrating from the default location.

Click Next. On the next screen, you will choose the database platform you would like to migrate the repository over to. However in order to select a database, you must create an ODBC connection to that database. If one does not already exist, click on the ODBC Administrator button. Once you select the ODBC connection, put in the necessary username/password to connect to the database. Beware that the BMF objects will be created into this account. If you prefer an independent BMF schema, then please create a new user ahead of time.

Once you have completed the above information and have clicked Next, you will come to a confirmation screen. This will indicate that everything has been setup correctly, and you are now ready to migrate the data.

Click Finish and the wizard will start the migration of your data.

After everything is completed, you can go into the target database to verify that the objects have been created successfully. They will be prefixed with ‘BF’.

Code Assist Feature – Customizing it to fit your needs

Kuljit Sangha — Mon, 31 Aug 2009 19:02:00 GMT

Under the Editor menu in Toad for Oracle we have the “Make Code” & “Strip Code” features which allow you to embed SQL Statements in a programming language of your choice.

For example you can take the following statement:

SELECT   b.order_date, d.price, a.cust_id, a.NAME, c.qty, d.part_id,
         d.qty_on_hand
    FROM quest_dev.ow_customers a, quest_dev.ow_orders b, quest_dev.ow_order_lines c, quest_dev.ow_parts d
   WHERE (d.part_id = c.part_id)
     AND (b.order_id = c.order_id)
     AND (b.cust_id = a.cust_id)
     AND (d.part_id = c.part_id)
     AND (c.order_id = b.order_id)
     AND (price < 150)
     AND (d.qty_on_hand < 250)
ORDER BY b.order_date ASC

Go to Editor menu and click Make Code. The new statement will be copied to the clipboard and you can paste it into another new tab:

SQL.Append(" SELECT   b.order_date, d.price, a.cust_id, a.NAME, c.qty, d.part_id, ") 
SQL.Append("          d.qty_on_hand ") 
SQL.Append("     FROM quest_dev.ow_customers a, quest_dev.ow_orders b, quest_dev.ow_order_lines c, quest_dev.ow_parts d ") 
SQL.Append("    WHERE (d.part_id = c.part_id) ") 
SQL.Append("      AND (b.order_id = c.order_id) ") 
SQL.Append("      AND (b.cust_id = a.cust_id) ") 
SQL.Append("      AND (d.part_id = c.part_id) ") 
SQL.Append("      AND (c.order_id = b.order_id) ") 
SQL.Append("      AND (price < 150) ") 
SQL.Append("      AND (d.qty_on_hand < 250) ")

The language above is an example of VB.net. You can change the language under View Menu |Toad Options | Editor | Code Assist . Look at the bottom left corner and you will see the Make Code section.

If you click on VB.net and click “Edit” you will see the code we use:

{VB.NET Language Template} 
 
%SqlVar%.Append(" %SqlText% ") 
%SqlVar%.Append(" %SqlTextNext% ")

%SqlVar%	This will put the word SQL in your template.
%SqlText%	This will put the first line of your select statement.
%SqlTextNext%	This will put the remaining lines of your select statement.

Knowing these values … it makes it easy for the end user to customize their own language. Toad comes with a pre-defined list of languages, but in case you are working with something not in our list, you can click on the “New” button and create your own template using the above variables in your code. In some cases, you will find that some languages that we do not list (like ASP) use the same template that can be found in our list (like VB) … so before you create your own template, take a look at all our existing templates.

Hope this was helpful, and if you are still having problems let us know and we will do our best to assist you.

Making Database Changes Logically

Kuljit Sangha — Thu, 30 Jul 2009 15:03:00 GMT

Our experts at Quest continue to work hard to develop educational material for our end users to learn from, thus allowing them to become product experts themselves. Unfortunately, due to the hectic schedule of our end users, they are sometimes unable to put all the pieces together. In this blog, I piece together some steps for using the Toad Data Modeler and tying it back to Toad for Oracle by presenting a situation, steps, and an objective.

Situation: You are tasked with the responsibilities of modifying your database objects and documenting the changes. There could be numerous ways of handling this, but I have chosen to tackle the method of modifying the objects logically first, and then applying the changes physically on the database.

Steps:

Using the Toad Data Modeler, we have the ability to reverse engineer your current schema into a physical data model. The following video tutorial illustrates the Reverse Engineering feature:
1. http://modeling.inside.quest.com/entry.jspa?externalID=1673&categoryID=158
Now, you can begin to work on your model and make any necessary changes. I find this to be very helpful because it eliminates the chances of making errors on the database with physical changes. Taking a logical approach, you can make changes numerous times without any real consequences. The following video tutorial discusses entity attribute modifications:
1. http://modeling.inside.quest.com/entry.jspa?externalID=2057&categoryID=158
If I have lost you along the way or you want to brush up on your foundational knowledge, the following video will help you obtain a good understanding of the workspace you are working on:
1. http://modeling.inside.quest.com/entry.jspa?externalID=2060&categoryID=158
Once you have made the necessary changes, you may want to save that particular version of the model. We have a built-in Version Manager in TDM to allow you to reference these versioned models. The following video tutorial further illustrates this concept:
1. http://modeling.inside.quest.com/entry.jspa?externalID=1697&categoryID=281
Once all of the changes have been made, you can choose between two options depending on your objective:
1. Option 1: If you want to generate a script for all the objects in the model, then we have a Generate DDL Script feature which that can be viewed in the following video:
  1. http://modeling.inside.quest.com/entry.jspa?externalID=2059&categoryID=158
2. Option 2: If you only want to generate a script of the differences*, then you can use our Compare/Alter Script feature which can be viewed in the following video:
  1. http://modeling.inside.quest.com/entry.jspa?externalID=1681&categoryID=158
  2. *For Option 2, you will have to compare it to another model. Specifically, keep the original vanilla model when you first reverse engineered the schema, and the current model is a version of that. By that I mean you have 1.0 and the model with the changes is 1.1 or 2.0. To achieve this type of versioning, please see step 4 above.
After the script has been generated, you can launch Toad for Oracle and open it in the editor. From here you can execute the script to apply the necessary changes. Please keep in mind that you will need to have the proper Oracle permissions to do so.
If you would like to take it one step further by comparing the new changes to another existing schema, then Toad offers you the Compare Schema functionality. This feature also allows you to develop a synchronization script to make the target schema look like the source schema. Please note that the Compare Schema feature is available in Standard Toad, however the ability to use the sync script is limited to the DB Admin module add-on in Toad. The following video provides additional information about this feature:
1. http://www.toadworld.com/Portals/0/assets_ifs/flashdemos/SchemaCompareSynch/index.htm
At this point you will have fulfilled your requirements and hopefully saved a lot of time from manually having to do all of this. You can keep the scripts created from the Toad products as documentation backup. If you are interested in reports, the video above in the Reverse Engineering tutorial also showcases how to generate reports. Of course, if you saw that video already then you know about the reporting functionality

Objective: The point of this blog was to string together multiple tutorials available to the public into one workflow. In many instances, we go on site and users ask us how to make their lives easier, or assist in certain day-to- day tasks. We recommend they visit our websites such as Toad World and learn from the tutorials. We realize that this may be overwhelming at times, so in the interest of helping to make our users’ lives easier, hopefully blogs like this can showcase a group of tutorials that meet one greater goal.

We will continue to encourage feedback in an effort to create what you, the public, is looking for in our tutorials and blogs.