Unlocking DB2 table/row locks

Knut Stolze · *nix forums Guru Joined: 28 Jul 2005 Posts: 755

DaveA wrote:

Keith · *nix forums addict Joined: 12 Mar 2005 Posts: 98

For a restore from an online backup image, one would still need to
supply all/any logs till the end of backup operation. DB2 will still
play thru these minimum logs even if there aren't any transactions.
WITHOUT ROLLING FORWARD will not help in this case.

Can you take an offline backup and restore?

BTW, Per V7.2 documentation (Administration Guide: Implementation),
error 1 treated as error 32 - "The user exit was terminated by the
user"

Keith Ponnapalli
adv_dba at yahoo dot com
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for Linux,
UNIX, and Windows
INFORMIX Certified Database Administrator

Knut Stolze wrote:

DaveA · *nix forums beginner Joined: 03 Apr 2006 Posts: 3

Knut,
Thanks for the suggestion. Ultimatly we need all the data, but I tried
your suggestion with the hope that at least that would give us some
data to get started, but I got error SQL2537N Roll-forward is required
following the Restore.

Frankly, I am somewhat surprised I have seen no postings on this
problem. What I am trying to do seems like a normal requirement
Certainly we do it all the time with other customers. I suspect this is
the first time though that we have encountered the use of the user
exit.

Knut Stolze · *nix forums Guru Joined: 28 Jul 2005 Posts: 755

Keith wrote:

apattin · *nix forums beginner Joined: 25 Apr 2005 Posts: 8

Thanks! I can do that.

Alejandrina

Phil Sherman · *nix forums Guru Wannabe Joined: 16 Jun 2005 Posts: 183

Since you needed a redirected restore, I assume that the disk
architecture/directory structure doesn't match the production system.

Did you make sure that the database log location, after restore, points
to the correct drive/path where you copied the logs? If you have to have
the user exit, you could write one that indicates the log is available
in the appropriate directory without actually doing anything.

Phil Sherman

DaveA wrote:

DaveA · *nix forums beginner Joined: 03 Apr 2006 Posts: 3

Tim,
Thankyou! The stop option was what finally resolved this for me.

Dave.

Liam Finnie · *nix forums beginner Joined: 25 Jan 2006 Posts: 11

p175 wrote:

Chris · *nix forums beginner Joined: 10 Nov 2005 Posts: 23

I've found this out using SQL in the past. First, you identify which
column(s) the constraint is failing on (it should tell you) and which
foreign table that is referencing (assuming the constraing is a foreign
key).

Then, SET INTEGRITY ... UNCHECKED. Then do a query like:

select * from table1 where col_with_problem not in (select distinct
parent_column from parent_table);

This should get you all of the rows that have the problem. You can
then take remedial action (deleting rows, adding rows into the parent,
or whatever else is appropriate), and then turn integrity off, and
reset the integrity with checking back on.

Note that if the tables are big, this can take a really long time,
though.

-Chris

Shashi Mannepalli · *nix forums addict Joined: 09 Sep 2005 Posts: 70

Check this ...it might help you

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0506chong/

cheers...
Shashi Mannepalli

apattin · *nix forums beginner Joined: 25 Apr 2005 Posts: 8

CLEVER!

BTW, I found out how to remove the totals from the rollup, in case
anyone is interested:

select primary_site , primary, morphology,
count(*) as COUNT from morphology_summary group by rollup
(primary_site,primary,morphology )
HAVING GROUPING(primary_site)=0 AND GROUPING(morphology)=0 and
grouping(primary)=0
order by primary_site , primary, morphology

the magic is "HAVING GROUPING(xxx)=0 " only selects the non-total rows.

Thanks!

Alejandrina

Another DB2 UDB DBA · *nix forums beginner Joined: 09 Feb 2005 Posts: 3

If you want to suppress totals from a GROUP BY ROLLUP, perhaps you
should use a different grouping method, such as GROUP BY GROUPING SETS,
or a straight GROUP BY(primary_site, primary, morphology)

Am I missing something?

technocrat · *nix forums beginner Joined: 21 Mar 2006 Posts: 35

you solution looks great and ideal for me...but i aint sure yet ...i ll
have to implement that and try...but in the meanwhile,...can you give
me an example (source code) of how u did that?
Like how u identify which colmns are failing..? and which table its
refering...i know when SET INTEGRITY fails..it throws something..lile
integrity failedfor "schema.table.foreigntableFK" but is ther a way i
can find out the table name...rather than catching the exception and do
"STRING" analysis on it....

Any help would be appreciated...
Thanks

technocrat · *nix forums beginner Joined: 21 Mar 2006 Posts: 35

Thanks gert ...I think I would try this first and then try the sql
version of it...if I could get some more info on it , i would
appreciate it.....

The thing i am wndering is...does for exception ....remove only
duplicates?? or remove rows that are violating the FK
constraint...becuase, I am not rteally worried about duplicates...but I
want to find the rows that are violating the FK Constraint...

Any help is appreciated..!

technocrat · *nix forums beginner Joined: 21 Mar 2006 Posts: 35

Thanks gert ...I think I would try this first and then try the sql
version of it...if I could get some more info on it , i would
appreciate it.....

The thing i am wndering is...does for exception ....remove only
duplicates?? or remove rows that are violating the FK
constraint...becuase, I am not rteally worried about duplicates...but I
want to find the rows that are violating the FK Constraint...

Any help is appreciated..!

Google