Additional steps required if you used 12.5.4 features before downgrading

If you are rolling back after having used any of the 12.5.4 features, additional steps can be necessary. You should take steps before you downgrade to pre-12.5.4, and others should be performed immediately after downgrading. Areas of concern are listed for each new feature.

In general, no additional steps are required when you are returning to an Adaptive Server version in which the feature was already available. When returning to a server version earlier than the version in which the feature was introduced, you must be aware of the areas of concern listed below.

Drop all stored procedures, views, and triggers that use the messaging built-ins for the Real-Time Messaging feature.

For more information about Real Time Messaging, see the Real-time Data Services Messaging Users Guide.

Upgrading from 12.5.0.3 to 12.5.4 or later modifies syscharsets. All of the sort orders that were (formerly) destined for use with unichar was rewritten in terms of ID and CSID, although the names remain the same. Other sort orders (that is, any that you loaded with the charset utility or the sqlloc GUI front end) remain unmodified.

While running under 12.5.4, you may configure UTF-8 with a non-binary sort order. If you return to 12.5.0.3 that server fails to start, with a message saying that the character set/sort order combination is not supported. Versions earlier than 12.5.0.3, however, do not have the error handling code; you do not get the message but instead see inexplicable behavior. The most likely symptom is that the server cannot find anything using indexes on the system tables.

To get around this:

• Restart the 12.5.4 server.

• Configure UTF-8 with binary sort order bin_utf8 (ID 50). Do not use any other binary sort order (there are three to choose from); bin_utf8 is the only one known to 12.5.0.3 and earlier servers.

Now when you start 12.5.0.3, syscharsets is still in the 12.5.4 state. If you run sp_helpsort, rewrite syscharsets to a close approximation of what it was before the upgrade.

If you have taken advantage of unicode parser feature through explicit or implicit use of the U&'syntax' (in a stored procedure, for example), the text of the stored procedure is saved in syscomments. If you roll back to 12.5.0.3 and the server needs to recompile from query text, you get syntax errors.

On another note, if you take advantage of the fact that in 12.5.4 unichar no longer requires UTF-8, you may have stored procedure plans that contain unichar-to-char conversions. In 12.5.4 this is handled correctly on a non-UTF-8 server. In 12.5.0.3 and earlier, however, the assumption was UTF-8; anything else causes the conversion to throw an error and abort the transaction.

When you start the 12.5.4 server, the new system types are installed in systypes. If you returned to 12.5.0.3:

• The new types remain in systypes, but server operation is not affected provided you did not use the new types on the 12.5.4 server

• You cannot create using date or time because error checks were added

• If the server encounters date/time tokens, it raises error messages

• If the server encounters a 12.5.4 procedure that accesses a table with date/time types, a stack trace results.

If you returned to a pre-12.5.0.3 server, nothing prevents you from creating objects using date and time types (since no error handling code exists for this purpose), but this is not advisable because any operations on those objects such as selects or inserts result in stack traces.

If you have created 12.5.4 tables with date or time columns, or procedures with date or time parameters, you need to remove references to these types before rolling back to a pre-12.5.4 server. You can run the following, which identifies the offending tables and procedures, advising you to remove references to them or use alter table to modify the types:

sp_date_time_type

If sp_date_time_types does not find any date/time objects, it marks procedural objects for recompile, removing all internal references to these new types

If you used SQL derived tables before returning to a pre-12.5.4 version server, and you:

• Create views with SQL derived tables using version 12.5.4 roll back to ASE 12.5.0.3, then try to select from the view, you get an error that derived tables are not supported in that version.

• Create stored procedures with SQL derived tables in 12.5.4 roll back to ASE 12.5.0.3 or earlier versions and run the stored procedure, you will see an error that the object (SQL derived table) does not exist.

• Create views with SQL derived tables in 12.5.4 return to a pre-12.5.0.3 version, you get stack trace errors.

The XML Services feature of Adaptive Server includes the following new built-in functions and clauses:

• xmlextract built-in function

• xmlparse built-in function

• xmltest predicate

• for xml clause of select commands

If you create views or stored procedures using one of these features in 12.5.4, then return to 12.5.0.3, you get an error that the feature is not supported in that version. If you store XML documents in:

• Their original text form, they can be processed on both Adaptive Server 12.5.4 and ASE 12.5.0.3.

• The parsed form generated by the xmlparse built-in function, then return to 12.5.0.3, the parsed form is not accessible. You must first regenerate the text form of the XML documents in 12.5.4.

For example, if a column named xmlindexed contains data generated in Adaptive Server 12.5.4 by the xmlparse built-in function, you can regenerate the text form of the document in the xmlsource column with the following SQL statement that uses the Adaptive Server 12.5.4 xmlextract built-in function:

update xmltab 
set xmlsource = xmlextract("/", xmlindexed) 

You may then do one of the following:

• Process the xmlsource column directly with the Java-based XQL processor, using the com.sybase.xml.xql.Xql.query Java-based method, or

• Update the xmlindexed column with the parsed form suitable for processing with the Java-based XQL processor, using the following statement:

  update xmltab 
  set xmlindexed = com.sybase.xml.xql.Xql.parse(xmlsource)
  

If you do not want to add the xmlsource column, you can combine these steps with the following SQL statement in ASE 12.5.4:

update xmltab 
set xmlindexed 
= com.sybase.xml.xql.Xql.parse 
(xmlextract("/", xmlindexed))

Prior to execution of this update statement, the xmlindexed column contains the parsed form of the documents generated by the xmlparse 12.5.4 built-in function. After the update statement, that column contains the parsed form of the documents, generated by the com.sybase.xml.xql.Xql.parse Java-based method, suitable for processing in Adaptive Server12.5.0.3 with the com.sybase.xml.xql.Xql.query Java-based method.

A configuration parameter was added to enable XML in 12.5.4. When you first restart with the prior version, you may see error message 5859 advising you that the parameter is unknown. After running installmaster from the pre-12.5.4 version this warning should no longer appear.

If you installed the 12.5.4 automatic database expansion procedures using installdbextend, and then applied the threshold procedure to one or more database segments, the thresholds might not work properly when applied to the log segment after a downgrade to 12.5.0.3.

To clear all auto-expansion thresholds that might exist on one or more segments before downgrading, use the command:

sp_dbextend 'clear', 'threshold' 

Alternatively, you can disable the entire automatic expansion feature server-wide without changing any existing rules or clearing any thresholds. Execute the following commands using sa_role:

use master 
go 
sp_dbextend 'disable', 'database', 'server-wide' 
go

This prevents threshold procedures from doing any work even if they were fired at runtime.

Sybase recommends that you leave all the policies and thresholds in place, and simply disable the entire feature server-wide before the downgrade. This simplifies re-enabling automatic expansion if you return to 12.5.4 later.