Let’s talk about something that keeps many IBM i programmers awake at night: data encryption. In today’s world of GDPR, data breaches, and compliance audits, knowing how to properly encrypt sensitive data isn’t just nice to have, it’s absolutely essential.

Now, I’ll be honest with you. When I first started digging into encryption on our beloved IBM-i System, I thought it was going to be a nightmare of complex APIs and headache-inducing cryptography theory. But here’s the good news: IBM has actually made it surprisingly straightforward with built-in SQL functions.

So, grab a tea, or coffee (or something stronger), and let’s dive into the wonderful world of encrypting data with SQL on IBM i.

Why Should You Care About Encryption?

Before we get our hands dirty with code, let’s talk about the “why.” In my experience, many RPG programmers on IBM i systems have been happily coding away for decades without giving much thought to encryption. And honestly? That worked fine in the 90s.

But times have changed, chaps. Here are some very real reasons you need to care:

• Compliance Nightmares: GDPR, HIPAA, PCI-DSS, BLAH-BLAH the alphabet soup of regulations that can cost your company serious money if you get it wrong
• Data Breaches: When (not if) someone gets unauthorized access to your data, encryption is your last line of defense
• Customer Trust: Your customers expect their sensitive information to be protected
• Audit Requirements: More and more audits are asking “How are you protecting sensitive data?”

With IBM i, you typically need to encrypt things like:

• Social security numbers (obviously)
• Credit card numbers and banking details
• Healthcare records and patient data
• Proprietary business information
• Names, addresses, phone numbers
• Passwords (please tell me you’re not storing these in plain text!)

The Three Amigos: Encryption Algorithms on IBM i

IBM i gives us three encryption algorithms to work with through SQL functions. Think of them as the Three Amigos, but instead of comedy, they deliver security:

1. AES (Advanced Encryption Standard) – The Hero

• Function: ENCRYPT_AES()
• Security Level: Excellent
• Performance: Good
• My Recommendation: Use this for everything new

AES is the gold standard. It’s what the US government uses, it’s what banks use, and it’s what you should use. Unless you have a very specific reason not to, AES is your go-to choice.

2. RC2 (Rivest Cipher 2) – The Old Timer

• Function: ENCRYPT_RC2()
• Security Level: Okay, but outdated
• Performance: Decent
• My Take: Only use if you’re maintaining legacy code

RC2 is like that old programmer in the corner who still codes in RPG III. He knows his stuff, but maybe it’s time to move on.

3. TDES (Triple DES) – The Middle Child

• Function: ENCRYPT_TDES()
• Security Level: Good, but slower than AES
• Performance: Slower than AES
• My Take: Better than single DES, but why bother when AES is available?

TDES is… fine. It’s definitely better than the original DES (which you should never use), but AES beats it in both security and performance.

Let’s Get Coding: Setting Up Your First Encrypted Table

Alright, enough theory. Let’s write some code!

Step 1: Create Your Table

First things first, we need a place to store our encrypted data.

Let’s create a simple file (aka SQL Table) in our library (aka SCHEMA):

create or replace table NICKLITTEN.CUSTENC (
      CUSTOMER_ID varchar(10),
      CUSTOMER_NAME varchar(50),
      SSN_PLAIN varchar(11),
      SSN_ENCRYPTED varchar(128) for bit data,
      CREDIT_CARD_ENCRYPTED varchar(128) for bit data,
      primary key (CUSTOMER_ID)
    )
  on replace delete rows;

Important Note: Notice that FOR BIT DATA clause? That is absolutely critical. If you forget it (and trust me, I’ve forgotten it), your decryption will fail with SQL code -171. The encrypted data is binary gobbledygook, and it needs to be stored as such.

Step 2: Set Your Encryption Password

Now, you could put the password directly in each encryption function call, but that’s about as secure as writing your PIN on your debit card.

Instead, do this:

set encryption password = 'MySuperSecretPassword123!';

This sets the password at the session level, so all subsequent encryption functions will use it automatically. Much cleaner, much more secure. This is the part that confuses people.

SET ENCRYPTION PASSWORD does NOT have a hint parameter. The hint is supplied only when calling the ENCRYPT_… functions, not when setting the default password. But more on this later…

Where and how your save your passwords is up to you! Personally, I use a multi-platform password application called 1-Password. It runs on windows, IOS, Android and keeps all my passwords secure and accessible. I’m not sponsored by them; I pay for this software and use every single day.

Step 3: Insert Some Encrypted Data

Here’s where the magic happens:

insert into NICKLITTEN.CUSTENC
  values
    (
      'CUST001',
      'John Smith',
      '123-45-6789', -- Plain text for comparison (remove in production!)
      encrypt_aes('123-45-6789'),
      encrypt_aes('4111-1111-1111-1111')
    );

Notice how clean that is? No password in the function calls because we set it earlier. Nice and tidy.

Remember your encryption password is not stored anywhere. If you lose it, the encrypted data is effectively gone. That’s where HINT comes in. With hinting you could even store different passwords for different columns (aka fields) if you wanted to get really fancy:

insert into NICKLITTEN.CUSTENC
  values
    (
      'CUST001',
      'John Smith',
      '123-45-6789', -- Plain text for comparison (remove in production!)
      encrypt_aes('123-45-6789', 'MySuperSecretPassword123!', HINT => 'My Super Secret One'),
      encrypt_aes('4111-1111-1111-1111', 'ThisFieldIsDifferent$1', HINT => 'This is Different')
    );

We can put all these statements into a single SQL session like this:

Decrypting Data: Getting Your Data Back

Encrypting data is only half the battle. At some point, you’re going to need to read it back. Here’s how:

Basic Decryption

select CUSTOMER_ID,
       CUSTOMER_NAME,
       decrypt_bit(SSN_ENCRYPTED) as SSN_DECRYPTED,
       decrypt_bit(CREDIT_CARD_ENCRYPTED) as CARD_DECRYPTED
  from NICKLITTEN.CUSTENC
  where CUSTOMER_ID = 'CUST001';

The DECRYPT_BIT function automatically uses the password you set with SET ENCRYPTION PASSWORD. If you forgot to set the password, you’ll get SQL code -443.

What If You Forgot the Password?

We’ve all been there. You set a password six months ago, and now you can’t remember what it was. This is where that hint comes in handy:

select gethint(SSN_ENCRYPTED)
  from NICKLITTEN.CUSTENC
  fetch first 1 row only;

This will return your hint: “My Super Secret One” (which, hopefully, will actually jog your memory).

RPGLE Implementation: Making It Work in Your Programs

Now, let’s talk about how to actually use this stuff in your RPGLE programs. Because let’s face it, most of us aren’t writing pure SQL we’re writing RPG with embedded SQL.

A Complete RPGLE Example

Here’s a practical example that shows how to integrate encryption into an RPGLE program:

**FREE

/TITLE Customer Data Encryption Example

// GETENC-Get_Encryption_Data.pgm.sqlrpgle (fully /free)
// This SQL RPGLE program demonstrates how to use SQL encryption functions.
//
// MODIFICATION HISTORY:
// 2026.01.23 Nick Litten V1.0 Created

ctl-opt dftactgrp(*no) actgrp('NICKLITTEN')
 option(*nodebugio:*srcstmt:*nounref)
 datfmt(*ISO) decedit('0.')
 copyright('| GETENC V1.0 Customer Data Encryption Example');

// Prototype for error handling
// this would be your own error handling program called 'ERRORMSG'
dcl-pr HandleError extpgm('ERRORMSG');
  ErrorMsg varchar(256) const;
end-pr;

// Variable declarations
dcl-s Password varchar(50) inz('MySuperSecretPassword123!');
dcl-s Hint varchar(100) inz('The usual password we use for testing');
dcl-s CustomerID varchar(10);
dcl-s CustomerName varchar(50);
dcl-s SSN varchar(11);
dcl-s CreditCard varchar(20);
dcl-s DecryptedSSN varchar(11);
dcl-s DecryptedCard varchar(20);
dcl-s DebugMSG varchar(52);

// Set up encryption password for this session
exec sql
  SET ENCRYPTION PASSWORD = :Password;

// Check for errors after setting password
if sqlstate <> '00000';
  HandleError('Failed to set encryption password: ' + sqlstate);
  return;
endif;

// Example: Insert a new customer with encrypted data
CustomerID = 'CUST002';
CustomerName = 'Jane Doe';
SSN = '987-65-4321';
CreditCard = '5555-5555-5555-5555';

exec sql
  insert into NICKLITTEN.CUSTENC
  values
    (
      :CUSTOMERID,
      :CUSTOMERNAME,
      :SSN,
      encrypt_aes(:SSN, :PASSWORD, HINT => :HINT),
      encrypt_aes(:CREDITCARD, :PASSWORD, HINT => :HINT)
    );

// Check for insert errors
if sqlstate <> '00000';
  HandleError('Failed to insert customer: ' + sqlstate);
  return;
endif;

// Example: Retrieve and decrypt customer data
exec sql
  select CUSTOMER_NAME,
       SSN_PLAIN,
       decrypt_bit(SSN_ENCRYPTED),
       decrypt_bit(CREDIT_CARD_ENCRYPTED)
  into :CUSTOMERNAME,
       :SSN,
       :DECRYPTEDSSN,
       :DECRYPTEDCARD
  from NICKLITTEN.CUSTENC
  where CUSTOMER_ID = :CUSTOMERID;

if sqlstate = '00000';
  // Display results (in a real app, you'd do something useful)
  DebugMSG = ('Customer: ' + %trim(CustomerName));
  dsply DebugMSG;
  DebugMSG = ('SSN: ' + %trim(DecryptedSSN));
  dsply DebugMSG;
  DebugMSG = ('Card: ' + %trim(DecryptedCard));
  dsply DebugMSG;
else;
  HandleError('Failed to retrieve customer: ' + sqlstate);
endif;

*INLR = *ON;
return;

Key Points for RPGLE Integration

1. Use Host Variables: Always use host variables (the ones with colons) for passwords and data. Don’t hardcode values.
2. Check SQLSTATE: Always check SQLSTATE after SQL operations. Trust me, you’ll thank me later when something goes wrong.
3. Handle Errors Gracefully: Have a plan for when things go wrong. Encryption operations can fail for various reasons.
4. Don’t Store Plain Text: Notice in my example I have an SSN_PLAIN field? That’s just for demonstration. In production, get rid of it!

Best Practices: The Wisdom of Experience

After working with encryption on IBM i for a while, I’ve learned a few things (often the hard way). Here are my best practices:

Security First

• Never, ever hardcode passwords in your source code. Use configuration files, environment variables, or a proper key management system.
• Use strong passwords. “password123” isn’t going to cut it. Mix uppercase, lowercase, numbers, and special characters.
• AES is your friend. For new applications, just use AES. Don’t overthink it.
• Test decryption regularly. There’s nothing worse than discovering you can’t decrypt your data when you really need it.

Performance Considerations

• Encryption isn’t free. It adds CPU overhead. If you’re doing high-volume transactions, test the performance impact.
• Don’t encrypt everything. Only encrypt sensitive data. Encrypting your entire database will slow things down unnecessarily.
• Indexes and encrypted data don’t mix well. Encrypted values are essentially random, so indexing them doesn’t help with searches.
• Document everything. Future you (or the poor sap who inherits your code) will thank you.

Password Management

• Store passwords securely. Don’t leave them lying around in text files.
• Use meaningful hints. But not too meaningful—”My birthday” isn’t a great hint if everyone knows your birthday.
• Plan for password rotation. At some point, you’ll need to change passwords. Have a plan for this.
• Consider a password management system. There are enterprise solutions that can handle this for you.

Common Errors and How to Fix Them

Let’s face it we all make mistakes. Here are the most common ones I’ve seen (and made):

SQL Code -171: Column not defined with FOR BIT DATA

The Problem: You forgot the FOR BIT DATA clause on your encrypted column.
The Fix:

-- Wrong
SSN_ENCRYPTED VARCHAR(128),

-- Right  
SSN_ENCRYPTED VARCHAR(128) FOR BIT DATA,

SQL Code -443: Password not set

The Problem: You tried to encrypt/decrypt without setting the password first.
The Fix: Run SET ENCRYPTION PASSWORD before your encryption operations.

SQL Code -205: Table not found

The Problem: Wrong library or table name.
The Fix: Double-check your library list and table names. Remember, IBM i is case-sensitive for SQL identifiers if you use quotes.

Data Type Mismatches

The Problem: Trying to insert the wrong data type into a column.
The Fix: Encrypted data is binary. Make sure your host variables in RPG match the expected data types.

Real-World Scenario: Converting an Existing Application

Let me share a quick story from the trenches. I once worked with a company that had a customer database with 20 years of unencrypted social security numbers. The auditors were not happy.

Here’s the approach we took:

1. Added encrypted columns to the existing table (with FOR BIT DATA, of course)
2. Wrote a one-time conversion program to encrypt all existing SSNs
3. Modified the application programs to use the encrypted columns
4. Tested everything thoroughly in a development environment first
5. Rolled it out during a maintenance window (because you don’t want to do this during business hours)

The key was doing it incrementally. We didn’t try to encrypt everything at once. We started with the most sensitive data and worked our way down.

The Bottom Line

Data encryption on IBM i doesn’t have to be scary. The SQL encryption functions make it surprisingly straightforward, and with a bit of planning, you can secure your sensitive data without breaking your existing applications. Remember:

• Use AES for new applications
• Always use FOR BIT DATA for encrypted columns
• Set your encryption password before working with encrypted data
• Test your decryption regularly
• Don’t hardcode passwords (seriously, don’t)

Is encryption a silver bullet for security? Of course not. But it’s a critical piece of the puzzle, and with the tools IBM provides, there’s really no excuse for leaving sensitive data unencrypted.

Now go forth and encrypt! Your auditors (and your customers) will thank you.

Encrypt your data before a hacker (or maybe just a disgruntled employee) encrypts it for you!!!!

What About You?

Have you implemented encryption on your IBM i system? What challenges did you run into? Any tips or tricks you’d like to share? Drop a comment below – I’d love to hear about your experiences!

And if you’re struggling with a specific encryption problem, feel free to reach out. We’ve all been there, and sometimes a fresh pair of eyes can make all the difference.

Happy encrypting, folks!