There is also one unique entry added when an EXECUTE IMMEDIATE request is found in an application this entry is shared by all other EXECUTE IMMEDIATE requests within the same application. There is one unique section entry for each unique PREPARE, DECLARE CURSORS, or static SQL found in an application. For dynamic SQL statements, the section entry stored with a package is empty and simply acts as a 'bookmark.' It also performs the same function for the state of any associated application cursors. During execution, a section entry contains information about the current status of any section associated with it. For example, for a cursor, the section entry contains the name of the cursor, whether it is a WITH HOLD cursor or not, and whether the cursor was defined FOR UPDATE. A section entry contains information about the SQL statement itself (if any exists) and about the context in which the SQL statement was found in the application. To keep the context of the individual SQL statements clear and to avoid having to provide this information with each request, DB2 UDB subdivides a package into smaller units called section entries. What is a section entry?īecause an application can have many different SQL statements of both a static and dynamic nature, so can a package. They also use a more optimized access path based on current database statistics. Dynamic SQL statements incur a higher startup cost, but do not have to be recompiled when they are reused. This is the most common case with ODBC applications. They are executed under the authorization of the user who bound the package rather than under that of the run-time user.ĭynamic SQL statements are compiled at run time, under the authorization of the run-time user, and must be used when all or part of the SQL statement is generated during application run time. Static SQL statements are pre-compiled and have a pre-existing access path and therefore do not have to be compiled by the DB2 SQL compiler at run time. All of these approaches are supported within DB2 UDB through the use of packages. Packages must exist for the user to be able to execute SQL statements against DB2 UDB.Īn application can be written using pure static SQL, a mix of static and dynamic SQL, or pure dynamic SQL.

This information is reflected in SYSCAT.PACKAGEAUTH. Privileges can be granted and revoked on the package as required to permit people to execute or maintain the package. A package is used by DB2 UDB as a basic authorization control point. A section is the DB2 UDB executable version of a SQL statement. For static SQL statements, a package also has a section associated with it for each statement.

All of this information is stored as an entry representing the package in the SYSCAT.PACKAGES catalog table. Package information includes items such as the optimization level used during compilation, whether blocking is used for eligible cursors during execution, and what degree of parallelism is used during execution. What is a package?Ī package is a cluster of information that controls the compilation of any static SQL statement, partially controls the compilation of any dynamic SQL statement, and influences the execution of any SQL request issued within its scope. A more precise statement of how DB2 UDB processes SQL requests is to say that each individual request is mapped to a specific section entry within a specific package within DB2 UDB. This is the case for application requests such as PREPARE, FETCH, and CLOSE. For a SQL statement to be processed, it must be associated with a DB2 UDB package. When DB2 UDB processes SQL statements, each individual SQL request is mapped to a specific package.

Db2 universal database for study