query not going away!!!! help!!

There's this query
select * from tab1 order by column1,column2;

and the table is 1.7 billion rows . The user says he has cancelled it
(a java based db tool), and I am trying to do this


bcut:~> db2 "force application(22511)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective
immediately.


bcut:~> db2 "force application(22511)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective
immediately.


It simply isn't going away. Its using 183 million is the cost, using
loads of resources. It's a pain now.

Also, I've asked my lead dba to implement workload management, and he
hasn't done it. I got a redbook that explains this in
detail. I don't have the time or patience to read it.
Any concise , to the point guide?

I am using db2 version 9.5
DB21085I Instance "bculinp" uses "64" bits and DB2 code release
"SQL09053"
with level identifier "06040107".
Informational tokens are "DB2 v9.5.0.3", "s081210", "MI00269", and Fix
Pack
"3a".
Product is installed at "/opt/IBM/dwe/db2/V9.5".

On Sep 9, 9:06*am, Arun Srinivasan wrote:
> There's this query
> select * from tab1 order by column1,column2;
>
> and the table is 1.7 billion rows . The user says he has cancelled it
> (a java based db tool), and I am trying to do this
>
> bcut:~> db2 "force application(22511)"
> DB20000I *The FORCE APPLICATION command completed successfully.
> DB21024I *This command is asynchronous and may not be effective
> immediately.
>
> bcut:~> db2 "force application(22511)"
> DB20000I *The FORCE APPLICATION command completed successfully.
> DB21024I *This command is asynchronous and may not be effective
> immediately.
>
> It simply isn't going away. Its using 183 million is the cost, using
> loads of resources. It's a pain now.
>
> Also, I've asked my lead dba to implement workload management, and he
> hasn't done it. I got a redbook that explains this in
> detail. I don't have the time or patience to read it.
> Any concise , to the point guide?
>
> I am using db2 version 9.5
> DB21085I *Instance "bculinp" uses "64" bits and DB2 code release
> "SQL09053"
> with level identifier "06040107".
> Informational tokens are "DB2 v9.5.0.3", "s081210", "MI00269", and Fix
> Pack
> "3a".
> Product is installed at "/opt/IBM/dwe/db2/V9.5".

Is this a Straight Forward Select statement or any HASH JOIN's ? If
there are any HASH JOINs then that query cannot be forced. U have to
upgrade to FP4

Cheers
Shashi Mannepalli

On Sep 9, 10:56*pm, whatever wrote:
> On Sep 9, 9:06*am, Arun Srinivasan wrote:
>
>
>
>
>
> > There's this query
> > select * from tab1 order by column1,column2;
>
> > and the table is 1.7 billion rows . The user says he has cancelled it
> > (a java based db tool), and I am trying to do this
>
> > bcut:~> db2 "force application(22511)"
> > DB20000I *The FORCE APPLICATION command completed successfully.
> > DB21024I *This command is asynchronous and may not be effective
> > immediately.
>
> > bcut:~> db2 "force application(22511)"
> > DB20000I *The FORCE APPLICATION command completed successfully.
> > DB21024I *This command is asynchronous and may not be effective
> > immediately.
>
> > It simply isn't going away. Its using 183 million is the cost, using
> > loads of resources. It's a pain now.
>
> > Also, I've asked my lead dba to implement workload management, and he
> > hasn't done it. I got a redbook that explains this in
> > detail. I don't have the time or patience to read it.
> > Any concise , to the point guide?
>
> > I am using db2 version 9.5
> > DB21085I *Instance "bculinp" uses "64" bits and DB2 code release
> > "SQL09053"
> > with level identifier "06040107".
> > Informational tokens are "DB2 v9.5.0.3", "s081210", "MI00269", and Fix
> > Pack
> > "3a".
> > Product is installed at "/opt/IBM/dwe/db2/V9.5".
>
> Is this a Straight Forward Select statement or any HASH JOIN's ? *If
> there are any HASH JOINs then that query cannot be forced. U have to
> upgrade to FP4
>
> Cheers
> Shashi Mannepalli

This is straight forward order by query. IT was hung in 10th node. We
had to use a dsh - to all nodes to bring this down. wonder why doing
this in admin node alone can't do the job.
Anyway, any help with concise text on workload management would be
appreciated.

Thanks
arun

On Sep 10, 7:30*am, Arun Srinivasan wrote:
> On Sep 9, 10:56*pm, whatever wrote:
>
>
>
>
>
> > On Sep 9, 9:06*am, Arun Srinivasan wrote:
>
> > > There's this query
> > > select * from tab1 order by column1,column2;
>
> > > and the table is 1.7 billion rows . The user says he has cancelled it
> > > (a java based db tool), and I am trying to do this
>
> > > bcut:~> db2 "force application(22511)"
> > > DB20000I *The FORCE APPLICATION command completed successfully.
> > > DB21024I *This command is asynchronous and may not be effective
> > > immediately.
>
> > > bcut:~> db2 "force application(22511)"
> > > DB20000I *The FORCE APPLICATION command completed successfully.
> > > DB21024I *This command is asynchronous and may not be effective
> > > immediately.
>
> > > It simply isn't going away. Its using 183 million is the cost, using
> > > loads of resources. It's a pain now.
>
> > > Also, I've asked my lead dba to implement workload management, and he
> > > hasn't done it. I got a redbook that explains this in
> > > detail. I don't have the time or patience to read it.
> > > Any concise , to the point guide?
>
> > > I am using db2 version 9.5
> > > DB21085I *Instance "bculinp" uses "64" bits and DB2 code release
> > > "SQL09053"
> > > with level identifier "06040107".
> > > Informational tokens are "DB2 v9.5.0.3", "s081210", "MI00269", and Fix
> > > Pack
> > > "3a".
> > > Product is installed at "/opt/IBM/dwe/db2/V9.5".
>
> > Is this a Straight Forward Select statement or any HASH JOIN's ? *If
> > there are any HASH JOINs then that query cannot be forced. U have to
> > upgrade to FP4
>
> > Cheers
> > Shashi Mannepalli
>
> This is straight forward order by query. IT was hung in 10th node. We
> had to use a dsh - to all nodes to bring this down. wonder why doing
> this in admin node alone can't do the job.
> Anyway, any help with concise text on workload management would be
> appreciated.
>
> Thanks
> arun- Hide quoted text -
>
> - Show quoted text -



http://www.ibm.com/developerworks/da...AGX11&S_CMP=LP