Skip to main content

Tracing & Diagnostics

ERPL-Web includes powerful tracing capabilities to help you debug network calls, optimize performance, and troubleshoot issues. See exactly what's happening under the hood.

Overview​

Tracing captures detailed information about:

  • HTTP requests and responses
  • URL construction and query parameters
  • Request/response timing
  • Pagination and chunking
  • Retry logic and error handling
  • OData metadata extraction
  • Datasphere endpoint selection
  • ODP subscription state changes

When to use tracing:

  • ✅ Debugging authentication issues
  • ✅ Understanding slow queries
  • ✅ Troubleshooting API errors
  • ✅ Optimizing data extraction
  • ✅ Learning how features work

Quick Start​

-- Enable tracing
SET erpl_trace_enabled = TRUE;

-- Run your query
SELECT * FROM http_get('https://api.example.com/data');

-- Check console output for trace logs

That's it! Trace information appears in your console.

Configuration​

Enable/Disable Tracing​

-- Enable tracing
SET erpl_trace_enabled = TRUE;

-- Disable tracing
SET erpl_trace_enabled = FALSE;

-- Check current setting
SELECT current_setting('erpl_trace_enabled');

Trace Levels​

Control the verbosity of trace output:

SET erpl_trace_level = 'DEBUG';

Available Levels:

  • TRACE - Most verbose, every detail
  • DEBUG - Detailed information for debugging
  • INFO - General informational messages
  • WARN - Warnings and potential issues
  • ERROR - Only errors

Example:

-- Maximum detail
SET erpl_trace_level = 'TRACE';

-- Moderate detail (recommended)
SET erpl_trace_level = 'DEBUG';

-- Minimal output
SET erpl_trace_level = 'ERROR';

Trace Output Destination​

Choose where trace logs appear:

SET erpl_trace_output = 'both';

Options:

  • console - Print to console/terminal only
  • file - Write to log file only
  • both - Print to console AND write to file

Example:

-- Console only (default)
SET erpl_trace_output = 'console';

-- File only (for production)
SET erpl_trace_output = 'file';

-- Both (for debugging)
SET erpl_trace_output = 'both';

File Configuration​

When using file output, configure the log file:

-- Set log file path
SET erpl_trace_file_path = './erpl_trace.log';

-- Set maximum file size (bytes)
SET erpl_trace_max_file_size = 10485760;  -- 10MB

-- Enable log rotation
SET erpl_trace_rotation = TRUE;

File Settings Explained:

  • erpl_trace_file_path: Where to write log file (default: ./erpl_trace.log)
  • erpl_trace_max_file_size: Max file size before rotation (default: 10MB)
  • erpl_trace_rotation: Enable automatic rotation when file reaches max size

Example with all file settings:

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';
SET erpl_trace_output = 'file';
SET erpl_trace_file_path = '/var/log/erpl/trace.log';
SET erpl_trace_max_file_size = 52428800;  -- 50MB
SET erpl_trace_rotation = TRUE;

What Gets Traced​

HTTP Functions​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

SELECT * FROM http_get('https://httpbun.com/json');

Trace Output:

[DEBUG] HTTP GET Request
  URL: https://httpbun.com/json
  Headers: {Accept: application/json}
  Timeout: 30000ms

[DEBUG] HTTP Response Received
  Status: 200 OK
  Content-Type: application/json
  Content-Length: 429 bytes
  Response Time: 245ms

OData Functions​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

SELECT * FROM odata_read('https://services.odata.org/TripPinRESTierService/People')
LIMIT 5;

Trace Output:

[DEBUG] OData Request Initiated
  URL: https://services.odata.org/TripPinRESTierService/People
  Method: GET

[DEBUG] OData Metadata Fetch
  Metadata URL: https://services.odata.org/TripPinRESTierService/$metadata
  Version: OData V4

[DEBUG] OData Predicate Pushdown
  $top: 5
  Final URL: https://services.odata.org/TripPinRESTierService/People?$top=5

[DEBUG] OData Type Mapping
  UserName: Edm.String -> VARCHAR
  FirstName: Edm.String -> VARCHAR
  Age: Edm.Int32 -> INTEGER

[DEBUG] OData Pagination
  Current Page: 1
  Records Fetched: 5
  Has Next: false

[DEBUG] OData Request Complete
  Total Records: 5
  Execution Time: 1247ms

Datasphere Functions​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

SELECT * FROM datasphere_show_spaces();

Trace Output:

[DEBUG] Datasphere Request
  Function: datasphere_show_spaces
  Tenant: acme-corp
  Data Center: eu10

[DEBUG] OAuth2 Token Check
  Token Valid: true
  Expires In: 2847 seconds
  
[DEBUG] DWAAS API Request
  Endpoint: /dwaas-core/v1/spaces
  Method: GET
  
[DEBUG] Datasphere Response
  Status: 200
  Spaces Found: 3
  Response Time: 456ms

ODP Functions​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

SELECT * FROM odp_odata_read(
  'https://sap/odata/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
  secret='sap_system'
);

Trace Output:

[DEBUG] ODP OData Request
  Entity Set: FactsOf0D_NW_C01
  Service: Z_ODP_BW_1_SRV

[DEBUG] Subscription Check
  Subscription ID: sub_abc123
  Status: active
  Last Delta Token: D20250914154609_000019000

[DEBUG] ODP Delta Request
  URL: https://sap/odata/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01?$deltatoken=D20250914154609_000019000
  
[DEBUG] ODP Response Processing
  Package Count: 3
  Records Received: 452
  New Records (N): 87
  Updated Records: 342
  Deleted Records (D): 23
  New Delta Token: D20250914161234_000020000
  
[DEBUG] Subscription Update
  Updated Delta Token in Database
  
[DEBUG] ODP Audit Log Written
  Execution Time: 3456ms

Reading Trace Files​

Tail Log File​

# Watch logs in real-time
tail -f ./erpl_trace.log

# Last 100 lines
tail -n 100 ./erpl_trace.log

# Filter for errors
grep 'ERROR' ./erpl_trace.log

Search for Specific Patterns​

# Find all HTTP requests
grep 'HTTP.*Request' ./erpl_trace.log

# Find slow queries (> 5 seconds)
grep 'Execution Time: [5-9][0-9][0-9][0-9]ms' ./erpl_trace.log

# Find authentication issues
grep -i 'auth\|401\|403' ./erpl_trace.log

Analyze with DuckDB​

-- Load log file into DuckDB
CREATE TABLE trace_logs AS
SELECT * FROM read_csv_auto('erpl_trace.log', 
  delim='|',
  header=false,
  columns={'timestamp': 'VARCHAR', 'level': 'VARCHAR', 'message': 'VARCHAR'}
);

-- Find slowest operations
SELECT message, COUNT(*) as occurrences
FROM trace_logs
WHERE message LIKE '%Execution Time:%'
ORDER BY occurrences DESC;

-- Errors by hour
SELECT 
  DATE_TRUNC('hour', TRY_CAST(timestamp AS TIMESTAMP)) as hour,
  COUNT(*) as error_count
FROM trace_logs
WHERE level = 'ERROR'
GROUP BY hour
ORDER BY hour;

Performance Analysis​

Identify Slow Queries​

Enable tracing and look for timing information:

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Run your query
SELECT * FROM datasphere_read_relational('SALES', 'LARGE_VIEW');

-- Look for "Execution Time" in output

Optimization Tips Based on Trace:

  • High network time → Check network latency
  • High metadata fetch time → Metadata is cached after first call
  • High parsing time → Simplify JSON/XML structure
  • Multiple roundtrips → Use predicate pushdown

Monitor Request Patterns​

SET erpl_trace_enabled = TRUE;

-- See pagination in action
SELECT * FROM odata_read('https://services.odata.org/V2/Northwind/Northwind.svc/Orders');

Trace shows:

[DEBUG] OData Pagination
  Page 1: 100 records
  Page 2: 100 records
  Page 3: 100 records
  Page 4: 30 records
  Total: 330 records

Optimization: Use LIMIT to avoid fetching all pages:

SELECT * FROM odata_read(...) LIMIT 100;  -- Only fetch first page

Analyze Retry Logic​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Simulate flaky network
SELECT * FROM http_get('https://unstable-api.example.com/data');

Trace shows retries:

[DEBUG] HTTP Request Failed
  Status: 503 Service Unavailable
  Attempt: 1/3
  Retry in: 1000ms

[DEBUG] HTTP Retry Attempt
  Attempt: 2/3
  
[DEBUG] HTTP Request Succeeded
  Status: 200 OK
  Attempts: 2

Troubleshooting with Traces​

Authentication Issues​

Enable tracing:

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

SELECT * FROM http_get('https://api.example.com/data');

Look for:

  • 401 Unauthorized → Wrong credentials
  • 403 Forbidden → Insufficient permissions
  • Token Expired → Need to refresh OAuth2 token
  • Invalid Client → Wrong CLIENT_ID or CLIENT_SECRET

Network Issues​

Trace shows:

[ERROR] Connection Refused
  URL: https://api.example.com
  Error: Connection timeout after 30000ms

Solutions:

  • Check network connectivity
  • Verify URL is correct
  • Check firewall rules
  • Increase timeout: timeout := 60000

OData Metadata Issues​

Trace shows:

[ERROR] OData Metadata Parse Error
  URL: https://api.example.com/odata/$metadata
  Error: Invalid XML

Solutions:

  • Verify service supports OData
  • Check if service is V2 or V4
  • Ensure metadata endpoint is accessible

ODP Subscription Issues​

Trace shows:

[WARN] ODP Subscription Not Found
  Entity: FactsOf0D_NW_C01
  Creating New Subscription...

This is normal for first extraction. If it happens repeatedly:

  • Check subscription storage
  • Verify database permissions
  • Check for subscription cleanup scripts

Advanced Trace Analysis​

Custom Log Parser​

import re
from datetime import datetime

def parse_trace_log(filepath):
    """Parse ERPL trace log file"""
    with open(filepath, 'r') as f:
        for line in f:
            match = re.match(r'\[(\w+)\] (.+)', line)
            if match:
                level, message = match.groups()
                if 'Execution Time:' in message:
                    time_ms = int(re.search(r'(\d+)ms', message).group(1))
                    if time_ms > 5000:  # Slow query
                        print(f"SLOW: {message}")

parse_trace_log('erpl_trace.log')

Trace Aggregation​

-- Parse trace logs with DuckDB
CREATE TABLE trace_parsed AS
SELECT 
  regexp_extract(line, '\[(\w+)\]', 1) as level,
  regexp_extract(line, 'Execution Time: (\d+)ms', 1)::INTEGER as exec_time_ms,
  line
FROM read_csv_auto('erpl_trace.log', header=false, columns={'line': 'VARCHAR'});

-- Statistics
SELECT 
  level,
  COUNT(*) as count,
  AVG(exec_time_ms) as avg_time,
  MAX(exec_time_ms) as max_time
FROM trace_parsed
WHERE exec_time_ms IS NOT NULL
GROUP BY level;

Production Considerations​

Log Rotation​

-- Enable rotation to prevent disk space issues
SET erpl_trace_rotation = TRUE;
SET erpl_trace_max_file_size = 52428800;  -- 50MB

How rotation works:

  • When log file reaches max size
  • Current file renamed to erpl_trace.log.1
  • New erpl_trace.log created
  • Old rotated files eventually purged (keep last 5)

Performance Impact​

Tracing has minimal overhead, but for production:

-- Production: Only log errors
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'ERROR';
SET erpl_trace_output = 'file';

-- Development: Full debugging
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'TRACE';
SET erpl_trace_output = 'both';

-- Optimal: Disable in production unless troubleshooting
SET erpl_trace_enabled = FALSE;

Centralized Logging​

For production deployments, send logs to centralized system:

# Ship logs to ELK, Splunk, etc.
tail -f /var/log/erpl/trace.log | logstash -f config.conf

# Or use file rotation + log shipper
# Filebeat, Fluentd, etc.

Examples by Scenario​

Debug Authentication​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Test authentication
SELECT * FROM http_get(
  'https://api.example.com/test',
  auth := 'user:pass',
  auth_type := 'BASIC'
);

-- Check trace for:
-- - Auth header construction
-- - 401/403 responses
-- - Token expiry messages

Optimize Slow Query​

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Run slow query
SELECT * FROM datasphere_read_analytical('SALES', 'BIG_VIEW');

-- Look for:
-- - Network time
-- - Metadata fetch time
-- - Row processing time
-- - Pagination count

-- Then optimize based on findings

Monitor API Rate Limits​

SET erpl_trace_enabled = TRUE;

-- Make multiple requests
SELECT * FROM http_get('https://api.example.com/data');
SELECT * FROM http_get('https://api.example.com/more');

-- Check trace for:
-- - 429 Too Many Requests
-- - Retry-After headers
-- - Rate limit warnings

Best Practices​

1. Enable for Debugging, Disable for Production​

-- Debug mode
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Production mode
SET erpl_trace_enabled = FALSE;

2. Use Appropriate Trace Levels​

-- Too verbose (slow)
SET erpl_trace_level = 'TRACE';

-- Good for debugging
SET erpl_trace_level = 'DEBUG';

-- Production
SET erpl_trace_level = 'ERROR';

3. Rotate Logs Regularly​

SET erpl_trace_rotation = TRUE;
SET erpl_trace_max_file_size = 10485760;  -- 10MB

4. Sanitize Sensitive Data​

Traces may contain URLs with parameters. Review logs before sharing:

[DEBUG] HTTP GET https://api.example.com/data?api_key=SECRET123

Consider filtering sensitive params from logs.

Next Steps​

Summary​

ERPL-Web's tracing provides:

✅ Detailed - See every HTTP request and response
✅ Configurable - Control verbosity and output
✅ Production Ready - File rotation and log levels
✅ Actionable - Performance metrics for optimization
✅ Comprehensive - Covers all ERPL-Web features

Debug smarter, not harder with ERPL-Web tracing!