SYBASE 12.5 Maintenance Manual 4. Improve the article

4. Improve the article

If you forget the password for the SA, how to recover it.
1, first stop Sybase ASE service.
2, under the Sybase Security directory%%\sybase\ase-12_5\install\, modify the Run_sybase database server startup script, add-psa Save exit at the end of the start command
3, start the Sybase service with this batch and get a new SA password in the DOS window.
4, log into Sybase with this password, and then modify the sa password.
5, remove the –PSA in the boot script and restart the service, and log on with the new SA password.

4.1 Why the database transaction log is full and the use of the dump Tran with NO_LOG still does not truncate the log. There are two situations in
Where this problem may occur. One is that the application system sends ASE a user-defined transaction that has not been committed, and this first active transaction prevents the system from truncating the log. The second is the client to ASE sent a large number of changes to the transaction, clearing the log, the transaction is still in progress, the office involved in the log can only wait until the end of the transaction can be truncated.
for the first case, the system administrator can clear out the log as long as the user is urged to quit the application or commit the transaction. Because the DUMP transaction with No-log or with Truncate-only is sent as E, it truncates the inactive portion of the transaction log. The inactive part refers to all transactions that have been committed or rolled back between the checkpoint of the server. Transaction log records from the earliest uncommitted transactions to the most recent log records are called active. It can be seen from this that open transactions can cause the log to rise because the log after the earliest active transaction cannot be truncated.
for the second case, the same goes for the same reason. Only when dealing with it, you need to be cautious. If this large transaction has been running for a long time, try to expand the database log space to ensure that the transaction ends normally. If the transaction is forcibly rolled back, ASE needs to do a lot of processing work, often the forward execution time several times, system recovery time is long, may affect the normal use of time.
4.2 How to back up a database with a data volume greater than 2GB
You may experience the following error when backing up a database with a volume greater than 2GB:
o i/oerror:
O operating system Error,server Device/backup/data. Code messages
O file too large.

This is because the size of the backup file exceeds the maximum user file limit for the operating system. While some operating systems do not support files larger than 2GB, you can use Backup server to reserve a database to multiple files.
? Dump database pubs2 to "/USR/SYBASE/PUBS2_DUMP.1"
? Stripe on "/USR/SYBASE/PUBS2_DUMP.2"
? Stripe on "/USR/SYBASE/PUBS2_DUMP.3"
? Go
?
This method can also improve the backup and recovery speed, but note that recovery must also use a corresponding number of devices. For example:
1>load database pubs2 from "/USR/SYBASE/PUBS2_DUMP.1"
2>stripe on "/USR/SYBASE/PUBS2_DUMP.2"
3>stripe on "/USR/SYBASE/PUBS2_DUMP.3"
4>go
4.3 How to change the ASE name (on UNIX, OpenVMS platforms)
There is no specific function in the Sybase product or the stored procedure is used to change the name of ASE server, so only some parameters or configurations can be modified manually to complete this task.

You need to modify the interfaces file, change the Run_server_name file name, and modify its contents, such as the parameters (server name) followed by-S (UNIX),/server (OpenVMS); Change the profile name Change the errorlog file name (if necessary), and if the server name is added to the table sysservers, you need to change the ' srvname ', ' srvnetname ' column to use sp_dropserver, sp_ Addserver stored procedures to implement.

The logical name of the Dslisten in the Run_server_name file is also modified in the OpenVMS system, along with the parameters followed by/server during the use of StartServer. Then, restart the Sql/ase Server. Confirm that the dsquery, dslisten environment variable has changed to the desired content.

Also, if the Sql/ase server is set to a remote server, modify the fields and interfaces files in the system table sysservers associated with this server.

In theory, for example, configuration files, run scripts, and errorlog files do not need to specify the server name (but are usually used to specify the server name). In the Run startup script, specify the server name.

You can complete this task based on the following outline:
? If the server name is added to the table sysservers, delete it with sp_dropserver.
? Shut Down Server
? Edit Interfaces File
? Change the Run_server_name file name and modify its contents, the parameters followed by-S (UNIX),/server (OpenVMS) (server name)
? Change errorlog file name (if required)
? Modify dsquery, Dslisten environment variables (if required)
? Change the profile name (Server_name.cfg, Server_name.bak, server_name.nnn), under the Sybase installation path
? Start Server
? Re-add server using sp_addserver (if required)
? If the server is used as a remote server, it also needs to be deleted and added again.
4.4 How to out more than 2G of data on HP platform BCP
Operating Environment:
HP UNIX11,
SYBASE ASE12.0.02 SWR 9310
SYBASE OPEN CLIENT 11.1.1 EBF8633
SYBASE OPEN CLIENT 12.0

Problem Description:
A backup of this table cannot be completed when a table bcp out of the Sybase database has an error when the output file size reaches 2G. The specific phenomenon is two days for the same large table bcp out output file of the same number of bytes, did not complete all the table bcp out.

Problem Analysis and Solution:
The previous version of the SYBASE OPEN CLIENT 11.1.1 EBF8960 does not support BCP (out, in) over 2G on the HP platform. After the OPEN CLIENT11.1.1 EBF8960 version, you can support BCP (out, in) with a file size above 2G through a "pipe" approach.
The method looks like this:
/usr/sbin/fsadm-f vxfs-o largefiles/dev/...//enable file system to support files larger than 2G
/usr/sbin/mount-f vxfs-o largefiles/dev/...

Mkfifo bcppipe//Build a pipe
bcp tempdb.. Large_table out Bcppipe-usa-p-C &//background run BCP out
Cat bcppipe > bcp.out//redirect bcp out output from directional to support large
File System for files

Implementation steps:
1. Enable file system to support files larger than 2G.
2. Since there are 8 processes for bcp out at the same time in the current bcp backup program, 8 pipeline Bcppipe0–bcppipe7 are required to ensure that sequential writes can be made to separate pipelines in each process.
3. In order not to affect the original other open client applications, decided not to upgrade the current open client 11.1.1, but with open client 12.0
4. Modify the C program that originally called BCP out for batch processing so that it uses open CLIENT 12. The output of the BCP out is first written to the pipe, which is redirected to the file by the pipe.

Results and conclusions:
1. The modified program can correctly bcp out files larger than 2G.
2. Due to the use of piping to do bcp out, performance slightly decreased.
3. Note that BCP in more than 2G of large files, also need to use the way pipeline. So the C program that invokes BCP in is also required to be modified.
4.5 How to migrate ASE server to a system on the same platform (same operating system)
Tips:
? N1 all the steps listed here do not need to be performed sequentially, but indicate the order in which the task was successfully completed in the experiment.
? N2 SYBASE prompts you to keep isql scripts up to today, including login, create DATABASE, DISK INIT, and so on. Using these scripts will make it easier for you to accomplish this task. These scripts can also be regenerated through the contents of the system tables in master, which can be tedious.
? The key to the success of N3 is that the fields of the system tables in the target system are the same as those in the corresponding system tables in the source system. In particular: sysusages, segmap, and size of all rows in syslogins and sysdatabases, ' suid and ' dbid ' Lstart.
On the data source system, do the following:
? Data consistency checks (DBCC) are performed on all databases, and all user databases are backed up.
? Retains the contents of the data table in the master database, using the SELECT * FROM table_name command:
? Sysdevices,sysusages,sysdatabses
? Syslogins,sysservers,syssvrroles,sysloginroles,sysremotelogins
For sysusages tables, use the following command:
SELECT * from sysusages ORDER by Dbid,lstart
For sysdatabases tables, use the following command:
SELECT * from sysdatabases ORDER by dbid
? Use the contents of the system tables listed in the bcp command copy (2).
UNIX:BCP Master. TABLE_NAME out FILE_NAME-USA-PSA_PASSWORD-C
VMS:BCP Master. table_name out file_name/username= "SA"/sa_password/char
? Preserve the execution results of the sp_configure command
On the target system, do the following:
? Install and configure a new ASE and backup Server.
Verify that the master, tempdb, and Sybsystemprocs sizes specified are at least equal to the size of the corresponding database on the data source system, while confirming the same language modules and character sets as the data source system.
? Start ASE so that it is in a normal working state. Refer to the configuration of the data source system (the result of the execution retained in 4) modify the target system configuration to the same, and confirm that the ' device ' parameter value is at least equal to the source system this parameter value.
? In the model, Sybsystemprocs database to perform a number of actions to determine the database work properly. Do not add users, roles, and modify system tables.
? Restart ASE to test that the new configuration is valid.
? Perform the following actions:
1> Use Master
2> Go
1> sp_configure "allow updates", 1
2> Go
Restart ASE.
? Use the contents of the system tables listed in the bcp command copy (2).
UNIX:BCP Master. table_name in File_name-usa-psa_password-b 1-c
VMS:BCP Master. table_name in file_name/user= "SA"/sa_password/char/batch=1
? Build a database device that is at least equal to the size of the corresponding database in the source system.
? Run the script for CREATE DATABASE and ALTER DATABASE (or use the command line). Note that the CREATE, alter order is consistent with the source system create, alter order, and uses the same parameters. When complete, compare the source system with the target system sysdatabases, sysusages, make it exactly the same, otherwise you want to do 12 again this step work. (Please refer to N3 prompt) Note:
? In the 10.0 and later versions, the Segmap field is modified after the database load has been made.
? When determining the need for the same dbid, create, alter, and use the same parameter values in the target system in the same order as create and alter in the source system. This requirement is only used when some objects in the database refer to the objects in different databases. In addition, this requirement is only used in every database, and the result will make the segment, Lastart, size field in the Sysusages table, or fragment the same as the value in the source system. Run the following command to compare the output of the original system:
SELECT * from sysusages ORDER by Lstart
? Load user database and perform DBCC detection.
? Perform the following actions:
1> sp_configure "allow updates", 0
2> Go
Restart ASE.
Backup Master Library and user database.
4.6 How to extend the master database space
The master database can only be extended on master devices. Then when the master device does not have enough space to use, follow these steps:
(This procedure takes the UNIX operating system as an example. Sybase installation path is/sybase)
1. Backup master Database
Start Backup server and enter ISQL environment execution:
1>dump database Master to '/sybase/master.dump '
2>go
Shutdown Sql/ase Server
1>shutdown
2>go
2. Create a new master device that is large enough
$buildmaster-d<master_device>-ssize (size in 2K)
Example: $buildmaster-d/sybase/data/master.dat-s102400
3. Modify the Run_servername file
Edit the Run_server_name file, and the-D argument points to the newly created device name.
4. Single User mode restart server
$startserver-F Run_servername-m
5. Execute Installmaster Script
6. Load master database from backup files
1>load database Master from '/sybase/master.dump '
2>go
7. Modify sysdevices Information
sp_configure ' allow updates ', 1
Go
BEGIN Tran
Go
Update sysdevices Set high = 102399, phyname = ' E:\sybase\data\master_test.dat ' WHERE name = ' master '
Go
(102399=200*512-1 master device size is 200M)
Commit Tran
Go
8. Extend master database
1>alter database master on master device name =size (this value is in m)
2>go
Example: Alter DATABASE master on MASTER=10
Extend the master database on a master device 10M
4.7 Invalid tdslength Value

Error such as:
00:00000:00000:2001/03/22 16:10:07.80 kernel ksmask__rpacket:invalid tdslength value 21536, kpid:1310740
00:00000:00000:2001/03/22 16:10:20.87 kernel ksmask__rpacket:invalid tdslength value 21536, kpid:1376277
00:00000:00000:2001/03/22 16:10:51.27 kernel ksmask__rpacket:invalid tdslength value 21536, kpid:1441814
00:00000:00000:2001/03/22 16:15:38.22 kernel ksmask__rpacket:invalid tdslength value 21536, kpid:1507351
It can be a heavy network traffic.  indicated, this, is a informational message that comes from the network. Possible causes may be:the size of the packet this server has received is different to the size this was sent by the  Client, ksmask__rpacket:the SQL Server does not validate the size of incoming TDS network packets. Bad incoming data could cause waiting processes to hang as the server waits for a very large amount of data on a network Soc Ket. When a corrupt TDS packet has been received due to tcpip sending begining of another TDS packet before finishes T He current TDS packet, the server terminates the process and outputs the above message. Kernel error "Ksmask__rpacket:invalid tdslength" indicates that TCP/IP has sent a corrupt TDS packet to SQLSVR.
Troubleshooting the error:
-max network packet size
-additional Network Memory
-any changes made to network recently

Increasing the network memory could be a solution. I can I would have a situation where there is no memory left so the ' server is ' unable to store the new packets. This could leads to invalid Tdslength. There are some cases where increasing network packet size and the additional network the memory.
In other cases, no further action is required as the message did not re-appear.
can use sp_configure to increase the Max network packet size and additional network, and memory check if also ' s Any issue with the network.
Max network packet size: Depends on the size of the packet that needs to be sent in the application.
Additional Network memory= Max network packet size+ Max network packet size*0.02
(and make this number a multiple of 2048.) )
4.8 How to move a master device from a UNIX file system to a raw partition

1. Turn off Asynch io
1> sp_configure "Allow SQL Server async I/O", 0
2> Go
You'll need to reboot your The server for this to take affect.
2. Mirror the master device to the new raw partition.
1> disk Mirror name = "Master",
2> mirror = "absolute path to new master raw partition"
3> Go
2. Unmirror Master (this'll permanantly break the mirror to the master
Device (If you are want to expirement, can set mode = retain and Remirror
A few times. Reference Manual for syntax).
1> disk Unmirror name = "Master",
2> side = "PRIMARY",
3> mode = Remove
4> Go
3. Shut down your SQL Server. In your run server file, there is a "-D"
Flag that has the path to your old master device. Change this to the path
For the new master device.
4. Restart your server and turn on Asynch io.
1> sp_configure "Allow SQL Server async I/o", 1
2> Go
5. Shutdown and restart your server one to enable the
Asynch io. Verify that your server are using Asynch io by looking at the
Start up sequence in the errorlog.

Note:it is best to perform this task and the server in single user mode.
Can do this while you are recycle your server to disable Asynch io into step 1
Above by adding a "-M" to your run server file. Be sure to remove the flag
When you are finished.
4.9 How to generate a bcp command file (for example, PUBS2)
This paper is adapted to isql 11.*, which can be obtained by isql-v version
? Edit a text file Select.sql, which reads as follows:
SET NOCOUNT ON
Use PUBS2
Go
Select "bcp pubs2 ..." + name + "Out" + Name + ". Bcp-usa-p-C"
From sysobjects where type= "U"
Go
? If it is UNIX, execute:
Isql-usa-p-b-i Select.sql-o Bcpout
chmod +x Bcpout
? If it is windows, execute:
Isql-usa-p-b-i Select.sql-o Bcpout.bat
To replace the out of select.sql with in, repeat the steps to get the command file for bcp in
4.10 How to modify the interfaces file

On some HP and sun machines, information about the server in the interfaces file is stored in 16-in-form and must be modified by the utility DSCP.

In fact, as long as we understand the format of these 16 data, we can change the interfaces file directly through VI.

The following is an example of e3000, which describes the structure and format of the interfaces file:

With VI open/opt/sybase/interfaces, you can see this information:

E3000

Master Tli tcp/dev/tcp \x00021a0a9e4d51f80000000000000000
Query Tli tcp/dev/tcp \x00021a0a9e4d51f80000000000000000

which

/x0002: Reserved words, no need to modify
1a01:16 port number, high on the left, converted to 10 to: 6666
9e4d51f8:16 the incoming host address,
Convert to 10:9e--158
4d--77
51--81
F8--248
That is: 158.77.81.248

The information that can be modified is basically the host address and port number, so if necessary, just modify the information in the format above.
4.11 about the optimization of tempdb
By default, the tempdb database is placed on a master device with a capacity of 2M, and the staging database is the most mundane database that is often used to sort, create temporary tables, reformat, and so on, so the optimization of tempdb deserves special attention.
The first step is to bind the staging database to the cache.
Because temporary tables are created, used, and the staging database uses the data cache frequently, a cache should be created for the staging database so that it can reside in memory and contribute to decentralized I/O:
1. Create a named cache
Sp_cacheconfig "Tempdb_cache", "10m", "mixed"
2. Reboot Server
3, bundled temporary database to Tempdb_cache cache
Sp_bindcache "Tempdb_cache", tempdb
4, if there is a large I/O, configure the memory pool
Step two: Optimize the temp table
The use of most temporary tables is simple and requires little optimization. But requiring complex access to temporary tables should separate the creation and indexing of tables by using multiple procedures or batches. The following two technologies can improve the optimization of temporary tables
1. Create an index on a temporary table
1) temporary table must exist
2 Statistics page must exist (that is, you cannot create an index on an empty table)
2. Spread the complex use of temporary tables across multiple batches or processes to provide information to the optimizer
The following process needs to be optimized:
Create proc Base_proc
As
SELECT * Into #huge_result from auths
SELECT * from article, #huge_result where article.author_code=
#huge_result. Author_code and sex= "0"
Better performance with two processes
1)
Create proc Base_proc
As
SELECT *
Into #huge_result
From Auths
EXEC Select_proc
2)
Create proc Select_proc
As
SELECT * from article, #huge_result
where Article.author_code= #huge_result. Author_code and sex= "0"
Description: In the same stored procedure or batch, when a table is created and used, the query optimizer cannot determine the size of the table.
a useful command for 4.12 ase12.5.x: Disk Resize
Syntax

Disk Resize
Name= ' Device_name ',
Size=additional_space

After using this command,you could alter database on the device that you just resize
To add Dev additional_space. Then We need not use command of ' DISK INIT '.

4.13 How to change the character set to cp936
(where Sybase's installation path is c:\sybase)
1.c:\>cd \sybase\charsets\cp936
2.c :\sybase\charsets\cp936> charset-usa-psa_pass-sserver_name binary.srt cp936
3. In SQL Environment
1>select name,id From Syscharsets
2>go
finds the ID corresponding to name cp936 (assumed to be 117)
4.1>sp_configure "default character Set ID", 117
2 >go
5. Restart Server two times
(note: After the first startup, the server automatically goes down and needs a second reboot before it can be used)