| View previous topic :: View next topic |
| Author |
Message |
Rama_Prayaga
Beginner
Joined: 20 May 2003
Posts: 45
Topics: 15
|
 Posted: Tue Jul 05, 2005 7:00 am Post subject: DB2 -susbsytem crash |
 |
|
Hi,
I have four questions which are related to each other.
My PL/1 - DB2 batch application program updates and inserts rows in a table.I am commiting after every 1000 rows are processed as below.
| Code: |
if Mod (count,1000) = 0 then
exec sql commit; |
Now this Batch Job was cancelled by operator in between.Some 461 records were still uncommited.
question 1: How Much time approymately the system should take to rollback.
question 2: Does the system will give -911 or - 904 while doing rollback changes.
question 3: The Big problem which I faced that my DB2 subsystem got crashed and auto Backout processing started.How can I recognise where that problem occured.I need to determine the problem why the sub-system crash occured.I need to make sure it wasnt because of my program? Below in the log which shows my AUTH-id(F123456) and my Plan name(TNxxxx) And Jobname(F123456A) , so ppl are coming to conclusion that it is due to my application program cancellation.I have cancelled the Job several times earlier and never faced such problem.
can anyone help me to determine the exact cause for determine the problem.This would help me in understand the cause.
06:32:19 DSNR048I -DTF4 DSNRBMON UR BACKOUT PROCESSING LOG
06:32:19 RECORD
06:32:19 AT RBA 000000000000 TO RBA 029EEC6237CF FOR
06:32:19 CORRELATION NAME = F123456A
06:32:19 CONNECTION ID = DB2CALL
06:32:19 LUWID = CHSKA000.DTF4.BD4225377694 = 1327
06:32:19 PLAN NAME = TNxxxx
06:32:19 AUTHID = F123456
06:32:19 END USER ID = *...............
06:32:19 TRANSACTION NAME = *...............................
06:32:19 WORKSTATION NAME = *.................
question 4: While Backing out Changes ,is the table cannot be accessed.The table cannot be accessed in my present Subsystem now because of auto backout.
Request your help,
Rama |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 05, 2005 7:27 am Post subject: |
|
|
Rama,
A couple of questions. How is the "count" field incremented? Is it after every update/insert?
An single update statement can result in updating more than 1 row. So is the "count" incrmented accordingly? I don't see the use of MOD function here. What is the purpose of the MOD function here? Why can't it be a simple like this
| Code: |
IF count = 0 then
exec sql commit
move zero to count;
|
| Quote: |
question 1: How Much time approymately the system should take to rollback.
|
Depends on the commit frequency. If there are many units of work to be rolled back it might take a few minutes.
| Quote: |
question 2: Does the system will give -911 or - 904 while doing rollback changes.
|
If some one is trying to access the same page , then they may be getting -911 or -904
| Quote: |
question 3: The Big problem which I faced that my DB2 subsystem got crashed and auto Backout processing started.How can I recognise where that problem occured.I need to determine the problem why the sub-system crash occured.
|
If your program is inserting too many rows and if your sub-system runs out of space, then there is a chance that the sub-system may crash. But that is very rare,as most shops set up space limits for each individual table. So if you exceed the space allocated , you will just put the table in question in a unavailable status as it cannot expand the vsam clusters.
Your DBA's might be able to tell you about the exact cause looking into the DB2 log.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Wed Jul 06, 2005 10:34 am Post subject: |
|
|
DB2 may crash when cancel a long running job without frequent commit.
But I think it's a bug of DB2, not your appliaction problem.
Increase commit frequency may reduce the chance of crash happen.
In V5/V6, I have ever encount DB2 crash due to a batch program occupy too much lock resource. The explaination from DB2 LAB is DB2 didn't reserve a certain amount sotrage to process its own must-do opration, so kill itsself to protect the data. And then an PTF applied.
In V8, I encounter same problem as yours. Cancel long running jobs, then DB2 crash. Not everytime DB2 will crash, but it happen several times. |
|
| Back to top |
|
|
Rama_Prayaga
Beginner
Joined: 20 May 2003
Posts: 45
Topics: 15
|
| Posted: Fri Jul 08, 2005 3:15 am Post subject: |
|
|
Hi Videlord,
You are absolutely right.This is what my DBA has also informed me.
Kolusu,
Regarding your questions,
| Quote: | | How is the "count" field incremented? Is it after every update/insert? |
Reply: count will be incremented after each update or insert.
| Quote: | Why can't it be a simple like this
IF count = 0 then
exec sql commit
move zero to count;
|
Reply: As I am incremeting counter after each update or Insert.I wanted to commit table after every 1000 records.
In the below code I used MOD Built-in function of Pl/1 which will give me the remainder ZERO whenever the count is exactly divisble by 1000.
| Code: | if Mod (count,1000) = 0 then
exec sql commit; |
Once again,Thanks for all your help.
Thanks
Rama Krishna Prayaga |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 08, 2005 6:04 am Post subject: |
|
|
Rama_Prayaga,
I had a typo in my earlier reply. My question was why use a MOD function when it can be done simply like below.
| Code: |
IF count = 1000 then
exec sql commit
move zero to count;
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Rama_Prayaga
Beginner
Joined: 20 May 2003
Posts: 45
Topics: 15
|
| Posted: Fri Jul 08, 2005 7:52 am Post subject: |
|
|
Hi Kolusu,
Actually I have to maintain the count for number of records that are updated or Inserted in my Program , so I cannot initialize it.
So had to use MOD function.
Regards,
Rama Krishna |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
