1581695 - SYB Configuration Guide for SAP ASE 16 0

Prévia do material em texto

15816952020-10-04
1581695 - SYB: Configuration Guide for SAP ASE 16.0
Version 61 Type SAP Note
Language English Master Language English
Priority Recommendations / Additional Info Category Consulting
Release Status Released for Customer Released On 03.09.2020
Component BC-DB-SYB ( Business Suite on Adaptive Server Enterprise )
Please find the original document at https://launchpad.support.sap.com/#/notes/ 1581695
Symptom
You want to check whether your database configuration complies with SAP's 
requirements and recommendations.
Other Terms
SAP ASE, SAP Adaptive Server Enterprise, Sybase ASE, DBA Cockpit, DBACOCKPIT, 
CCMS, Database Monitoring, Adaptive Server Enterprise
Reason and Prerequisites
The configuration described in this SAP Note is recommended for running SAP 
NetWeaver application servers on the database SAP ASE version 16.0. For other 
database versions, see SAP Note 1539124.
Make sure to implement SAP Note 2889537 to ensure the Note download in DBA 
Cockpit is working.
Solution
The configuration requirements and recommendations specified in this SAP Note 
can be compared with your configuration of an SAP ASE database with the DBA 
Cockpit. This feature is available starting with the SAP_BASIS support package 
stacks:
7.02 SP17 
7.30 SP13 
7.31 SP14 
7.40 SP09
To run the configuration check, open transaction DBACOCKPIT, switch to the 
Database tab, and select Configuration -> SAP Configuration Check from the menu.
The configuration requirements and recommendations in this SAP Note adhere to 
the following syntax:
Curly brackets indicate logical groups of parameters.•
Lines starting with a "greater than" sign ('>') contain a configuration rule 
(either a recommendation or a requirement).
•
A rule consists of the name of the parameter, a relational operator, and a 
value.
•
At the end of a rule, additional information is given in square brackets:•
REQ means this is a requirement. SAP will not be able to support customer 
installations without this setting. 
REC means this is a recommendation. You can decide not to follow this rule, but 
1 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
https://launchpad.support.sap.com/#/notes/%0d%0a%0d%0a%0d%0a%0d%0a%0d%0a%0d%0a%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201581695
https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1539124
https://launchpad.support.sap.com/#/notes/2889537
15816952020-10-04
for the majority of all installations this recommendation will give you better 
performance and/or supportability. 
 
Requirements and recommendations can refer to particular operating systems. This 
is indicated by a colon followed by an operating system identifier. As an 
example, [REC:AIX] indicates that this is a recommendation for AIX only. The 
following operating system identifiers are used:
WIN Microsoft Windows•
AIX IBM AIX•
HPUX HP-UX•
SAP recommends the following settings for SAP ASE 16.0 as the database for SAP 
NetWeaver application servers. 
 
Database Server Configuration 
The 'Configuration' group contains database configuration parameters that can be 
set using the DBA Cockpit. 
{ Configuration 
 
The parameter 'functionality group' sets several database parameters as a group. 
This parameter has to be set to 1 to run SAP applications. 
> enable functionality group = 1 [REQ] 
 
The individual parameters of this functionality group are: 
> enable inline default sharing = 1 [REQ] 
> select for update = 1 [REQ] 
> enable permissive unicode = 1 [REQ] 
> quoted identifier enhancements = 1 [REQ] 
> streamlined dynamic SQL = 1 [REQ] 
> suppress js max task message = 1 [REQ] 
 
It is required that you use the threaded kernel mode: 
> kernel mode = threaded [REQ] 
 
The data collection framework used to collect monitoring information for the DBA 
Cockpit and to schedule recurring DBA tasks needs the SAP Sybase ASE job 
scheduler in order to be enabled. 
> enable job scheduler = 1 [REQ] 
> job scheduler interval <= 60 [REQ] 
> job scheduler tasks >= 4 [REQ] 
> js job output width >= 1024 [REQ] 
 
For the purpose of database monitoring and to ensure supportability of the 
database, it is necessary that you set the following parameters: 
> enable monitoring = 1 [REQ] 
> enable stmt cache monitoring = 1 [REQ] 
> errorlog pipe active = 1 [REQ] 
> deadlock pipe active = 1 [REQ] 
> sql text pipe active = 0 [REC] 
> plan text pipe active = 0 [REC] 
> lock timeout pipe active = 1 [REQ] 
> per object statistics active = 1 [REQ] 
> statement statistics active = 1 [REQ] 
> SQL batch capture = 1 [REQ] 
> wait event timing = 1 [REQ] 
> object lockwait timing = 1 [REQ] 
> process wait events = 1 [REC] 
> enable metrics capture = 0 [REC] 
> execution time monitoring = 1 [REQ] 
2 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
> enable cis = 1 [REQ] 
> cis connect timeout = 30 [REQ] 
 
The statement pipe is not needed for SAP monitoring and it is recommended that 
you keep it switched off for performance reasons: 
> statement pipe active = 0 [REC] 
 
For the size of pipes that store messages used by the data collection framework, 
it is necessary that you set this to at least 500: 
> errorlog pipe max messages >= 500 [REQ] 
> deadlock pipe max messages >= 500 [REQ] 
> statement pipe max messages = 0 [REQ] 
> sql text pipe max messages = 0 [REC] 
> plan text pipe max messages = 0 [REC] 
> lock timeout pipe max messages >= 500 [REQ] 
> max SQL text monitored >= 2048 [REQ] 
 
SAP applications rely on strong password encryption. 
> FIPS login password encryption = 1[REQ] 
 
We recommend forcing the database not to send unencrypted passwords over the 
network: 
> net password encryption reqd >= 1 [REC] 
 
The compression feature of SAP Sybase ASE is always used for SAP applications. 
Therefore, compression has to be enabled for the database server: 
> enable compression = 1 [REQ] 
> compression info pool size >= 32768 [REQ] 
 
XML support is needed to run SAP applications: 
> enable xml = 1 [REQ] 
 
For Unicode, the following settings are required: 
> enable unicode normalization = 0 [REQ] 
> enable surrogate processing = 0 [REQ] 
> disable character set conversions = 0 [REQ] 
> enable unicode conversions = 1 [REQ] 
 
Default character set settings: 
> default character set id = 190 [REQ] 
> default language id = 0 [REQ] 
 
The maximum job output needs to be set to the maximum value allowed by ASE: 
> maximum job output <= 16777216 [REQ] 
> maximum job output >= 16384 [REQ] 
 
A size of at least 512 MB memory is recommended for the procedure cache. 
Depending on your system's workload and the available memory, this may need to 
be increased: 
> procedure cache size >= 262144 [REC] 
 
The statement cache needs to have at least 100 MB: 
> statement cache size >= 51200 [REQ] 
 
For running SAP systems on SAP Sybase ASE, you are required to set row level 
locking: 
> lock scheme = datarows [REQ] 
 
This parameter is required for the dictionary (DDIC): 
> select on syscomments.text = 1 [REQ] 
 
3 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
It is recommended that you do not use lock promotion for SAP applications: 
> row lock promotion HWM = 2147483647 [REC] 
> row lock promotion LWM >= 2147483646 [REC] 
 
The number of locks should be at least 1000000. Depending on your workload, a 
higher number of locks may be required: 
> number of locks >= 1000000 [REC] 
> lock wait period = 1800 [REC] 
> lock spinlock ratio = 40 [REC] 
 
You need to set the lock hash table size according to the number of locks. It is 
recommended to set the lock hash table size to at least the number of locks in 
full millions times 8192. The value of this parameter should be a power of two, 
otherwise ASE will round it up to the next power of two and show a warning 
message. 
> lock hashtable size >= @number of locks@ / 1000000 * 8192 [REC] 
> lock hashtable size >= 8192 [REQ] 
 
The following settings affect memoryusage of SAP Sybase ASE. The values given 
are required to allow SAP applications to run on Sybase ASE: 
> number of open objects >= 40000 [REQ] 
> number of open indexes >= 30000 [REQ] 
> number of open partitions >= 30000 [REQ] 
> number of alarms >= 1000 [REQ] 
> number of aux scan descriptors >= 1024 [REQ] 
> number of devices >= 200 [REQ] 
> disk i/o structures >= 4096 [REQ] 
> kernel resource memory >= 32768 [REQ] 
> heap memory per user >= 49152 [REQ] 
> size of unilib cache >= 512000 [REC] 
 
The number of user connections needs to be adapted to your system size. For 
NetWeaver ABAP, recommendation is [total number of work processes * 4]. For 
NetWeaver Java, recommendation is [number of server nodes * 50]. In both cases, 
use a minimum of 200 user connections. 
> number of user connections >= 200 [REC] 
 
CPU grace time has to be set to 1000: 
> cpu grace time = 1000 [REC] 
 
The following settings are required for network communication: 
> max network packet size = 16384 [REQ] 
> default network packet size = 16384 [REQ] 
> additional network memory >= 10485760 [REQ] 
> send doneinproc tokens = 1 [REQ] 
 
For the recovery interval, please note that the parameter name is misleading as 
the interval is based on an estimate of 1000 pages per minute, whereas the real 
rate is approximately 10x higher. The value given here represents roughly a 5-
minute recovery window: 
> recovery interval in minutes <= 60 [REC] 
> print recovery information = 1 [REC] 
 
Additional settings that are requirements or recommended for SAP on Sybase ASE. 
> disable varbinary truncation = 1 [REQ] 
> allocate max shared memory = 1 [REC] 
> optimizer level = ase_current [REQ] 
> enable literal autoparam = 0 [REQ] 
> enable semantic partitioning = 1 [REQ] 
> permission cache entries >= 128 [REC] 
> enable java = 0 [REC] 
4 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
> deadlock checking period >= 500 [REC] 
> read committed with lock = 0 [REQ] 
> auto query tuning = 0 [REQ] 
max query parallel degree = 1 [REQ] 
> max query parallel degree >= 6 [REQ:BI] 
> max parallel degree = 1 [REQ:BI]
The optimization goal must be either "allrows_mix" or "sap_oltp" for systems 
that are not SAP BW Systems. 
 
optimization goal = allrows_mix [REQ] 
> optimization goal = sap_oltp [REQ]
The optimization goal is recommended to be "allrows_mix" or "sap_oltp" for SAP 
BW Systems. 
optimization goal = allrows_mix [REC:BI] 
> optimization goal = sap_oltp [REC:BI] 
> number of histogram steps = 100 [REC] 
> histogram tuning factor >= 20 [REC] 
> capture compression statistics = 0 [REC] 
> print deadlock information = 1 [REQ] 
> min pages for parallel scan >= 60000 [REC] 
> cpu accounting flush interval >= 5000000 [REC] 
> i/o accounting flush interval >= 5000000 [REC] 
> session tempdb log cache size = 32768 [REC] 
> number of large i/o buffers = 32 [REC] 
> enable housekeeper GC = 5 [REC] 
> enable housekeeper GC >= 1 [REQ] 
> sysstatistics flush interval = 5 [REC] 
> sysstatistics flush interval >= 1 [REQ] 
> global cache partition number >= 8 [REC] 
> number of oam trips = 1 [REC] 
> number of index trips = 1 [REC] 
> user log cache spinlock ratio = 5 [REC] 
> user log cache size = 65536 [REQ] 
> workload manager cache size = 145 [REQ] 
> default exp_row_size percent = 3 [REC] 
> housekeeper free write percent = 10 [REC] 
> number of pre-allocated extents = 32 [REC] 
> SQL Perfmon Integration = 0 [REC:WIN] 
> enable spinlock monitoring = 1 [REC] 
> enable plan sharing = 0 [REQ] 
> enable concurrent dump tran = 1 [REQ] 
> enable dump history = 1 [REQ] 
> update statistics hashing = partial [REC] 
> auditing = 1 [REC] 
> suspend audit when device full = 1 [REQ] 
> allow statement rollback = 1 [REQ] 
> enable utility lvl 0 scan wait = 1 [REC] 
> enable utility lvl 0 scan wait = 1 [REQ;BI] 
> threshold event max messages >= 500 [REQ] 
> threshold event monitoring = 1 [REQ] 
> allow resource limits = 1 [REC] 
> wait on uncommitted insert = 1 [REQ] 
> number of network tasks = 4 [REC:LNX] 
> number of network tasks = 4 [REC:SOL] 
> number of network tasks = 4 [REC:HPUX] 
> number of network tasks = 4 [REC:AIX] 
> number of network tasks = 1 [REC:WIN] 
> max nesting level >= 32 [REQ] 
> enable logins during recovery = 0 [REC]
5 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
Stack size needs to be set to the following values, depending on your operating 
system: 
> stack size >= 151552 [REQ] 
> stack size >= 194560 [REQ:AIX] 
> stack size >= 409600 [REQ:HPUX]
The stack guard size does not need special settings, the ASE default settings 
can be used.
Set tape retention to at least 1 day, this avoids risk that dump files get 
overwritten accidentally when generated dump file names contain a timestring AND 
daylight saving reverts to standard time. 
> tape retention in days >= 1 [REQ]
 
For systems running on HP-UX, it is necessary that you set the following 
parameter: 
> enable hp posix async i/o = 1 [REQ:HPUX]
For systems that have HADR enabled, it is necessary to set the following 
parameter:
> replication agent memory size >= 16290 [REQ:REP]
}
Data Caches
Depending on the size and the workload, you should consider a larger default 
data cache, a separate large I/O cache pool, and a separate cache for the 
database log: 
{ Data Caches 
> default data cache >= 409600 [REQ] 
}
Logging Error Messages
Using the stored procedure 'sp_altermessage', you can enable logging of certain 
error messages. Call the following command in isql: 
sp_altermessage <message_number>, 'with_log', 'true' 
The current setting for a specific message number can be checked using the 
following SQL command: 
SELECT CASE WHEN dlevel & 128 = 0 THEN 'false' ELSE 'true' END from 
master..sysmessages where error = <message_number> 
{ Messages 
 
Message number 701 is raised when the procedure cache size is insufficient to 
run a request: 
> 701 = 'true' [REQ] 
 
Message number 1105 is raised when it is not possible to allocate additional 
space to store data or logs: 
> 1105 = 'true' [REQ] 
 
Message number 2901 is raised when the stack limit has been exceeded: 
> 2901 = 'true' [REQ] 
 
Message number 12205 is raised when a lock could not be set within the 
configured wait time: 
> 12205 = 'true' [REQ] 
}
6 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
Database Options
Database options are set using the stored procedure sp_dboption. The following 
options need to be set for the databases <SID> and saptools. The current 
settings can be checked using the stored procedure sp_helpdb. 
{ DB Options 
> ddl in tran = 1 [REQ; S:@SID] 
> ddl in tran = 1 [REQ; S:saptools] 
> ddl in tran = 1 [REQ; S:tempdb] 
> ddl in tran = 1 [REQ; S:saptempdb] 
> allow nulls by default = 1 [REQ; S:@SID] 
> allow nulls by default = 1 [REQ; S:saptools] 
> allow nulls by default = 1 [REQ; S:tempdb] 
> allow nulls by default = 1 [REQ; S:saptempdb] 
> allow wide dol rows = 1 [REQ; S:@SID] 
> allow wide dol rows = 1 [REQ; S:saptools] 
> allow wide dol rows = 1 [REQ; S:tempdb] 
> allow wide dol rows = 1 [REQ; S:saptempdb] 
> allow incremental dumps = 1 [REQ; S:@SID] 
> allow incremental dumps = 1 [REQ; S:saptools] 
> no free space acctg = 0 [REQ; S:@SID] 
> no free space acctg = 0 [REQ; S:saptools] 
> unique auto_identity index = 0 [REQ; S:@SID] 
> unique auto_identity index = 0 [REQ; S:saptools] 
 
For the database <SID> the following options need to be set: 
> deferred table allocation = 1 [REC; S:@SID] 
> deallocate first text page = 1 [REC; S:@SID] 
 
It is recommended that you set the 'abort tran on log full' option, but this is 
not a requirement: 
> abort tran on log full = 1 [REC; S:@SID] 
> abort tran on log full = 1 [REC; S:saptools] 
 
Page compression can be set for all databases. This is strongly recommended for 
the SAP database: 
> page compress = 1 [REC; S:@SID]
 
 
For the production use, for the <SID> database options need to be set asfollows: 
> enforce dump tran sequence = 1 [REQ; S:@SID] 
> full logging for all = 1 [REQ; S:@SID] 
> select into/bulkcopy/pllsort = 0 [REQ; S:@SID] 
> select into/bulkcopy/pllsort = 1 [REQ; S:tempdb] 
> select into/bulkcopy/pllsort = 1 [REQ; S:saptempdb] 
> trunc log on chkpt = 0 [REQ; S:@SID] 
 
For 'sybsystemdb' and 'saptools' databases the following option needs to be set: 
> trunc log on chkpt = 1 [REC; S:sybsystemdb] 
> trunc log on chkpt = 1 [REC; S:saptools] 
}
Table Options
Table options are set using the stored procedure sp_chgattribute. To check the 
current settings of a table, use stored procedure sp_help.
The following tables are known to be volatile. If 'concurrency_opt_threshold' is 
set to '-1', the ASE optimizer will always scan the index without consideration 
7 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
of the table's statistics. For more information, see SAP Note 2049506. 
{ Table Options 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.ARFCSSTATE] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.ARFCSDATA] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.ARFCRSTATE] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.QREFTID] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.RSBATCHCTRL] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.TRFCQSTATE] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.TRFCQINS] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.TRFCQIN] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.TRFCQOUT] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.TRFCQDATA] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.VBMOD] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.VBHDR] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3.VBDATA] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3DB.BC_MID_CON_TRFC] 
> concurrency_opt_threshold = -1 [REQ; T:@SID.SAPSR3DB.BC_MID_CON_BGRFC] 
}
Additional Settings
Version 16.0.00.02 and higher:
{ Configuration -- Version >= 16.0.00.02 
> allow statement rollback = 1 [REQ] 
}
Version 16.0.00.04 and higher:
{ Configuration -- Version >= 16.0.00.04 
> enable async database init = 1 [REQ] 
} 
 
Version 16.0.01.00 and higher:
{ Configuration -- Version >= 16.0.01.00 
> enable sticky statistics = 0 [REQ] 
> enable bulk inserts = 0 [REQ] 
> expand numeric truncated scale = 1 [REQ] 
}
Version 16.0.02.04 and higher:
{ DB Options -- Version >= 16.0.02.04 
> allow db suspect on rollback error = 1 [REQ; S:@SID] 
} 
{ Configuration -- Version >= 16.0.02.04 
> enable lightweight rvm = 1 [REC] 
}
Version 16.0.02.05 and higher:
{ Configuration -- Version >= 16.0.02.05 
> wait on uncommitted insert = 2 [REQ] 
> restore database options = 1 [REQ] 
> extend implicit conversion = 1 [REQ] 
}
Version 16.0.03.00 and higher:
{ Configuration -- Version >= 16.0.03.00 
8 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
> stack size >= 1001472 [REC:HPUX] 
}
Other Components
Component Description
BW-SYS-DB-SYB BW on Adaptive Server Enterprise
This document refers to
SAP Note/KBA Title
2889537 SYB: DBA Cockpit - Unable to Parse SAP Note to SAP Configuration Check
1750510 FAQ: Sybase ASE 15.7 Compression
1539124 SYB: Database Configuration for SAP applications on SAP ASE
This document is referenced by
SAP 
Note/KBA
Title
2597701 Performance analysis for EU_CLONE_MIG_DT_RUN phase
2394975 Q&A for service report content - SAP Solution Manager
2906390 Deadlocks (SQL1205) reported on custom tables since migration to SAP ASE
2509889
DBACockpit Configuration Check: concurrency_opt_threshold expected value = -1 but current value = 
15.
2914499 status2 bit in sysdatabases is showing as 65 - SAP ASE
2913802 CREATE INDEX is slow on hyper-partitioned tables - SAP BW on ASE
2178977 Job scheduling fails on DBA Planning Calendar - SAP ASE for Business Suite
2889158
SUM upgrade - cannot determine value: unexpected result 0 != 1 Error during phase 
PREP_GENCHECKS/SYB_CHECKS_GENERIC
2285030 Which optimization goal to configure for Netweaver (BS / BW) applications on SAP ASE ?
2228134 Error 4002 "Login failed" when trying to connect to isql - SAP ASE for Business Suite and SAP BW
9 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
15816952020-10-04
2834034 SYBASE consume 100% CPU Usage
1980414 Error SQL712 "There is insufficient heap memory to allocate xxxxx bytes" - SAP ASE for BS
1729707 Error 716 - SAP ASE for Business Suite
2188214 How to analyse errors from a BW job running on SAP ASE
2733961
Error SQL1767 Number of variable length columns exceeds limit of 254 for allpage locked tables. - 
SAP ASE for BS
2471426 SAP Configuration check displays in status red - SAP ASE for Business Suite
2735906 Data collector 'Tables' is not working due to a message 233 - SAP ASE For Business Suite
2632877
The Volume to be overwritten on 'devname' has not expired: creation date on this volume is 
creation_date, expiration date is expiration_date.
2183108 t/qRFC processing: general performance verifications
2626395
"SAP Configuration Check" for parameter 'enable async database init' is RED - SAP ASE for 
Business Suite
2637935
DBACOCKPIT Configuration Check - Database option "allow db suspect on rollback" is RED - SAP 
ASE for Business Suite
2558414
'SAP configuration check' menu shows an incorrect Expected Value of stack size and stack guard 
size.
2588615
Short dump with Runtime in Errors EXPORT_TABLE_UPDATE_CONFLICT - SAP ASE for Business 
Suite
2495370 The EarlyWatch Alert is reporting a very high wait time for wait event 54.
2297423
"Warning The ATM setup on system 'DEV' is incomplete..." after upgrade - SAP ASE for Business 
Suite
2664265
SYB: Improving performance of Sybase ASE 15.7 and SAP ASE 16.0 on HP-UX with 
SCHED_NOAGE
2662578 IDM performance issues on Sybase ASE 15.7 and SAP ASE 16.0
2494590 SYB: Adaptions for Database Parameter Check for SAP ASE
1539124 SYB: Database Configuration for SAP applications on SAP ASE
1750510 FAQ: Sybase ASE 15.7 Compression
2400260 SYB: expand numeric scale for unit conversion function
Terms of use | Copyright | Trademark | Legal Disclosure | Privacy
10 of© 2020 SAP SE or an SAP affiliate company. All rights reserved 10
https://support.sap.com/support-programs-services/about/terms-of-use.html
http://www.sap.com/corporate-en/about/legal/copyright/index.html
http://www.sap.com/corporate-en/about/legal/copyright/index.html#trademark
http://www.sap.com/corporate-en/about/legal/impressum.html
http://www.sap.com/corporate-en/about/legal/privacy.html