Near Line Storage With Sybase IQ 16.1
Why NLS and Key concepts of SAP IQ?
Near Line Storage (NLS) is a practise that is used to archive the cold and frozen data from BW to its nearest data store called Sybase IQ. This practice not only reduces the data loads on BW systems, but also makes sure that the archived data is available through queries, thereby helping in better performance. The data compressed and stored through NLS to Sybase IQ server is less expensive in storage capacity compared to BW database. This also reduces the maintenance cost for handling BW on both HANA or Non-HANA databases by offloading the data from primary database to secondary database with robust data analytics capabilities, easy administration, best performance, and scalability in handling large data volumes.
SAP IQ is a highly optimized analytics server designed to deliver superior performance for all critical data warehouse, business intelligence, and analytical solutions. Some of the Key capabilities of SAP Sybase IQ are Fast data ingest, Industry leading query engine, Data compression, Scale out, Security, Low TCO, Extensibility and Elasticity.
Offloading data should be done outside business hours or scheduled as housekeeping jobs which should be done with minimal business impact. The below diagram clearly explains about the usage of BW data on both HANA and Non-HANA databases, where the data is classified into three types – cold, warm and hot. NLS is done only for the cold and warm data liable to the customer’s requirements. Hot data is referred to as active data, which has to be accessible on a permanent basis for read and write processes. This data is stored in the main memory of the HANA DB, whereas the warm data is not accessed quite often. The final data class is cold data, with which NLS is related and those data for which no more updates are anticipated in BW that is rarely used. This data will be detached in time slices to the Sybase-IQ DB through NLS concept.
How to implement NLS and its Functionalities?
When you are ready with a new hardware (Note: Information related hardware spec and OS version dependencies for IQ server installation can be viewed under PAM in SAP support portal), start installing the “Sybase IQ server, SAP Note 2551457 “SAP IQ 16.1 SP 03 Supported Operating Systems and Versions”. Below images have got all the details required for IQ server 16.1 SP03 installation.
Sybase IQ server installation involves the following components by default.
IQ Server, IQ Client, IQ ODBC drivers, IQ Web drivers, IQ Cockpit& framework, SAP jConnect for IQ, SySam License Server & Utilities, additional connectivity language modules, remote command & control agent for SAP IQ.
SAP has provided Sybase IQ server as 30 days free trial version. This needs additional SySam licenses for activating the NLS services for archiving data.
Note – for any installation errors start with ‘IQ_suite.log’ file then with specific component log files for detailed error info
Basically, installation of Sybase IQ server is of two types – ‘custom’ & ‘typical’. It is better to choose custom installation for more detailed installation component input. Sybase IQ server installation will also install the required SAP IQ client software on the same machine. Also, ensure to install the Sybase IQ client software where the primary database alias BW instance is located. Start the installation by running the “setup.exe” file in normal mode. We can also start the installation in either command prompt mode or also in unattended mode. After successful installation in SAP IQ, there will be two databases – Utility DB and Demo DB by default and optional. Start the Demo and Utility DBs, then create a test service on the top which creates a database for NLS implementation. The native BW source system is connected to SAP IQ DB for NLS solution via database connection defined in DBCO. This can be tested by running report “ADBC_TEST_CONNECTION”. For this successful test connection, install SAP IQ client in BW server.
The following table explains the types of environments and where to have SAP IQ client.
|If you are||You need||Example|
|having a web client application||SAP IQ client on the application server’s machine. No need of client on machine where the browser runs.||Install IQ client on laptop if you have the web client app on a client machine/laptop to run server queries on Linux.|
|Running queries against remote data sources. If data from two SAP IQ databases is on different platforms||An SAP IQ client on the laptop/server where you are running queries. Install SAP IQ server and an SAP IQ client on the same server so now Remote data accessed through the client will be queried as a proxy table.||While running queries on Linux server and accessing data stored on both the Linux server and an HP server, install SAP IQ client on Linux.|
|If your Application server & IQ ARE placed in two different hosts||SAP IQ client on application server too.||If the client applications are on the Windows platform to access data in an SAP IQ server on Linux, install SAP IQ client on Windows.|
After installation, there will be few commands involved in post configuration for applying licenses to prevent authorization issues, while accessing NLS DB on SAP BW. SAP provides KBAs instructions on how to execute commands for applying licenses and allowing authorization to read or write data in NLS DB from BW instance. NLS with SAP IQ server has quick access to archived data due to its vertical storage and multiplex capability of managing big queries and their loads in multi-server configuration. It has fully flexible schema support. When it comes to storage, the entire database and its indexing is stored in less space than the raw data.
Process of moving data to IQ Server
Once the connection from BW to NLS DB is established successfully, start archiving the required data which is not necessary to be stored on primary DB. Archiving can be done based on tables, object, modules, and so on. Here in BW system, start archiving with Creating Data Archiving Process (DAP) for any Info Provider, where you can also enable option here for compressing the data while archiving in transaction ‘RSA1’. Prevent most common errors while archiving by applying required SAP KBAs for authorization and licensing issues. General settings, selection profile, semantic group and NLS are the four-tab settings that are involved in data archiving. Disable ADK-based archiving in general settings tab, whereas in selection profile tab choose primary DAP. The time frame for data to be archived, primary DAP cannot be changed. One must recreate it, if required. Semantic tab is useful for adjusting sort order. At last, query optimization and sizing of data packages can be done in near-line storage tab.
Stragglers Issue: This is similar to the locks in SAP, which we are aware of. When the back postings are done on the same data which is already being archived, the system will display lock issue which is called Stragglers issue. As the data is already locked in archiving request, with the request number in error, we can reload the data from NLS and fix the delta request in archiving tab for info providers.
Reloading data from Sybase IQ server to HANA DB: The process of reloading data from SAP Sybase IQ database (secondary DB) to HANA or Non HANA DB (primary DB) is as follows – Select the info provider -> go to manage tab -> Archiving request -> double click on the request which you want to reload by checking the selection condition. Then a new window pops up, select the reload radio button and run the job in the background. Always make sure to either reload or archive the data in the background during non-business hours.
Changes to DSO or cube: Adding new field to an info provider deactivates an associated DAP. Reactivating DAP adds the new field to the NLS table only when the added field is empty. If the added field is not left empty, then we must reload the data, update new field, and archive the data again. DAP activation can be included as part of the transport to adjust in QA and Production environments.
Checking Archived Data: To make an existing BeX query to read data from NLS DB, set Near Line storage data option to ‘Read’. Use transaction ‘LISTCUBE’ for checking the archived data from call list viewer. For info providers, we can read the data by calling the selected info provider name with option DB aggregation. List of queries with NLS enabled are as follows:
Z_DSC0036_SKU_RAT_NA_CONS_1, Z_DSC0036_SKU_RAT_CONS_1, Z_DSC0036_SKU_RAT_0010_CONS_1, Z_DSC0036_SKU_RAT_NA_CONS_EO, Z_GSMG02_MULTICUBE, Z_GSMG02_GS02_FORECAST, Z_GSMG02_GS02_FORECAST_1, Z_GSMG02_GS02_FORECAST_2, Z_GSMG02_GS02_FORECAST_3, Z_GSMG02_GS02_FORECAST_8, Z_DSC0005_ISFLD_PLNARIV_RECSHP, Z_DSC0005_ISFLD_RECSHP_USO, Z_DSC0005_ISFLD_USO_ASN, Z_DSC0005_ISFLD_PLANARRIV_ASN, Z_DSC0005_ISFLD_PLNARRIV_USO, Z_DSC0005_NO_XFACTORY_COST, Z_MMC001_MARD_INV_QLIK, Z_DSO001_OPEN_USOS, Z_DSO005_USOS_DOC_DETAIL_INF
SAP NLS Solutions
Various NLS solutions are available for SAP BW such as IBM DB6-NLS, PBS Software, and Datavard which are the benchmarks of SAP NLS chargeable solutions. Based on the complexity, navigational attributes, hierarchies and formulae used for query search. It will take time to show the data placed in IQ server using NLS solution.
Key principles of SAP IQ server are that it stores data in column wise rather than row wise which helps in recurring only those columns which are necessary for query by reducing I/O. SAP IQ might look like another RDBS, as it is integrated with open client and open server to the end user. But, SAP IQ is not like any other database. It can load data from variety of sources and saves lot of storage by indexing whose size is kept small by different data compression methods. SAP IQ saves lot of disk space by saving storage through fully indexing database than saving on raw data.
Sybase IQ is a dominant query server designed to increase the speed of query performance against large data. The features of Sybase IQ are vertical or column data storage, supported by Sybase central and optimized access methods for all types of data. The main advantage of Sybase IQ is its speed while accessing data when compared to all other databases. It is quicker to implement, as it allows multiple server configurations and handles large amount of data. It can also load the data when users are querying.
MOURI Tech Strategy
MOURI Tech will come up with a strategy based on the customer’s inputs and requirements. Our team will design the road-map and implementation plan considering all the pros & cons. Case studies with our past clients definitely explains how best we have handled implementations and managed services on various business verticals. We have vast experience in SAP BW, Basis, HANA and Sybase to work on effective implementation for NLS in any landscape with low expenses, high returns and higher reliability.
- SAP NLS with SAP IQ Guide – https://www.sap.com/documents/2013/03/d2ff3a4c-577c-0010-82c7-eda71af511fa.html
Contact for further details
Sai Kumar DHUDALA
Sr. SAP Technical Consultant – SAP BASIS & HANA