Skip to content

Instantly share code, notes, and snippets.

@forstie
Created January 18, 2024 19:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save forstie/e53920610e3a5c35058c185b270704c0 to your computer and use it in GitHub Desktop.
Save forstie/e53920610e3a5c35058c185b270704c0 to your computer and use it in GitHub Desktop.
This gist comes from client requests to have a mechanism to "reset" environment variables within a job.
-- Subject: Reset environment variables in a job
-- Author: Scott Forstie
-- Date : January, 2024
-- Features Used : This Gist uses SQL PL, qsys2.ENVIRONMENT_VARIABLE_INFO, and qsys2.qcmdexc
--
--
-- When a job is started, it "inherits" all system level environment variables as job level environment variables.
-- From that point on, the job can add, change, and remove job level environment variables.
-- But... what should a job do if they want to reset or return to the initial state of environment variables?
-- The answer lies at the bottom of this gist...
--
-- Resources:
-- https://www.ibm.com/docs/en/i/7.5?topic=services-environment-variable-info-view
-- https://www.ibm.com/docs/en/i/7.5?topic=locale-using-environment-variables-set-active
-- The LANG environment variable is automatically created during job initiation
-- when you specify a locale path name for the LOCALE parameter in your user profile or for the QLOCALE system value.
--
-- Note: The approach below does not compensate for additional JOB level environment variables appearing.
-- If the reset needs to also remove JOB level environment variables, this gist could be revised to establish a base
-- understanding of the base set of job level envvars. Then, the reset processing would remove and/or reset those envvars.
stop;
--
-- What environment variables do I have established in this job?
--
select ENVIRONMENT_VARIABLE_NAME, ENVIRONMENT_VARIABLE_VALUE
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'JOB'
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- What system-level environment variables exist?
--
select ENVIRONMENT_VARIABLE_NAME, ENVIRONMENT_VARIABLE_VALUE
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'SYSTEM'
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- What environment variables does this job have, that do not exist at the sysstem-level?
--
select ENVIRONMENT_VARIABLE_NAME, ENVIRONMENT_VARIABLE_VALUE
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'JOB' and
ENVIRONMENT_VARIABLE_NAME not in (
select ENVIRONMENT_VARIABLE_NAME
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'SYSTEM')
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- Remove job level environment variables that came from system level envvar inheritance
--
select qsys2.qcmdexc(
'QSYS/RMVENVVAR ENVVAR(''' concat ENVIRONMENT_VARIABLE_NAME concat ''') LEVEL(*JOB)')
as rmvenvvar_results
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'JOB' and
ENVIRONMENT_VARIABLE_NAME in (
select ENVIRONMENT_VARIABLE_NAME
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'SYSTEM')
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- Re-review the job's envvars
--
SELECT ENVIRONMENT_VARIABLE_NAME, ENVIRONMENT_VARIABLE_VALUE
FROM QSYS2.ENVIRONMENT_VARIABLE_INFO
WHERE ENVIRONMENT_VARIABLE_TYPE = 'JOB'
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- Re-inherit the system level envvars
--
select
qsys2.qcmdexc(
'QSYS/ADDENVVAR ENVVAR(''' concat ENVIRONMENT_VARIABLE_NAME concat ''') VALUE(''' concat
ENVIRONMENT_VARIABLE_VALUE concat ''') CCSID(*JOB) LEVEL(*JOB) REPLACE(*YES)')
as addenvvar_results
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'SYSTEM'
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- Re-review the job's envvars
--
SELECT ENVIRONMENT_VARIABLE_NAME, ENVIRONMENT_VARIABLE_VALUE
FROM QSYS2.ENVIRONMENT_VARIABLE_INFO
WHERE ENVIRONMENT_VARIABLE_TYPE = 'JOB'
order by ENVIRONMENT_VARIABLE_NAME;
stop;
--
-- Bring the reset logic together in a procedure.
--
create or replace procedure Coolstuff.Reset_envvars ()
not deterministic
modifies sql data
set option commit = *NONE, dynusrprf = *USER, usrprf = *USER
begin
--
-- Remove job level environment variables that came from system level envvar inheritance
--
create or replace table qtemp.envvarrmv as
(select
qsys2.qcmdexc(
'QSYS/RMVENVVAR ENVVAR(''' concat ENVIRONMENT_VARIABLE_NAME concat ''') LEVEL(*JOB)')
as rmvenvvar_results
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'JOB' and
ENVIRONMENT_VARIABLE_NAME in (select ENVIRONMENT_VARIABLE_NAME
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'SYSTEM')
order by ENVIRONMENT_VARIABLE_NAME)
with data
on replace delete rows;
--
-- Add all system evnvars as job level environment variables
--
create or replace table qtemp.envvarrmv as
(select
qsys2.qcmdexc(
'QSYS/ADDENVVAR ENVVAR(''' concat ENVIRONMENT_VARIABLE_NAME concat ''') VALUE('''
concat ENVIRONMENT_VARIABLE_VALUE concat
''') CCSID(*JOB) LEVEL(*JOB) REPLACE(*YES)') as addenvvar_results
from QSYS2.ENVIRONMENT_VARIABLE_INFO
where ENVIRONMENT_VARIABLE_TYPE = 'SYSTEM'
order by ENVIRONMENT_VARIABLE_NAME)
with data
on replace delete rows;
end;
--
-- Authorize any user to use this facility
--
grant all on procedure Coolstuff.Reset_envvars to public;
stop;
--
-- Reset the envvars in the current job
--
CALL Coolstuff.Reset_envvars();
stop;
--
-- Re-review the job's envvars
--
SELECT ENVIRONMENT_VARIABLE_NAME, ENVIRONMENT_VARIABLE_VALUE
FROM QSYS2.ENVIRONMENT_VARIABLE_INFO
WHERE ENVIRONMENT_VARIABLE_TYPE = 'JOB'
order by ENVIRONMENT_VARIABLE_NAME;
@NielsLiisberg
Copy link

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment