Dermot's Tech Tips

Most of the time SQL Developer does a good job of migrating a database from SQL Server, Sybase ,... To Oracle. But there are some tricks to help make very large migrations perform smoother. This talks about Large migrations in terms of object numbers , not amount of data in tables. If your migrating 50 databases at once, and each has 1,000 tables, 1,000 views , 1,000 procedures . That is a lot of meta data to churn through and sometimes we find memory issues , open cursors ,... can be a problem. We are always working to reduce the amount of resources required to perform the migrations , but for the moment (SQL Dev 3.0) here are a few tricks which just might make the difference between the migration failing and the migration completing. Memory By Default SQL Developer startups with AddVMOption -XX:MaxPermSize=128M (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf) AddVMOption -Xmx640M (found in sqldeveloper\ide\bin\ide.conf) AddVMOption -Xms128M (found in sqldev...

Empty Space strings, that is Strings with no text whats so ever '', are treated differently by different databases. Oracle treats an empty string '' as NULL Sybase treats an empty string '' as a single spaced string ' ' Some other database support empty strings In the migration preferences you can decide how to handle empty strings. You can either migrate them to 1) ' ' A single space. or 2) NULL (which is how Oracle would interpret an empty string anyway) There is one other thing to note. JTDS 1.2 (the recommended JDBC driver for Sybase and SQL Server) returns an empty string even when the data in the Sybase or SQL Server database is a single space. This is a bug in JTDS, but it should be noted that if you keep the default option of migrating empty strings to a single space, you should see no issue.

Sybase FLOAT is generally used to save non integer numbers like fractions where no number of precision can hold the exact value. Sybase FLOATs do not store an exact value "It stores slightly imprecise representations of real numbers as binary fractions at the hardware level" http://www.sybase.com/detail?id=20313 Oracle has two data types, FLOAT and BINARY_FLOAT. http://stackoverflow.com/questions/332492/oracle-floats-vs-number FLOAT is really a decimal data type with exact values (basically it is a NUMERIC) BINARY_FLOAT is a binary data type which better maps to Sybase FLOAT data type If you migrate from Sybase FLOAT to Oracle FLOAT any value inserted will be treated as a specific explicit numeric, which will not behave like Sybase FLOAT. If you migrate from Sybase FLOAT to Oracle BINARY_FLOAT then any value inserted will be treated as a binary imprecise number, just like Sybase. SYBASE drop table testfloat go create table testfloat (floatcol float) go ins...

We have revamped the migration reports in SQL Developer 3.0 to provide significantly more information about your migration. Instead of "hiding" this information under the Migration Report navigator , now the reports can be viewed by double clicking on any of the folder nodes in the Migration navigator. The reports are smart enough to tailor the information depending on where in the Migration Navigator you click. Status Gives you a heads up of the status of each migration project/model. Also provides links which when double clicked, launch the migration wizard to perform the appropriate action Summary Provides the number of objects (tables, procedures, views , ... ) involved in each of the migration projects.It then breaks down into the number of objects in each database or owner. Analysis Provides fine grain details about the databases and objects being migrated. SQL Size Chart Visualize size of all SQL Objects SQL Size Summary Number of SQL Objects with similar sizes ...

SQL Developer 2.1.1 (and 3.0) supports browsing of IBM DB2 LUW 7, 8, 9 databases. IBM DB2 iSeries and ZSeries Z/OS are not supported at this time. To connect to DB2 LUW, SQL Developer first needs the correct JDBC driver. Only one specific JDBC driver is supported. db2jcc.jar with db2jcc_license_cu.jar All other IBM drivers are not supported and may cause problems even if the above two drivers are specified. So best to only add the above two. These drivers are usually found in your own DB2 database install or DB2 client install. They are also available when you agree to the license (please check this first) and download IBMs DB2 Express-C database or Data Studio Standalone administration tools http://www-01.ibm.com/software/data/db2/express/download.html Once you have a DB2 client or database installed you can search for the specific JDBC jar files. db2jcc.jar db2jcc_license_cu.jar Note you cannot use db2jcc4.jar and this would cause issues if added to SQL Developers Third Party JDBC D...

SQL Developer 3.0 (EA3) makes it easier to browse your SQL Server and Sybase servers. All databases on your server are now available under the one connection, not just the default databases selected in the new connection dialog. As long as your login has the privileges you can browse the objects in databases other than your default database. When it comes to issuing commands/queries in the worksheet you can reference objects outside of the default database explicitly SELECT * FROM databasename.owner.table1 or you can change the default database using a worksheet "hint" /*sqldev:stmt*/USE databasename; SELECT * FROM table1 or you can choose the default databases in the navigator Note that the default is set for the life of the session only. If you want to change the default database permanently you can do this in the New Connection Dialog. Just right click the connection and choose Properties. Then click "Retrieve Databases", choose the default databases and click sa...

Barry McGillin has a breakdown of the new migration features of SQL Developer 3.0 http://barrymcgillin.blogspot.com/2010/10/migration-features-in-sql-developer-30.html And an article on the new Estimation Report http://barrymcgillin.blogspot.com/2010/10/migration-estimation-from-sql-developer.html I'm going to flesh out some of these on this blog