Learning Visually with Examples [Electronic resources] نسخه متنی

Figure D.1. Sample buffer pool information

Figure D.2. The SYSCAT.BUFFERPOOLS view in the Control Center

[View full size image]

Figure D.3. Contents of the SYSCAT.BUFFERPOOLS view

Tables folder. All system catalog views (the SYSCAT views) and user-defined views are stored in the Views folder. Open the Views folder to display all the views. The contents of the selected folder in the left pane are displayed in the top right pane.

In the top right pane, click on the view you are interested in. The definition of the view is displayed in the bottom right pane of Figure D.2. In this example, it is the SYSCAT.BUFFERPOOLS view.

Double-click on the view name to display its contents (or right-click on the view name and select

Open ). Figure D.3 shows the contents of the SYSCAT.BUFFERPOOLS view.

Note that the information in Figure D.3 is the same as the output of the

SELECT * FROM SYSCAT.BUFFERPOOLS statement (see Figure D.1).

Example 2: Determining Which Table Spaces Use the Buffer Pool

To find out which table spaces use the buffer pool found in Example 1, you need to query the SYSCAT.TABLESPACES view. Use the

DESCRIBE TABLE command to display the columns defined in the view; then you can issue queries to display the contents of the columns you are really interested in. For example, the

DESCRIBE TABLE SYSCAT.TABLESPACES statement displays the output shown in Figure D.4.

In Figure D.4, the columns you really need are the TBSPACE and BUFFERPOOLID columns. Issue

SELECT TBSPACE, BUFFERPOOLID FROM SYSCAT.TABLESPACES 

to see the results shown in Figure D.5. You can see that there are four table spaces in the database, and all of them are using the buffer pool with an ID of 1. From Example 1, you know this buffer pool ID corresponds to the IBMDEFAULTBP buffer pool.

You can obtain the same information by displaying the contents of the SYSCAT.TABLESPACES view in the Control Center.

Example 3: Checking for Privileges and Authorization

A user with user ID

guest complains that he is not able to insert any rows into a table called SYLVIAQ.TABLE1. The first thing you need to know is whether the user

guest has the authority to insert into SYLVIAQ.TABLE1.

You are not sure which SYSCAT view contains the table privilege information. Therefore, you issue the

LIST TABLES FOR SCHEMA SYSCAT statement to display all the available SYSCAT views, and hope to see one that might give you the table privilege information.

You browse through the output and find a table called TABAUTH. This might be the table you are looking for. You issue

DESCRIBE TABLE SYSCAT.TABAUTH 

to display its contents. Figure D.6 shows the output.

Figure D.6 displays the contents of the SYSCAT.TABAUTH view. The view contains 13 columns: GRANTOR, GRANTEE, GRANTEETYPE…UPDATEAUTH. The DELETEAUTH column contains information about whether a user has DELETE privileges. Likewise, the INSERTAUTH column contains information about whether a user has INSERT privileges. You issue the following statement to find out what privileges the user

guest has on SYLVIAQ.TABLE1:

SELECT * FROM SYSCAT.TABAUTH WHERE GRANTEE = 'GUEST' 

As you can see in Figure D.7, the INSERTAUTH column has a value

N . This means that the user

guest does not have insert privileges; therefore, he cannot insert any rows. Note the SELECTAUTH column has a value

Y . This means the user has SELECT privileges for the table SYLVIAQ.TABLE1.