View previous topic :: View next topic
|
Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi,
I am trying to load DB2 tables in a database.
The job is getting abended, this is the error.
Code: |
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN 4:05:24
ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TYPE
OF RESOURCE 00000D01, AND RESOURCE NAME 00000260.00001635 4:05:24
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE 4:05:24
DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR 4:05:24
DSNT416I SQLERRD = -190 13172746 13172878 13226969 -1026420731 4:05:24
536870912 SQL DIAGNOSTIC INFORMATION 4:05:24
DSNT416I SQLERRD = X'FFFFFF42' X'00C9000A' X'00C9008E' 4:05:24
X'00C9D3D9' X'C2D21005' X'20000000' SQL DIAGNOSTIC 4:05:24
INFORMATION 4:05:24
Collection of TS - VTGDATAW.TACCDJ - Incomplete 4:05:24
Collection of X2 - VTGDATAW.IACCDJ1 - Incomplete 4:05:24
Collection of X2 - VTGDATAW.IACCDJ2 - Incomplete 4:05:24
PUT0350I - PDA Processing Complete |
These are the things i checked.
The type of resource : 00000D01 (DBID/OBID)
None of the tablespace are in pending or in any restricted states.
I also have checked, is any utiltity holding any of the DB objects. i found none.
I don't know how to continue from here.
Tell me how to proceed.
Sushanth |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi,
I have found name of the resource, which is responsible
RESOURCE NAME 00000260.00001635(RATS_STATS_1105)
I found it using this query,
Code: |
SELECT *
FROM SYSIBM.SYSTABLES
WHERE DBID = 260 AND OBID = 1635; |
Sushanth |
|
Back to top |
|
|
nareshkp
New User
Joined: 26 Sep 2007 Posts: 28 Location: Bangalore
|
|
|
|
Which utility you are using to load the table? |
|
Back to top |
|
|
nareshkp
New User
Joined: 26 Sep 2007 Posts: 28 Location: Bangalore
|
|
|
|
I mean IBM/BMC/Platinum utility? |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Platinum FASTLOAD, naresh. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
hello folks,
From the RESOURCE NAME 00000260.00001635
I found out the following details
DB name : PTDB
Table NAME : RATS_STATS_1105
TS name : PTTSRTS7
I checked, is there any locks on the DB & TS. This is the result.
Code: |
DSNT360I # ***********************************
DSNT361I # * DISPLAY DATABASE SUMMARY
* GLOBAL LOCKS
DSNT360I # ***********************************
DSNT362I # DATABASE = PTDB STATUS = RW
DBD LENGTH = 561272
DSNT397I #
NAME TYPE PART STATUS CONNID CORRID LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
PTTSRTS7 TS RW DB2CALL MDBV040L H-IX,S,C
PTTSRTS7 TS RW DB2CALL PDL010 H-IX,S,C
1635 TB DB2CALL PDL010 H-X,T,C
******* DISPLAY OF DATABASE PTDB ENDED **********************
DSN9022I # DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION |
Here, MDBV040L is my load job, again i restarted it. The job is not going forward the CPU% of the JOB in spool is 0.00.
Any ideas anyone......
Let me know....
Sushanth |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hello Guyz,
This is how the story goes, continued from the previous my post.
I just executed a count query on the table RATS_STATS_1105
It gave me the same error, which i got in the load job.
I came to know there is REPAIR utility for the DBID. I also came to know about the REPAIR TEST DBD, which will tell about the inconsistency in DBID in catalog & directory. I prepared a REPAIR TEST job & executed on the database PTDB. There were a lot of consistency problems.
I thought that was the reason for my job failing.
After sometime, i again executed the load job, it ran. It is still running successfully. I again ran the REPAIR TEST DBD, there were still errors, i don't understand. How the job is running. So, i check the locks
-DISPLAY DATABASE (PTDB) SPACENAM(PTTSRTS7) LOCKS
Code: |
DSNT360I # ***********************************
DSNT361I # * DISPLAY DATABASE SUMMARY
* GLOBAL LOCKS
DSNT360I # ***********************************
DSNT362I # DATABASE = PTDB STATUS = RW
DBD LENGTH = 561272
DSNT397I #
NAME TYPE PART STATUS CONNID CORRID LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
PTTSRTS7 TS RW
******* DISPLAY OF DATABASE PTDB ENDED **********************
DSN9022I # DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION |
Compare this with my previous post.
There is "1635" missing. I have no idea what is that 1635.
Anyhow, the LOAD job is running. Problem REsolved.
My question is how ?
If anybody can formulate an explanation for this. That would be helpfull...
Code: |
Two Things that will not let you SLEEP peacefully,
----------------------------------------------------------
1. Love
2. Questions
|
Sushanth |
|
Back to top |
|
|
John K
New User
Joined: 27 Aug 2008 Posts: 3 Location: St. Louis, MO
|
|
|
|
The SQLCODE = -911 is a timeout on a deadlock. Something else was accessing/using the table at the time you were trying to load it; either intentional or unintentional. |
|
Back to top |
|
|
|