• Rajesh A R

F1 for Mainframe

F1 for Mainframe

Tag Archives: query

DB2 CRUD Query – Create Read Update Delete

08 Monday Oct 2018

Posted by rajeshar in DB2

≈ Leave a comment

Tags

access, create, crud, DB2, DELETE, mainframe, program, query, read, spufi, UPDATE

Below query that is written against DB2 catalog table will be helpful to see what program has what level of access for a specific DB2 table that you are looking at.

SELECT TCREATOR AS CREATOR
, TTNAME AS TABLE
, GRANTEE AS GRANTEE
, SUBSTR(TTNAME,6,3) AS TBL
, VALUE(USR_USER_NAME,’ ‘) AS “USER NAME”
, VALUE(USR_DEL_IND,’ ‘) AS DEL_IND
, VALUE(USR_DEPT_ID,’ ‘) AS DEPT
, GRANTEETYPE AS TYPE
, SUM(CASE WHEN INSERTAUTH = ‘Y’ THEN 1 ELSE 0 END) *1 AS C
, SUM(CASE WHEN SELECTAUTH = ‘Y’ THEN 1 ELSE 0 END) *3 AS R
, SUM(CASE WHEN UPDATEAUTH = ‘Y’ THEN 1 ELSE 0 END) *5 AS U
, SUM(CASE WHEN DELETEAUTH = ‘Y’ THEN 1 ELSE 0 END) *7 AS D
, ((SUM(CASE WHEN INSERTAUTH = ‘Y’ THEN 1 ELSE 0 END) *1) +
(SUM(CASE WHEN SELECTAUTH = ‘Y’ THEN 1 ELSE 0 END) *3) +
(SUM(CASE WHEN UPDATEAUTH = ‘Y’ THEN 1 ELSE 0 END) *5) +
(SUM(CASE WHEN DELETEAUTH = ‘Y’ THEN 1 ELSE 0 END) *7)) AS TOT
FROM SYSIBM.SYSTABAUTH
WHERE LOCATION = ”
AND GRANTEETYPE = ‘P’
AND TCREATOR = ‘SCHEMA_NAME’
AND TTNAME LIKE ‘TABLE_NAME%’
GROUP BY TCREATOR
, TTNAME
, GRANTEE
, GRANTEETYPE
, USR_USER_NAME
, USR_DEL_IND
, USR_DEPT_ID
WITH UR;

Rate this:

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • September 2019
  • August 2019
  • October 2018
  • April 2018
  • March 2018
  • December 2017
  • October 2017
  • August 2017
  • July 2017
  • June 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • May 2016
  • March 2016
  • February 2016
  • January 2016
  • May 2015
  • September 2013
  • August 2013
  • July 2013
  • June 2013
  • May 2013
  • January 2013
  • December 2012
  • November 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • April 2011
  • March 2011
  • August 2009
  • June 2009
  • April 2009

Categories

  • Azure
  • CA7
  • CICS
  • COBOL
    • Keywords
  • DB2
  • EASYTRIEVE
  • FILEAID
  • FILEMAXX
  • IMS
  • ISPF
  • JCL
  • KNOW YOUR MAINFRAME
  • Others
  • REXX
  • SDSF
  • SORT
  • TSQL
  • VSAM

Meta

  • Register
  • Log in

Create a free website or blog at WordPress.com.