Stored procedures

A file submitted with @ is compile-and-run: nothing persists. A stored procedure is the persistent form — the same program, named, stored in the target database itself, and runnable from any host with EXEC name(...). Procedures are how scheduled and repeated work ships: compile once, run forever.

Writing a procedure

A procedure file is the CREATE statement itself:

flag_big_orders.sumatra

CREATE OR REPLACE PROCEDURE flag_big_orders(cutoff INT) IS
BEGIN
  FOR batch IN (SELECT order_id, customer_id, amount, status, order_date, region
                FROM sumatra_orders
                WHERE status = 'pending'
                  AND amount >= CAST(PARAM('cutoff') AS INT))
      ORDER KEY (order_id)
      BATCH SIZE 10000
  LOOP
    FOR rec IN batch LOOP
      rec.status := 'priority';
      UPDATE sumatra_orders SET ROW = rec WHERE order_id = rec.order_id;
    END LOOP;
    COMMIT;
  END LOOP;
END;

The form differs from an anonymous block in three small ways:

The header is CREATE OR REPLACE PROCEDURE name(arg TYPE, ...) IS. Write the parentheses only when there is at least one argument — a zero-argument procedure has no () in its header.
Declarations come bare between IS and BEGIN — a procedure has no DECLARE keyword.
It closes with plain END; (not END name;).

Arguments are positional, read-only inputs, exactly like PARAM declarations: reference them inside SQL as PARAM('cutoff'), casting non-text values at the point of use. There are no OUT arguments, no defaults, and no named-argument calls. A PARAM section inside a procedure is not allowed — the header replaces it. RETURN; anywhere in the body ends the run cleanly (exit 0); work committed before the RETURN stays committed.

Storing it

Submit the file like any other:

sumatra> @flag_big_orders.sumatra
[sumatra] procedure flag_big_orders created (procid 4f2a91be00c17d55)

Because the file’s program is a CREATE, submitting it stores instead of running: the shell compiles it against the live catalog, builds it, saves the source in the database, and caches the binary on this host. Nothing executes, and a CREATE submission takes no arguments — argument values belong to EXEC. A compile error stores nothing. There is no “invalid procedure” state to discover at 3 a.m.: if it is stored, it compiled.

Running it

sumatra> EXEC flag_big_orders(500)
Completed (exit 0)

EXEC rules:

Parentheses always — EXEC nightly() is the zero-argument call.
Arguments are positional literals: bare tokens (42, 3.14, 2026-01-01, TRUE) or single-quoted text ('North America'). A text value containing a quote cannot be passed in this release.
No trailing semicolon — EXEC is one shell statement.

From a script or cron, use the one-shot form; the exit code is the run’s outcome (0 / 2 / 1):

# crontab: reprioritize nightly at 02:00
0 2 * * * /usr/local/bin/sumatra --dsn /etc/sumatra/prod.dsn "EXEC flag_big_orders(500)"

Where things live

Location	What	Notes
`sumatra_source` table, in the target database	One row per procedure: `name`, `source`, `procid`, `updated_at`	The database is the single source of truth; any SQL client can `SELECT` from it to list procedures
`~/.sumatra/bin/<db>/` on each host	Cached compiled binaries, named `<name>-<procid>-<toolid>`, with the generated `.cpp` beside each for inspection	Purely a cache — safe to delete; the next `EXEC` recompiles from stored source

Because the source lives in the database, a procedure stored from your laptop runs from the production cron host with nothing copied around — the first EXEC there compiles it locally and caches the binary.

When recompiles happen

A normal EXEC runs the cached binary with zero compilation. Recompiles happen only for four reasons, and each is announced:

[sumatra] compiling flag_big_orders (first run on this host)
[sumatra] compiling flag_big_orders (source changed)
[sumatra] compiling flag_big_orders (sumatra upgraded to 0.2.0)
[sumatra] compiling flag_big_orders (schema changed)

The last one deserves a note: every compiled program bakes in a fingerprint of the table shapes it was compiled against, and re-checks it at run start. If the schema drifted since compile, the binary refuses to run on stale assumptions; the shell then recompiles the stored source against the current schema and reruns — once. If the fresh compile refuses again, the schema is changing underfoot and the run stops loudly.

Managing procedures

sumatra> DROP PROCEDURE flag_big_orders
[sumatra] procedure flag_big_orders dropped

DROP PROCEDURE removes the database row and this host’s cache entries. Dropping a name that does not exist is an error, never a silent no-op. To change a procedure, just submit the edited CREATE OR REPLACE file again — replace is last-write-wins. A few operational notes:

Procedures do not call procedures. EXEC is a shell statement, not a language statement — composition is sequencing several EXEC lines in your script.
Duplicate runs are refused. Two concurrent runs of the same procedure with the same argument values are refused via an engine-side lock marker, so a cron overlap cannot double-apply.
The cache key is the database name. If two different Firebolt accounts have same-named databases, they share a host cache directory — use distinct database names (or hosts) to keep them apart.

Introduction

Getting Started

Writing Programs

Reference

Writing a procedure

Storing it

Running it

Where things live

When recompiles happen

Managing procedures

​Writing a procedure

​Storing it

​Running it

​Where things live

​When recompiles happen

​Managing procedures

Writing a procedure

Storing it

Running it

Where things live

When recompiles happen

Managing procedures