Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1738
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: 1714
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: 1738
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: 186
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: 1738
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 How to find a CICS resource used in C... Arunkumar Chandrasekaran CICS 8 Thu Sep 29, 2016 1:45 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Copybook structure in Load module Vai123 COBOL Programming 7 Fri Sep 16, 2016 8:29 pm
This topic is locked: you cannot edit posts or make replies. ERROR WHILE SUBMITTING LOAD JOB ANURADHA NEELAKANTAN JCL & VSAM 12 Thu Aug 25, 2016 11:50 am
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us