Methods for cleaning locks in XMETALOCKINFO table

bcarlson · Post by **bcarlson** » Mon Nov 24, 2008 12:57 pm

In a past posting, we were dealing with job locks that had no corresponding OS-level processes. Turns out the locks were logged in the XMETALOCKINFO table and needed to be removed.

See Unable to unlock jobs in v8 for details.

I am starting a new posting to deal directly with the topic of using a trigger (or some other method) of removing locks from the XMETALOCKINFO table.

The trigger basically does a delete whenever a lock record is inserted and there was reasonable concern over what is actually getting deleted.

Here is the trigger in question (from lstsaur, Thanks!):

Code: Select all

CREATE OR REPLACE TRIGGER CleanupAbandonedLocksTrg 
AFTER INSERT 
ON XMETA.XMETALOCKINFO 
BEGIN 
DELETE FROM XMETA.XMETALOCKINFO; 
END CleanupAbandonedLocksTrg;

I don't have permissions (I don't think) to get the DDL for the table. Can anyone tell me what the table looks like? I am wondering if there are any other columns that could help determine if a lock should be dropped or not (i.e. with a timestamp you could drop locks > 6 hours old?). Or could it be done by user? If I know my processes are gone, then write a program that can drop locks based on my userid only...

Thoughts anyone?

Brad.

Mike · Post by **Mike** » Mon Nov 24, 2008 1:22 pm

Suggest extreme caution...

Code: Select all

CREATE TABLE XMETALOCKINFO
(
	REPOSITORY_ID  VARCHAR(64)  NOT NULL,
	LOCKED_AT  DECIMAL(31,7)  NOT NULL,
	LOCKED_BY_USER  VARGRAPHIC(256)  NOT NULL,
	LOCKED_BY_SESSION  VARGRAPHIC(256)  NOT NULL,
	NEEDS_UPGRADE  SMALLINT  
)
;



ALTER TABLE XMETALOCKINFO
	ADD CONSTRAINT  XMETALOCKINFO_PK  PRIMARY KEY (REPOSITORY_ID)
;



CREATE INDEX SESSIONLOCK ON XMETALOCKINFO
(
	LOCKED_BY_SESSION  ASC
)
;

Mike

bcarlson · Post by **bcarlson** » Mon Nov 24, 2008 1:38 pm

I had one of our DBAs dump some data for this table. Does anyone know the format of the locked_at column? I assume this is some kind of timestamp (or seconds/ticks from some point in time):

select * from xmeta.xmetalockinfo with UR
;

REPOSITORY_ID LOCKED_AT LOCKED_BY_USER LOCKED_BY_SESSION NEEDS_UPGRADE
---------------------------------------------------------------- --------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------
c2e76d84.43058877.6409720a.11d1fdaa6ab.4cc8323a.b8d8a 1227513150943.0000000 kpawax 00056FD9-2E8E-4570-8680-A42AD361AA87 -
c2e76d84.43058877.69c8f255.11d1fd3f483.5951f23a.b7d0a 1227518595297.0000000 kpawax 00056FD9-2E8E-4570-8680-A42AD361AA87 -
c2e76d84.43058877.2e7f724b.11d1f99d894.1add322a.b61b0 1227535952074.0000000 kpawax 496BE352-AF2F-4213-AD57-CBF79DCC5547 -
c2e76d84.43058877.1ff0421c.11d4d189c46.74780250.3754 1227504529550.0000000 snoonx 5C821ECE-D6F5-4A6C-B0A5-A927A1703A81 -
c2e76d84.43058877.6511426b.11d4d18954b.2be90250.3706 1227504637734.0000000 snoonx 5C821ECE-D6F5-4A6C-B0A5-A927A1703A81 -
c2e76d84.43058877.43ca4237.11d4d1887c4.74780250.33a0 1227504639644.0000000 snoonx 5C821ECE-D6F5-4A6C-B0A5-A927A1703A81 -
c2e76d84.43058877.1db8b196.11d1d627248.1add322a.a6f26 1227535675265.0000000 schakrx 89B73424-345C-47FE-A0BD-05D11FC1DBFA -
c2e76d84.43058877.5093265.11d1d6456f3.4cc8323a.a7770 1227535873668.0000000 schakrx 89B73424-345C-47FE-A0BD-05D11FC1DBFA -
c2e76d84.43058877.1d77419b.11d4daf0a32.6bce4250.a6df 1227513619560.0000000 kpawax B2F98D97-68C9-4752-ADAD-169836145DB0 -

So I have a LOCKED_AT value of 1227513150943.0000000. What does this actually represent?

I am contemplating a trigger that is limited by LOCKED_BY and by LOCKED_AT if I can understand the timestamp format.

Brad.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 24, 2008 2:56 pm

I don't know, but LOCKED_AT looks like a timestamp to me. What does the table definition (you can see it in DB2 Control Center) tell you?

lstsaur · Post by **lstsaur** » Mon Nov 24, 2008 3:23 pm

Brad,
Since "what is actually getting deleted" is the concern, for Oracle, you can use either UTL_MAIL send you an email with deleted rows info. or us UTL_FILE to write deleted rows to a file. But don't know how to do it with DB2.

rvvrr · Post by **rvvrr** » Mon Nov 24, 2008 3:32 pm

ray.wurlod wrote:I don't know, but LOCKED_AT looks like a timestamp to me. What does the table definition (you can see it in DB2 Control Center) tell you? ...

Hopefully the below query might help associate repository ids to job names and projects. I am still working on this and have not come to a conclusion.
If the stored procedure is parameterized to accept Projectname and Job name as parameters, then the delete can be applied to records of an individual job on XMetaLockInfo table.
Please do Correct if I have missed out anything.

select
a._xmeta_repos_object_id_xmeta DSJobRepID
, a.name_xmeta JobName
, b.repository_id XMETARepID
, b.locked_by_user LockedBy
, b.locked_at LockedAt
from
DataStageX_xmetagen_DSJobDefxxxxxx a
,XMetaLockInfo b
where
a.DSNameSpace_xmeta='hostname:Projectname'
and a._xmeta_repos_object_id_xmeta=b.repository_id