Using the default object lists on VM and VSE

QMF provides the following default views and automatically assigns them to the user Q during installation into DB2 databases:

• Q.DSQEC__TABS__SQL
• Q.DSQEC__COLS__SQL

QMF supplies a variation of the following views when QMF is installed into a DB2 workstation server or DB2 iSeries:

• Q.DSQEC__TABS__LDB2
• Q.DSQEC__ALIASES
• Q.DSQEC__COLS__LDB2

The view Q.DSQEC_TABS_SQL selects only those database tables the user is authorized to see. Figure 98 shows the view provided for DB2. To override the default view Q.DSQEC_TABS_SQL, issue a command like this:

CREATE VIEW Q.DSQEC_TABS__SQL
    (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,' ',' ',' ',
    TLABEL,' ',' ',' '
    FROM SYSTEM.SYSCATALOG, SYSTEM.SYSTABAUTH
    WHERE CREATOR = TCREATOR AND TNAME=TTNAME AND GRANTEETYPE = ' ' AND
         GRANTEE IN (USER,'PUBLIC');
COMMENT ON TABLE Q.DSQEC_TABS_SQL IS
                  'QMF VIEW FOR DB2 TABLES/VIEWS LIST';
GRANT SELECT ON Q.DSQEC_TABS_SQL TO PUBLIC;

The view Q.DSQEC_COLS_SQL selects only the column information a user is authorized to see in DB2 database servers. Figure 99 shows the view provided:

CREATE VIEW Q.DSQEC__COLS__SQL
    (OWNER, TNAME, CNAME, REMARKS,LABEL)
  AS SELECT
    CREATOR, TBNAME, CNAME, REMARKS, CLABEL
  FROM SYSTEM.SYSCOLUMNS, SYSTEM.SYSTABAUTH
    WHERE TCREATOR = CREATOR AND TTNAME = BNAME AND GRANTEETYPE = ' '
      AND GRANTEE IN (USER,'PUBLIC')

To override the default view Q.DSQEC_COLS_SQL, issue the command:

SET GLOBAL (DSQEC_COLS_SQL = userid_hour_local_sql_columns

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 SYSTEM.SYSCATALOG and SYSTEM.SYSCOLUMNS tables. 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 below 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.

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 SYSTEM.SYSCATALOG.A
   WHERE TNAME IN (SELECT TTNAME
                FROM SYSTEM.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 SYSTEM.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 SYSTEM.SYSCATALOG for table list or SYSTEM.SYSCOLUMNS for column list. Then the administrators can name this view in the DSQEC__COLS__SQL or DSQEC__TABS__SQL global variables and access descriptive information for any columns in the database.

Follow these rules if you are creating a list view of your own:

• The view must have the same view column names as the corresponding QMF-supplied view. The column names in the CREATE VIEW statement of the alternative view can be in any order.
• All columns must have a data type of CHAR or VARCHAR. QMF returns errors upon finding other data types.
• Do not exceed the following maximum lengths for columns in the view:
  • 18 characters for TNAME, CNAME, and NAME__AT__LOCATION
  • 254 characters for REMARKS
  • 30 characters for LABEL
  • 1 character for RESTRICTED
  • 16 characters for LOCATION
  • 8 characters for OWNER, TYPE, SUBTYPE, MODEL, and OWNER__AT__LOCATION
• Always supply values for OWNER, TNAME, TYPE, and CNAME. These columns cannot be null.

DSQEC__TABS__SQL and DSQEC__COLS__SQL 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.

• The QMF internal RPT record collection requires:
  • Object OWNER key information, 50 bytes
  • REMARKS, up to 254 bytes
  • TABLE with a LABEL, up to 30 bytes
  • ALIAS, 42 bytes
  • Object information for QUERY, PROC, and FORM, 63 bytes
• The storage to hold displayed data and control information requires 130 bytes plus the actual number of bytes for REMARKS, up to 254 bytes and the actual number of bytes for the LABEL associated with a table, up to 30 bytes.

Note: For a complete list of the views provided by QMF refer to Appendix B. QMF Objects Residing in DB2.