How to check your environment using Invantive UniversalSQL or Invantive Script?

Wiki (en)
,
1

Scripts written in Invantive SQL/PSQL or Invantive Script may pose requirements on their environment. For example, a script may temporarily require a very large amount of memory to complete or expect a number of cores to complete in time.

Both Invantive UniversalSQL and Invantive Script provides, respectively, advanced functions and variables to inspect the environment using a query or custom logic.

Environment Variables using Invantive UniversalSQL

Invantive UniversalSQL provides access to the environment using the SQL-function sys_context. The SQL-function sys_context provides a variety of information, all returned as a text. The following arguments can be used to retrieve information about the environment; consult the SQL-grammar for all possibilities:

  • CLIENT_LOGICAL_CORE_COUNT: number of logical processor cores in the client device.
  • CLIENT_SYSTEM_64_BIT: whether the OS is 64-bit on the client device.
  • PROCESS_64_BIT: whether the OS process on the client device runs as 64-bit.

The following code raises an error like:

The program requires a 64-bit process

when the program does not run in 64-bit mode:

declare
  l_is_process_64_bit boolean;
begin
  select to_boolean(sys_context('USERENV', 'PROCESS_64_BIT'))
  into   l_is_process_64_bit
  ;
  if l_is_process_64_bit = true
  then
    raise_application_error
    ( -20163
    , 'The program requires a 64-bit process.'
    , 'Please switch to 64-bit process.'
    );
  end if;
end;

Environment Variables using Invantive Script

Invantive Script provides local variables, which are lexicographically replaced in any SQL-statement by their value using the notation ${FULL-NAME}. The following Invantive Script variables can be used to retrieve information about the environment; consult the manual for all possibilities including access to environment variables:

  • ${system:is64bitprocess}: evaluates to a True text value when the process is running in x86-mode and True otherwise.
  • ${system:is64bitoperatingsystem}: evaluates to a True text value when the operating system is running in x86-mode and True otherwise.
  • ${system:processorcount}: evaluates to an integer value equaling the number of processors.
  • ${system:logicalcorecount}: evaluates to an integer value equaling the number of logical cores.
  • ${system:physicalcorecount}: evaluates to an integer value equaling the number of physical cores.
  • ${system:physicalmemoryinbytes}: evaluates to an integer value equaling the number of bytes in the physical memory.

The following code raises an error such as:

Number of cores must be at least 8 error

when the number of cores is deemed insufficient:

declare
  l_core_count pls_integer;
begin
  select '${system:physicalcorecount}'
  into   l_core_count
  ;
  if l_core_count < 8
  then
    raise_application_error
    ( -20163
    , 'The number of cores must be at least 8.'
    , 'Please increase the number of cores from ' 
      || l_core_count 
      || ' to 8.'
    );
  end if;
end;

More Resources

Other samples of Invantive UniversalSQL extracting data from the environment include:

A complete overview of the Invantive Script system variables is available in the manual. The manual also includes application variables and how to generally use variables in Invantive Script.

Access to the value of an environment variable is available through ${system:environmentvariable:NAME}.

Other samples of Invantive Script variables include: