Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Upgrading Backup Server, Monitor Server, and XP Server [Table of Contents] Recovering from a failed upgrade

Installation Guide Adaptive Server Enterprise for Linux/Intel

[-] Chapter 8 Upgrading Sybase Servers
[-] Upgrading compiled objects with dbcc upgrade_object

Upgrading compiled objects with dbcc upgrade_object

Adaptive Server version 11.9.3 introduced the process of upgrading compiled objects based on their source text. Compiled objects are:

The source text of each compiled object is stored in the syscomments table, unless it has been manually deleted. When you upgrade the server, the existence of the source text in syscomments is verified during that process. However, the compiled objects are not actually upgraded until they are invoked.

For example, say you have a user-defined stored procedure named list_proc. The presence of source text for list_proc is verified when you upgrade to Adaptive Server 12.5. Then, the first time list_proc is invoked after the upgrade, Adaptive Server detects that the list_proc compiled object has not been upgraded. Adaptive Server 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 that they used prior to being upgraded.

Compiled objects for which the source text was hidden using sp_hidetext are upgraded like objects for which the source text is not hidden. For information on sp_hidetext, see the Reference Manual.

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 pre-upgrade calculates the exact size. Be sure to increase your upgraded database size accordingly.

To ensure that compiled objects have been upgraded successfully before they are invoked, you can upgrade them manually using the dbcc upgrade_object command. For details, see "Finding compiled object errors before production."

Finding compiled object errors before production

Changes made in previous releases of Adaptive Server may cause compiled objects to work differently in version 12.5 and later. You can use dbcc upgrade_object to find the following errors and potential problem areas that may require manual changes to achieve the correct behavior:

After reviewing the errors and potential problem areas, and fixing those that need to be changed, you can use dbcc upgrade_object to upgrade compiled objects manually instead of waiting for the server to upgrade the objects automatically. For details, see "Using dbcc upgrade_object".

Reserved word errors

If dbcc upgrade_object finds a reserved word used as an object name in a compiled object, it returns an error, and the upgrade of that object fails. To fix the error, either manually change the object name or use quotes around the object name and issue the command set quoted identifiers on. Then, drop and recreate the compiled object.

For example, suppose you load a database dump from Adaptive Server 11.5 into Adaptive Server 12.5 and the dump contains a stored procedure that uses the word "lock." When you run dbcc upgrade_object on that stored procedure, the command returns an error because, although "lock" was not reserved in version 11.5, it became a reserved word in version 11.9.2. With this advance notice, you can change the stored procedure and any related tables before they are used in a production environment.

Missing, truncated, or corrupted source text

If the source text in syscomments was deleted, truncated, or otherwise corrupted, dbcc upgrade_object may report syntax errors. If the source text was not hidden, you can use sp_helptext to verify the completeness of the source text. If truncation or other corruption has occurred, drop and re-create the compiled object.

Quoted identifier errors

dbcc upgrade_object returns a quoted identifier error if:

To avoid this error, activate quoted identifiers before running dbcc upgrade_object. When quoted identifiers are active, you must use single quotes instead of double quotes around quoted dbcc upgrade_object keywords.

If quoted identifier errors occur, use the set command to activate quoted identifiers, and then run dbcc upgrade_object to upgrade the object.

For compiled objects created in 11.9.2 or later, the upgrade process automatically activates or deactivates quoted identifiers as appropriate.

Quoted identifiers are not the same as literals enclosed in double quotes. The latter do not require you to perform any special action before the upgrade.

Temporary table references

If a compiled object such as a stored procedure or trigger refers to a temporary table (#temp table_name), which was created outside the body of the object, the upgrade fails, and dbcc upgrade_object returns an error. To correct this error, create the temporary table exactly as expected by the compiled object, and then execute dbcc upgrade_object again. This is not necessary if the compiled object is upgraded automatically when it is invoked.

select * potential problem areas

In Adaptive Server version 11.9.3 and later, the results of a select * clause in a stored procedure, trigger, or view that was created in an earlier version of Adaptive Server may be different from what you expect.

For more information about the changes, see the Reference Manual.

If dbcc upgrade_object finds a select * clause in the outermost query block of a stored procedure, it returns an error, and does not upgrade the object.

For example, if a stored procedure has the following statements: 

create procedure myproc as
  select * from employees
go
create procedure yourproc as
  if exists (select * from employees)
    print "Found one!"
go

dbcc upgrade_object returns an error on myproc because myproc includes a statement with a select * clause in the outermost query block. This procedure is not upgraded.

dbcc upgrade_object does not return an error on yourproc because the select * clause occurs in a subquery. This procedure is upgraded.

Determining whether select * should be changed in views

If dbcc upgrade_object reports the existence of select * in a view, compare the output of syscolumns for the original view to the output of the table, to determine whether columns have been added to or deleted from the table since the view was created.

For example, suppose you have the following statement: 

create view all_emps as select * from employees

Before upgrading the all_emps view, use the following queries to determine the number of columns in the original view and the number of columns in the updated table: 

select name from syscolumns 
  where id = object_id("all_emps")
select name from syscolumns 
  where id = object_id("employees")

Compare the output of the two queries. If the table contains more columns than the view, and retaining the pre-upgrade results of the select * statement is important, change the select * statement to a select statement with specific column names. If the view was created from multiple tables, check the columns in all tables that comprise the view and rewrite the select statement if necessary.

Warning!

Do not execute a select * statement from the view. Doing so will upgrade the view and the information about the original column information in syscolumns will be overwritten.

Another way to determine the difference between the columns in the view and in the new tables is to run sp_help on both the view and the tables that comprise the view.

This comparison works only for views, not for other compiled objects. To determine whether select * statements in other compiled objects need to be revised, review the source text of each compiled object.

Using dbcc upgrade_object

Syntax

dbcc upgrade_object [ ( dbid | dbname [, ['database.[owner].]compiled_object_name' | 
 'check' | 'default' | 'procedure' | 'rule' | 
 'trigger' | 'view' 
 [, 'force' ] ] ) ]

where:

If set quoted identifiers is on, use single quotes around the keywords. If set quoted identifiers is off, you can use either double quotes or single quotes.

Examples

dbcc upgrade_object

Upgrades all compiled objects in the active database.

dbcc upgrade_object(listdb, 'procedure')

Upgrades all stored procedures in the listdb database. Single quotes are used around procedure because set quoted identifiers is on.

dbcc upgrade_object(listdb, "rule")

Upgrades all rules and check constraints in the listdb database. Double quotes are used around rule because set quoted identifiers is off.

dbcc upgrade_object(listdb, list_proc)

Upgrades all stored procedures named list_proc in the listdb database.

dbcc upgrade_object(listdb, 
  "listdb.jkarrik.list_proc")

Upgrades the stored procedure list_proc, which is owned by the login "jkarrik".

dbcc upgrade_object(master, 
  "listdb.jkarrik.list_proc")

Returns an error because the value of dbname is master and the value of database is listdb. These values must match.

Permissions

Only the Database Owner or a System Administrator can execute dbcc upgrade_object. The Database Owner can upgrade his or her own objects in the database.

Upgraded objects retain the same owner that they had prior to being upgraded.

Increasing the log segment size

You can specify that all compiled objects of a particular class should be upgraded in one execution of dbcc upgrade_object; for example, you can upgrade all triggers by using the trigger keyword. However, even though you use only one dbcc command, the upgrade of each object is recorded in a separate transaction; the old row is deleted from sysprocedures and a new row is written. Therefore, if you run dbcc upgrade_object on a large number of compiled objects, your system may run out of log space. Be sure to increase the size of the log segment in the databases in which you plan to run this command, to allow sufficient room to log all the upgrades.

Error reporting

To send all the output from dbcc upgrade_object to the screen, a System Administrator can start the server with dbcc traceon(3604). Using this command is recommended if you think the output of error messages might overflow the error log.

Upgrading compiled objects in database dumps

When you load a database dump that was created in an earlier version than the current Adaptive Server, you are not required to perform the pre-upgrade tasks before loading the dump. Therefore, you will not receive any notification if the compiled objects in your database dump are missing their 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.

For information on using sp_checksource, see the Reference Manual.

Determining whether a compiled object has been upgraded

To determine whether a compiled object has been upgraded, do one of the following:

Upgrading Backup Server, Monitor Server, and XP Server [Table of Contents] Recovering from a failed upgrade