MVSFORUMS.com

[jctgf]

--------------------------------------------------------------------------------

hi,

I have a batch application that updates 2 tables. I need to update AND COMMIT a record from the first table every time I update a SET of 500 records from the 2nd table.

Meanwhile the first table needs to be commited many times during the processing of the job, the 2nd table can only be committed at the end of program.

How can I do that?


thanks,

[dbzTHEdinosauer]

jctgf,

You need to code your batch application in programming language(s) that provides you a means of separating your units of work, in this case SQL against table 1 and table 2, into two tasks.

[CICS Guy]

I'm no DBA guru, but I'm thinking that, no way.....
Have you looked a the FM to see if there is a way to COMMIT part of an LUW without COMMITing the rest of it? - Kind of rings a bell that it might be possible, but, like I said......
Do you need a pointer to the Fine Manuals?

But, If Dbz says there might be a way, I'm listening too.....

[jctgf]

when the user presses enter, an update process begins. from the user's point of view, the update is running online but due to the number of lines to be updated, it's actually being processed in the batch mode.

the online application will submit a batch job in order to update table "a". the update process may take up to 10 minutes. during this time, i need to inform the user the status of the process. i'd like to exhibit on the screen the number of rows processed at every 500 rows.

table "b" is only a service table. it's function is only to register the number of lines updated on table "a" and make possible the display i mentioned above.

i can't commit table "a" before the end of the job because, in case of an abend, this table would remain partially updated and it would be disastrous.

thanks

[CICS Guy]

If table "b" is only a service table, why are you worryed about commiting it?

[Nic Clouston]

Also, if you are just displaying to the user the progress - why not use a variable to hold the count - you probably do anyway so just use that on your display. Update the service table at commit time on table a.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

[dbzTHEdinosauer]

jctgf,

Have you thought of having the CICS routine do a dirty read on your 'service table'? (dirty read is WITH UR).

[jctgf]

Hi there,

I appreciate the interest and I think I have found a solution.

It

[dbzTHEdinosauer]

jctgf,

can you show us a copy of your SQL for the on-line modules' SELECT? That will enable us to explain.

The results of a SELECT on unCOMMITed data in DB2 has nothing to do with batch and on-line. Each CICS session is a task (each time the user depresses a key and CICS is invoked, it is a new task); every active batch pgm is a task.

Any unCOMMITed data in one task is not available to another task, unless the 'other' task uses the WITH UR clause in the SQL.
UR= Uncommitted Read.
_________________
Dick Brenholtz
American living in Varel, Germany

[jctgf]

hi,

[dbzTHEdinosauer]

The WITH clause Specifies the isolation level at which the statement is executed. the following two links elaborate further. (though they are VSN 7, I don't think they have changed). The first link shows the syntax used in a SELECT ... WITH UR

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/5.92?SHELF=&DT=20010718164132&CASE=

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/4.4.5?SHELF=&DT=20010718164132&CASE=

Other than SPUFI, I have never known the default isolation level to be UR. From a business prospective, it would be suicide.

Please show us the SELECT SQL; partial SQL is of little help.

also,
How is the timestamp column in the db2evt.prm_srvc row generated?

how many users could 'simultaneously' perform this 'update' process?
If more than one, is your service table capable of differentiating two simultaneous processes?
Providing your complete SELECT would help.
How many rows are in db2evt.prm_srvc?

Please, give us answers to our (my) questions so that we can help you,
because the only way you are receiving results in you CICS modules is:

• you have a WITH clause in your SELECT - no site on this planet would make the default isolation level UR
• you obtain results because the batch side is complete
• your SELECT results are not what you think

_________________
Dick Brenholtz
American living in Varel, Germany

[jctgf]

hi,

i think u r 100% right.

i was running my application in the development environment and things were working as expected, that is, the online application could read the uncomitted updates made by the batch application.

today i transferred the application to the production environment and things simply didn't work anyway. the progress counter wasn't able to change from zero to any value, despite the fact the batch application was running for quite a few minutes.

following your directions, i activated the WITH UR clause but things still didn't work.

i finally activated the FOR FETCH ONLY clause and things started to work as expected.

apparently there is a difference between the configuration of the development and the production environments.

my current query is:

EXEC SQL
DECLARE C001 CURSOR FOR
SELECT CD_IDFR_SRVC ,
CD_PGM ,
CD_USU
FROM DB2EVT.PRM_PRCT_SRVC
WHERE TS_TRAN = :GD-TS-TRAN
FOR FETCH ONLY
WITH UR
END-EXEC.

my previous query was:

EXEC SQL
DECLARE C001 CURSOR FOR
SELECT CD_IDFR_SRVC ,
CD_PGM ,
CD_USU
FROM DB2EVT.PRM_PRCT_SRVC
WHERE TS_TRAN = :GD-TS-TRAN
END-EXEC.

[dbzTHEdinosauer]

jctgf,

I realize that I was not asked, but I am going to give you some thoughts, anyway.

1. Does your CICS display routine execute a RETURN? or are you sitting there, executing SQL and updating the screen with repeated SENDS?

If so, read on, otherwise skip to 2., below.
do you execute a CICS EXEC DELAY or SUSPEND inbetween the SENDS? If you have the timestamp value for your USER's particular update, why not just a simple SELECT instead of a CURSOR? If you have to use a CURSOR to find it, suggest that your logic incorporate a first time routine to use the CURSOR to find the exact ROW and then subsequent (500 row updates) just use the simple SELECT - both with UR. That will reduce the resource usage of the session.

2. You mention there are many rows in the service table. When do you delete 'old' rows. After some period of time the service table will have enough rows that the CURSOR/FETCH logic will itself use more resources than a utility such as this should use.

3. Have you provided a facility whereby someone can see all the update jobs in a single screen?
_________________
Dick Brenholtz
American living in Varel, Germany

[jctgf]

hi dinosaur,

the user keeps pressing enter at every 5 seconds or so in order to see the progress of the batch routine.

when i say CURSOR i mean a simple select. however, u have lit a doubt on me: is there a substantial difference between using a cursor or a simple select?

i can't precise the exact number of rows now, but all old rows r meant to be deleted soon.

the answer for item 3 is no.


thanks,
jc

[dbzTHEdinosauer]

jctgf,