V_PACKAGE := TRIM(SUBSTRING(:p_payload, :V_POS)) Extract the last few records into V_PACKAGE The CLOB does not contains records in multiples of 12. The CLOB contains records in multiples of 12 IF V_POS > V_LEN THEN -Reached End of File. V_PACKAGE := TRIM(SUBSTRING(:p_payload, :V_POS, (:V_LOCATE - :V_POS) + 1)) -Extract only 12 records in V_PACKAGE including record delimiter
V_LOCATE := LOCATE(:p_payload, :V_RECORD_DELIMT, :V_LOCATE + 1, 12) - Locate the 12th occurrence of record delimiter V_LEN := LENGTH(:p_payload) -payload variable contains the actual CLOB data Hence, we had to change the approach and wrote the below SQL Reassigning values to the same CLOB variable doesn’t release the memory for some reasons. The SQL couldn’t handle more than 1000 records within the CLOB. We bumped into an Out-Of-Memory (OOM) exception in the Stored Procedure due to the way we wrote the SQL. The SQL within the Stored Procedure parses the CLOB based on record delimiter and inserts the records into the target table. We performed the message mapping in PI such that PI concatenates all the records in the file with a record delimiter (#) and passes the entire payload to HANA Stored Procedure. Parsing of the PI Payload in the Stored Procedure This version of jar has fixes to address the issue with CLOB data type.
SAP HANA STUDIO DATA PROVISIONING OPTIONS DRIVER
This still didn’t resolve our problem.įinally we took JDBC Driver ngdbc.jar that comes with HANA client revision 94 from HANA Studio and renamed it as. and updated it in the library as mentioned above. A SAP Incident suggested to take rt.jar from the SAPJVM folder(sapjvm_5/jre/lib) and add it into the .lib library. We still had errors when trying to use CLOB objects during the communication. Would have been great if there was a way to pass an internal table to the Stored Procedure. We decided to use CLOB as a means to transfer all the file contents. INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output only in connection with the Oracle JDBC driver) There would be multiple flat files amounting to 4 millions records.īelow are the data types which are supported by Receiver JDBC Adapter Though we were able to establish a communication from PI to HANA, we wanted to be able to pass large volume of records from PI. Using a CLOB data type to pass data from PI to HANA Stored Procedure This enabled the communication between PI and HANA via JDBC. Added this jar file into the .lib library and deployed in SAP PI via JSPM. We took JDBC Driver .jdbc_1.85.0.jar file (from HANA Client Rev 85) and renamed it to. There is a document on SCN (bit old) which refers to the steps for installing the driver. The role of the Stored Procedure was to extract the records from the input and insert them into the target table. We created a Stored Procedure in SAP HANA which would get the contents of a file and process it. The first challenge was “How to get PI talking to HANA”. On HANA, we had created a Schema and relevant tables which would be used during the Data acquisition process. We decided take the PI – JDBC – HANA path. Versions: SAP PI 7.1 (JVM 5) and SAP HANA Rev 85
This article covers most of the challenges we faced in several stages and I hope this is helpful. One of the main reasons were to leverage the existing infrastructure and monitoring capabilities of SAP PI.
However, one of the customers I worked with wanted to leverage SAP PI to push data from external sources into HANA. There are various Data provisioning techniques for HANA and some of the recommended options are listed in the SAP HANA Master Guide.
0 kommentar(er)
