The maximum number of result sets that can be returned by a common language runtime (CLR) procedure is limited. The limit is determined by the maximum number of DB2DataReader objects that the DB2 .NET data provider can simultaneously support having open within a connection. Concurrent active data reader support enables multiple DB2DataReader objects to be opened within a connection. Therefore multiple result sets can be returned from a CLR procedure.
As a database administrator or application developer, you might want to protect the assemblies associated with your DB2 Universal Database(TM) (UDB) external routines from unwelcome tampering by restricting the actions of routines at runtime. DB2(R) .NET CLR routines support the specification of an execution control mode that identifies what types of actions a routine is allowed to perform at runtime. At runtime, DB2 UDB can detect if the routine attempts to perform actions beyond the scope of its specified execution control mode, which can be helpful when determining whether an assembly has been compromised.
To set the execution control mode of a CLR routine, specify the optional EXECUTION CONTROL clause in the CREATE statement for the routine. Valid modes are:
To modify the execution control mode in an existing CLR routine, execute the ALTER PROCEDURE or ALTER FUNCTION statement.
If the EXECUTION CONTROL clause is not specified for a CLR routine, by default the CLR routine runs using the most restrictive execution control mode, SAFE. Routines that are created with this execution control mode can only access resources that are controlled by the database manager. Less restrictive execution control modes allow a routine to access files on the local file system (FILEREAD or FILEWRITE) or on the network. The execution control mode UNSAFE specifies that no restrictions are to be placed on the behavior of the routine. Routines defined with UNSAFE execution control mode can execute binary code.
These control modes represent a hierarchy of allowable actions, and a higher-level mode includes the actions that are allowed below it in the hierarchy. For example, execution control mode NETWORK allows a routine to access files on the network, files on the local file system, and resources that are controlled by the database manager. Use the most restrictive execution control mode possible and avoid using the UNSAFE mode.
If DB2 UDB detects at runtime that a CLR routine is attempting an action outside 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. The scope of applicability of the EXECUTION CONTROL clause is limited to the .NET CLR routine itself, and does not extend to any other routines that it might call.
The DECIMAL data type in DB2 Universal Database (UDB) is represented with a precision of 31 digits and a scale of 28 digits. The .NET CLR System.Decimal data type is limited to a precision of 29 digits and a scale of 28 digits. Therefore, DB2 UDB external CLR routines must not assign a value greater than (2^96)-1, the highest value that can be represented using a 29 digit precision and a 28 digit scale, to a System.Decimal data type variable. DB2 UDB raises a runtime error (SQLSTATE 22003, SQLCODE -413) if such an assignment occurs.
When a routine CREATE statement is executed, if a DECIMAL data type parameter is defined with a scale greater than 28, DB2 UDB raises an error (SQLSTATE 42611, SQLCODE -604).
[ Top of Page |Previous Page | Next Page | Contents ]