Obfuscating source code of SQL procedures, SQL functions, and triggers

You can protect the intellectual property of source code for certain types of SQL procedures, SQL functions, and triggers by obfuscating the data definition statements that create them. The obfuscation renders the source code bodies of the SQL functions, SQL procedures, and triggers unreadable, except when decoded by a database server that supports obfuscated statements.

About this task

When you obfuscate data definition statements, the result is the prefix of the statement, including the name and parameter list, the WRAPPED keyword, the eight-byte product identifier string, and the obfuscated body that defines the routine or trigger. The obfuscated text is scrambled so that it cannot be read, except when executed by database servers that support obfuscated statements.

The following limitations apply to the obfuscation of SQL functions, SQL procedures, and triggers:

Obfuscation is supported for only the following data definition statements:
- CREATE FUNCTION (compiled SQL scalar)
- CREATE FUNCTION (inlined SQL scalar)
- CREATE FUNCTION (SQL table)
- CREATE PROCEDURE (SQL - native)
- CREATE TRIGGER (basic)
- CREATE TRIGGER (advanced)
FL 507 If the CREATE PROCEDURE statement contains the OR REPLACE or SPECIFIC clauses, the application compatibility level of the package for the built-in SYSIBMADM.CREATE_WRAPPED procedure must be V12R1M507 (or higher).
You cannot alter obfuscated routines or triggers. However anyone with sufficient authority can drop them and create replacements.
Obfuscated data definition statements are not supported in static SQL statements embedded in host programming languages.
The obfuscation algorithm is not strong encryption, and is not meant for use in security contexts.
Only the body of the SQL text after the name of created object of the obfuscated data definition statement is encoded.
Although SQL statements in the body of obfuscated data definition statement are not readable in the SYSROUTINES or SYSTRIGGERS catalog tables, individual SQL statements are readable in the SYSPACKSTMT catalog table. Only program logic is thoroughly obfuscated.
Obfuscated statements cannot be used in sample programs that automatically convert non-delimited SQL statement text to uppercase. Examples include DSNTEP2, DSNTEP4 and SPUFI.

Procedure

To obfuscate program logic in the body of a routine or trigger in a data definition statement, use any the following approaches:

Call the CREATE_WRAPPED stored procedure to deploy the routine or trigger.

For example, the following CALL statement produces an obfuscated version of a function that computes a yearly salary from an hourly wage, given a 40-hour work week.

CALL CREATE_WRAPPED('CREATE FUNCTION salary(wage DECFLOAT)
RETURNS DECFLOAT
RETURN wage * 40 * 52');
SELECT TEXT FROM SYSIBM.SYSROUTINES
WHERE NAME ='SALARY' AND SCHEMA = CURRENT SCHEMA;

The result of the statement is similar to the following form:

CREATE FUNCTION salary(wage DECFLOAT) WRAPPED DSN12015
ablGWmdiWmtyTmduTmJqTmdKUntiUmdGUodG5nJeYidaWmdaWmdaWmZG1mIa
GicaGC2vNAYS0sYhIem7EL_pIQ29Jt::OXaD8PwazN0O5Q4uZa0R0JfQ1aqaa

Invoke the WRAP built-in function to create the obfuscated form of a statement for use in your application source code.
For example, the following statement returns the obfuscated form of a procedure:
```
SELECT SYSIBMADM.WRAP('create procedure jason.P1 (inout p1 
char(1)) modifies sql data language sql begin SET P1=''A''; end') 
FROM SYSIBM.SYSDUMMY1
```
Notice the that string delimiters inside the wrapped create procedure statement must be escaped, such as by doubling the single quotes in P1=''A''.
The result of the statement is similar to the following form:
```
'create procedure jason.P1 (inout p1 char(1)) WRAPPED DSN12015
ablGWmdiWmtuTmteTmtaTmtmUntmUmdeUmdCYnJGXidaWmdaWmdaWntvUzc
aGicaGx1LCwjGCLzZHV5pcDVYTSnZb0rDLtuJTaIHONQiKxZKbbofdYtby2
3um_5PkE416c1Y2wHLPptCa'
```

What to do next

The obfuscated statements can be used in scripts or batch jobs that can be distributed to users, without exposing the intellectual property that the statements contain.