Java, MySQL, and Shell Scripting for SailPoint IdentityIQ

SailPoint IdentityIQ is a Java web application running on an application server against a relational database. Most IdentityIQ development happens in BeanShell rules and XML workflows — covered in the companion guide to IdentityIQ BeanShell rules, workflows, and tasks. This article covers the layer underneath: when to write compiled Java instead of BeanShell, how the MySQL schema is actually laid out, and the shell scripting that turns manual console work into repeatable automation.

The Stack, Concretely

An IdentityIQ deployment is four layers, and knowing which one a problem lives in cuts debugging time dramatically:

IdentityIQ_HOME is wherever identityiq.war was expanded — typically $TOMCAT_HOME/webapps/identityiq. Nearly every path in this article is relative to it.

Java: When to Leave BeanShell

BeanShell is convenient for short scripts, but it is interpreted, untyped, and untestable. Move to compiled Java when any of these apply:

• The logic exceeds roughly 50 lines
• You need unit tests
• It runs in a hot path — per-account during aggregation, for instance
• You need a library BeanShell struggles to use cleanly
• You are writing a custom connector or task executor

Compiling Against the IdentityIQ API

Your code compiles against identityiq.jar, found in IdentityIQ_HOME/WEB-INF/lib/. A minimal Maven setup installs it into your local repository, since SailPoint does not publish to Maven Central:

mvn install:install-file \
  -Dfile=/opt/tomcat/webapps/identityiq/WEB-INF/lib/identityiq.jar \
  -DgroupId=sailpoint \
  -DartifactId=identityiq \
  -Dversion=8.4 \
  -Dpackaging=jar

Then declare it as provided — it must not be bundled into your artifact, because the container already has it:

<dependency>
  <groupId>sailpoint</groupId>
  <artifactId>identityiq</artifactId>
  <version>8.4</version>
  <scope>provided</scope>
</dependency>

Marking it compile instead ships a second copy of every SailPoint class inside your JAR, producing ClassCastException errors where the same class loaded by two classloaders is not considered equal. This is one of the harder IdentityIQ bugs to diagnose, because the exception message names the same class on both sides.

Java Version Compatibility

IdentityIQ 8.x supports Java 8 and 11, with 8.4 adding Java 17 on supported application servers. Compile targeting the version your application server actually runs:

java -version                       # on the app server host
mvn -DskipTests package             # with maven.compiler.release matching

A mismatch produces UnsupportedClassVersionError at class load time — not at deployment — so the failure shows up the first time your code is invoked, often long after the deploy appeared to succeed.

Deploying a Custom JAR

sudo systemctl stop tomcat
sudo cp target/iiq-custom-1.0.0.jar /opt/tomcat/webapps/identityiq/WEB-INF/lib/
sudo chown tomcat:tomcat /opt/tomcat/webapps/identityiq/WEB-INF/lib/iiq-custom-1.0.0.jar
sudo systemctl start tomcat

A restart is mandatory — the JVM does not reload classes from WEB-INF/lib at runtime. This is the key operational difference from rules, which are database objects you can update live.

Remove the old version explicitly when deploying an update with a changed filename. Two JARs both containing com.example.iiq.MyRule produce nondeterministic behaviour depending on classloader ordering.

MySQL: The IdentityIQ Schema

Generating and Loading the Schema

IdentityIQ generates its own DDL. From IdentityIQ_HOME/WEB-INF/bin:

./iiq schema

This writes versioned scripts into IdentityIQ_HOME/WEB-INF/database, named like create_identityiq_tables-8.4.mysql. Load the one matching your database platform:

mysql -u root -p identityiq < create_identityiq_tables-8.4.mysql

Expect this to take anywhere from 45 minutes to 2 hours. Re-run ./iiq schema after adding extended attributes — they become real columns, and the generated DDL changes.

Connection Settings

Connection configuration lives in IdentityIQ_HOME/WEB-INF/classes/iiq.properties:

dataSource.url=jdbc:mysql://db.example.com:3306/identityiq?useUnicode=true&characterEncoding=utf8
dataSource.username=identityiq
dataSource.password=<encrypted-value>
dataSource.maxActive=50

The password must be encrypted. Generate the ciphertext with:

./iiq encrypt changeit

Paste the output into iiq.properties. IdentityIQ will not accept a plaintext password here.

The Tables You Will Actually Query

Every table carries the spt_ prefix. These are the ones worth knowing:

Read-Only Diagnostics That Save Real Time

These queries answer questions the UI makes tedious. All are SELECT only.

Which aggregations are failing, and how recently:

SELECT name,
       FROM_UNIXTIME(completed/1000) AS completed_at,
       completion_status,
       SUBSTRING(messages, 1, 200)   AS first_message
FROM   spt_task_result
WHERE  completion_status IN ('Error', 'Warning')
ORDER  BY completed DESC
LIMIT  20;

Accounts that failed to correlate — the usual cause of “the user exists but has no access”:

SELECT a.name        AS application,
       COUNT(*)      AS uncorrelated_accounts
FROM   spt_link l
JOIN   spt_application a ON l.application = a.id
WHERE  l.identity_id IS NULL
GROUP  BY a.name
ORDER  BY uncorrelated_accounts DESC;

Workflows stuck in flight, which accumulate invisibly and eventually degrade performance:

SELECT name,
       FROM_UNIXTIME(created/1000) AS created_at,
       DATEDIFF(NOW(), FROM_UNIXTIME(created/1000)) AS age_days
FROM   spt_workflow_case
WHERE  DATEDIFF(NOW(), FROM_UNIXTIME(created/1000)) > 30
ORDER  BY created ASC;

Table sizes, to find what is actually consuming disk:

SELECT table_name,
       ROUND(((data_length + index_length) / 1024 / 1024), 1) AS size_mb,
       table_rows
FROM   information_schema.TABLES
WHERE  table_schema = 'identityiq'
ORDER  BY (data_length + index_length) DESC
LIMIT  15;

Note that IdentityIQ stores timestamps as Unix epoch milliseconds in BIGINT columns, which is why every date needs FROM_UNIXTIME(col/1000).

Never Write Directly to the Database

This deserves emphasis because it is the most damaging mistake available to someone comfortable with SQL.

Most IdentityIQ objects serialize their real content into an XML blob column. The relational columns beside it are a partial, denormalized projection maintained by the application for querying. An UPDATE that changes a column leaves the XML blob untouched, so the object now disagrees with itself — and the XML wins the next time the object loads.

Compounding this, Hibernate caches objects in memory. A direct SQL change to a cached object is silently overwritten the next time the application saves it.

Use the iiq console, the API, or a task. SELECT freely; never UPDATE, INSERT, or DELETE.

Keeping the Database from Growing Forever

Four tables grow without bound if left alone: spt_task_result, spt_syslog_event, spt_audit_event, and spt_provisioning_transaction. On a busy deployment spt_syslog_event can reach tens of millions of rows, at which point ordinary queries slow noticeably.

Schedule the built-in Perform Maintenance task and configure retention in System Setup. Verify it is working:

SELECT COUNT(*)                          AS total,
       FROM_UNIXTIME(MIN(created)/1000)  AS oldest,
       FROM_UNIXTIME(MAX(created)/1000)  AS newest
FROM   spt_syslog_event;

If oldest predates your retention window, purging is not running.

MySQL Settings That Matter

[mysqld]
innodb_buffer_pool_size = 8G        # size to available RAM
max_allowed_packet      = 64M       # large XML blobs exceed the 4M default
innodb_log_file_size    = 512M
character_set_server    = utf8mb4
transaction_isolation   = READ-COMMITTED

max_allowed_packet is the one that bites first. IdentityIQ writes large XML blobs, and the default rejects them with Packet for query is too large, usually during aggregation of a large application.

Shell Scripting: Automating the Console

The iiq console reads from stdin, which makes it scriptable. This is the foundation for backup, deployment, and health-check automation.

Exporting Objects for Version Control

IdentityIQ objects live in the database and are therefore invisible to Git. A database refresh destroys uncommitted customization. This script exports them:

#!/usr/bin/env bash
set -euo pipefail

IIQ_HOME="${IIQ_HOME:-/opt/tomcat/webapps/identityiq}"
IIQ_BIN="$IIQ_HOME/WEB-INF/bin"
EXPORT_DIR="${1:-./iiq-export}"

mkdir -p "$EXPORT_DIR"/{rules,workflows,tasks,applications}

export_class() {
  local cls="$1" dest="$2"
  echo "Exporting $cls..."

  "$IIQ_BIN/iiq" console <<CONSOLE | grep -v '^>' > "$dest/_list.txt"
list $cls
CONSOLE

  while IFS= read -r name; do
    [[ -z "$name" ]] && continue
    local safe="${name//[^a-zA-Z0-9._-]/_}"
    "$IIQ_BIN/iiq" console <<CONSOLE >/dev/null
checkout $cls "$name" $dest/$safe.xml
CONSOLE
  done < "$dest/_list.txt"
}

export_class Rule         "$EXPORT_DIR/rules"
export_class Workflow     "$EXPORT_DIR/workflows"
export_class TaskDefinition "$EXPORT_DIR/tasks"

echo "Exported to $EXPORT_DIR"

Run it on a schedule, commit the output, and a database refresh becomes recoverable.

Deploying XML with Validation

#!/usr/bin/env bash
set -euo pipefail

IIQ_BIN="${IIQ_HOME:-/opt/tomcat/webapps/identityiq}/WEB-INF/bin"
TARGET="$1"

if [[ ! -e "$TARGET" ]]; then
  echo "ERROR: $TARGET not found" >&2
  exit 1
fi

# Fail before touching IdentityIQ if the XML is malformed
find "$TARGET" -name '*.xml' -print0 | while IFS= read -r -d '' f; do
  xmllint --noout "$f" || { echo "ERROR: invalid XML in $f" >&2; exit 1; }
done

for f in $(find "$TARGET" -name '*.xml' | sort); do
  echo "Importing $(basename "$f")"
  "$IIQ_BIN/iiq" console <<CONSOLE
import $f
CONSOLE
done

The xmllint pre-check matters because import on malformed XML can partially apply, leaving the environment in a state neither matching the old nor the new definition.

A Health Check Worth Cron-ing

#!/usr/bin/env bash
# Report IdentityIQ health; exit non-zero if anything is wrong.

DB_USER="${DB_USER:-identityiq}"
DB_NAME="${DB_NAME:-identityiq}"
CATALINA_OUT="${CATALINA_OUT:-/opt/tomcat/logs/catalina.out}"
status=0

q() { mysql -u "$DB_USER" -p"$DB_PASS" -N -B -e "$1" "$DB_NAME" 2>/dev/null; }

failed=$(q "SELECT COUNT(*) FROM spt_task_result
            WHERE completion_status='Error'
              AND completed > (UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) * 1000);")
if [[ "${failed:-0}" -gt 0 ]]; then
  echo "WARN: $failed task(s) failed in the last 24h"
  status=1
fi

stuck=$(q "SELECT COUNT(*) FROM spt_workflow_case
           WHERE created < (UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) * 1000);")
if [[ "${stuck:-0}" -gt 0 ]]; then
  echo "WARN: $stuck workflow case(s) older than 30 days"
  status=1
fi

if [[ -f "$CATALINA_OUT" ]]; then
  oom=$(grep -c 'OutOfMemoryError' "$CATALINA_OUT" || true)
  [[ "$oom" -gt 0 ]] && { echo "CRITICAL: $oom OutOfMemoryError in catalina.out"; status=2; }
fi

exit $status

Note -p"$DB_PASS" reads from the environment rather than hardcoding a credential, and -N -B strips headers and formatting so the output parses cleanly.

Watching Aggregation in Real Time

# Follow only connector activity during an aggregation run
tail -f /opt/tomcat/logs/catalina.out | grep --line-buffered -E 'sailpoint.connector|Aggregation'

# Count errors by type from today's log
grep 'ERROR' /opt/tomcat/logs/catalina.out \
  | sed -E 's/.*ERROR[[:space:]]+//' \
  | cut -d' ' -f1 \
  | sort | uniq -c | sort -rn | head -20

--line-buffered on grep is what makes the first command actually stream; without it grep buffers output and the tail appears frozen.

Putting It Together

A sound IdentityIQ deployment pipeline uses all three layers: shell scripts export objects from development into Git, compiled Java holds logic too complex for BeanShell, and MySQL is queried read-only for diagnostics while all writes go through the IdentityIQ API.

The governance logic you build on top of this — rules, workflows, and tasks — is covered in the companion guide to IdentityIQ BeanShell rules, workflows, and tasks. For how IdentityIQ compares to other platforms in this space, see our IAM tools comparison.