SAP HANA 2.0 SPS 02: 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 02.  We will focus on the database development topic including Core Data Services, as well as SQLScript.

Core Data Services(CDS)

Comments

The first new feature for CDS is the ability to use comments in your CDS source file.  This is done by using the @Comment annotation which can be placed anywhere in your source code.

Column level comments are transferred to the run-time objects in the catalog as well.  So if you were to open a table in Database Explorer, and look at the comments column, you would find the comment for your column.

Select from Table Functions

The next feature for CDS is the ability to select from a table function when defining a view in your CDS artifact.  As you can see in this example, we have defined a view which selects from the BusinessPartner table and does an inner join with a table function called getPartnerRoles

SQLScript

Procedure/Function Encryption

The first new feature for SQLScript is encryption.  Customers and Partners wanting to protect their IP have been asking for this for quite some time.  We use the WITH ENCRYPTION clause in the CREATE statement to flag the procedure or function as encrypted.  If a procedure or a function is created by using the WITH ENCRYPTION option, their definition is saved as an encrypted string that is not human readable. That definition is decrypted only when the procedure or function is compiled. The body in the CREATE statement is masked in various traces, monitoring views and the object definition in the catalog. Decryption of an encrypted procedure is not supported and not even be done by SAP.  Encrypting a procedure or function means that you will also lose supportability features.  For example, you will not be able to debug an encrypted procedure or function, nor will you be able to use PlanViz, traces, or any other supportability tool that can reveal the procedure or functions code.

CREATE OR REPLACE

The next new feature for SQLScript is CREATE OR REPLACE.   When creating a SQLScript procedure or function, you can use the OR REPLACE option to change the defined procedure or function if it already exits. There are some restrictions with this, for example, the signature of the procedure or function can not be changed.  Also, the security mode can not be changed.  Lastly, you can create a new header-only procedure of function, or you can define a body for an existing header-only procedure or function, but you can not replace an existing procedure or function with a header-only procedure of function.

System Variable for Line Number

The next feature is a system variable for line number.  In some cases, a developer requires to know the line number of the statement within a procedure/function which is currently being executed.  You can now use the system variable ::CURRENT_LINE_NUMBER to capture this.

Enhancements to INSERT Operator on Table Variables

In SAP HANA 2.0 SPS 01, we introduced the new table operators for table variables. This included operators such as INSERT, UPDATE, and DELETE.  At the time, when using the INSERT operator, you could only insert 1 row at a time.  In SPS 02, we’ve enhanced the INSERT operator to allow the possibility to insert many rows at one time.  As you can see in this example, we can now insert a set of rows from another table variable into a table variable or export parameter.

Parallel Execute Blocks for SELECT

We have supported parallel execution blocks for some time, but the statements allowed within these blocks were limited to INSERT, UPDATE, and DELETE DML statements.  SELECT was not allowed.  Now in SAP HANA 2.0 SPS 02, we now allow the use of SELECT statements in a parallel execution block.

SLEEP/WAKE Functions

Next, we have the SLEEP/WAKE functionality.  These new built-in procedures are part of the new SYS.SQLSCRIPT_SYNC library.  We are in the process of introducing a new library concept in general and this library is the first to be released for customer use. The plan for the next support packages is to add more and more built-in procedures and functions for customer use.  We also are looking to allow customers/partners to build their own reusable libraries as well.  But for now, we have the SLEEP_SECONDS procedure which allows you to put your current execution to sleep for a given number of seconds without burning CPU, and the WAKEUP_CONNECTION procedure which will wake the sleeping sessions.   In order to use these procedure, you must use the USING clause in your BEGIN statement block as well.

SQLScript Code Analyzer

Finally, we have the SQLScript Code Analyzer.   The SQLScript Code analyzer consists of two built in procedures that scan the course code of procedures and functions.   By applying a selected set of rules, they search for certain patterns that indicate problems regarding code quality, performance, or security.  The rules are defined in the SQLSCRIPT_ANALYZER_RULES table, and currently there are 4 rules.  We do plan to add more rules over time.  The SQLScript Code Analyzer is currently deactivated by default. It can be activated with an ini parameter or a session parameter:

alter system alter configuration ('indexserver.ini','SYSTEM') set

    ('sqlscript', 'enable_code_scanner') = 'true' with reconfigure;

set '__SQLSCRIPT_ENABLE_CODE_SCANNER' = 'true';

Again, there are two procedures which can be used to scan. The first procedure is the ANALYZE_SQLSCRIPT_DEFINTION which is used to analyze the source code of a single procedure or function which has yet to be created.  This procedure expects 2 input parameters, a string which contains the DDL of the procedure or function, and the list of rules to be applied.  It then returns 1 output parameter containing the findings.

The other procedure we can use is ANALYZE_SQLSCRIPT_OBJECTS which is used to analyze the source code of multiple procedures or functions that already exist in the catalog.  This procedure expects 2 input parameters, a list of objects which are to be scanned, and the list of rules.  It then returns 2 output parameters, a list of object which were scanned, and of course the findings.

As mentioned, there are currently 4 rules that are defined.  Below is the list of rules and their description.

• UNNECESSARY_VARIABLE: Each variable is tested if it is used by any output parameter of the procedure or if it influences the outcome of the procedure. Relevant statements for the outcome could be DML statements, implicit result sets, conditions of control statements.
• UNUSED_VARIABLE_VALUE: If a value assigned to a variable is not used in any other statement, the assignment can be removed. In case of default assignments in DECLARE statements, the default is never used.
• UNCHECKED_SQL_INJECTION_SAFETY: Parameters of string type should always be checked for SQL injection safety if they are used in dynamic SQL. This rule checks if for any such parameter the function is_sql_injection_safe was called. For a simple conditional statement like IF is_sql_injection_safe(:var) = 0 THEN…, the control flow in the true branch is checked. The procedure should either end (by returning or by throwing an error) or the unsafe parameter value should be escaped with the functions escape_single_quotes or escape_double_quotes, depending on where the value is used. If the condition is more complex (e.g. more than one variable checked in one condition), a warning will be displayed, as it could only be checked if any execution of the dynamic sql has passed the sql injection check.
• SINGLE_SPACE_LITERAL: This rule searches for string laterals consisting of only one space. If ABAP VARCHAR MODE is used, such string literals are treated as empty strings. In this case CHAR(32) can be used instead of ‘ ‘.