Sybase IQ assigns internal catalog proc_ids for procedures sequentially and and unused proc_ids are not reused. As procedures are dropped and created, databases created prior to Sybase IQ 12.6 may eventually reach the maximum proc_id limit of 32767, causing CREATE PROCEDURE to return an “Item already exists” error in Sybase IQ 12.6.
For databases created with a version prior to Sybase IQ 12.6 GA, the maximum proc_id for procedures is 32767, even if the database has been upgraded to Sybase IQ 12.6 or higher. This limit does not apply to databases created with Sybase IQ 12.6 and higher.
To determine if your database has a 32767 maximum proc_id
limit, run sp_columns sysprocedure.
If the data type for the proc_id column
is smallint than the maximum proc_id of 32767 applies.To
determine the current maximum proc_id value for your database,
run the following query:
SELECT MAX (proc_id) FROM sys.sysprocedure
Sybase IQ 12.6 ESD7 and higher ensures that, for databases created prior to Sybase IQ 12.6, the maximum proc_id is at a level that allows ALTER DATBASE UPGRADE to complete. If the maximum proc_id is higher, ALTER DATABASE UPGRADE fails and returns the message “Database upgrade not possible”.
To resolve this issue for databases created prior to Sybase IQ 12.6, ALTER DATABASE UPGRADE supports a PROCEDURE ON clause in 12.6 ESD7 and higher that compacts the proc_ids by recreating all stored procedures. The syntax is ALTER DATABASE UPGRADE PROCEDURE ON. The PROCEDURE ON clause is ignored for databases created in 12.6 and higher.
ALTER DATABASE UPGRADE PROCEDURE ON recreates all procedures without comments. If you want the comments back in the procedures after running the command, run ALTER PROCEDURE <procedure_name> with your source code for the procedures that contain comments. The sp_helptext <owner>.<procname> command can be used to save the text of procedures with comments before running ALTER DATABASE UPGRADE PROCEDURE ON.
As a backup, copy the .db and .log files for the database immediately before running ALTER DATABASE UPGRADE PROCEDURE ON. Since only the catalog is modified during an ALTER DATABASE UPGRADE command, a full backup is unnecessary.
