SAP BASIS Crew: 2016-09-04

Wednesday, September 14, 2016

System Rename

Home

How to rename an SAP system using software provisioning manager 1.0.Which is the successor of the product- and release-specific delivery of provisioning tools, such as SAPinst and part of the Software Logistics Toolset . for more information check SAP NOTE 1680045
It is valid for SAP systems based on SAP NetWeaver releases described on the following SAP NOTE 1619720 - System Rename for SAP Systems based on SAP NetWeaver

It is also used
1- Installation, copy, and transformation for products based on SAP NetWeaver AS ABAP and AS Java
2- dual-stack split
3- System Rename
4- Frontend Installation and Distribution

You can rename the following SAP systems:

Systems installed by using the standard SAP tools.
Systems copied by using classical system copy (R3load/Jload or DB-specific procedure).
Systems created by using backup or restore with procedures offered by Microsoft Windows
Systems created by using SAP NetWeaver Landscape Virtualization management
Cloned systems (virtual to virtual, virtual to physical, or physical to virtual)

In the following case trying to change the SAP System SID , DB SID , and NR for theSource System
which is based on SAP NetWeaver 7.0x and Oracle DB and windows Server 2008 OS

SAP System SID = S6C
DB SID = S6C
Nr= 00
to New SID the Target System
SAP System SID = T11
DB SID = T11
Nr= 11

Constraints:
Rename can be performed on primary application server instances – additional application server instances have to be reinstalled

according to your OS download Software Provisioning Manager1.0 Package from SAP Service Marketplace at:
http://service.sap.com/sltoolset -> 1.0 -> Software Provisioning Manager

Log on to your SAP system
Extract the SAR file

Running the Software Provisioning Manager

On the Welcome screen, choose the following:System Rename ==> central System ==> SAP system Rename
Follow the instructions in the input screens AS shown in the following Step

Select SAP System Rename
And Click Next

Enter the Required Parameter for source system (SID) and target System (NEW SID, Global Host and Installation Drive) And Click Next
SAP Global Host is giving by default

If you Plane to use Virtual Host Name Make sure that the the virtual host name is bound to local network interface
For the Target Instance Number
Do not use the following numbers:
43 - used by MSCS "in case you are using MSCS"
89 - used by Windows Terminal Server

Click Next

Enter the Source SID and Target SID and Host Name For the Database and New Password For Oracle Schema
Recommendation
Enter exactly the central instance number and SID that you have entered during the central instance installation.

If you choose different domain you have to follow the procedure of Changing Windows Domain in System Rename guide
Click next

the above step will be required in case you did not perform Maintaining Users and Groups Step
Enter the Password for the following OS user (<SID>ADM ,SAPService <SID>)
Click Next

if you like to change the ORACLE HOME deselect the checkbox of rename Oracle Software
Click next

Leave the field Empty
Click Next

Click Next

Make sure your SAP System IS down
Click Next

Click OK

System rename finished successfully

Post Activities

1- check the New SID name on OS Level in the following Directories
/usr/sap/SID
/Oracle/SID

2 - Logon to sap SAP system and as you have New Server Name Adapt your configuration accordingly

3- Run an installation check: Administration System administration Administration Installation Check(transaction SM28).

4- Perform server check (transaction SM51)

5- Adapt the Transport Management System (transaction STMS) According to the New SID if it is required

6- Delete all entries from the following tables: ALCONSEG, ALSYSTEMS, DBSNP, MONI, OSMON, PAHI, SDBAD, SDBAH, SDBAP, SDBAR as it is Contain log and Monitoring data related to the Old SID

7- Adapt all Background jobs needed in the SAP System

8- Adapt the definition of the printers to meet the new system

9- Adapt the RFC destination: (transaction SM59).

10- Adapt Your Operation Mode If it is configured

11- Adapt Your Logical System According to your naming convention was used

12- Adapt the instances and profiles

13- Adapt CCMS settings (for example, alert thresholds, reorganization parameters of CCMS table MONI) if required.

14- Replace all existing PSEs in the target systems with new ones, which contain the new system's information

15- Add your System To the SLD

Restarting the SAP System

----------------------------------------------

Idle session time out / Logging off inactive users

Home

Idle session time out / Logging off inactive users

Logging off the inactive users is not set up by default. Setting up the feature of logging off the users improves the security as unattended terminals do not stay active indefinitely.

To activate automatic logging off, do the following:

Go to RZ10/RZ11 -> instance/default profile -> select the below parameter -- change and save it

Maintain the parameter rdisp/gui_auto_logout. Enter the required number of seconds of inactivity before the user is logged off automatically.

To deactivate automatic logoff, delete the parameter from the profile(s) or set the value '0'.

Also find parameter rdisp/plugin_auto_logout to set timeout for http sessions as well.

Tables to edit

Home

1) go to tcode SE37,
2) FU SE16N_INTERFACE,
3) set import params:
I_TAB=
I_EDIT=X
I_SAPEDIT=X
4) press F8.

or

There is another way to change entries of a table with SE16 even if you don’t have the authorization:

Go in SE16, enter your table Name (ex: USR02). Display the entry (or entries) you want to modify.
When you have your entry, write ‘/h’ to go in debug mode. Cross the line you want to modify and press F7 (display function). Here, you are in the code… Press F7 again to skip some code…

Now you should see the code:

Refresh Exclude_Tab.
If Code = ‘SHOW’.
Set Titlebar ‘TAB’ With Name ‘anzeigen’(100).
Elseif Code = ‘EDIT’.
…
Change the value from SHOW to EDIT, click Change FLD Contents, and press F8…
or to delete an entry
Change the value from SHOW to DELE, click Change FLD Contents, and press F8…
Here we are… You can change the value to what you want, and don’t forget to save!

Export/Import Tables through BRTOOLS

Home

If would like to reorganize table and rebuild index offline for table SOFFCONT1. Table SOFFCONT1 contains SAP Business Workplace/SAPoffice objects which often grow rapidly in Netweaver based component.

Procedure : Here’s the procedure to export/import tables and rebuild index using BRTOOLS,

1. Export table SOFFCONT1
2. Drop index
3. Import table SOFFCONT1
4. Run Update Statistics Optimizer

Details steps :

1. Export table SOFFCONT1

>brspace -f tbexport -t soffcont1
BR0280I BRSPACE time stamp: 2009-06-06 21.10.00
BR1009I Name of database instance: R3X
BR1010I BRSPACE action ID: seasymoz
BR1011I BRSPACE function ID: tbe
BR1012I BRSPACE function: tbexport
BR0280I BRSPACE time stamp: 2009-06-06 21.10.07
BR0657I Input menu 359 - please enter/check input values
Main options for export of tables: SAPR3X.SOFFCONT1
1 * Export utility (utility) ............... [EXP]
2 * Tablespaces for export (tablespaces) ... []
3 * Owner for export (owner) ............... []
4 - Export table rows (rows) ............... [yes]
5 - Export table indexes (indexes) ......... [yes]
6 - Export table constraints (constraints) . [yes]
7 - Export table grants (grants) ........... [yes]
8 - Export table triggers (triggers) ....... [yes]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2009-06-06 21.12.18
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-06 21.12.18
BR0657I Input menu 360 - please enter/check input values
Additional options for export of tables: SAPR3X.SOFFCONT1
1 - Use direct path (direct) ................ [yes]
2 - Export buffer size in KB (buffer) ....... [10240]
3 - Compress table extents/data (compress) .. [no]
4 - Consistent export (consistent) .......... [no]
5 # Parallel degree (parallel) .............. [1]
6 - Max. size of dump file in MB (filesize) . [20000]
7 - Force table export (force) .............. [no]
8 - EXP/EXPDP command (command) ............. [E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2009-06-06 21.13.15
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-06 21.13.16
BR0370I Directory I:\Oracle\R3X\sapreorg\seasymoz created
BR0280I BRSPACE time stamp: 2009-06-06 21.13.16
BR0370I Directory I:\Oracle\R3X\sapreorg\seasymoz.edd created
BR1164I Export of tables will be started with command line: E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp
BR0280I BRSPACE time stamp: 2009-06-06 21.13.16
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
BR0280I BRSPACE time stamp: 2009-06-06 21.13.47
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0278I Command output of 'E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp':
Export: Release 10.2.0.2.0 - Production on Sat Jun 6 21:13:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8DEC character set and UTF8 NCHAR character set
About to export specified tables via Direct Path ...
Current user changed to SAPR3X
. . exporting table SOFFCONT1
continuing export into file I:\Oracle\R3X\sapreorg\seasymoz.edd\expdat1.dmp
962456 rows exported
Export terminated successfully without warnings.
BR0280I BRSPACE time stamp: 2009-06-06 22.44.59
BR1160I 1 table exported by EXP utility
BR0280I BRSPACE time stamp: 2009-06-06 22.45.02
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
BR0280I BRSPACE time stamp: 2009-06-06 22.55.09
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-06 22.55.10
BR1022I Number of tables processed: 1
BR1003I BRSPACE function 'tbexport' completed
BR1008I End of BRSPACE processing: seasymoz.tbe 2009-06-06 22.55.10
BR0280I BRSPACE time stamp: 2009-06-06 22.55.12
BR1005I BRSPACE completed successfully

2. Drop index
>sqlplus “/as sysdba”
SQL>drop index “SAPR3X”.”SOFFCONT1~0″;
Index dropped

3. Import table SOFFCONT1
>brspace -f tbimport -t soffcont1
BR0280I BRSPACE time stamp: 2009-06-06 23.00.00
BR1009I Name of database instance: R3X
BR1010I BRSPACE action ID: seasywiu
BR1011I BRSPACE function ID: tbi
BR1012I BRSPACE function: tbimport
BR0280I BRSPACE time stamp: 2009-06-06 23.00.00
BR0656I Choice menu 361 - please make a selection
Import tables main menu
1 = Import tables
2 - Show BRSPACE export runs
3 * Exit program
4 - Reset program status
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2009-06-06 23.00.14
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0699I Reading log file I:\Oracle\R3X\sapreorg\spaceR3X.log ...
BR0280I BRSPACE time stamp: 2009-06-06 23.00.15
BR0658I List menu 362 - please select one entry
List of BRSPACE exports for import
Pos. Run Date Tables Dumps Size[KB] Util.
1 = seasymoz.tbe 2009-06-06 21.09.57 1 2 25519234 EXP
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your selection:
BR0280I BRSPACE time stamp: 2009-06-06 23.01.22
BR0663I Your selection: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-06 23.01.22
BR0657I Input menu 363 - please enter/check input values
Main options for import from dump file I:\Oracle\R3X\sapreorg\seasymoz.edd\expd at.dmp,... (2 dumps)
1 * Import utility (utility) ............... [IMP]
2 - Import type (type) ..................... [full]
3 # Owner for import (owner) ............... []
4 # Tables for import (tables) ............. [SOFFCONT1]
5 - Import table rows (rows) ............... [yes]
6 - Import table indexes (indexes) ......... [yes]
7 - Import table constraints (constraints) . [yes]
8 - Import table grants (grants) ........... [yes]
9 # Import table triggers (triggers) ....... [yes]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2009-06-06 23.02.42
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-06 23.02.42
BR0657I Input menu 364 - please enter/check input values
Additional options for import from dump file I:\Oracle\R3X\sapreorg\seasymoz.ed d\expdat.dmp,... (2 dumps)
1 - Import buffer size in KB (buffer) ....... [10240]
2 - Commit after each array insert (commit) . [yes]
3 - Ignore creation errors (ignore) ......... [yes]
4 # Table exists action (action) ............ [skip]
5 # Parallel degree (parallel) .............. [1]
6 # Max. size of dump file in MB (filesize) . [20000]
7 - Force table import (force) .............. [no]
8 - IMP/IMPDP command (command) ............. [E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2009-06-06 23.03.06
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-06 23.03.07
BR0370I Directory I:\Oracle\R3X\sapreorg\seasywiu created
BR1172I Import of tables will be started with command line: E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp
BR0280I BRSPACE time stamp: 2009-06-06 23.03.07
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
BR0280I BRSPACE time stamp: 2009-06-06 23.03.34
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0278I Command output of 'E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp':
Import: Release 10.2.0.2.0 - Production on Sat Jun 6 23:03:35 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8DEC character set and UTF8 NCHAR character set
. importing SYS's objects into SYS
. importing SAPR3X's objects into SAPR3X
. . importing table "SOFFCONT1" 962456 rows imported
Import terminated successfully without warnings.
BR0280I BRSPACE time stamp: 2009-06-07 00.43.12
BR1166I 1 table imported by IMP utility
BR0280I BRSPACE time stamp: 2009-06-07 00.43.14
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRSPACE:
BR0280I BRSPACE time stamp: 2009-06-07 00.46.19
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-07 00.46.19
BR0656I Choice menu 361 - please make a selection
Import tables main menu
1 * Import tables
2 - Show BRSPACE export runs
3 = Exit program
4 - Reset program status
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2009-06-07 00.46.25
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2009-06-07 00.46.25
BR0680I Do you really want to exit BRSPACE? Enter y[es]/n[o]:
BR0280I BRSPACE time stamp: 2009-06-07 00.46.33
BR0257I Your reply: 'y'
BR0280I BRSPACE time stamp: 2009-06-07 00.46.33
BR1022I Number of tables processed: 1
BR1003I BRSPACE function 'tbimport' completed
BR1008I End of BRSPACE processing: seasywiu.tbi 2009-06-07 00.46.33
BR0280I BRSPACE time stamp: 2009-06-07 00.46.34
BR1005I BRSPACE completed successfully

4. Run Update Statistic Optimizer in SAP
Execute or Schedule this via DB13 then it will create a new index for table SOFFCONT1.
Note: Tables SOFFCONT1 need to be reorganized offline as they contain LONG RAW fields and must not be converted to LOBS with the online reorganization.

Shell Script to Check status of SAP whether Up or not

Home

#!/bin/ksh

################################################################################
#
# /usr/bin/checksap.sh
# UNIX Shell Script for Checking
# 1. Whether SAP Has Started or not
# is found by checking whether kill.sap file exists or not
# and if it exists, whether the PID recorded in the file is live or not
# 2. Whether DB has started or notA
# is found using the Command R3trans -d
# 3. Whether SAP is able to detect the Message Server or not
# (important for Applicateion Server Test
#
################################################################################

__time=`date` ;
__date=`echo $__time | awk '{ printf "%s",$3$2$6"/"$4 ; }' ;`;
__variable='';
__variable1='';
SAPSYSTEMNAME=$1 #### 'D20';

LOCAL_FILE_NAME=`echo /usr/sap/${SAPSYSTEMNAME}/D*/work/kill.sap`;
my_SID_USERNAME=`echo ${SAPSYSTEMNAME}adm | tr '[A-Z]' '[a-z]'`
APPLICATION_SERVER="true" # Notes whether the SAP System has Application Servers or not ?

MY_PROC_LIST="";
MY_SAP_RUNNING='';
if [ -f $LOCAL_FILE_NAME ];
then
MY_PROC_LIST=`cat $LOCAL_FILE_NAME | awk '{printf $3}'`;
MY_SAP_RUNNING=`ps -p $MY_PROC_LIST | grep 'sapstart' | awk '{printf $4}'`;
fi;

MY_DBSERVER='true';

MY_DB_STR="";
if [ $MY_DBSERVER == "true" ] ;
then
MY_DB_STR=`su - $my_SID_USERNAME -c "R3trans -d" | grep "R3trans finished"| awk '{printf $3}'`;
fi;

__variable1='';
if [ $MY_DB_STR == "(0000)." ];
then
__variable1="$__time DB Inst Running";
else
__variable1="$__time DB Inst Not Running";
fi;
__variable=$__variable" "$__variable1;

MY_ARGUMENT=`ps -ef | grep dw.sap | awk '{if (NR==1) printf $9}'`

if [ $APPLICATION_SERVER == "true" ];
then

my_var=`su - $my_SID_USERNAME -c "lgtst $MY_ARGUMENT" | grep "list of reachable "`;

if [ -z $my_var ];
then
__variable=$__variable", CI Not Running ";
else
__variable=$__variable", CI is Running ";
fi;
fi;

__variable1="";

if [ -f $LOCAL_FILE_NAME ]; #### -a [ $MY_SAP_RUNNING == "sapstart" ];
then
if [ $MY_SAP_RUNNING == "sapstart" ];
then
my_start_time=`ls -lt $LOCAL_FILE_NAME | awk '{ printf "%s",$7"-"$6"-2006 "$8 ; }' ;`;
__variable1="SAP Inst has started at $my_start_time /$__date";
fi;
else
__variable1="SAP Inst has not started /$__date";
fi;

__variable=$__variable", "$__variable1;

echo $__variable ;

##### It can be added to crontab, and scheduled to run maybe every 5 or 10 minutes. #####

Configuration of Bar Code Printing in SAP

Home

Steps to Configure Bar Code Printing in SAP

1. Download barcode.dll file from website http://www.e-bizco.com

2. Install the barcode.dll file in the system, by either running the Setup program or copying the barcode.dll file in the directory where saplpd.exe (SAP Printer Daemon) File exists or in the Windows directory (c:\windows or c:\windows\system32).

3. Check whether the SAP Printer Daemon (SAPLpd) is sensing barcode.dll file or not by starting the SAP Printer Daemon. It will show in the log.

4. Configure Front End printer using the Transaction Code : SPAD
a. with Device Type “SWIN” or a copy “ZSWIN”

b. Access Method “F: Printing on Front End Computer” or “S: Print Using SAP Protocol”

6. Test the printer by printing the Bar Code, whether it works or not ?

7. If you get some Extra text "bC=PSN9,B=65,H=3,A=0,D=" in the printout in place of the Barcode.

Then the reason is because some R/3 releases have a broken device type SWIN. Please copy SWIN to ZSWIN and change the print control SBS01: The text field shall be empty. Atributes "Extended" and "Converted" are OK.

8. Other Errors, please refer the URL
http://www.e-bizco.com/english/support.phtml

Time Zone difference issue between 000 and other clients

Home

While executing the transaction code ST03N in SAP some time problem comes that there is a
conflicts in Time Zone between 000 and other clients in the same system.

solution:-

1) Execute the transaction code stzac in both of the respective clients.

2) Check whether System Time Zone and User's Default Time Zone are same in both the clients.

3) If not the maintain both the Time Zone i.e., System Time Zone and User's defaults Time Zone Same in both the clients.

While Saving it will create a Transport Request.

Again check with the Transaction Code ST03N you will find conflict has been resolved

DB2 Parameters

Home

584952 - DB6: DB2 UDB Version 8 Standard Parameter Settings
1851832 - DB6: DB2 10.5 Standard Parameter Settings
1420036 - DB6: DB2 9.8 pureScale Standard Parameter Settings
1086130 - DB6: DB2 9.5 Standard Parameter Settings
1692571 - DB6: DB2 10.1 Standard Parameter Settings
899322 - DB6: DB2 9.1 Standard Parameter Settings
1329179 - DB6: DB2 9.7 Standard Parameter Settings

Monday, September 12, 2016

HANA Loads

Home

1. What are loads and unloads in SAP HANA environments?

When a column is loaded, it is copied from persistence into SAP HANA column store memory.

Unloads are table column displacements from column store memory.

In this SAP Note we focus on loads and unloads of columns in column store. Tables in row store are typically loaded during startup and remain in memory permanently.

2. Which indications exist for SAP HANA load and unload issues?

While loads usually don't indicate issues, unloads are critical for the following reasons: They are often indicators of memory bottlenecks and they introduce overhead because unloaded column may have to be reloaded after some time.

The following SAP HANA alerts indicate problems in the unload area:

Alert	Name	SAP Note	Description
55	Columnstore unloads	1977207	Determines how many columns in columnstore tables have been unloaded from memory. This can indicate performance issues.

SQL: "HANA_Configuration_MiniChecks" (SAP Notes 1969700, 1999993) returns a potentially critical issue (C = 'X') for one of the following individual checks:

Check ID	Details
430	Number of column store unloads (last day)
431	Time since last column store unload (days)

3. When do loads happen?

Columns are loaded into memory in the following situations:

Reason

Details

Explicit access

When a table column is accessed and it doesn't reside in memory, yet, it is loaded into memory.

Exceptions:

Hybrid LOB columns are not loaded into memory when they exceed the configured memory threshold (SAP Note 1994962).
Paged attributes are only partially loaded into memory (SAP Note 1871386).

Time spent for loading table columns during SQL statement preparation can be identified via column TOTAL_TABLE_LOAD_TIME_DURING_PREPARATION in monitoring view M_SQL_PLAN_CACHE. Time spent for loading table columns during SQL statement execution is not inidividually displayed.

Explicit load

You can use the following options to load tables into memory explicitly:

Command	Details
LOAD "<table>" ALL SAP HANA Studio -> <system> -> Catalog -> <schema> -> <table> -> "Load into memory..."	Load all table columns into memory
LOAD "<table>" ("<column>") LOAD "<table>" ("column1>", ..., "<columnN>")	Load the defined table columns into memory
LOAD "<table>" DELTA	Load the delta storage of the table into memory
LOAD "<table>" HISTORY ...	When the key word HISTORY is added after <table>, the load is related to the history part of a temporal table.

If you want to load all tables into memory (e.g. as recommended in SAP Note 2066313), you can use the following Python script available as part of the SAP HANA installation:

/usr/sap/<sid>/HDB<inst_id>/exe/python_support/loadAllTables.py

This tool should only be used in exceptional situations, because loading all tables into memory can result in memory and CPU bottlenecks.

Reload after startup (explicitly configured tables)

The following commands can be used to define tables that should be loaded directly after startup:

Command	Details
ALTER TABLE "<table>" PRELOAD ALL	Set the reload flag for all columns of the table
ALTER TABLE "<table>" PRELOAD ("<column>") ALTER TABLE "<table>" PRELOAD ("<column1>", ..., "<columnN>")	Set the reload flag for the defined columns of the table
ALTER TABLE "<table>" PRELOAD NONE	Unsets the reload flag for all columns of the table

You can check for tables and / or columns with an activated reload flag in the following ways:

Columns IS_PRELOAD and IS_PARTIAL_PRELOAD of TABLES
Column PRELOAD of TABLE_COLUMNS
SQL: "HANA_Tables_ColumnStore_PreloadActive" (SAP Note 1969700)

Loading columns flagged with PRELOAD has precedence over columns marked for pre-warming (see below).

Reload after startup (pre-warming based on columns previously loaded)

Automatic load of columns on secondary system of system replication environment

Automatic load of columns on standby node during auto host failover

The following SAP HANA parameters control column loads during SAP HANA startup and on the secondary system of a system replication scenario based on columns loaded into memory before the shutdown:

Parameter	Default	Unit	Details
indexserver.ini -> [sql] -> reload_tables	true		If set to 'true', SAP HANA loads columns into memory during startup, which were located in memory before shutdown. This can be considered as pre-warming in order to make sure that column loads are not required when the table is accessed the first time explicitly.
indexserver.ini -> [parallel] -> tables_preloaded_in_parallel	5		Number of tables loaded in parallel after startup A higher value typically results in quicker reloads, but a higher CPU consumption, so it is a trade-off between load time and resource consumption. If you want to adjust it, you should perform some tests to find an optimal value to fulfill your needs.
global.ini -> [system_replication] -> preload_column_tables	true		Per default SAP HANA loads the columns into the memory of the secondary system of a system replication scenario during normal uptime. This has the advantage that a reload is not required at failover time. If you want to disable this feature (e.g. because only limited memory is available on the secondary side), you can set the preload_column_tables parameter to 'false'. The effect of this parameter depends on the system where it is set: Primary system: Information about loaded tables is collected and persisted in the system replication related snapshot. Secondary system: The load information from primary is evaluated and the tables are loaded accordingly.

You can check for tables currently part of this reload information using the following hdbcons command (SAP Note 2222218):

tablepreload c -f

4. How can reload information for startup, host auto failover and system replication be collected?

The following parameter is used to control the collection of reload information:

Parameter	Default	Unit	Details
global.ini -> [persistence] -> tablepreload_write_interval	3600 (Rev. 69 and below) 86400 (Rev. 70 and above)	s	This parameter defines the frequency of collecting table load information for reloads during startup and on the secondary system replication side. Collection of the data is disabled by setting the value to 0.

In exceptional cases you can manually execute the following hdbcons command (SAP Note 2222218) in order to collect the current load state:

tablepreload w

See SAP Note 1889081 for more information.

5. When do unloads happen?

The following table lists the main reasons for unloads. You can determine the reason of an unload via column REASON of monitoring view M_CS_UNLOADS.

Reason

Details

LOW MEMORY

SAP HANA automatically performs unloads when memory becomes scarce. This is typically critical for performance and should be avoided whenever possible.

The following SAP HANA parameter globally controls when unloads are performed in case of a high memory utilization:

Parameter	Default	Unit	Details
indexserver.ini -> [memoryobjects] -> unload_lower_bound	0 (default algorithm)	byte	This parameter can be used to define the minimum size of swappable SAP HANA objects that should remain in memory. Higher values result in less and later unloads. See SAP Note 1993128 for more information. Normally the default value provides a reasonable unload behavior and so it should only be adjusted in specific cases.

EXPLICIT

Tables can be unloaded explicitly using the following SQL command:

UNLOAD "<table_name>"

Alternatively you can use SAP HANA Studio for that purpose:

SAP HANA Studio -> <system> -> Catalog -> <schema> -> <table> -> "Unload from memory..."

UNUSED RESOURCE

Automatic unloads when a column exceeds the configured unused retention period:

Parameter	Default	Unit	Details
global.ini -> [memoryobjects] -> unused_retention_period	0 (disabled)	s	Number of seconds after which an unused object can be unloaded
global.ini -> [memoryobjects] -> unused_retention_period_check_interval	7200	s	Check frequency for objects exceeding the retention time

Configuring a retention for unloads typically provides no advantage and increases the risk of unnecessary unloads and loads. Therefore these parameters should only be set in exceptional situations.

MERGE

If a column is loaded and unloaded purely for merge reasons, you will find the unload reason MERGE as of SAP HANA SPS 12. See SAP Note 2057046 for more information related to SAP HANA delta merges.

6. In which sequence are columns unloaded?

Usually unloads happen based on a "least recently used" (LRU) approach, so the columns having not being used for the longest time are unloaded first.

If there are tables that should in general be replaced earlier or later, you can prioritize unloads using the UNLOAD PRIORITY setting:

ALTER TABLE "<table_name>" UNLOAD PRIORITY <priority>

The priority can vary between 0 and 9. Tables with a higher priority are unloaded earlier than tables with a lower priority. SAP HANA considers both the last access time and the unload priority for the proper sequence of unloads, so both factors are important.

The unload priority of a table can be checked via:

SELECT UNLOAD_PRIORITY FROM TABLES WHERE TABLE_NAME = '<table_name>'

7. What are typical unload priorities for tables in SAP environments?

The following table unload priorities are typically used in SAP environments:

Unload priority	Table type	Details
0	Temporary tables System tables	All temporary tables (TABLES.IS_TEMPORARY = 'TRUE', created with NO LOGGING) must not be unloaded and therefore always have unload priority 0. If you want to define a higher unload priority, you receive the following error: SQL error 257: sql syntax error: invalid unload priority for temporary table, only 0 is allowed
5	Default	Per default tables are delivered with a medium unload priority of 5.
7	BW tables (DSO, PSA)	Some BW DSO (/BIC/A) and PSA (/BIC/B) can typically be unloaded earlier than other tables and are therefore configured with unload priority 7. Be aware that it depends significantly on the type of DSO and PSA, so it is normal that there are also DSO and PSA tables with unload priority 5.

You can use SQL: "HANA_Tables_ColumnStore_UnloadPriorities" (SAP Note 1969700) in order to evaluate existing unload priorities and check for tables with unload priorities different from the standard.

8. How can unloads be monitored?

You can monitor unloads in the following way:

Monitoring view M_CS_UNLOADS
SAP HANA Studio -> Administration -> Performance -> Load -> Column Unloads
SQL: "HANA_Tables_ColumnStore_Unloads" (SAP Note 1969700)
SQL: "HANA_LoadHistory_Services" (SAP Note 1969700, SPS 09 and higher)

9. Where is unload information recorded?

Unload information is recorded in unload trace files on disk level. M_CS_UNLOADS is based on these trace files. Typically up to 10 * 10 MB trace files can be written per host and service. As a consequence unload information is still available, even if SAP HANA is restarted.

See SAP Note 2119087 ("Unload trace") for more information.

10. How granular can loads and unloads be performed?

The fines granularities of loads and unloads are:

Mechanism	Loads	Unloads
Manual	per column	per table
Automatic	per column and partition Paged attribute (SAP Note 1871386): per page	per column and partition Paged attribute (SAP Note 1871386): per page

11. How can I check for errors during column loads?

The monitoring view M_CS_LOADS doesn't contain an error information. Whenever a column load is triggered, it is recorded in M_CS_LOADS, independent if it was successful or if it failed (e.g. due to a lack of memory or an inconsistency).

If you want to monitor failed column loads, you can check the trace files for entries starting with "load failed:", e.g.:

[224524]{340032}[291/-1] 2016-02-03 18:37:39.190596 e attributes AttributeValueContainer.cpp(03563) : load failed: exception 1: no.70000000 (AttributeEngine/AttributeStoreFile.h:339)
ste::Exception type AttributeStoreFile error 'ISAPSR3T000AAattribute_203.bin': AttributeEngine: error reading file message additionalInfo $ADDINFO$
exception throw location:
1: 0x00007f43076400c7 in ste::Exception::Exception(char const*, char const*, char const*, int, char const*)+0x53 at Exception.cpp:12 (libhdbbasement.so)
2: 0x00007f42fc5c8e94 in AttributeEngine::AttributeStoreReadFile::throwError(int, char const*, long, bool)+0x130 at AttributeStoreFile.h:142 (libhdbcs.so)

You can use SQL: "HANA_TraceFiles_Content" (TRACE_TEXT = 'load failed:%') available via SAP Note 1969700 for that purpose.

SAP BASIS Crew

Pages