IBM Db2

This page shows how to set up and use the IBM Db2 destination. The IBM Db2 destination loads data to an IBM Db2 destination.

Requirements

The appropriate version (32bit for 32bit OS, 64bit for 64bit OS) of the ADO .NET driver must be installed.
Select the IBM Data Server Driver Package and then the IBM Data Server Driver Package (Windows AMD64 and Intel EM64T) or IBM Data Server Driver Package (Windows 32-bit AMD and Intel x86), see IBM Data Server Client Packages - Version 11.5 GA.

If a fixed version is available, download the fixed version of the provider from the software vendor's website, see Overview IBM Data Server Client Packages.

Create a new IBM Db2 Destination

Follow the steps below to add a new IBM Db2 destination to Xtract Universal:

1. In the main window of the Designer, navigate to Server > Manage Destinations. The window “Manage Destinations” opens.
   
2. Click [Add] to create a new destination. The window "Destination Details" opens.
   
3. Enter a Name for the destination.
4. Select the destination type IBM Db2 from the drop-down menu. A list of connection details opens.
5. Fill out the destination details to connect to the destination.
6. Click [OK] to confirm your input.

The destination can now be assigned to extractions.

Destination Details

The destination details define the connection to the destination.

Provider

Enter the .Net provider for DB2. To install the provider refer to requirements.

Host Name / Port

Enter the name or IP of the DB2 server and the port number.

Username / Password

IBM Db2 authentication user name and password.

Database name

Enter the name of the IBM database.

Default schema

Enter the schema of the DB2 database.

Test Connection

Check the database connection.

Assign the IBM Db2 Destination to an Extraction

Extractions write data to their assigned destination. Follow the steps below to assign a destination to an extraction:

1. In the main window of the Designer, select an extraction.
2. Click [ Destination]. The window “Destination Settings” opens.
   
3. In the “Destination Settings” window, select a destination from the dropdown list.
   
4. Optional: edit the destination settings.
5. Click [OK] to confirm your input.

When running the extraction, the extracted SAP data is now written to the destination.

Destination Settings

The destination settings only affect the extraction that the destination is assigned to. To open the destination settings, select an extraction in the main window of the Designer and click [ Destination]. The window "Destination Settings" opens.

File Name

Determines the name of the target table. The following options are available:

Append timestamp

Add the timestamp in the UTC format (_YYYY_MM_DD_hh_mm_ss_fff) to the file name of the extraction

Use Script Expressions as Dynamic File Names

Script expressions can be used to generate a dynamic file name. This allows generating file names that are composed of an extraction's properties, e.g. extraction name, SAP source object. This scenario supports script expressions based on .NET and the following XU-specific custom script expressions:

#{Source.Name}#

#{Extraction.ExtractionName}#

#{Extraction.FullyQualifiedExtractionName}#

#{Extraction.Type}#

#{Extraction.SapObjectName}#

#{Extraction.Timestamp}#

#{Extraction.SapObjectName.TrimStart("/".ToCharArray())}#

#{Extraction.SapObjectName.Replace('/', '_')}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].LowerValue}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].UpperValue}#

#{Extraction.Context}#

#{Extraction.Fields["[NameSelectionFiels]"].Selections[0].Value}#

#{Odp.UpdateMode}#

#{TableExtraction.WhereClause}#

Column Name Style

Defines the style of the column name. Following options are available:

Convert dates

Converts the character-type SAP date (YYYYMMDD, e.g., 19900101) to a special date format (YYYY-MM-DD, e.g., 1990-01-01). Target data uses a real date data-type and not the string data-type to store dates.

Year 0

Converts the SAP date 00000000 to the entered value.

Year 9999

Converts the SAP date 9999XXXX to the entered value.

Invalid values

If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.

Preparation

Defines the action on the target database before the data is inserted into the target table.

To only create the table in the first step and not insert any data, you have two options:

1. Copy the SQL statement and execute it directly on the target data database.
2. Select the None option for Row Processing and execute the extraction.

Once the table is created, it is up to you to change the table definition, e.g., by creating corresponding key fields and indexes or additional fields.

Row Processing

Defines how the data is inserted into the target table.

Finalization

Defines the action on the target database after the data has been successfully inserted into the target table.

Transaction style

One Transaction

Preparation, Row Processing and Finalization are all performed in a single transaction.

• Advantage: clean rollback of all changes.
  
• Disadvantage: possibly extensive locking during the entire extraction period.

Three Transactions

Preparation, Row Processing and Finalization are each executed in a separate transaction.

• Advantage: clean rollback of the individual sections, possibly shorter locking phases than with One Transaction (e.g. with DDL in Preparation, the entire DB is only locked during preparation and not for the entire extraction duration).
  
• Disadvantage: no rollback of previous step possible (error in Row Processing only rolls back changes from Row Processing, but not Preparation).

RowProcessingOnly

Only Row Processing is executed in a transaction. Preparation and Finalization without an explicit transaction (implicit commits).

• Advantage: DDL in 'Preparation and Finalization* for DBMS that do not allow DDL in explicit transactions (e.g. AzureDWH)
  
• Disadvantage: no rollback of Preparation/Finalization.

No Transaction

No explicit transactions.

• Advantage: no transaction management required by DBMS (locking, DB transaction log, etc.). This means no locking and possible performance advantages.
  
• Disadvantage: no rollback

Merge Data

The following example depicts the update of the existing data records in a database by running an extraction to merge data. In this case, merging means changing a value of a field or inserting a new data row or updating an existing record in SAP.

With a merge command, the updated value is written to the destination database table. The merge command ensures delta processing: new records are inserted into the database and / or existing records are updated.

Prerequisites

You need a table with existing SAP data, in which new data can be merged.
Ideally, the table with existing data is created in the initial load with the corresponding Preparation option and filled with data with the Row Processing option Insert.

After the table is created, open SAP and change a field value in the SAP table that is used for the data merge.

Merge Command

The merge process is performed using a staging table and takes place in three steps:

1. A temporary table is created.
2. The data is inserted in the temporary table.
3. The temporary table is merged with the target table and then the temporary table is deleted.

Follow the steps below to set up the merge process in Xtract Universal:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions.
2. Click [ Destination]. The window "Destination Settings" opens.
3. Make sure to assign IBM Db2 destination to the extraction.
4. Apply the following destination settings:
   
5. Click [OK] and run the extraction.

More information about the updated fields can be found in the SQL statement.
It is possible to edit the SQL statement if necessary, e.g., to exclude certain columns from the update.
Fields that do not appear in the SQL statement are not affected by changes.

Custom SQL Statements

The IBM Db2 destination supports the use of custom SQl statements in the staging steps of the database. Follow the steps below to create custom SQL statements in the IBM Db2 destination:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions..
2. Click [ Destination], the window "Destination Settings" opens.
3. Make sure to assign the IBM Db2 destination to the extraction.

4. Select the option Custom SQL from the drop-down list in one of the following sections:

   • Preparation
   • Row Processing
   • Finalization

   

5. Click [Edit SQL] . The window "Edit SQL" opens.

6. Enter your custom SQL statement and click [OK] to confirm your input.

Use Templates

Existing SQL commands can be used as templates.
You can write your user-defined SQL expressions and adapt the loading of the data to your needs. You can additionally execute stored procedures that exist in the database. To do so, use the SQL templates provided in the following staging steps:

• Preparation, e.g., in Drop & Create or Create if Not Exists
• Row Processing, e.g., in Insert or Merge
• Finalization

Follow the steps below to generate a Custom SQL command from a template:

1. In one of the staging steps, select the Custom SQL option from the drop-down list .
2. Click [Edit SQL] . The dialogue "Edit SQL" opens.
   
3. Navigate to the drop-down menu and select an existing command .
4. Click [Generate Statement]. A new statement is generated.
   
5. Click [Copy] to copy the statement to the clipboard.
6. Click [OK] to confirm your input.

Check out the Microsoft SQL Server example for details on predefined expressions.

Use Script Expressions

You can use script expressions for Custom SQL commands. The following Xtract Universal specific custom script expressions are supported:

#{Extraction.ExtractionName}#

#{Extraction.FullyQualifiedExtractionName}#

#{Extraction.TableName }#

#{Extraction.RowsCount }#

#{Extraction.RunState}#

#{(int)Extraction.RunState}#

#{Extraction.Timestamp}#

For more information, see Script Expressions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#{
   iif
   (
      ExistsTable("MAKT"),
      "TRUNCATE TABLE \"MAKT\";",
      "
         CREATE TABLE \"MAKT\"(
            \"MATNR\" VARCHAR(18),
            \"SPRAS\" VARCHAR(2),
            \"MAKTX\" VARCHAR(40));
      "
   )
}#

Create a Status Overview

The table "ExtractionStatistics" provides an overview and status of the executed Xtract Universal extractions. To create the "ExtractionStatistics" table, create an SQL table according to the following example:

1
2
3
4
5
6
7

CREATE TABLE [dbo].[ExtractionStatistics](
    [TableName] [nchar](50) NULL,
    [RowsCount] [int] NULL,
    [Timestamp] [nchar](50) NULL,
    [RunState] [nchar](50) NULL
) ON [PRIMARY]
GO

The ExtractionStatistics table is filled in the Finalization process step, using the following SQL statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO [ExtractionStatistics]
(
     [TableName], 
     [RowsCount], 
     [Timestamp],
     [RunState]
)
VALUES
(
     '#{Extraction.TableName}#', 
     '#{Extraction.RowsCount}#', 
     '#{Extraction.Timestamp}#',
     '#{Extraction.RunState}#'
);