Using HTTP_POST_VERBOSE on IBM i for REST API Calls

IBM i provides powerful SQL functions for making HTTP requests, allowing seamless integration with REST APIs. One of the most useful functions is QSYS2.HTTP_POST_VERBOSE, which sends a POST request and returns detailed response data. This capability is perfect for connecting IBM i applications to modern web services, whether you’re sending data to an external API or caching results with Redis, as I’ve done in my redis400 project.

In this article, we’ll cover:

How to construct HTTP headers (including authentication and SSL settings)
Making POST requests with HTTP_POST_VERBOSE
Processing the response (status code, headers, and body)

Let’s dive in!

1. Building the HTTP Request Headers

The HTTP_POST_VERBOSE function requires HTTP headers to be passed in JSON format. Depending on your security requirements, you may need to include headers like Content-Type, Content-Length, Authorization, and SSL settings for secure connections.

Key Headers to Include

Content-Type: Typically application/json for REST APIs.
Content-Length: The length of the input data (calculated dynamically).
Authorization: A Bearer token for authenticated APIs.
SSL Settings: Options like sslCertificateStoreFile for custom certificates or sslTolerate to handle certificate issues.

-- If HTTPS is enabled
If IN_USE_HTTPS = 1 Then
    -- Use a custom certificate store (if not default)
    If IN_USE_DEFAULT_CERT_STORE = 0 Then
        Set REQ_HEADER = Json_Object(
            'header' Value 'content-type, application/json',
            'header' Value 'content-length, ' Concat Length(RTrim(IN_DATA)),
            'header' Value 'Authorization, Bearer ' Concat Trim(TOKEN),
            'sslCertificateStoreFile' Value Trim(APP_CERT_STORE)
        );
    Else
        -- Use default SSL trust (ignore some cert issues)
        Set REQ_HEADER = Json_Object(
            'header' Value 'content-type, application/json',
            'header' Value 'content-length, ' Concat Length(RTrim(IN_DATA)),
            'header' Value 'Authorization, Bearer ' Concat Trim(TOKEN),
            'sslTolerate' Value 'true'
        );
    End If;
Else
    -- Plain HTTP (no SSL)
    Set REQ_HEADER = Json_Object(
        'header' Value 'content-type, application/json',
        'header' Value 'content-length, ' Concat Length(RTrim(IN_DATA)),
        'header' Value 'Authorization, Bearer ' Concat Trim(TOKEN)
    );
End If;

This code checks if HTTPS is enabled (IN_USE_HTTPS = 1) and whether to use a custom certificate store (IN_USE_DEFAULT_CERT_STORE = 0). It then builds the REQ_HEADER JSON object with the necessary headers, including Content-Type, Content-Length, and Authorization. For HTTPS, it either specifies a custom certificate store (sslCertificateStoreFile) or tolerates minor certificate issues (sslTolerate).

2. Making the POST Request with HTTP_POST_VERBOSE

The QSYS2.HTTP_POST_VERBOSE function is designed to send HTTP POST requests and return detailed response data. It takes three parameters:

URL: The API endpoint (e.g., https://api.example.com/data).
IN_DATA: The JSON payload (as a CLOB).
REQ_HEADER: The headers in JSON format (constructed in the previous step).

Example: Executing the POST Request

Here’s how to execute the POST request and extract the HTTP status code from the response:

SELECT 
    IFNULL(R.HTTP_CODE, '5xx') 
INTO 
    RESP_CODE  
FROM 
    TABLE(QSYS2.HTTP_POST_VERBOSE(RTRIM(URL), RTRIM(IN_DATA), RTRIM(REQ_HEADER))) A  
CROSS JOIN 
    JSON_TABLE(
        REPLACE(A.RESPONSE_HTTP_HEADER, '=', ':'),  
        '$' COLUMNS(
            HTTP_CODE CHAR(3) PATH '$.HTTP_STATUS_CODE'  
        )
    ) R;

Key Points

HTTP_POST_VERBOSE returns a table with columns like RESPONSE_HTTP_HEADER (headers as a key=value string) and RESPONSE_BODY (the response data).
The RESPONSE_HTTP_HEADER is in a Key=Value format, so we use Replace(A.RESPONSE_HTTP_HEADER, ‘=’, ‘:’) to convert it to JSON (Key:Value) for parsing with JSON_TABLE.
We extract the HTTP_STATUS_CODE (e.g., 200, 401, 500) using JSON_TABLE and store it in RESP_CODE.
If the request fails and no status code is returned, we default to ‘5xx’ (indicating a server error).

This approach is useful for scenarios like sending data to an external API and confirming the request succeeded before proceeding with further processing.

3. Handling the Response

The HTTP_POST_VERBOSE function provides detailed response data, including:

HTTP Status Code: E.g., 200 (success), 401 (unauthorized), 500 (server error).
Response Headers: Useful for pagination, rate limits, or debugging.
Response Body: The actual data returned by the API (e.g., JSON, XML).

Example: Extracting Response Data

To retrieve both the status code and the response body, you can modify the query like this:

SELECT 
    R.HTTP_CODE,
    A.RESPONSE_BODY
FROM 
    TABLE(QSYS2.HTTP_POST_VERBOSE(
        RTRIM(URL), 
        RTRIM(IN_DATA), 
        RTRIM(REQ_HEADER)
    ) A
CROSS JOIN 
    JSON_TABLE(
        REPLACE(A.RESPONSE_HTTP_HEADER, '=', ':'),
        '$' COLUMNS(
            HTTP_CODE CHAR(3) PATH '$.HTTP_STATUS_CODE'
        )
    ) R;

This query returns the HTTP status code (HTTP_CODE) and the response body (RESPONSE_BODY), which you can then parse further (e.g., using JSON_TABLE if the body is JSON). For example, in my redis400 project, I use this to retrieve data from a Redis server via a REST API, ensuring I can cache the response efficiently.

Best Practices

To make the most of HTTP_POST_VERBOSE, follow these best practices:

Always Check the Response Code: Handle 4xx (client errors) and 5xx (server errors) appropriately. For example, a 401 might indicate an expired token, while a 429 suggests rate limiting.
Use HTTPS in Production: Avoid plain HTTP to ensure data security, especially when sending sensitive data.
Log Errors and Performance: Track API failures and response times to identify issues early. You can store logs in a database table or a user space on IBM i.
Retry on Transient Failures: For errors like 429 Too Many Requests, implement a retry mechanism with exponential backoff to handle transient issues gracefully.

Conclusion

IBM i’s QSYS2.HTTP_POST_VERBOSE function is a powerful tool for integrating with REST APIs directly from SQL, enabling seamless communication with modern web services. By properly constructing headers, making POST requests, and handling responses, you can connect IBM i to a wide range of systems—whether it’s caching data with Redis in my redis400 project or integrating with AI APIs for advanced analytics. This approach simplifies development and ensures your IBM i applications can interact with the modern web efficiently.

ER April 9, 2025 No Comments IBM i, SQL

REDIS400: Redis SQL Functions for IBM i: A Comprehensive Tutorial

Introduction

Welcome to this comprehensive tutorial on using Redis SQL Functions for IBM i. This powerful integration allows IBM i developers to leverage Redis’s high-performance caching capabilities directly from SQL queries. Whether you’re looking to implement session storage, application caching, or real-time data processing, these functions provide a seamless bridge between your IBM i database and Redis.

Prerequisites

Before we begin, ensure you have:

Access to an IBM i system with development tools (ILE C compiler, SQL)
A running Redis server accessible from your IBM i (default: 127.0.0.1:6379)
Basic familiarity with IBM i development and Redis concepts

Installation and Setup

Step 1: Clone the Repository

git clone https://github.com/krakadin/redis400.git
cd redis400

Step 2: Build the Project

gmake

This will:

Create the REDIS400 library
Compile all C modules
Create the service program
Register all SQL functions

Step 3: Verify Installation

Check that all objects were created successfully:

SELECT * FROM QSYS2.SYSFUNCS 
WHERE SPECIFIC_NAME LIKE 'REDIS_%';

Example:

-- Store a simple key-value pair
VALUES REDIS_SET('app:config:timeout', '30');

-- Store JSON data
VALUES REDIS_SET('user:1001', '{"name":"John","email":"john@example.com"}');

Best Practices:

Use descriptive key names with a consistent naming convention
Consider serializing complex data to JSON before storage
For temporary data, combine with REDIS_EXPIRE (covered later)

2. REDIS_GET – Retrieving Data

Syntax:

SELECT REDIS_GET(key) FROM SYSIBM.SYSDUMMY1;

Example:

-- Retrieve a simple value
SELECT REDIS_GET('app:config:timeout') AS timeout FROM SYSIBM.SYSDUMMY1;

-- Use in a more complex query
SELECT u.user_id, u.user_name, REDIS_GET('user_pref:' || u.user_id) AS preferences
FROM users u
WHERE u.department = 'SALES';

-- From JSON data
SELECT x.*
    FROM JSON_TABLE(
            REDIS_GET('user:1001'),
            '$'
            EMPTY ON ERROR COLUMNS(
                name VARCHAR(256) PATH '$.name', 
                email VARCHAR(256) PATH '$.email', 
                age INTEGER PATH '$.age' DEFAULT 27 ON EMPTY
            )
        ) AS x;

Handling Results:

Returns NULL if the key doesn’t exist
For numeric values, you may need to CAST the result: CAST(REDIS_GET('count') AS INTEGER)

3. REDIS_INCR – Atomic Counter

Syntax:

VALUES REDIS_INCR(key);

Example:

-- Create or increment an order counter
VALUES REDIS_INCR('orders:current_id');

-- Use in an application
SET order_id = REDIS_INCR('orders:current_id');
INSERT INTO orders VALUES(order_id, CURRENT_TIMESTAMP, ...);

Key Features:

Atomic operation – safe for concurrent access
If key doesn’t exist, initializes to 0 then increments to 1
Returns the new value after increment

4. REDIS_DEL – Removing Data

Syntax:

VALUES REDIS_DEL(key);

Example:

-- Remove a single key
VALUES REDIS_DEL('temp:session:abc123');

-- Clean up old data (in a stored procedure)
BEGIN
  DECLARE keys_deleted INT;
  SET keys_deleted = REDIS_DEL('old:data:*');
  -- Returns count of keys deleted
END

Return Value:

1 if the key was deleted
0 if the key didn’t exist

5. REDIS_EXPIRE – Setting Expiration

Syntax:

SELECT REDIS_EXPIRE(key, seconds) FROM SYSIBM.SYSDUMMY1;

Example:

-- Set a 30-minute expiration
SELECT REDIS_EXPIRE('user:session:abc123', 1800) FROM SYSIBM.SYSDUMMY1;

-- Combine with SET in a transaction
BEGIN
  DECLARE success INT;
  VALUES REDIS_SET('temp:report:status', 'processing');
  SET success = REDIS_EXPIRE('temp:report:status', 3600); -- 1 hour
END

Return Value:

1 if timeout was set
0 if key doesn’t exist

6. REDIS_TTL – Checking Expiration

Syntax:

SELECT REDIS_TTL(key) FROM SYSIBM.SYSDUMMY1;

Example:

-- Check how long until a session expires
SELECT REDIS_TTL('user:session:abc123') AS seconds_remaining 
FROM SYSIBM.SYSDUMMY1;

-- Monitor cache expiration
SELECT cache_key, REDIS_TTL(cache_key) AS ttl
FROM cached_items
WHERE REDIS_TTL(cache_key) BETWEEN 1 AND 300; -- Expiring in 1-5 minutes

Return Values:

Positive integer: Remaining time in seconds
-1: Key exists but has no associated expiration
-2: Key does not exist

7. REDIS_PING – Testing Connectivity

Syntax:

VALUES(REDIS_PING());

Example:

-- Simple connection test
VALUES(REDIS_PING());

-- Use in a health check procedure
BEGIN
  DECLARE status VARCHAR(10);
  SET status = REDIS_PING();
  IF status = 'PONG' THEN
    -- Redis is responsive
  ELSE
    -- Handle connection issue
  END IF;
END

Practical Use Cases

1. Session Management

-- Store session data with expiration
VALUES REDIS_SET('session:' || session_id, session_data);
VALUES REDIS_EXPIRE('session:' || session_id, 1800); -- 30 minutes

-- Check session validity
SELECT 
  REDIS_GET('session:' || session_id) AS session_data,
  REDIS_TTL('session:' || session_id) AS ttl
FROM SYSIBM.SYSDUMMY1;

2. Application Caching

-- Cache expensive query results
CREATE PROCEDURE get_product_details(IN product_id INT)
BEGIN
  DECLARE cache_key VARCHAR(128);
  DECLARE cached_result CLOB;
  
  SET cache_key = 'product:' || product_id;
  SET cached_result = REDIS_GET(cache_key);
  
  IF cached_result IS NULL THEN
    -- Not in cache, run the query
    SELECT JSON_OBJECT(
      'id' VALUE p.id,
      'name' VALUE p.name,
      'price' VALUE p.price,
      'stock' VALUE p.stock
    ) INTO cached_result
    FROM products p
    WHERE p.id = product_id;
    
    -- Store in cache with 1-hour expiration
    VALUES REDIS_SET(cache_key, cached_result);
    VALUES REDIS_EXPIRE(cache_key, 3600);
  END IF;
  
  -- Return the result
  RETURN cached_result;
END

3. Rate Limiting

-- Track API calls per user
CREATE PROCEDURE check_rate_limit(IN user_id INT)
BEGIN
  DECLARE key VARCHAR(128);
  DECLARE calls INT;
  
  SET key = 'api_limit:' || user_id;
  SET calls = REDIS_INCR(key);
  
  -- If this was the first call, set expiration
  IF calls = 1 THEN
    VALUES REDIS_EXPIRE(key, 3600); -- 1-hour window
  END IF;
  
  -- Check if over limit (100 calls/hour)
  IF calls > 100 THEN
    SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'Rate limit exceeded';
  END IF;
END

Performance Considerations

Network Latency: Each Redis operation involves a network call. Minimize round trips by:
- Using Redis effectively for caching rather than primary storage
- Batching operations where possible
Connection Management: The UDFs manage connections efficiently, but:
- Avoid extremely frequent calls in tight loops
- Consider connection pooling for high-volume applications
Data Size: Redis performs best with smaller values:
- Keep individual values under 100KB when possible
- For larger data, consider splitting or using traditional DB storage

Troubleshooting

Common Issues and Solutions:

Connection Errors:
- Verify Redis server is running: VALUES(REDIS_PING())
- Check network connectivity between IBM i and Redis server
- Confirm correct host/port in configuration
Timeout Errors:
- Increase timeout in Redis configuration if needed
- Check for network latency issues
Function Not Found:
- Verify the functions were created successfully
- Check your library list includes REDIS400

Monitoring Usage

Track Redis function usage:

SELECT 
  function_name, 
  COUNT(*) as call_count,
  AVG(elapsed_time) as avg_time
FROM QSYS2.FUNCTION_STATISTICS
WHERE function_schema = 'REDIS400'
GROUP BY function_name;

Conclusion

The Redis SQL Functions for IBM i provide a powerful way to integrate Redis’s high-performance caching with your IBM i applications. By mastering these functions, you can:

Implement efficient caching layers to reduce database load
Build robust session management systems
Create atomic counters for ID generation or rate limiting
Add expiration features to temporary data
Enhance application performance with Redis’s in-memory speed

For more advanced usage, consider exploring:

Redis data structures (hashes, lists, sets)
Pub/sub functionality for event-driven architectures
Lua scripting for complex atomic operations

Remember to monitor your Redis usage and adjust configurations as your application scales. Happy coding with Redis on IBM i!

ER April 7, 2025 No Comments IBM i, ILE C, JSON, Open Source, Redis, SQL

er400

Monthly Archives: April 2025

Using HTTP_POST_VERBOSE on IBM i for REST API Calls

1. Building the HTTP Request Headers

Key Headers to Include

2. Making the POST Request with HTTP_POST_VERBOSE

Example: Executing the POST Request

Key Points

3. Handling the Response

Example: Extracting Response Data

Best Practices

Conclusion

REDIS400: Redis SQL Functions for IBM i: A Comprehensive Tutorial

Introduction

Prerequisites

Installation and Setup

Step 1: Clone the Repository

Step 3: Verify Installation

2. REDIS_GET – Retrieving Data

3. REDIS_INCR – Atomic Counter

4. REDIS_DEL – Removing Data

5. REDIS_EXPIRE – Setting Expiration

6. REDIS_TTL – Checking Expiration

7. REDIS_PING – Testing Connectivity

Practical Use Cases

1. Session Management

Performance Considerations

Troubleshooting

Monitoring Usage

Conclusion