SAP HANA 2.0 SPS 01: New Developer Features; Database Development
In this blog, I would like to introduce you to the new features for the database developer in SAP HANA 2.0 SPS 01. We will focus more on the database development topic including Core Data Services, as well as SQLScript. If you are interested in the new features for XS Advanced(XSA), or the SAP WebIDE for SAP HANA, please check out this blog by my colleague Thomas Jung.
Core Data Services(CDS)
Multiple Top Level Artifacts
The first new feature for CDS is the ability to have multiple top level artifacts within the CDS(.hdbcds) file. Up till now, CDS files could contain only a single top level artifact, which was one context which had to have the same name as the CDS source file itself. All other artifacts would have to be contained within that single context. This forced the runtime objects to contain this context name as well. Now that multiple top level artifacts are allowed, there is no longer any requirement that the name of the CDS source file must be the same as the top level artifact within. You can choose any name for the CDS source file. This also means that you can have many contexts at the top level, or no contexts at all. You could simply have your entity and view definitions at the top level. This means during deployment, the runtime objects(tables and views) only have the name which was defined in the entity and view definitions and are not required to have any context name associated with it, making the table and view names shorter. So in the example below, we have 1 type, 1 entity, and 3 contexts, all at the top level. So for the table1 entity, the runtime object which is created in the catalog would be “table1” and could not contain any context in its name.
TOP in Queries
TOP has been supported by SQL in SAP HANA for quite some time, but now in SAP HANA 2.0 SPS 01, we can now use it within the context of CDS when defining views.
View with Parameters
CDS Views can now have parameters. These parameters are defined in the view definition before the query block and must be typed with built-in primitive, user defined scalar types or defined with the “Type of” operator. Parameters can be used at any position in a query where an expression is allowed. A parameter is referred to inside a query by prefixing the parameter name with either the scope-operator ‘:’ or “$parameters”. During deployment, a table function is generated as the runtime object, not a view. Take the example below, we have a view definition using the WITH PARAMETERS clause, which defines two parameters for PRODUCTID and GROSSAMOUNT. We then reference these parameters in the WHERE clause of the query.
When consuming via a SELECT statement, we can simply pass the values for the parameters as shown.
New View – SYS.CDS_ANNOTATION_ASSIGNMENTS
There is a new view called SYS.CDS_ANNOTATION_ASSIGNMENTS in the CDS catalog API which provides annotation values in a flat manner. This is in contrast to the view CDS_ANNOTATION_VALUES, where the values of structured annotations are returned in a structured form as a JSON value. Having the data as tabular is much easier to read. Take the following example CDS artifact, where there are two annotations here. You would then see these in the new view as tabular data.
For more information on Core Data Services, please see the Core Data Services Reference Guide.
Insert/Update/Delete Operators on Table Variables
The first new feature for SQLScript is the new Insert/Update/Delete operators for table variables. This allows you to execute DML-like statements on intermediate table variables without involving the SQL layer by using the <table_variable>.<operator> syntax. This provides better performance over using index-based cell access or arrays. In the example below, you can see how we can use the three operators. First, we are selecting from the “Products” table and returning the results into the output table parameter called ex_products. We then insert 3 new rows into ex_products using the INSERT operator. Next, we loop through the ex_products table variable and use the UPDATE operator to increase the PRICE column. Finally, we loop through the ex_products table again to determine if the price is less than some value, and if so, add that row index to an array. This array is then used to delete the selected rows in the next statement using the DELETE operator.
Dynamic SQL with In/Out Scalar Parameters
We’ve supported dynamic SQL(EXEC and EXECUTE IMMEDIATE) for quite some time, but there has always been the problem that the results of SELECT statements executed within would be lost and there was no way to work with the result set any further. As of SAP HANA 2.0, SPS 01 we now have the ability to pass scalar parameters to the dynamic statement as well as return a scalar result in the case where a SELECT INTO :VAR statement is executed. In the example below, you can see we are leveraging the EXECUTE IMMEDIATE statement and building a SELECT statement dynamically using variables for the table and column names. We are also passing a scalar parameter :lv_multiplier by leveraging the USING clause. Finally, we can return the result of the SELECT statement by using the INTO clause.
SET SESSION Enhancement – Support Key as Variable
Global Session Variables can be used in SQLScript to share a scalar value between procedure and function that are running in the same session. This has been supported for several support packages. Until SAP HANA 2.0 SPS 01, the key value must be a constant string. Now there is the ability to use a variable in place of a string literal.
Support of LOB Type in Scalar UDF
As of SAP HANA 2.0 SPS01, LOB Types(CLOB, BLOB, NCLOB) are now supported for input and output parameters for scalar user defined functions.
Explain Plan for CALL
We’ve had the ability to do EXPLAIN PLAN on SQL statements for quite some time, but now as of SAP HANA 2.0 SPS 01, we have the ability to execute on stored procedures as well. EXPLAIN PLAN provides information about the compiled plan of a given procedure. It inserts each information into system global temporary table named EXPLAIN_CALL_PLANS, which is visible only within a session.
For more information on SQLScript, please see the SQLScript Reference Guide.