 |
|
| Setting Up Query Managers |
Creating DB2 Query Manager Tables
The following five sample programs are provided to create the DB2 Query Manager tables. These programs can be edited to meet your needs. These programs can be run from Query and Reporting or from outside Query and Reporting, either from the Program Editor window or in batch. All the sample programs are located in source entries in the SASHELP.QASSIST catalog. See Setting Up Query Managers for an example of using some of these programs.
Note: Users need READ
access to the SYSIBM.SYSTABLES and SYSIBM.SYSCOLUMNS tables
when they use a dynamic DB2 Query Manager. ![[cautionend]](../../../../common/65445/HTML/default/images/cautend.gif)
- SYSQM.SOURCE
-
Use this program to generate a set of master DB2 Query Manager tables from which all other DB2 Query Manager tables are generated. This program should be run as a batch job, when the activity on the DB2 systems catalog is low. READ access to the following DB2 tables is required to run this program:
SYSIBM.SYSTABLES SYSIBM.SYSFOREIGNKEYS SYSIBM.SYSCOLUMNS SYSIBM.SYSINDEXES SYSIBM.SYSRELS SYSIBM.SYSTABAUTH SYSIBM.SYSKEYS -
QMUSER1.SOURCE
QMUSER3.SOURCE -
Use these programs to generate subsets of the master Query Manager tables based on a specified set of creators. You can modify the programs to reflect the names of the creators that you want in the Query Manager tables. The programs can be run from the Program Editor window, in batch, or from Query and Reporting when specified in a Query Manager definition. Use QMUSER1 to generate smaller subsets of creators and QMUSER3 to generate larger subsets, because the creator names are kept in a separate table.
- QMUSER2.SOURCE:
-
Use this program to generate a set of Query Manager tables containing only the tables to which the user has READ access. This program uses the TABAUTH table generated by the SYSQM.SOURCE program.
- DB2DYNA.SOURCE:
-
Use this program to create a set of dynamic Query Manager tables that read from the DB2 systems catalog directly. A dynamic Query Manager is useful if the structure of DB2 tables changes frequently or if DB2 tables are added and deleted frequently. Be aware that using a dynamic Query Manager on a large DB2 system is considerably slower than using a static one because the DB2 catalog must be read at the time a user chooses the Select window.
| TABAUTH Table |
The TABAUTH table resides in the library that contains the master DB2 Query Manager tables. It is created by the program in SASHELP.QASSIST.SYSQM.SOURCE. It is used to create personal DB2 Query Manager tables and contains only the tables to which the user has READ access. This table is used by the SASHELP.QASSIST.QMUSER2.SOURCE program.
Note: If privileges are granted to RACF groups
instead of to single DB2 users, you can modify QMUSER2.SOURCE and the DB2
RACF exit to use these grants. See SASHELP.QASSIST.SAMP_SCL.SOURCE for an
example of the DB2 RACF exit.
| Column Number | Column Name | Column Label | Column Type | Column Length | Index Type |
|---|---|---|---|---|---|
|
|
|||||
| 1 | CREATOR | Table creator | CHAR | 8 | SIMPLE |
| 2 | NAME | Table name | CHAR | 18 | SIMPLE |
| 3 | GRANTEE | Table access granted to | CHAR | 8 | SIMPLE |
| _DB2COLS Table |
The _DB2COLS table contains one row for every DB2 column in the _DB2TAB table. The column names, column types, and column lengths must be specified as they are described in the _DB2COLS table. The rest of the table structure can be customized. This table is shown here because of the way that the sample Query Manager source programs create the table. It is recommended that you index the _DB2COLS table for the fastest possible access.
| Column Number | Column Name | Column Label | Column Type | Column Length | Index Type |
|---|---|---|---|---|---|
| 1 | CREATOR | Table creator | CHAR | 8 | SIMPLE |
| 2 | NAME | Table name | CHAR | 18 | SIMPLE |
| 3 | COLNAME | Column name | CHAR | 18 | SIMPLE |
| 4 | INDEX | Index | CHAR | 1 |
|
| 5 | LABEL | Label/Column name | CHAR | 40 |
|
| 6 | COLTYPE | Column type | CHAR | 8 |
|
| 7 | LENGTH | Length | NUM | 8 |
|
| 8 | DECIMALS | Decimals | NUM | 8 |
|
| 9 | NULLS | Nulls allowed? | CHAR | 1 |
|
| 10 | COLNO | Column number | NUM | 8 |
|
| 11 | TEXT1 | Text1 | CHAR | 70 |
|
| 12 | TEXT2 | Text2 | CHAR | 70 |
|
| 13 | TEXT3 | Text3 | CHAR | 70 |
|
| 14 | TEXT4 | Text4 | CHAR | 70 |
|
| 15 | F_CREA | First.Creator | CHAR | 1 |
|
| 16 | L_CREA | Last.Creator | CHAR | 1 |
|
| 17 | F_NAME | First.Name | CHAR | 1 |
|
| 18 | L_NAME | Last.Name | CHAR | 1 |
|
| 19 | FORMAT |
|
CHAR | 12 |
|
| 20* | SASNAME | Name that is used when creating a SAS table or view | CHAR | 8 |
|
| 21* | SASLEN | Length in SAS | NUM | 8 |
|
| * Optional: If the name exists, it is used as a column name in SAS tables or views. | |||||
The following list describes the columns in the _DB2COLS table:
- CREATOR
-
contains the authorization ID for the table that contains the column.
- NAME
- COLNAME
- INDEX
-
contains an indication of whether the column is part of an index. By default, the indicator is an asterisk (*).
- LABEL
-
contains the DB2 label for the column if one is defined; otherwise, it contains the column name.
- COLTYPE
- LENGTH
- DECIMALS
-
contains the number of decimals for the DB2 data type decimal.
- NULLS
-
contains an indication of whether or not null values are allowed in the DB2 column: N No (the default) or Y Yes.
- COLNO
- TEXT1 -TEXT4
-
holds descriptions of the column. The default is the contents of the REMARKS column of the SYSIBM.SYSCOLUMNS table, divided into 70-byte strings.
- F_CREA
-
is a quoted flag. 1 is the first column for a given creator; otherwise, F_CREA contains 0. F_CREA must have the contents as specified.
- L_CREA
-
is a quoted flag. 1 is the last column for a given creator; otherwise, L_CREA contains 0. L_CREA must have the contents as specified. All rows in the _DB2COLS table between F_CREA='1' and L_CREA='1' must belong to the same CREATOR.
- F_NAME
-
is a quoted flag. 1 is the first column in the table; otherwise, F_NAME contains 0. F_NAME must have the contents as specified.
- L_NAME
-
is a quoted flag. 1 is the last column in the table; otherwise, L_NAME contains 0. L_NAME must have the contents as specified. All rows in the _DB2COLS table between F_NAME='1' and L_NAME='1' must belong to the same table NAME.
- FORMAT
-
contains the SAS format being used when the field is displayed. This field is defined in the SASHELP.QASSIST.SYSQM.SOURCE program (optional column).
- SASNAME
-
assigns a SAS name to a column in the SAS table. DB2 column names can be 18 characters long. If they truncate in SAS tables, the names might not be meaningful. Use SASNAME to assign a more meaningful eight-character name to a SAS column (optional column).
- SASLEN
-
Defines the length of the column when loaded into SAS from DB2 (optional column).
| _DB2INDX Table |
The _DB2INDX table contains information about indexes that are defined on columns in tables in the _DB2COLS table. The _DB2INDX table contains one row for every column that is part of an index. _DB2INDX is not required by the DB2 Query Manager, but users often find the information helpful.
| Column Number | Column Name | Column Label | Column Type | Column Length | Index Type |
|---|---|---|---|---|---|
|
|
|||||
| 1 | CREATOR | Table creator | CHAR | 8 | SIMPLE |
| 2 | NAME | Table name | CHAR | 18 | SIMPLE |
| 3 | COLNAME | Column name | CHAR | 18 | SIMPLE |
| 4 | IXNAME | Index name | CHAR | 18 | |
| 5 | CLUSTER | Cluster index | CHAR | 1 | |
| 6 | UNIQUE | Unique index? | CHAR | 1 | |
| 7 | ICREATOR | Index creator | CHAR | 8 | |
| 8 | ORDERING | Order of col. in key | CHAR | 1 | |
| 9 | IXCOLS | Columns in index | CHAR | 200 |
The following list describes the columns in the _DB2INDX table:
- CREATOR
- NAME
- COLNAME
-
contains the name of the column that contributes to the index.
- IXNAME
- CLUSTER
-
indicates whether the index is a cluster index. Y (yes) indicates a cluster index and is the default value; N (no) indicates it is not a cluster index.
- UNIQUE
-
indicates whether the index is unique.
D U P - ICREATOR
- ORDERING
- IXCOLS
-
contains the names of all the columns that are part of the index, separated by commas and listed in the order in which the columns appear.
| _DB2RELS Table |
The _DB2RELS table contains primary key/foreign key relations that are defined between DB2 tables. _DB2RELS is not required by the Query Manager, but if relations are defined between DB2 tables, the tables can be joined automatically. If relations are not used in your DB2 system, you can provide the information directly or from other sources (for example, from an external data dictionary). The _DB2RELS table has one row for every column that is part of a relation. This table must be sorted by RELNAME, TABLE, RTABLE, and COLSEQ.
| Column Number | Column Name | Column Label | Column Type | Column Length | Index Type |
|---|---|---|---|---|---|
| 1 | TABLE | Table | CHAR | 27 | SIMPLE |
| 2 | RTABLE | Referenced table | CHAR | 27 | SIMPLE |
| 3 | COLCOUNT | No. of columns in foreign key | NUM | 8 |
|
| 4 | RELNAME | Relation name | CHAR | 8 | SIMPLE |
| 5 | ORIGIN | Keep track of relation-originator | CHAR | 3 |
|
| 6 | COLNAME | Foreign key column | CHAR | 18 | SIMPLE |
| 7 | COLSEQ | Position within foreign key | NUM | 8 |
|
| 8 | PKEYCOL | Primary key column | CHAR | 18 |
|
| 9 | CREATOR | Creator of table | CHAR | 8 | SIMPLE |
| 10* | RELDESC | Relation description | CHAR | 30 |
|
| * Optional: If this column exists, it is used as a relation description in the menu when the tables are automatically joined. | |||||
The following list describes the columns in the _DB2RELS table:
- TABLE
-
contains the creator.name of the table that contains the foreign key(s).
- RTABLE
-
contains the creator.name of the referenced table, that is, the table that contains the primary key(s).
- COLCOUNT
-
contains the number of columns in the foreign key (of the table specified in the TABLE column).
- RELNAME
- ORIGIN
-
describes where the relation was created: in DB2, in the Master Query Manager, or by the user. This information is used when updating the table so user-defined relations are not affected.
- COLNAME
-
contains the name of the column (from the table in the TABLE column) that contributes to the primary key/foreign key relation.
- COLSEQ
-
contains the position in the foreign key that the column in the COLNAME column has.
- PKEYCOL
-
contains the name of the column in the primary key with the number that corresponds to the value of the column COLSEQ.
- CREATOR
- RELDESC
-
describes the primary key/foreign key relation(s) for the Joins/relations menu in the Join Table window (optional column).
| _DB2TAB Table |
The _DB2TAB table contains one row for every DB2 table or view that the user can query.
| Column Number | Column Name | Column Label | Column Type | Column Length | Index Type |
|---|---|---|---|---|---|
|
|
|||||
| 1 | CREATOR | Table creator | CHAR | 8 | SIMPLE |
| 2 | NAME | Table name | CHAR | 18 | SIMPLE |
| 3 | LABEL | Table label | CHAR | 30 | |
| 4 | TYPE | Alias/Table/View | CHAR | 1 | |
| 5 | DBNAME | Database | CHAR | 8 | |
| 6 | TSNAME | Table space | CHAR | 8 | |
| 7 | NPAGES | Number of pages in table | NUM | 8 | |
| 8 | ROWS | Number of rows in table | NUM | 8 | |
| 9 | TEXT1 | Text1 | CHAR | 70 | |
| 10 | TEXT2 | Text2 | CHAR | 70 | |
| 11 | TEXT3 | Text3 | CHAR | 70 | |
| 12 | TEXT4 | Text4 | CHAR | 70 | |
| 13 | P_KEY | Primary Key | CHAR | 200 | |
| 14* | SCREEN | FSEDIT screen name | CHAR | 35 | |
| * Optional. | |||||
The following list describes the columns in the _DB2TAB table.
- CREATOR
- NAME
- LABEL
- TYPE
-
contains information about the type of DB2 object in question, T for a table, V for a view, A for an alias. T is the default.
- DBNAME
-
contains the name of the DB2 database where the table or view is stored.
- TSNAME
-
contains the name of the DB2 table space where the table resides.
- NPAGES
-
contains the number of pages that the table uses. Default: -1 if it is a view or if a RUNSTATS utility has not been run for the table space.
- ROWS
-
contains the number of rows in the table, unless it is a view or a RUNSTATS utility has not been run for the table space.
- TEXT1 -TEXT4
-
contains a description of the table. The REMARKS column in SYSIBM.SYSTABLES is divided into 70-byte strings.
- P_KEY
-
contains the names of the columns in the primary key (separated with commas).
- SCREEN
-
contains the name of a PROC FSEDIT screen to be used for browsing and editing DB2 tables (optional column).
|
|
Copyright © 2012 by SAS Institute Inc., Cary, NC, USA. All rights reserved.