Tuesday, 7 December 2010

Disconnecting Folder Connections

A post came in on the forum the other day about disconnecting numbers of connections in SQL Developer that are open. This was on the Feature request application for a while and having a few hours free with snow 9 inches deep outside, I plugged it in.

This feature now has 2 options. The first is on the main connections node where you can now disconnect all connections that are open.

The other option added was on folders. Now you can disconnect all the connections in a folder which if you have them organised by category or type can help speed up your maneuvers around the tool.

This will be in the next EA of SQL Developer 3.0.

Friday, 19 November 2010

Cross Connection Queries

When we were hooking up the "Copy to Oracle" functionality, Dermot added a cool bit of code to allow us to do queries across connections.

The Copy to Oracle function allows you point at a table in any supported connection type and choose copy to oracle from the menu. This then goes and creates the table in Oracle and populates it with data from the source table.

Now, as I said, to do this, Dermot added some functionality to enable this. This functionality is very flexible and the UI in Copy to Oracle uses this mechanism to achieve the functionality.

The command shown here is just the basics of what this can do and we will document this in more detail as we go on.

(SELECT * FROM "Order Details") ;

This command can be broken into a few pieces. After the BRIDGE token, the identifier is the name of the new table to be create in the current Oracle connection. The access identifier represents the connection that we will copy the table from and the query between the braces is the query which will be run on the source connection as the data for the new table.

Running the commands listed here, drop the table if it exists and creates it on the Oracle Connection.

We then run a query against that connection we get the following results in the results panel.

This is just a small view into what the bridge command can do and how it is used in the Copy to Oracle Functionality today.

Now we have the table is in Oracle. However, we can do something even cooler than this. We can join a table in Oracle with a table in Access and show the results.

BRIDGE TEMPcustomers AS access
(SELECT * FROM "Order Details")
SELECT * FROM TEMPcustomers,Products
where Products.productid=TEMPcustomers.ProductID;

This is a little step further. We take the table in Access, copy it to Oracle as a temporary table and join it with a table in Oracle to show the results below.

Thursday, 28 October 2010

Migration Estimation from SQL Developer

Last post we mentioned all the features of 3.0 for migration. This one, we're taking it a bit further and looking at the overview of the migration project itself.

For most users, the main thing they want to know is how much of the migration will be completed automatically. We've introduced a new spreadsheet which gets generated from the repository and gives a birds eye view of your database servers and your applications which are being migrated.

Here we have a number of migration projects in the migration navigator which are Sybase, SQL Server and DB2. We have converted all three and need to know how much of the migration the tools can do.

Very simply, we can right click on the top projects node, or any individual project node and choose to "create an estimation plan". This will generate a spreadsheet with the detail of the project with a high level summary of the state of the project.

On the summary page, there are 4 different graphs. The top two deal with the databases being migrated and how much automation the tool can provide. Specifically, they show how many objects failed as part of the migration and which area the failures occured in. The bottom two show the applications which have been scanned and show what percentage of the files which make up the application have database calls which need to be changed. This example shows the Sybase ctlib sample applications, the most of which have things to change.

This represents the first page of the migration plan. Each of the other pages in the plan break down the database migration into tables, stored procedures, triggers and view detail. The user is allowed to add specifics for their organisation which will help put together a project estimate of how long it will take and how much resource it will take to bring the database migration project to a production state.

Monday, 18 October 2010

Migration Features in SQL Developer 3.0

Since we released 2.1, we've worked with several customers and have added several great new features which have helped these customers increase the automation within their migration and the information they receive about it.

Here's a quick breakdown and we'll delve into these in later posts.

Command line interface
Most customers are looking to use a command line interface to do at least some of the migration for them, These can include all the steps that SQL Developer supports:
  • Capture - Connect to a source database, scan the data dictionary and create an independent model of the source.
  • Analyze - Analyze the model and report on what the source database contains and what issues there might be in it which need manual intervention
  • Convert to Oracle. - Convert the source model into its Oracle equivilent.
  • Generate Oracle Model - From the converted model, the user can generate the DDL for all the new schema in his database including all PLSQL
  • Build new database - The tool can run the sql produced and report on the errors which were found, even correlating between objects in the source and objects in the target
  • Move data - You can move data in two ways, either connected to the target database or you can generate unload files for the source database and SQL*Loader scripts to load this into Oracle

Enterprise Capture
This new feature allows the user to point at a server and capture all the databases in that server. This has been tested on Servers with up on 100 databases, captured and processed in minutes. Its one of the biggest requests we have had over the last few releases and this makes multi schema migration a breeze.

Application Scanning
In 3.0 we have introduced the concept of applications tied to databases. For this release we have focused on ctlib and dblib programs. Today, we can find all sybase calls in any program. We generate details reports on the contents of your application which provide information on size and complexity of the migration problem.

Estimation Reports
3.0 has introduced extensive database reports on the migration. Specifically, the reports include
  • High level object summary
  • High level error summary
  • Detailed error summary
  • Detailed object size summary
  • Detailed comparison between source objects, capture and converted objects and the new Oracle object
  • Temporary table usage
Migration Project Navigator
3.0 has also introduced a brand new project concept for Migration Projects. This is a new navigator which holds all the servers in a project all together. This has the benefit of being able to run several projects together, but report centrally on any one of them at any time with detailed information.

Migration Wizard
In 3.0, we have changed the quick migrate wizard to be a generic multi entry, single source of true path through a migration. This replaces the whole Quick Migrate concept and is now the only way to do a migration with the tool.

Parser and Translation enhancements
A lot of work has gone into fixing issues within the translators so they can perform in a better way and produce more consistent output. We are continuing to improve this with each release.

Copy to Oracle
Lastly, we have a new concept on all the third party navigators which is called "Copy to Oracle". This allows the user to copy tables and procedures from third parties into an oracle schema, without a migration repository.

Saturday, 16 October 2010

SQL Developer 3.0 EA1

After nearly a year in development, we're ready to show you what we have been doing in Sql Developer. You can try it out from here.

Over the next few weeks, I'll be going through some of the features and giving some ideas as to what this can be used for.