In one of our FMCG client we had faced issues wrt Data data load performance post the SAP BW kernel upgrade and TICx parameters Changes for SAP HANA data base.
We started getting the longer runtime for some of the DTPs where it started hitting to 9-10 hrs from 20 -30 mins earlier runtime. We started looking into the issues wrt week end activities/patching performed by the Technical teams.
If DTP load is running for long duration and its direct impact on IQ server, in case if it is trying to read lot of data from IQ server and its taking longer time . As per our earlier experience with long running DTP load or if they are not stopped/ cancelled properly ,they will create memory issue at IQ-NLS. After clearing old long jobs, we can see the performance has improved but we can still see memory usage on IQ is high and may be the old IPC/Memory which were blocked due to long running jobs has not been recycled back to Operating system.
Below trouble shooting was carried out initially in our issue
We were facing below issue where in while reading data from NLS only 1 record was getting inserted and it was making the data loading process slower.
/BIC/OMTLT_2018091904103042591(This table is getting generated dynamically) which is consuming lot of time with DB Insert. Below are some of the screen shots and details from backend as shown below.
C Wed Sep 19 08:58:51:629 2018
C *** ERROR => parse error at field 2; statement: INSERT INTO “/BIC/OMTLT_2018091906585105658” ( “DOC_NUMBER”, “FISCVARNT” )
VALUES ( ? , ? )/*USE_IQ_LOAD*/
/* R3:CL_SQL_STATEMENT==============CP:1114 T:/BIC/OMTLT_2018091906585105658 */
[dbsyb.c 34532]
C {root-id=E4115BE06B601EE8AEFA7FFB7E6578F0}_{conn-id=00000000000000000000000000000000}_0
C *** ERROR => meta data length 1 <= 1
[dbsyb.c 55623]
C *** ERROR => creation of load specification failed.
[dbsyb.c 34532]
C could not start iq load – failback to standard DML operation.
C *** ERROR => could not lock table before array insert to IQ – table name is unknown.
[dbsyb.c 34532]
As you can see below row inserts changed from multiple rows to single row from 32K to 1 row which started causing the performance issue.
— SAPIQDB_DIAG.log shows flip at between 09/24 21:49:14 – 09/24 21:49:16
— SQL log
Below steps were performed to fix the issue based on the decision to downgrade from BW kernel(lib_dbsl for IQ (dbsybslib.so) ) PL 500 of release 749 to PL400 or PL300 .
Finally we decided to go back to our older version of PL400.
Issue stands resolved post implementation of above SAP note and performing 4 steps mentioned above.
We had multiple calls with SAP and SAP proposed below SAP notes to go through
2286931 – Lookup for SPO
2484911 – DTP failing with error while accessing hierarchy
2613273 – Avoid fallback to array insert after archive conversion
2130587 – SYB IQ: Performance enhancement for LOAD statement
2128579 – Data Load into SAP IQ during Copy Phase utilizes only one server-side Thread
2603241 – Overview and summary of the most important SAP Notes in the context of BW transformations with SAP HANA and ABAP execution
2467323 – SAP HANA processing: BW 7.50 with SP04 – SP08: SAP HANA analysis processes and SAP HANA transformations
2679170 – SIQ: SYSTEM_CORE_DUMPED in RSBATCH_EXECUTE_PROZESS
Out of above SAP Notes we have implemented 2613273 and 2484911 implemented in SAP BW.
Finally, SAP released below note based on our client Issue and its resolutions
2698989 – DTP job performance degradation post upgrading BW kernel PL 500 of release 749 – SAP IQ
Okumaya devam et...
We started getting the longer runtime for some of the DTPs where it started hitting to 9-10 hrs from 20 -30 mins earlier runtime. We started looking into the issues wrt week end activities/patching performed by the Technical teams.
If DTP load is running for long duration and its direct impact on IQ server, in case if it is trying to read lot of data from IQ server and its taking longer time . As per our earlier experience with long running DTP load or if they are not stopped/ cancelled properly ,they will create memory issue at IQ-NLS. After clearing old long jobs, we can see the performance has improved but we can still see memory usage on IQ is high and may be the old IPC/Memory which were blocked due to long running jobs has not been recycled back to Operating system.
Below trouble shooting was carried out initially in our issue
- We cancelled the load and repeated the load with same/optimizing the DTP settings.
- We tried restarting the SAP IQ server but it did not improve the performance of the load.
- SQL trace were analyzed
- We also went through ST22 ABAP dumps and found in some cases there were network connection issues were shown.
We were facing below issue where in while reading data from NLS only 1 record was getting inserted and it was making the data loading process slower.
/BIC/OMTLT_2018091904103042591(This table is getting generated dynamically) which is consuming lot of time with DB Insert. Below are some of the screen shots and details from backend as shown below.
C Wed Sep 19 08:58:51:629 2018
C *** ERROR => parse error at field 2; statement: INSERT INTO “/BIC/OMTLT_2018091906585105658” ( “DOC_NUMBER”, “FISCVARNT” )
VALUES ( ? , ? )/*USE_IQ_LOAD*/
/* R3:CL_SQL_STATEMENT==============CP:1114 T:/BIC/OMTLT_2018091906585105658 */
[dbsyb.c 34532]
C {root-id=E4115BE06B601EE8AEFA7FFB7E6578F0}_{conn-id=00000000000000000000000000000000}_0
C *** ERROR => meta data length 1 <= 1
[dbsyb.c 55623]
C *** ERROR => creation of load specification failed.
[dbsyb.c 34532]
C could not start iq load – failback to standard DML operation.
C *** ERROR => could not lock table before array insert to IQ – table name is unknown.
[dbsyb.c 34532]
As you can see below row inserts changed from multiple rows to single row from 32K to 1 row which started causing the performance issue.
— SAPIQDB_DIAG.log shows flip at between 09/24 21:49:14 – 09/24 21:49:16
- 09/24 21:49:07. Insert for ‘/BIC/OMTLT_2018092419490724772’ completed in 0 seconds. 32622 rows inserted.
- 09/24 21:49:14. Insert for ‘/BIC/OMTLT_2018092419491407857’ completed in 0 seconds. 31249 rows inserted.
- 09/24 21:49:14. Insert for ‘/BIC/OMTLT_2018092419491451597’ completed in 0 seconds. 32842 rows inserted.
- 09/24 21:49:16. Insert for ‘/BIC/OMTLT_2018092419491626544’ completed in 0 seconds. 1 rows inserted.
- 09/24 21:49:16. Insert for ‘/BIC/OMTLT_2018092419491626544’ completed in 0 seconds. 1 rows inserted.
- 09/24 21:49:16. Insert for ‘/BIC/OMTLT_2018092419491626544’ completed in 0 seconds. 1 rows inserted.
— SQL log
- 09/22 08:58:33. 0000018554 [20896]: Insert for ‘/BIC/OMTLT_2018092206520419771’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018535 [20896]: Insert for ‘/BIC/OMTLT_2018092206512886703’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018544 [20896]: Insert for ‘/BIC/OMTLT_2018092206514486876’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018521 [20896]: Insert for ‘/BIC/OMTLT_2018092206511881873’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018540 [20896]: Insert for ‘/BIC/OMTLT_2018092206513818994’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018530 [20896]: Insert for ‘/BIC/OMTLT_2018092206512344735’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018524 [20896]: Insert for ‘/BIC/OMTLT_2018092206512368211’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018528 [20896]: Insert for ‘/BIC/OMTLT_2018092206512194613’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018524 [20896]: Insert for ‘/BIC/OMTLT_2018092206512368211’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018535 [20896]: Insert for ‘/BIC/OMTLT_2018092206512886703’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018549 [20896]: Insert for ‘/BIC/OMTLT_2018092206515636551’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018528 [20896]: Insert for ‘/BIC/OMTLT_2018092206512194613’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018554 [20896]: Insert for ‘/BIC/OMTLT_2018092206520419771’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018530 [20896]: Insert for ‘/BIC/OMTLT_2018092206512344735’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018544 [20896]: Insert for ‘/BIC/OMTLT_2018092206514486876’ completed in 0 seconds. 1 rows inserted.
- 09/22 08:58:33. 0000018540 [20896]: Insert for ‘/BIC/OMTLT_2018092206513818994’ completed in 0 seconds. 1 rows inserted.
Below steps were performed to fix the issue based on the decision to downgrade from BW kernel(lib_dbsl for IQ (dbsybslib.so) ) PL 500 of release 749 to PL400 or PL300 .
Finally we decided to go back to our older version of PL400.
- Download PL400 sar file as recommended by SAP note 2679170
- Rename existing .so file , copy the .so file from the sar file over to the BW kernel directory
- Restart the BW system
- Rerun the data loads
Issue stands resolved post implementation of above SAP note and performing 4 steps mentioned above.
We had multiple calls with SAP and SAP proposed below SAP notes to go through
2286931 – Lookup for SPO
2484911 – DTP failing with error while accessing hierarchy
2613273 – Avoid fallback to array insert after archive conversion
2130587 – SYB IQ: Performance enhancement for LOAD statement
2128579 – Data Load into SAP IQ during Copy Phase utilizes only one server-side Thread
2603241 – Overview and summary of the most important SAP Notes in the context of BW transformations with SAP HANA and ABAP execution
2467323 – SAP HANA processing: BW 7.50 with SP04 – SP08: SAP HANA analysis processes and SAP HANA transformations
2679170 – SIQ: SYSTEM_CORE_DUMPED in RSBATCH_EXECUTE_PROZESS
Out of above SAP Notes we have implemented 2613273 and 2484911 implemented in SAP BW.
Finally, SAP released below note based on our client Issue and its resolutions
2698989 – DTP job performance degradation post upgrading BW kernel PL 500 of release 749 – SAP IQ
Okumaya devam et...