Start of change

CREATE_WRAPPED

The CREATE_WRAPPED procedure transforms a readable DDL statement into an obfuscated DDL statement and then deploys the object in the database.

Read syntax diagramSkip visual syntax diagramCREATE_WRAPPED( object-definition-string )

In an obfuscated DDL statement, the procedural logic and embedded SQL statements are scrambled in such a way that any intellectual property in the logic cannot be easily extracted.

The schema is SYSIBMADM.

object-definition-string
A string of type CLOB containing a DDL statement. It can be one of the following SQL statements:
  • CREATE FUNCTION (SQL scalar)
  • CREATE FUNCTION (SQL table)
  • CREATE PROCEDURE (SQL)
  • CREATE TRIGGER
The procedure transforms the input into an obfuscated DDL statement string and then dynamically executes that DDL statement. The encoding consists of a prefix of the original statement up to and including the routine signature or trigger name, followed by the keyword WRAPPED. This keyword is followed by information about the application server that invoked the function. The information has the form pppvvrrm where:
  • ppp identifies the product using the following 3 characters:
    • QSQ for Db2® for i
    • SQL for DB2® Database for Linux®, UNIX, and Windows
  • vv is a two-digit version identifier, such as '07'
  • rr is a two-digit release identifier, such as '02'
  • m is a one-character modification level identifier, such as '0'
For example DB2 for i version 7.2 is identified as 'QSQ07020'.

This application server information is followed by a string of letters (a-z and A-Z), digits (0-9), underscores, and colons.

The encoded DDL statement may 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.

Note

The encoding of the statement is meant to obfuscate the content and should not be considered as a form of strong encryption.

Examples

Example 1: Produce 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 ROUTINE_DEFINITION FROM QSYS2.SYSROUTINES 
    WHERE routine_name ='SALARY' AND routine_schema = CURRENT SCHEMA;    

Upon successful completion of the CALL statement, the ROUTINE_DEFINITION column in QSYS2.SYSROUTINES for the row corresponding to routine 'SALARY' would be something of the form:

WRAPPED QSQ07020 <encoded-suffix>

Example 2: Produce an obfuscated version of a trigger that sets a complex default.

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 ACTION_STATEMENT FROM QSYS2.SYSTRIGGERS 
    WHERE trigname ='TRIG1' AND trigschema = CURRENT SCHEMA;     

Upon successful completion of the CALL statement, the ACTION_STATEMENT column in QSYS2.SYSTRIGGERS for the row corresponding to trigger 'TRIG1' would be something of the form:

WRAPPED QSQ07020 <encoded-suffix>
End of change