MVSFORUMS.com

Sarangadhar

hi,
I am trying to execute some INSERTS/UPDATES on a table from a spufi. Since there are nearly million rows, and there is not COMMIT in between, the query fails saying "Unsuccessful execution caused by an unavailable resource". I guess this is because of no COMMIT in between.

Any way to commit in Spufi or QMF in a query, after some number of rows specified?
_________________
Thanks

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Your guess might not be true!! Why don't you post us the error message including the name and type of the unavailable ressoure?

A missing commit could harm all other processes in DB2, but not yours.

regards
Christian

Sarangadhar · Posted: Tue Nov 20, 2007 1:08 pm Post subject:

I feel it right because when I tried to do for whole table, it failed. Then I did for first couple of members by giving a range in where caluse and it worked. Hence I thought of doing it by range. However, I have a member where mroe than 10K are present. This time it is failing again with same message. Hence, this should be because of intermediate commit.

here is the entire error message:

jsharon1248 · Posted: Tue Nov 20, 2007 4:26 pm Post subject:

You're exceeding the max number of locks allowed at your installation. Your first option is to split the SQL further. I hesitate to suggest the next option, but you could LOCK the table. Probably not that great of an idea, but it is an option. I'd work with the first option if at all possible.

One more thought. I assume you're using an INSERT with a SELECT. If you're not already doing it, try changing the isolation level on the SELECT. The default is RR which will hold locks on the SELECT table name much longer than RS and CS.

Sarangadhar · Posted: Tue Nov 20, 2007 6:10 pm Post subject:

Hi Frank,
I am doing UPDATE, not INSERT.

Please see first post of this (mine) : I am using this update
http://www.mvsforums.com/helpboards/viewtopic.php?t=9214
_________________
Thanks

Sarangadhar · Posted: Tue Nov 20, 2007 6:13 pm Post subject:

This si the query: Please ignore the previous post. i think it is unnecessary to look at the other post..

UPDATE TAB2 B
SET IND = (
SELECT A.IND FROM TAB1 A
WHERE A.SNUM = B.SNUM
)
_________________
Thanks

jsharon1248 · Posted: Wed Nov 21, 2007 9:08 am Post subject:

If you haven't changed the default isolation level for SPUFI, you should try that. Use option 5, Change Defaults, and then change the Isolation Level to CS. The default is RR which holds more locks longer than CS.

If that doesn't resolve the problem, add a WHERE clause for TAB2.

One quick question. Is the D6744DBT.TS674401 tablespace the source or the target?

Sarangadhar · Posted: Fri Nov 30, 2007 5:39 pm Post subject:

Jsharon,
its already CS, not RR.

I tried using predicates in TAB2 to the lowest level possible; still there are more rows for a lowest level predicate. Hence getting the same problem. (the lowest predicate results 10K rows)

and D6744DBT.TS674401 is the source table's tablespace. Now I wonder why should I get the problem with source table. Any idea? I am just reading that table.
_________________
Thanks

jsharon1248 · Posted: Mon Dec 03, 2007 4:20 pm Post subject:

I wouldn't expect that updating 10K rows would trigger this error. You're more likely to get the 00C90096 error on the source table when using isolation RS and RR because the locks are held for the duration of the UPDATE. I'd check one more time that you're using CS.

If you can utilize the LOCK TABLE on the target, that will reduce the total number of locks and might get you through this.

Just a thought at large. I don't think I would recommend using SPUFI to update over 1 million rows. The time you're spending trying to get SPUFI to complete this task is probably more than just coding this in a procedural language using a cursor. If it's an existing process, it has grown large enough that the design should be re-evaluated.