Show TOC

ALTER DATABASE StatementLocate this document in the navigation structure

Upgrades a database created with a previous version of the software, adds or removes jConnect™ for JDBC™ support, or defines management of system procedure execution. Run this statement with DBISQL Interactive SQL.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax
ALTER DATABASE UPGRADE
   [ PROCEDURE ON ]     
   [ JCONNECT { ON | OFF } ]
   [ RESTART { ON | OFF } ]
   [ SYSTEM PROCEDURE AS DEFINER {ON | OFF} ]
Parameters

(back to top)

  • PROCEDURE drops and re-creates all dbo- and sys-owned procedures in the database.
  • JCONNECT to allow the SAP IQ jConnect JDBC driver to access system catalog information, you must specify ON. This installs jConnect system tables and procedures. To exclude the jConnect system objects, specify OFF. You can still use JDBC, as long as you do not access system catalog information. The default is to include jConnect support (JCONNECT ON).
  • RESTART when ON (default) is specified and the AutoStop connection parameter is set to NO, the database restarts after it is upgraded. Otherwise, the database is stopped after an upgrade.
  • SYSTEM PROCEDURE AS DEFINER defines whether a privileged system procedure runs with the privileges of the invoker (the person executing the procedure) or the definer (the owner of the procedure).
    • OFF all privileged system procedures execute with the privileges of the invoker. Use sp_proc_priv() to identify the system privileges required to run a system procedure.
    • ON (default), or not specified
      • when upgrading a pre-16.0 database, pre-16.0 privileged system procedures execute with the privileges of the definer and 16.0 or later privileged system procedures execute with the privileges of the invoker.
      • when upgrading a database that is version 16.0 or later, the default is the behavior of the database being upgraded.
    Note Changing the execution model after upgrade may result in loss of functionality on custom stored procedures and applications that explicitly grant EXECUTE privilege on system procedures. It may also impact the ability to run system procedures. See Reference: Building Blocks, Tables, and Procedures > System Procedures.
Examples

(back to top)

  • Example 1

    disables jConnect support:

    ALTER DATABASE UPGRADE JCONNECT OFF
Usage

(back to top)

The ALTER DATABASE statement upgrades databases created with earlier versions of the software. This applies to maintenance releases as well as major releases.

When you upgrade a database, SAP IQ makes these changes:
  • Upgrades the system tables to the current version.
  • Adds any new database options.
  • Enables new features in the current version.

You can also use ALTER DATABASE UPGRADE simply to add jConnect features, if the database was created with the current version of the software.

Note
  • See the Installation and Configuration Guide for backup recommendations before you upgrade.
  • Be sure to start the server in a way that restricts user connections before you run ALTER DATABASE UPGRADE. For instructions and other upgrade caveats, see the Migration guide for your platform.
  • Use the iqunload utility to upgrade databases created in versions earlier than 15.0. See the Migration guide for your platform.

After using ALTER DATABASE UPGRADE, shut down the database.

Side effects:
  • Automatic commit
Standards

(back to top)

  • SQL—Vendor extension to ISO/ANSI SQL grammar.
  • SAP Database products—Not supported by SAP ASE.
Permissions

(back to top)

Requires the ALTER DATABASE system privilege.

Related Information