PowerBuilder & HANA: Migrating Data and Business Logic; The tip of the iceberg
Part 05 of a 05 part series
HANA is a hugely powerful DBMS engine, with its own unique scripting language and capabilities. Many businesses could benefit greatly from the real-time analytics and performance boosts when locating all their data in a single in-memory HANA data store. But what is the real effort to migrate to HANA, when applications currently surround another vendor’s DBMS?
While I believe it will take vast HANA knowledge coupled with deep experience with other DBMS’ as well as experience with several migration projects to formulate reasonable metrics (and HANA is currently a rapidly evolving platform), my tiny experience in migrating SSSS from ASA to HANA gave me a little window in the issues. I’ll share with you a couple of my findings.
Data migration is one thing; logic migration is another
As a course developer and instructor I love little applications. They let my students and I get our heads around underlying technology issues without lots of bulk and overhead. I work a lot with new technologies. I spend a lot of time figuring out how new technologies work and how to interface PowerBuilder to them. The last thing I want is to deal with is figuring out the nuances of a use case while dealing with new technologies.
I chose the SSSS application for my HANA proof of concept for several reasons. (1) The app use case is easily graspable by any human being with a mouth and a stomach. (2) I “own” the code and freely share it (3) I designed the apps polymorphic to support a ‘pluggable’ connectivity layer. It already has two connectivity layers, ASA and SOA; adding a third should be a non-issue.
In my simple case, as I showed in a previous article, data migration was straight forward. I needed to be aware of the data types and their storage representation, correctly map between them and import. I was careful not to change data type during migration. For example, I did not change my ANSI Char column into Unicode NVarChar types (Although I did need to change Char into VarChar). Since my goal was not an exhaustive type by type migration and comparison, I was satisfied with the result. See the HANA online reference for more detail on HANA Data Types.
Autoincrement vs. Sequences
Sybase supports generating unique ID numbers by setting an integral column’s default value to autoincrement. Figure 1 shows a table definition. Based on this mechanism, I use a PowerBuilder DataWindow feature called “Identity column” to have the DataWindow engine automatically re-retrieve a newly generated identity number following an insert operation. With the identity number present in the client buffer, data in the DataWindow is positioned for a potential subsequent Update operation. (A DataWindow needs a primary key value to uniquely locate the row to update or delete) Three of the four tables rely on this feature.
SAP HANA SQL has many Oracle like features. (According to Bruce Armstrong, it is deceivingly Oracle-like but with many subtle differences) HANA requires the definition of Sequencers to generate unique key values. While Sequences are a powerful concept, implementing them required changes on both the database side AND the client side. Figure 2 shows the sequencer I created in HANA.
While implementing the sequencer change, I ran into what I perceived as an undocumented and yet unimplemented feature of Before Triggers. Being new to the syntax; I used the online SAP HANA Reference as my coding guide while working in HANA Studio. All I want to do was to get the next number from a Sequencer in a Before Insert trigger and put it into the new row.
When compiling the trigger I get the message “NEW transition variable is not allowed with BEFORE trigger” Huh?!
The doc says “When trigger transition variable is declared, trigger can access records that are being changed by trigger triggering DML. While row level trigger execution, <trans_var_name>.<column_name> represents corresponding column record being changed in trigger”
“You can access new record of DML in trigger, which is to-be-inserted new record or to-be-updated new record. UPDATE trigger and INSERT trigger can have NEW ROW transition variable.”
There is currently no example of using NEW ROW in a BEFORE INSERT trigger in HANA SQL reference guide. I did find Oracle examples on the internet. This example had this straightforward code illustrating exactly what I wanted to do in HANA.
CREATE OR REPLACE TRIGGER orders_before_insert
FOR EACH ROW
— Find username of person performing INSERT into table
SELECT user INTO v_username
— Update create_date field to current system date
:new.create_date := sysdate;
— Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;
In the end I was forced to rethink my client application design. As shown in listing 1 I needed to embed database logic in the client. This is not a clean solution, however, since the application supports multiple databases, it must be database agnostic to the largest degree. For the time being it’s not. Good thing I had the polymorphic service class design shown in figure 3. It provided a convenient clean way to implement the change.
My ASA schema has the little stored procedure shown in listing xx. All it does is return the result of a select statement. You can find the complete syntax for ASA Create Procedure here
I had to make three changes for HANA compatibility. (1) HANA doesn’t support the Result clause. I had to remove it. (2) ASA supports aliased names for columns and expressions, HANA only supports them for expressions (3) In ASA the count( ) function can be used without a * or other parameter value, HANA requires the count( ) function parameter. An especially good thing PowerBuilder DataWindows that worked in my favor is that once the DataWindow object is designed from a result set description, it doesn’t care at runtime about column names, only types and sizes. Therefor the stored procedure result set name change didn’t affect my runtime code. The resulting HANA procedure is shown in listing 3
Here’s a video illustrating the issues:
Here are a couple of related noteworthy observations: (1) Bruce Armstrong, a fellow SAP Mentor and [TeamSybase] member pointed out that HANA does not appear to support an equivalent to Oracle packages. An Oracle based shop currently wanting to migrate to HANA would need to devise a way to migrate package based logic into HANA SQLScript. In researching migration paths to HANA I discovered a database migration tool from http://www.ispirer.com/ which may take code part way. Here’s a YouTube video showing the tool in action
HANA is revolutionizing the way organizations conduct their activities. Organizations supported by PowerBuilder applications can certainly join the revolution by migrating their data to or incorporating their data in HANA datastores. For an experienced developer, connecting the PowerBuilder IDE to HANA and a coding a PowerBuilder application to HANA connection are pretty trivial activities. With that said, using PowerBuilder’s world class RAD tools to build new rich client applications that access tables, views and stored procedure in HANA can help organizations quickly reach their HANA adaption goal of ‘real time business’. Once PowerBuilder 15 delivers its expected OData Data Source, it will be possible to write even more impressive rich client applications that couple tightly with business logic hosted in HANA’s XS server. Business logic living together with data in the HANA server gains from increased performance due to its integration with the data source, which effectively eliminates the overhead of the middle-tier between the user-interface (the view) and the data-intensive control logic.
However, experience gained from my prototype migration points to the greatest challenge being migrating existing applications with their RDBMS specific data logic to HANA. Although some tool supported logic migration is possible, I anticipate that, for the current time, manual intervention will be needed to devise and implement logic work-arounds. That extra degree of effort will surely be application specific and dependent on application design.
Long Live PowerBuilder!
- Review the online reference document detailing syntax to create a HANA sequence, at http://help.sap.com/hana/html/sql_create_sequence.html
- Create 3 sequences in the SSSS schema: 1) order_numbers 2) favorites 3) sandwiches. Since, no doubt, you will be restarting your development server server, specify a restart clause in your statements
- Migrate the stored procedure sp_sandwich_sales from SQL Anywhere to the SSSS schema in HANA
- Review the code in the constructor event and the update( ) function in n_cart_hana and n_favorites_hana
- Review the code in of_connect( ) function in n_connect_hana
- Change the ConnectString value in the SSSS.INI file for both the HANA and DestDBParm keys to point to your ODBC profile and to your uid and pwd.
- Set the value of the LastSandwichID key to the maximum sandwich_id value in the HANA sandwich table (this controls the sandwich pipeline starting point)
- Use the project objects in the three targets to compile and run your three applications