select update statements for syspackstmt

A. J. Zobjeck

select update statements for syspackstmt
I'm trying to get the table, package and then report if it's a select or
update statement. I see that the STMT column is only 256 bytes which
means a large statement may not be reported. I there a better way to
find this?



SELECT

A.NAME AS TABLENAME,

B.DNAME AS PACKAGE,

CASE WHEN C.STMT LIKE '%SELECT%' THEN 'S SELECT'

WHEN C.STMT LIKE '%DELETE%' THEN 'D UPDATE'

WHEN C.STMT LIKE '% UPDATE %' THEN 'U UPDATE'

WHEN C.STMT LIKE '%INSERT%' THEN 'I UPDATE'

END

FROM SYSIBM.SYSTABLES A,

SYSIBM.SYSPACKDEP B,

SYSIBM.SYSPACKSTMT C

WHERE

A.NAME = B.BNAME

AND ((B.DNAME = C.NAME AND C.STMT LIKE '%SELECT%')

OR (B.DNAME = C.NAME AND C.STMT LIKE '%DELETE%')

OR (B.DNAME = C.NAME AND C.STMT LIKE '% UPDATE %'))

AND A.NAME LIKE 'T__+_%' ESCAPE '+'

AND A.TYPE = 'T'

ORDER BY A.NAME ASC

WITH UR;

COMMIT;





> -----------------------------------------------------------------
> ----------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG
> DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins
> can be reached at [login to unmask email] Find out the
> latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Jardine

Re: select update statements for syspackstmt
(in response to A. J. Zobjeck)
You can use something like this...

SELECT *
FROM SYSIBM.SYSTABAUTH
WHERE (DELETEAUTH = 'Y'
OR INSERTAUTH = 'Y'
OR UPDATEAUTH = 'Y' )
AND GRANTEETYPE = 'P'
AND TTNAME = 'xxxx'
WITH UR;

Larry Jardine
Production DBA


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Zobjeck, A. J.
Sent: Tuesday, December 12, 2006 11:22 AM
To: [login to unmask email]
Subject: [DB2-L] select update statements for syspackstmt



I'm trying to get the table, package and then report if it's a select or
update statement. I see that the STMT column is only 256 bytes which
means a large statement may not be reported. I there a better way to
find this?



SELECT

A.NAME AS TABLENAME,

B.DNAME AS PACKAGE,

CASE WHEN C.STMT LIKE '%SELECT%' THEN 'S SELECT'

WHEN C.STMT LIKE '%DELETE%' THEN 'D UPDATE'

WHEN C.STMT LIKE '% UPDATE %' THEN 'U UPDATE'

WHEN C.STMT LIKE '%INSERT%' THEN 'I UPDATE'

END

FROM SYSIBM.SYSTABLES A,

SYSIBM.SYSPACKDEP B,

SYSIBM.SYSPACKSTMT C

WHERE

A.NAME = B.BNAME

AND ((B.DNAME = C.NAME AND C.STMT LIKE '%SELECT%')

OR (B.DNAME = C.NAME AND C.STMT LIKE '%DELETE%')

OR (B.DNAME = C.NAME AND C.STMT LIKE '% UPDATE %'))

AND A.NAME LIKE 'T__+_%' ESCAPE '+'

AND A.TYPE = 'T'

ORDER BY A.NAME ASC

WITH UR;

COMMIT;





> -----------------------------------------------------------------
> ----------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG
> DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins
> can be reached at [login to unmask email] Find out the
> latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



-----------------------------------------
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: select update statements for syspackstmt
(in response to Larry Jardine)
I like this statement similar to the one suggested by Larry



SELECT DISTINCT TAUT.TTNAME AS Table

, TAUT.GRANTEE As Package

, CASE WHEN TAUT.INSERTAUTH = 'Y' THEN 'C' ELSE '.' END As C

, CASE WHEN TAUT.SELECTAUTH = 'Y' THEN 'R' ELSE '.' END As R

, CASE WHEN TAUT.UPDATEAUTH = 'Y' THEN 'U' ELSE '.' END As U

, CASE WHEN TAUT.DELETEAUTH = 'Y' THEN 'D' ELSE '.' END As D

FROM SYSIBM.SYSTABAUTH TAUT

WHERE

TAUT.TCREATOR = iQUALIFIER_NM

AND TAUT.TTNAME LIKE iTABLE_NM

AND TAUT.GRANTEETYPE = 'P'

AND TAUT.GRANTEE LIKE iPACK_NM

ORDER BY C,R,U,D,Table,Package

WITH UR





________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jardine, Lawrence J
Sent: Tuesday, December 12, 2006 9:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] select update statements for syspackstmt



You can use something like this...



SELECT *
FROM SYSIBM.SYSTABAUTH
WHERE (DELETEAUTH = 'Y'
OR INSERTAUTH = 'Y'
OR UPDATEAUTH = 'Y' )
AND GRANTEETYPE = 'P'
AND TTNAME = 'xxxx'
WITH UR;



Larry Jardine

Production DBA



This e-mail may contain confidential or privileged
information. If you think you have received this e-mail in error,
please advise the sender by reply e-mail and then delete this
e-mail immediately. Thank you. Aetna
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm