Installation guide

10.9.6 How Compiled Objects Are Handled When Upgrading

SAP ASE

SAP ASE upgrades compiled objects based on their source text.

Compiled objects include:

● Check constraints

● Defaults

● Rules

● Stored procedures (including extended stored procedures)

● Triggers

● Views

The source text for each compiled object is stored in the syscomments table, unless it has been manually deleted.

The upgrade process verifies the existence of the source text in syscomments. However, compiled objects are not

actually upgraded until they are invoked.

For example, if you have a user-defined stored procedure named list_proc, the presence of its source text is

verified when you upgrade. The first time list_proc is invoked after the upgrade, SAP ASE detects that the

list_proc compiled object has not been upgraded. SAP ASE recompiles list_proc, based on the source text

in syscomments. The newly compiled object is then executed.

Upgraded objects retain the same object ID and permissions.

You do not receive any notification if the compiled objects in your database dump are missing source text. After

loading a database dump, run sp_checksource to verify the existence of the source text for all compiled objects

in the database. Then, you can allow the compiled objects to be upgraded as they are executed, or you can run

dbcc upgrade_object to find potential problems and upgrade objects manually.

Compiled objects for which the source text has been hidden using sp_hidetext are upgraded in the same

manner as objects for which the source text is not hidden.

For information on sp_checksource and sp_hidetext, see Reference Manual: Procedures.

Note

If you are upgrading from a 32-bit to a 64-bit SAP ASE, the size of each 64-bit compiled object in the

sysprocedures table in each database increases by approximately 55 percent when the object is upgraded.

The preupgrade process calculates the exact size; increase your upgraded database size accordingly.

To determine whether a compiled object has been upgraded when you upgrade to a 64-bit pointer size in the

same version, look at the <sysprocedures.status> column. It contains a hexadecimal bit setting of 0x2 to

indicate that the object uses 64-bit pointers. If this bit is not set, it indicates a 32-bit object, which means it has not

been upgraded.

To ensure that compiled objects have been upgraded successfully before they are invoked, upgrade them

manually using the dbcc upgrade_object command.

Installation Guide for Linux

SAP ASE Upgrades