Saturday, 17 October 2015

SAP Data Loading using BAPI Stage



Defining Keywords of SAP R/3

  ABAP (Advanced Business Application Programming)

The language developed by SAP for application development purposes. All R/3 applications are written in ABAP.
 IDOC (Intermediate Document)
IDoc is a standard data structure for electronic data interchange (EDI) between application programs written for the popular SAP business system or between an SAP application and an external program. It provides a standard format for exchanging data with R/3 Enterprise. This interface should be used primarily for bulk data transfers when the desired data set is already represented by an available IDoc. It is used to load data into R/3 system.
 BAPI (Business Application Programming Interface)
A precisely defined interface providing access to processes and data in business application systems. BAPIs are defined as API methods of SAP objects. These objects and their methods are stored in the Business Objects Repository. Most suited to transactional environments where the efficiency of mass data transfers is not a requirement.

  WebSphere DataStage Pack for SAP R/3

The WebSphere® DataStage™ Pack for SAP R/3 helps you extract data from and load data into SAP R/3 and all mySAP Business Suite application modules. The IBM WebSphere DataStage Pack for SAP R/3 includes the following stages and utility:

     ABAP Extract

 Lets WebSphere DataStage extract data from the R/3 repository using the ABAP extraction program generated by the stage.

  IDoc Extract

Let’s WebSphere DataStage capture IDocs from R/3 source systems to be used as source data for WebSphere DataStage job.

  IDoc Load

Generates IDocs to load data into R/3 Enterprise.

      BAPI

Loads data into and extracts data from R/3 Enterprise.

   Administrator for SAP

Manages the configurations of R/3 connection and IDoc type objects.


Data Loading through BAPI Stage in Datatage 


1.    BAPI stage Architecture

 

2.   BAPI Structure

BAPI is a business API that exposes business objects to external applications.  The business objects can be executed synchronously or asynchronously.  Invoked via RPC. There are several hundred delivered BAPIs, companies can create their own BAPIs. Structure of a business object and BAPI is in the below picture.


BAPI’s in R/3 implemented as function modules.
BAPI supports Remote Function Call (RFC) protocol.
BAPI are processed without returning screen dialog to calling application.
BAPI Interface defined by:
  •   Import Parameters
  •   Export Parameters
  •   Import/Export (table) parameters

3.   BAPI Load Design Steps


Ø  The GUI client logs on to the SAP R/3 application server and retrieves metadata information from BOR.
Ø  The GUI client stores BAPI metadata in the DataStage Repository.
Ø  The run-time server reads the metadata from the DataStage Repository and dynamically builds a BAPI call.
Ø  The run-time server makes a BAPI call and processes the generated dataset.
Ø  The returned values are written to the appropriate logs.

4.   BAPI Stage in WebSphere




5.   Sample BAPI Load job



6.   BAPI Stage Properties


This stage dialog has three pages – STAGE, INPUT and OUTPUT
Stage Page
·         This page displays the name of the stage you are editing and contains the General, BAPI and BAPI Parameters pages. The selected SAP connection parameters are displayed on the General tab on the Stage page.
·         Use the BAPI tab of the Stage page to select the BAPI name to load (see Selecting BAPI).
·         Using BAPI Paremeters tab pass the necessary parameter as per the requirement.
·         The NLS page defines a character set map to use with the stage. This page opens only if you have installed NLS for InfoSphere DataStage (see Defining character set mapping).
6.1.        BAPI Stage: Stage Page-General Tab
DataStage Connection to SAP - The InfoSphere DataStage connection to the SAP R/3 system that is defined on the server machine and shared by all users connected to that machine.
     -> Name - The name of the selected connection to the SAP R/3 system that stores the BAPI. The logical connection includes RFC client logon details.
-> Select - Click to choose an InfoSphere DataStage connection to an existing SAP R/3 system. The Select InfoSphere DataStage Connection to SAP dialog opens. The selected connection provides all needed connection and default logon details that are needed to communicate with the corresponding SAP R/3 system. The default SAP Logon Details are used if Use connection defaults is selected.
->  New – Using this option you can create a new connection.
->  Use job parameter – Use the DS parameters in SAP Logon Details (User, Password, Client and Language) defined in your job/project. After that, again select on the connection option to parameterize the SAP system using the option ‘Use job parameter’ option and write the parameter name as defined in the job/project and then click OK.
->  Clear job parameter – This option is required to clear the existing job parameter and entry a new one.
-> Clear connection – This is for clear the existing connection.

Description - Additional information about the selected connection.
     -> Application Server - The name of the host system running R/3. If the selected connection uses load balancing, Message Server is used instead.
     -> System Number - The number assigned to the SAP R/3 system used to connect to R/3. If the selected connection uses load balancing, System ID is used instead.

SAP Logon Details - The fields in this area are read-only unless you clear the Use connection defaults box.
     – User Name - The user name that is used to connect to SAP.
     – Password - A password for the specified user name.
     – Client Number - The number of the client system used to connect to SAP.
     – Language - The language used to connect to SAP.
     – Use connection defaults - Clear to remove the default SAP Logon Details settings so you can use different logon information for only this stage in this job. If selected, the displayed logon details are obtained from the selected connection and are disabled.
  • Enter an optional description to describe the purpose of the stage in the Description field.
  • Validate All - Select to check the stage and link properties and the column lists for consistency and completeness. Always check this once any of the connection details is updated.

To start with BAPI load stage, open the stage and then click on General tab.
After that click on button to select the available connections to SAP system configured before.

Below pop up will appear with list of configured SAP systems. Select the required system connection.

6.2.        BAPI Stage: Stage Page-BAPI Tab



Using the BAPI tab we can select the BAPI to be loaded.



To configure BAPI name:
  1. Click on BAPI Exploer tab of the Stage page to open the Select Connection Details dialog. 

  1. Select the proper connection details to Find the BAPI name. After Click OK a pop up window will come up.
  1. A list of all released BAPI defined on the R/3 system will appear to select from ‘Show Released BAPIs’. Or we can go with the specific BAPI name using the option ‘Show RFMs’. Select the ‘Show RFMs’ and type the BAPI name and click OK.


  1. Click OK to set proper BAPI name for the stage.









6.3.        BAPI Stage: Stage Page-BAPI Parameters-Import Tab
When a BAPI is first selected, only required parameters are active on the BAPI Import tab of the Input page. For input links, fields that are required for import parameter appear on the Columns page.
Activate/Deactivate the parameter
Green icons beside the parameter names indicate that the parameters are active, That is, used to dynamically build BAPI calls at run time. (Red icons indicate That parameters are inactive, unused when calling BAPIs.) Green icons display I or E to indicate whether table parameters are activated for Import or Export. To activate or deactivate parameters, do one of the following:
. Double-click a parameter name
. Right-click for a shortcut menu
6.4.        BAPI Stage: Stage Page-BAPI Parameters-Table Tab
Click the Tables tab on the Input BAPI page to view the table parameters of a BAPI. Fields that are required for table parameters appear on the Columns page.

Activate/Deactivate the parameter for Input or Output
Green icons beside the parameter names indicate that the parameters are active, that is, used to load that table using build BAPI calls at run time. (Red icons    indicate that parameters are inactive, unused when calling BAPIs.)
To activate any of the parameter Right-click for a shortcut menu and select it.
·         Activate Selected Parameter for Input – select this option to load any particular table in SAP system
·         Activate Selected Parameter for Output - select this option to capture the data into file/dataset/table
·         Activate Selected Parameter for Input and Output- it will load SAP system and the same time you can capture the same record into file/dataset/table
·         De-Activate Selected Parameter – to de activate the parameter

6.5.        BAPI Stage: Return Table
In the table list there is Return table which return the parameters. This table is very important for de-bugging purpose. It writes the status of the loaded records whether it is loaded success or fail.
By the filed Message type (T_RETURN_BAPIRET2_TYPE): S Success, E Error, W Warning, I Info, A Abort




The structure of the Return table shown as below
6.6.        BAPI Stage: Stage Page-BAPI Parameters-Export Tab
Click the Export tab on the Input BAPI page to view the export parameters of a BAPI. By default, export parameters are optional and initially inactive. Fields that are required for export parameters appear in a grid on the Input Logs Page, not on the Input Columns page, because they contain return values from the BAPI call. The reference structure being used for each parameter appears with a short text Description of the parameter. This helps you decide whether to activate a particular 
parameter. 

7. Data Validation in SAP environment


Once the Datastage load job execute successfully, you need to do the data validation in SAP environment. To do data validation use the following steps.
Login into correct SAP system and execute the Tcode se16 in the left upper section and press enter


Enter SAP table name into the table name section and press enter.






Provide the primary key value, create date and time. Click on execute button (F8)


Below Screen shows all processed records on the given date and time.



Provide the primary key value, create date and time. Click on Number of Entries button. It will provide the count of the processed records for the given date and time.


8. BAPI Load Error Analysis


By selecting the Return table in the Output, you can capture the BAPI Return table log report into file/dataset. It writes the status of the loaded records whether it is loaded success or fail. By the filed Message type (T_RETURN_BAPIRET2_TYPE): S Success, E Error, W Warning, I Info, A Abort.
The detailed error report is mentioned in the field T_RETURN_BAPIRET2_MESSAGE




9. BAPI stage and it’s disadvantage


It is experienced that for huge volume of data the BAPI stage performs very slow. For a volume like more than 10K, it slow down the entire SAP load process. Though we don’t know the actual reason for this delay of the performance. But it is also experienced and tested if the total input data can divided into some logical chunks and call the BAPI for one chunk at a time, we can get some better performance compare to process all the data in a single shot.
For each and every BAPISEQNO generate a unique number using the below derivation.
StageVariabe = @PARTITIONNUM + (@NUMPARTITIONS * @INROWNUM - 1) +1
For example, see the below table
BapiSeqNo
StageVariable
7000
11
7001
12
7002
13
7003
14
7004
15
7005
16

The logical group can be made using the below derivation.
Batch = Mod(StageVariable,p_Batch) + 1
Where p_Batch is the no. of logical group, user want to create based on the input volume of the data.
Batch shows the formulated group number associated with each and individual BAPISEQNO.
For example, the user want to divide the above dataset into 3 logical group. That means p_Batch = 3
BapiSeqNo
StageVariable
Batch
7000
11
3
7001
12
1
7002
13
2
7003
14
3
7004
15
1
7005
16
2

Finally we can design a sequence job where using loops we can easily pass the Batch list so it can execute the BAPI load job for one group at a time.




 



Thursday, 8 October 2015

Some Useful Unix Command

1. cut -c103-108 FT_DCS_PSGR_DETAIL_20150826.TXT|tr -d ' \t'|sed '/^$/d'|head -10|grep -f - FT_DCS_PSGR_DETAIL_20150826.TXT|cut -c103-121| awk -v OFS=, '{print substr($0,1,6),substr($0,7,20)}'

2. grep -lR 'pattern' * |egrep '(xml)$'

3. head -1  Crane4C1.dat| grep -nf -  Crane4C1.dat | cut -d : -f 1 | awk '{for (i=1; i<=NF; i++)  {a[NR,i] = $i;}} NF>p { p = NF }END {for(j=1; j<=p; j++) {str=a[1,j]; for(i=2; i<=NR; i++){ str=str"d;"a[i,j];} print str }}' | sed 's/$/d/g' > temp.txt

sed "/$(cat temp.txt)/d" Crane4C1.dat | sed '/^-/d' >  Crane4C.dat

head -1  Crane4C1.dat| grep -nf -  Crane4C1.dat | cut -d : -f 1 > lines.txt

awk 'NR==FNR{l[$0];next;} !(FNR in l)' lines.txt Crane4C1.dat | sed '/^-/d' > Crane4C.dat

4. ls #Global_File_Parameter_Set.Delta_Data_Dir# | egrep *-TK* | grep .xml | tr -s ' ' | cut -d ' ' -f9 | awk '{print "#Global_File_Parameter_Set.Delta_Data_Dir#" $0}' > #Global_File_Parameter_Set.Delta_Data_Dir#apis_file.txt

5. ls /home/IBM/DSProjects/CCDBMDM_DEV/DL/ | egrep *-TK* | tr -s ' ' | cut -d ' ' -f9 | awk '{for (i=NF; i>0; i--) total=total","$i;}{print total}'| tail -n 1|sed 's/^,//'

6. cp $(ll /home/IBM/DSProjects/CCDBMDM_SIT/data/archive/ | grep APIS_MDM | tr -s ' \t' | cut -d ' ' -f9 | sed -n -e '501,1000p' | awk '{for (i=1; i<=NF; i++)  {a[NR,i] = $i;}} NF>p { p = NF }END {for(j=1; j<=p; j++) {str=a[1,j]; for(i=2; i<=NR; i++){ str=str" "a[i,j];} print str }}') /home/IBM/DSProjects/CCDBMDM_SIT/DL/ 

Friday, 11 September 2015

How to read and write data from Greenplum Connector using InfoSphere DataStage

Greenplum Connector is a generic connector which comes under Connectivity component category in IBM InfoSphere Information Server. It is used by IBM InfoSphere DataStage to perform extract, load, lookup and metadata operations (by using InfoSphere Metadata Asset Manager (IMAM)) on Greenplum Database. Greenplum Connector is supported on Parallel Canvas in InfoSphere DataStage. This blog provides an overview of how to read and write data from Pivotal Greenplum database using Greenplum Connector through DataStage.
To connect to Pivotal Greenplum database through Greenplum Connector, it must have the proper driver to be loaded to connect to a particular database in order to extract, load or lookup the data.

Greenplum CC Configuration:
To connect to a Greenplum database, you must first configure an ODBC data source definition (DSN) for the database by using the IBM Greenplum Wire Protocol ODBC driver. Before you begin ensure that the ODBC driver for Greenplum libraries is installed.
a) Windows OS Procedure:
1. Start the Microsoft ODBC Data Source Administrator.
 On a 32-bit Windows computer, click Start > Control panel > Administrative Tools > Data Sources (ODBC)
On a 64-bit Windows computer, navigate to C:\Windows\SysWOW64\odbcad32.exe.
Note: On Windows, InfoSphere® Information Server is a 32-bit application. Even on a 64-bit Windows computer, the connector is running as a 32-bit application. Therefore you must use the 32-bit version of the ODBC Data Source Administrator as the Greenplum connector will not be able to locate DSN definitions created in the 64-bit ODBC Data Source Administrator.
2. On the System DSN page, click Add.
3. On the Create New Data Source page, select the IBM Greenplum Wire Protocol driver and click Finish. For information about configuring the driver options, see the Greenplum Wire Protocol Driver chapter in the DataDirect Connect Series for ODBC User's Guide.
b) UNIX OS Procedure:
1. Add a new ODBC DSN definition for the Greenplum Wire Protocol to the .odbc.ini file.
2. Specify the Hostname and PortNumber to the host and port where Greenplum database server is running.
3. Specify the Database name to default database to use for connections using the new ODBC DSN.
4. Save the .odbc.ini file.
Example
[Greenplum_DEV_SERVER]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMgplm00.so
Description=DataDirect 7.0 Greenplum Wire Protocol
Database=gp_dev
HostName=gp_host
PortNumber=5432
where, gp_dev is the name of the Greenplum database the DSN connects to and gp_host is the host name where the Greenplum server resides.
* For more information about configuring and other driver options, see the Greenplum Wire Protocol Driver chapter in the DataDirect Connect Series for ODBC User's Guide.
c) Greenplum parallel file distribution program (gpfdist)
The Greenplum Connector stage exchanges data with the Greenplum server by using the Greenplum file distribution program, which is called gpfdist.  The gpfdist program runs on the database client, and it must be installed on the InfoSphere Information Server engine tier computer. For data to be transferred by using the gpfdist protocol, a network route must be present to enable bidirectional access by using an IP address and optionally the presence of a DNS server to facilitate the name resolution. The connector invokes a gpfdist process on every physical computer node and creates the external table. The host of the external table data is identified by the fastname entry in the parallel engine configuration file ($APT_CONFIG_FILE).
In order for the connector to invoke gpfdist on each engine tier, the location of gpfdist(%GPHOME_LOADERS%\bin) must be in the system path. In addition, the location of gpfdist dependent libraries (%GPHOME_LOADERS%\lib) must be in the system library path. On Windows, the system environment variable PATH is updated in the Advanced system settings. On Linux, the PATH environment variable is updated in the dsenv script. 
Note: On Windows, the Greenplum installer adds %GPHOME_LOADERS%\bin and %GPHOME_LOADERS%\lib to the PATH system environment variable. Verify that these directories are in the PATH.
Greenplum Connector as a Source:
The following figure shows a sample job which can extract the data from Greenplum database and put it into a Sequential File.



From the Job Canvas, Double click on Greenplum Connector Stage and go to the Output tab.

 
Connection:
Data source (Required) - Data source name (DSN) as defined in the ODBC driver manager or the odbc.ini file for a Greenplum database
Database (Optional) - Name of the Greenplum database on the server defined in the ODBC Data source. If not specified, the database specified in the Data source will be used.
Username (Required) - The user name for the ODBC connection
Password (Optional)* - The password for the ODBC connection
* Greenplum Database allows password less authentication, and can potentially support other forms of authentication (SSL, LDAP) through the ODBC driver. If the user would like to use these types of authentication methods then the connector should allow the Password property to be optional.
Usage :  Provide the source table name in Table Name option.

 
For Column Definition:
Go to the Columns tab under Outputs tab and specify the columns of the table which you want to extract from Greenplum Database.

Greenplum Connector as a target:
The following figure shows a sample job which can load the data from a sequential file into Greenplum database.


 
From the Job Canvas, Double click on Greenplum Connector Stage and go to the Input tab.


 
Connection:
For Connection, please follow the same steps above which were performed when Greenplum CC
Usage:
When the user selects the insert mode, then the user should be able to choose the direct insert mode. Select the below options
Write Mode=Insert
Table Action= Target table name.

For Column Definition:
Go to the Columns tab under Inputs tab and specify the columns of the table in which you want to load data.
Conclusion
Greenplum connector can be use to perform operations like read data from Greenplum databases or write data to Greenplum databases or look up data in the contexts of those jobs. Also by using InfoSphere Metadata Asset Manager (IMAM), it can be used to import metadata from Greenplum database.
Disclaimer: “The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.”