SAP ILM with SAP IQ Database
source link: https://blogs.sap.com/2021/02/17/sap-ilm-store-based-on-sap-iq-database/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Last Changed: 12th of August 2022
SAP ILM with SAP IQ Database
Blog Content
Manage information through its lifecycle while balancing cost, risk, and compliance.
Streamline your IT infrastructure and protect the privacy rights of your consumer data with the SAP Information Lifecycle Management (SAP ILM) component. You can automate data archiving and retention, as well as the decommissioning of legacy systems, while balancing the total cost of ownership, risk, and legal compliance.
SAP ILM Framework
While the nearline storage functionality for the SAP Analytical (OLAP) based Systems like SAP BW and BW/4 with SAP IQ Database is well established, the usage of the SAP IQ Database for the general storage of BLOB, ILM-controlled archive files and archive indexes from transactional (OLTP) Systems like R/3, ERP or S/4 Data is also suitable option to use.
So let’s optimize the SAP ILM Store Setup with the experiences and strengths of the SAP-NLS Implementation known from SAP BW and BW/4.
Keep in Mind that SAP IQ is not the primary Database here, where the Application Data is stored, it is the secondary Database where the mentioned Objects from above stored for anytime fast access outside the primary Database for shrinking the main Database, e.g. SAP HANA. SAP IQ can compress the SAP HANA Data up to 80% in size due to the column based compression logic.
Blog from Mark Mumy (SAP) – Should you archive both BW (or BW/4HANA) and ERP (pre-S/4HANA) on the same instance of SAP IQ?
Short Answer: No – Note 3085053 – Landscape Considerations for NLS and ILM
SAP ILM – Documentation and Information
SAP ILM Detailed Presentation (PDF)
SAP ILM Origins
SAP ILM Help – SAP ILM Implementation Guide – NW ILM Store – SAP Information Lifecycle Management in SAP S/4HANA
SAP Customer Activity Repository (CAR) – Install and Set Up Integration with SAP IQ
SAP Solution Brief – SAP ILM – ILM Store Configuration –
Note 2222074 – Collective information regarding ADK and AIS (Archive Development Kit and Archive Information System)
Note 2705663 – Collective information regarding ILM (Information Lifecycle Management)
despite of SAP ILM or SAP-NLS,
it is all about the SAP IQ Database Setup
Note 3017355 – SAP IQ 16.1 SP04 PLx – correct SAPIQDB.cfg settings
Note 3094594 – Enable SAP IQ for DBACOCKPIT
Note 3085053 – Landscape Considerations for NLS and ILM
Note 3119008 – Configure SAP IQ and HANA for SDA/ODBC
Note 3196447 – troubleshooting an existing SAP IQ 16.x Implementation for NLS/DTO and ILM
The IQ implementation is the same as for NLS, so the following Note is relevant for ILM as well.
Note 2780668 – SAP First Guidance – BW NLS Implementation with SAP IQ
From an IQ License perspective, ILM needs the same license options (bundled) like the NLS use case (technically speaking)
Note 2372218 – Requisite IQ licenses for SAP-NLS solution – SAP BW with SAP IQ Nearline-Storage
Note 3159879 – ILM licence information and validity
However, getting a working ILM License for IQ needs the following options (separately purchased):
– IQ_CORE – SAP IQ Enterprise Edition/AS Platform Edition EE/CP (X Core)
– IQ_UDA – SAP IQ Unstructured Data Analytics Option (usage = 1)
– IQ_UDF – SAP IQ Unstructured Data Analytics Option (usage = 1)
– IQ_VLDBMGMT – SAP IQ Very Large Database Management Option (max. X TB)
sybase@server:/usr/sap/<SID>/data/db>
dbisql -c "uid=SAP<SID>USR;pwd=Sybase4me;dsn=hostname_<SID>_<nr>" -nogui
(SAPSQ3USR)> sp_iqlmconfig;
Property Value
--------------------------------------------------------
Licence Notice
Edition EE
License Type CP
Application Type null
IQ_CORE License Count in use 16 (CPU core based)
Optional license in use : IQ_UDA Yes(Allowed)
Optional license in use : IQ_LOB Yes(Allowed)
Optional license in use: IQ_SECURITY No (Allowed)
Optional license in use: IQ_MPXNODE No
Optional license in use: IQ_VLDBMGMT Yes(Allowed)
IQ_VLDBMGMT License Count in use 1 (Max Allowed : 4)
Optional license in use: IQ_UDF No (Allowed)
Optional license in use: IQ_IDA No (Allowed)
Optional license in use: IQ_URIDA No (Allowed)
Email Severity NONE
SMTP Host smtp
SMTP Port 25
Email Sender
Email Recipients
(19 rows)
(SAPSQ3USR)>
Just for the Record: the following SAP Notes are obsolete/not necessary, when the SAP IQ Database is installed in the mentioned way above. (Components BC-SYB-IQ, BC-ILM-STO)
Note 2161052 – ILM Store: Free connection types
Note 2257318 – ILM Store: Lock Exception using IQ
Note 2284492 – ILM Store: Message “Connection limit exceeded” on IQ
Note 2414314 – IQ: PUT succeeds, GET fails with Row not found
Note 2478703 – ILM Store – IQ: Put Succeeds, GET fails with Row not found for the Streaming Case
Note 2634053 – SAP ILM and Sybase IQ storage
Note 2705663 – Collective information regarding ILM (Information Lifecycle Management)
Note 2748058 – During ILM storage “DETERMINE_PUT_TARGET” error occurs in SLG1
Note 2785785 – DAP ends with IQ Error SQL-131 Syntax error near ‘(end of line)’
Note 2806115 – Error binary data not supported on data longer than 32767 Bind host variable – SAP ILM and IQ
Note 2942648 – ILM store: error during storage of files with size greater than the streaming size
Note 2969212 – ILM : Error while deleting documents from ILM store set up as remote content repository
these are some corrections in relation to SAP IQ (Components BC-CCM-ADK, BC-ILM)
Note 3010127 – Dump CX_ILM_STOR_EXCEPTION during report RILM_STOR_TEST_PF_SINGLE
Note 3010305 – IQ: Structure tables are not filled on SAP IQ during setup of inactive infostructures
Note 3017579 – DBACOCKPIT: Monitoring multi sys using the SQL Statements cockpit action
Update June 2021 – Improvements when testing the ILM Functionality
SAP IQ Database Setup for ILM
Consolidate Your Archived Data on SAP Sybase IQ
Blog – Q – the easy Installer for SAP IQ (for the Installer contact [email protected])
Installation of the SAP IQ Database with three Values, only SDA/ODBC Setup needed.
Q – the easy SAP IQ database installer
smart data access (SDA) for SAP ILM
Report ADBC_TEST_CONNECTION
there is one little addition to the DBCO setup of the ILM Store:
DBCO Values for ILM
SYBASE_SERVER=hostname.fqdn SYBASE_PORT=3<nr>38 SYBASE_IQ_ENGINE=hostname_<SID>_<nr>
SYBASE_DBNAME=SAPIQDB SYBASE_IQ_LOAD_SIDE_FILES=1 SYBASE_IQ_LOAD_DIR=/sapmnt/IQ
SYBASE_CONTYPE=IQ SYBASE_IQ_LOAD=1 SYBASE_IQ_CESU-8=1 SIQ_BULK_OP=1
SYBASE_IQ_LOCKWAIT=600 SYBASE_IQ_BUFFER_SIZE=500000
sybase@server:/usr/sap/<SID>/data/db>
iqdsn -y -w "hostname_<SID>_<nr>" -c "UID=DBA;PWD=Sybase4me;ServerName=hostname_<SID>_<nr>;
LINKS=tcpip(host=hostname.fqdn;port=3<nr>38)"
Configuration "hostname_<SID>_<nr>" written to file /usr/sap/<SID>/home/.odbc.ini
sybase@server:/usr/sap/<SID>/data/db> dbping -d -c dsn=hostname_<SID>_<nr>
Connected to SQL Anywhere 16.1.50.1949 server
Ping database successful.
sybase@server:/usr/sap/<SID>/data/db>
dbisql -c "uid=SAP<SID>USR;pwd=Sybase4me;dsn=hostname_<SID>_<nr>" -nogui
(SAP<SID>USR>)
sp_iqlmconfig;
sp_iqstatus;
sp_iqcheckoptions;
sp_displayroles;
sp_iqdbspaceobjectinfo USER0001;
SAP IQ Monitoring with DBACOCKPIT
Blog – How to keep Enterprise Flash Applications accessible in 2021
Blog – enable SAP IQ for DBACOCKPIT
Note 3094594 – Enable SAP IQ for DBACOCKPIT
necessary user roles for SAP<SID>USR
tx. DBACOCKPIT – define the SAP IQ Database
tx. DBACOCKPIT – SAP IQ Database availability
tx. DBACOCKPIT – overview SAPSQ205 IQ Database
tx. DBACOCKPIT – SAPSQ205 IQ Database Options
tx. DBACOCKPIT – SAPSQ205 – IQ Database Tables
Setup SAP Information Lifecycle Management
First of all, you have to activate the SAP ILM Business Function with tx. SFW5 or maintain with tx. SFW2
SAP Help – Prerequisites for Using the ILM Store
Please Note, you have to use a Web based Method to do changes in the Business Framework. you can use the WebGUI for that task.
Note 2900689 – SAPGUI for HTML. WebGUI logon not working on S/4 Hana 1909
Business function FIN_ACC_ILM is prerequisite for business function ILM_CDE_CONT_SAP_APPL
activate the ILM Database Store (ILM_STOR)
For the ILM Store usage there are several SAP Roles available. For a detailed description and the use of the authorization objects, you can refer to the ILM Implementation Guide.
Roles for the usage of ILM
Configuration of the SAP ILM Framework
as already mentioned, we are focusing here only on the usage of the SAP IQ Database together with ILM (no Azure BLOB, Hadoop or NFS will be covered).
Note 2563024 – ILM Store – Collection of Notes
Note 2243603 – ILM Store: Utility for adding Connection IDs to Class Factory
create an Entry in the Table TILMSTOR_CF for the created SAP IQ Database with the Report RILM_STOR_CF_DBCON
Table: TILMSTOR_CF
Interface: IF_ILM_STOR_DB_OPERATOR
for SAP IQ: CL_ILM_STOR_DB_OPERATOR_IQ
Report RILM_STOR_CF_DBCON for DBCON
SM30 – Table TILMSTOR_CF
Please Note, the Customizing Settings for the tx. SARI and SARA are only available, when the activation of the SAP ILM Business Function was successfully.
customizing tx. SARI
customizing tx. SARA – 1-2
customizing tx. SARA – 2-2
Note 2544517 – Archive administration: Direct navigation to ArchiveFileBrowser
Note 2607963 – How to find the relationship between table and archive object
tx. AS_AFB (SARA)
Define the Origin Concept for SAP ILM
Origin Concept
The Origin supports the ILM Store in determining the data origin and is a central element of the configuration of the ILM Store. Basically you define in this two Transaction from were the Data is coming from, and were it will be stored. In our Case of course the defined SAP IQ Database.
Details of creation the Origin can be found in the ILM Implementation Guide. Additional hints can be found in the Blog from Neenu Susan George – ILM Store Configuration
Note 3068508 – Simplified transaction (ILM_STOR_GEN_CUST) for ILM Store customizing
- ILM_STOR_ADM_CUST (ILM Store Administrative Customizing)
- ILM_STOR_OPR_CUST (ILM Store Operational Customizing)
- Report RILM_STOR_GENERATE_CUSTOMIZING (ILM_STOR_GEN_CUST)
tx. ILM_STOR_GEN_CUST
tx. ILM_STOR_ADM_CUST
tx. ILM_STOR_OPR_CUST
tx SM30 – Table TILM_STOR_CUS
tx. SM30 – Table TILM_STOR_O_ROUT
tx. SM30 – Table TILM_STOR_CUS_O
tx. SM30 – Table TILM_STOR_CUS_P
Setup and Publishing of the SAP ILM Store
Details of the Publishing of the ILM Store can be found in the ILM Implementation Guide.
Additional hints can also be found in the Blog from Neenu Susan George – ILM Store Configuration
- tx. SICF (HTTP Service Hierarchy)
- tx. SM59 (RFC Destination)
- tx. ILMSTOREADM (access with WebGUI)
Note 3027603 – Error “401 Unauthorized” in ILMSTOREADM while adding an ILM Store
Note 3044246 – “500 Internal server error” in ILMSTOREADM while creating ILM Store
Blog – How to solve “expiration_date403 Forbidden” error during ILM store phase
to publish the storage system using an ICF node, the SAP Backend must be correctly configured for the HTTP/S usage as described in the Documents. Some of the ILM Transaction are “hard coded” as HTTPS calls.
tx. SMICM
tx. SM30 – Table HTTPURLLOC
Note 2704178 – The error HTTP 500 “Redirect is not possible” occurs in /sap/public/myssocntl or in /sap/public/bc/icf/logoff
Note 2846151 – WEBGUI: “No OK code 200 in response from server.” when uploading large files
tx. UCON_CHW
tx. SICF – 1-2
tx. SICF – 2-2
tx. SM59 – S4H_ILM_STORE
tx. SM59 – test the RFC connection
tx. ILMSTOREADM (access with tx. WebGUI)
If the Administration Origin is specified in Table TILM_STOR_O_ROUT, then the creation of the ILM Store will fail with the following message:
Note 3115295 – In ILMSTOREADM error “597: Inconsistent Configuration” occurs
597: Inconsistent Configuration (CX_XADK_DAS_SERVER)
If this error arise, you can already run the mentioned check Reports RILM_STOR_TEST_PF_REPEAT_SMALL and RILM_STOR_TEST_PF_REPEAT_BIG (the message might be misleading)
598: Input or output error (triggered during access to file system, WebDAV or data stream)
"SQL message: [IQ Error SQL-141:42S02][SAP][ODBC Driver][SAP IQ]Table 'TILM_STOR' not found".
597: Inconsistent configuration
CX_XADK_DAS_SERVER
DA Service Message: _DEFINE_ARCHIVE_STORES:
Store S4H_400_ILM_IQ Does Not Correspond to ILM Conformance 2
598: Input or output error
(triggered during access to file system, WebDAV, or data stream; see long text)
CX_XADK_DAS_SERVER
DA Service Message: _DEFINE_ARCHIVE_STORES: Store SAP_ILM_STORE Does Not Support the WebDAV Log
Some of the Transaction are Web based, so access them via the WebGUI
### used Services in the HTTP hierarchy - tx. SICF
/sap/bc/webdynpro/sap/irm_policies
/sap/bc/webdynpro/sap/ilm_audit_area
/sap/bc/webdynpro/sap/das_store_browser
/sap/bc/webdynpro/sap/ilm_store_admin
/sap/bc/webdynpro/sap/wdr_test_input
ILM – selected access via WebGUI
Testing the ILM Functionality with IQ
Blog – demystifying NLS/ILM error messages …
for the testing of the ILM functionality, there are various Report’s available, which more less show a successful result or errors which are not related to the Solution. Additional checks can also be found in the Blog from Neenu Susan George – ILM Store Configuration and – ILM Store Troubleshooting
- RILM_STOR_TEST_HC_CLIENT (ILM Store: Health Check for Client Configuration)
- RILM_STOR_TEST_AT (Validation of BC-ILM 3.0)
- RILM_STOR_TEST_PF_SINGLE (only for file based archiving)
- RILM_STOR_TEST_PF_REPEAT_SMALL
- RILM_STOR_TEST_PF_REPEAT_BIG
- RILM_STOR_TEST_CLEAR (needed to reset the Test Loop)
- RILM_STOR_WD_DEMO (test of the WebDAV Layer)
- RSARCH_CHECK_CUSTOMIZING
Report RILM_STOR_TEST_HC_CLIENT
Report RILM_STOR_TEST_AT
Report RILM_STOR_TEST_CLEAR
Report RILM_STOR_TEST_PF_SINGLE
Report RILM_STOR_WD_DEMO
Report RSARCH_CHECK_CUSTOMIZING
SAP ILM – 1st Step Done …
Ok., that was a Hell of a Ride.
Nevertheless, this is another proved Example of SAP (Sybase) IQ Multi Purpose Database.
It is true: SAP (Sybase) IQ – the hidden treasure …
Next Steps
Blog – SAP Data Archiving Principals & Legal Consolidations
Blog – SAP Archiving Process and Configuration Steps
nice Overview how the SAP Archiving Process works and now with SAP IQ Database as Target
Blog – SAP Archiving Step by Step Guide for Beginners
nice Overview including how the Table Analysis (tx. TAANA) can be used
Blog – Data Archive – Archive Material Documents – MM_MATBEL
nice “Tutorial” to understand the basic concepts of SAP Data Archiving
Roland Kramer, SAP Platform Architect for Intelligent Data & Analytics
@RolandKramer
“Ihave no special talent, Iam only passionately curious.”
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK