Application development: Programming Server Applications

Common language run time (CLR) routine execution control modes (EXECUTION 7CONTROL clause)

As a database administrator or application developer, you might want to 7protect the assemblies associated with your DB2 Universal Database (UDB) external routines from unwelcome tampering by restricting the 7actions of routines at run time. DB2 .NET CLR routines support the specification 7of an execution control mode that identifies what types of actions a routine 7is allowed to perform at run time. At run time, DB2 UDB can detect if the routine attempts 7to perform actions beyond the scope of its specified execution control mode, 7which can be helpful when determining whether an assembly has been compromised.

To set the execution control mode of a CLR routine, specify the optional 7EXECUTION CONTROL clause in the CREATE statement for the routine. Valid modes 7are:

7
• SAFE
7
• FILEREAD
7
• FILEWRITE
7
• NETWORK
7
• UNSAFE

To modify the execution control mode in an existing CLR routine, execute 7the ALTER PROCEDURE or ALTER FUNCTION statement.

If the EXECUTION CONTROL clause is not specified for a CLR routine, by 7default the CLR routine runs using the most restrictive execution control 7mode, SAFE. Routines that are created with this execution control mode can 7only access resources that are controlled by the database manager. Less restrictive 7execution control modes allow a routine to access files on the local file 7system (FILEREAD or FILEWRITE) or on the network. The execution control mode 7UNSAFE specifies that no restrictions are to be placed on the behavior of 7the routine. Routines defined with UNSAFE execution control mode can execute 7binary code.

These control modes represent a hierarchy of allowable actions, and a higher-level 7mode includes the actions that are allowed below it in the hierarchy. For 7example, execution control mode NETWORK allows a routine to access files on 7the network, files on the local file system, and resources that are controlled 7by the database manager. Use the most restrictive execution control mode 7possible and avoid using the UNSAFE mode.

If DB2 UDB detects at run time that a CLR routine is attempting an action 7outside of the scope of its execution control mode, DB2 UDB returns an error (SQLSTATE 38501).

The EXECUTION CONTROL clause can only be specified for LANGUAGE CLR routines. 7The scope of applicability of the EXECUTION CONTROL clause is limited to the 7.NET CLR routine itself, and does not extend to any other routines that it 7might call.

Maximum decimal precision and scale in common language run time (CLR) 7routines

The DECIMAL data type in DB2 Universal Database (UDB) is represented 7with a precision of 31 digits and a scale of 28 digits. The .NET CLR System.Decimal 7 data type is limited to a precision of 29 digits and a scale of 28 digits. 7Therefore, DB2 UDB external CLR routines must not assign a value greater than (2^96)-1, 7the highest value that can be represented using a 29 digit precision and a 728 digit scale, to a System.Decimal data type variable. DB2 UDB raises a 7run time error (SQLSTATE 22003, SQLCODE -413) if such an assignment 7occurs.

When a routine CREATE statement is executed, if a DECIMAL data type parameter 7is defined with a scale greater than 28, DB2 UDB raises an error (SQLSTATE 42611, 7SQLCODE -604).