Skip to Content

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.

 

 

 

For more information on Core Data Services, please see the Core Data Services Reference Guide.

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 ‘ ‘.

For more information on SQLScript, please see the SQLScript Reference Guide.

 

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Rama Shankar

    Rich: is SP2 out already for General Availability to all customer?.

    We are on HANA 20 and are interested in upgrading to HANA 20 SP2.  Please advise. Thanks.

    (0) 
  2. Luis Andres Bersier

    Hi Rich,

    First of all, I’d like to thank you for sharing this knowledge.

    Then, I have a question regarding the encryption. Is there already, or in plans for future development, a way to encrypt also Repository objects, specially graphic column views?

    In my team, we try to use graphic views as much as possible in order to get the maximum optimization for HANA and it’ll be really useful if you also protect those views, and not only SQLScript code.

    Thanks in advance,

    Best.

    –Luis

    (0) 
  3. Patrick Steffens

    Hi Rich,

    thanks for sharing the information. Particularly the description on the rules currently supported was very hepful.

    As part of my master’s thesis, I am investigating possibilities for optimizing existing SQLScript code. The idea is to formalise the best practice descriptions from the SQLScript Reference and provide information on violations in existing code.

    Can custom rules be defined and added? Are you planning to transform the best practices into such rules in near future?

    Best,
    Patrick

    (0) 
  4. Patrick Steffens

    When I try to reproduce the SQLScript Code Analyzer examples, I get the following error:

    SAP DBTech JDBC: [7]: feature not supported: “SYSTEM”.”(DO statement)”: line 6 col 1 (at pos 117): feature not supported: not supported builtin procedure

    I execute the following code via the SQL console from within Eclipse:

    DO
    BEGIN
    
    lt_rules = SELECT rule_namespace, rule_name, category FROM SQLSCRIPT_ANALYZER_RULES;
    
    CALL analyze_sqlscript_definition
        ('CREATE PROCEDURE UNCHECKED_DYNAMIC_SQL(IN query NVARCHAR(500)) AS
        BEGIN
            SELECT rule_namespace, rule_name, category FROM SQLSCRIPT_ANALYZER_RULES;
        END', 
        :lt_rules, 
        lt_findings);
        
    SELECT * FROM :lt_findings;
    END;
    

    Calling other procedures is not a problem, so there must be something odd with this particular call. Do you have an idea what is wrong with it? To me it looks equivalent to the example from the SQLScript reference and in your post.

    (0) 

Leave a Reply