Migrating PL/SQL Scripts to HANA

[Former Member]

Hello Experts,

we are contemplating the idea of migrating a few very exepnsive PL/SQL Scripts to HANA SQL Script. Wanted to check if any of you have experience with this. Please can you share any of your learnings or words of caution as we do this.

Looking forward to guidance,

Sudarshan

[rindia · Accepted solution]

Hi Sudharshan,

You can find the best practices and explanation  in SAP HANA SQLScript reference guide.

For more details, please refer to chapter 9 of version SP6 here.

If you follow those steps you are mostly done.

While scripting if you have any questions, then post it with code so that you can get better response.

Regards

Raj

[Former Member · Accepted solution]

Hello Raj,

Thanks for pointing me to this right chapter.

Thanks

Sudarshan

[former_member9607]

Hi Sudarshan,

I am not sure whether you should do this or not because i don know that how complex your SQL Queries are, But in every DBMS system you will find table/views which will return the queries whether they are Stored Procedure, Triggers any thing.

I will suggest why don't you create a small application in any technology which will read queries from your data base and convert as per the syntax in HANA. let system do the syntax check n all by the logic defined by you.

Hope it makes some sense .

[lbreddemann]

Hey Krishnakant,

Krishnakant Joshi wrote:
I will suggest why don't you create a small application in any technology which will read queries from your data base and convert as per the syntax in HANA. let system do the syntax check n all by the logic defined by you.

Ok, that's the wish-for dream of everybody doing cross-platform development, right?

Take syntax that worked in a specific way on system A apply a conversion function (best would be a 1:1 mapping) and the application is ported.

Easy, done, dusted, right?

Right, except that's not working in real life.

Typically what you get with this approach is code that is hard to read, hard to maintain and that often doesn't do what it used to or should do.

So, to just get some code that compiles and runs the syntax-adaption approach is there - but to get something to move on with, I highly dis-recommend it.

- Lars

[lbreddemann]

HI there,

in my experience the most important part to migrate existing procedural logic from other DBMS to HANA (or any other DBMS) is to actually understand the existing implementation and the requirements it should address.

Just copying the code and adapting the syntax until no error messages are thrown any more by the parser is clearly not sufficient (and unfortunately I've seen projects where this was the "migration approach").

The thing is: as soon as you leave the standard SQL area where all DBMS should basically work the same, you suddenly are faced with the vendor specific limitations and features.

For SAP HANA on very prominent feature is the information models that should be the building blocks of your solution.

Also, the technique to cut down large complex SQL statements into smaller ones and use assignments to table variables that can in turn be used in other SQL statements again is something rather specific to SAP HANA.

What I'm saying is: do re-implementation.

Understand the desired functionality and the current implementation and implement it in your SAP HANA solution.

Don't mess up the architecture of your SAP HANA design by trying to retrofit former PL/SQL design into it.

my 2 ct on this

- Lars

[Former Member]

Hello Lars,

Many thanks for the advise. Infact, the very point we were discussing in our team was to go ahead with the syntax corrections and worry about optimization/performance later. But, the point of view you shared is very useful and we are discussing what are those situations where a straight copy will not work - for eg, are there cases where a legacy query will run a lot slower on HANA than in the original system, are there ways we can influence the extent of parallelizaion during execution.

Thanks again

Sudarshan