The Object Search and List tools are a set of integrated applications that search the system catalog tables in your database and display information about these objects:
For every object type except columns, you can "drill-down" to a lower level of information. For example, you might ask for a list of public DBSPACES whose names contain the string "ORDER". To do this, you would make the entries shown in Figure 212 and press ENTER.
Figure 212. DBSPACE Object Search and List screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- DBSPACE List Utility ----------------------------* | | | CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | DBSPACE OWNER ==> ALL (ALL, PUBLIC, PRIVATE, ownername) | | | | | | | | DBSPACE NAME ==> %ORDER% (blank for ALL, use % for wildcard) | | | | | | | | DBSPACE PAGES: MINIMUM ==> 0 MAXIMUM ==> 9999999 | | | | | | | | DBSPACE TYPE ==> A (A=Acquired, U=Unacquired, B=Both) | | | | | | | | STORPOOL ==> _____ (blank for ALL) | | | | | | | | The % SQL wildcard character can be used when specifying the | | | | Dbspace Owner or Dbspace Name values for selection. | | | | | | | | | | | | | | | *---------------------------------------------------------------SQMDLIST-----* | | PF1 HELP PF3 QUIT PF4 EXIT PF5 MAIN MENU ENTER Process | | PF10 TABLE Search panel PF11 PACKAGE Search panel | | | +--------------------------------------------------------------------------------+
The Object Search and List tools search the database system catalog tables using the parameters you supply and displays the output as shown in Figure 213.
Figure 213. DBSPACE List Utility Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- DBSPACE List Utility ----------------------------* | | | Database => SQLDBA | | | | % % | | | | SEL SPNO OWNER NAME POOL PAGES NACTIVE NH IX FR NTAB LK | | | | --- ---- -------- ------------------ ---- ------- ------- -- -- -- ---- -- | | | | _ 17 PUBLIC ORDER_DETAIL 4 5120 908 8 33 15 0 S | | | | _ 8 PUBLIC CUSTOMER_ORDER 3 1206 52 8 20 5 1 S | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Select: S = SHOW DBSPACE L = SHOW LOCK T = Table List | | | | R = REORG A = ACQUIRE U = UPDATE STATISTICS D = DROP | | | | | | | | Page 1 of 1 | | | *---------------------------------------------------------------SQMDLST2-----* | | PF: 1 Help 3 QUIT 4 StatHistory | | 9 Sort/Name 10 Sort/Spno 11 Sort/Pages 12 Sort/Pool | +--------------------------------------------------------------------------------+
Commonly requested information is displayed for each instance of the object type you are listing. For DBSPACES, this includes:
At the bottom of each list screen, the commonly used DBA commands and utilities which can be invoked directly from the list screen are displayed. For DBSPACES, this includes:
The PF key selections offer further function such as the ability to access help or exit to the next higher level screen. Other PF keys allow you to alter the display of information itself. For example, the DBSPACE List screen offers these PF key functions:
There are two ways to invoke the Object Search and List:
To reach the Object Search and List using the panel interface:
NOTE:
|
To reach the Object Search and List directly from CMS, you can:
Once you enter the Object Search and List tool, you have many options which are best described by referring to Figure 214.
Figure 214. Navigating the Object Search and List Tools
DBSPACE Search and List tool
Dbspace Search Entry Panel
|
| ENTER
| DBSPACE Search and List tool
|----------------> Dbspace List Panel
| |
| | ENTER
| | DBSPACE Search and List tool
| +----> DBSPACE List Utility Panel
| |
| | (List Option T)
| | Table Search and List tool
| +---> Table Search Entry Panel
|
| <PF10>
| Table Search and List tool
|----------------> Table Search Entry Panel
| |
| +-----> ENTER
| Table Search and List tool
| Table List Panel
| |
| | (List Option P)
| | Package Search and List tool
| +---> Package List Panel
| |
| | (List Option V)
| | Table View List tool
| +---> Table View List Panel
| |
| | (List Option I)
| | Table Index List tool
| +---> Table Index List Panel
| |
| | (List Option C)
| | Table Column List tool
| +---> Table Column List Panel
|
| <PF11>
| Package Search and List tool
|----------------> Package Search Entry Panel
| |
| | ENTER
| | Package Search and List tool
| +---> Package List Panel
For example, the first panel displayed is the DBSPACE List Utility Screen. From this panel, you can:
If you want to view package information and you know the package creator and name, you can go directly to the Package List Utility screen, key in the creator and name, and see the information you want. You can also find the same information by displaying DBSPACES and "drilling-down" through the Table List and Package List screens.
Using the Object Search and List tools requires database DBA authority or greater.
Using the DBSPACE Search and List tool, you can search a database for a specific DBSPACE, several DBSPACES with common or similar attributes, or all DBSPACES. DBSPACES meeting your search criteria are displayed in a list panel. Actions such as performing a DBSPACE reorganization can be executed against specific DBSPACES in the list.
Figure 215. DBSPACE Search Entry Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- DBSPACE List Utility ----------------------------* | | | CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | DBSPACE OWNER ==> ANDYS (ALL, PUBLIC, PRIVATE, ownername) | | | | | | | | DBSPACE NAME ==> __________________ (blank for ALL, use % for wildcard) | | | | | | | | DBSPACE PAGES: MINIMUM ==> 0 MAXIMUM ==> 9999999 | | | | | | | | DBSPACE TYPE ==> A (A=Acquired, U=Unacquired, B=Both) | | | | | | | | STORPOOL ==> _____ (blank for ALL) | | | | | | | | The % SQL wildcard character can be used when specifying the | | | | Dbspace Owner or Dbspace Name values for selection. | | | | | | | | | | | | | | | *---------------------------------------------------------------SQMDLIST-----* | | PF1 HELP PF3 QUIT PF4 EXIT PF5 MAIN MENU ENTER Process | | PF10 TABLE Search panel PF11 PACKAGE Search panel | | | +--------------------------------------------------------------------------------+
Shown in Figure 215 is the search entry panel of the DBSPACE Search and List tool. The database you are currently working with is identified as is the ID of the service machine that controls that database and the node it resides upon. You can change the database name on this panel. To specify another service machine and node, use Option C on the Control Center Main Menu. Control Center Communication Path and Database Settings are discussed on page "Control Center Communication Path and Database Settings".
From this panel, enter your DBSPACE search criteria and press ENTER to process, or go directly to the search entry panel of the Table Search and List tool using program function key 10 (PF10), or to the Package Search and List tool using PF11.
One or more of these attributes can be specified when searching for a specific DBSPACE or group of DBSPACES:
DBSPACES which satisfy your search criteria are displayed as shown in Figure 216.
Figure 216. DBSPACE List Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- DBSPACE List Utility ----------------------------* | | | Database => SQLDBA | | | | SEL SPNO OWNER NAME POOL PAGES NACTIVE NH IX FR NTAB LK | | | | --- ---- -------- ------------------ ---- ------- ------- -- -- -- ---- -- | | | | _ 22 DUKEWE DUKE_IX 3 1024 1 0 15 1 S | | | | _ 21 D128980 D128980A 2 1024 1 20 0 1 S | | | | _ 26 D128980 SUSAN_DENNIS_DBS -4 1024 1 20 0 1 S | | | | _ 14 L002653 L002653A 3 1024 1 10 0 1 S | | | | _ 12 M356959 M356959A 3 1024 2 31 0 1 S | | | | _ 13 M760595 M760595A 3 1024 1 33 0 1 S | | | | _ 15 MILBURN MILBURNJ 3 1024 1 33 0 1 S | | | | _ 6 PUBLIC ANDYS 1 1024 1 33 15 2 P | | | | _ 7 PUBLIC ETCTPHD_DEV 3 5120 2 25 0 2 T | | | | _ 3 PUBLIC HELPTEXT 1 8192 0 33 10 19 P | | | | | | | | Select: S = SHOW DBSPACE L = SHOW LOCK T = Table List | | | | R = REORG A = ACQUIRE U = UPDATE STATISTICS D = DROP | | | | | | | | Page 1 of 3 | | | *---------------------------------------------------------------SQMDLST2-----* | | PF: 1 Help 3 QUIT 4 StatHistory | | 9 Sort/Name 10 Sort/Spno 11 Sort/Pages 12 Sort/Pool | +--------------------------------------------------------------------------------+
Select options can be invoked against listed DBSPACES.
Operational Notes:
Figure 217. Acquire DBSPACE Entry Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *----------------------------- Acquire DBSPACE ------------------------------* | | | CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | DBSPACE OWNER ==> ________ (specify for PRIVATE dbspaces only) | | | | | | | | DBSPACE NAME ==> __________________ | | | | | | | | NHEADER ==> 8 (Number of HEADER pages) | | | | | | | | PCTINDEX ==> 33 (Percent INDEX pages) | | | | | | | | PCTFREE ==> 15 (Percent FREESPACE on each page) | | | | | | | | LOCKMODE ==> DBSPACE (DBSPACE, PAGE, ROW) | | | | | | | | | | | | TYPE: PRIVATE PAGES: 1024 STORPOOL: 3 | | | | | | | | | | | *---------------------------------------------------------------SQMDACQ------* | | PF1 HELP PF3 QUIT PF4 EXIT PF5 MAIN MENU ENTER Process | | | +--------------------------------------------------------------------------------+
Using the Package Search and List tool, you can search an entire database for a specific package, several packages with common or similar attributes, or all packages. Packages meeting your search criteria are displayed in a list panel. Actions such as performing a package rebind can be executed against specific packages in the list. |Individual statements can be submitted to the EXPLAIN processing |tool.
Packages can also be listed for a specific table selected using the Table Search and List tool.
Figure 218. Package Search Entry Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- PACKAGE List Utility ----------------------------* | | | CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | PACKAGE CREATOR ==> ALL (ALL, creator, use % for wildcard) | | | | | | | | PACKAGE NAME ==> __________________ (blank for ALL, use % for wildcard) | | | | | | | | VALID ==> B (Y=Yes, N=No, B=Both) | | | | | | | | | | | | | | | | | | | | The % SQL wildcard character can be used when specifying the | | | | Package Creator or Package Name values for selection. | | | | Use Y to select VALID packages, N to select INVALID packages, | | | | or B to select BOTH. | | | | | | | | | | | *---------------------------------------------------------------SQMPLST2-----* | | PF1 HELP PF3 QUIT PF4 EXIT PF5 MAIN MENU ENTER Process | | PF10 DBSPACE Search Panel | | | +--------------------------------------------------------------------------------+
Shown in Figure 218 is the search entry panel of the Package Search and List tool. The database you're currently working with is identified by your communication path and database settings shown near the top of the panel. The communication path can be changed using Option C from the Control Center Main Menu, whereas the database setting can be changed on this panel. Control Center Communication Path and Database Settings are discussed on page "Control Center Communication Path and Database Settings".
From this panel you can enter your package search criteria and press ENTER to process, or return to the DBSPACE Search and List tool using program function key 10 (PF10).
One or more of these attributes can be specified when searching for a specific package or group of packages:
Packages which satisfy your search criteria are displayed as shown in Figure 219. If you invoked the Package List (P) select option from the list panel of the Table Search and List tool, then only packages which reference the selected table are listed.
Figure 219. Package List Panel
+--------------------------------------------------------------------------------+
| mm/dd/yyyy CONTROL CENTER hh:mm:ss |
| *-------------------------- PACKAGE List Utility ----------------------------* |
| | Database => SQLDBA Table => | |
| | | |
| | SEL CREATOR PACKAGE NAME SPNO VALID TIMESTAMP | |
| | --- -------- ------------------ ----- ----- ----------------- | |
| | _ SQLDBA ARIDSQL 2 Y 05/13/97.15:37:23 | |
| | _ SQLDBA ARIFCRD 2 Y 05/13/97.15:37:23 | |
| | _ SQLDBA ARIISQL 2 Y 05/13/97.15:37:26 | |
| | _ SQLDBA ELORXSQL 2 Y 01/18/97.15:09:22 | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | Select: D = Drop Package R = Rebind Package |X = eXplain | |
| | U = Unload L = reLoad, V = View Package | |
| | | |
| | Page 1 of 1 | |
| *---------------------------------------------------------------SQMPLIST-----* |
| PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU |
| |
| |
+--------------------------------------------------------------------------------+
Select options can be invoked against listed packages.
Package name is used as output filename. Package creator is used as filetype.
Usage Considerations:
|Selecting option 'V' allows you to view the attributes and SQL |statements contained in your package. The information is placed into a |CMS file and viewed using XEDIT. An example of this output is in Figure 220. The output file is given a file name equal to the |first eight characters of the package name and a file type of PACKDATA.
|Figure 220. Example Output from View Package Option
|
Package: MSTRUSR1.EMPLOYEP
Database: SQLDBA
Package create release: Unknown
Last prepped in release: 3.4
Character Set: ENGLISH
Sections: 1
Preprocessing Characteristics:
------------------------------
Package built using Extended Dynamic
Database DBCS option OFF at package creation
Block option: NOBLOCK
Package is modifiable
Describe option: ON
Options Specified at Prep time:
-------------------------------
BLock
ISOLation(USER)
DEFAULT Options at Prep time:
-----------------------------
RELease(COMMIT), EXPLAIN(NO), NOBLock, KEEP, REPLACE, NOEXIST
NOCHECK, TIME(default), DATE(default), PERiod, APOST, ISOLation(CS)
PREPname=EMPLOYEP
LABEL(default=spaces)
CHARSUB(Default), CTOKEN(NO)
Static SQL Statements in package:
---------------------------------
UPDATE MSTRUSR1.EMPLOYEE SET TPH_RECTYPE = ? WHERE WBSID = ? AND
TPH_RECTYPE = ?;
|Figure 221 shows an example of the first panel of output when the |'X' (eXplain) option is chosen for the ARIISQL package.
| Note: | For information about running EXPLAIN on a QMF query, refer to Running EXPLAIN on a QUERY. |
|Figure 221. Package SQL Statements for EXPLAIN Processing
+--------------------------------------------------------------------------------+ | mm/dd/yyyy Control Center hh:mm:ss | |*-------------------------- PACKAGE SQL STATEMENTS -------------------------* | || Database => SQLDBA CTRLID: MSTRSRV1 | | || Package => "SQLDBA"."ARIISQL" NODE: VMSYSTM1 | | || | | || S TEXT | | || - --------------------------------------------------------------------- | | || _ DELETE FROM SQLDBA.STORED QUERIES WHERE STMTNAME = :H AND CREATOR = | | || USER | | || | | || _ INSERT INTO SQLDBA.STORED QUERIES (STMTNAME,STMTTEXT,CREATOR) | | || VALUES(:H,:H,USER) | | || | | || _ SELECT STMTTEXT INTO :H FROM SQLDBA.STORED QUERIES WHERE STMTNAME = | | || :H AND CREATOR = USER | | || | | || _ SELECT STMTNAME INTO :H FROM SQLDBA.STORED QUERIES WHERE STMTNAME = | | || | | || Select (X) for the statement to be EXPLAINed. | | || | | || Page 1 of 4 | | |*---------------------------------------------------------------SQMPSTMT----* | |PF: 1 Help 3 QUIT 4 EXIT 5 MAIN MENU 7 Bkwd 8 Fwd | | | +--------------------------------------------------------------------------------+ |
|By placing an X next to the SQL statement to be EXPLAINed (beneath the S |option), the following is displayed:
|Figure 222. EXPLAIN PACKAGE STATEMENT Menu
+--------------------------------------------------------------------------------+ | dd/mm/yyyy Control Center hh:mm:ss | | *------------------------ EXPLAIN PACKAGE STATEMENT ------------------------* | | | Option ====> CTRLID: MSTRSRV1 | | | | Database ==> SQLDBA NODE: VMSYSTM1 | | | | | | | | | | | | EXPLAIN =====> _ ALL | | | | _ COST | | | | _ PLAN | | | | _ REFERENCE | | | | _ STRUCTURE | | | | | | | | SET QUERYNO => __________ VIEW RESULTS => 1 (1=YES,0=NO) | | | | | | | | FOR DELETE FROM SQLDBA."STORED QUERIES" WHERE STMTNAME = :H | | | | AND CREATOR = USER | | | | | | | | NOTE: Place an 'X' in the appropriate selection fields. Enter a query | | | | number so the results can be extracted for viewing. | | | | Press ENTER to submit the EXPLAIN statement. | | | | | | | *---------------------------------------------------------------SQMEXPLN----* | | PF: 1 Help 3 Quit 4 Exit 5 Main Menu | | | +--------------------------------------------------------------------------------+ |
|Place an X in front of each EXPLAIN option you want performed; ALL |includes each of the four other options. Then provide a numeric ID for |the SET QUERYNO field. After the EXPLAIN is run, if you enter 1 for |VIEW REPORTS, you can view the results immediately in a report presented to |you as a CMS file. If you enter 0, the report is not displayed; it |is saved in a CMS file named QUERYNO Qnn, where nn is the |SET QUERYNO value you entered.
|The first 110 bytes of the SQL statement selected to be EXPLAINed are |displayed on the menu. When you press Enter, the tool runs EXPLAIN |dynamically. The results are saved in the users EXPLAIN tables in the |database.
|A check is also made to ensure that the EXPLAIN tables exist for the user |requesting the EXPLAIN. An error message is produced if the EXPLAIN |tables do not exist.
|EXPLAIN reports include a description of each EXPLAIN parameter. A |sample of an EXPLAIN report with the REFERENCE data from a query can be found |in Sample EXPLAIN Report.
Using the Table Search and List tool you can search an entire database for a specific table, several tables with common or similar attributes, or all tables. Tables meeting your search criteria are displayed in a list panel. Actions such as performing a table reorganization can be executed against specific tables in the list.
Tables can also be listed for a specific DBSPACE selected using the DBSPACE Search and List tool.
Figure 223. Table Search Entry Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *--------------------------- TABLE List Utility -----------------------------* | | | CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | TABLE CREATOR ==> M760595 (ALL, creator, use % for wildcard) | | | | | | | | TABLE NAME ==> __________________ (blank for ALL, use % for wildcard) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The % SQL wildcard character can be used when specifying the | | | | Table Creator or Table Name values for selection. | | | | | | | | | | | | | | | *---------------------------------------------------------------SQMTLST2-----* | | PF1 HELP PF3 QUIT PF4 EXIT PF5 MAIN MENU ENTER Process | | PF10 DBSPACE Search Panel | | | +--------------------------------------------------------------------------------+
Shown in Figure 223 is the search entry panel of the Table Search and List tool. The database you are currently working with is identified as well as the server machine ID and node. To change server machine ID and node, use Option C on the Control Center Main Menu. You can specify another database name on this panel. Control Center Communication Path and Database Settings are discussed on page "Control Center Communication Path and Database Settings".
From this panel, enter your table search criteria and press ENTER to process. To return to the DBSPACE Search and List tool, use program function key 10 (PF10).
One or more of these attributes can be specified when searching for a specific table or group of tables:
The Table List tool can be reached in one of two ways.
The Table List panel is shown in Figure 224.
+--------------------------------------------------------------------------------+
| mm/dd/yyyy CONTROL CENTER hh:mm:ss |
| *--------------------------- TABLE List Utility -----------------------------* |
|| Database => SQLDBA Dbspace => | |
|| | |
|| SEL CREATOR TNAME ROWCOUNT NPAGES PCTPAGES NCOLS ROWLEN | |
|| --- -------- ------------------ -------- -------- -------- ----- ------ | |
|| _ M760595 COLTAB 0 0 0 10 0 | |
|| _ M760595 COST_TABLE 0 0 0 3 0 | |
|| _ M760595 DATACAPTURE 3 1 100 2 13 | |
|| _ M760595 INDEXTEST 4160 55 100 4 48 | |
|| _ M760595 INVALID_IDS 15 1 100 1 17 | |
|| _ M760595 PLAN_TABLE 0 0 0 12 0 | |
|| _ M760595 PMPHDM 17 3 5 65 385 | |
|| _ M760595 REFERENCE_TABLE 0 0 0 8 0 | |
|| _ M760595 STRUCTURE_TABLE 0 0 0 6 0 | |
|| _ M760595 TABERROR 3 1 0 3 24 | |
|| | |
|| Select: R = Reorg U = Upd ALL Stats D = DROP L = DEL Rows S = DBSPACE | |
|| List : I = INDX P = PKGS V = VIEWS C = COLS RI = Ref Int |Y = SYNONYMS | |
|| | |
|| Page 1 of 2 | |
| *---------------------------------------------------------------SQMTLIST-----* |
|PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU 6 VIEW REMARKS |
| 7 Bkwd 8 Fwd |
+--------------------------------------------------------------------------------+
Tables which satisfy your search criteria are displayed as shown in Figure 224. If you invoked the Table List (T) select option from the list panel of the DBSPACE Search and List tool, then only tables in the selected DBSPACE are listed.
Select options can be invoked against listed tables. Enter a selection in the SEL field.
List options can be invoked against listed tables.
|The Table Synonyms List panel is shown in Figure 225.
|Figure 225. Table Synonyms List Panel
|+--------------------------------------------------------------------------------+ || mm/dd/yyyy Control Center hh:mm:ss | || *----------------------------- Table Synonyms -------------------------------* | || | Database => SQLDBA Table => M760595.COLTAB | | || | | | || | SEL USERID ALTNAME | | || | --- -------- ------------------ | | || | _ M760595 RAYCOL | | || | | | || | | | || | | | || | | | || | | | || | | | || | | | || | | | || | | | || | | | || | Select: D = Drop Synonym | | || | | | || | Page 1 of 1 | | || *---------------------------------------------------------------SQMSYNLST----* | || PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU | || | || | |+--------------------------------------------------------------------------------+
|The D option can be used to drop the synonyms listed. A confirmation |message is issued before the drop takes place. The user may cancel the |drop request.
PF key 6 allows viewing, adding, or changing the remarks column of a table in the catalog. Pressing PF6 causes panel SQMTLST3 to be displayed; see Figure 226.
Figure 226. Table Remarks Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *--------------------------- TABLE List Utility -----------------------------* | || Database => SQLDBA Dbspace => || || || || SEL CREATOR TNAME REMARKS || || -------- ------------------ ------------------------------------------ || || _ M760595 COLTAB Test Column table || || _ M760595 COST_TABLE || || _ M760595 DATACAPTURE || || _ M760595 INDEXTEST Index table for testing || || _ M760595 INVALID_IDS || || _ M760595 PLAN_TABLE || || _ M760595 PMPHDM || || _ M760595 REFERENCE_TABLE || || _ M760595 STRUCTURE_TABLE || || _ M760595 TABERROR || || || || Select: R = Reorg U = Upd ALL Stats D = DROP |L = DEL ROWS A = ADD Com || || List: I = INDX P = PKGS V = VIEWS C = COLS |RI = Ref Int Y = SYNONYMS || || || || Page 1 of 2 || | *---------------------------------------------------------------SQMTLST3-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU 6 TABLE DATA | | 7 Bkwd 8 Fwd | +--------------------------------------------------------------------------------+
The Select and List options are the same as those for the preceding Table list screen, with the additional select choice, (A), for adding or changing a table's REMARKS field.
| Note: | Since only 42 characters of a comment can be displayed, changing the text of a pre-existing, longer comment will result in truncation after the 42nd character. |
You can use the Index List Tool to list all indexes of a table selected using the Table List Tool. You can DROP or reorganize indexes displayed in the list by entering the appropriate selection code.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *--------------------------- INDEX List Utility -----------------------------* | || Database => SQLDBA Table => M760595.INDEXTEST | | || | | || SEL ICREATOR INAME TYPE RATIO CLUST COLNAMES | | || --- -------- ------------------ ---- ----- ----- ------------------------ | | || _ M760595 IXT1 I-D 10000 F +NAME,+OWNER, | | || | | || | | || | | || | | || | | || | | || | | || | | || | | || | | || | | || Select: D = Drop Index/Key/Constraint R= Reorganize Index/Key/Constraint | | || | | || Page 1 of 1 | | | *---------------------------------------------------------------SQMILIST-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU 6 INDEX DATA | | | +--------------------------------------------------------------------------------+
Possible values are:
Select options can be invoked against listed indexes.
PF key 6 presents the Index List screen with more detailed information about the indexes obtained from the catalog. The new screen is shown in Figure 228.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *--------------------------- INDEX List Utility -----------------------------* | || Database => SQLDBA Table => M760595.INDEXTEST | | || | | || SEL ICREATOR INAME LEAF IPFREE FIRSTCOUNT FULLCOUNT KEYLEN | | || --- -------- ------------------ ---- ------ ---------- --------- ------ | | || * M760595 IXT1 34 15 3813 4160 23 | | || | | || | | || | | || | | || | | || | | || | | || | | || | | || | | || | | || Select: D = Drop Index/Key/Constraint R =Reorganize Index/Key/Constraint | | || | | || Page 1 of 1 | | | *---------------------------------------------------------------SQMILST2-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU 6 INDEX LIST | | | +--------------------------------------------------------------------------------+
The Select options are the same as those of the Index List panel. Pressing F6 on this panel returns you to the Index List panel, Figure 227.
Use the View List Tool to list the views on a table selected from the Table List Tool. Any view displayed can be dropped or displayed by entering the appropriate selection code.
+--------------------------------------------------------------------------------+
| mm/dd/yyyy CONTROL CENTER hh:mm:ss |
| *---------------------------- VIEW List Utility -----------------------------* |
| | Database => SQLDBA Table => SQLDBA.EMPLOYEE | |
| | | |
| | SEL VCREATOR VIEWNAME VIEW TEXT | |
| | --- -------- ------------------ ------------------------------------------ | |
| | _ SQLDBA VEMPLP SELECT PHONENO, EMPNO FROM EMPLOYEE | |
| | _ SQLDBA VPHONE SELECT LASTNAME, FIRSTNME, MIDINIT, PHONEN | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | Select: D = Drop view, F = Full View Text display, |Y = Synonyms | |
| | | |
| | Page 1 of 1 | |
| *---------------------------------------------------------------SQMVLIST-----* |
| PF: 3 QUIT 4 EXIT 5 MAIN MENU |
| |
| |
+--------------------------------------------------------------------------------+
Select options can be invoked against listed views.
Output file is given file name SQMMENUL VIEW.
|Refer to Figure 225 for an example of the Table Synonyms list panel.
Figure 230. Example Output from Full View Text Display Option
CREATE VIEW VPHONE (LASTNAME, FIRSTNAME, MIDINITL, PHNUMBER, EMNUMBER,
DPNUMBER, DEPTNAME) AS SELECT LASTNAME, FIRSTNME, MIDINIT, PHONENO,
EMPNO, DEPTNO, DEPTNAME FROM EMPLOYEE,
DEPARTMENT WHERE WORKDEPT=DEPTNO
The Column List tool displays information about all of the columns in a table. It is reached by selecting C from the Table Search and List screen.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- COLUMNS List Utility ----------------------------* | | | Database => SQLDBA Table => SQLDBA.CUSTOMER_PRICE | | | | | | | | NAME TYPE LENGTH NULLS COLNO | | | | ------------------ ----------- --------- ------ ----- | | | | ADDED_BY CHAR 3 N 6 | | | | ADDED_DATE DATE 4 N 5 | | | | CHANGED_BY CHAR 3 N 8 | | | | CHANGED_DATE DATE 4 N 7 | | | | CUSTOMER_NO INTEGER 4 N 1 | | | | MODEL SMALLINT 2 N 3 | | | | RETAIL_PRICE_AMT DECIMAL 1794 N 4 | | | | SELL_LOT INTEGER 4 N 2 | | | | | | | | | | | | | | | | | | | | | | | | Page 1 of 1 | | | *---------------------------------------------------------------SQMCLIST-----* | | PF: 1 Help 3 QUIT | | | | | +--------------------------------------------------------------------------------+
The Table Referential Integrity (RI) tool is a comprehensive tool, which can be invoked from the Table List panel (Figure 224) of the Search List tool, |or a stand-alone EXEC, SQLRI, can be invoked from the CMS command |line. For more informaton about the stand-alone Table Referential |Integrity Tool, refer to Stand-Alone Referential Integrity Tool.
The Referential Integrity tool is reached through the Search List tool (from the Table List tool RI option), which can be invoked either from the SL option of the Utility Menu or from the stand alone exec SQMUTIL.
The Referential Integrity Summary panel is shown in Figure 232.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *---------------------- Referential Integrity Summary -----------------------* | || Database => SQLDBA Table => SQLDBA.DEPARTMENT | | || | | || Relationship of this table to Other Tables: | | || | | || PARENT OF STATUS DEPENDENT OF STATUS | | || ---------------------------- ------ --------------------------- ------ | | || SQLDBA.EMPLOYEE A SQLDBA.EMPLOYEE A | | || SQLDBA.PROJECT A | | || | | || | | || | | || | | || | | || | | || | | || | | || PRIMARY KEY STATUS: ACTIVE | | || UNIQUE CONSTRAINTS: 1 | | || | | |*---------------------------------------------------------------SQMREFLST-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU 6 PRIMARY KEY 7 FOREIGN KEY | | 8 DEPENDENT KEYS 9 UNIQUE CONSTRAINTS 10 FULL REPORT | +--------------------------------------------------------------------------------+
Select options are chosen by use of the PF keys. These are described below:
The remaining panels are discussed below.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *---------------------------- Primary Key Data ------------------------------* | || Database => SQLDBA Table => SQLDBA.DEPARTMENT | | || | | || NAME STATUS COLUMN NAMES TYPE LENGTH | | || ------------------ -------- ------------------ ----------- -------- | | || PKEYCI1YMLSBIXLG ACTIVE DEPTNO CHAR 3 | | || | | || | | || | | || | | || | | || | | || | | || | | || CLUSTER RATIO LEAF IPCTFREE FIRSTKEYCOUNT FULLKEYCOUNT | | || ------- ----- ---- -------- ------------- ------------ | | || F 10000 1 10 9 9 | | || | | || | | || | | |*---------------------------------------------------------------SQMPKLIST-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU | | 6 DROP 7 DEACTIVATE 8 DEACTIVATE ALL 9 ACTIVATE 10 ACTIVATE ALL | +--------------------------------------------------------------------------------+
Select options are chosen by use of the PF keys:
Figure 234. Foreign Keys Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *---------------------------- Foreign Key Data ------------------------------* | || Database => SQLDBA Table => SQLDBA.DEPARTMENT | | || | | || OPTION: FOREIGN KEYS OF THIS TABLE: | | || -- | | || KEY NAME : R_EMPLY1 STATUS: A | | || PARENT TABLE: SQLDBA.EMPLOYEE STATUS: A | | || DELETE RULE : SET NULL | | || COLUMN NAMES TYPE LENGTH | | || ------------------ ----------- -------- | | || MGRNO CHAR 6 | | || | | || | | || | | || | | || | | || | | || Select: D = Drop DE = Deactivate A = Activate | | || | | || Page 1 of 1 | | |*---------------------------------------------------------------SQMFKLIST- ---* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU | | | +--------------------------------------------------------------------------------+
There is one such screen displayed for each foreign key of the subject table.
Select options can be invoked for the foreign key. These are described below:
Figure 235. Dependent Keys Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*---------------------------- Dependent Key Data -----------------------------* | || Database => SQLDBA Table => SQLDBA.DEPARTMENT | | || | | || OPTION: DEPENDENTS OF THIS TABLE: | | || -- | | || TABLE NAME : SQLDBA.EMPLOYEE | | || KEY NAME : R_DEPT1 STATUS: A | | || DELETE RULE: SET NULL | | || COLUMN NAMES TYPE LENGTH | | || ------------------ ----------- -------- | | || WORKDEPT CHAR 3 | | || | | || | | || | | || | | || | | || | | || Select: D = Drop DE = Deactivate A = Activate | | || | | || Page 1 of 2 | | |*---------------------------------------------------------------SQMDKLIST-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU | | | +--------------------------------------------------------------------------------+
There is one such screen displayed for each dependent of the subject table (that is, each table which has a foreign key referencing the subject table's primary key). The display fields and the Select options are identical to those of the foreign keys panel shown in Figure 234.
Figure 236. Unique Constraint Panel
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *------------------------- Unique Constraint Data ---------------------------* | || Database => SQLDBA Table => SQLDBA.DEPARTMENT | | || | | || NAME STATUS COLUMN NAMES TYPE LENGTH | | || ------------------ -------- ------------------ ----------- -------- | | || DEPNAME ACTIVE DEPTNAME VARCHAR 36 | | || | | || | | || | | || | | || | | || | | || | | || | | || CLUSTER RATIO LEAF IPCTFREE FIRSTKEYCOUNT FULLKEYCOUNT | | || ------- ----- ---- -------- ------------- ------------ | | || C 10000 1 10 9 9 | | || | | || | | || Page 1 of 1 | | |*---------------------------------------------------------------SQMUQLIST-----* | |PF: 1 HELP 3 QUIT 4 EXIT 5 MAIN MENU | | 9 DROP 10 DEACTIVATE 11 ACTIVATE | +--------------------------------------------------------------------------------+
There is one such screen displayed for each unique constraint defined for the subject table.
The display fields are identical to those of the Primary Key panel (see Figure 233).
Select options are chosen by use of the PF keys. These are described below:
Displays a full report of the subject table and all its referential relations to and with other tables. An example of the report for the subject table is:
Figure 237. Full Referential Integrity Report
+--------------------------------------------------------------------------------+
| REFERENTIAL INTEGRITY REPORT |
| |
| Database: WMAVM1.PSNLDBA |
| 08/19/1998 09:08:39 |
| |
| |
| Table: SQLDBA.DEPARTMENT |
| ----------------------------------- |
| | PRIMARY KEY IS ACTIVE | |
| | ---> PRIMARY KEY COLUMNS ARE: | |
| | DEPTNO | |
| ----------------------------------- |
| |
| Relationship to Other Tables: |
| |
| PARENT OF: SQLDBA.EMPLOYEE STATUS: ACTIVE |
| SQLDBA.PROJECT STATUS: ACTIVE |
| |
| DEPENDENT OF: SQLDBA.EMPLOYEE STATUS: ACTIVE |
| foreign key: R_EMPLY1 delete rule: SET NULL |
| columns: MGRNO |
| |
| |
| |
+--------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------+
| Table: SQLDBA.EMPLOYEE |
| ----------------------------------- |
| | PRIMARY KEY IS ACTIVE | |
| | ---> PRIMARY KEY COLUMNS ARE: | |
| | EMPNO | |
| ----------------------------------- |
| |
| Relationship to Other Tables: |
| |
| PARENT OF: SQLDBA.DEPARTMENT STATUS: ACTIVE |
| SQLDBA.EMP_ACT STATUS: ACTIVE |
| SQLDBA.PROJECT STATUS: ACTIVE |
| |
| DEPENDENT OF: SQLDBA.DEPARTMENT STATUS: ACTIVE |
| foreign key: R_DEPT1 delete rule: SET NULL |
| columns: WORKDEPT |
| |
| |
| |
+--------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------+
| Table: SQLDBA.PROJECT |
| ----------------------------------- |
| | PRIMARY KEY IS ACTIVE | |
| | ---> PRIMARY KEY COLUMNS ARE: | |
| | PROJNO | |
| ----------------------------------- |
| |
| Relationship to Other Tables: |
| |
| PARENT OF: SQLDBA.PROJ_ACT STATUS: ACTIVE |
| |
| DEPENDENT OF: SQLDBA.DEPARTMENT STATUS: ACTIVE |
| foreign key: R_DEPT2 delete rule: RESTRICT |
| columns: DEPTNO |
| ******** |
| |
| DEPENDENT OF: SQLDBA.EMPLOYEE STATUS: ACTIVE |
| foreign key: R_EMPLY2 delete rule: SET NULL |
| columns: RESPEMP |
| |
| |
| |
| |
+--------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------+
| Table: SQLDBA.EMP_ACT |
| ----------------------------------- |
| | NO PRIMARY KEY DEFINED | |
| ----------------------------------- |
| |
| Relationship to Other Tables: |
| |
| |
| DEPENDENT OF: SQLDBA.EMPLOYEE STATUS: ACTIVE |
| foreign key: R_EMPLY3 delete rule: CASCADE |
| columns: EMPNO |
| ******** |
| |
| DEPENDENT OF: SQLDBA.PROJ_ACT STATUS: ACTIVE |
| foreign key: R_PROACT delete rule: RESTRICT |
| columns: PROJNO |
| columns: ACTNO |
| columns: EMSTDATE |
| |
| |
| |
| |
| |
+--------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------+
| Table: SQLDBA.PROJ_ACT |
| ----------------------------------- |
| | PRIMARY KEY IS ACTIVE | |
| | ---> PRIMARY KEY COLUMNS ARE: | |
| | PROJNO | |
| | ACTNO | |
| | ACSTDATE | |
| ----------------------------------- |
| |
| Relationship to Other Tables: |
| |
| PARENT OF: SQLDBA.EMP_ACT STATUS: ACTIVE |
| |
| DEPENDENT OF: SQLDBA.ACTIVITY STATUS: ACTIVE |
| foreign key: R_ACTIV delete rule: RESTRICT |
| columns: ACTNO |
| ******** |
| |
| DEPENDENT OF: SQLDBA.PROJECT STATUS: ACTIVE |
| foreign key: R_PROJ2 delete rule: RESTRICT |
| columns: PROJNO |
| |
| |
+--------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------+
| Table: SQLDBA.ACTIVITY |
| ----------------------------------- |
| | PRIMARY KEY IS ACTIVE | |
| | ---> PRIMARY KEY COLUMNS ARE: | |
| | ACTNO | |
| ----------------------------------- |
| |
| Relationship to Other Tables: |
| |
| PARENT OF: SQLDBA.PROJ_ACT STATUS: ACTIVE |
| |
| |
|**************** END OF REPORT ********************* |
+--------------------------------------------------------------------------------+
The report starts with the subject table. It lists the table name and whether a primary key has been defined for the table. If so, its status is shown. Next, it lists those tables the subject table is a parent of and their status. Then it lists those tables of which the subject table is a dependent and their status.
The report then proceeds to list the same information for each table involved in a referential relationship with the subject table, printing the same information as for the subject table. If any new tables are found, the same information on them will be printed. This proceeds until all the tables involved have had their information printed.
The report is presented as an XEDIT screen and filed under the name |xxxxxxxx RIREPORT, where xxxxxxxx is the first 8 |characters of the table name.
|A stand-alone EXEC, SQLRI, is executable from the CMS command line. |It provides a full report of the referential integrity characteristics of a |specific table without starting the Search List tool. The stand-alone |referential integrity tool generates the full referential integrity report as |described in the Full Report Option Step 6 and Full Referential Integrity Report.
|The report starts with the subject table. It lists the table name |and whether a primary key has been defined for the table. If so, its |status is shown. It then lists the tables for which the subject table |is a parent of and their status, and lists the tables for which the subject |table is a referential dependent of and their status. Also included in |this latter list is the name of the foreign key and its delete rule and the |column names which make up the foreign key.
|The report then lists the same information for each table involved in a |referential relationship with the subject table, printing the same information |as for the subject table. If any new tables are found, the same |information for them will be printed. This process is repeated until |all tables involved have had their information printed.
|The report is presented as an XEDIT screen for viewing and then filed under |the name nnnnnnnn RIREPORT (where nnnnnnnn is the first |eight characters of the table name). Viewing the report is the default |option. If immediate viewing of the report is not wanted, an optional |parameter can be supplied in the invocation syntax (see Invocation). This option can thus make the tool useful for |running on a disconnected machine, as a scheduled job, or one that can be |called from other EXECs. In addition, a "SENDTO = userid" |option is provided if it is desired to send the report to another user ID than |the one running the exec.
|The stand-alone SQLRI EXEC can be executed from the CMS |command line with the database name (or machine name) followed by the table |name as arguments. The table name should be entered as |creator.tablename, with or without quotes around |each part (for example, |'creator'.'tablename'). |Single or double quotes may be used. A sample invocation is:
|SQLRI SQLDBA SQLDBA.ACTIVITY
|This would create the report and present it for viewing at the user's |terminal. Another example is:
|SQLRI SQLDBA SQLDBA.ACTIVITY (NODISPlay
|This would create the report and store it in a file on the user's |A-disk without presenting it for viewing. Another example is:
|SQLRI SQLDBA SQLDBA.ACTIVITY (NODISP SENDTO=someuser [AT somenode]
|This would create the report, leave the file on the user's A-disk |without presenting it for viewing, and send the file to the specified user ID |(someuser). The AT somenode portion is |optional; the node defaults to the one on which the SQLRI EXEC is running |if not specified.