Thursday, 22 March 2012

Application Migration - Part 2

In our previous post, Application Migration -Part 1, we introduced application migration for change of database.  In the examples, we cited Sybase to Oracle.  In this post, we will introduce the way we can scan the application for items of interest and report on those with Oracle SQLDeveloper.  In our third post, we will replace and translate the items found so we can run the application in Oracle.

Since  SQL Developer 3.1 we have had the ability to scan source files and replace the items we find with appropriate changes.

The application migration functionality uses XML rules to define the type of files to be scanned and the type of changes which should be made.  The tool uses regular expressions defined in XML to scan the application.  Lets look at these in stages.  There are a few parts of the tool which needs to be defined.

  1. Recognizer - A recognizer is used to identify the types of files which are supported.
  2. Rules - Rules are ways to define regular expressions which can identify your statements or clauses that you want to identify
  3. Replacements - what types of replacements you want to do. We can do the following
    1. Text replacement: Replace the item found by the with another string
    2. Translator: Use a built in translator from SQL Developer to translate the string identified
    3. Script replacement: Use Javascript, or others like ruby or groovy
    4. Class Replacement: Use a custom class to translate the string found.
Without a recognizer, scanning a directory will give us this.
migration.sh -actions=scan -dir=/blog/src/ 
------------------------    Summary   -----------------------
High Level Overview
-------------------
        0 total calls found
        0 distinct calls found
        0 files scanned
        0 language types
        1 total files in source
        0 lines of code in scanned application
-------------------------------------------------------------
Here, the scanner is telling us, it found our test file, but doesn't know what to do with it. So, we can start with defining a recognizer to do that.

<rulesfile version="1.0" name="Shell file recognizer"
    description="Recognize .sh files">
    <!-- This recognizer will identify a shell script file. This will be used 
        by the scanner to identify a shell script. Using this recognition, any registered 
        rules will be used on this a shell script to do translation. -->
    <recognizer name="sh" description="sh recognizer" enable="true">
        <fileExtension extension="sh" />
        <expression><![CDATA[\#\!\/bin\/sh]]></expression>
    </recognizer>
</rulesfile>


So, now we can use the recognizer to identify the type of file.
Migration.sh -actions=scan -dir=/blog/src -rulesdir=/blog/rules -type=sybase
File Type Summary
----------------------
         sh      1 file

-------------------------------------------------------------
------------------------    Summary   -----------------------
High Level Overview
-------------------
        0 total calls found
        0 distinct calls found
        0 files scanned
        1 language types
        1 total files in source
        0 lines of code in scanned application
-------------------------------------------------------------
Now, we can see the scanner knows what type of file it has.  It can now use that type to identify rules which we will specify to identify the items in the file.
    <ruleset name="sample rules" enable="true" type="SYBASE"
        description="" source="sh" codetype="sql">
        <required>
            <regex>
                <expression><![CDATA[select]]></expression>
            </regex>
        </required>
        <rules>
            <regex>
                <expression><![CDATA[select.*\n]]></expression>
            </regex>
        </rules>
    </ruleset>
For a rules file, we need to define a ruleset which contains the rules.  There are two parts to it, a required section and a rules section.  The required section defines an expression which will be used to test the file to see if we should use the rules that are defined.  In this case, we have just defined a keyword to identify before we look at anything.  The rules section then defines some expressions which are used to identify some statements.  In this example, we have a select statement expressions which is really simple to illustrate the point.
The other interesting points here are the 'source'  attribute on the ruleset which defines the type of source files these rules should be used against. The second one is the 'type' attribute which defines the database type for the application.
We can either have two files with a recognizer in one and rules in another.  Or, you can merge the rules and the recognizer in one <rulesfile>

We can use the same command as earlier to scan with these rules now to see if we find something in the file. Now,  I have added other rules which search for each artifact to be changed, but the process is the same.
Migration.sh -actions=scan -dir=/blog/src -rulesdir=/blog/rules -type=sybase
 and now we get these results.
-------------------------------------------------------------
Call Breakdown Summary
----------------------
        3:      go
        1:      select count(*) from authors
        1:      select top 5 au_lname,au_fname,postalcode from authors
        1:      use pubs2
        1:      isql -Usa -P -SSLC01PAP <<EOF

File Type Summary
----------------------
         sh      1 file

------------------------    Summary   -----------------------
High Level Overview
-------------------
        7 total calls found
        5 distinct calls found
        1 files scanned
        1 language types
        1 total files in source
        12 lines of code in scanned application
-------------------------------------------------------------

So, we now have found all the statements identified that we want to translate or change for the translation.

In Summary, we have defined a recognizor to identify the file types we want to look at, we've defined some rules to identify the things we want to change.

Running the scanner with these rules against our source file, we showed in Application Migration -Part 1, we have found several things to change.  In the next instalment, we'll show you how to change the things you found to run against our oracle database.




Tuesday, 6 March 2012

Application Migration - Part 1

For the last couple of releases SQLDeveloper has added features which help our users analyze their applications.  Now, when I say analyze, I really just mean search the application code for items of interest which either need to be reported on, or need to be changed to work with Oracle.


This feature within the migration capabilities  allows you to look at application code and search it for particular items that are of interest. Lets take a very simple example of what we want to look at.  I'm using a sample shell script to use isql to get some data from sybase.

#!/bin/sh
isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
use pubs2
go
select count(*) from authors
go
select top 5 au_lname,au_fname,postalcode from authors
go
EOF



Running this in sybase using pubs2 gives us these results.



bash-3.2$ sh test.sh
             
 ----------- 
          23 

(1 row affected)
 au_lname                                 au_fname             postalcode 
 ---------------------------------------- -------------------- ---------- 
 White                                    Johnson              94025      
 Green                                    Marjorie             94618      
 Carson                                   Cheryl               94705      
 O'Leary                                  Michael              95128      
 Straight                                 Dick                 94609      

(5 rows affected)



Now, migrating this to Oracle by itself is a simple operation.  The SQL is simple and we can migrate it by hand, however, if we had many scripts like this we can use the application migration features to automate a lot of this.  


For this migration to work, we first need to migrate the database which we can do with the database migration features in SQLDeveloper.   Firstly, we use SQLDeveloper to connect to Sybase.  We can see the structure of the table for authors here.



Next we create a migration repository in an oracle schema and migrate the pubs2 database to oracle.  When its complete, we can see the status here.




Now we have the database moved to oracle, we can see the tables and data when we create a connection to the new schema for dbo, which by default is called dbo_pubs2.




Now, when we migrate the application and run it against oracle, we have a database and data to use.
So, in a perfect world, we can get the application to run out of the box after migration.  Lets manually rewrite this and see what it looks like.


#!/bin/sh

sqlplus dbo_pubs2/dbo_pubs2 << EOF

select count(*) from authors;

select au_lname, au_fname, postalcode from authors where rownum <=5;

EOF


and running this against our migrated database gives us this output.
ORACLE>sh test.sh

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 6 12:24:29 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> 
  COUNT(*)
----------
        23

SQL> SQL> 
AU_LNAME                                 AU_FNAME             POSTALCODE
---------------------------------------- -------------------- ----------
White                                    Johnson              94025
Green                                    Marjorie             94618
Carson                                   Cheryl               94705
O'Leary                                  Michael              95128
Straight                                 Dick                 94609

SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE>


So at this stage, the we have a migrated database, and we also have a mockup of what we want our application changes to be for changing the application.  In the next post, Application Migration - Part 2, we'll look at migrating the script using the application migration features of SQL Developer to change the sql and the call to isql.