Isolation level of SPUFI

THOLKAPPIAN Chidambaram

Isolation level of SPUFI
Hi List,

I have a question on locking parameters of SPUFI. We have been using SPUFI
to access production data. We are advised to use WITH UR option. But even
then some of the batch jobs accessing the same tables are getting -911.

In the SPUFI defaults panel we give CS. If this is the isolation level used,
is there any benefit of using WITH UR option? Also what is the best way of
accessing production data with maximum concurrency?

our DB2 is v6r1

Thanks
Thol



James Kwan

Re: Isolation level of SPUFI
(in response to THOLKAPPIAN Chidambaram)
Thol,

Unless they have changed, the default is RR. Even with CS/UR option, if you
use any update/delete in your SPFUI, you will still have potential to
timeout other applications or vice versa. Also make sure the autocommit is
set to YES.
I personally don't think it is good idea to access production data with
SPUFI or QMF.

Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390

----- Original Message -----
From: "THOLKAPPIAN Chidambaram" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, May 29, 2002 12:04 PM
Subject: Isolation level of SPUFI


> Hi List,
>
> I have a question on locking parameters of SPUFI. We have been using SPUFI
> to access production data. We are advised to use WITH UR option. But even
> then some of the batch jobs accessing the same tables are getting -911.
>
> In the SPUFI defaults panel we give CS. If this is the isolation level
used,
> is there any benefit of using WITH UR option? Also what is the best way of
> accessing production data with maximum concurrency?
>
> our DB2 is v6r1
>
> Thanks
> Thol
>
>
>


>



Rob Crane

Re: Isolation level of SPUFI
(in response to James Kwan)
WITH UR defaults to WITH CS for INSERT, UPDATE and DELETE statements.

Best way to ensure concurrency is to have your batch processes
committing frequently.

-Rob

THOLKAPPIAN Chidambaram wrote:
>
> Hi List,
>
> I have a question on locking parameters of SPUFI. We have been using SPUFI
> to access production data. We are advised to use WITH UR option. But even
> then some of the batch jobs accessing the same tables are getting -911.
>
> In the SPUFI defaults panel we give CS. If this is the isolation level used,
> is there any benefit of using WITH UR option? Also what is the best way of
> accessing production data with maximum concurrency?
>
> our DB2 is v6r1
>
> Thanks
> Thol
>
>
>



Tim Lowe

Re: Isolation level of SPUFI
(in response to Rob Crane)
James,
I don't think it is fair to blame SPUFI or QMF for problems accessing
production data.
In my opinion, both QMF and SPUFI can (and should) be used against
production data without problems.
It is not the tool, but how it is used.
If you eliminate SPUFI and QMF, someone might create the same kinds of
problems with other tools.
We cannot eliminate all of the tools, and expect the business goals to be
accomplished.

We do need to establish good rules, hire good people, train them well and
trust them.
Otherwise, we are just trying to be overly protective of the data.
(If nobody can touch production data, then nobody can break it!)
And, that just leads to rules that people don't understand (and don't like)
and they want to move to other platforms with the false impression that the
platform is the problem.

Obviously, some client/server applications make this much more difficult.
But, that does not mean that we should give up, it just means that we need
to work harder on education.
We are talking about DB2 application developers and DB2 end-users that use
their own dynamic sql (what we used to call "power-users").
Can we establish good rules about what data they can access and how?
Can we hire good people, and train them?

First, I don't see any need to grant the Spufi RR plan to anyone.
Second, you can create some simple rules to encourage using WITH UR.

And, what do you do when the rules are broken (and they will be,
regardless of anything we do).

Thanks,
Tim



James Kwan

Re: Isolation level of SPUFI
(in response to Tim Lowe)
Tim,

When I meant was I don't encourage user level to use SPUFI and QMF. Even
for some junior DBA, I would be hesitated to give them production access.
Yes, it really depends on who is using it and if there is a good rule to do
it. One of my arguments for this is that it is easier to screw up the
production data if you provide them with 'ease of use' tools. Another
argument is it is harder to trace who has done what without any log analyzer
tools. Also if you have any application maintain RI, it will be almost
impossible to find out when the link is broken. I have worked in many shops
and production updating throught SPFUI/any tools is restricted to only
production DBAs. I don't know if your shop allows users to update the
production system.



Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390

----- Original Message -----
From: <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, May 29, 2002 1:53 PM
Subject: Re: Isolation level of SPUFI


> James,
> I don't think it is fair to blame SPUFI or QMF for problems accessing
> production data.
> In my opinion, both QMF and SPUFI can (and should) be used against
> production data without problems.
> It is not the tool, but how it is used.
> If you eliminate SPUFI and QMF, someone might create the same kinds of
> problems with other tools.
> We cannot eliminate all of the tools, and expect the business goals to be
> accomplished.
>
> We do need to establish good rules, hire good people, train them well and
> trust them.
> Otherwise, we are just trying to be overly protective of the data.
> (If nobody can touch production data, then nobody can break it!)
> And, that just leads to rules that people don't understand (and don't
like)
> and they want to move to other platforms with the false impression that
the
> platform is the problem.
>
> Obviously, some client/server applications make this much more difficult.
> But, that does not mean that we should give up, it just means that we need
> to work harder on education.
> We are talking about DB2 application developers and DB2 end-users that use
> their own dynamic sql (what we used to call "power-users").
> Can we establish good rules about what data they can access and how?
> Can we hire good people, and train them?
>
> First, I don't see any need to grant the Spufi RR plan to anyone.
> Second, you can create some simple rules to encourage using WITH UR.
>
> And, what do you do when the rules are broken (and they will be,
> regardless of anything we do).
>
> Thanks,
> Tim
>
>
>


>