Партнёры:
Хостинг:
Archive-name: databases/sybase-faq/part6
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/01/17
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.
Advanced ASE Administration
1.3.1 How do I clear a log suspend'd connection?
1.3.2 What's the best value for cschedspins?
1.3.3 What traceflags are available?
1.3.4 How do I use traceflags 5101 and 5102?
1.3.5 What is cmaxpktsz good for?
1.3.6 What do all the parameters of a buildmaster -d<device> -yall mean?
1.3.7 What is CIS and how do I use it?
1.3.8 If the master device is full how do I make the master database
bigger?
1.3.9 How do I run multiple versions of Sybase on the same server?
General Troubleshooting User Database Administration ASE FAQ
-------------------------------------------------------------------------------
1.3.1 How to clear a log suspend
-------------------------------------------------------------------------------
A connection that is in a log suspend state is there because the transaction
that it was performing couldn't be logged. The reason it couldn't be logged is
because the database transaction log is full. Typically, the connection that
caused the log to fill is the one suspended. We'll get to that later.
In order to clear the problem you must dump the transaction log. This can be
done as follows:
dump tran db_name to data_device
go
At this point, any completed transactions will be flushed out to disk. If you
don't care about the recoverability of the database, you can issue the
following command:
dump tran db_name with truncate_only
If that doesn't work, you can use the with no_log option instead of the with
truncate_only.
After successfully clearing the log the suspended connection(s) will resume.
Unfortunately, as mentioned above, there is the situation where the connection
that is suspended is the culprit that filled the log. Remember that dumping the
log only clears out completed transaction. If the connection filled the log
with one large transaction, then dumping the log isn't going to clear the
suspension.
System 10
What you need to do is issue an ASE kill command on the connection and then
unsuspend it:
select lct_admin("unsuspend", db_id("db_name"))
System 11
See Sybase Technical News Volume 6, Number 2
Retaining Pre-System 10 Behavior
By setting a database's abort xact on log full option, pre-System 10 behavior
can be retained. That is, if a connection cannot log its transaction to the log
file, it is aborted by ASE rather than suspended.
Return to top
-------------------------------------------------------------------------------
1.3.2 What's the best value for cschedspins?
-------------------------------------------------------------------------------
It is crucial to understand that cschedspins is a tunable parameter
(recommended values being between 1-2000) and the optimum value is completely
dependent on the customer's environment. cschedspins is used by the scheduler
only when it finds that there are no runnable tasks. If there are no runnable
tasks, the scheduler has two options:
1. Let the engine go to sleep (which is done by an OS call) for a specified
interval or until an event happens. This option assumes that tasks won't
become runnable because of tasks executing on other engines. This would
happen when the tasks are waiting for I/O more than any other resource such
as locks. Which means that we could free up the CPU resource (by going to
sleep) and let the system use it to expedite completion of system tasks
including I/O.
2. Go and look for a ready task again. This option assumes that a task would
become runnable in the near term and so incurring the extra cost of an OS
context switch through the OS sleep/wakeup mechanism is unacceptable. This
scenario assumes that tasks are waiting on resources such as locks, which
could free up because of tasks executing on other engines, more than they
wait for I/O.
cschedspins controls how many times we would choose option 2 before choosing
option 1. Setting cschedspins low favors option 1 and setting it high favors
option 2. Since an I/O intensive task mix fits in with option 1, setting
cschedspins low may be more beneficial. Similarly since a CPU intensive job mix
favors option 2, setting cschedspins high may be beneficial.
The consensus is that a single CPU server should have cschedspins set to 1.
However, I strongly recommend that users carefully test values for cschedspins
and monitor the results closely. I have seen more than one site that has shot
themselves in the foot so to speak due to changing this parameter in production
without a good understanding of their environment.
Return to top
-------------------------------------------------------------------------------
1.3.3 Trace Flag Definitions
-------------------------------------------------------------------------------
To activate trace flags, add them to the RUN_* script. The following example is
using the 1611 and 260 trace flags. Note that there is no space between the
'-T' and the traceflag, despite what is written in some documentation.
Use of these traceflags is not recommended by Sybase. Please use at your
own risk.
% cd ~sybase/install
% cat RUN_BLAND
#!/bin/sh
#
# SQL Server Information:
# name: BLAND
# master device: /usr/sybase/dbf/BLAND/master.dat
# master device size: 25600
# errorlog: /usr/sybase/install/errorlog_BLAND
# interfaces: /usr/sybase
#
/usr/sybase/dataserver -d/usr/sybase/dbf/BLAND/master.dat \
-sBLAND -e/usr/sybase/install/errorlog_BLAND -i/usr/sybase \
-T1611 -T260
-------------------------------------------------------------------------------
Trace Flags
+-----------------------------------------------------------------------------+
| | |
|------+----------------------------------------------------------------------|
| Flag | Description |
|------+----------------------------------------------------------------------|
| 200 | Displays messages about the before image of the query-tree. |
|------+----------------------------------------------------------------------|
| 201 | Displays messages about the after image of the query-tree. |
|------+----------------------------------------------------------------------|
| 241 | Compress all query-trees whenever the SQL dataserver is started. |
|------+----------------------------------------------------------------------|
| | Reduce TDS (Tabular Data Stream) overhead in stored procedures. Turn |
| | off done-in-proc packets. Do not use this if your application is a |
| | ct-lib based application; it'll break. |
| 260 | |
| | Why set this on? Glad you asked, typically with a db-lib application |
| | a packet is sent back to the client for each batch executed within a |
| | stored procedure. This can be taxing in a WAN/LAN environment. |
|------+----------------------------------------------------------------------|
| | Changes the hierarchy and casting of datatypes to pre-11.5.1 |
| | behavior. There was an issue is some very rare cases where a wrong |
| | result could occur, but that's been cleared up in 11.9.2 and above. |
| | |
| 291 | The trace can be used at boot time or at the session level. Keep in |
| | mind that it does not disqualify a table scan from occurring. What |
| | it will do is result in fewer datatype mismatch situations and thus |
| | the optimizer will be able to estimate the costs of SARGs and joins |
| | on columns involved in a mismatch. |
|------+----------------------------------------------------------------------|
| 299 | This trace flag instructs the dataserver to not recompile a child |
| | stored procedure that inherits a temp table from a parent procedure. |
|------+----------------------------------------------------------------------|
| 302 | Print information about the optimizer's index selection. |
|------+----------------------------------------------------------------------|
| 303 | Display OR strategy |
|------+----------------------------------------------------------------------|
| | Revert special or optimizer strategy to that strategy used in |
| 304 | pre-System 11 (this traceflag resolved several bug issues in System |
| | 11, most of these bugs are fixed in ASE 11.0.3.2) |
|------+----------------------------------------------------------------------|
| 310 | Print information about the optimizer's join selection. |
|------+----------------------------------------------------------------------|
| 311 | Display the expected IO to satisfy a query. Like statistics IO |
| | without actually executing. |
|------+----------------------------------------------------------------------|
| 317 | Provide extra optimization information. |
|------+----------------------------------------------------------------------|
| 319 | Reformatting strategies. |
|------+----------------------------------------------------------------------|
| 320 | Turn off the join order heuristic. |
|------+----------------------------------------------------------------------|
| 324 | Turn off the like optimization for ad-hoc queries using |
| | @local_variables. |
|------+----------------------------------------------------------------------|
| | (Only valid in ASE versions prior to 11.9.2.) Instructs the server |
| | to use arithmetic averaging when calculating density instead of a |
| 326 | geometric weighted average when updating statistics. Useful for |
| | building better stats when an index has skew on the leading column. |
| | Use only for updating the stats of a table/index with known skewed |
| | data. |
|------+----------------------------------------------------------------------|
| | |
|------+----------------------------------------------------------------------|
| 602 | Prints out diagnostic information for deadlock prevention. |
|------+----------------------------------------------------------------------|
| 603 | Prints out diagnostic information when avoiding deadlock. |
|------+----------------------------------------------------------------------|
| 699 | Turn off transaction logging for the entire SQL dataserver. |
|------+----------------------------------------------------------------------|
| 1204 | Send deadlock detection to the errorlog. |
| * | |
|------+----------------------------------------------------------------------|
| 1205 | Stack trace on deadlock. |
|------+----------------------------------------------------------------------|
| 1206 | Disable lock promotion. |
|------+----------------------------------------------------------------------|
| 1603 | Use standard disk I/O (i.e. turn off asynchronous I/O). |
| * | |
|------+----------------------------------------------------------------------|
| 1605 | Start secondary engines by hand |
|------+----------------------------------------------------------------------|
| | Create a debug engine start file. This allows you to start up a |
| | debug engine which can access the server's shared memory for running |
| | diagnostics. I'm not sure how useful this is in a production |
| 1606 | environment as the debugger often brings down the server. I'm not |
| | sure if Sybase have ported the debug stuff to 10/11. Like most of |
| | their debug tools it started off quite strongly but was never |
| | developed. |
|------+----------------------------------------------------------------------|
| | Startup only engine 0; use dbcc engine("online") to incrementally |
| 1608 | bring up additional engines until the maximum number of configured |
| | engines. |
|------+----------------------------------------------------------------------|
| 1610 | Boot the SQL dataserver with TCP_NODELAY enabled. |
| * | |
|------+----------------------------------------------------------------------|
| 1611 | If possible, pin shared memory -- check errorlog for success/ |
| * | failure. |
|------+----------------------------------------------------------------------|
| 1613 | Set affinity of the SQL dataserver engine's onto particular CPUs -- |
| | usually pins engine 0 to processor 0, engine 1 to processor 1... |
|------+----------------------------------------------------------------------|
| 1615 | SGI only: turn on recoverability to filesystem devices. |
|------+----------------------------------------------------------------------|
| | Linux 11.9.2 only: Revert to using cached filesystem I/O. By |
| 1625 | default, ASE on Linux 11.9.2 opens filesystem devices using O_SYNC, |
| | unlike other Unix based releases, which means it is safe to use |
| | filesystems devices for production systems. |
|------+----------------------------------------------------------------------|
| 2512 | Prevent dbcc from checking syslogs. Useful when you are constantly |
| | getting spurious allocation errors. |
|------+----------------------------------------------------------------------|
| 3300 | Display each log record that is being processed during recovery. You |
| | may wish to redirect stdout because it can be a lot of information. |
|------+----------------------------------------------------------------------|
| 3500 | Disable checkpointing. |
|------+----------------------------------------------------------------------|
| 3502 | Track checkpointing of databases in errorlog. |
|------+----------------------------------------------------------------------|
| 3601 | Stack trace when error raised. |
|------+----------------------------------------------------------------------|
| 3604 | Send dbcc output to screen. |
|------+----------------------------------------------------------------------|
| 3605 | Send dbcc output to errorlog. |
|------+----------------------------------------------------------------------|
| 3607 | Do not recover any database, clear tempdb, or start up checkpoint |
| | process. |
|------+----------------------------------------------------------------------|
| 3608 | Recover master only. Do not clear tempdb or start up checkpoint |
| | process. |
|------+----------------------------------------------------------------------|
| 3609 | Recover all databases. Do not clear tempdb or start up checkpoint |
| | process. |
|------+----------------------------------------------------------------------|
| 3610 | Pre-System 10 behavior: divide by zero to result in NULL instead of |
| | error - also see Q6.2.5. |
|------+----------------------------------------------------------------------|
| 3620 | Do not kill infected processes. |
|------+----------------------------------------------------------------------|
| 4001 | Very verbose logging of each login attempt to the errorlog. Includes |
| | tons of information. |
|------+----------------------------------------------------------------------|
| 4012 | Don't spawn chkptproc. |
|------+----------------------------------------------------------------------|
| 4013 | Place a record in the errorlog for each login to the dataserver. |
|------+----------------------------------------------------------------------|
| 4020 | Boot without recover. |
|------+----------------------------------------------------------------------|
| | Forces all I/O requests to go thru engine 0. This removes the |
| 5101 | contention between processors but could create a bottleneck if |
| | engine 0 becomes busy with non-I/O tasks. For more information... |
| | 5101/5102. |
|------+----------------------------------------------------------------------|
| 5102 | Prevents engine 0 from running any non-affinitied tasks. For more |
| | information...5101/5102. |
|------+----------------------------------------------------------------------|
| 7103 | Disable table lock promotion for text columns. |
|------+----------------------------------------------------------------------|
| 8203 | Display statement and transaction locks on a deadlock error. |
|------+----------------------------------------------------------------------|
| * | Starting with System 11 these are sp_configure'able |
+-----------------------------------------------------------------------------+
Return to top
-------------------------------------------------------------------------------
1.3.4 Trace Flags -- 5101 and 5102
-------------------------------------------------------------------------------
5101
Normally, each engine issues and checks for its own Disk I/O on behalf of the
tasks it runs. In completely symmetric operating systems, this behavior
provides maximum I/O throughput for ASE. Some operating systems are not
completely symmetric in their Disk I/O routines. For these environments, the
server can be booted with the 5101 trace flag. While tasks still request disk I
/O from any engine, the actual request to/from the OS is performed by engine 0.
The performance benefit comes from the reduced or eliminated contention on the
locking mechanism inside the OS kernel. To enable I/O affinity to engine 0,
start ASE with the 5101 Trace Flag.
Your errorlog will indicate the use of this option with the message:
Disk I/O affinitied to engine: 0
This trace flag only provides performance gains for servers with 3 or more
dataserver engines configured and being significantly utilized.
Use of this trace flag with fully symmetric operating systems will degrade
performance!
5102
The 5102 trace flag prevents engine 0 from running any non-affinitied tasks.
Normally, this forces engine 0 to perform Network I/O only. Applications with
heavy result set requirements (either large results or many connections issuing
short, fast requests) may benefit. This effectively eliminates the normal
latency for engine 0 to complete running its user thread before it issues the
network I/O to the underlying network transport driver. If used in conjunction
with the 5101 trace flag, engine 0 would perform all Disk I/O and Network I/O.
For environments with heavy disk and network I/O, engine 0 could easily
saturate when only the 5101 flag is in use. This flag allows engine 0 to
concentrate on I/O by not allowing it to run user tasks. To force task affinity
off engine 0, start ASE with the 5102 Trace Flag.
Your errorlog will indicate the use of this option with the message:
I/O only enabled for engine: 0
-------------------------------------------------------------------------------
Warning: Not supported by Sybase. Provided here for your enjoyment.
Return to top
-------------------------------------------------------------------------------
1.3.5 What is cmaxpktsz good for?
-------------------------------------------------------------------------------
cmaxpktsz corresponds to the parameter "maximum network packet size" which you
can see through sp_configure. I recommend only updating this value through
sp_configure. If some of your applications send or receive large amounts of
data across the network, these applications can achieve significant performance
improvement by using larger packet sizes. Two examples are large bulk copy
operations and applications reading or writing large text or image values.
Generally, you want to keep the value of default network packet size small for
users performing short queries, and allow users who send or receive large
volumes of data to request larger packet sizes by setting the maximum network
packet size configuration variable.
caddnetmem corresponds to the parameter "additional netmem" which you can see
through sp_configure. Again, I recommend only updating this value through
sp_configure. "additional netmem" sets the maximum size of additional memory
that can be used for network packets that are larger than ASE's default packet
size. The default value for additional netmem is 0, which means that no extra
space has been allocated for large packets. See the discussion below, under
maximum network packet size, for information on setting this configuration
variable. Memory allocated with additional netmem is added to the memory
allocated by memory. It does not affect other ASE memory uses.
ASE guarantees that every user connection will be able to log in at the default
packet size. If you increase maximum network packet size and additional netmem
remains set to 0, clients cannot use packet sizes that are larger than the
default size: all allocated network memory will be reserved for users at the
default size. In this situation, users who request a large packet size when
they log in receive a warning message telling them that their application will
use the default size. To determine the value for additional netmem if your
applications use larger packet sizes:
* Estimate the number of simultaneous users who will request the large packet
sizes, and the sizes their applications will request.
* Multiply this sum by three, since each connection needs three buffers.
* Add 2% for overhead, rounded up to the next multiple of 512
Return to top
-------------------------------------------------------------------------------
1.3.6 Buildmaster Configuration Definitions
-------------------------------------------------------------------------------
Attention! Please notice, be very careful with these parameters. Use only
at your own risk. Be sure to have a copy of the original parameters. Be
sure to have a dump of all dbs (include master) handy.
Since the release of 11.0, there is a lot less need for buildmaster to
configure parameters. Check sp_configure and/or SERVERNAME.cfg to see if
the configuration parameter is there before using buildmaster.
-------------------------------------------------------------------------------
The following is a list of configuration parameters and their effect on the
ASE. Changes to these parameters can affect performance of the server. Sybase
does not recommend modifying these parameters without first discussing the
change with Sybase Tech Support. This list is provided for information only.
These are categorized into two kinds:
* Configurable through sp_configure and
* not configurable but can be changed through 'buildmaster -y<variable>=value
-d<dbdevice>'
Configurable variables:
crecinterval:
The recovery interval specified in minutes.
ccatalogupdates:
A flag to inform whether system catalogs can be updated or not.
cusrconnections:
This is the number of user connections allowed in SQL
Server. This value + 3 (one for checkpoint, network
and mirror handlers) make the number of pss configured
in the server.
-------------------------------------------------------------------------------
cfgpss:
Number of PSS configured in the server. This value will
always be 3 more than cusrconnections. The reason is we
need PSS for checkpoint, network and mirror handlers.
THIS IS NOT CONFIGURABLE.
-------------------------------------------------------------------------------
cmemsize:
The total memory configured for the Server in 2k
units. This is the memory the server will use for both
Server and Kernel Structures. For Stratus or any 4k
pagesize implementation of ASE, certain values
will change as appropriate.
cdbnum:
This is the number of databases that can be open in SQL
Server at any given time.
clocknum:
Variable that defines and controls the number of logical
locks configured in the system.
cdesnum:
This is the number of open objects that can be open at
a given point of time.
cpcacheprcnt:
This is the percentage of cache that should be used
for procedures to be cached in.
cfillfactor:
Fill factor for indexes.
ctimeslice:
This value is in units of milli-seconds. This value determines
how much time a task is allowed to run before it yields.
This value is internally converted to ticks. See below
the explanations for cclkrate, ctimemax etc.
ccrdatabasesize:
The default size of the database when it is created.
This value is Megabytes and the default is 2Meg.
ctappreten:
An outdated not used variable.
crecoveryflags:
A toggle flag which will display certain recovery information
during database recoveries.
cserialno:
An informational variable that stores the serial number
of the product.
cnestedtriggers:
Flag that controls whether nested triggers allowed or not.
cnvdisks:
Variable that controls the number of device structures
that are allocated which affects the number of devices
that can be opened during server boot up. If user
defined 20 devices and this value is configured to be
10, during recovery only 10 devices will be opened and
the rest will get errors.
cfgsitebuf:
This variable controls maximum number of site handler
structures that will be allocated. This in turn
controls the number of site handlers that can be
active at a given instance.
cfgrembufs:
This variable controls the number of remote buffers
that needs to send and receive from remote sites.
Actually this value should be set to number of
logical connections configured. (See below)
cfglogconn:
This is the number of logical connections that can
be open at any instance. This value controls
the number of resource structure allocated and
hence it will affect the overall logical connection
combined with different sites. THIS IS NOT PER SITE.
cfgdatabuf:
Maximum number of pre-read packets per logical connections.
If logical connection is set to 10, and cfgdatabuf is set
to 3 then the number of resources allocated will be
30.
cfupgradeversion:
Version number of last upgrade program ran on this server.
csortord:
Sort order of ASE.
cold_sortdord:
When sort orders are changed the old sort order is
saved in this variable to be used during recovery
of the database after the Server is rebooted with
the sort order change.
ccharset:
Character Set used by ASE
cold_charset:
Same as cold_sortord except it stores the previous
Character Set.
-------------------------------------------------------------------------------
cdflt_sortord:
page # of sort order image definition. This should
not be changed at any point. This is a server only
variable.
cdflt_charset:
page # of character set image definition. This should
not be changed at any point. This is a server only
variable.
cold_dflt_sortord:
page # of previous sort order image definition. This
should not be changed at any point. This is a server
only variable.
cold_dflt_charset:
page # of previous chracter set image definition. This
should not be changed at any point. This is a server
only variable.
-------------------------------------------------------------------------------
cdeflang:
Default language used by ASE.
cmaxonline:
Maximum number of engines that can be made online. This
number should not be more than the # of cpus available on this
system. On Single CPU system like RS6000 this value is always
1.
cminonline:
Minimum number of engines that should be online. This is 1 by
default.
cengadjinterval:
A noop variable at this time.
cfgstacksz:
Stack size per task configured. This doesn't include the guard
area of the stack space. The guard area can be altered through
cguardsz.
-------------------------------------------------------------------------------
cguardsz:
This is the size of the guard area. ASE will
allocate stack space for each task by adding cfgstacksz
(configurable through sp_configure) and cguardsz (default is
2K). This has to be a multiple of PAGESIZE which will be 2k
or 4k depending on the implementation.
cstacksz:
Size of fixed stack space allocated per task including the
guard area.
-------------------------------------------------------------------------------
Non-configurable values :
-------------------------------------------------------------------------------
TIMESLICE, CTIMEMAX ETC:
-------------------------------------------------------------------------------
1 millisecond = 1/1000th of a second.
1 microsecond = 1/1000000th of a second. "Tick" : Interval between two clock
interrupts occur in real time.
"cclkrate" :
A value specified in microsecond units.
Normally on systems where a fine grained timer is not available
or if the Operating System cannot set sub-second alarms, this
value is set to 1000000 milliseconds which is 1 second. In
other words an alarm will go off every 1 second or you will
get 1 tick per second.
On Sun4 this is set to 100000 milliseconds which will result in
an interrupt going at 1/10th of a second. You will get 6 ticks
per second.
"avetimeslice" :
A value specified in millisecond units.
This is the value given in "sp_configure",<timeslice value>.
Otherwise the milliseconds are converted to milliseconds and
finally to tick values.
ticks = <avetimeslice> * 1000 / cclkrate.
"timeslice" :
-------------------------------------------------------------------------------
The unit of this variable is in ticks.
This value is derived from "avetimeslice". If "avetimeslice"
is less than 1000 milliseconds then timeslice is set to 1 tick.
"ctimemax" :
The unit of this variable is in ticks.
A task is considered in infinite loop if the consumed ticks
for a particular task is greater than ctimemax value. This
is when you get timeslice -201 or -1501 errors.
"cschedspins" :
For more information see Q1.3.2.
This value alters the behavior of ASE scheduler.
The scheduler will either run a qualified task or look
for I/O completion or sleep for a while before it can
do anything useful.
The cschedspins value determines how often the scheduler
will sleep and not how long it will sleep. A low value
will be suited for a I/O bound ASE but a
high value will be suited for CPU bound ASE. Since
ASE will be used in a mixed mode, this value
need to be fined tuned.
Based on practical behavior in the field, a single engine
ASE should have cschedspins set to 1 and a multi-engine
server should have set to 2000.
Now that we've defined the units of these variables what happens when we change
cclkrate ?
Assume we have a cclkrate=100000.
A clock interrupt will occur every (100000/1000000) 1/10th milliseconds.
Assuming a task started with 1 tick which can go upto "ctimemax=1500" ticks can
potentially take 1/10us * (1500 + 1) ticks which will be 150 milliseconds or
approx. .15 milliseconds per task.
Now changing the cclkrate to 75000
A clock interrupt will occur every (75000/1000000) 1/7th milliseconds. Assuming
a task started with 1 tick which can go upto ctimemax=1500 ticks can
potentially take 1/7us * (1500 + 1) ticks which will be 112 milliseconds or
approx. .11 milliseconds per task.
Decreasing the cclkrate value will decrease the time spent on each task. If the
task couldnot voluntarily yield within the time, the scheduler will kill the
task.
UNDER NO CIRCUMSTANCES the cclkrate value should be changed. The default
ctimemax value should be set to 1500. This is an empirical value and this can
be changed under special circumstances and strictly under the guidance of DSE.
-------------------------------------------------------------------------------
cfgdbname:
Name of the master device is saved here. This is 64
bytes in length.
cfgpss:
This is a derived value from cusrconnections + 3.
See cusrconnections above.
cfgxdes:
This value defines the number of transactions that
can be done by a task at a given instance.
Changing this value to be more than 32 will have no
effect on the server.
cfgsdes:
This value defines the number of open tables per
task. This will be typically for a query. This
will be the number of tables specified in a query
including subqueries.
Sybase Advises not to change this value. There
will be significant change in the size of per user
resource in ASE.
cfgbuf:
This is a derived variable based on the total
memory configured and subtracting different resource
sizes for Databases, Objects, Locks and other
Kernel memories.
cfgdes:
This is same as cdesnum. Other values will have no effect on it.
cfgprocedure:
This is a derived value. Based on cpcacheprcnt variable.
cfglocks:
This is same as clocknum. Other values will have no effect on it.
cfgcprot:
This is variable that defines the number of cache protectors per
task. This is used internally by ASE.
Sybase advise not to modify this value as a default of 15 will
be more than sufficient.
cnproc:
This is a derived value based on cusrconnections + <extra> for
Sybase internal tasks that are both visible and non-visible.
cnmemmap:
This is an internal variable that will keep track of ASE
memory.
Modifying this value will not have any effect.
cnmbox:
Number of mail box structures that need to be allocated.
More used in VMS environment than UNIX environment.
cnmsg:
Used in tandem with cnmbox.
cnmsgmax:
Maximum number of messages that can be passed between mailboxes.
cnblkio:
Number of disk I/O request (async and direct) that can be
processed at a given instance. This is a global value for all
the engines and not per engine value.
This value is directly depended on the number of I/O request
that can be processed by the Operating System. It varies
depending on the Operating System.
cnblkmax:
Maximum number of I/O request that can be processed at any given
time.
Normally cnblkio,cnblkmax and cnmaxaio_server should be the same.
cnmaxaio_engine:
Maximum number of I/O request that can be processed by one engine.
Since engines are Operating System Process, if there is any limit
imposed by the Operating System on a per process basis then
this value should be set. Otherwise it is a noop.
cnmaxaio_server:
This is the total number of I/O request ASE can do.
This value s directly depended on the number of I/O request
that can be processed by the Operating System. It varies
depending on the Operating System.
csiocnt:
not used.
cnbytio:
Similar to disk I/O request, this is for network I/O request.
This includes disk/tape dumps also. This value is for
the whole ASE including other engines.
cnbytmax:
Maximum number of network I/O request including disk/tape dumps.
cnalarm:
Maximum number of alarms including the alarms used by
the system. This is typically used when users do "waitfor delay"
commands.
cfgmastmirror:
Mirror device name for the master device.
cfgmastmirror_stat:
Status of mirror devices for the master device like serial/dynamic
mirroring etc.
cindextrips:
This value determines the aging of a index buffer before it
is removed from the cache.
coamtrips:
This value determines the aging of a OAM buffer before it
is removed from the cache.
cpreallocext:
This value determines the number of extents that will be
allocated while doing BCP.
cbufwashsize:
This value determines when to flush buffers in the cache
that are modified.
Return to top
-------------------------------------------------------------------------------
1.3.7: What is CIS and how can I use it?
-------------------------------------------------------------------------------
CIS is the new name for Omni ASE. The biggest difference is that CIS is
included with Adaptive Server Enterprise as standard. Actually, this is not
completely accurate; the ability to connect to other ASEs and ASEs, including
Microsoft's, is included as standard. If you need to connect to DB2 or Oracle
you have to obtain an additional licence.
So, what is it?
CIS is a means of connecting two servers together so that seamless cross-server
joins can be executed. It is not just restricted to selects, pretty much any
operation that can be performed on a local table can also be performed on a
remote table. This includes dropping it, so be careful!
What servers can I connect to?
* Sybase ASE
* Microsoft SQL Server
* IBM DB2
* Oracle
What are the catches?
Well, nothing truly comes for free. CIS is not a means of providing true load
sharing, although you will find nothing explicitly in the documentation to tell
you this. Obviously there is a performance hit which seems to affect cursors
worst of all. CIS itself is implemented using cursors and this may be part of
the explanation.
OK, so how do I use it?
Easy! Add the remote server using sp_addserver. Make sure that you define it
as type sql_server or ASEnterprise. Create an "existing" table using the
definition of the remote table. Update statistics on this new "existing"
table. Then simply use it in joins exactly as if it were a local table.
Return to top
-------------------------------------------------------------------------------
1.3.8: If the master device is full, how do I make the master database bigger?
-------------------------------------------------------------------------------
It is not possible to extend the master database across another device, so the
following from Eric McGrane (mcgrane@sybase.com) from Sybase Product Support
Engineering should help.
* dump the current master database
* buildmaster a new master device with a larger size
* start the server in single user mode using the new master device
* login to the server and execute the following tsql:
select * from sysdevices
* take note of the high value
* load the dump of the master you had just taken
* restart the server (as it will be shut down when master is done loading),
again
in single user mode so that you can update system tables
* login to the server and update sysdevices setting high for master to the
value
that you noted previously
* shut the server down and start it back up, but this time not in single user
mode.
The end result of the above is that you will now have a larger master device
and you can alter your master database to be a larger size. For details about
starting the server in single user mode and how to use buildmaster (if you need
the details) please refer to the documentation.
Return to top
-------------------------------------------------------------------------------
1.3.9: How do I run multiple versions of Sybase on the same server?
-------------------------------------------------------------------------------
The answer to this relies somewhat on the platform that you are using.
Unix
ASE Versions Before 12.0
This applies to Unix and variants, Linux included. Install the various releases
of software into logical places within your filesystem. I like to store all
application software below a single directory for ease of maintenance, choose
something like /sw. I know that some are keen on /opt and others /usr/local. It
is all down to prefence and server usage. If you have both Oracle and Sybase on
the same server you might want /sw/sybase or /opt/sybase. Be a little careful
here if your platform is Linux or FreeBSD. The standard installation
directories for Sybase on those platforms is /opt/sybase. Finally, have a
directory for the release, say ASE11_9_2 or simply 11.9.2 if you only ever have
Sybase ASE running on this server. A little imagination is called for!
So, now you have a directory such as /sw/sybase/ASE/11.9.2 (my preferred choice
:-), and some software installed under the directories, what now? In the most
minimal form, that is all you need. Non of the environment variables are
essential. You could quite successfully run
/sw/sybase/ASE/11.9.2/bin/isql -Usa -SMYSERV -I/sw/sybase/ASE/11.9.2/interfaces
and get to the server, but that is a lot of typing. By setting the SYBASE
environment variable to /sw/sybase/ASE/11.9.2 you never need tell isql or other
apps where to find the interfaces. Then, you can set the path with a cool
PATH=$SYBASE/bin:$PATH
to pick up the correct set of Sybase binaries. That reduces the previous mass
of typing to
isql -Usa -SMYSERV
which is much more manageable.
You can create yourself a couple of shell scripts to do the changes for you. So
if the script a11.9 contained:
SYBASE=/sw/sybase/ASE/11.9.2
PATH=$SYBASE/bin:$SYBASE
# Remember to export the variables!
EXPORT PATH SYBASE
and a11.0 contained:
SYBASE=/sw/sybase/ASE/11.0.3.3
PATH=$SYBASE/bin:$SYBASE
# Remember to export the variables!
EXPORT PATH SYBASE
you would toggle between being connect to and 11.9.2 server and a 12.0 server,
depending upon which one you executed last. The scripts are not at all
sophisticated, you could quite easily have one script and pass a version string
into it. You will notice that the PATH variable gets longer each time the
script is executed. You could add greps to see if there was already a Sybase
instance on the path. Have I mentioned imagination?
ASE 12.0 and Beyond
Sybase dramatically changed the structure of the installation directory tree
with ASE 12. You still have a SYBASE environment variable pointing to the
route, but now the various packages fit below that directory. So, if we take /
sw/sybase as the root directory, we have the following (the following is for a
12.5 installation, but all versions follow the same format):
/sw/sybase/ASE-12_5
/OCS-12_5
Below ASE-12_5 is most of the stuff that we have come to expect under $SYBASE,
the install, bin and scripts directories. This is also where the SERVER.cfg
file has moved to. (Note the the interfaces file is still in $SYBASE.) The bin
directory on this side includes the dataserver, diagserver and srvbuild
binaries.
The OCS-12_5 is the open client software directory. It means that Sybase can
update the client software without unduly affecting the server. isql, bcp and
other clients are to be found here.
It does take a little getting used to if you have been using the pre-12 style
for a number of years. However, in its defence, it is much more logical, even
if it about triples the length of your PATH variable!
That is another good part of the new installation. Sybase actually provides you
with the shell script to do all of this. There is a file in /sw/sybase called
SYBASE.sh (there is an equivalent C shell version in the same place) that sets
everything you need!
Intefaces File
The only real addition to all of the above is an easier way to manage the
interfaces file. As mentioned before, ASE based apps look for the interfaces
file in $SYBASE/interfaces by default. Unix is nice in that it allows you to
have symbolic links that make it appear as if a file is somewhere that it
isn't. Place the real interfaces file somewhere independent of the software
trees. /sw/sybase/ASE/interfaces might be a sound logical choice. Now, cd to
$SYBASE and issue
ln -s /sw/sybase/ASE/interfaces
and the intefaces will appear to exist in the $SYBASE directory, but will
infact remain in its own home.
Note: make sure that interfaces file is copied to its own home before removing
it from $SYBASE.
Now you can put symbolic links in each and every software installation and only
have to worry about maintaining the server list, on that server, in one place.
Having the interfaces file common to many physical servers is trickier, but not
impossible. Personally I would choose to put it in a central CVS repository and
use that to keep each server reasonably up-to-date.
Return to top
-------------------------------------------------------------------------------
General Troubleshooting User Database Administration ASE FAQ
--
- David Alex Lamb, one of the *.answers moderators
news-answers-request@mit.edu
|
Закладки на сайте Проследить за страницей |
Created 1996-2024 by Maxim Chirkov Добавить, Поддержать, Вебмастеру |