How Compiled Objects Are Handled When Upgrading SAP ASE

SAP ASE upgrades compiled objects based on their source text.

Compiled objects include:

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.

Related tasks
Postupgrade Tasks
Restoring Functionality in SAP ASE After Upgrading