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:

  1. Access to an IBM i system with development tools (ILE C compiler, SQL)
  2. A running Redis server accessible from your IBM i (default: 127.0.0.1:6379)
  3. 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:

  1. Create the REDIS400 library
  2. Compile all C modules
  3. Create the service program
  4. 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

  1. 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
  2. Connection Management: The UDFs manage connections efficiently, but:
    • Avoid extremely frequent calls in tight loops
    • Consider connection pooling for high-volume applications
  3. 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:

  1. 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
  2. Timeout Errors:
    • Increase timeout in Redis configuration if needed
    • Check for network latency issues
  3. 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:

  1. Implement efficient caching layers to reduce database load
  2. Build robust session management systems
  3. Create atomic counters for ID generation or rate limiting
  4. Add expiration features to temporary data
  5. 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!

No Comments IBM i, ILE C, JSON, Open Source, Redis, SQL

Leave a Reply

Your email address will not be published. Required fields are marked *