DB2 SQL GLOBAL VARIABLES

GLOBAL VARIABLES

  1. GLOBAL VARIABLES are database objects to save a single value at a time(like DTAARA object)
  2. They can be accessed and modified using SQL statements.
  3. Their definition (only definition, not actual value) is stored in the database catalogs.
  4. The reason for this is that global variables have a session scope.
  5. This means that every session can use the GLOBAL VARIABLES that exist in the catalogs, but each session has its own private value that it can manipulate and use.
  6. No session can access the GLOBAL VARIABLE’s value of another session.
  7. CREATE VARIABLE statement is used to create new GLOBAL VARIABLES.
  8. These are very useful when calling a stored procedure with OUT/INOUT parameters.

Example:

Create Global variable

CREATE VARIABLE myCounter INT DEFAULT 01

User Global variable

SELECT EMPNO, LASTNAME, CASE WHEN myCounter  = 1 THEN SALARY ELSE NULL END FROM EMPLOYEE WHERE WORKDEPT = ’A00’

Change Value of GLOBAL VARIABLE

SQL SET command can be used to change the value of GLOBAL VARIABLE

SET myCounter = 29

Check Value of GLOBAL VARIABLE

You can use any SELECT statement to view the value of GLOBAL VARIABLE.

SELECT myCounter from SYSIBM.SYSDUMMY1

DROP VARIABLE

To delete GLOBAL VARIABLE, use DROP VARIABLE

DROP VARIABLE myCounter

--

--

--

A programmer.

Love podcasts or audiobooks? Learn on the go with our new app.

Spark Structured Streaming in K8s with Argo CD

AWS App Runner — A quick way to deploy applications

Azure DevOps Publish Artifacts for ASP.NET Core

Scalable airflow with Kubernetes + Git Sync

Searching for the right D(igital) Banking :D

How to recursively load PHP files in a Directory

linode api

How to use Git and GitHub for collaborative programming?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sumit goyal

Sumit goyal

A programmer.

More from Medium

Using the CASE Statement in SQL Server

SQL Part 6

How to create databases and tables?

SQL: Zero to Hero