1

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.

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.

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

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Cee
  • 11
  • 3
  • .... okay, _why_ do you want this? What business problem are you trying to solve? Note that any 'monitoring and decision' you attempt to make will be subject to severe race conditions. Note too that attempting to make a decision and do something else after either `ROLLBACK` or `COMMIT` is issued is liable to cause massive headaches. Both of these statements nearly always succeed instantly, but if you want to do some extra work (like adding an extra row somewhere), that goes completely out the window. Especially in the case of `ROLLBACK`, which may be auto-called for errors... – Clockwork-Muse Jul 17 '17 at 20:41
  • @Clockwork-Muse I totally agree, this approach is liable to cause headache. To make it simpler, if the main program issues an exec SQL commit, the controller program also has to issue a RRS Commit (SRRCMIT), on the other hand if the main program issues SQL rollback, the controller also has to issue RRS Rollback (SRRBACK). We opted for this approach as we cannot update the existing main programs. Disclaimer: This approach is still on design, as we are testing different approach and will choose the most effective one later. Thanks! – Cee Jul 18 '17 at 06:53
  • I've never used this feature (so can't speak to everything), but I feel this approach is doomed. If your "Main" program is down for any reason, things aren't going to be committed like you want. From the little I'm reading, it looks like you want to call either `COMMIT` or `SRRCMIT`, but not both, and I'm not sure what effect issuing the command would have (using that command implies you're doing some extra processing - but where?). It sounds like what you want isn't a monitoring program, but some sort of interpretation/wrapper layer, but I'm doubting you can replace `COMMIT`... – Clockwork-Muse Jul 18 '17 at 16:37

1 Answers1

0

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

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.

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.

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.

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

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.

cschneid
  • 10,237
  • 1
  • 28
  • 39
  • Yes I already scoured IBM Knowledge Center for DB2. I'm planning to explore your idea since we already have checkpoint table and it's existing on main programs. I can capture before and after image of the table and determine if there is a field value change. With that being said, If there is a change in commit interval, the main program issued a commit. For rollback, initial record will be inserted to checkpoint table as rollback identifier. Once the record is not found, the main program issued explicit Rollback, we've built LE handlers for unexpected error in the call chain. – Cee Jul 18 '17 at 10:25