Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Unavailable resource - index during load

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Musab Ahmed

New User


Joined: 10 Dec 2012
Posts: 37
Location: India

PostPosted: Tue Oct 08, 2013 5:20 pm    Post subject: Unavailable resource - index during load
Reply with quote

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
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL

PostPosted: Tue Oct 08, 2013 9:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Musab Ahmed

New User


Joined: 10 Dec 2012
Posts: 37
Location: India

PostPosted: Wed Oct 09, 2013 1:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1868
Location: UK

PostPosted: Wed Oct 09, 2013 1:59 pm    Post subject:
Reply with quote

What did your DBA say - he probably has to do it anyway and you were already talking to him!
Back to top
View user's profile Send private message
Musab Ahmed

New User


Joined: 10 Dec 2012
Posts: 37
Location: India

PostPosted: Wed Oct 09, 2013 2:32 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL

PostPosted: Wed Oct 09, 2013 4:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Wed Oct 09, 2013 6:46 pm    Post subject: Reply to: Unavailable resource - index during load
Reply with quote

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
View user's profile Send private message
Musab Ahmed

New User


Joined: 10 Dec 2012
Posts: 37
Location: India

PostPosted: Thu Oct 10, 2013 1:02 pm    Post subject:
Reply with quote

Thanks All for your inputs..

Just one more question.

Should i run RUSTATS before binding all the packages?

Regards,
Musab Ahmed
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL

PostPosted: Fri Oct 11, 2013 12:39 am    Post subject:
Reply with quote

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
View user's profile Send private message
Joseph Reynolds

New User


Joined: 07 Oct 2013
Posts: 6
Location: United States

PostPosted: Fri Oct 11, 2013 1:44 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts Finding IMS resource blocker after se... Hooman24 IMS DB/DC 1 Wed Aug 16, 2017 9:17 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us