Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere 10 - Changes and Upgrading » What's New in Version 10.0.0 » SQL Anywhere » Behavior changes

Server option changes Next Page

Catalog changes


The catalog has undergone major changes in version 10.0.0. The most significant change is that system tables have been renamed to include an I at the beginning of their name. If you attempt to access the system tables, you will receive a permission denied error. Information in the system tables is made available through system views. There is one system view per system table, and, for backward compatibility, the system view names coincide with the table names from previous versions of SQL Anywhere. For example, in 9.0.2, there was a system table called SYS.SYSARTICLE. In version 10.0.0 that system table is now called SYS.ISYSARTICLE, and a corresponding system view, SYS.SYSARTICLE.

The catalog now also contains consolidated views. These are views which provide commonly needed joins from two or more tables or views. Most of the consolidated views were present as system views in previous releases.

Some system tables and views have been deprecated or removed from the catalog. In most cases, however, compatibility views are provided.

The following table provides a complete mapping of the catalog from Adaptive Server Anywhere 9.0.2 to SQL Anywhere 10.0.0. The first column, 9.0.2 system table/view, shows names of the 9.0.2 system tables, followed by a forward slash ('/'), and then the name the 9.0.2 associated view(s). The middle column, 10.0.0 system table, contains the 10.0.0 table name. The final column, 10.0.0 system view, contains the associated 10.0.0 view name(s), as well as compatibility notes.

Note

A dash (-) in any of the columns indicates that there is no equivalent object. For example, a new table in the catalog for the 10.0.0 release results in a dash for the table in the 9.0.2 column.

9.0.2 system table/view10.0.0 system table 10.0.0 system view
DUMMY / -DUMMY-
RowGenerator / -RowGenerator-
SYSARTICLE / SYSARTICLESISYSARTICLE

SYSARTICLE system view

For pre-10.0.0 compatibility: SYSARTICLES consolidated view.

SYSARTICLECOL / SYSARTICLECOLISYSARTICLECOL

SYSARTICLECOL system view

For pre-10.0.0 compatibility: SYSARTICLECOLS consolidated view.

SYSATTRIBUTE / - ISYSATTRIBUTE -
SYSATTRIBUTENAME / - ISYSATTRIBUTENAME -
SYSCAPABILITY / SYSCAPABILITIESISYSCAPABILITY

SYSCAPABILITY system view

SYSCAPABILITIES consolidated view

SYSCAPABILITYNAME / -ISYSCAPABILITYNAMESYSCAPABILITYNAME system view
- / SYSCATALOGSYSCATALOG consolidated view
SYSCHECK / -ISYSCHECKSYSCHECK system view
- / SYSCOLAUTH-SYSCOLAUTH consolidated view
SYSCOLLATION / --SYSCOLLATION compatibility view (deprecated)
SYSCOLLATIONMAPPINGS / --SYSCOLLATIONMAPPINGS compatibility view (deprecated)
SYSCOLPERM / -ISYSCOLPERMSYSCOLPERM system view
SYSCOLSTAT / SYSCOLSTATSISYSCOLSTATSYSCOLSTAT system view and SYSCOLSTATS consolidated view
SYSCOLUMN / SYSCOLUMNSISYSTABCOL

SYSTABCOL system viewand SYSCOLUMNS consolidated view

For pre-10.0.0 compatibility:SYSCOLUMN compatibility view (deprecated)

SYSCONSTRAINT / -ISYSCONSTRAINTSYSCONSTRAINT system view
- / - ISYSDEPENDENCYSYSDEPENDENCY system view
SYSDOMAIN / - ISYSDOMAINSYSDOMAIN system view
SYSEVENT / -ISYSEVENTSYSEVENT system view
SYSEVENTTYPE / -ISYSEVENTTYPESYSEVENTTYPE system view
SYSEXTENT / -- -
SYSEXTERNLOGINS / -ISYSEXTERNLOGIN

SYSEXTERNLOGIN system view

SYSFILE / -ISYSFILESYSFILE system view
SYSFKCOL / -ISYSIDXCOL

SYSIDXCOL system view

For pre-10.0.0 compatibility: SYSFKCOL compatibility view (deprecated)

SYSFOREIGNKEY / SYSFOREIGNKEYSISYSFKEY

SYSFKEY system view and SYSFOREIGNKEYS consolidated view.

For pre-10.0.0 compatibility: SYSFOREIGNKEY compatibility view (deprecated)

- / SYSGROUPSISYSGROUP

SYSGROUP system view and SYSGROUPS consolidated view

SYSHISTORY / -ISYSHISTORYSYSHISTORY system view
SYSINDEX / SYSINDEXESISYSIDX

SYSIDX system view and SYSINDEXES consolidated view

For pre-10.0.0 compatibility: SYSINDEX compatibility view (deprecated)

SYSINFO / --SYSINFO compatibility view (deprecated)
SYSIXCOL / -ISYSIDXCOL

SYSIDXCOL system view

For pre-10.0.0 compatibility: SYSIXCOL compatibility view (deprecated)

SYSJAR / -ISYSJARSYSJAR system view
SYSJARCOMPONENT / -ISYSJARCOMPONENTSYSJARCOMPONENT system view
SYSJAVACLASS / -ISYSJAVACLASSSYSJAVACLASS system view
SYSLOGIN / -ISYSLOGINMAPSYSLOGINMAP system view
SYSOPTBLOCK / --system use only
- / -ISYSMVOPTIONSYSMVOPTION system view
- / -ISYSMVOPTIONNAME SYSMVOPTIONNAME system view
- / -ISYSOBJECTSYSOBJECT system view
SYSOPTION / SYSOPTIONSISYSOPTIONSYSOPTION system view and SYSOPTIONS consolidated view
SYSOPTJOINSTRATEGY / SYSOPTJOINSTRATEGIES-system use only
SYSOPTORDER / SYSOPTORDERS-system use only
SYSOPTQUANTIFIER / --system use only
SYSOPTREQUEST / --system use only
SYSOPTREWRITE / --system use only
SYSOPTSTAT / - ISYSOPTSTATSYSOPTSTAT system view
-ISYSPHYSIDXSYSPHYSIDX system view
- / SYSPROCAUTH-SYSPROCAUTH consolidated view
SYSPROCEDURE / SYSPROCEDURESISYSPROCEDURE

SYSPROCEDURE system view

The SYSPROCEDURES view has been renamed to SYSPROCS. See SYSPROCS consolidated view.

SYSPROCPARM / SYSPROCPARMSISYSPROCPARM SYSPROCPARM system view and SYSPROCPARMS consolidated view
SYSPROCPERM / -ISYSPROCPERMSYSPROCPERM system view
-ISYSPROXYTABSYSPROXYTAB system view
SYSPUBLICATION / SYSPUBLICATIONS ISYSPUBLICATION SYSPUBLICATION system view and SYSPUBLICATIONS consolidated view
- / -ISYSREMARKSYSREMARK system view
SYSREMOTEOPTION / SYSREMOTEOPTIONS, SYSREMOTEOPTION2ISYSREMOTEOPTIONSYSREMOTEOPTION system view, SYSREMOTEOPTION2 consolidated view, and SYSREMOTEOPTIONS consolidated view
SYSREMOTEOPTIONTYPE / -ISYSREMOTEOPTIONTYPESYSREMOTEOPTIONTYPE system view
SYSREMOTETYPE / SYSREMOTETYPESISYSREMOTETYPESYSREMOTETYPE system view and SYSREMOTETYPES consolidated view
SYSREMOTEUSER / SYSREMOTEUSERS ISYSREMOTEUSER SYSREMOTEUSER system view and SYSREMOTEUSERS consolidated view
SYSSCHEDULE / - ISYSSCHEDULE SYSSCHEDULE system view
SYSSERVERS / - ISYSSERVER

SYSSERVER system view

- / -ISYSSOURCESYSSOURCE system view
SYSSQLSERVERTYPE / -ISYSSQLSERVERTYPESYSSQLSERVERTYPE system view
SYSSUBSCRIPTION / SYSSUBSCRIPTIONSISYSSUBSCRIPTIONSYSSUBSCRIPTION system view and SYSSUBSCRIPTIONS consolidated view
SYSSYNC / SYSSYNCS, SYSSYNC2ISYSSYNCSYSSYNC system view, SYSSYNC2 consolidated view, and SYSSYNC2 consolidated view
-ISYSSYNCSCRIPTSYSSYNCSCRIPT system view and SYSSYNCSCRIPTS consolidated view
- / SYSSYNCSUBSCRIPTIONS- SYSSYNCSUBSCRIPTIONS consolidated view
- / SYSSYNCUSERS-SYSSYNCUSERS consolidated view
- / SYSTABAUTH-SYSTABAUTH consolidated view
SYSTABLE / -ISYSTAB

SYSTAB system view

For pre-10.0.0 compatibility: SYSTABLE compatibility view (deprecated)

-ISYSTABCOLSYSTABCOL system view
SYSTABLEPERM / -ISYSTABLEPERMSYSTABLEPERM system view
SYSTRIGGER / SYSTRIGGERSISYSTRIGGERSYSTRIGGER system view and SYSTRIGGERS consolidated view
SYSTYPEMAP / -ISYSTYPEMAPSYSTYPEMAP system view
-ISYSUSERSYSUSER system view
- / SYSUSERAUTHISYSUSERAUTHORITYSYSUSERAUTHORITY system view and SYSUSERAUTH compatibility view (deprecated)
- / SYSUSERLISTSYSUSERAUTHORITY system view and SYSUSERLIST compatibility view (deprecated)
SYSUSERMESSAGES / -ISYSUSERMESSAGE SYSUSERMESSAGE system view
- / SYSUSEROPTIONS- SYSUSEROPTIONS consolidated view
SYSUSERPERM / SYSUSERPERMS-

Data now located in the ISYSUSER and ISYSUSERAUTHORITY system tables. See: SYSUSER system view and SYSUSERAUTHORITY system view

For pre-10.0.0 compatibility: SYSUSERPERM compatibility view (deprecated) and SYSUSERPERMS compatibility view (deprecated)

SYSUSERTYPE / -ISYSUSERTYPESYSUSERTYPE system view
- / SYSVIEWSISYSVIEWSYSVIEW system view and SYSVIEWS consolidated view
SYSWEBSERVICE / -ISYSWEBSERVICESYSWEBSERVICE system view
Summary of new views
System view nameLink to more information
SYSDEPENDENCYEach row in the SYSDEPENDENCY system view describes a dependency between two database objects. See SYSDEPENDENCY system view
SYSFKEY Each row in the SYSFKEY system view describes a foreign key constraint in the system. See SYSFKEY system view.
SYSIDXEach row in the SYSIDX system table defines a logical index in the database. See SYSIDX system view.
SYSIDXCOLEach row in the SYSIDXCOL system view describes one column of an index described in the SYSIDX system view. See SYSIDXCOL system view.
SYSLOGINMAPThe SYSLOGINMAP system view contains all the user names that can be used to connect to the database using either an integrated login, or Kerberos login. See SYSLOGINMAP system view.
SYSMVOPTIONEach row in the SYSMVOPTION system view describes the setting of one option value for a materialized view. See SYSMVOPTION system view.
SYSMVOPTIONNAMEEach row in the SYSMVOPTIONNAME system view contains the name of an option defined in the SYSMVOPTION system view. See SYSMVOPTIONNAME system view.
SYSOBJECTEach row in the SYSOBJECT system view describes an object. Examples of database objects include tables, views, columns, indexes, and procedures. See SYSOBJECT system view.
SYSPHYSIDXEach row in the SYSPHYSIDX system view defines a physical index in the database. See SYSPHYSIDX system view.
SYSPROCSThe SYSPROCS system view replaces the former SYSPROCEDURES view. See SYSPROCS consolidated view.
SYSPROXYTABEach row of the SYSPROXYTAB system view describes the remote parameters of one proxy table. See SYSPROXYTAB system view.
SYSREMARK Each row in the SYSREMARK system view describes a remark (or comment) for an object. See SYSREMARK system view.
SYSSOURCE Each row in the SYSSOURCE system view contains the source for an object listed in the ISYSOBJECT system table. See SYSSOURCE system view.
SYSSYNCSCRIPT Each row in the SYSSYNCSCRIPT system view identifies a stored procedure for MobiLink scripted upload. See SYSSYNCSCRIPT system view.
SYSTABCOLThe SYSTABCOL system view contains one row for each column of each table and view in the database. See SYSTABCOL system view.
SYSUSER Each row in the SYSUSER system view describes a user in the database. See SYSUSER system view.
SYSUSERAUTHORITYEach row of SYSUSERAUTHORITY system view describes an authority granted to one user ID. See SYSUSERAUTHORITY system view.
Summary of deprecated tables or views

Following is a list of catalog objects that are deprecated. In most cases, the object was a table in previous versions but is now a compatibility view. Referencing these objects does not result in an error; however, for future compatibility, you are encouraged to change your applications to point to the suggested object(s) instead.

Deprecated table or viewTransition information
SYSCOLLATION system tableCollation mapping information is now stored as database properties. See SYSCOLLATION compatibility view (deprecated).
SYSCOLLATIONMAPPINGS system tableCollation mapping information is now stored as database properties. See SYSCOLLATIONMAPPINGS compatibility view (deprecated).
SYSCOLUMN system tableUse the SYSTABCOL system view instead. See SYSTABCOL system view and SYSCOLUMN compatibility view (deprecated).
SYSFKCOL system tableUse the SYSFKEY system view instead. See SYSFKEY system view and SYSFKCOL compatibility view (deprecated).
SYSFOREIGNKEY system tableUse the SYSFKEY system view instead. See SYSFKEY system view and SYSFOREIGNKEY compatibility view (deprecated).
SYSINDEX system tableUse the SYSIDX system view instead. See SYSIDX system view and SYSINDEX compatibility view (deprecated).
SYSIXCOL system tableUse the SYSIDXCOL system view instead. See SYSIDXCOL system view and SYSIXCOL compatibility view (deprecated).
SYSTABLE system tableUse the SYSTAB system view instead. See SYSTAB system view and SYSTABLE compatibility view (deprecated).
SYSUSERAUTH system viewUse the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERAUTH compatibility view (deprecated).
SYSUSERPERM system tableUse the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERPERM compatibility view (deprecated).
SYSUSERLIST system viewUse the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERLIST compatibility view (deprecated).
SYSUSERPERMS system viewUse the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERPERMS compatibility view (deprecated).
Summary of removed or renamed tables or views

Following is a list of catalog objects that are no longer present in the catalog. Referencing these objects results in an error.

Removed table or viewTransition information
SYSATTRIBUTE system tableUse the SYSTAB and SYSPHYSIDX system views instead. Information about percent free and clustered index is now maintained in the ISYSTAB system table. Information about key values, key distance, leaf pages, and depth is now stored in the ISYSPHYSIDX system table. See SYSTAB system view and SYSPHYSIDX system view.
SYSATTRIBUTENAME system tableUse the SYSIDX and SYSPHYSIDX system views instead. See SYSIDX system view and SYSPHYSIDX system view.
SYSEXTENT system tableThe SYSEXTENT table is no longer available in the catalog in SQL Anywhere version 10.0.0 and higher. This table was previously unused.
SYSEXTERNLOGINSRenamed to SYSEXTERNLOGIN. See SYSEXTERNLOGIN system view.
SYSLOGIN system tableThe SYSLOGIN table has been replaced by the SYSLOGINMAP system view, with some changes. See SYSLOGINMAP system view.
SYSOPTBLOCKThis table was for internal use only.
SYSOPTJOINSTRATEGYThis table was for internal use only.
SYSOPTJOINSTRATEGIESThis view was for internal use only.
SYSOPTORDERThis table was for internal use only.
SYSOPTORDERSThis view was for internal use only.
SYSOPTQUANTIFIERThis table was for internal use only.
SYSOPTREQUESTThis table was for internal use only.
SYSOPTREWRITEThis table was for internal use only.
SYSPROCEDURES viewUse the SYSPROCS consolidated view instead. See SYSPROCS consolidated view.
SYSSERVERSRenamed to SYSSERVER. See SYSSERVER system view.
SYSUSERMESSAGESRenamed to SYSUSERMESSAGE. See SYSUSERMESSAGE system view.
Change to columns in system tables and system views

There have been numerous changes to columns in system tables and views. With the exception of the information below, all of the changes consist of adding new columns, or removing unused columns, neither of which impact your applications.