1796393 - SAP BW near-line solution with Sybase IQ

Symptom
This SAP Note delivers the SAP BW-specific near-line storage solution with Sybase IQ.
The solution is documented under the following link:
http://help.sap.com/saphelp_nw73ehp1/helpdata/en/60/cf63e1bbbb49429ee6c35e6ad03a45/content.htm?frameset=/en/4c/75184b167821d1e10000000a42189c/frameset.htm

Other Terms
Archiving, package RSDA_SYB, NLS, near-line storage, Sybase IQ

Reason and Prerequisites
This is an advance development.
Near-line storage for Sybase IQ is released only for BW systems that already run under Unicode. See SAP Note 1737415 (section 3.a) for more details.

Solution
You cannot implement this function as an advance correction using correction instructions (with this SAP Note). For this, import the specified Support Packages:
  • SAP NetWeaver BW 7.30

    Import Support Package 9 for SAP NetWeaver BW 7.30 (SAPKW73009) into your BW system. The Support Package is available when SAP Note 1750249 ("SAPBWNews NW 7.30 BW ABAP SP9"), which describes this Support Package in more detail, is released for customers.
  • SAP NetWeaver BW 7.31 (SAP NW BW 7.0 Enhancement Package 3)

    Import Support Package 7 for SAP NetWeaver BW 7.31 (SAPKW73107) into your BW system. The Support Package is available when SAP Note 1782744 "SAPBWNews NW BW 7.31/7.03 ABAP SP7", which describes this Support Package in more detail, is released for customers.

To implement the solution as an advance correction on:
  • SAP NetWeaver BW 7.00 from SAPKW70028 to SAPKW70030
  • SAP NetWeaver BW 7.01 from SAPKW70110 to SAPKW70113
  • SAP NetWeaver BW 7.02 from SAPKW70210 to SAPKW70214
  • SAP NetWeaver BW 7.30 from SAPKW73005 to SAPKW73008
  • SAP NetWeaver BW 7.31 from SAPKW73102 to SAPKW73106

refer to SAP Note 1858550.


The SAP BW-specific near-line storage solution with Sybase IQ consists of the following components:
    1. ABAP package RSDA_SYB, which contains the implementation of the SAP BW near-line interface
    2. DBSL implementation in the SAP NetWeaver kernel
    3. Sybase IQ Server and SYBASE IQ ODBC NLS client driver The installation of an Sybase IQ server for the SAP BW near-line storage solution is subject to a license.


Installation:
  1.  You can install the ABAP package RSDA_SYB with the specified Support Packages SAP NetWeaver BW 7.30 (SAPKW73009) or SAP
       NetWeaver BW 7.31 (SAPKW73107).

  2.  For the minimum prerequisites regarding the SAP NetWeaver kernel for the above SAP BW releases,
       refer to SAP Note 1737415.
    3.  Sybase
  • The IQ 15.4 or IQ 16.0 server can be downloaded/installed using SAP Service Marketplace (SMP). This is described in SAP Note 1737415. For IQ 16.0 Server SP08, you must also meet the prerequisites stated in SAP Note 2016130.
  • Go to SAP Service Marketplace (SMP) to download/install the SQL Anywhere ODBC client driver for IQ Server Version 15.4 and 16.0. This is described in SAP Note 1737415. Also note the restrictions with regard to the operating system platforms for the Application Server ABAP described further below.
  • The installation and configuration guide of Sybase IQ 15.4 or 16.0 is available at:
    Sybase IQ 15.4:
    http://infocenter.sybase.com/help/index.jsp?docset=/com.sybase.infocenter.help.iq.15.4/title.htm& docSetID=1817.
    Sybase IQ 16.0 SP1:
    http://infocenter.sybase.com/help/index.jsp?workingSet=Sybase%20IQ%2016.0%20SP01
    Also refer to the document "SAP First Guidance - SAP-NLS Solution with Sybase IQ" at https://scn.sap.com/docs/DOC-39627

Note the following restrictions for the SAP BW-specific near-line storage solution with Sybase IQ.

At the moment, the following IQ servers are supported for productive use:
  • 15.4 ESD #3 - IQ SERVER
  • 16.0 SP01 PLXX - 64-bit
  • 16.0 SP04 PLXX - 64-bit
  • 16.0 SP08 PLXX - 64-bit
  • 16.0 SP10 PLXX - 64-bit
PLXX stands for the highest patch level available at the SAP Service Marketplace.
In addition, you must implement SAP Note 1966432 following installation of the IQ Server in BW. With regard to database inconsistencies and IQ server, also see SAP Notes 2023695 and 2088943.
The following restrictions also apply to production use with regard to the operating system platforms for the Application Server ABAP (SYBASE IQ ODBC NLS):
For the IQ Server 15.4, the operating system platforms
  • Linux on x86_64 64bit
  • Windows on x64 64bit
are supported.
For IQ Server 16.0 SP01 PLXX - 64-bit, the following operating system platforms are also supported:
  • AIX 64bit
  • IBM i (aka OS/400)
  • Solaris on SPARC 64-bit 
.
For IQ Server 16.0 SP04 PLXX - 64-bit, the following operating system platform is also supported:
  • HP-UX on IA64 64 bit
are supported.
Also note that the Sybase IQ ODBC NLS client 16.0 SP03 is relevant for the IQ Server 16.0 SP04 PLXX - 64-bit.
For IQ Server 16.0 SP08 PLXX - 64-bit, the following operating system platforms are also supported:
  • Linux on IA32 32-bit
  • Linux on Power 64-bit
  • Solaris on x86 64 64-bit
  • Windows Server on IA32 32-bit
.

If you have successfully installed the above components, you can carry out the BW configuration.

The configuration contains two steps:
  • You use transaction DBCO (table DBCON) to set up the additional second database connection. The configuration is described in SAP Note 1737415.
  • You use transaction RSDANLCON to set up the near-line connection.
See the documentation at:
http://help.sap.com/saphelp_nw73ehp1/helpdata/en/e8/395401e46f4edca50aefeead7f3a44/frameset.htm


Note the following distinction with regard to the SAP Support components:
  • For problems with the ABAP Adapter or with the near-line storage solution in general, use the component BW-WHM-DST-ARC.
  • For problems in the DBSL implementation, use the component BC-DB-SYB.
  • For problems with the SQL Anywhere ODBC client driver or with the Sybase IQ server, use the component BC-SYB-IQ.
Posted by No comments:

Increasing the SAP-NLS Performance

Increasing the SAP-NLS Performance


With the Introduction of smart data access (SDA) especially between SAP HANA and IQ, the data provisioning process can be optimized. Never the less, some additional Parameters have to be introduced on the ABAP and HANA Backend as well.
The Implementation of the SDA between SAP HANA and IQ is already discussed in the - SAP First Guidance - SAP-NLS Solution with SAP IQ | SCN This would be a mandatory task first.

Check the SAP IQ database settings (based on SAP IQ 16.0 SP10)
  • SQL Anywhere Server 17.0 - DB start options
  • IQ PAGE SIZE Parameter Guidelines
  • Catalog Page Size Option
  • Performance Options
  • additional settings for the SQL anywhere interface:
    • -gss:This parameter sets the catalog (Sql Anywhere) stack size for catalog threads. For all 64-bit Unix platforms: Default=Min = 1 MB. Max =  8MB. Considering some large or complex queries ,please set this value to 8MB
    • -iqtss: Specifies the stack size, in KB, for server execution threads running either in the background or as part of a thread team assisting the main server connection thread. The default value is 512KB on 64-bit platforms. However, some very complex queries may return an error indicating that the depth of the stack exceeded this limit, So you may need increase this value to 2048.
    • SAP IQ calculates the stack size of server threads using the formula: ( -gss + -iqtss)

HBD_SDA_configuration.JPG

A good starting point is also the following document which affects the settings for SAP-NLS in the SAP HANA database - Open ODS View on a Virtual Table | SCN and SAP BW on SAP HANA & SAP HANA Smart Data Access

Please Note the selections and navigational attributes are considered with the automatic usage of semi-joins or joint relocations for the execution of queries based on SDA together with HANA and IQ. See the following SCN Document for Details - SAP BW on HANA and HANA Smart Data Access - B... | SCN


Special Features of InfoCubes with Non-Cumulative Key Figures

Archiving data from InfoCubes with non-cumulative key figures is only supported if you are using an SAP HANA database and have connected SAP IQ as a near-line solution using the adapter delivered with the BW system. The InfoCubes must be SAP HANA-optimized. You can convert standard InfoCubes if required. More information:Converting Standard InfoCubes to SAP HANA-Optimized InfoCubes
The mode for non-cumulative processing for this InfoCube must be Y. If your InfoCube is above a certain age, you might have to perform the non-cumulative conversion using report RSDD_SWITCH_NCUM_HANDLING. The data in the InfoCube with non-cumulative key figures that you want to archive must be compressed before an archiving run is started. During compression, the reference point is updated. This ensures that a query on an InfoCube with non-cumulative key figures, whose data has been archived in part, will return consistent results with satisfactory performance.

Optimizing the F4 Help access to SAP-NLS
It is suitable to change the settings for the affected Dimension tables to a more optimized setting. This can be done via the “Provider-Specific” settings for the Dimensions within the InfoCube. By default these options are empty, which means the default settings of the individual InfoObjects for query definition and execution are take place.
Furthermore any reorganization of the dimension tables e.g. via transaction RSRV should be avoided, due to no benefit at all for the application rather to corrupt the F4 access to the SAP-NLS solution and this will result in wrong F4 values and much less performance.

As an example see the following screens for an optimized setting. For further information, please consult thehelp.sap.com pages.
F4-Help_1.JPG
F4-Help_2.JPG
F4-Help_3.JPG


With this SAP Notes two additional parameters were introduced to significantly increase the LOAD statement for writing data into SAP-NLS.
Parameter LOAD_STRIPE_SIZE setting this parameter to a value n > 1 parallelizes the load.
Parameter LOAD_STRIPE_WITH is the parallel degree multiplied with SYBASE_IQ_BUFFER_SIZE
Parameter SYBASE_IQ_LOAD_DIR could be changed at the database connection level (DBCO) and is by default the data directory of the SAP Instance. If you plan to load a large amount of data, please make sure that you have enough space left, or specify another directory/device.
LOAD_STRIPE_SIZE=4; LOAD_STRIPE_WIDTH=4


SAP-NLS_11.JPG



Furthermore there are several SAP Notes discussing the optimization:



The following SAP Corrections are necessary (SAP BW 7.40/7.50):
Note 2130587 - SYB IQ: performance enhancement for LOAD statement (latest ASE LibDBSL 7.22, 7.42, 7.50)
Queries on InfoCubes show poor performance since filter conditions are pushed as "SID-based" filters to the HANA database instead of using the "key-values" for filtering. With "SID-based" filters, the SID-table needs to be joined to the Virtual Table in order to execute the filter. This join adds complexity to the SQL-Statement which makes it more difficult to optimize the query in a federated database environment.


The following SAP Corrections are necessary for the aDSO Support with BW 7.50 SP02:



Currently the following parameters should be changed on the SAP HANA server (included in SP09):

  • semi_join_virtual_table_threshold
  • virtual_table_format
  • join_relocation
  • fda_enabled


HBD_SDA_configuration2.JPG


With HANA 1.0 SP10 (Rev. 10x.x) you should also set the DML Mode to "readonly"


HBD_RES_settings.JPG

Currently the following parameters should be changed on the SAP ABAP server:

  • rsdb/supports_fda_prot = 0
  • rsdb/max_blocking_factor = 50
  • rsdb/max_in_blocking_factor = 1024
  • rsdb/prefer_union_all = 0
  • rsdb/prefer_in_itab_opt = 1
  • rsdb/prefer_join_with_fda = 1



With these actions you can optimize the SAP-NLS solution via SDA:


This note adds a checkbox to the "Near-line Storage" tab of the Data Archiving Process (DAP) maintenance to switch query optimization on (this is the default) or off. You must activate the DAP in order to make your setting effective. If optimization is switched on, but query optimization is not configured or not available for the Near-line Connection query access will use the non-optimized implementation via the VirtualProvider interface utilizing the Near-line Provider implementation via the standard Near-line Interface. In case Smart Data Access is configured for the Near-line Connection also the name of the HANA Virtual Table is shown.

SAP-NLS_3.JPG

SAP-NLS_4.JPG



SAP-NLS_5.JPG

SAP-NLS_6.JPG


The Report RSSDA_CREATE_TABLE_STAT can be used to create database statistics for HANA Virtual Tables. HANA Virtual Tables are used in the context of HANA Smart Data Access. The execution time can be quite time consuming. As of HANA SP10, a new statistics type RECORD COUNT is available for virtual tables. RECORD COUNT specifies that only the number of records is computed. This type of statistics should take much less time to compute compared to the other types SIMPLE or HISTOGRAM. From query execution point of view, the HANA Query Optimizer however have less information which could lead to less optimized query execution. This type should therefore only be used in case it's too expensive to create SIMPLE or HISTOGRAM statistics.

SAP-NLS_7.JPG

SAP-NLS_8.JPG
Posted by No comments:
Subscribe to: Posts (Atom)