Using the default object lists on OS/390

For a complete list of the views provided by QMF, refer to Appendix B. QMF provides the following default views and automatically assigns them to the user Q during installation into DB2 for OS/390 databases:

QMF also provides SQL default views that you might need in a remote unit of work environment:

The view Q.DSQEC__TABS__LDB2 selects only the list of tables and views from the current location in DB2 for OS/390, and workstation or iSeries database servers. Figure 93 shows the view provided for DB2 for OS/390.

Figure 93. Default view that provides a list of tables for the LIST command (OS/390)

CREATE VIEW Q.DSQEC__TABS__LDB2
    (OWNER,TNAME,TYPE,SUBTYPE,MODEL,RESTRICTED,REMARKS,
     CREATED,MODIFIED,LAST_USED,LABEL,LOCATION,OWNER__AT__LOCATION,
     NAME__AT__LOCATION)
  AS SELECT DISTINCT
    CREATOR,NAME,'TABLE',TYPE,' ',' ',REMARKS,' ',' ',' ',
    LABEL,LOCATION,TBCREATOR,TBNAME
    FROM SYSIBM.SYSTABLES, SYSIBM.SYSTABAUTH
    WHERE CREATOR = TCREATOR AND NAME=TTNAME AND GRANTEETYPE = ' ' AND
         GRANTEE IN (USER,'PUBLIC',CURRENT SQLID,'PUBLIC*')

To use a view you have created (for example, QMFADM.LOCAL__DB2__TABLES) and override the default view, issue a command like this one:

The view Q.DSQEC__TABS__RDB2 selects only the list of tables and views in a remote DB2 location accessed through a three-part name or the LOCATION option of LIST. The user's current location must be DB2 OS/390.

Figure 94. Default view that provides a list of tables for the LIST command (OS/390)

CREATE VIEW Q.DSQEC__TABS__RDB2
    (OWNER,TNAME,TYPE,SUBTYPE,MODEL,RESTRICTED,REMARKS,
     CREATED,MODIFIED,LAST__USED,LABEL,LOCATION,OWNER__AT__LOCATION,
     NAME__AT__LOCATION)
  AS SELECT DISTINCT
    CREATOR,NAME,'TABLE',TYPE,' ',' ',REMARKS,' ',' ',' ',
    LABEL,LOCATION,TBCREATOR,TBNAME
    FROM SYSIBM.SYSTABLES, SYSIBM.SYSTABAUTH
    WHERE CREATOR = TCREATOR AND NAME=TTNAME AND GRANTEETYPE = ' ' AND
         GRANTEE IN (USER,CURRENT SQLID,'PUBLIC*')

To use a view you have created (for example, QMFADM.REMOTE__DB2__TABLES) and override the default view, issue a command like this one:

If you are a remote user: You do not have access to objects defined only as PUBLIC at the relevant remote location.

The view Q.DSQEC__ALIASES selects only the list of aliases for a list of tables, or the column information for an alias in DB2 for OS/390, DB2 workstation, or iSeries servers.

Figure 95. Default view that provides a list of aliases for the LIST command (OS/390)

CREATE VIEW Q.DSQEC__ALIASES
    (OWNER,TNAME,TYPE,SUBTYPE,MODEL,RESTRICTED,REMARKS,
     CREATED,MODIFIED,LAST__USED,LABEL,LOCATION,OWNER__AT__LOCATION,
     NAME__AT__LOCATION)
  AS SELECT
    CREATOR,NAME,'TABLE',TYPE,' ',' ',REMARKS,' ',' ',' ',
    LABEL,LOCATION,TBCREATOR,TBNAME
    FROM SYSIBM.SYSTABLES
    WHERE CREATOR IN (USER,CURRENT SQLID) AND TYPE = 'A'

To use a view you have created (for example, QMFADM.DB2__ALIASES) and override the default view, issue a command like this one:

Figure 96. Default view that provides column information for the DESCRIBE command (OS/390)

CREATE VIEW Q.DSQEC__COLS__LDB2
    (OWNER, TNAME, CNAME, REMARKS,LABEL)
  AS SELECT DISTINCT
    TBCREATOR, TBNAME, NAME, REMARKS, LABEL
  FROM SYSIBM.SYSCOLUMNS, SYSIBM.SYSTABAUTH
    WHERE TCREATOR = TBCREATOR AND TTNAME = TBNAME AND GRANTEETYPE = ' '
      AND GRANTEE IN (USER,'PUBLIC',CURRENT SQLID,'PUBLIC*')

To use a view you have created (for example, QMFADM.LOCAL__DB2__COLUMNS) and override the default view, issue a command like this one:

The view Q.DSQEC__COLS__RDB2 selects only the column information from a table on another DB2 location. The user's current location must be DB2.

To use a view you have created (for example, QMFADM.REMOTE__DB2__COLUMNS) and override the default view, issue a command like this one:

If you are a remote user: You do not have access to objects defined only as PUBLIC at the relevant remote location.

The views shipped with QMF can return multiple identical rows if SYSIBM.SYSTABAUTH has multiple entries authorizing the user or PUBLIC to a given table. When used by the QMF LIST or DESCRIBE commands, rows with duplicate OWNER and TNAME (for the table view) or duplicate OWNER, TNAME, and CNAME (for the column view) are ignored.

Changing the default list

Using the QMF-provided default views for your table lists and column information might increase processing time, because DB2 gathers authorization information from the SYSIBM.SYSTABAUTH. If you do not need the extra security provided by these authorization checks, consider creating your own views that generate a list of objects stored in the database.

Use a query similar to the one in Figure 97 to create your own view. This query eliminates duplicate rows in the view and, although DB2 spends more time before returning rows to QMF, there is less data transfer between the database and the user machine, producing better performance. You can name your customized view any name that is valid in QMF. See the QMF Reference manual for information on QMF naming conventions.

Figure 97. Customizing your object lists using global variables (OS/390)

CREATE VIEW Q.DATABASE__OBJECTS
  (OWNER,TNAME,TYPE,SUBTYPE,MODEL, RESTRICTED, REMARKS,
   CREATED,MODIFIED,LAST__USED,LABEL,LOCATION,OWNER__AT__LOCATION,
   NAME__AT__LOCATION)
AS SELECT CREATOR,TNAME,
'TABLE',TABLETYPE,' ',' ',REMARKS,
   ' ',' ',' ',TLABEL,' ',' ',' '
FROM SYSIBM.SYSTABLES
   WHERE TNAME IN (SELECT TTNAME
                FROM SYSIBM.SYSTABAUTH
                 WHERE TCREATOR = A.CREATOR
                   AND GRANTEETYPE = ' &'
                   AND GRANTEE IN (USER, 'PUBLIC'))

Remember to SET GLOBAL for the appropriate variable for the new view name to be used.

If you want to create a view that shows only the tables for which a user has privileges, but does not require a join, consider defining a view that selects only from SYSIBM.SYSTABAUTH, but does not return values for REMARKS or LABEL.

For other administrators, consider creating another view similar to the default QMF view, but that selects only from SYSIBM.SYSTABLES or SYSIBM.SYSCOLUMNS for column list. Then the administrators can name this view in the DSQEC__COLS__LDB2 or DSQEC__COLS__RDB2 global variables and access descriptive information for any columns in the database.

DSQEC__TABS__LDB2, DSQEC__TABS__RDB2, DSQEC__ALIASES, DSQEC__COLS__LDB2, and DSQEC__COLS__RDB2 are part of a set of global variables that help you control aspects of a user's QMF session. For more information on using global variables in procedures, see Using QMF. For a list of global variables and information on using them in applications, see the Developing QMF Applications manual .

Object list storage requirement

For the LIST command, there are two sets of storage requirements for each row of the object list.