![Moving data in one endian format to a system with a different endian format [CR 445754]](arrow-left.gif){kind=icon}
![Restrict connections during upgrades [CR 419328]](arrow-right.gif){kind=icon}
The catalog proc_ids (procedure identifiers) are assigned sequentially and unused proc_ids are not reused. Over time, as procedures are dropped and created, databases created prior to IQ 12.6 may eventually reach the maximum proc_id limit of 32767, causing CREATE PROCEDURE to return an “Item already exists” error in IQ 12.6.
For databases created with a version prior to IQ 12.6 GA, the maximum proc_id for procedures is 32767, even if the database has been upgraded to IQ 12.6 or higher. This limit does not apply to databases created with IQ 12.6 and higher.
To determine if your database has a 32767 maximum proc_id limit, you can run sp_columns sysprocedure. If the data type for the proc_id column is smallint, the maximum proc_id of 32767 applies. To determine the current maximum proc_id value in use for your database, run the following query:
SELECT MAX(proc_id) FROM sys.sysprocedure
In IQ 12.6 ESD #7 and higher a check has been added to ensure that for databases created prior to IQ 12.6, the maximum proc_id is at a level that allows ALTER DATBASE UPGRADE to complete. If the maximum proc_id is above that level, then ALTER DATABASE UPGRADE does not run and instead returns the message “Database upgrade not possible”.
To resolve this issue for databases created prior to IQ 12.6, the ALTER DATABASE UPGRADE command supports a PROCEDURE ON clause in 12.6 ESD #7 and higher which will compact 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 later releases.
Please note that using ALTER DATABASE UPGRADE PROCEDURE ON recreates all procedures without comments. If you want the comments back in the procedures after running the command, you must 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
Sybase recommends that you make a copy of the .DB and .LOG files for the database immediately before running ALTER DATABASE UPGRADE PROCEDURE ON as a backup. Because only the catalog is modified during an ALTER DATABASE UPGRADE command, a full backup is unnecessary.
