1749935 - SYB Configuration Guide for SAP ASE 15 7

Prévia do material em texto

17499352021-06-15
1749935 - SYB: Configuration Guide for SAP ASE 15.7
Version 72 Type SAP Note
Language English Master Language English
Priority Recommendations / Additional Info Category FAQ
Release Status Released for Customer Released On 12.02.2021
Component BC-DB-SYB ( Business Suite on Adaptive Server Enterprise )
Please find the original document at https://launchpad.support.sap.com/#/notes/ 1749935
Symptom
You want to check whether your database configuration complies with SAP's 
requirements and recommendations.
Other Terms
SAP ASE, Sybase ASE, SAP Adaptive Server Enterprise, DBA Cockpit, DBACOCKPIT, 
CCMS, Database Monitoring
Reason and Prerequisites
The configuration described in this SAP Note is recommended for running SAP 
NetWeaver application servers on SAP Adaptive Server Enterprise version 15.7. 
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© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
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%201749935
https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1539124
https://launchpad.support.sap.com/#/notes/2889537
17499352021-06-15
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•
AIX•
HPUX•
SAP recommends the following settings for SAP ASE 15.7 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 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] 
> enable cis = 1 [REQ] 
2 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
 
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] 
 
Pipes store messages used by the Data Collection Framework. It is necessary that 
you set the size to at least 500: 
> errorlog pipe max messages >= 500 [REQ] 
> deadlock pipe max messages >= 500 [REQ] 
> statement pipe max messages = 0 
> 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 >= 2 [REC] 
 
The compression feature of SAP 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 SAP 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 ASE, you are required to set row level locking: 
> lock scheme = datarows [REQ] 
 
This parameter is required for the SAP DDIC: 
> select on syscomments.text = 1 [REQ] 
 
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] 
3 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
 
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 memory usage of SAP ASE. The values given are 
required to allow SAP applications to run on SAP ASE: 
> number of open objects >= 40000 [REQ] 
> number of open indexes >= 30000 [REQ] 
> number ofopen 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. 
Recommendation for SAP NetWeaver ABAP: [total number of work processes * 4] 
Recommendation for SAP NetWeaver Java: [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, 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] 
 
Additional settings that are requirements or recommended for running SAP 
applications on SAP 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] 
> deadlock checking period >= 500 [REC] 
> read committed with lock = 0 [REQ] 
> auto query tuning = 0 [REQ] 
4 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
> max query parallel degree = 1 [REQ] 
> number of histogram steps = 100 [REC]
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] 
> 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] 
> 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] 
> 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] 
 
 
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 that you set the following 
parameter:
5 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
> 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] 
}
Database Options
Database options are set using the stored procedure sp_dboption. Set the following 
options 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] 
6 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
> 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] 
 
Set the following option for database <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] 
 
Page compression can be set for all databases. This is strongly recommended for 
the SAP database: 
> page compress = 1 [REC; S:@SID] 
 
 
For productive use, set the options for the <SID> database as follows: 
> 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] 
 
Set the following option for database 'sybsystemdb': 
> trunc log on chkpt = 1 [REC; S:sybsystemdb] 
}
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 perform an index scan without 
consideration of the table's statistics. More information on this can be found 
in 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 15.7.0.021 and higher:
7 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
{ Configuration -- Version >= 15.7.0.021 
> enable spinlock monitoring = 1 [REC] 
> enable plan sharing = 0 [REQ] 
> update statistics hashing = off [REQ] 
> enable concurrent dump tran = 1 [REC] 
> enable dump history = 1 [REQ] 
}
{DB Options -- Version >= 15.7.0.021 
> deferred table allocation = 0 [REQ; S:@SID] 
> deferred table allocation = 0 [REQ; S:saptools] 
}
Version 15.7.0.030 and higher:
{ Configuration -- Version >= 15.7.0.030 
> enable plan sharing = 0 [REQ] 
> update statistics hashing = partial [REC] 
}
Version 15.7.0.042 and higher: 
 
{ DB Options -- Version >= 15.7.0.042 
Wrong setting of the following parameter will block you from using your database 
dumps. See SAP Note 1864348 for details 
> deallocate first text page = 0 [REQ; S:@SID] 
> deallocate first text page = 0 [REQ; S:saptools] 
> deferred table allocation = 1 [REC; S:@SID] 
} 
 
Version 15.7.0.043 and higher: 
 
{ DB Options -- Version >= 15.7.0.043 
> deallocate first text page = 1 [REQ; S:@SID] 
}
Version 15.7.0.100 and higher: 
 
{ Configuration-- Version >= 15.7.0.100 
> execution time monitoring = 1 [REQ] 
> enable concurrent dump tran = 1 [REQ] 
} 
 
{ DB Options -- Version >= 15.7.0.100 
> allow incremental dumps = 1 [REQ; S:@SID] 
> allow incremental dumps = 1 [REQ; S:saptools] 
> deallocate first text page = 1 [REC; S:@SID] 
}
Version 15.7.0.121 and higher:
{ Configuration -- Version >= 15.7.0.121 
> enable utility lvl 0 scan wait = 1 [REC] 
> enable utility lvl 0 scan wait = 1 [REQ;BI] 
}
Version 15.7.0.122 and higher:
{ Configuration -- Version >= 15.7.0.122 
> wait on uncommitted insert = 1 [REQ] 
}
8 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1864348
17499352021-06-15
Version 15.7.0.130 and higher:
{ Configuration -- Version >= 15.7.0.130 
> enable sticky statistics = 0 [REQ] 
> cis connect timeout = 30 [REQ] 
> enable bulk inserts = 0 [REQ] 
}
Version 15.7.0.131 and higher:
{ Configuration -- Version >= 15.7.0.131 
> enable async database init = 1 [REQ] 
}
Version 15.7.0.133 and higher: 
 
{ Configuration -- Version >= 15.7.0.133 
> expand numeric truncated scale = 1 [REC] 
}
Version 15.7.0.137 and higher:
{DB Options -- Version >= 15.7.0.137 
> allow db suspect on rollback error = 1 [REQ:S:@SID] 
}
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
1924067 SYB: sp_configure 'sysstatistics flush interval'
1539124 SYB: Database Configuration for SAP applications on SAP ASE
This document is referenced by
SAP 
Note/KBA
Title
9 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
2780687 EWA report recommendation: table option not set correctly - SAP ASE
2914499 status2 bit in sysdatabases is showing as 65 - SAP ASE
2178977 Job scheduling fails on DBA Planning Calendar - SAP ASE for Business Suite
2906390 Deadlocks (SQL1205) reported on custom tables since migration to SAP ASE
2889158
SUM upgrade - cannot determine value: unexpected result 0 != 1 Error during phase 
PREP_GENCHECKS/SYB_CHECKS_GENERIC
1729707 Error 716 - SAP ASE for Business Suite
2733961
Error SQL1767 Number of variable length columns exceeds limit of 254 for allpage locked tables. - 
SAP ASE for BS
2735906 Data collector 'Tables' is not working due to a message 233 - SAP ASE For Business Suite
2394975 Q&A for service report content - SAP Solution Manager
2632877
The Volume to be overwritten on 'devname' has not expired: creation date on this volume is 
creation_date, expiration date is expiration_date.
2188214 How to analyse errors from a BW job running on SAP ASE
2052047
When run SQL Statements menu in DBACOCKPIT, '500 Connection timed out' message occurred - 
SAP ASE for Business Suite
2118594 Error 1204 SQL During data load - SAP BW on ASE.
2134346 "Invalid column length" message and stacktrace happened - SAP ASE for Business Suite
2240787 XML for table BC_CPT_CORR_DEF could not be read from database - SAP ASE for Business Suite
2228134 Error 4002 "Login failed" when trying to connect to isql - SAP ASE for Business Suite and SAP BW
2558414
'SAP configuration check' menu shows an incorrect Expected Value of stack size and stack guard 
size.
2626395
"SAP Configuration Check" for parameter 'enable async database init' is RED - SAP ASE for 
Business Suite
2588615
Short dump with Runtime in Errors EXPORT_TABLE_UPDATE_CONFLICT - SAP ASE for Business 
Suite
2183108 t/qRFC processing: general performance verifications
2495370 The EarlyWatch Alert is reporting a very high wait time for wait event 54.
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
10 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
17499352021-06-15
1924067 SYB: sp_configure 'sysstatistics flush interval'
Terms of use | Copyright | Trademark | Legal Disclosure | Privacy
11 of© 2021 SAP SE or an SAP affiliate company. All rights reserved 11
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