問題描述

大型機中是否有 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 語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！