Sending Locking Alert

Question

Sending Locking Alert

Bruin

SSCrazy

Points: 2058
More actions
September 28, 2021 at 2:34 pm

#3934145
I have 2 tables the first one holds the dbwait information(locks), and the second table holds the db sessions related to the locks. I'm in build block stage where I have the files loading to SQL, and will make updates to the Table Defs(PK,Index) after some testing. Just trying to work thru the logic of an automated email based upon the data. I first need to read the dbwait table to see if I have any locked records (lockmethod) will be 'Active'. Then I need to check who it's blocking (lockmethod) will be 'Begin', and Lockrcd will be the same. After I find who is locking who I need to read the dbsession table and relate the txnnbr field of the 2 tables. Then I would like to send an EMAIL to the head of the lock chain(dbwait), and tell them which users they have locked.
Email sample:
From: lockalert@xxx.com
To: --> qaduser from the session table@xxx.com
Subject: Locking records
Body:
(qaduser from the session table) you are currently holding locks in pgm(field qadmenu) and are blocking the following users:
-- list on users blocked --
(qaduser from the session table) ---> locked records from dbwait table (lockmethod) will be 'Begin'

Thanks
```
CREATE TABLE [dbo].[db_wait](
[dbname] [varchar](255) NULL,
[txnnbr] [varchar](255) NULL,
[osuser] [varchar](255) NULL,
[locaktype] [varchar](255) NULL,
[locktbl] [varchar](255) NULL,
[lockrcd] [varchar](255) NULL,
[lockmethod] [varchar](255) NULL
) ON [PRIMARY]
GO

Insert Into db_wait
Values('dbprod','79','npr_con','rec','541','4354','X L Active')
go
Insert Into db_wait
Values('dbprod','51','npr_con','rec','541','4354','X Q H Begin')
go



/****** Object:  Table [dbo].[db_session]    Script Date: 9/28/2021 9:56:00 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[db_session](
[dbsession] [varchar](255) NULL,
[qaduser] [varchar](255) NULL,
[osuser] [varchar](255) NULL,
[dbtask] [varchar](255) NULL,
[ptsnbr] [varchar](255) NULL,
[qadpgm] [varchar](255) NULL,
[qadmenu] [varchar](255) NULL,
[tnxnbr] [varchar](255) NULL,
[ostask] [varchar](255) NULL,
[locktime] [varchar](255) NULL
) ON [PRIMARY]
GO

Insert Into db_session
Values('WEB',Usera','npr_con','3227','pts/6','pgma','4.4.4','51','27480','10:53:27')
go
Insert Into db_session
Values('WEB',Userb','npr_con','3228','pts/8','pgmb','4.4.4','79','27481','10:55:27')
go
```

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

I can't see a question in your post. Are you blogging?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bruin SSCrazy Points: 2058 More actions · Answer 2

My question is how do I create the automated email based upon request.

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Automated e-mails can be generated from SQL Agent jobs, SSIS packages and using various other technologies.

I assume that's not the answer you're after, but it does answer your question, bearing in mind that you have not defined what 'request' is.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bruin SSCrazy Points: 2058 More actions · Answer 4

Looking for the SP logic that joins by txnnbr the 2 tables and produces the logic to determine which user is doing the locking and email with criteria provided in early posts.

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 5

Bruin

SSCrazy

Points: 2058

September 29, 2021 at 10:59 am

#3934613

any examples of how to make this work?

Thx.

Bruin SSCrazy Points: 2058 More actions · Answer 6

How can I send the alert (email) to the user(qad_user from session table) where db_wait table has LockMethod = 'Active' . I want to send the screen that they have active (Qadmenu).

Thx.