Sybase to Oracle conversion

Background: Few years ago, I worked on a project where I had to convert close to 100 stored procedures from Sybase to Oracle. There are several differences between the 2 databases.

I was hired to be the Oracle expert in a team full of Sybase developers. I constantly worked with them to convert the databases and the code to Oracle. I worked on it from start to finish. I was asked to set up the environment, set the standards for Oracle coding and convert a lot of stored procedures. I participated in their migration project and eventually even supported their batch environment that ran in both Sybase and in the new Oracle environment. I even got a chance to work as a Oracle development DBA within the team. This gave me a lot of opportunity to learn about both the databases. I got to know their similarities (they both are SQLbased relational databases, aren't they?) and their differences (Did you know, Sybase database is the same as Oracle Schema and Oracle Database is the same as Sybase Server??).

There are a lot of sites out there discussing the differences and even migration from one to another. I benefited from a lot of those and I made a lot of notes at the time. When I was working on the project, I was so busy I couldn't post all those notes online. Now, that I have some time, I am dusting off my old notes and posting here. I hope it benefits someone doing similar kind of migration.

Sybase to Oracle Database conversion

This is a much bigger topic than I intend to cover in this post. My take on the conversion is mainly from a developer point of view. The DBAs will have a lot more to add about the database setup and architecture. Though, you will see references to database level differences, as I did perform the duties of a development DBA within the team. Below presentation lists out some of the differences while coding a stored procedure in the 2 databases. I took one of the procedures we migrated and annotated it with comments. I made this for the team at the time. I've changed the names of the tables and procedures to be more generic.

Note about the convention in the attached presentation: I wrote a complete Oracle Coding standards manual for the Sybase developers. I am following this convention in the attachment below. In general keywords are upper cased, other identifiers lower cased. The names of identifiers follow certain naming convention. I will post about this separately.

Sybase to oracle_conversion[slideshare id=8735258&w=668&h=714&sc=no]

View more documents from svaradar

PB 101 - Contd... Project Workspace

As mentioned earlier, PB is geared up for developing applications for various platforms. We saw how the IDE is built around the Project Workspace, and the tools it provides to access every in the workspace. First let's define the various parts of a Powerbuilder based software development project.

PB 101 - Contd... Powerbuilder IDE

Powerbuilder IDE

PB IDE, just like any other windows application is an MDI application. It is made up of several windows and menus and toolbars. When you open the IDE, it opens up with no Workspace. You need to create/open a Workspace (using options in File menu) to work on a specific project. A  Workspace wraps around one or more Targets which in turn includes one or more Libraries. The libraries contain the objects. The objects themselves have properties and methods like in any Object oriented environment. The objects also contain events as Powerbuilder is an event driven programming environment.

Navigation through the project hierarchy is available through a tool called System Tree. The IDE also contains a full menu toolbar called Powerbar that lets you perform several tasks including creating various components in the project hierarchy. The IDE is made up of several individual tools and wizards, which are called Painters. There is a painter for each type of object and each task in PB.

System Tree & Powerbar

The System Tree is a project explorer. This let's you see the project hierarchy mentioned above, in a tree like fashion. The TreeView object fits this hierarchical structure perfectly. The topmost parent in the tree is the Project Workspace. You can drill down targets and libraries to object properties and methods here. You can expand or collapse at any level in the tree. You can open any existing objects in the System Tree (by double-clicking on them).

Powerbar is the Powerbuilder menu Toolbar containing buttons for important menu items.  You can create new objects by Clicking on the "New" button in Powerbar (or File->New option in the menu).

Clicking on New opens up a pop-up window that has several options (buttons) to open various wizards and tools in PB. You can create workspace, target, PB objects including windows, menus, datawindows from here. You can also open up several tools and wizards, such as Library explorer, Database wizard, File Editor, PB Profiler etc, by creating "New" instances of these. Every such wizard or tool in PB is called a Painter.

[caption id="attachment_139" align="aligncenter" width="1024" caption="Powerbuilder System Tree and Powerbar"][/caption]

Fig 1 - Powerbuilder System Tree & Powerbar

PB New Options - Here showing several types of PB Objects

Fig 2- New Option in Powerbar leads to "New" options Pop-up

Painters

As mentioned above Powerbuilder IDE is essentially made of several Painters. Each specific component type has it's own Painter (Object Editor) and several tasks have associated Painters (Tools or Wizards). So you will see Window Painter, Datawindow Painter, Database Painter, Library Painter etc. Each Painter is self-contained unit and has it's own menu (and toolbar), a control(s) list and properties sheet. As you can see, Powerbuilder IDE itself is designed in an object oriented way.

You open the individual component painters when you open an object in System Tree View or when Click on New on he menu bar.  Several task and tool painters can be opened in Powerbar.

Here are some of the Painters are available in PB:

• Application Painter


• Window Painter


• Menu Painter


• DataWindow Painter


• Function Painter


• User Object Painter


• Database Painter


• Library Painter


• Project Painter (this is used to build applications - sort of compiler/linker options window; more about this later).

PB IDE - Other parts

Apart from these, the IDE UI has the following components:

An Output (child) Window which shows all the messages from the PB IDE environment, such as compiler outout, error messages etc.

A To-Do List Popup - Here you can enter a "todo" list. You can enter your own laundry list of things to be done in a project. Some of the wizards in PB also create To-Do entries automatically.

PB IDE includes a Browser window, which lists out all the objects in the project. This tool is very useful in locating objects in a large project with lot of libraries. As of version 10.5, it does not have a search capability yet, but you can navigate through libraries and types of objects to easily locate an object. Once found, you can "Edit" the object.

There are also several tools and wizards available in PB. Application Profiler, Debug Window, Database Painter, EA Server Profile etc. Each of these have special Toolbars attached to them.

See below link for more information (The link is for PB 11.0, but still applicable to any version > 8.x in general).

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc37772_1100/html/pbgs/CBBCHDJG.htm

PB 101 - Starting from scratch

the last few posts, we talked about PB a lot. My last post on Hello World gave an introduction to PB as a programming language. Now, it's about time we introduce Powerbuilder, the programming environment itself and how to use it to successfully develop a simple application.

PB as a n-tier tool

PB started as a simple client/server tool. We and PB are now in n-tier world. This means an application could be divided into multi-tiers,  such as a client and one or more application servers connecting to the database in the backend. Even the client can be multiple, as in you may have a PB application client in the office, a web page accessing the same servers etc. Dividing the application logic into logical blocks of client and server(s) is called application partitioning. Java J2EE is a great multi-tier development environment in 3GL. PB has grown into a n-tier tool in the 4GL world.  Sybase has come up with an Application Server called EA Server (originally called Jaguar in version 7). You can now use it to build not only windows applications, but middle tier applications (Application Servers) and Web Applications as well. To handle this new world, PB has completely changed it's own GUI as of version 7.

PB IDE

When you open PB (versions > 8.x), the first thing you will see is the workspace. A workspace comprises of several "Targets" each target representing a piece (partition) of the application that can be independently deployed. A target can be for different type of applications - a windows client application, a Jaguar component, a DLL, a JSP program, a .Net program etc.

To the target, you add the PB libraries, by adding it to the library list. A library list is not only for housekeeping of libraries in a target, but at run-time this serves as the search path for the application to find objects.

And to the libraries, you add objects such as Applications, windows, datawindows etc. When you create a windows client application, an application object will be automatically added to the library just created. Below images show the various parts of a workspace. We will start looking into each of these in detail in the coming posts.

[gallery]

Google Sites

At work we switched to Google mail some time back. We use Google docs to share docs. I started using Google sites just out of curiosity. Anyone that has a GMail account has automatic access to Google Docs and Google Sites. Google Sites is a simple web site builder. Google Site started as JotBot, a wiki tool that Google bought in 2006 and later developed into what it is today. See wiki article at http://en.wikipedia.org/wiki/Google_Sites for more info.

Creating a Site
Building a site using Google Site is very easy. Simply click on "Create New Site" button and select any site template available in the template gallery. Then select the Theme and visibility settings, voila... you have the new site up and running. Though, it can be used to build any web site, because of it's original wiki flavor, it's a great tool to create wiki pages. Google site also offers several gadgets that can be embedded thus you can include other web pages, graphics etc. Google Docs can easily be embedded as well.

Creating Web Pages
Once a site is created, pages can be added to it at any time. Google Site by default offers the following page templates:

Web Page
Announcements
File Cabinets
etc

Any of these pages can have embedded content. "Web page" template A simple web page. Each new posting to the site will be an additional page. "Announcements" template offers a Blog/wiki style post where each page will have multiple posts. So a wiki site may contain one page of type "Announcements" and multiple posts in it. It's like a blog, except the pages can be added/updated by anyone (See security settings) and thus it's like a wiki page. File Cabinets shows list of files uploaded. One can create other complex templates based on these.

Each page can have comments and attachments. (These options can be turned off for specific pages as well).

Managing the Site
Site settings can be changed at any time, by clicking on More Actions->Mange Site (Only owners can manage sites). This screen can be reached by clicking on "Edit Sidebar" link. Here several options are shown. Here several options are available. In Site Layout option, you can change the size and layout of the site. This also has a Navigation section. By adding pages (links) here, they can be displayed on the side bar. The theme used for the site can be changed at any time as well.

Security Settings
Google Site access is controlled by "Site Permissions". This can changed at any time. Typically permissions are,

1. Public - Any one can see it
2. Anyone with the link - If someone got hold of the link, they can see it
3. Private - Access only by invite; sign-in required to see the pages.

Google Site vs Wordpress
In many ways, Google Sites can be compared to Wordpress.com blog pages. In both, you can create (web) pages quickly and use gadgets ( extensions) to add functionality. Both provide templates and themes to create web pages quickly. In general, Wordpress has a bigger developer community and is also a more matured product. It also has a lot more extensions available. Sites developed using Google Sites tend to be small personal sites. Since Google Sites had a wiki beginning it is stronger on collaboration of content. It allows each site to have multiple owners, contributors and viewers. This makes it more useful for development community to share information within a team.

My Google Sites
I created my first Google Site for our apartment community. It's a small community and I just created a page or 2 as a bulletin board. After that, I created and maintain a web page for our team to use at work. We create some developer documents which we used to dump on Network drive. The problem there was the files are not easily searchable and thus, after 100's files in the drive, we had to rely on individual's memory and notes. I tried to convert these to posts in the Google Site.

The current site I maintain at work has several pages, some with embedded Google Docs (converted Word, Powerpoint, Excel files). I also used a embedded widget to included an IFrame, so I can display an external web site. Since it's meant to be a collaborative site, I created several pages for various topics, each using "Announcement" template, so several posts can be made to the same page. For e.g., I have PB Wiki, for all posts related to our Powerbuilder development environment. Similarly a "Database Wiki" page for database related postings.

Now that we have site up and running, we need to back it up. I will discuss it in a separate post.

Pinging windows PC behind firewall

If your windows PC (XP, Vista) has firewall turned on, it may not respond to ping from other computers on the network. To allow pinging on this computer, try the following command:

netsh firewall set icmpsetting 8

To disable the same,

netsh firewall set icmpsetting 8 disable

Make sure you open command prompt as an Administrator.

Netsh is the command line utility available in windows command prompt (DOS box) to help with network configuration. See here for more on netsh.

The above settings can also be set in windows firewall settings GUI (screen) in control panel. Surprisingly it's a little more convoluted. See here

Dot... and Dotty

As a developer, one of the things I constantly do is documentation. This is not exactly a favorite task of mine, but I know it's a necessary evil. My programs are typically commented generously. Where necessary I create separate technical documents as well.

In order to make this mundane task a little more interesting, I always try to find tools to help with documentation. For e.g., I found Cppdoc, to document C++ programs, (similar to Javadoc), couple of years ago. While researching on this, I stumbled on Dotty. I found a great tool called GraphViz, but  I didn't do much with it at the time.

Recently when I had a need to create a flowchart for a work flow, again I looked for some tools. I could have used Visio and created some diagrams, but I wanted to automate the process of creating diagrams based on some (text) input. While researching, I re-discovered Dot and Dotty!

DOT and Dotty were originally developed by AT&T Bell Labs. DOT is a plain text graph description language. It's a definition language like CSS.  We can simply type up the information about a graph in human readable Text format, then feed it to a tool to generate a visual graph in various graphic formats. Dotty is a graph editor. AT&T actually bundled dotty and other tools as Graphviz. Graphviz is a collection of tools that interpret Dot structures and generate Graphs in various layouts.

In Dot language, Graphs can be defined as directed (DiGraph) or undirected (Graph). It also defines various elements in a graph such as nodes, arrows/lines or edges, labels etc. The syntax for the language includes various attributes of these elements as well. Thus by combining these elements and their attributes, one can build complex graphs/charts. Examples of such diagrams include, database schema diagrams, network nodes and connections, flow charts etc. The tools are capable of generating the diagrams in various graphic formats including JPG, PNG and SVG. A diagram is created in SVG format can be read by visio and other tools, thus sharing is easier.

The specification for DOT language is simple:

For e.g.,

graph {
a--b--c
}
Will generate

Each circle there is a node and the lines (with or without arrows) are called edges. Each of these have several attributes which can be used to control the presentation and to some extent actual layout.

Below is a simple directional graph (lines with arrows):

digraph{A->B->C->A}

Following line creates a yellow colored oval node:

n[style=filled color="red" fillcolor="yellow"]

The same node as a rectangle:
n[shape=rectangle style=filled color="red" fillcolor="yellow"]



There are several other attributes that can be used to build really complex diagrams. See here for syntax. References listed at the bottom have more details.

The strength of Dot is that the language is small and text-based. Thus, we can write scripts or programs to generate such a text description of a graph. One such application would be to generate graphical calling tree for programs.

Dotty and other tools in Graphviz bundle all have similar command line syntax. A typical usage would be,

 

dot -O -Tpng <example.dot>

I found a great use for this tool, recently. At work, we have sort of a workflow system. The system keeps track of various types of Cases generated by the system or created by the users. Each case type is defined by it's own workflow. To track various steps  in the workflow, we used a Treeview structure in Powerbuilder.While I was doing some research on these flows, I had a need to document the flows in a flowchart. As always, I didn't want to just open Visio and draw a few. I wanted to script it, so it can be generated by text and better yet, automatically from the database with the information we have about the flows!

Since it's so easy to create graphs from the text files, we may even be able to build graphs dynamically, thus could represent state diagrams of running processes.

There are various resources available for DOT and dotty. Dotty is originally written in C. There is a Java API (Grappa) available that wraps around Dotty functions. Apart from Dot, there are also other languages available for representing graphs in text format.

References:

http://www.graphviz.org/Documentation.php

http://wapedia.mobi/en/DOT_language

http://code.google.com/apis/chart/image/docs/gallery/graphviz.html