Module 13: Non-SAP Source Systems

Learn how to replicate data from non-SAP databases and systems using SLT.

1. Supported Non-SAP Sources

Overview

graph TD
    A[Oracle] -->|SLT| E[HANA Target]
    B[MS SQL Server] -->|SLT| E
    C[IBM DB2] -->|SLT| E
    D[MySQL/PostgreSQL] -->|ODBC| E
    F[Flat Files] -->|Smart Data Integration| E

Compatibility Matrix

Database	Version	Protocol	Change Capture	Performance
Oracle	11g, 12c, 19c	OCI / ODBC	Log-based CDC	Excellent
MS SQL Server	2016, 2019, 2022	ODBC	CDC / Triggers	Very Good
IBM DB2	10.5, 11.x	CLI / ODBC	Triggers	Good
MySQL	5.7, 8.0	ODBC	Binary logs	Good
PostgreSQL	10.x, 11.x, 12.x	ODBC	Triggers	Good

2. Oracle Database Integration

Connection Setup

Step 1: Install Oracle Client on SLT Server

# Download Oracle Instant Client
# Extract to /opt/oracle/instantclient_19_8

export ORACLE_HOME=/opt/oracle/instantclient_19_8
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH

# Verify installation
sqlplus system@oracle-db:1521/ORCL

Step 2: Configure ODBC Connection

# /etc/odbc.ini
[ORACLE_PROD]
Driver = Oracle 19 ODBC driver
ServerName = oracle-db.company.com
Port = 1521
Database = ORCL
UID = slt_user
PWD = ******

# Test connection
isql -v ORACLE_PROD

Step 3: Create MT_ID in SLT

Transaction: LTRC → Create Configuration

MT_ID: ORA_TO_HANA_01
Source System Type: ● Non-SAP Database
Database Type: ● Oracle
Connection: ORACLE_PROD
Target: HANA_TARGET
Schema: ORACLE_REPL

Oracle-Specific Configuration

Change Data Capture (CDC)

-- Enable Oracle CDC (run as SYSDBA)
GRANT SELECT ANY TRANSACTION TO slt_user;
GRANT EXECUTE_CATALOG_ROLE TO slt_user;
GRANT SELECT_CATALOG_ROLE TO slt_user;

-- Enable supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE ORDERS ADD SUPPLEMENTAL LOG GROUP order_log 
  (ORDER_ID, CUSTOMER_ID, ORDER_DATE) ALWAYS;

-- Verify
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
-- Result: YES

Table Replication

LTRC → Select Tables

Schema: SALESDB
Tables:
├── ☑ ORDERS (250M rows)
├── ☑ ORDER_ITEMS (1.2B rows)
├── ☑ CUSTOMERS (15M rows)
└── ☑ PRODUCTS (500K rows)

Options:
├── Initial Load: Full
├── Delta Method: ● Log-Based CDC
└── Batch Size: 50,000

Performance Tuning

Oracle Source Optimization:
1. Add indexes on timestamp columns:
   CREATE INDEX IDX_ORD_MOD ON ORDERS(MODIFIED_DATE);

2. Increase SGA:
   ALTER SYSTEM SET sga_max_size = 8G SCOPE=SPFILE;
   
3. Tune log writer:
   ALTER SYSTEM SET log_buffer = 67108864;

Result:
├── Initial load: 1.2B records in 4 hours
├── Delta throughput: 50K records/second
└── Latency: <3 seconds

3. Microsoft SQL Server Integration

Connection Setup

Transaction: LTRC → Create Configuration

MT_ID: MSSQL_TO_HANA_01
Source: MS SQL Server
Server: mssql-server.company.com
Port: 1433
Database: SalesDB
Authentication: ● SQL Server Authentication
User: slt_user
Password: ******

Enable Change Data Capture

-- Enable CDC on database (run as sysadmin)
USE SalesDB;
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on specific tables
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Orders',
  @role_name = NULL,
  @supports_net_changes = 1;

-- Verify
SELECT name, is_cdc_enabled 
FROM sys.databases
WHERE name = 'SalesDB';
-- Result: is_cdc_enabled = 1

Replication Configuration

Table Selection:
├── dbo.Orders → MSSQL_REPL.ORDERS
├── dbo.OrderItems → MSSQL_REPL.ORDER_ITEMS
├── dbo.Customers → MSSQL_REPL.CUSTOMERS
└── dbo.Products → MSSQL_REPL.PRODUCTS

Field Mapping:
├── DATETIME → TIMESTAMP
├── NVARCHAR(MAX) → NCLOB
├── UNIQUEIDENTIFIER → NVARCHAR(36)
└── BIT → BOOLEAN

Delta Method: ● CDC
Commit Frequency: 10,000 records

Monitoring SQL Server CDC

-- Check CDC job status
EXEC sys.sp_cdc_help_jobs;

-- Monitor CDC latency
SELECT 
  capture_instance,
  start_lsn,
  DATEDIFF(SECOND, tran_begin_time, GETDATE()) as latency_sec
FROM cdc.lsn_time_mapping
ORDER BY tran_begin_time DESC;

-- CDC table size
SELECT 
  t.name,
  SUM(s.used_page_count) * 8 / 1024 as size_mb
FROM sys.dm_db_partition_stats s
JOIN sys.tables t ON s.object_id = t.object_id
WHERE t.name LIKE 'cdc_%'
GROUP BY t.name;

4. IBM DB2 Integration

Connection Configuration

# Install DB2 client on SLT server
tar -xzvf v11.5_linuxx64_client.tar.gz
cd client
./db2_install

# Catalog database
db2 catalog tcpip node DB2NODE remote db2-server.company.com server 50000
db2 catalog database SALESDB at node DB2NODE
db2 connect to SALESDB user slt_user using password

SLT Configuration

Transaction: LTRC

MT_ID: DB2_TO_HANA_01
Source Type: ● Non-SAP (DB2)
Connection:
├── Node: DB2NODE
├── Database: SALESDB
├── Schema: SALES
└── User: slt_user

Target:
├── HANA System: HANA_TARGET
└── Schema: DB2_REPL

Trigger-Based CDC

-- Create change tracking table
CREATE TABLE SALES.ORDERS_CDC (
  ORDER_ID INT,
  OPERATION CHAR(1),  -- I/U/D
  CHANGED_AT TIMESTAMP,
  PRIMARY KEY (ORDER_ID, CHANGED_AT)
);

-- Create triggers
CREATE TRIGGER TRG_ORDERS_INSERT
AFTER INSERT ON SALES.ORDERS
REFERENCING NEW AS N
FOR EACH ROW
  INSERT INTO SALES.ORDERS_CDC 
  VALUES (N.ORDER_ID, 'I', CURRENT_TIMESTAMP);

CREATE TRIGGER TRG_ORDERS_UPDATE
AFTER UPDATE ON SALES.ORDERS
REFERENCING NEW AS N
FOR EACH ROW
  INSERT INTO SALES.ORDERS_CDC 
  VALUES (N.ORDER_ID, 'U', CURRENT_TIMESTAMP);

CREATE TRIGGER TRG_ORDERS_DELETE
AFTER DELETE ON SALES.ORDERS
REFERENCING OLD AS O
FOR EACH ROW
  INSERT INTO SALES.ORDERS_CDC 
  VALUES (O.ORDER_ID, 'D', CURRENT_TIMESTAMP);

5. MySQL Integration

Setup and Configuration

# Enable binary logging (my.cnf)
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7

# Restart MySQL
systemctl restart mysql

# Create replication user
CREATE USER 'slt_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slt_user'@'%';
GRANT SELECT ON sales.* TO 'slt_user'@'%';
FLUSH PRIVILEGES;

SLT Configuration

Transaction: LTRC

MT_ID: MYSQL_TO_HANA_01
Source: MySQL Database
Host: mysql-server.company.com
Port: 3306
Database: sales
User: slt_user

Delta Method: ● Binary Log
Position Tracking: Enabled

Monitor Binary Logs

-- Check binary log status
SHOW BINARY LOGS;

-- Current position
SHOW MASTER STATUS;

-- Events in log
SHOW BINLOG EVENTS IN 'mysql-bin.000042' LIMIT 10;

-- Purge old logs
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

6. PostgreSQL Integration

Setup

-- Enable logical replication (postgresql.conf)
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- Restart PostgreSQL
pg_ctl restart

-- Create replication user
CREATE USER slt_user WITH REPLICATION PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO slt_user;

-- Create publication
CREATE PUBLICATION slt_pub FOR ALL TABLES;

SLT Configuration

Transaction: LTRC

MT_ID: PGSQL_TO_HANA_01
Source: PostgreSQL
Host: postgres-server.company.com
Port: 5432
Database: sales
Schema: public
User: slt_user

Replication Method: ● Logical Replication
Publication: slt_pub
Slot: slt_slot_01

7. Data Type Mapping

Mapping Table

Source DB	Source Type	HANA Type	Notes
Oracle	NUMBER(p,s)	DECIMAL(p,s)	Direct
Oracle	VARCHAR2	NVARCHAR	Encoding
Oracle	CLOB	NCLOB	Large text
Oracle	DATE	TIMESTAMP	Add time
SQL Server	DATETIME	TIMESTAMP	Direct
SQL Server	NVARCHAR(MAX)	NCLOB	> 5000 chars
SQL Server	BIT	BOOLEAN	Direct
DB2	TIMESTAMP	TIMESTAMP	Direct
DB2	CLOB	NCLOB	Large text
MySQL	DATETIME	TIMESTAMP	Direct
MySQL	TEXT	NCLOB	Large text
PostgreSQL	TIMESTAMP	TIMESTAMP	Direct
PostgreSQL	TEXT	NCLOB	Large text

Custom Mapping

LTRC → Advanced Settings → Data Type Mapping

Source: Oracle NUMBER(15,2)
Target: ● DECIMAL(15,2)
       ○ DOUBLE
       ○ BIGINT

Source: SQL Server UNIQUEIDENTIFIER
Target: ● NVARCHAR(36)
       ○ VARBINARY(16)

8. Heterogeneous Replication

Multi-Source Consolidation

graph TD
    A[Oracle ERP] -->|MT_ID_ORA| E[Central HANA]
    B[SQL Server CRM] -->|MT_ID_SQL| E
    C[MySQL E-Commerce] -->|MT_ID_MYS| E
    D[PostgreSQL HR] -->|MT_ID_PGS| E
    E --> F[Unified Data Model]

Key Management

-- Add source system identifier
CREATE COLUMN TABLE UNIFIED.CUSTOMERS (
  SOURCE_SYSTEM NVARCHAR(20),
  SOURCE_KEY NVARCHAR(50),
  GLOBAL_ID BIGINT GENERATED BY DEFAULT AS IDENTITY,
  CUSTOMER_NAME NVARCHAR(100),
  EMAIL NVARCHAR(100),
  PRIMARY KEY (SOURCE_SYSTEM, SOURCE_KEY),
  UNIQUE (GLOBAL_ID)
);

-- Insert from Oracle
INSERT INTO UNIFIED.CUSTOMERS
SELECT 'ORACLE_ERP', CUSTOMER_ID, CUSTOMER_NAME, EMAIL
FROM ORACLE_REPL.CUSTOMERS;

-- Insert from SQL Server
INSERT INTO UNIFIED.CUSTOMERS
SELECT 'SQLSERVER_CRM', CUST_GUID, CUST_NAME, EMAIL_ADDR
FROM MSSQL_REPL.CUSTOMERS;

9. Performance Considerations

Network Optimization

Challenge: High latency to remote non-SAP systems

Solutions:
1. Dedicated network link (MPLS/VPN)
2. Compression: Enable in LTRC → Advanced
3. Batch size tuning:
   Fast network (LAN): 100,000 records
   Slow network (WAN): 10,000 records
4. Parallel jobs:
   High bandwidth: 16 jobs
   Low bandwidth: 4 jobs

Source Database Tuning

Oracle:
├── Increase PROCESSES parameter
├── Tune redo log size
└── Add indexes on timestamp columns

SQL Server:
├── Increase CDC retention
├── Add filtered indexes
└── Schedule CDC cleanup off-peak

DB2:
├── Increase buffer pools
├── Optimize triggers (avoid complex logic)
└── Archive old CDC data

MySQL:
├── Increase binlog cache size
├── Optimize binary log rotation
└── Use SSD for binlog storage

10. Troubleshooting

Common Issues

Issue 1: Connection Timeout

Error: Unable to connect to source database

Solution:
1. Test connectivity: telnet <host> <port>
2. Check firewall rules
3. Verify credentials
4. Increase timeout: LTRC → Advanced → Timeout = 300s

Issue 2: Data Type Mismatch

Error: Cannot convert NVARCHAR(MAX) to target type

Solution:
1. LTRC → Data Type Mapping
2. Map NVARCHAR(MAX) → NCLOB
3. Restart replication

Issue 3: CDC Lag

Problem: Delta replication 10 minutes behind

Diagnosis:
1. Check CDC job status (database-specific)
2. Monitor CDC table growth
3. Review source database load

Solution:
1. Increase CDC cleanup frequency
2. Add more SLT parallel jobs
3. Optimize source database performance

Summary

✅ Non-SAP source system support ✅ Oracle integration with log-based CDC ✅ SQL Server with CDC ✅ DB2 with trigger-based replication ✅ MySQL binary log replication ✅ PostgreSQL logical replication ✅ Data type mapping strategies ✅ Heterogeneous consolidation ✅ Performance tuning ✅ Troubleshooting guide

Next: Module 14 - Cloud Integration