The primary database in the ASE

Source: Internet
Author: User
Tags sybase sybase database

Adaptive Server includes multiple types of databases: required databases, "Additional features" databases, example databases, application databases

1, the required database master database contains system tables, the data stored in these system tables are used for management, and some system tables exist only in the master library, such as Syslogins.sysdatabases Store server-level information

Model database template for creating a new database Sybsystemprocs tables in the database storage process sybsystemdb The data in the database is about the Distributed transaction Management feature of the tempdb database that contains the staging table

Stored Procedure Lookup Order: Current Database "Sybsystemprocs" Master Master: The database contains system tables, the data stored in these system tables is used to manage adaptive Server model: is a template database for new database creation Sybsystemprocs: Contains system stored procedures. As we used to sp_who,sp_lock and so on tempdb: when used to store temporary tables, there are some sorts, distinct aggregation functions, and user temporary tables, all using Sybsystemdb: For distributed transaction management functions

2. The "Additional functions" database consists of three. Sybsyntax: Syntax database. After installation like our SQL. Syntax, can be viewed with stored procedure Sp_syntax, default is not installed DBCCDB: For DBCC CHECKSTORAGE, using parallel processing allows SA, to check database consistency Sybsecurity: Audit database is used for security audits. Allow system administrators to audit user actions, such as some operations, illegal login, etc.

Sybsyntax contains syntax help for SQL keywords, after installing Sybsyntax, users can use Sp_syntax to get syntax help dbccdb contain input and output from DBCC CHECKSTORAGE. Installing the DBCCDB database allows system administrators to use parallel processing commands to check the consistency of the database sybsecurity the database contains audit information. After you install the Sybsecurity database, you can allow the system administrator to audit the user's actions

3, examples of databases such as: PUBS2 and PUBS3 are examples of databases, they are about a fictitious book sales company installed PUBS2 or PUBS3 database, allowing users to practice Transact-SQL commands in a secure and predictable environment

4, application database application database, is user-defined created for the development and production environment. ASE is a multi-library server that can create multiple databases.


Sybase SQL Server is a multi-library-structured RDBMS, and the system database architecture that is automatically generated when you install the Sybase database is roughly as follows:

1) master database Master 2) model database models 3) system process database Sybsystemprocs 4) staging database tempdb. You can also select the following databases: 5) Security Audit Database sybsecurity 6) sample database PUBS2 7) command syntax database Sybsyntax

1. System Database

The database used by the server itself, or the database that manages the server and user databases. Sybase automatically creates four system databases at the time of installation:

Master, model, tempdb, Sybsystemprocs

(1) Master database It is a core database that manages and controls user databases and maintains server uptime, contains many system tables and system processes, controls user databases and SQL Server operations in general, and forms a data dictionary for Sybase systems, which preserves a large amount of system information such as server configuration, users, devices, and so on. A normal user is not allowed to create a database object in the master database, otherwise the transaction log for the master database will quickly become full. If the transaction log is exhausted, you cannot use the DUMP TRANSACTION command to free up space in the master database.

Master database main record information is: 1) login account (SQL Server user name); Syslogins,sysremolelogins 2) the process being executed; sysprocesses 3) modifiable environment variables; sysconfigures 4) system error information; sysmessages 5) information for each database on SQL Server, sysdatabases 6) storage space per database, sysusages 7) tape and tape information installed on the system, sysdevices 8) Activity lock information ; syslocks

(2) Model database It is a template that is provided for creating a user database. It provides the initial type of the new user database. Every time we use the CREATE DATABASE command, SQL Server generates a copy of the model databases as a new user database and extends it to the size required by the command. If we modify the model database, then the newly created database will change with it. The model database contains the system tables required for each user database. The model database can be modified to customize the newly created. Here are a few of the commonly used changes to the model database: Increasing the user database type, rules, or default permissions for users who have access to all databases on SQL Server using Sp_adduser to increase the default privileges on the model database (especially the Guest account) can be established in model Database options such as Select Into/bulkcopy can be set in model. These settings are reflected in all newly created databases. Their most primitive value in the model is off (off). In general, most users do not have permission to modify the model database and are not authorized to read it. Because everything in the model has been copied into the new database, it doesn't make sense to authorize reading the model.

(3) tempdb database It is a temporary database that provides a shared storage area for the server to run and process, such as the intermediate results of group by and order by are stored here. The space for tempdb is shared by all users of all databases in the server. Each time you restart SQL Server, an automated process for the server copies the model database to the tempdb database and clears the original content in tempdb. Therefore, the user tables in tempdb are temporary. Temporary tables are divided into two categories: shareable and non-shareable. A non-shareable temporary table is created before the symbol # is placed in the table name by the CREATE table; a shareable temporary table is prefixed with the table name specified in the CREATE table tempdb. and founded. A non-shareable temporary table SQL Server automatically adds a numeric suffix name to it, and it exists only in the current session.

(4) Sybsystemprocs database It is a database designed to hold system commands (stored procedures), such as sp_help, sp_configure, sp_helpdevice, and so on. When any database user runs a stored procedure that begins with sp_, SQL Server looks in the following order: the current database, the Sybsystemprocs database, and the master database. Other optional databases: (5) Security Audit Database sybsecurity database to save the system security audit information, it can track the operation of each user, to maintain the security of the system to provide a means of control. (6) Sample database PUBS2 provides a database model for users to learn Sybase. (7) Command syntax database sybsyntax the command syntax and supported language libraries for the Sybase database in the library.

2. User Database

The user database is the real purpose of our use of the Sybase server. To manage user data, you must create your own database in Sybase, which refers to the database created with the CREATE DATABASE command. All new user databases are created in the master database, which means that users who cannot access the master database are not authorized to create new databases. The SA can grant other users permission to create a database. There are some system tables in the new database, with at least one record in the sysusers table, both of which are the creators of the database. When the database is created, the creator is the owner of the database, and of course the creator can grant this status or ownership to other users in the system process.

Primary content in a database-database objects: tables, views, temporary tables, indexes, primary keys, foreign keys, defaults, rules, stored procedures, triggers, and so on

The primary database in the ASE

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.