white paper on data archiving using near line storage on...

White Paper on Data Archiving using Near Line Storage on SAP

SybaseIQ

Configuration and Installation of SybaseIQ on Client Systems

By

Pavan Mulpur ([email protected])

Project Manager - SAP BW, Certified SAP HANA Consultant

GrayMatter Software Services Pvt. Ltd. 4th Floor, West Wing, RMZ Ecoworld SEZ,

Outer Ring Road, Bangalore – 560103, India

mailto:[email protected]

Copyrights © 2015 GrayMatter Software Services Pvt. Ltd 2

Table of Contents

1.0 Introduction………………………………………………………………………………………………………………………..4

1.1 What is Data Archiving………………………………………………………………………………………………………..4

1.2 What is Near Line Storage……………………………………………………………………………………………………5

1.3 Data Archiving Process…………………………………………………………………………………………………………7

1.4 System requirement for Data Archiving ………………………………………………………………………………7

2.0 Configuration and installation of SybaseIQ database for NLS……………………………………………….8

3.0 References………………………………………………………………………………………………………………………….33


Paper Title

Data Archiving using Near Line Storage on SAP SybaseIQ

Configuration and Installation of SybaseIQ on Client Systems

Author

Pavan Mulpur, Project Manager - SAP BW & SAP HANA

GrayMatter Software Services, Bangalore

November 23, 2015

Content Overview: This white paper gives an overview about data archiving, Near Line

Storage, how data archiving using Near Line Storage helps in

reducing cost for the business and what are the steps required to

configure and install SAP SybaseIQ in order to perform data

archiving using Near Line Storage on SAP BW.


Data Archiving using Near Line Storage on SAP SybaseIQ

1.0 Introduction: The objective of this white paper is to introduce to the concept of data archiving

in general and more in particular per se data archiving mechanism within in SAP BW using the concept

of Near Line Storage mechanism on SybaseIQ. The version for SAP BW referred to in this paper is

SAP BW 7.40 in general and for SybaseIQ the version is 15.4/16.0. The main focus of this paper is on

how data is archived in SAP BW with SybaseIQ as a database for archiving laying equal emphasis on

the configuration and installation of SybaseIQ for the purpose of data archiving.

1.1 What is Data Archiving?

Data Archiving – a service provided by SAP NetWeaver – removes mass data that the system no longer

needs online, but which must still be accessible at a later date if required, from the database. The

following graphic illustrates the archiving process: Archiving objects are used to write documents to

archive files, which can be stored on other media.

(Source:http://help.sap.com/saphelp_nw70/helpdata/en/8d/3e4c11462a11d189000000e8323d3a/frameset.htm)

http://help.sap.com/saphelp_nw70/helpdata/en/8d/3e4c11462a11d189000000e8323d3a/frameset.htm


Data in the database can only be archived using archiving objects, which describe the data structure

and context.

Financial Accounting documents are archived using the archiving object

FI_DOCUMNT. It includes the document header, company code-dependent postings,

change documents, SAPscript texts, and other elements.

In addition to the aforementioned technology SAP also offers XML-based archiving, as of SAP Web

Application Server 6.40. With this new procedure XML archiving objects are used to write data in the

form of resources either to a file system or directly to a WebDAV system, which takes on the role of

the storage system shown in the figure above.

1.2 What is Near Line Storage (NLS)?

Near Line Storage is an application from a third party vendor, running on a separate system. The near

line data is stored in compressed form with fewer backups and offers reduction in costs for the data that

is accessed less frequently. How does it offer reduction in costs? Let us take a situation of a client with

50 terabytes of data in their entire data warehouse. Now let us say the client identifies that 25 terabytes

of their entire data is historical and that they do not need to access this data on a daily basis. So instead

of storing 25 terabytes of data on their regular database which occupies a huge amount of database

space, if they can compress this data its storage space will also come down thus bringing down the cost

of storage.

With reference to data archiving SAP has classified data into three classes viz., Hot, Warm and Cold.

“Active data that has to be accessible on a permanent basis for read and write processes is referred to

as ‘hot’ data. This data is stored in the main memory of the HANA DB. Warm data which is not

accessed quite so often is best stored in the HANA DB file system. Whenever this data is loaded into

the main memory for processing, it is displaced with higher priority once processing has finished. The

final data class is the cold data, with which NLS is associated. Cold data is rarely used and for which

no more updates are anticipated in BW. This data is removed in time slices to the SybaseIQ DB.” (SAP

NetWeaver BW Near Line Storage solution based on SybaseIQ)


Figure 1 showing the classification of data into hot, warm and cold and how the data is stored in SAP

BW. (Source: SAP Netweaver BW Near Line Solution (NLS) based on SybaseIQ, Michaela Pastor,

April 4, 2013 http://scn.sap.com/docs/DOC-39944)

NLS allows SQL-based direct access for reporting and ETL and extracted NLS partitions are deleted

in RDBMS after archiving. The primary criteria of partitioning data in NLS is by time/age. The

compression rate of data using NLS is upto 90% and it can handle large data volumes with minimal

administrative effort. This type of archiving is very much suitable for ad-hoc queries with long history.

Certain key facts about NLS interface are:

NLS should be part of a data-aging strategy

Based on well-established SAP/BW archiving concepts

Data consistency guaranteed before deleting the data from source

Data archived in NLS can be incorporated into reporting

Saves storage costs and other system resources

High compression rate up to 90% (not on top of HANA compression)

http://scn.sap.com/docs/DOC-39944


Increases retention period for analysis data

Included in the query statistic data collection (RSRT)

Supports archiving of InfoCubes and DataStore Objects

Mainly time-based archiving, but can also be based on other characteristics

Lock of the archived time slices in the original InfoProvider

NLS process steps in process chain handling

Copes with changes in the metadata to the BW objects of the archived data

Deletion of NLS partitions supported

Direct load from ADK archive files into NLS supported

1.3 What do you understand by the term Data Archiving Process (DAP)?

Data Archiving is a process of offloading the unused/inactive/dormant data from the online database

and store it in an alternate storage system, such as magnetic tapes or hard disks. Archiving has many

advantages like performance improvement, availability of storage space, reduced maintenance costs,

etc. Though, archiving has advantages, organizations cannot archive as they please. An organization

needs to have data on the database to a certain period of time before it is archived in order to meet some

legal and government requirements.

The archived data can be made available upon request. In order to make the archived data available it

has to be re-loaded in to the online database. But, with NetWeaver 2004s, a new method of archiving

called NearLine Storage has come into existence. A NearLine Storage acts as an intermediate solution

between a traditional archiving and an online database. Using a NearLine Storage would allow us to

have access to the archived data without the need of reloading the data to online database.

Archiving data process can be created using the context menu on the infoprovider we choose to archive.

In the archive data process we define the fields from the infoprovider which are used for data selections,

structure of the Archive file, archive file location, etc. Once thearchive data process is set up, we can

start the archive process using the transaction SARA. It is possible to write the archive file and delete

the archived data from database in two separate steps or it can be done in one single step by using an


option while defining the archive data process. Using the logs and statistics from transaction SARA

detailed information about the archiving session can be obtained.

(Source: http://wiki.scn.sap.com/wiki/display/BI/Data+Archiving+-+A+Brief+Introduction)

1.4 System requirement for Data Archiving using SybaseIQ: For data archiving using

SybaseIQ as database the required version is 15.4/16.0 and the SAP BW version has to be SAP BW

7.30 SP9/ SAP BW 7.31 SP 7/ SAP BW 7.40 SP 5.

In the subsequent pages I’ll be discussing about the configuration and installation of SybaseIQ in order

to perform the data archiving.

2.0 Configuration and Installation of SybaseIQ database for NLS on SAP BW

system

Introduction: The subsequent pages demonstrate the steps required to install and configure SybaseIQ

for Near Line Storage on SAP BW systems. The prerequisite for installation of SybaseIQ is that file

system structure has to be in place. The following table shows the amount of disk space to be allocated

on the server for each of the volumes.

Volume File System Name Minimum Size

Volume1:

/sybase/<SID>_NLS 30GB

/sybase/<SID>_NLS/data 15GB

/sybase/<SID>_NLS/nlslog_1 5GB

Volume2:

http://wiki.scn.sap.com/wiki/display/BI/Data+Archiving+-+A+Brief+Introduction


/sybase/<SID>_NLS/nlsdata_1 100GB

/sybase/<SID>_NLS/nlsdata_x 100GB

Volume3:

/sybase/<SID>_NLS/nlstemp_1 30GB

Volume4:

/sybase/<SID>_NLS/shmdump 10GB

Volume5:

/sybase/<SID>_NLS/nlsarchive_1 200GB

NZQ implementations:

=================

A. Prerequisites:

1. Check RAM and CPU size for different systems like for NTQ_NLS

2. File system structures verifications as per MCD standards.

Volume File System Name Minimum Size

Volume1:


/sybase/<SID>_NLS 30GB

/sybase/<SID>_NLS/data 15GB

/sybase/<SID>_NLS/nlslog_1 5GB

Volume2:

/sybase/<SID>_NLS/nlsdata_1 100GB

/sybase/<SID>_NLS/nlsdata_x 100GB

Volume3:

/sybase/<SID>_NLS/nlstemp_1 30GB

Volume4:

/sybase/<SID>_NLS/shmdump 10GB

Volume5:

/sybase/<SID>_NLS/nlsarchive_1 200GB

The file systems should be with huge page disabled and direct io enabled.

The above size details are for minimum requirement. The actual size of nlsdata_* is based on the

database size during live.


Once the file system structure is in place then we move on to the subsequent system in installing

SybaseIQ. The steps for the same are given in subsequent pages along with screenshots.


B. IQ installations: (if it is already installed, continue with below steps: given below)

Manually installing IQ

Hit Enter key to continue.



Enter the $SYBASE path

Enter ‘Y’



Enter 1

Enter 1


Choose the region 10

Type back


Enter Y

Enter 1

Enter the location where the license file is copied on the host

Enter 1


Enter 1

Enter the value N


Check the preinstallation Summary and Press Enter



You can see the progress here

Press enter for all the inputs above


Choose 2 to not install SCC

We can choose ‘N’ here

Now the installation of software on the host is completed.


Create .login file in $SYBASE directory as above. Please replace NZQ with your SID

Logout and login once

You should see that SYBASE.csh file is sourced now

start_iq -n util_$HOSTNAME -su abcd123


Utility database is started now

You can check by connecting to the utility database as above

Execute the sql statement 0CreateDB.SQL by passing the password for the dba account in [] bracket


Stop the utility database using stop_iq command (alias)

Create directories lis, html inside /Sybase/<SID>_NLS/data and copy the file IQ_ <SID>_NLS.opt

inside data directory

Create a directory called satmp inside nlstemp_1

Create a directory called NLS_<SID> inside nlsdata_1 as above


Start the IQ Server as above

Now the IQ database server is started successfully


Execute the scripts as above

In the above query, you can see the event ScheduledIncSFullBackup is scheduled. But this will fail till

the full backup is done. As the full backup is npot yet scheduled, let us disable this job.


C. Creating a database: On the successful completion of the installation of SybaseIQ, the next

step is to create the database. The following steps illustrate the same.

Connect to utility_db:

start_iq -n util $HOSTNAME -su abcd123

start_iq -n util_hec01v001223 -su abcd123

Utility database is started now


You can check by connecting to the utility database as below:-

Execute the sql statement 0CreateDB.SQL by passing the password for the dba account in [] bracket

This sql is used to create the IQ database.

Stop the utility database using stop_iq command (alias)


Create directories lis, html inside /Sybase/<SID>_NLS/data and copy the file IQ_ <SID>_NLS.opt

inside data directory

Create a directory called satmp inside nlstemp_1

Create a directory called NLS_<SID> inside nlsdata_1 as above

Start the IQ Server as below


Now the IQ database server is started successfully


Execute the scripts as below.

Kindly note that make change in the below SQL’s as appropriate ( Change the SID to the current one

that is being created)

This command is used to define User Defined DB spaces.

This script is used for customized

stored procedures

The above script is used to take a backup of stored procedures.

C. IQ page size verifications: If page size is not met with MCD standards, drop the database and

recreate with required page size. (This is optional step, use only if database is already created with

incompliant page size).

Shutdown the IQ server and connect to utility database and use below command to drop the database.

Drop database ‘xxxx.db’

This completes the creation of the database. The next step is to proceed with the post installation steps

which are mentioned in the following pages.


D. Post installation steps:

sp__iqdbsapce sql

patch upgrade

Script scheduling

1. To go into the server level give the following command typed in italics:

hec01v001244 /sybase/NZQ_NLS> dbisql -c

"servername=NZQ_NLS;uid=dba;pwd=HmKf1667;dbn=NZQ_NLS" –nogui

2. The below command is used to create a customized stored procedure

dbisql -c "servername=NZQ_NLS;uid=dba;pwd=HmKf1667;dbn=NZQ_NLS" -nogui read

4Tools.SQL

3. The below command schedules backup

dbisql -c "servername=NZQ_NLS;uid=dba;pwd=HmKf1667;dbn=NZQ_NLS" -nogui read

5dBackupSupplement.SQL

4. Command to change the password:

(DBA)> sp_iqpassword abcd123,HmKf1646

1 row(s) affected

5. To Grant DBA role to USER the command is

Grant DBA to username


3.0 References

1. SAP First Guidance… SAP BW: Implementation SAP-NLS/SDA with SAP IQ 16.0 -

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/301395ac-c476-3010-

1d84-802a2f12f530?QuickLink=index&overridelayout=true&59824599468455

2. SAP NetWeaver BW Near Line Storage solution (NLS) based on SybaseIQ - Michaela Pastor,

May 3, 2013 - http://scn.sap.com/docs/DOC-39944

3. Data Archiving Process in SAP BW – Vibha CJ, August 2012 - http://scn.sap.com/docs/DOC-

30539

4. SAP BW Near Line Storage (NLS) Implementation for SybaseIQ – Rainer Uhle, July 2013 -

https://scn.sap.com/docs/DOC-40055

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/301395ac-c476-3010-1d84-802a2f12f530?QuickLink=index&overridelayout=true&59824599468455

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/301395ac-c476-3010-1d84-802a2f12f530?QuickLink=index&overridelayout=true&59824599468455




https://scn.sap.com/docs/DOC-40055

white paper on data archiving using near line storage on...

Documents