Catalog table that shows readable/updatable views in Db2 v11 NFM

Bharath Nunepalli

Catalog table that shows readable/updatable views in Db2 v11 NFM

Hi all,
I know how to find whether a view is just readable or not.
I'm wondering if there is any Db2 catalog table that has a value for readable/updatable views.

 

I haven't found anything like that in Db2 v11 NFM. Not sure if that's available in Db2 v12.

Please let me know if you find about that.

 

 

Bharath Nunepalli,

Senior DB2 DBA.

Michael Hannan

RE: Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Bharath Nunepalli)

In Reply to Bharath Nunepalli:

Hi all,
I know how to find whether a view is just readable or not.
I'm wondering if there is any Db2 catalog table that has a value for readable/updatable views.

 

I haven't found anything like that in Db2 v11 NFM. Not sure if that's available in Db2 v12.

Please let me know if you find about that.

 

 

Bharath Nunepalli,

Senior DB2 DBA.

I have not checked it, but how about UPDATEAUTH in SYSTABAUTH. I assume a row must exist for View with UPDATEAUTH='Y' if view is updateable.

Michael Hannan

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Roy Boxwell

Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Michael Hannan)
Docu:



UPDATEAUTH CHAR(1) NOT NULL

Whether the GRANTEE can update rows of the table or view:

blank

Privilege is not held

G

Privilege is held with the GRANT option

Y

Privilege is held without the GRANT option







Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, March 6, 2020 1:33 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Catalog table that shows readable/updatable views in Db2 v11 NFM



In Reply to Bharath Nunepalli:

Hi all,
I know how to find whether a view is just readable or not.
I'm wondering if there is any Db2 catalog table that has a value for readable/updatable views.



I haven't found anything like that in Db2 v11 NFM. Not sure if that's available in Db2 v12.

Please let me know if you find about that.





Bharath Nunepalli,

Senior DB2 DBA.

I have not checked it, but how about UPDATEAUTH in SYSTABAUTH. I assume a row must exist for View with UPDATEAUTH='Y' if view is updateable.

Michael Hannan



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Bharath Nunepalli

RE: Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Roy Boxwell)

Roy,

Checking UPDATEAUTH column helps only if the accesses are granted with due diligence.

In our shop, I found out that UPDATE access has been granted on read-only views also.

 

Bharath Nunepalli,

Senior DB2 DBA.

Jorg Lueke

RE: Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Bharath Nunepalli)

I didn't know you could grant futile access. Can you look just at the 'G' option for UPDATEAUTH?

 

TTNAME                                                                            INSERTAUTH
---------+---------+---------+---------+---------+---------+---------+---------+---------+---
VEMP                                                                              G         
VEMP2                                                                                       
VEMP2                                                                                       
VEMP2                                                                             Y         
DSNE610I NUMBER OF ROWS DISPLAYED IS 4                                                      
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                                 
---------+---------+---------+---------+---------+---------+---------+---------+---------+---
--GRANT INSERT ON VEMP2 TO PUBLIC;                                      00080008            
INSERT INTO VEMP2 VALUES('SPITZ MARK','ATHLETE',72)                     00090009            
---------+---------+---------+---------+---------+---------+---------+---------+---------+---
DSNT408I SQLCODE = -150, ERROR:  THE OBJECT OF THE INSERT, DELETE, OR UPDATE                
         STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR                
         TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED                

Michael Hannan

RE: Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Jorg Lueke)

Jorg,

I am not sure what you are showing us there. GRANT to public is commented out. Relevant? INSERTAUTH is granted to who? Are you saying that VIEW2 is a non updateable and non insertable view despite INSERTAUTH apparently granted, hence your word "futile"?

That would be a bit baffling.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

James Campbell

Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Jorg Lueke)
Consider an INSTEAD OF trigger on a non-updateable view. A grant might be futile when it
is made, but might not be futile when the update / delete / insert happens.

James Campbell


On 6 Mar 2020 at 10:43, Jorg Lueke wrote:

> I didn't know you could grant futile access. Can you look just at the 'G' option for UPDATEAUTH?
>  
> TTNAME                                                                            INSERTAUTH
> ---------+---------+---------+---------+---------+---------+---------+---------+---------+---
> VEMP                                                                              G         
> VEMP2                                                                                       
> VEMP2                                                                                       
> VEMP2                                                                             Y         
> DSNE610I NUMBER OF ROWS DISPLAYED IS 4                                                      
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                                 
> ---------+---------+---------+---------+---------+---------+---------+---------+---------+---
> --GRANT INSERT ON VEMP2 TO PUBLIC;                                      00080008            
> INSERT INTO VEMP2 VALUES('SPITZ MARK','ATHLETE',72)                     00090009            
> ---------+---------+---------+---------+---------+---------+---------+---------+---------+---
> DSNT408I SQLCODE = -150, ERROR:  THE OBJECT OF THE INSERT, DELETE, OR UPDATE                
>          STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR                
>          TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED                
>
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

J&#248;rn Thyssen

RE: Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Bharath Nunepalli)

SYSCOLUMNS.UPDATES could be interesting

In Reply to Bharath Nunepalli:

Hi all,
I know how to find whether a view is just readable or not.
I'm wondering if there is any Db2 catalog table that has a value for readable/updatable views.

 

I haven't found anything like that in Db2 v11 NFM. Not sure if that's available in Db2 v12.

Please let me know if you find about that.

 

 

Bharath Nunepalli,

Senior DB2 DBA.



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2020 IBM Champion.

Views are personal. 

Bharath Nunepalli

RE: Catalog table that shows readable/updatable views in Db2 v11 NFM
(in response to Jørn Thyssen)

SYSCOLUMNS.UPDATES looks useful.
I think we can say a view is updatable if SYSCOLUMNS.UPDATES is 'Y' for at least one of the columns of the view.

 

Bharath Nunepalli,

Senior DB2 DBA.