Documentation Plugins IBM Db2 Template variables

IBM Db2 template variables

Template variables allow you to create dynamic, reusable dashboards. Instead of hard-coding values in your queries, you can use variables that users can change from the dashboard.

Before you begin

Before using template variables, ensure you have:

• Configured the IBM Db2 data source
• Basic understanding of Grafana template variables

Use variables in queries

You can use Grafana template variables in your SQL queries. Variables are replaced with their selected values before the query runs.

Use the $variable or ${variable} syntax to reference a variable:

SQL Copy

SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY
FROM EMPLOYEE
WHERE WORKDEPT = '$department'

Multi-value variables

For variables that allow multiple selections, use the IN operator:

SQL Copy

SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY
FROM EMPLOYEE
WHERE WORKDEPT IN ($department)

Multi-value variables are automatically formatted as comma-separated quoted strings. For example, if the user selects departments A00, B01, and C01, the query becomes:

SQL Copy

WHERE WORKDEPT IN ('A00','B01','C01')

Create a query variable

You can populate variable options dynamically using a SQL query against your IBM Db2 database.

To create a query variable:

1. Navigate to Dashboard settings > Variables.
2. Click Add variable.
3. Select Query as the variable type.
4. Select your IBM Db2 data source.
5. Enter a SQL query that returns the values you want.

Query variable behavior

The query result determines how variable options are populated:

Example: Department variable

Create a variable that lists all departments:

SQL Copy

SELECT DISTINCT WORKDEPT FROM EMPLOYEE ORDER BY WORKDEPT

Example: Department with description

Create a variable with department code as the value and a description as the display text:

SQL Copy

SELECT DEPTNO, DEPTNAME FROM DEPARTMENT ORDER BY DEPTNO

This displays the department name to users but uses the department number in queries.

Variable examples

Filter by job type

Variable query:

SQL Copy

SELECT DISTINCT JOB FROM EMPLOYEE ORDER BY JOB

Dashboard query using the variable:

SQL Copy

SELECT EMPNO, FIRSTNME, LASTNAME, JOB, SALARY
FROM EMPLOYEE
WHERE JOB = '$job'
ORDER BY SALARY DESC

Filter by salary range

Variable query (custom ranges):

SQL Copy

SELECT '0-50000' AS range FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '50000-75000' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '75000-100000' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '100000+' FROM SYSIBM.SYSDUMMY1

Dashboard query using the variable:

SQL Copy

SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM EMPLOYEE
WHERE 
  CASE 
    WHEN '$salary_range' = '0-50000' THEN SALARY < 50000
    WHEN '$salary_range' = '50000-75000' THEN SALARY >= 50000 AND SALARY < 75000
    WHEN '$salary_range' = '75000-100000' THEN SALARY >= 75000 AND SALARY < 100000
    WHEN '$salary_range' = '100000+' THEN SALARY >= 100000
  END
ORDER BY SALARY DESC