1 Overview In the 1970s, with the popularity of large shared database applications, the United States Sybase has developed a Sybase database system based on the database relationship model proposed by the relationship algebraic theory at the time; Sybase is a famous relationship model database based on SQL (Structure Query Language). The system is one of the most popular DBMS software in the world; it can run on a variety of computer hardware platforms, and can be distributed into distributed database environments by connecting the network; SQL Server means running on the host, providing SQL language queries, Modify or control a service process in the database in the database, understand it as a database server; a general database server has two service processes of Data Server and Backup Server. The database is a collection of facts in the objective world described in a series of forms (relationships); the system database in Sybase basically includes: Master, Model, SysProcs, Tempdb, and other user libraries. Sybase Adaptive Server Enterprise 12.5 is a smart database developed by Sybase. Site Sybase Database System Structure 1.1 References "Sybase Database Maintenance Quick Reference Manual Version 1.1", Sybase Company Publish 2 Basics, Configuration and Maintenance 2.1 Installation Sybase 12.5 includes before installation environment configuration, installation process description, successful test, uninstallation, etc.
(For details, please refer to v1.00) 2.2 Access SQL Server In installation Sybase servers, you can start accessing SQL Server to check and control data from SQL Server:?% Isql -u username -s service name Password : // Enter the cipher of this subscriber 2.3 Sybase start and close 2.3.1 Start Sybase database to start Sybaserver commands in $ Sybase / ASE-12_5 / install directory to launch Sybase database, such as% $ Sybase / ASE-12_5 / install / startserver -f run_ service name% $ Sybase / ASE-12_5 / install / startserver -f run_ service name _bakup 2.3.2 Close Sybase database Log in to the Sybase database system in Query Sysservers table Services, such as Isql -usa-Sjoli Password: 1> Select * from sysservers 2> Go generally need to close the service: system backup service, system main service is closed in ISQL prompt first turn off system backup service (default is Syb_Backup), then Close the primary service (default, you can not enter the main service name), such as: 1> Shutdown Syb_backup 2> Go 1> Shutdown 2> Go 2.4 Sybase User Management 2.4.1 Creating a login user (login) sp_addlogin login_name, passwd [, defaultdb [, Deflanguage [, Fullname]]]]: 1> sp_addlogin joli, joli, joli_db 2> Go 2.4.2 Delete Login Example: 1> sp_droplogin Test 2> Go 2.4.3 Creating Database User (User) sp_adduser login_name [ , Name_IN_DB [, GRPNAME]: 1> sp_adduser joli, joli 2> Go 2.4.4 Delete Database User (User) Example: 1> SP_DropUser Test 2> Go 2.4.5 Modify This user is DBO example: 1> Use joli_db 3> sp_changedbowner joli 2.4.6 View Database User Information S p_displaylogin [login_name] sp_helpuser example: 1> sp_helpuser 2> Go 1> sp_displaylog joli 2> Go 1> SP_WHO 2> Go 2.4.7 Modify User Password If you modify the sa password, you can use the SA user to log in, then perform the following command to modify 1> Sp_password "old command | null", "new_password" 2> Go If not the SA user, use the user who wants to modify the password, then execute 1> sp_password old command, new command 2> Go 2.4.8 Modify the system default equipment • Close Create Database Master Default Devices: • 1> Exec sp_diskdefault "Master", "Defaultoff" • Specify user_db_dev for default devices: • 1>
Exec sp_diskdefault "user_db_dev", "defaulton" 2.5 creation, delete, modify Sybase devices, Database 2.5.1 Create device example: 1> Disk init 2> name = "joli_dev", 3> physname = "/ opt / sybase / joli / Joli_Dev.dat ", 4> vdevno = 9, 5> size = 51200 6> GO Description: The physical device name is: joli_dev physical device file path is: /opt/sybase/joli/joli_dev.dat device number (I can't repeat it To: 9 Size (2K) is: 100m (after creating a digital device for the user database, then create a log device separately for the user database) 2.5.2 Mirror Equipment Example: 1> Disk mirror 2> Name = " JOLI_DEV ", 3> mirror =" / opt / sybase / joli / joli_dev_mirror.dat "4> GO Description: Create a mirror of device joli_dev, mirroring mirroring device for /opt/sybase/joli/joli_dev_mirror.dat. Check if a device is imaged, you can use the "sp_helpdevice device name". 2.5.3 Termination Device Mirror Termination Device can be divided into two classes of the primary device and termination equipment, while also selecting a temporary termination or permanent termination. (If it is permanently terminated, you need to delete physical device files on the operating system) Disk unmirror name = "device name" [, side = "{primary | secondary}]" [, mode = {retain | remove}] // termination Main / Sub-devices: Primary / Secondary // Temporary / Permanent Termination: Retain / Remove Examples: 1) Temporary Termination Main Device 1> Disk Unmirror 2> Name = "Joli_Dev", 3> Side = "primary", 4> Mode = retain 5> Go 2) Permanent Entry Master 1> Disk unmirror 2> Name = "joli_log_dev", 3> Side = "primary", 4> mode = remove 5> Go? Recovery Temporary Termination Mirror: Example: 1> Disk Remirror 2> Name = "JOLI_DEV" 3> Go 2.5.4 Deleting devices Need to determine that there is no database on the device. Example: 1> sp_dropDevice test_dev 2> Go Remove the operating system device file. 2.5.5 Viewing device status: 1> sp_helpdevice [device name] 2> GO Description: You can view the device if the device is mirror (related mirror type, whether it has a temporary cancel image, etc.).
2.5.6 Creating Database: 1> CREATE DATABASE JOLI_DB ON JOLI_DEV = 50 LOG ON JOLI_LOG_DEV = 20 2> GO Description: Create User Database JOLI_DB, the database data is stored on the device joli_dev device, can be used 50m; log storage On JOLI_LOG_DEV devices, the size is 20m. 2.5.7 Extended Database: alter database db_name on device_name = size, device_name = size log on device_name = size, device_name = size Examples: 1> alter database joli_db on joli_dev = 10 log on joli_log_dev = 5 2> go Description: Database in joli_db The device number joli_dev extends 10M, and extends 5M on the log device joli_log_dev. 2.5.8 Deleting Database: DROP DATABASE DB_NAME Example: 3> DROP DATABASE JOLI_DB 4> Go 2.5.9 Online Database Example: 1> Online Database DB_NAME 2> Go 2.5.10 Check Database Status? General Check 1> sp_helpdb 2> Go Systemic Check All Library Check: 1> DBCC CHECKDB 2> Go Specify User Database Check: 1> DBCC Checkalloc (joli_db) 2> Go 2.6 Sybase Log Management When you create a user database, you should try to create a separate log for the transaction log. Equipment (typically created 20% of the database size), which can be backed up for the transaction log, preventing the database from being full, and can see the occupation of the transaction log and can be mirroable. Dump Transaction DB_Name with truncate_only // Does not back up the transaction log, direct clearance. DUMP Transaction DB_Name with no log dump Transaction DB_Name to "Path / Name" // Backup Transaction Log? Check LOG Size 1> DBCC CheckTable (Syslogs) 2> Go Quick View LOG Size 1> Select Data_PGS (8, Doa MPG) 2> From sysindexes where id = 8 3> Go 2.7 Modify Sybase system default parameters You can modify $ Sybase / ASE-12_5 directory "service name .cfg" file, restart the Sybase service to modify the Sybase system parameters. You can also use sp_configure to modify some dynamic parameters.
? Query all parameter configuration 1> sp_configure 2> Go? Query can dynamically modify parameters 1> sp_configure "abcdef" 2> Go? Query the configuration of a specific parameter 1> sp_configure "parameter name 2> Go"? Modify Dynamic Configuring parameters 1> sp_configure "Parameter", Value 2> GO General Visual Sight Improvements: Cache Size, Number of Open Databases, Number of Open Objects, Number of Open Indexes, Number of Large I / O Buffers, Number of Devices, DEFAULT NETWORK Packet Size, Max Network Packet Size, Number of Remote Connections, Number of Remote Logins, Number of Worker Processes. 2.8 Modifying the Database Deposit Configure the database you created on your device, you can set the following parameters: Abort Tran on log full, allow nulls by Default, Auto Identity, DBO Use Only, DDL in Tran, Disable Alias, Identity In nounique index, no chkpt on recovery, no free space acctg, read only, select into / bulkcopy / pllsort, single user, trunc log on chkpt, trunc. log on chkpt., unique auto_identity index by sp_dboption [db_name, "option_name", {True | False}] command to modify the configuration of these parameters. Example: 1> Use master 2> Go 1> sp_dboption joli_db, "trunc log on chkpt", TRUE 2> Go Description: Check points, clear the previous log. If set to OFF, keep the log and continue to grow until you use the dump transaction command. 2.9 Network Services Configuration? Unix Under the $ SYBASE directory, the interfaces file provides the user service name. Generally modified by executing DSEDIT. After performing dsedit, according to the prompt, step by step, you can complete the configuration. ? Windows starts dsedit, select Server Object-> add, add a data source to Server, enter IP, port (note, IP address, "," separation, such as 192.168 in its properties "Server Address". .2.98, 4100). 2.10 Checking the Database 1> SELECT @@ version 2> Go 2.11 Database Up Summary Influence Maintenance At the database, it will take over the entire database (default), you can set it offline this suspicious page (system database cannot change).
sp_setsuspect_granularity [dbname "|" page "} [," read_only "]] sp_setsuspect_threshold [dbname [, threshold]] (must have SA_ROLE and in the Master database)? View, modify the database Current offline settings 1) View Set to Database Offering or Page 1> SP_SETSUSPECT_GRANTY JOLI_DB 2> GO 2) Set Database to Pin ... p_setsuspect_granular joli_db, "Page" 3) View the maximum offline page of the settings (the biggest maximum of the default 20 pages, beyond the 20 pages, the same will be offline database) 1> sp_setsuspect_threshold joli_db 2> Go 4) Modify the database to the maximum offline page 300 1> sp_setsuspect_threshold joli_db, 300 2> Go? Display suspicious page: sp_listsuspect_db? Detailed display Database suspicious page information: sp_LISTSUSPECT_PAGE [DBNAME]? Make database all nets online: sp_forceonline_db dbname, {"sa_on" | "limited_users"}? Make the specified page: sp_forceonline_page dbname, pgid {"sa_on" | sa_off "" All_Users "} (// must be sa_sole to execute in Master.) 2.12 Other common maintenance 1) You can perform command $ Sybase / Sybcent32 / ScjView to maintain it using the graphical interface. 2) View the log file (* .log) in the $ SYBASE / ASE-12_5 / Install directory. 3) Use some processes: sp_reportstats, sp_helpdb, sp_helpsegment segment_name (system, log, default) (sp_helpsegment logsegment checks the space available for the log growth), sp_spaceused (sp_spaceused syslogs check the log size), dbcc checkdb & dbcc checkcatalog & dbcc checkalloc, sp_lock Wait; you can also query some system tables: syslogs, master .. sysdevices, master .. sysusages, syssegments, sysindexes, sysdatabases, etc. 4) View the currently started service: showserver. 5)> DBCC TRACEON (3604) Displays the information in the console and writes an ERRORLOG file.
