Change pointer table

BDCP2

Background job tables

TBTCO and TBTCP

Export and import in shared memory

EXPORT WA_VAR1 TO SHARED MEMORY INDX(mu) ID 'MEMORYNAME'

Same for import

IMPORT WA_VAR1 FROM SHARED MEMORY INDX(mu) ID 'MEMORYNAME'

HANA Views

Attribute View: Select one master table and one text table and join and display

Analytical: create a view by joining two table with numeric fields and with some calculation added.

Calculated view:

HANA analytic Privilege

To provide the access for a view or table to a specific user - Right clock on the package and select Analytical Privilege

Stored Procedures(like function module):   we can define the procedure(with native sql) and we can call in out program by required input and output parameters

to call any procedure we haveto start with _SYS_BIC( all the abap objects will be saved here)   .


We can create the Interface for the stored procedure and we can use that as a in ABAP program and we can directly call the procedure in the program without any Native sql, but here the database should be HANA database.

Go to ABAP stack and go to package
right click on package
Select other ABAP repository object
search with PROXY
click on database procedure proxy
click a name for your proxy and select the proxy required and complete it
It will generate an interface for this which we can use in program


  DATA: lt_cust_early TYPE TABLE OF if_ha400_early_and_late=>et_early,
        lt_cust_late  TYPE TABLE OF if_ha400_early_and_late=>et_late.


  CALL DATABASE PROCEDURE ha400_early_and_late
               EXPORTING
                    iv_number = pv_number
               IMPORTING
                     et_early = lt_cust_early
                     et_late  = lt_cust_late.

  ct_cust_early = lt_cust_early.
  ct_cust_late  = lt_cust_late.  

HANA Certification

the below link shows about HANA certification

https://training.sap.com/de/en/courses-and-curricula/hana

To access HANA data from the excel report

1) we have to instal HANA CLIENT in system
2) Then go to excel -> data - > from other sources -> From data connection wizard -> other/ advanced and then press next -> SAP HANA MDX provider and press next
3) Enter the HOST details and connect to HANA STUDIO

 

Transport management in HANA

1) we haveto create the delivery unit
2) assign package to delivery unit
3) select the raquired transport or create new or select the new object
But, wheneverr we do some changes we have to transport(create new delivery unit) again. it will create another version.
 

Performance in SAP HANA ABAP

1) By using database specifc native SQL statements
2) Inner joins instead of nested loops
3) It will do all the calculations at the database level, and ther wont be much load on application layer.

OPEN SQL and NATIVE SQL IN HANA ABAP

OPEN SQL:
SELECT bp~bp_id AS bp_id
         bp~company_name AS bp_name
         inv~created_at AS created_at
    FROM snwd_so_inv_head AS inv JOIN snwd_bpa AS bp ON inv~buyer_guid = bp~node_key
    CONNECTION (pv_dbcon)
    INTO TABLE lt_customer_invoice
    WHERE inv~payment_status <> 'P'
    ORDER BY bp~bp_id.

NATIVE SQL:
*     Use a native SQL call with ADBC here to retrieve the joined

*     Step 1: Construct the native SQL string into local variable lv_sql.
*             Make sure to order by business partner ID

*     Step 2: Instantiate the SQL connection and the SQL statement objects

*     Step 3: Execute the native SQL query

*     Step 4: Read the result into the internal table lt_partner

*     Step 5: close resources, i.e. the SQL statement and connection
* ADBC Objects and variables
  DATA:  lo_sql_stmt TYPE REF TO cl_sql_statement,
         lo_conn     TYPE REF TO cl_sql_connection,
         lo_result   TYPE REF TO cl_sql_result_set,
         lv_sql      TYPE string,
         lr_data     TYPE REF TO data.

*&---------------------------------------------------------------------*
*&      Form  get_data_solution
*&---------------------------------------------------------------------*
FORM get_data_solution USING pv_dbcon     TYPE dbcon-con_name
                       CHANGING ct_customers TYPE tt_customer.

* analyze, then improve code here

* Declarations
****************

  TYPES: BEGIN OF lts_partner,
           bp_id        TYPE snwd_bpa-bp_id,
           company_name TYPE snwd_bpa-company_name,
           created_at   TYPE snwd_so_inv_head-created_at,
         END OF lts_partner.

* Work Area for Result
  DATA ls_customer LIKE LINE OF ct_customers.

* Targets for Select
  DATA: ls_partner TYPE lts_partner,
        lt_partner TYPE STANDARD TABLE OF lts_partner.

* ADBC Objects and variables
  DATA:  lo_sql_stmt TYPE REF TO cl_sql_statement,
         lo_conn     TYPE REF TO cl_sql_connection,
         lo_result   TYPE REF TO cl_sql_result_set,
         lv_sql      TYPE string,
         lr_data     TYPE REF TO data.

* Eception handling
  DATA:   lx_sql_exc TYPE REF TO cx_sql_exception,
          lv_text TYPE string.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  TRY.
      CLEAR ct_customers.
      CLEAR lv_count.

      " get current date (UTC)
      GET TIME STAMP FIELD lv_current_date.
*     Step 1: Construct the native SQL string into local variable lv_sql.
*             Make sure to order by business partner ID
      CONCATENATE 'SELECT BP.BP_ID, BP.COMPANY_NAME, INV.CREATED_AT'
                  '  FROM SNWD_BPA AS BP INNER JOIN SNWD_SO_INV_HEAD AS INV'
                  '                              ON BP.NODE_KEY = INV.BUYER_GUID'
                  ' WHERE INV.CLIENT = '''
                  sy-mandt
                  ''' AND PAYMENT_STATUS <> ''P'''
                  '  ORDER BY BP.BP_ID'
             INTO lv_sql.
**     Alternative using String templates and expressions:
*      lv_sql = | SELECT BP.BP_ID, BP.COMPANY_NAME, INV.CREATED_AT |
*            && |   FROM SNWD_BPA AS BP INNER JOIN SNWD_SO_INV_HEAD AS INV |
*            && |                           ON BP.NODE_KEY = INV.BUYER_GUID |
*            && |  WHERE iNV.CLIENT = '{ SY-MANDT }' AND PAYMENT_STATUS <> 'P' |
*            && |  ORDER BY BP.BP_ID |.

*     Step 2: Instantiate the SQL connection and the SQL statement objects

*     Get a secondary DB connection
      lo_conn = cl_sql_connection=>get_connection( pv_dbcon ).
*     Create an SQL statement to be executed via the connection
      lo_sql_stmt = lo_conn->create_statement( ).

**     ALTERNATIVE: Do both in one step.
*      CREATE OBJECT lo_sql_stmt EXPORTING con_ref = cl_sql_connection=>get_connection( pv_dbcon ).
**     ALTERNATIVE: Don't even pass a secondary connection if the "default" connection is used anyway
*      CREATE OBJECT lo_sql_stmt.

*     use method set_table_name_for_trace( ... ) to tell SQL Trace which table is accessed
      lo_sql_stmt->set_table_name_for_trace( 'SNWD_SO join SNWD_BPA' ).

*     Step 3: Execute the native SQL query
      lo_result = lo_sql_stmt->execute_query( lv_sql ).

*     Step 4: Read the result into the internal table lt_partner
      GET REFERENCE OF lt_partner INTO lr_data.
      lo_result->set_param_table( lr_data ).
      lo_result->next_package( ).

*     Step 5: close resources, i.e. the SQL statement and connection
      lo_result->close( ).
      lo_conn->close( ).

*     Loop over result as in Open SQL version, to calculate the average sales order ages
      LOOP AT lt_partner INTO ls_partner.

        IF sy-tabix = 1. " First customer, first order

          ls_customer-id       = ls_partner-bp_id.
          ls_customer-name     = ls_partner-company_name.
          CLEAR: ls_customer-avg_days_open.

        ELSEIF ls_customer-id <> ls_partner-bp_id. "First order of next customer
          ls_customer-avg_days_open = ls_customer-avg_days_open / lv_count.
          INSERT ls_customer INTO TABLE ct_customers.

          ls_customer-id       = ls_partner-bp_id.
          ls_customer-name     = ls_partner-company_name.
          CLEAR: ls_customer-avg_days_open,
                 lv_count.


        ENDIF.
        lv_count = lv_count + 1.
        ls_customer-avg_days_open = ls_customer-avg_days_open
                      + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                                 tstmp2 = ls_partner-created_at ) / 86400.

      ENDLOOP.
     ls_customer-avg_days_open = ls_customer-avg_days_open / lv_count.
     INSERT ls_customer INTO TABLE ct_customers. " Don't forget last customer

    CATCH cx_sql_exception INTO lx_sql_exc.
      lv_text = lx_sql_exc->get_text( ).
      MESSAGE lv_text TYPE 'E'.

  ENDTRY.

ENDFORM.                    "




and display the data:

FORM display USING it_customers TYPE tt_customer.

  DATA: lo_alv  TYPE REF TO cl_salv_table,
        lx_msg  TYPE REF TO cx_salv_msg,
        lv_text TYPE string.

* display
  TRY.
      cl_salv_table=>factory(
          IMPORTING
            r_salv_table = lo_alv
          CHANGING
            t_table      = it_customers ).

      lo_alv->display( ).

    CATCH cx_salv_msg INTO lx_msg.
      lv_text = lx_msg->get_text( ).
      MESSAGE lv_text TYPE 'E'.
  ENDTRY.

ENDFORM.

1) referance for SQL ststements
2) Referance for the connection
3) Referance for the instantiation
4) read the data withh standard methos
5) close the connection

SQL trace in HANA ABAP

1) SET we can execute
2) ST05 :
select activate trace with filter
give the parameters with you id
then execute the transaction
come to ST05 and deactivate thetrace
click on display trace
go to trace and select structure identical statements

3) SCI
4) ATC: ABAP TEST COCKPIT : to get this right click on the program and select ATC.

5) SQLM: SQL

Program: ZHA400_OPT_OSQL_T1

*&---------------------------------------------------------------------*
*& Report  HA400_OPTIMIZE_OSQL_S1
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zha400_opt_osql_t1 MESSAGE-ID ha400.

* Structure for Result: list of customers with age of their sales orders
TYPES: BEGIN OF ts_customer,
         id              TYPE snwd_bpa-bp_id,
         name            TYPE snwd_bpa-company_name,
         avg_days_open   TYPE i,
       END OF ts_customer.

TYPES: tt_customer TYPE STANDARD TABLE OF ts_customer
                       WITH NON-UNIQUE KEY id, company_name.

* Data objects
DATA: gt_customers   TYPE tt_customer,
      gt_customers_t TYPE tt_customer.


* Database connection
DATA gv_dbcon TYPE dbcon-con_name.
CONSTANTS:
     c_primdb TYPE dbcon-con_name VALUE 'DEFAULT',
     c_secdb  TYPE dbcon-con_name VALUE 'HANADB'.

* selection screen
SELECTION-SCREEN BEGIN OF BLOCK exe WITH FRAME TITLE text-exe.
PARAMETERS:
  pa_temp   TYPE xfeld RADIOBUTTON GROUP exe DEFAULT 'X',
  pa_solu   TYPE xfeld RADIOBUTTON GROUP exe,
  pa_both   TYPE xfeld RADIOBUTTON GROUP exe.
SELECTION-SCREEN END OF BLOCK exe.

SELECTION-SCREEN BEGIN OF BLOCK dbc WITH FRAME TITLE text-dbc.
PARAMETERS:
  pa_prim   TYPE xfeld RADIOBUTTON GROUP dbc,
  pa_sec    TYPE xfeld RADIOBUTTON GROUP dbc DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK dbc.

START-OF-SELECTION.

  CASE 'X'.
    WHEN pa_prim.
      gv_dbcon = c_primdb.
    WHEN pa_sec.
      gv_dbcon = c_secdb.
  ENDCASE.


  CASE 'X'.
    WHEN pa_temp.  " template only
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      PERFORM display USING gt_customers_t.
    WHEN pa_solu.  " solution only
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM display USING gt_customers.
    WHEN pa_both.  " both subroutines
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      IF gt_customers <> gt_customers_t.
        MESSAGE i010.
      ELSE.
        MESSAGE s011.
      ENDIF.
  ENDCASE.

*&---------------------------------------------------------------------*
*&      Form  get_data_template
*&---------------------------------------------------------------------*
FORM get_data_template  USING pv_dbcon     TYPE dbcon-con_name
                        CHANGING ct_customers TYPE tt_customer.

* Declarations
****************

* Work Area for Result
  DATA ls_customer LIKE LINE OF ct_customers.

* Targets for Select
  DATA: ls_partner TYPE snwd_bpa,
        ls_invoice TYPE snwd_so_inv_head.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  CLEAR ct_customers.
  CLEAR lv_count.

  " get current date (UTC)
  GET TIME STAMP FIELD lv_current_date.

  SELECT * FROM snwd_bpa
        CONNECTION (pv_dbcon)
            INTO ls_partner.

    ls_customer-id       = ls_partner-bp_id.
    ls_customer-name     = ls_partner-company_name.
*
    CLEAR: ls_customer-avg_days_open,
           lv_count.

    SELECT * FROM snwd_so_inv_head
            CONNECTION (pv_dbcon)
            INTO ls_invoice
            WHERE buyer_guid = ls_partner-node_key
              AND payment_status <> 'P'.

      ls_customer-avg_days_open = ls_customer-avg_days_open
                    + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                               tstmp2 = ls_invoice-created_at ) / 86400.
      lv_count = lv_count + 1.

    ENDSELECT.
    IF ls_customer-avg_days_open <> 0.
      ls_customer-avg_days_open = ls_customer-avg_days_open /  lv_count.
      INSERT ls_customer INTO TABLE ct_customers.
    ENDIF.

  ENDSELECT.

  SORT ct_customers BY id.

ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  get_data_solution
*&---------------------------------------------------------------------*
FORM get_data_solution USING pv_dbcon     TYPE dbcon-con_name
                       CHANGING ct_customers TYPE tt_customer.


ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  output
*&---------------------------------------------------------------------*

FORM display USING it_customers TYPE tt_customer.

  DATA: lo_alv  TYPE REF TO cl_salv_table,
        lx_msg  TYPE REF TO cx_salv_msg,
        lv_text TYPE string.

* display
  TRY.
      cl_salv_table=>factory(
          IMPORTING
            r_salv_table = lo_alv
          CHANGING
            t_table      = it_customers ).

      lo_alv->display( ).

    CATCH cx_salv_msg INTO lx_msg.
      lv_text = lx_msg->get_text( ).
      MESSAGE lv_text TYPE 'E'.
  ENDTRY.

ENDFORM.

SAP HANA - DATABASE CONFIG

We haveto config in DBCO transaction with the server and instance number and it will take as secondary database for system.


To config connection name to database in HANA system will be done in the transaction DBACOCKPIT.

here we will maintain the database(HDB) to connection name(HANADB).

we will use the connectname in abap programs to read the data from hana database.

 

SAP HANA PROGRAM - HA400_SEC_DB_CON_S1

*&---------------------------------------------------------------------*
*& Report  HA400_OPTIMIZE_OSQL_S1
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ha400_sec_db_con_s1 MESSAGE-ID ha400.

* Structure for Result: list of customers with age of their sales orders
TYPES: BEGIN OF ts_customer,
         id          TYPE snwd_bpa-bp_id,
         name        TYPE snwd_bpa-company_name,
         avg_days_open   TYPE i,
       END OF ts_customer.

TYPES: tt_customer TYPE STANDARD TABLE OF ts_customer
                       WITH NON-UNIQUE KEY id.

* Data objects
DATA: gt_customers   TYPE tt_customer,
      gt_customers_t TYPE tt_customer.
DATA: lv_start       TYPE timestampl,
      lv_end         TYPE timestampl,
      lv_message     TYPE string.

* Database connection
DATA gv_dbcon TYPE dbcon-con_name.
CONSTANTS:
     c_primdb TYPE dbcon-con_name VALUE 'DEFAULT',
     c_secdb  TYPE dbcon-con_name VALUE 'HANADB'.

* selection screen
SELECTION-SCREEN BEGIN OF BLOCK exe WITH FRAME TITLE text-exe.
PARAMETERS:
  pa_temp   TYPE xfeld RADIOBUTTON GROUP exe DEFAULT 'X',
  pa_solu   TYPE xfeld RADIOBUTTON GROUP exe,
  pa_both   TYPE xfeld RADIOBUTTON GROUP exe.
SELECTION-SCREEN END OF BLOCK exe.

SELECTION-SCREEN BEGIN OF BLOCK dbc WITH FRAME TITLE text-dbc.
PARAMETERS:
  pa_prim   TYPE xfeld RADIOBUTTON GROUP dbc,
  pa_sec    TYPE xfeld RADIOBUTTON GROUP dbc DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK dbc.

START-OF-SELECTION.

  CASE 'X'.
    WHEN pa_prim.
      gv_dbcon = c_primdb.
    WHEN pa_sec.
      gv_dbcon = c_secdb.
  ENDCASE.


  CASE 'X'.
    WHEN pa_temp.  " template only
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      PERFORM display USING gt_customers_t.
    WHEN pa_solu.  " solution only
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM display USING gt_customers.
    WHEN pa_both.  " both subroutines
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      IF gt_customers <> gt_customers_t.
        MESSAGE i010.
      ELSE.
        MESSAGE s011.
      ENDIF.
  ENDCASE.

*&---------------------------------------------------------------------*
*&      Form  get_data_template
*&---------------------------------------------------------------------*
FORM get_data_template  USING pv_dbcon     TYPE dbcon-con_name
                        CHANGING ct_customers TYPE tt_customer.

* Declarations
****************

* Work Area for Result
  DATA ls_customer LIKE LINE OF ct_customers.

* Targets for Select
  DATA: ls_partner TYPE snwd_bpa,
        ls_invoice TYPE snwd_so_inv_head.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  CLEAR ct_customers.
  CLEAR lv_count.

  " get current date (UTC)
  GET TIME STAMP FIELD lv_current_date.

  SELECT * FROM snwd_bpa INTO ls_partner.

    ls_customer-id       = ls_partner-bp_id.
    ls_customer-name     = ls_partner-company_name.
*
    CLEAR: ls_customer-avg_days_open,
           lv_count.

    SELECT * FROM snwd_so_inv_head INTO ls_invoice
            WHERE buyer_guid = ls_partner-node_key
              AND payment_status <> 'P'.

      ls_customer-avg_days_open = ls_customer-avg_days_open
                    + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                               tstmp2 = ls_invoice-created_at ) / 86400.
      lv_count = lv_count + 1.

    ENDSELECT.
    IF ls_customer-avg_days_open <> 0.
      ls_customer-avg_days_open = ls_customer-avg_days_open /  lv_count.
      INSERT ls_customer INTO TABLE ct_customers.
    ENDIF.

  ENDSELECT.

  SORT ct_customers BY id.

ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  get_data_solution
*&---------------------------------------------------------------------*
FORM get_data_solution USING pv_dbcon     TYPE dbcon-con_name
                       CHANGING ct_customers TYPE tt_customer.

* Declarations
****************

* Work Area for Result
  DATA ls_customer LIKE LINE OF ct_customers.

* Targets for Select
  DATA: ls_partner TYPE snwd_bpa,
        ls_invoice TYPE snwd_so_inv_head.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  CLEAR ct_customers.
  CLEAR lv_count.

  " get current date (UTC)
  GET TIME STAMP FIELD lv_current_date.

  SELECT * FROM snwd_bpa
        CONNECTION (pv_dbcon)
            INTO ls_partner.

    ls_customer-id       = ls_partner-bp_id.
    ls_customer-name     = ls_partner-company_name.
*
    CLEAR: ls_customer-avg_days_open,
           lv_count.

    SELECT * FROM snwd_so_inv_head
            CONNECTION (pv_dbcon)
            INTO ls_invoice
            WHERE buyer_guid = ls_partner-node_key
              AND payment_status <> 'P'.

      ls_customer-avg_days_open = ls_customer-avg_days_open
                    + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                               tstmp2 = ls_invoice-created_at ) / 86400.
      lv_count = lv_count + 1.

    ENDSELECT.
    IF ls_customer-avg_days_open <> 0.
      ls_customer-avg_days_open = ls_customer-avg_days_open /  lv_count.
      INSERT ls_customer INTO TABLE ct_customers.
    ENDIF.

  ENDSELECT.

  SORT ct_customers BY id.

ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  output
*&---------------------------------------------------------------------*

FORM display USING it_customers TYPE tt_customer.

  DATA: lo_alv  TYPE REF TO cl_salv_table,
        lx_msg  TYPE REF TO cx_salv_msg,
        lv_text TYPE string.

* display
  TRY.
      cl_salv_table=>factory(
          IMPORTING
            r_salv_table = lo_alv
          CHANGING
            t_table      = it_customers ).

      lo_alv->display( ).

    CATCH cx_salv_msg INTO lx_msg.
      lv_text = lx_msg->get_text( ).
      MESSAGE lv_text TYPE 'E'.
  ENDTRY.

ENDFORM.