Skip to content
A Db2 for i SDK for connecting to various services

Usage

This module allows you to connect to Wallaroo AI endpoints using OAuth2 authentication. Wallaroo provides OpenAI-compatible APIs, so the configuration includes both Wallaroo-specific OAuth2 settings and OpenAI-compatible endpoint settings.

Setup

Before using the Wallaroo functionality, you need to configure both the OAuth2 authentication and endpoint details:

Wallaroo OAuth2 Authentication

Configure the OAuth2 client credentials for authentication:

-- Set Wallaroo OAuth2 authentication for current job
CALL dbsdk_v1.wallaroo_set_token_url_for_job('https://YOUR_WALLAROO_SERVER/auth/realms/master/protocol/openid-connect/token');
CALL dbsdk_v1.wallaroo_set_confidential_client_for_job('api-client');
CALL dbsdk_v1.wallaroo_set_confidential_client_secret_for_job('your-client-secret');

To make these settings persistent across jobs for your user profile:

CALL dbsdk_v1.wallaroo_set_token_url_for_me('https://YOUR_WALLAROO_SERVER/auth/realms/master/protocol/openid-connect/token');
CALL dbsdk_v1.wallaroo_set_confidential_client_for_me('api-client');
CALL dbsdk_v1.wallaroo_set_confidential_client_secret_for_me('your-client-secret');

OpenAI-Compatible Endpoint Configuration

Since Wallaroo provides OpenAI-compatible APIs, you also need to configure the endpoint settings:

-- Set OpenAI-compatible endpoint settings for current job
CALL dbsdk_v1.openai_compatible_setserverforjob('YOUR_WALLAROO_SERVER');
CALL dbsdk_v1.openai_compatible_setbasepathforjob('/v1/api/pipelines/infer/llama-3dot1-8b-pipe-2/llama-3dot1-8b-pipe/openai/v1');
CALL dbsdk_v1.openai_compatible_setprotocolforjob('https');


-- Optional: Set the model identifier (often empty for Wallaroo)
CALL dbsdk_v1.openai_compatible_setmodelforjob('');

To make these settings persistent across jobs for your user profile:

CALL dbsdk_v1.openai_compatible_setserverforme('YOUR_WALLAROO_SERVER');
CALL dbsdk_v1.openai_compatible_setbasepathforme('/v1/api/pipelines/infer/llama-3dot1-8b-pipe-2/llama-3dot1-8b-pipe/openai/v1');
CALL dbsdk_v1.openai_compatible_setprotocolforme('https');
CALL dbsdk_v1.openai_compatible_setmodelforme('');  -- Optional

Verify Configuration

You can verify your configuration settings:

VALUES('Token URL: ' || COALESCE(dbsdk_v1.wallaroo_get_token_url(), 'NOT SET'));
VALUES('Client ID: ' || COALESCE(dbsdk_v1.wallaroo_get_confidential_client(), 'NOT SET'));
VALUES('Client Secret: ' || CASE WHEN dbsdk_v1.wallaroo_get_confidential_client_secret() IS NOT NULL THEN 'SET' ELSE 'NOT SET' END);
VALUES('Server: ' || COALESCE(dbsdk_v1.openai_compatible_getserver(), 'NOT SET'));
VALUES('Base Path: ' || COALESCE(dbsdk_v1.openai_compatible_getbasepath(), 'NOT SET'));
VALUES('Protocol: ' || COALESCE(dbsdk_v1.openai_compatible_getprotocol(), 'NOT SET'));

Basic Usage

Text Generation

To generate text using Wallaroo AI:

SELECT dbsdk_v1.wallaroo_generate('Why are armadillos so cute?') as response
FROM sysibm.sysdummy1;

Set max_tokens

It is recommended to specify a value for max_tokens depending on your task:

SELECT dbsdk_v1.wallaroo_generate(
  'Explain quantum computing in simple terms',
  '{"max_tokens": 200}'
) as response FROM sysibm.sysdummy1;

Optional Parameters

The wallaroo_generate function supports a wide range of optional parameters through a JSON options object:

SELECT dbsdk_v1.wallaroo_generate(
  'Tell me a story about a dragon',
  '{
    "max_tokens": 150,
    "temperature": 0.7,
    "top_p": 0.9,
    "frequency_penalty": 0.1,
    "presence_penalty": 0.1,
    "seed": 42
  }'
) FROM sysibm.sysdummy1;

Supported Parameters

See official OpenAI API Reference for full breakdown of optional parameters.

ParameterTypeDefaultDescription
model_idstring(configured model)The model to use for generation
max_tokensinteger256Maximum number of tokens to generate
temperaturedecimal1.0Controls randomness (0-2). Lower is more deterministic
top_pdecimal1.0Nucleus sampling probability cutoff
ninteger1Number of completions to generate
streambooleanfalseWhether to stream back partial progress
logprobsintegernullInclude log probabilities on most likely tokens
echobooleanfalseEcho back the prompt in the completion
stopstringnullSequences where generation should stop
presence_penaltydecimal0.0Penalty between -2.0 and 2.0 for tokens based on presence
frequency_penaltydecimal0.0Penalty between -2.0 and 2.0 for tokens based on frequency
logit_biasobjectnullJSON object mapping tokens to bias values
userstringnullUnique identifier for the end user
seedintegernullSeed for deterministic sampling
suffixstringnullSuffix after completion insertion (for compatible models only)

Here are some key parameters explained:

  • temperature: Controls randomness in token selection. Lower values (e.g., 0.2) make the output more focused and deterministic, while higher values (e.g., 0.8) make it more creative.

  • max_tokens: Sets the maximum length of the generated text. Increase for longer responses, decrease for shorter ones.

  • top_p: An alternative to temperature for controlling randomness. Sets a probability threshold for token selection.

  • frequency_penalty: Reduces repetition by penalizing tokens that have already appeared in the text. Higher values (0.8) strongly discourage repetition.

  • presence_penalty: Similar to frequency_penalty, but penalizes tokens based on their presence regardless of how many times they’ve appeared.

  • seed: When provided, attempts to make outputs deterministic. Good for reproducible results.

JSON Generation

For structured data or to get the complete API response, use the JSON-specific function:

SELECT dbsdk_v1.wallaroo_generate_json(
  'Write a haiku about databases',
  '{
    "max_tokens": 100,
    "temperature": 0.8
  }'
) as json_response
FROM sysibm.sysdummy1;

The wallaroo_generate_json function returns the complete API response as a JSON object, allowing you to access not only the generated content but also metadata such as token usage statistics.

Parsing JSON Response

You can parse specific fields from the JSON response:

VALUES JSON_VALUE(
  dbsdk_v1.wallaroo_generate_json('Why are armadillos so cute?', '{}'),
  '$.choices[0].text'
);

Custom Base URL

If your Wallaroo endpoint uses a different URL structure, you can specify a custom base URL directly in the function call:

SELECT dbsdk_v1.wallaroo_generate(
  'What is machine learning?',
  '{"max_tokens": 150, "temperature": 0.5}',
  'https://custom.wallaroo.io/v1/api/pipelines/infer/my-pipeline/openai/v1/completions'
) as response
FROM sysibm.sysdummy1;

API Response Format

The Wallaroo API response follows the OpenAI-compatible structure:

{
  "choices": [
    {
      "text": "** GENERATED TEXT HERE **",
      "index": 0,
      "logprobs": null,
      "finish_reason": "length"
    }
  ],
  "created": 1746039098,
  "model": "llama3",
  "system_fingerprint": "b5043-c262bedd",
  "object": "text_completion",
  "usage": {
    "completion_tokens": 100,
    "prompt_tokens": 13,
    "total_tokens": 113
  },
  "id": "chatcmpl-OW6vxJUGFMwUyg6HPGHYO1Uaza3VeXRj"
}

The regular wallaroo_generate function extracts just the text content from choices[0].text, while the wallaroo_generate_json function returns the entire JSON response.

Authentication

Wallaroo uses OAuth2 client credentials flow for authentication:

  1. The SDK automatically requests an access token from the configured token URL
  2. The access token is used to authenticate requests to the Wallaroo API
  3. Tokens are managed automatically - you just need to configure the client credentials

The OAuth2 flow is handled internally, so you don’t need to manage tokens manually.

Configuration Functions

Wallaroo OAuth2 Configuration

  • wallaroo_get_token_url() - Gets the current OAuth2 token URL

  • wallaroo_set_token_url_for_job() - Sets the token URL for the current job

  • wallaroo_set_token_url_for_me() - Sets the token URL persistently for your user profile

  • wallaroo_get_confidential_client() - Gets the current OAuth2 client ID

  • wallaroo_set_confidential_client_for_job() - Sets the client ID for the current job

  • wallaroo_set_confidential_client_for_me() - Sets the client ID persistently for your user profile

  • wallaroo_get_confidential_client_secret() - Gets the current OAuth2 client secret

  • wallaroo_set_confidential_client_secret_for_job() - Sets the client secret for the current job

  • wallaroo_set_confidential_client_secret_for_me() - Sets the client secret persistently for your user profile

OpenAI-Compatible Endpoint Configuration

Since Wallaroo provides OpenAI-compatible APIs, you also have access to all OpenAI-compatible configuration functions:

Server Configuration

  • openai_compatible_getserver() - Gets the current server hostname/IP
  • openai_compatible_setserverforjob() - Sets the server for the current job
  • openai_compatible_setserverforme() - Sets the server persistently for your user profile

Protocol Configuration

  • openai_compatible_getprotocol() - Gets the current protocol (http/https)
  • openai_compatible_setprotocolforjob() - Sets the protocol for the current job
  • openai_compatible_setprotocolforme() - Sets the protocol persistently for your user profile

Base Path Configuration

  • openai_compatible_getbasepath() - Gets the current base path
  • openai_compatible_setbasepathforjob() - Sets the base path for the current job
  • openai_compatible_setbasepathforme() - Sets the base path persistently for your user profile

Model Configuration

  • openai_compatible_getmodel() - Gets the current model
  • openai_compatible_setmodelforjob() - Sets the model for the current job
  • openai_compatible_setmodelforme() - Sets the model persistently for your user profile

Wallaroo Main Functionality

Function: wallaroo_generate

Description: Uses Wallaroo AI to generate a reply to the given prompt

Input parameters:

  • PROMPT (required): The input prompt for the LLM.
  • options (optional): JSON object containing optional parameters:
    • model_id: The model identifier to use for generation.
    • max_tokens: Maximum number of tokens to generate. Default 16.
    • temperature: Sampling temperature between 0 and 2. Default 1.
    • top_p: Nucleus sampling probability mass. Default 1.
    • n: Number of completions to generate. Default 1.
    • stream: Whether to stream back partial progress. Default false.
    • logprobs: Include log probabilities on most likely tokens, max 5.
    • echo: Echo back the prompt in the completion. Default false.
    • stop: Up to 4 sequences where generation should stop.
    • presence_penalty: Penalty between -2.0 and 2.0 for tokens based on presence. Default 0.
    • frequency_penalty: Penalty between -2.0 and 2.0 for tokens based on frequency. Default 0.
    • best_of: Generate best_of completions server-side. Default 1.
    • logit_bias: JSON object mapping tokens to bias values.
    • user: Unique identifier for the end user.
    • seed: Seed for deterministic sampling.
    • suffix: Suffix after completion insertion (for compatible models only).

Return type:

  • clob(2G) ccsid 1208

Return value:

  • The generated reply from Wallaroo AI. create or replace function dbsdk_v1.wallaroo_generate( prompt varchar(32000) ccsid 1208, options varchar(32000) ccsid 1208 default '' ) returns clob(2G) ccsid 1208 modifies sql data not deterministic no external action set option usrprf = *user, dynusrprf = *user, commit = *none begin declare access_token varchar(8000) ccsid 1208; declare response_text clob(2G) ccsid 1208; — Step 1: Get access token using Wallaroo OAuth2 client credentials flow set access_token = dbsdk_v1.wallaroo_get_access_token(); — Check if we got a valid access token if (access_token is null or trim(access_token) = ”) then call systools.lprintf(‘Wallaroo authentication failed - no access token received’); return null; end if; — Step 2: Call OpenAI compatible generate function with the access token — Make sure we pass the parameters in the correct order and types call systools.lprintf(‘this is a cool test message’); set response_text = dbsdk_v1.openai_compatible_generate( prompt, options, access_token ); return response_text; end;

Function: wallaroo_generate

Description: Uses Wallaroo AI to generate a reply to the given prompt with improved authentication and URL handling

Input parameters:

  • PROMPT (required): The input prompt for the LLM.
  • options (optional): JSON object containing optional parameters:
    • model_id: The model identifier to use for generation.
    • max_tokens: Maximum number of tokens to generate. Default 256.
    • temperature: Sampling temperature between 0 and 2. Default 1.
    • top_p: Nucleus sampling probability mass. Default 1.
    • n: Number of completions to generate. Default 1.
    • stream: Whether to stream back partial progress. Default false.
    • logprobs: Include log probabilities on most likely tokens, max 5.
    • echo: Echo back the prompt in the completion. Default false.
    • stop: Up to 4 sequences where generation should stop.
    • presence_penalty: Penalty between -2.0 and 2.0 for tokens based on presence. Default 0.
    • frequency_penalty: Penalty between -2.0 and 2.0 for tokens based on frequency. Default 0.
    • logit_bias: JSON object mapping tokens to bias values.
    • user: Unique identifier for the end user.
    • seed: Seed for deterministic sampling.
    • suffix: Suffix after completion insertion (for compatible models only).
  • base_url (optional): Custom base URL for the API endpoint. If not provided, uses configured endpoint settings.

Return type:

  • clob(2G) ccsid 1208

Return value:

  • The generated reply from Wallaroo AI.

Features:

  • Automatic OAuth2 client credentials authentication
  • Configurable endpoint URL support
  • Comprehensive parameter validation
  • Detailed error logging and handling
  • OpenAI-compatible API interface

Function: wallaroo_generate_json

Description: Uses Wallaroo AI to process a prompt and return structured JSON

Input parameters:

  • PROMPT (required): The input prompt for the LLM.
  • options (optional): JSON object containing optional parameters (same as wallaroo_generate)

Return type:

  • clob(2G) ccsid 1208

Return value:

  • The complete JSON response from Wallaroo AI.

Wallaroo Utilities

function: wallaroo_get_token_url

Description: gets the Wallaroo token URL to be used for authentication Input parameters:

  • TOKEN_URL (optional): The token URL for Wallaroo authentication. If not provided, will use job-level or user-level configuration. Return type:
  • varchar(1000) ccsid 1208 Return value:
  • The token URL to use for Wallaroo authentication

function: wallaroo_get_confidential_client

Description: gets the Wallaroo confidential client identifier to be used for authentication Input parameters:

  • CLIENT (optional): The confidential client identifier for Wallaroo authentication. If not provided, will use job-level or user-level configuration. Return type:
  • varchar(1000) ccsid 1208 Return value:
  • The confidential client identifier to use for Wallaroo authentication

function: wallaroo_get_confidential_client_secret

Description: gets the Wallaroo confidential client secret to be used for authentication Input parameters:

  • CONF_SECRET (optional): The confidential client secret for Wallaroo authentication. If not provided, will use job-level or user-level configuration. Return type:
  • varchar(8000) ccsid 1208 Return value:
  • The confidential client secret to use for Wallaroo authentication

procedure: wallaroo_set_token_url_for_job

Description: sets the Wallaroo token URL to be used for this job Input parameters:

  • TOKEN_URL (required): The token URL for Wallaroo authentication.

procedure: wallaroo_set_token_url_forme

Description: sets the Wallaroo token URL to be used for this user profile (persists across jobs) Input parameters:

  • TOKEN_URL (required): The token URL for Wallaroo authentication.

procedure: wallaroo_set_confidential_client_for_job

Description: sets the Wallaroo confidential client to be used for this job Input parameters:

  • CLIENT (required): The confidential client identifier for Wallaroo authentication.

procedure: wallaroo_set_confidential_client_forme

Description: sets the Wallaroo confidential client to be used for this user profile (persists across jobs) Input parameters:

  • CLIENT (required): The confidential client identifier for Wallaroo authentication.

procedure: wallaroo_set_confidential_client_secret_for_job

Description: sets the Wallaroo confidential client secret to be used for this job Input parameters:

  • CONF_SECRET (required): The confidential client secret for Wallaroo authentication.

procedure: wallaroo_set_confidential_client_secret_forme

Description: sets the Wallaroo confidential client secret to be used for this user profile (persists across jobs) Input parameters:

  • CONF_SECRET (required): The confidential client secret for Wallaroo authentication.

function: wallaroo_get_access_token

Description: gets an access token from Wallaroo using OAuth2 client credentials flow Input parameters:

  • TOKEN_URL (optional): The token URL for Wallaroo authentication. If not provided, will use configured value.
  • CLIENT (optional): The confidential client identifier. If not provided, will use configured value.
  • CLIENT_SECRET (optional): The confidential client secret. If not provided, will use configured value. Return type:
  • varchar(8000) ccsid 1208 Return value:
  • The access token for Wallaroo authentication