問題描述

大型機(jī)中是否有 DB2 系統(tǒng)表 - 批處理運(yùn)行時(shí)日志?在 DB2 for i 系列中，有一個(gè)表函數(shù) QSYS2.GET_JOB_INFO() 在運(yùn)行時(shí)返回作業(yè)信息，包括狀態(tài)(活動(dòng)/完成)和最重要的 V_SQL_STATEMENT_TEXT - 語(yǔ)句上次 SQL 運(yùn)行的時(shí)間.

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.

場(chǎng)景:我想在 Cobol 批處理作業(yè)中檢索運(yùn)行時(shí)最后執(zhí)行的 SQL 語(yǔ)句.這樣做的主要目的是確定在作業(yè)運(yùn)行時(shí)是否發(fā)出了 COMMIT 或 ROLLBACK.目的是創(chuàng)建一個(gè)小程序，我們稱之為控制器"，在發(fā)出 Commit 或 Commit interval 甚至 Rollback 時(shí)監(jiān)控 DB2.更具體地說(shuō)，這個(gè)控制器"將充當(dāng)迷你操作系統(tǒng)，并具有觸發(fā)主程序的能力.

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.

例如，如果主程序發(fā)出 ROLLBACK，則控制器程序"可以發(fā)出特定的業(yè)務(wù)邏輯并控制更新.可以在 T1 和 T2 類型的 DB2 連接中進(jìn)行更新.通過這種方式，更新是在批處理客戶端或在 EXCI 中運(yùn)行的 Java 端完成的(EXCI 使用 RRS 恢復(fù)).

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...

創(chuàng)建一個(gè)表，將其稱為 APP_RESTART_DATA 列，以唯一標(biāo)識(shí)您的流程的執(zhí)行.我們使用 PROC_NAME 和 STEP_NAME，因?yàn)槲覀儍H限于批處理作業(yè).還有一個(gè) KEY 列和任何其他您可能會(huì)發(fā)現(xiàn)在重新啟動(dòng)情況下有用的元數(shù)據(jù).有些人存儲(chǔ)的是記錄號(hào)而不是實(shí)際的鍵值.

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.

在您的控制器程序中，首先使用您的唯一標(biāo)識(shí)符執(zhí)行 SELECT 以確定您是否處于重新啟動(dòng)模式.如果您的 SQLCODE 為 0，則您處于重新啟動(dòng)模式，并且將檢索到最后一個(gè)成功執(zhí)行 COMMIT 的 KEY.在這些情況下，您必須在輸入數(shù)據(jù)中找到該鍵，然后立即開始對(duì)數(shù)據(jù)進(jìn)行正常處理.如果您的 SQLCODE 為 100，則說(shuō)明您未處于重新啟動(dòng)模式；在這些情況下，您可以在輸入數(shù)據(jù)的開頭開始正常處理.

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.

當(dāng)您處理輸入數(shù)據(jù)并到達(dá) COMMIT 點(diǎn)時(shí)，還可以使用新 KEY 對(duì)您的 APP_RESTART_DATA 表進(jìn)行 UPDATE.然后COMMIT.我們的 COMMIT 點(diǎn)也由一個(gè)參數(shù)決定，該參數(shù)指示在 COMMITs 之間要處理多少邏輯工作單元.如果有必要在主要班次期間運(yùn)行通常在班外運(yùn)行的批處理過程，我們可以減小此參數(shù).

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.

當(dāng)您完成輸入數(shù)據(jù)的處理后，DELETEAPP_RESTART_DATA 表中您的進(jìn)程的行.

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

捕捉 ROLLBACK 可能很棘手.您可以將 APP_RESTART_DATA 中的行標(biāo)記為在代碼中完成時(shí)執(zhí)行了 ROLLBACK，但如果在異常結(jié)束情況下隱式完成，您可能會(huì)發(fā)現(xiàn)自己通過語(yǔ)言環(huán)境 CEEHDLR 可調(diào)用服務(wù)，以便您獲得控制權(quán)并可以指示發(fā)生了 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.

這篇關(guān)于DB2 System Runtime Table 檢索最后執(zhí)行的 SQL 語(yǔ)句的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！