New SQLScript Features in SAP HANA 1.0 SPS9
Semantic Code Completion in SAP HANA Studio
The fact that we have such long names for tables, views and table types in HANA, has been a pain point for many for some time now. To help alleviate this issue, we have built semantic code completion into the SQLScript editor in SAP HANA Studio. Now when a developer needs to do a SELECT against a particular table, he can hit CTRL+SPACE and get a list of tables to choose from. The list is compiled of relevant objects based on the context of the statement, so if you have a SELECT statement and have entered the name of the schema already, and hit CTRL+SPACE, you will only get a listing of tables from that particular schema. This also works when calling procedures, or defining parameters with global table types.
Check out the demo video here.
SQLScript Editor & Debugger in the SAP Web-Development Workbench
Prior to SPS9, there was no way to maintain procedures from the SAP Web-based Development Workbench. You were forced to use the SAP HANA Studio for this. Now as of SPS9, we have a basic SQLScript editor for maintaining .hdbprocedure files. The .procedure file format is not supported here. This editor has basic keyword code hints and syntax highlighting.
Since we can now create procedures from the SAP HANA Web-Based Development Workbench, it makes sense that we should be able to debug them. As of SPS9, we also have a SQLScript procedure debugger as well. Currently, you must set breakpoints in the runtime object in the catalog, and then CALL your procedure from the SQLConsole in order to debug it. We have plans to make it possible to debug a design time artifact directly without having to drop to the runtime object. Within the debugger, you can of course single step through the code, and evaluate input/output parameters as well as intermediate scalar and table variables.
See a demo video here.
Table Type Definitions for Parameters
In previous support packages, we’ve had several different ways to create and reference table types when defining input/output parameters in our procedures. For .procedure file format, we had “local table types” which really were not local, which is why we did not support them in the new .hdbprocedure file format. For .hdbprocedure files, we recommended to create your tables types globally via CDS(.hdbdd file). While I will still recommend to create table types via CDS for global type scenarios, I am pleased to announce that we now have the possibility to declare local table types inline for parameters. In the screen shot below you will see that I have an OUT parameter called EX_PRODUCT_SALE_PRICE which has a table type definition using the keyword TABLE followed by the column list with associated simple types. These type declarations are truly local and cannot be used across procedures. For situations where that you know that your table type will not be reused frequently, it might make sense and be a little easier to simply define the structure inline as opposed to creating it via CDS.
Table Type Definitions for Variables
In previous support packages, intermediate table variables were simply defined by the result set of the data selection, such as a SELECT statement. So whatever columns were in the field list would become the structure of the intermediate table variable. The issue with this approach is that there is some performance cost associated with the type conversions at runtime. Also, this could cause some ambiguousness in the code. As of SPS9, we can now explicitly define the structure of an intermediate table variable from the DECLARE statement. As shown below, I have an intermediate table variable called LT_PRODUCTS which is defined as a TABLE, followed by the column list and associated simple types. This allows the developer to have strict typing within the procedure and avoid any unnecessary performance costs from type conversions.
Another new language features in SPS9, is Autonomous Transactions. The autonomous transaction allows the developer to create an isolated block of code which runs as an independent transaction. This feature is particular helpful when executing logging type tasks. Committed statements inside the autonomous transaction block will be persisted regardless of a rollback of the main transaction. The keywords COMMIT and ROLLBACK are only allowed within the autonomous transaction block and not in the main line of the procedure. If any tables are updated within the main body of the procedure, those tables are not allowed to be accessed from within the autonomous transaction block.
See a demo video of the new language features here. And for more information regarding the SQLScript language itself, please check out the SQLScript Reference Guide.
Use of CE Functions within Procedures & Scripted Calculation Views
Although not specific to SPS9, I’d like to close with some clarification around the use of CE Functions. Calculation Engine(CE) Functions, also known as Plan Operators, are an alternative to writing SQL. At one time, it was recommended to always use CE Functions over SQL in both SQLScript stored procedures as well as scripted calculation views as they performed better than SQL. This is no longer the case. The recommendation moving forward is to use SQL rather than CE Functions within SQLScript. The execution of Calculation Engine Functions currently is bound to processing within the calculation engine and does not allow a possibility to use alternative execution engines, such as L native execution. As most Calculation Engine Functions are converted internally and treated as SQL operations, the conversion requires multiple layers of optimizations. This can be avoided by direct SQL use. Depending on your system configuration and the version you use, mixing Calculation Engine Functions/Plan Operators and SQL can lead to significant performance penalties when compared to plain SQL implementation. Please note that the above described recommendation/behavior only applies to calculation engine functionality exposed by SQLScript. Therefore only SQLScript related artifacts such as procedures, table functions and scripted calculation views are affected.
For a more general overview of all of the developer related features for SAP HANA 1.0 SPS9, please have a look at this blog post.
Awesome tidbit Rich! Particularly looking forward to the autonomous transactions. I've been slapped on the wrist before for using unsupported COMMIT in SPS7 (even though it worked nicely!)
Very good post. Thanks.
Especially love the Semantic Code Completion and autonomous transactions!
Could you clarify a bit on your last paragraph? Specifically:
- Are graphical CVs impacted in any way? I thought graphical CVs were converted into CE functions behind the scenes, wouldn't this be made irrelevant?
- What do you mean with "the L native execution"?
Excellent Question. One could infer from that paragraph that graphical calculation views would continue to use the CE Plan Operators on the back end. I would assume that this in not the case but it would be nice know that it is not. It would also be nice to understand how the optimizer are now working in the context of these new enhancements.
Without going into too much detail on this, graphical calculation views still use CE functions(a superset of what we expose in SQLScript) and are treated differently from optimization and execution perspective and thus are not effected in this scenario.
If I configure the graphical calculation view to execute in the SQL engine, would it then take advantage of the above mentioned optimizations? I am assuming that executing in the SQL Engine would force the graphical calculation view away from CE plan operators and towards more traditional SQL statements.
great post Rich. I am looking forward to testing out the autonomous transactions. I believe we had issues before due to this. I am also pumped about the ability to use SQL code completion. This is absolutely required.
You can absolutely create an RLANG procedure from the repository. Just change the LANGUAGE to RLANG and save and activate.
Yea, I just check .hdbprocedure and I am getting an error there when changing to RLANG. I'll check with development on this.
Got an answer back from development on this. They have opened a bug on this and it will be addressed soon via a revision. Thanks for letting us know.
Yes, if you want to send me your list via email that will be fine. My email should be on my profile.
I was excited to see that with autonomous transactions you are now offering a de-coupling mechanisms for stored procedures. That was dearly needed in light of all the other limitations imposed by the SQL/stored procedure implementation in HANA.
In particular, I had hoped to use it in working around the limitation (bug?), where database triggers cannot alter the table they were triggered from. Alas, (and contrary to the notion of a truly autonomous transaction), I still cannot update the trigger table from within an autonomous transaction inside the trigger body.
Are you guys aware of this issue? When can we expect a fix to be rolled out?
The lifting of the limitation of access to the subject table in the trigger body is being looked at for SP10. Again, it is planned, so nothing definite.
Is AMDP debugging possible?
When i try to put breakpoints in the runtime object , i see error messages in console
(Debugger) Error in setting breakpoint: Setting breakpoints failed since the sql debugger (the own session id) not available..
Yes, I believe that it did work for me when I tested this before. You have to make sure to grant ATTACH DEBUGGER to user as explained here.