|
View previous topic :: View next topic
|
| Author |
Message |
Musab Ahmed
New User
Joined: 10 Dec 2012
Posts: 37
Location: India
|
|
|
|
Hi,
I used to work in Model204 database and now i am new to DB2.
There is a load job in production which loads around 1,000,000 rows everday into a table. The jobs abends in the SORTBLD phase frequently with the below abend message.
Load statement:
LOAD DATA
REPLACE COPYDDN (COPY01)
STATISTICS TABLE (ALL) INDEX (ALL) UPDATE ALL
LOG NO
SORTKEYS 900000 SORTDEVT SYSDA
DISCARDS 0
INTO TABLE DB2FS001.TFSA092_UPC_HIER
(..........)
Abend message:
SYSPRINT error message:
DSNU342I >DB2L 278 22:40:04.14 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E403322', INDEX = DB2FS001.XFSA092B
DSNT500I 278 22:40:04.17 DSNUGSAT - RESOURCE UNAVAILABLE
REASON 00D70014
TYPE 00000220
NAME DB2L.DSNDBC.BFSA001.XFSA092B.I0001.A001
DSNU017I 278 22:40:04.17 DSNUGSAT - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40322', CAUSE=X'0000000A'
DFSORT error message:
ICE185A 0 AN S13E ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT
Job error message: S04E
My initial analysis,
1. The dataset DB2L.DSNDBC.BFSA001.XFSA092B.I0001.A001 is available and not migrated.
2. I thought the index size - Primary quantity & secondary quantity are too low. But a strange thing happened. I just restarted the job without running any Reorg or performing any modification to the database and the job ran fine. This has been happening a few times everday. I used to restart the job 4 to 5 times and it would run fine the final time.
So i came to a conclusion that the index size is not a problem because if so , no matter how many times i restarted the job would abend every time.
What i think is the problem is with sort work space. I suppose if i allocate enough sort work datasets instead of dynamic allocation using SORTKEYS SORTDEVT would help to resolve this.
As i am new to DB2, could any of you tell me what you think the problem might be?
Thanks in advance.
Musab Ahmed |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| The reason code X'00D70014' in the DSNT500I message indicates that the index space data set could not be extended. Have you checked that there is space on the volume, and that it has not run of extents? |
|
| Back to top |
|
|
Musab Ahmed
New User
Joined: 10 Dec 2012
Posts: 37
Location: India
|
|
|
|
I consulted with my DBA who has confirmed that the indexspace is running out of extents.
To resolve, should i drop teh index and then create it with more PQTY,SECQTY, and then build it?
Or is there any other way to increase the index space?
I use DB2 v9.1. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| What did your DBA say - he probably has to do it anyway and you were already talking to him! |
|
| Back to top |
|
|
Musab Ahmed
New User
Joined: 10 Dec 2012
Posts: 37
Location: India
|
|
|
|
DBA is not of much use here. He just asked me to increase the index space.
I have to do this.
"To resolve, should i drop the index and then create it with more PQTY,SECQTY, and then build it? " - Is this a correct approach? |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Well, I'm not a DBA either (although I've occasionally played one), but this seems like a reasonable plan to me, although I think that there are one or two senior people on this board who will know better than I. If I am not mistaken, though, this will require re-binding all packages referencing the table that this index is on. |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi,
| Code: |
| "To resolve, should i drop the index and then create it with more PQTY,SECQTY, and then build it? " - Is this a correct approach? |
Instead of providing a user defined value for PRIQTY and SECQTY, you can use -1 as the value. Let DB2 will decide the allocation of your dataset.
Try this.
This may help you
Regards
Raghunathan |
|
| Back to top |
|
|
Musab Ahmed
New User
Joined: 10 Dec 2012
Posts: 37
Location: India
|
|
|
|
Thanks All for your inputs..
Just one more question.
Should i run RUSTATS before binding all the packages?
Regards,
Musab Ahmed |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Musab Ahmed wrote: |
Just one more question.
Should i run RUSTATS before binding all the packages? |
Yes; IIRC, dropping and recreating the index will invalidate the previous stats; in any case, it won't hurt. |
|
| Back to top |
|
|
Joseph Reynolds
New User
Joined: 07 Oct 2013
Posts: 6
Location: United States
|
|
|
|
| Quote: |
| "To resolve, should i drop the index and then create it with more PQTY,SECQTY, and then build it? " - Is this a correct approach? |
CREATE does the "..build it" piece.
DROP/CREATE only if the definition of the index cannot be changed with ALTER and REORG.
There are consequences to the drop/create approach:
1. Upon DROP, packages (access paths) previously relying on that index will be marked for incremental rebind. If an explicit BIND is not performed first, the next use will cause a new access path to be created. All future uses of the package will use the access path built during incremental rebind or BIND, whichever comes first. The access path from incremental rebind if the index has yet to be recreated/runstats may not be very good.
2. Creating the index requires reading the underlying table (space) so there is that to consider.
3. Don't forget runstats.
4. Don't forget to find and bind the packages that will go invalidated by #1 - before you DROP. Incremental rebind would take care of a missed bind, but the first use will take longer and lock catalog tables. If first use is time-sensitive, that may be an issue. Also, incremental rebind without the index won't help you find which packages don't use the index. Also, incremental rebind doesn't repopulate plan_table, so that will be out of sync.
Instead, ALTER the priqty/secqty and REORG the index. That will redefine the underlying VSAM to get you more space.
Run RUNSTATS (primarily for the benefit of dynamic SQL and new binds).
Prior to BIND, find the packages that use the index and its table, and also know what other tables/indexes those packages use. Your BIND will bind all statements in the package so access paths to other objects unrelated to the index in question can be affected.
If you are using DB2 10, EXPLAIN(ONLY) to see if the access path changes before you do the BIND to change the access path.
Good luck! |
|
| Back to top |
|
|
|
|
