Use commands to operate sybase databases

Use commands to operate sybase databases

This article consists of four parts: Starting and disabling databases by using commands, creating databases, backing up databases, and exporting database structures.
(1) Start and close the database
& Sup2; UNIX platform:
Log On As a Sybase installation account or as a root user, and run the following command to enter the Sybase account.
# Su-sybase
Start ASE:
$ Cd install or $ cd ASE-12 _? /Install (enter the corresponding subdirectory ,? Is a minor version)
$ Startserver-f RUN_ServerName (ServerName is your server name)
Check ASE run:
$ Cd install or $ cd ASE-12_0/install (go to the appropriate subdirectory)
$ Showserver
Stop ASE:
$ Isql-Usa-P <password>;-S <ServrName>;
Shutdown
Go
& Sup2; windows platform:
Go to the sybase installation directory, $/Sybase/install, and run RUN_Servername.bat. Servername indicates the corresponding host name.

(2) create a database
Two databases are created:
1. Create a database Device
The syntax for creating a database device is:
Disk init
Name = "device_name ",
Physname = "physicalname ",
Vdevno = "virtual_device_number,
Size = number_of_blocks
Go
Take creating a testdb database device as an example:
Disk init
Name = "testdb ",
Physname = "C:/Sybase/data/testdb. dat ",
Vdevno = 3,
Size = 5120
Go
The database device is created with a size of 10 MB.
Note:
& Sup2; vedvno indicates the device Number of the database. when creating the device, modify it according to the database. The command for viewing the vedvno number is sp_helpdevice. You can see the device Number of the database that has been used, the device Number of the newly created database must be greater than the device number that has been used at most.
& Sup2; the command used to check the number of databases supported by the current system is sp_configure "number of devices ".
& Sup2; if the size is set to an hour, 512 indicates the size of 1 MB. The size of the database to be created is proportional to the size of the database. Because disk init divides database devices into 256 2 k allocation units, totaling 1/2 M, 512 means 1 M.
2. Create a database
The syntax for creating a database is:
Create database database_name
[On {default | database_device} [= size]
[, Database_device [= size]…]
[Log on database_device [= size]
[, Database_device [= size]…]
[With override]
[For load]
Go
The data part and log part of the database are created on the same database device. The example database is created:
Create database example
On testdb = 8
Log on testdb = 2
With override
Go
After the example database is created, the database places the data part on the testdb database device and allocates 8 Mb space. The transaction logs are also stored on the testdb database device, allocating 2 MB space.
The data and logs of the database are created on different database devices. The test database is used as an example:
Create database test
On test1 = 8
Log on test2 = 2
Go
After the test database is created, the database stores the data on the test1 database device and allocates 8 Mb space. The transaction logs are also stored on the test2 database device, allocating 2 MB space.
Note:
& Sup2; with override is used to recycle transaction logs. When the space is tight, the transaction log space is automatically reused. Only the data part and the transaction log part can be used on the same database device.
& Sup2; it is recommended that the data parts and transaction logs of the database be created separately on different database devices when the database is created. The following benefits are provided:
· You can back up the transaction log separately.
· Prevent database Overflow
· You can see the usage of the log space. [Dbcc checktable (syslogs)]
· Mirror log Devices

 

(3) backup database
There are two methods to back up a database: BCP and dump. BCP can back up data in the database on different platforms, but BCP can only export data in the database and cannot export the table structure. Dump can export the database structure and data, but cannot be backed up between different platforms. The following sections describe:
1. BCP
You can use the BCP command to export a table at a time or the entire database at a time.
& Sup2; Method for exporting a table at a time
Take zljd_mpqyxx in the nbcredit database as an example.
BCP nbcredit .. zljd_mpqyxx out C:/zljd_mpqyxx-USA-pxxx-sxxx-C
Change out to in during import.
& Sup2; Method for exporting the entire database at one time
Take the nbcredit database as an example.
Create a folder named data_for_dbunder C:/, edit the script.txt file, and save it to C:/. The content is as follows:
Use nbcredit
Go
Select "BCP nbcredit .." + name + "out C:/data_for_db/" + name + "-USA-pxxx-c" from sysobjects where type = "U"
Go
Note: The BCP parameter "-c" indicates the text mode, and "-n" indicates the binary mode.
Run the following command on Windows: iSQL-USA-pxxx-B-I script.txt-O bcpout. bat will see a bcpout in C. execute the bat batch file.
Run the following command on UNIX: iSQL-USA-p-B-I script.txt-O bcpout
Chmod + x bcpout
2. Dump
Take the nbcredit database as an example.
Dump database nbcredit to "D:/nbcredit. dat"
Load database nbcredit from "D:/nbcredit. dat"
Note:
& Sup2; after the database is loaded, you must use the online database nbcredit command before the database can be used.
& Sup2; start the backup server before dumping the database.
& Sup2; if the data volume is larger than 2 GB, use the following method for backup:
Dump database nbcredit to "D:/nbcredit1.bat"
Stripe on "D:/nbcredit2.bat"
Stripe on "d:/nbcredit3.bat"
Go
Use the following method to restore data:
Load database nbcredit from "d:/nbcredit1.dat"
Stripe on "d:/nbcredit2.bat"
Stripe on "d:/nbcredit3.bat"
Go

(4) export the database table structure
When exporting data on different platforms, you can only use the BCP command, but the BCP command cannot export the database table structure. Therefore, you need to export the database table structure.
& Sup2; In sybase12.5 or later, you can use the following method to export the table structure:
Ddlgen-Usa-Pxxx-Ddb_name-Sxxx: port-Ooutput_file
Db_name indicates the name of the database to be exported.

& Sup2; versions earlier than sybase12.5 must execute scripts to export the table structure. The script file is as follows:
Use sybsystemprocs
Go

If object_id ('dbo. sp_ddl_create_table ') is not null
Drop procedure sp_ddl_create_table
Print "Dropping sp_ddl_create_table"
Go

Create proc sp_ddl_create_table
As

-- Creates the DDL for all the user tables in
-- Current database

Select right ('create table' + so1.name + '(' +'
', 255 * (abs (sign (sc1.colid-1)-1) +
Sc1.name + ''+
St1.name + ''+
Substring ('+ rtrim (convert (char, sc1.length) +') ', 1,
Patindex ('% char', st1.name) * 10) +
Substring ('+ rtrim (convert (char, sc1.prec) +', '+ rtrim (
Convert (char, sc1.scale) + ')', 1, patindex ('numeric ', st1.name) * 10) +
Substring ('not null', (convert (INT, convert (bit, (sc1.status & 8) * 4) + 1,
8 * ABS (convert (bit, (sc1.status & 0x80)-1) +
Right ('Identity ', 9 * convert (bit, (sc1.status & 0x80) +
Right (',', 5 * (convert (INT, sc2.colid)-convert (INT, sc1.colid) +
Right (')
'+ 'Go' +'
'+'
', 255 * ABS (sign (convert (INT, sc2.colid)-convert (INT, sc1.colid )))-
1 ))
From sysobjects so1,
Syscolumns sc1,
Syscolumns sc2,
Policypes st1
Where so1.type = 'U'
And sc1.id = so1.id
And st1.usertype = sc1.usertype
And sc2.id = sc1.id
And sc2.colid = (select max (colid)
From syscolumns
Where id = sc1.id)
Order by so1.name, sc1.colid
Go

If object_id ('dbo. sp_ddl_create_table ') is not null
Begin
Grant execute on sp_ddl_create_table to public
Print "Created sp_ddl_create_table"
End
Else
Print "Failed to create sp_ddl_create_table"
Go
The following describes how to view the table structure of a database:
Edit the script file script.txt and save it in c:/. The content is as follows:
Use nbcredit
Go
Sp_ddl_create_table
Go
Run the following command: isql-Usa-Pxxx-B-I script.txt-o scriptout.txt
The content of the scriptout.txt file is the entire database table structure.

(5) Clear logs

When the melinets system is applied, sometimes the system is slow so that you cannot borrow or return books. The reason is that the system log file is slow and you need to clear the log file. The specific operation is as follows:

Step 1: Go to SQL advantage Management in SYSBASE management and log on to the master database with the account "sa.

Step 2: Enter

"Dump transaction melinets with truncate_only" or

"Dump transaction melinets with no_log"

Step 3: run the command. (When "ready" is displayed in the lower left corner of the window, the operation is completed)