sql局部变量和全局变量_有效使用SQL内置全局变量

SQL内置全局变量是只读的，由IBM®DB2®for i维护，并且是受信任且易于使用的资源。 存在一些全局变量是为了与DB2系列兼容，并且包含在SYSIBM模式中。 其他全局变量提供IBM i特定的值，并包含在QSYS2模式中。 全局变量使应用程序和用户可以轻松访问有用的环境信息，这些信息可用于审核和调试。 它们允许改进的应用程序日志记录以及更高级的出口点程序，触发器程序以及行和列访问控制（RCAC）规则文本。

表1包含有关SQL内置全局变量的一般信息。 内置全局变量在IBM i 7.2之前的版本中不可用。

表1. SQL内置全局变量的一般信息

变量名	架构图	数据类型	尺寸	SF99702等级	SF99703等级
PROCESS_ID	QSYS2	整数	--	11级	7.3基
THREAD_ID	QSYS2	比金特	--	11级	7.3基
JOB_NAME	QSYS2	VARCHAR	28	3级	7.3基
SERVER_MODE_JOB_NAME	QSYS2	VARCHAR	28	3级	7.3基
CLIENT_IPADDR	SYSIBM	VARCHAR	128	7.2基	7.3基
CLIENT_HOST	SYSIBM	VARCHAR	255	7.2基	7.3基
CLIENT_PORT	SYSIBM	整数	--	7.2基	7.3基
ROUTINE_SCHEMA	SYSIBM	VARCHAR	128	7.2基	7.3基
ROUTINE_SPECIFIC_NAME	SYSIBM	VARCHAR	128	7.2基	7.3基
ROUTINE_TYPE	SYSIBM	焦炭	1个	7.2基	7.3基
包裹名字	SYSIBM	VARCHAR	128	7.2基	7.3基
PACKAGE_SCHEMA	SYSIBM	VARCHAR	128	7.2基	7.3基
PACKAGE_VERSION	SYSIBM	VARCHAR	64	7.2基	7.3基

您可以在QSYS2.SYSVARIABLES目录中找到内置的全局变量。 显示所有可用SQL内置全局变量的查询是：

SELECT VARIABLE_SCHEMA, VARIABLE_NAME
    FROM QSYS2.SYSVARIABLES
    WHERE VARIABLE_SCHEMA = 'QSYS2' OR
          VARIABLE_SCHEMA = 'SYSIBM';

在引入这些内置全局变量之前，在SQL中获取此信息的唯一方法是创建一个外部过程或函数，以从系统API获取信息并返回该信息。 具有内置的全局变量消除了这种工作，并提高了效率。

QSYS2.THREAD_ID

内置的QSYS2.THREAD_ID全局变量包含当前线程的线程标识符（ID）。 数据为BIGINT ，但是如果您希望以十六进制形式查看值，请使用内置函数HEX()全局变量。

线程ID用于唯一标识作业中的线程。 虽然在同一作业中启动的两个线程都不会具有相同的线程ID，但是不同作业中的线程可能具有相同的线程ID值。 当线程结束时，其线程ID永远不会在作业中重用。 随着线程信息以更多方式出现，线程ID信息变得越来越重要。 这包括在使用它们的查询中的不同线程中运行的函数。

这是一个示例，用于捕获当前线程持有的SALES表上的线程范围的记录锁：

SELECT * FROM QSYS2.RECORD_LOCK_INFO
  WHERE TABLE_NAME = 'SALES' AND
  TABLE_SCHEMA = 'COMPANY'   AND
  JOB_NAME = QSYS2.JOB_NAME  AND
  THREAD_ID = QSYS2.THREAD_ID;

QSYS2.PROCESS_ID

内置全局变量QSYS2.PROCESS_ID包含当前正在运行的作业的进程（ID）。 数据类型为INTEGER 。 进程ID用于唯一地标识系统上的活动作业。 没有两个活动作业具有相同的进程ID。

这是捕获当前作业的详细信息的示例：

SELECT USER, CURRENT SERVER, QSYS2.JOB_NAME, QSYS2.THREAD_ID, QSYS2.PROCESS_ID
  FROM SYSIBM.SYSDUMMY1;

QSYS2.JOB_NAME

QSYS2.JOB_NAME内置全局变量包含当前作业的名称。 它是VARCHAR(28)类型。

作业名称用于唯一标识活动作业。 没有两个活动作业具有相同的作业名称。

这是一个使用视图隐藏表函数的大量用法的示例：

CREATE OR REPLACE VIEW QGPL.MYJOBINFO AS
(SELECT QSYS2.JOB_NAME, A.* FROM TABLE(QSYS2.GET_JOB_INFO(QSYS2.JOB_NAME)) A);

SELECT * FROM QGPL.MYJOBINFO;

无论如何建立连接，都可以获取作业信息。 当使用接口（例如命令行界面（CLI），Java数据库连接性（JDBC）或分布式关系数据库体系结构（DRDA））时，找到工作可能会面临挑战，这可以减轻寻找工作的麻烦。 您还可以使用三部分命名来确定远程系统上正在使用的作业：

SELECT * FROM REMOTESYS.QGPL.MYJOBINFO;

表2.引用线程ID，进程ID和作业信息的位置

参考信息的地方	THREAD_ID	PROCESS_ID	JOB_NAME
OBJECT_LOCK_INFO视图	✔		✔
RECORD_LOCK_INFO视图	✔		✔
AUTHORITY_COLLECTION视图	✔		✔
JVM_INFO视图		✔
SYSLIMITS视图			✔
OUTPUT_QUEUE_ENTRIES视图			✔
OUTPUT_QUEUE_ENTRIES表函数			✔
ACTIVE_JOB_INFO表函数			✔
数据库监视器（DBMON）	✔		✔
日记帐分录	✔
系统API	pthread_equal，pthread_getunique_np，pthread_kill，pthread_setname_np，pthread_getname_np，pthread_join_np	getpid，getppid，QlgSpawn，Qp0wChkChld，Qp0wChkPid，Qp0wGetJobID，Qp0wGetPid，Qp0wGetPPid，生成，等待，waitpid
系统指令	WRKJOB，STRAUTCOL，STRWCH
IBM PASE for i命令		杀死，ps，等待

QSYS2.SERVER_MODE_JOB_NAME

QSYS2.SERVER_MODE_JOB_NAME内置全局变量包含建立SQL Server模式连接的作业的名称。 它是VARCHAR(28)类型。 如果没有服务器模式连接，则值为NULL 。

这是捕获服务器模式作业的详细信息的示例：

SELECT * FROM TABLE(QSYS2.GET_JOB_INFO(QSYS2.SERVER_MODE_JOB_NAME)) A;

SYSIBM.CLIENT_IPADDR

内置的SYSIBM.CLIENT_IPADDR全局变量包含系统返回的当前客户端的IP地址。 它是VARCHAR(128)类型。 如果客户端未使用TCP / IP或安全套接字层（SSL）协议进行连接，则该值为NULL 。

SYSIBM.CLIENT_PORT

内置SYSIBM.CLIENT_PORT内置全局变量包含当前客户机用于与服务器通信的端口号。 它是INTEGER类型的。 如果客户端未使用TCP / IP协议进行连接，则该值为NULL 。

SYSIBM.CLIENT_HOST

内置的SYSIBM.CLIENT_HOST全局变量包含系统返回的当前客户端的主机名。 它的类型为VARCHAR(255) 。

如果客户端连接源自在本地系统上运行的应用程序，则全局变量的值为NULL 。 接受连接后，服务器将从网络获取客户端IP地址。 如果该进程不是源自使用TCP / IP的远程系统，则该值为NULL 。

这是定义RCAC权限以允许客户端使用的特定客户端IP地址，端口号或主机名访问CUSTOMER_TABLE表的示例。

CREATE OR REPLACE PERMISSION HOST_ACCESS ON CUSTOMER_TABLE
   FOR ROWS WHERE
       (SYSIBM.CLIENT_IPADDR = '9.181.88.248' OR
        SYSIBM.CLIENT_PORT = 51074 OR
        SYSIBM.CLIENT_HOST = 'IBMSYSTEM.IBM.COM')
   ENFORCED FOR ALL ACCESS
   ENABLE;

SYSIBM.ROUTINE_SCHEMA

SYSIBM.ROUTINE_SCHEMA内置全局变量包含当前正在运行的例程的模式名称。 它是VARCHAR(128)类型。 如果当前没有例程在运行，则值为NULL 。

ROUTINE_SCHEMA变量仅为过程和功能设置。 未为触发器设置该变量。

SYSIBM.ROUTINE_SPECIFIC_NAME

SYSIBM.ROUTINE_SPECIFIC_NAME内置全局变量包含当前正在运行的例程的名称。 它是VARCHAR(128)类型。 如果当前没有例程在运行，则值为NULL 。

ROUTINE_SPECIFIC_NAME变量仅为过程和函数设置。 未为触发器设置该变量。

SYSIBM.ROUTINE_TYPE

SYSIBM.ROUTINE_TYPE内置全局变量包含当前运行的例程的类型。 它是CHAR(1)类型。 全局变量的值对于过程为' P '或对于函数为' F '。 如果当前没有例程在运行，则值为NULL 。

表3.引用常规信息的位置

参考信息的地方	ROUTINE_SCHEMA	ROUTINE_SPECIFIC_NAME	ROUTINE_TYPE
ROUTINE_PRIVILEGES视图	✔	SPECIFIC_NAME
SYSROUTINEAUTH视图	✔	SPECIFIC_NAME
SYSFUNCS视图	✔	SPECIFIC_NAME
SYSPROCS视图	✔	SPECIFIC_NAME
常规视图	✔	SPECIFIC_NAME	✔
SYSPROGRAMSTAT视图		SPECIFIC_NAME	✔
SYSROUTINES表	✔	SPECIFIC_NAME	✔
QSQPRCED API	✔

这是一个定义RCAC掩码的示例，以允许仅针对特定的例程模式，例程名称和例程类型查看EMPLOYEE表中的实际薪水数据：

CREATE MASK EMP_ACCESS ON EMPLOYEE
  FOR COLUMN SALARY
  RETURN
  CASE
    WHEN (SYSIBM.ROUTINE_SCHEMA = 'HUMAN_RESOURCE_DEPT' AND
          SYSIBM.ROUTINE_SPECIFIC_NAME = 'HRDEPT' AND
          SYSIBM.ROUTINE_TYPE = 'P')
      THEN SALARY ELSE 00000
    END ENABLE;

SYSIBM.PACKAGE_NAME

SYSIBM.PACKAGE_NAME内置全局变量包含当前用于DRDA连接的软件包的名称。 它是VARCHAR(128)类型。

如果当前没有正在运行的包，则值为NULL 。

SYSIBM.PACKAGE_SCHEMA

SYSIBM 。 PACKAGE_SCHEMA内置全局变量包含当前用于DRDA连接的软件包的模式名称。 它是VARCHAR(128)类型。 如果当前没有正在运行的包，则值为NULL 。

SYSIBM.PACKAGE_VERSION

内置SYSIBM.PACKAGE_VERSION内置全局变量包含当前用于DRDA连接的软件包的版本标识符。 它是VARCHAR(64)类型。

如果当前没有正在运行的程序包，或者当前正在运行的程序包没有版本标识符，则该值为NULL 。 仅当从DB2 for i以外的服务器创建程序包时，程序包才具有版本标识符。

表4.引用软件包信息的位置

参考信息的地方	包裹名字	PACKAGE_SCHEMA	PACKAGE_VERSION
SYSPACKAGE视图	✔	✔
SYSPACKAGESTAT视图	✔	✔
SYSPACKAGESTMTSTAT视图	✔	✔
SYSPACKAGEAUTH视图	✔	✔
数据库监视器（DBMON）	✔

这是一个示例，用于在每次插入DATATAB表之前收集包信息（名称，架构和版本）：

CREATE TRIGGER RECORD BEFORE INSERT ON DATATAB
REFERENCING NEW ROW AS N
FOR EACH ROW MODE DB2ROW ENABLE SECURED
BEGIN
  INSERT INTO INFOCOLLECTION(SYSIBM.PACKAGE_NAME,  
                             SYSIBM.PACKAGE_SCHEMA,
                             SYSIBM.PACKAGE_VERSION);
END;

摘要

SQL内置的全局变量旨在为用户提供对有用的环境信息的轻松访问。 如本文所述，这些SQL内置全局变量可以轻松用于审核和调试。 继续尝试一下吧！

翻译自: https://www.ibm.com/developerworks/ibmi/library/i-use-sql-built-in-global-variables-trs/index.html