IBM i: The SQL Way · #7

URL-Encode API Parameters with Db2 for i

Use the Db2 for i URL_ENCODE scalar function to safely encode query-string values such as spaces, ampersands, plus signs, and non-ASCII text before calling an HTTP API.

A query-string value containing spaces, ampersands, plus signs, slashes, or non-ASCII characters cannot always be placed directly into a URL. Db2 for i can encode the value before the HTTP request is built.

The scalar function is:

URL_ENCODE

It applies URL encoding using UTF-8.

Basic example

VALUES URL_ENCODE(
    'IBM i Q&A'
);

Result:

IBM+i+Q%26A

The function converts:

space       to +
ampersand   to %26

Build a complete search URL

VALUES
    'https://www.example.com/search?q='
    CONCAT URL_ENCODE('IBM i Q&A');

Result:

https://www.example.com/search?q=IBM+i+Q%26A

The important point is that only the parameter value is encoded.

Do not encode the entire URL:

-- Avoid this
VALUES URL_ENCODE(
    'https://www.example.com/search?q=IBM i Q&A'
);

Encoding the complete URL also encodes structural characters such as:

:
/
?
=

That usually produces a value that is not usable as the intended request URL.

Encode several parameters

WITH INPUT
(
    CUSTOMER_NAME,
    CITY,
    REFERENCE_TEXT
) AS
(
    VALUES
    (
        'A & B Industries',
        'Calgary',
        'Order 1001/2026'
    )
)
SELECT
    'https://api.example.com/search?customer='
    CONCAT URL_ENCODE(CUSTOMER_NAME)
    CONCAT '&city='
    CONCAT URL_ENCODE(CITY)
    CONCAT '&reference='
    CONCAT URL_ENCODE(REFERENCE_TEXT)
        AS REQUEST_URL
FROM INPUT;

Possible result:

https://api.example.com/search?customer=A+%26+B+Industries&city=Calgary&reference=Order+1001%2F2026

Each value is encoded independently.

Use variables in SQLRPGLE

exec sql
   set :requestUrl =
       'https://api.example.com/customer?name='
       concat URL_ENCODE(:customerName)
       concat '&city='
       concat URL_ENCODE(:city);

This avoids manually replacing individual characters in RPG.

The host variable receiving the result must be large enough for:

• the base URL
• parameter names
• encoded values
• any expansion caused by percent encoding

Use it with an HTTP function

WITH REQUEST (REQUEST_URL) AS
(
    VALUES
      'https://api.example.com/customer?name='
      CONCAT URL_ENCODE('A & B Industries')
)
SELECT QSYS2.HTTP_GET(REQUEST_URL)
FROM REQUEST;

The exact HTTP function and options depend on the API, response type, headers, TLS requirements, and IBM i release.

Encode data from a table

SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    'https://api.example.com/customer?name='
      CONCAT URL_ENCODE(CUSTOMER_NAME)
        AS REQUEST_URL
FROM MYLIB.CUSTOMER
WHERE ACTIVE = 'Y';

This can be useful when generating:

• integration work queues
• batch API requests
• callback URLs
• web links in reports
• outbound notification links

Special characters

Test the behavior with common values:

VALUES URL_ENCODE('A B');
VALUES URL_ENCODE('A&B');
VALUES URL_ENCODE('A+B');
VALUES URL_ENCODE('A/B');
VALUES URL_ENCODE('A?B');
VALUES URL_ENCODE('A=B');
VALUES URL_ENCODE('100% Complete');

Typical results include:

A+B
A%26B
A%2BB
A%2FB
A%3FB
A%3DB
100%25+Complete

A literal plus sign is encoded differently from a space.

That distinction matters when the receiving service decodes form-style query parameters.

Encode non-ASCII data

URL_ENCODE uses UTF-8.

VALUES URL_ENCODE('Montréal');

Characters outside the unreserved URL set are represented using percent-encoded UTF-8 bytes.

This is safer than trying to build encoding rules around one EBCDIC CCSID.

Null handling

When the input expression is null, the result is null.

When constructing a URL, decide whether a missing value should:

• omit the parameter
• send an empty parameter
• use a default value
• reject the request

Example with an empty default:

VALUES
    'https://api.example.com/search?q='
    CONCAT URL_ENCODE(
        COALESCE(CAST(NULL AS VARCHAR(100)), '')
    );

Do not apply COALESCE automatically without considering the API contract. Null and empty string may have different business meanings.

Conditionally include a parameter

WITH INPUT
(
    SEARCH_TEXT,
    COUNTRY_CODE
) AS
(
    VALUES
    (
        'IBM i Q&A',
        CAST(NULL AS VARCHAR(10))
    )
)
SELECT
    'https://api.example.com/search?q='
    CONCAT URL_ENCODE(SEARCH_TEXT)
    CONCAT CASE
        WHEN COUNTRY_CODE IS NOT NULL
          THEN '&country=' CONCAT URL_ENCODE(COUNTRY_CODE)
        ELSE ''
    END AS REQUEST_URL
FROM INPUT;

Decode a value

The companion function is:

URL_DECODE

Example:

VALUES URL_DECODE(
    'IBM+i+Q%26A'
);

Result:

IBM i Q&A

Encoding and then decoding can be useful during testing:

VALUES URL_DECODE(
    URL_ENCODE('IBM i Q&A')
);

Built-in function versus SYSTOOLS helper

Some systems may also contain:

SYSTOOLS.URLENCODE

The current Db2 built-in function is named:

URL_ENCODE

Notice the underscore.

When documenting or reviewing code, verify which function is being used because:

• the schema is different
• supported input sizes can differ by PTF level
• return types can differ
• older helper functions may have different behavior

For new code on a supported current level, use the documented Db2 function unless there is a specific reason to use the SYSTOOLS helper.

URL encoding is not JSON escaping

Do not use URL_ENCODE to prepare a JSON document.

For JSON, use Db2 JSON functions such as:

JSON_OBJECT
JSON_ARRAY
JSON_ARRAYAGG

A value may require different treatment depending on where it is placed:

URL query parameter   URL_ENCODE
JSON string           JSON function
HTML output           HTML encoding
SQL value             Parameter marker or host variable

Encoding for the wrong context can corrupt the data or create a security problem.

URL encoding is not SQL injection protection

URL_ENCODE does not make a value safe for dynamic SQL.

Use:

• parameter markers
• host variables
• prepared statements
• validated identifiers

for SQL safety.

Similarly, encoding a URL parameter does not prove that the value is authorized or appropriate for the receiving API.

Length considerations

Percent encoding can expand one character into several output characters.

A receiving variable or column should not assume the encoded result will be the same length as the input.

The current function supports large character input and returns a CLOB on supported PTF levels.

When a smaller application variable is required, explicitly cast only after confirming the maximum expected size:

VALUES CAST(
    URL_ENCODE('IBM i Q&A')
    AS VARCHAR(500) CCSID 1208
);

Release requirement

IBM lists URL_ENCODE among current Db2 for i functional enhancements for IBM i 7.6 TR2 and IBM i 7.5 TR8.

The function existed on earlier levels, but behavior and supported sizes have been enhanced over time.

Check the installed Db2 Group PTF and the documentation for the system release before depending on a particular maximum size or return type.

A practical API workflow

1. Identify each URL parameter value.
2. Validate the business data.
3. Encode each value separately with URL_ENCODE.
4. Join the encoded values with the URL structure.
5. Keep credentials out of query strings where possible.
6. Send the request with the appropriate HTTP function.
7. Record the response status and diagnostic detail.
8. Avoid logging sensitive query parameters.

Final takeaway

URL_ENCODE removes the need to manually replace special characters while building API URLs in Db2 for i.

Use it on individual parameter values—not the complete URL—and remember that URL encoding solves representation, not validation, authorization, or SQL security.