Create a DB2 global variable

What global variables are for

A user-defined global variable is created with CREATE VARIABLE and referenced as schema.variable. Each application process has its own current value, initialized from the defined default. SET changes that session value. This makes variables useful for session-level context that can be read by SQL PL routines, views, functions, or statements without repeatedly passing the same argument.

Built-in global variables are supplied by Db2 for system functions; user-defined variables are your schema objects. Keep the distinction clear in naming and documentation. Use variables sparingly: a procedure parameter is clearer when the value is part of the operation contract, and a table is required when the value must persist across sessions.

Prerequisites and creation

Confirm CREATEIN authority for the schema, intended owner, type and nullability, default semantics, and READ/WRITE privilege model. Pick a schema dedicated to application context rather than scattering variables across unrelated object schemas. Do not store passwords, access tokens, or secrets in a global variable: they are application state, not a secret-management facility.

1
2
3
4
5
6
CREATE VARIABLE SALES.CURRENT_REGION
  VARCHAR(10)
  DEFAULT 'NA';
 
SET SALES.CURRENT_REGION = 'EMEA';
VALUES SALES.CURRENT_REGION;

Using session context safely

Set the variable at connection checkout or request entry, and reset it before a pooled connection returns to the pool. Connection pooling is the most common failure mode: session state left by one request can affect the next user. Prefer a transaction/request wrapper that sets all context values deterministically rather than assuming a default survived every route.

Variables can be referenced in SQL expressions and routines. Use them for controlled filters only after designing authorization correctly. A client-controlled CURRENT_REGION variable is not proof that the client is entitled to see that region; enforce real access with privileges, row permissions, trusted contexts, or server-side validation.

Verification, grants, and errors

Verify the definition in the catalog, use VALUES to read the default, SET a test value, and reconnect to prove session scope. Test from the actual application role. Grant READ to consumers that need to inspect a value and WRITE only to trusted routines or roles that may change it. Document who sets each variable and when it is reset.

Common errors include missing CREATEIN, trying to use an unsupported type/default expression, missing READ or WRITE authority, confusing a user variable with a built-in variable, and stale state in a pooled connection. If a query returns another user’s context, stop treating it as a formatting bug—it is a security incident.

Profiles and routines

Db2 profile attributes can set selected built-in global variables for qualifying remote applications. That is a subsystem-level policy feature and is different from CREATE VARIABLE. An explicit SET in an application can take precedence for supported built-ins, so document the interaction before relying on a profile for a security-sensitive value.

Routines may use global variables to reduce parameter noise, but this creates implicit dependencies. Include variable initialization in routine tests and deploy the variable before dependent views, functions, or procedures. In change control, treat a default-value change as behavior change, not as harmless metadata.