DB2 System Runtime Table 检索最后执行的 SQL 语句 [英] DB2 System Runtime Table to retrieve last executed SQL Statement

问题描述

大型机中是否有 DB2 系统表 - 批处理运行时日志?在 DB2 for i 系列中，有一个表函数 QSYS2.GET_JOB_INFO() 在运行时返回作业信息，包括状态(活动/完成)和最重要的 V_SQL_STATEMENT_TEXT - 语句上次 SQL 运行的时间.

Is there a DB2 System Table - Batch Runtime log in Mainframe? In DB2 for i Series, there is a table function QSYS2.GET_JOB_INFO() that returns Job Information during runtime including the Status (Active /Complete) and most importantly V_SQL_STATEMENT_TEXT - Statement of the last SQL run.

场景:我想在 Cobol 批处理作业中检索运行时最后执行的 SQL 语句.这样做的主要目的是确定在作业运行时是否发出了 COMMIT 或 ROLLBACK.目的是创建一个小程序，我们称之为控制器"，在发出 Commit 或 Commit interval 甚至 Rollback 时监控 DB2.更具体地说，这个控制器"将充当迷你操作系统，并具有触发主程序的能力.

Scenario: I want to retrieve the last executed SQL Statement during runtime in Cobol Batch Job. The main purpose of this is to determine if a COMMIT or ROLLBACK has been issued, while the job is running. The aim is to create small program, let's call it "controller", to monitor DB2 when Commit or Commit interval is issued, or even Rollback. To be more specific - this "controller" will act as mini OS and will have the capacity to trigger the Main Programs.

例如，如果主程序发出 ROLLBACK，则控制器程序"可以发出特定的业务逻辑并控制更新.可以在 T1 和 T2 类型的 DB2 连接中进行更新.通过这种方式，更新是在批处理客户端或在 EXCI 中运行的 Java 端完成的(EXCI 使用 RRS 恢复).

For instance, if the Main program issues a ROLLBACK the "controller program" can issue specific business logic and can control the updates. Updates can be done in both T1 and T2 Type of DB2 Connection. By that means, updates are done in batch client side or Java side running in EXCI (EXCI using RRS recovery).

推荐答案

快速浏览 IBM DB2 文档 似乎表明否".

A quick look in the IBM Documentation for DB2 seems to indicate "no."

但是，虽然与您的情况不完全匹配，但我们过去常常这样做...

However, while not an exact match for your situation, here's what we used to do...

创建一个表，将其称为 APP_RESTART_DATA 列，以唯一标识您的流程的执行.我们使用 PROC_NAME 和 STEP_NAME，因为我们仅限于批处理作业.还有一个 KEY 列和任何其他您可能会发现在重新启动情况下有用的元数据.有些人存储的是记录号而不是实际的键值.

Create a table, call it APP_RESTART_DATA with columns to uniquely identify an execution of your process. We used PROC_NAME and STEP_NAME as we were confined to batch jobs. Also have a KEY column and any other metadata you might find helpful in a restart situation. Some people stored the record number instead of the actual key value.

在您的控制器程序中，首先使用您的唯一标识符执行 SELECT 以确定您是否处于重新启动模式.如果您的 SQLCODE 为 0，则您处于重新启动模式，并且将检索到最后一个成功执行 COMMIT 的 KEY.在这些情况下，您必须在输入数据中找到该键，然后立即开始对数据进行正常处理.如果您的 SQLCODE 为 100，则说明您未处于重新启动模式；在这些情况下，您可以在输入数据的开头开始正常处理.

In your controller program, begin by doing a SELECT with your unique identifier(s) to determine if you're in restart mode. If you get an SQLCODE of 0 then you are in restart mode and will have retrieved the last KEY for which a COMMIT was successfully executed. Under these circumstances you must locate that key in your input data and then begin normal processing with the data immediately subsequent. If you got an SQLCODE of 100 then you are not in restart mode; under these circumstances you can just begin normal processing at the start of your input data.

当您处理输入数据并到达 COMMIT 点时，还可以使用新 KEY 对您的 APP_RESTART_DATA 表进行 UPDATE.然后COMMIT.我们的 COMMIT 点也由一个参数决定，该参数指示在 COMMITs 之间要处理多少逻辑工作单元.如果有必要在主要班次期间运行通常在班外运行的批处理过程，我们可以减小此参数.

As you process the input data and reach a COMMIT point, also UPDATE your APP_RESTART_DATA table with the new KEY. Then COMMIT. Our COMMIT points were also dictated by a parameter indicating how many logical units of work to process between COMMITs. We could decrease this parameter if it became necessary to run batch processes during prime shift that were normally run off-shift.

当您完成输入数据的处理后，DELETEAPP_RESTART_DATA 表中您的进程的行.

When you complete processing of your input data, DELETE the row for your process in the APP_RESTART_DATA table.

捕捉 ROLLBACK 可能很棘手.您可以将 APP_RESTART_DATA 中的行标记为在代码中完成时执行了 ROLLBACK，但如果在异常结束情况下隐式完成，您可能会发现自己通过语言环境 CEEHDLR 可调用服务，以便您获得控制权并可以指示发生了 ROLLBACK.

Catching ROLLBACK might be tricky. You could flag your row in APP_RESTART_DATA as having performed a ROLLBACK when done in the code, but if done implicitly in an abend situation you may find yourself registering a condition handler via the Language Environment CEEHDLR callable service so you get control and can indicate a ROLLBACK occurred.

这篇关于DB2 System Runtime Table 检索最后执行的 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！