Start of change

CREATE_WRAPPED stored procedure

The CREATE_WRAPPED procedure encodes a readable data definition statement into an obfuscated data definition statement and then deploys the object in the database. The procedural logic and embedded SQL statements in an obfuscated data definition statement are scrambled in such a way that any intellectual property in the logic cannot be easily extracted.

Read syntax diagramSkip visual syntax diagram CALL CREATE_WRAPPED ( object-definition-string )

The schema is SYSIBMADM.

object-defintion-string
A string of any built-in character type that contains any of 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)

object-defintion-string must not be bit data, and it cannot contain the VERSION keyword.

The procedure encodes the input into an obfuscated data definition statement string and then dynamically executes that data definition statement. The encoded statement consists of the following parts:

  • The original statement up to and including the routine signature or trigger name.
  • The WRAPPED keyword.
  • The product identifier of the database manager that invoked the function.
    The product identifier (PRDID) value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. In Db2 12 for z/OS®, the modification level indicates a range of function levels:
    • DSN12015 for V12R1M500 or higher.
    • DSN12010 for V12R1M100.
    Start of changeFor more information, see Product identifier (PRDID) values in Db2 for z/OS.End of change
  • The encoded remainder of the data definition statement, in the form of a string of letters (a-z and A-Z), digits (0-9), underscores, and colons. It is converted to Unicode UTF-8 before it is encoded. An error is issued if object-definition-string cannot be converted or if the intermediate string exceeds the maximum length for SQL statements. A warning occurs if a character is converted to the substitution character.

    The encoded data definition statement can be up to one-third longer than the plain text form of the statement. If the result exceeds the maximum length for SQL statements, an error is issued.

Examples

Example 1
The following procedure call 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 is similar to the following form:

CREATE FUNCTION salary(wage DECFLOAT) WRAPPED DSN12015
ablGWmdiWmtyTmduTmJqTmtaUmtCUmZqUmdiXodK3idaWmdaWmdaWmZG1mIaG
icaGy31TyStm_qGbe3sDxdxjtC8ymVGLpMXnuL8lkmNuRhhZ6qYJ2YYdXGaa
Example 2
The following procedure call produces an obfuscated version of a trigger that sets a complex default value 
CALL CREATE_WRAPPED('CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON emp
REFERENCING NEW AS n FOR EACH ROW
WHEN (n.bonus IS NULL)
SET n.bonus = n.salary * .04')
SELECT STATEMENT FROM SYSIBM.SYSTRIGGERS
WHERE NAME='TRIG1' AND SCHEMA= CURRENT SCHEMA;

The result is similar to the following form:

CREATE TRIGGER trig1 WRAPPED DSN12015
ablGWmdiWmtyTmduTmJqTmtaUmtGUnteUmZKWmtqWidaWmdaWmdaXmdyWncaGica
GK6ot_81NzyodncdrRIJFp_tBjpJeIwg_dTKNHcdtHPSaNCpmqBKH2pMwExkRTJW
Zr:dJd0_gSbehW:4Xx1UGPGnDxvmJfa5ZAGOr_1sfFiyaPrkOXzt5UMTmsASfyJR
ksbPfM2dlATbq:0RW
End of change