September 2006 Blog Posts
Like this
SELECT o.name, t.TABLE_NAME, c.text
FROM syscomments c
JOIN sysobjects o
ON c.id = o.id
JOIN INFORMATION_SCHEMA.Tables t
ON c.text LIKE '%'+t.TABLE_NAME+'%'
Every so often, someone asks, "How do I know who executed a SQL Statement against my database".
Well you can either have SQL Profiler running all the time (which can be very expensive), or you can use Lumingent's Log Explorer.
I have taken a different tack lately.
Any Access to a database I am supporting will be done ONLY Through stored procedures. OK, that's not "lately", but the part I've added is that the developers MUST call the sproc below. What this does is to log every stored procedure call. I now have statistics as to what's being called when, and how long...
A director came to me asking if a set based approach could be used to find the fallout for trades and receipts. Even though they handed the work off already. The developer used COBOL to compare the 2 file and did "spin-up" processing to match a trade to a receipt. Only probalem is that is totally arbitrary since the "key" was basicall generic and did not exactly marry the receipt to the trade. So their "fallout" was based on LILO. They considered everything else as a match.
What they really needed to do was identify target population that have "fallout" and...
Well, there really isn't anything that I know of that is like sp_depends for DB2 z/OS Version 7.2. Hopefully V8 will alot more features...but for Now you have to interogate the catalog. So this is how you do it....
SELECT DISTINCT NAME,DNAME,BNAME
FROM SYSIBM.SYSPACKDEP D
INNER JOIN SYSIBM.SYSPACKSTMT S
ON D.DCOLLID = S.COLLID AND D.DNAME = S.NAME
AND D.DCONTOKEN = S.CONTOKEN
WHERE BQUALIFIER = 'AXHRSPDA'
AND BNAME IN('POSITION_TREE')
ORDER BY NAME,DNAME,BNAME
;
And this new editor for posting is pretty neat...lots of features...like background color...and for anyone not familiar with the mainframe...that's what I...