Help for Foxhound 4.0.4740a

Table of Contents     [RisingRoad]


Foxhound 4 

2. The Display Schema Page

The Display Schema page shows database and table-level "curiosities" and database option and property values as well as CREATE statements for all the tables and views.

2.1 The Display Schema Menu

2.2 Facts & Figures

2.3 Database-level Curiosities

2.4 Table-level Curiosities

2.5 The Tables List

2.6 The Columns List

2.7 The CREATE TABLE Display

2.8 The CREATE VIEW Display

2.9 The SET OPTION Display

2.10 The Engine and Database Properties Display


Foxhound 4 2. The Display Schema Page 
2.1 The Display Schema Menu

The Back to Menu link displays the main Foxhound menu page in the current browser window or tab.

The DSN: / Connection String: title shows the name of the DSN or Connection String that you used to connect to the target database. This identifies the "current target database", a term used in this Help.

The New Menu link opens another Foxhound menu page in a new browser window or tab.

The Monitor link opens the Monitor Database page in a new browser window or tab. If a sampling session already exists for the current target database, sample data from that session will be displayed. If not, Foxhound will start a new sampling session and display that data.

The History link opens the History page in a new browser window or tab if a sampling session already exists for the current target database.

The Foxhound Options link opens the Foxhound Options page in a new browser window or tab.

The Monitor Options link opens the Monitor Options page in a new browser window or tab.

The button is a context-sensitive link to this Help topic. This button appears in many locations, each of them a link to a different Help topic in this frame.

Tip: To hide the Help for every new page, see the Show Help section on the Foxhound Options page.

The About link opens the About Foxhound page in a new browser window or tab.

The Top link is a shortcut that scrolls the left hand frame to the top.

The Tables: link scrolls the left hand frame to the list of table names.

The table name letter links A B C... scroll the left hand frame to different sections in the table name list.

The Columns: link scrolls the left hand frame to the list of column names.

The column name letter links A B C... scroll the left hand frame to different sections in the column name list.

The Options link displays the current SET OPTION PUBLIC values in the right hand frame.

The Properties link displays the current engine and database properties in the right hand frame.

The Checkpoint and redisplay link reconnects to the current target database, sends a CHECKPOINT command to that database and then redisplays the entire Display Schema page. This forces Foxhound to display fresh data for everything; e.g., the database file version is only up to date when Foxhound connects to the target database, and row counts are only up to date when a checkpoint is taken. Other information is automatically refreshed more frequently; e.g., the CREATE TABLE display in the right hand frame is always up to date whenever it is displayed, and there is no need to use the Checkpoint and redisplay link to see up to date table schema.

Do not use the Checkpoint and redisplay link on a busy production database unless you are sure that forcing a checkpoint won't hurt performance.


Foxhound 4 2. The Display Schema Page 
2.2 Facts & Figures

The Print link lets you print the entire left hand frame: all the Facts & Figures, Curiosities and table and column name lists.

You can click on the Show Facts & Figures and Hide Facts & Figures links to show and hide this section.

The Facts & Figures title line names the target database and shows when this information was gathered for display.

The DSN: or Connection string name: identifies the target database according to how Foxhound made its connection.

The Server version: shows the version and build number of the SQL Anywhere engine running the target database.

The Started timestamp shows when the target database was started.

The Database file version: shows the version and build number of the SQL Anywhere software that was used to initialize the target database file.

For database files created with SQL Anywhere Version 9 and higher this field is based on the SYSHISTORY table. For database files created with earlier versions (5.5 through 8) Foxhound uses a proprietary process to determine the value to display. For early versions and for databases that have had the upgrade utility run on them this value may be somewhat imprecise.

The Created timestamp shows when the target database file was initialized.

Server name: is the currently assigned network server name.

Database name: is the currently assigned network database name.

Machine name: is the name assigned to computer running the database server.

The Server command line options: shows what options were used by the database server when it was started.

Server edition: shows the server licensing details as listed in the engine ServerEdition property.

Server type: shows what kind of database server is being run: Network, Personal or Limited Desktop Runtime. This information is based on the IsNetworkServer and IsRuntimeServer engine properties.

Processor type: is the kind of CPU used to run the database server.

Number of processors available: is the number of CPUs available on the computer running the database server.

Number of processors used: is the number of CPUs actually being used by the database server.

O/S: shows what operating system is being used by the computer running the database server.

Current cache: is the amount of RAM currently being used for the database cache.

The min amount is the lower limit on the size of the database cache.

The peak amount is the largest size actually reached by the database cache.

The max amount is the upper limit on the size of the database cache.

Multi-programming level: is the -gn setting for the database server.

The Client address backtrace: shows the result of network trace back from the computer running target database back to the computer running Foxhound. If you see one of the following values, it means Foxhound is running on the same computer as the target database:

   127.0.0.1
   ::1
   NA

The DBSPACE section describes each physical file associated with the target database: DBSPACE name, disk file size, free space inside the file, free space on the drive, how badly fragmented the file is, and the full file specification. Note that the Free Space On Drive will be the same for all files that are on the same drive.

The data in the DBSPACE section is based on the sa_disk_free_space stored procedure and the FileSize, FreePages, DBFileFragments, File, LogName, LogMirrorName and TempFileName properties.

The section starting with page size describes various counts and sizes: page size, dbspaces, tables, rows, foreign keys, disk space, columns, and column data types.

The total space amount is made up of disk space used to store table data pages, table extension pages and index pages.

The section starting with permanent (base) tables shows how many and what kind of tables, views and indexes are defined in the database.


Foxhound 4 2. The Display Schema Page 
2.3 Database-level Curiosities

You can click on the Show Curiosities and Hide Curiosities links to show and hide both Curiosities sections.

The Database-level Curiosities title line names the target database and shows when this information was gathered for display.

Each database-level curiosity (interesting fact or possible problem) is shown on its own line.

Here's the list of possible database-level curiosities:

No foreign keys
No transaction log (dbinit -n)
No mirror log (no dbinit -m file)
DB, log and temp files all on same drive
DB and log files are on same drive
DB and temp files are on same drive 
Temp and log files are on same drive
A UNC specification is in use for the DB file
A UNC specification is in use for the transaction log file
A UNC specification is in use for the temporary file
Blank padding for comparisons (dbinit -b)
Case sensitive comparisons (dbinit -c)
Global Checksums are disabled (dbinit -s- was specified)
More views than base tables
No clustered indexes
No secondary indexes or unique constraints
No explicit PCTFREE settings
Database file version a.b.c.d -- different from engine version a.b.c.d
More than one dbspace nn -- for tables and indexes
Few tables with foreign keys    xx%    -- 25% or fewer
Many file fragments    nnn    -- 100 or more in database and temporary files
Small maximum cache relative to database size    -ch nnnK    -- nn% of nnnK (10% or smaller)
Engine page size > Database page size
Database page size    nK    -- different from 4K
Automatic multiprogramming level tuning is turned off
Multiprogramming level    -gn nnn    -- different from default 20
All tables are empty
Most tables are empty    nn%    -- more than 50%
Many NULL columns    nn%    -- 25% or more
Column names with differing data types    nnn    -- see "differs:" in Columns
SET OPTION PUBLIC.xxx    = 'yyy'   - different from default 'zzz'
Large transaction log file  xxM -- larger than data files
Largest tables and materialized views ( xx% of the rows and yy% of the total space)...

The Largest tables list shows the set of tables that contain at least 80% of the rows in the database and use at least 80% of the disk space.


Foxhound 4 2. The Display Schema Page 
2.4 Table-level Curiosities

The Table-level Curiosities title line names the target database and shows when this information was gathered for display.

Each table-level curiosity (interesting fact or possible problem) is shown on its own line, ordered by table name.

Here's the list of possible table-level curiosities:

ttt    -- no primary key or unique constraint, but does have a unique index
ttt    -- no primary key, unique constraint or unique index
ttt    -- materialized view with no indexes
ttt    -- self-referencing foreign key relationship (fishhook)
ttt    -- possibly invalid view (no column definitions in SYSCOLUMN)
ttt    -- cascading foreign key action (CASCADE or SET)
ttt    -- clustered primary key index
ttt    -- clustered foreign key index
ttt    -- clustered unique constraint index
ttt    -- clustered secondary index
ttt    nnn    -- most rows in a base table
ttt    nnn    -- most bytes per row in a base table
ttt    nnK    -- most total space in a base table
ttt    nnK    -- most table space in a base table
ttt    nnK    -- most extension space in a base table
ttt    nnK    -- most index space in a base table
ttt    nnn    -- most columns in a base table
ttt    nnn    -- most indexes for a base table
ttt    nnn    -- most parent relationships
ttt    nnn    -- most child relationships
ttt    nnn    -- most rows in a materialized view
ttt    nnn    -- most bytes per row in a materialized view
ttt    nnK    -- most total space in a materialized view
ttt    nnK    -- most table space in a materialized view
ttt    nnK    -- most extension space in a materialized view
ttt    nnK    -- most index space in a materialized view
ttt    nnn    -- most columns in a materialized view
ttt    nnn    -- most indexes for a materialized view
ttt    nnn    -- most columns in a proxy table
ttt    nnn    -- most columns in a global temporary table
ttt    nnn    -- most columns in a shared global temporary table
ttt    nnn    -- most columns in a view
ttt    nnn    -- large number of bytes per row in a table (500 or more)
ttt    nnn    -- large number of columns in a table (50 or more)
ttt    nnn    -- large number of indexes for a table (10 or more)
ttt    nnn    -- large number of secondary indexes for a table (5 or more)
ttt    nnn    -- large number of columns in the primary key (5 or more)
ttt    nnn    -- large number of columns in a foreign key (5 or more)
ttt    nnn    -- large number of columns in a secondary index for a table (5 or more)
ttt    nnn    -- large number of parent relationships (5 or more)
ttt    nnn    -- large number of child relationships (10 or more)
ttt    nnn    -- large number of bytes per row in a materialized view (500 or more)
ttt    nnn    -- large number of columns in a materialized view (50 or more)
ttt    nnn    -- large number of indexes for a materialized view (10 or more)
ttt    nnn    -- large number of secondary indexes for a materialized view (5 or more)
ttt    nnn    -- large number of columns in a secondary index for a materialized view (5 or more)
ttt    nn%    -- large fraction of total rows in a table (10% or more) 
ttt    nn%    -- large fraction of total space for a table (10% or more)           
ttt    nn%    -- large fraction of total table space for a table (10% or more)    
ttt    nn%    -- large fraction of total extension space for a table (10% or more) 
ttt    nn%    -- large fraction of total index space for a table (10% or more)   
ttt    nn%    -- large fraction of total rows in a materialized view (10% or more) 
ttt    nn%    -- large fraction of total space for a materialized view (10% or more)           
ttt    nn%    -- large fraction of total table space for a materialized view (10% or more)    
ttt    nn%    -- large fraction of total extension space for a materialized view (10% or more) 
ttt    nn%    -- large fraction of total index space for a materialized view (10% or more)     
ttt    ddd    -- different dbspace for table
ttt    ddd    -- different dbspace for index
ttt    nn%    -- most index space relative to table space in a base table
ttt    nn%    -- most index space relative to table space in a materialized view
ttt    nn%    -- large amount of index space relative to table space (80% or more)
ttt    iii    -- duplicate [clustered] unique index      - matches [clustered] primary key index
ttt    iii    -- duplicate [clustered] unique constraint - matches [clustered] primary key index
ttt    iii    -- duplicate [clustered] index             - matches [clustered] primary key index
ttt    iii    -- duplicate [clustered] unique index      - matches [clustered] foreign key index
ttt    iii    -- duplicate [clustered] unique constraint - matches [clustered] foreign key index
ttt    iii    -- duplicate [clustered] index             - matches [clustered] foreign key index
ttt    iii    -- duplicate [clustered] unique index      - matches [clustered] unique index
ttt    iii    -- duplicate [clustered] unique constraint - matches [clustered] unique index
ttt    iii    -- duplicate [clustered] index             - matches [clustered] unique index
ttt    iii    -- duplicate [clustered] unique index      - matches [clustered] unique constraint
ttt    iii    -- duplicate [clustered] unique constraint - matches [clustered] unique constraint
ttt    iii    -- duplicate [clustered] index             - matches [clustered] unique constraint
ttt    iii    -- duplicate [clustered] unique index      - matches [clustered] index
ttt    iii    -- duplicate [clustered] unique constraint - matches [clustered] index
ttt    iii    -- duplicate [clustered] index             - matches [clustered] index
ttt    nnn    -- reserved words used as names (see "quotes" in table definition)
ttt    nn%    -- small PCTFREE specified (10% or less)
ttt    nn%    -- large PCTFREE specified (90% or more)
ttt    ccc    -- UNIQUEIDENTIFIER primary key column


Foxhound 4 2. The Display Schema Page 
2.5 The Tables List

The Tables title line names the target database and shows when this information was gathered for display.

The table names are listed alphabetically, and the number of rows shown for each table is up to date at the most recent CHECKPOINT performed on the target database.

The bytes per row amount is based on the disk space used for table data, extension and index pages. It doesn't include free pages in the database, but it does count free space in pages allocated to this table. As such, this number is usually larger, sometimes quite a bit larger, than the average number of bytes a query might return. To avoid showing inflated numbers for very small tables, the bytes per row is only shown for tables with at least 2 data or 2 extension pages.


Foxhound 4 2. The Display Schema Page 
2.6 The Columns List

The Columns title line names the target database and shows when this information was gathered for display.

The columns list is ordered alphabetically by column name and table name. The base data type of each column is shown (no domain names), and differs: is used to highlight any data type differences among columns with the same name.


Foxhound 4 2. The Display Schema Page 
2.7 The CREATE TABLE Display

Each table is shown in valid CREATE TABLE syntax, suitable for copy and paste. Warning messages may appear at the top; for more information click on the Note: link.

The title line includes following fields:

The CREATE TABLE statement includes the row count and disk space used for table data, extension and index pages.

Note: For proxy tables, no information about row counts, disk space, constraints or indexes is shown.

The bytes per row amount is based on the disk space used for table data, extension and index pages. It doesn't include free pages in the database, but it does count free space in pages allocated to this table. As such, this number is usually larger, sometimes quite a bit larger, than the average number of bytes a query might return. To avoid showing inflated numbers for very small tables, the bytes per row is only shown for tables with at least 2 data or 2 extension pages.

Each column is shown with it's base data type, and if a domain was used it is shown as a "-- domain ..." comment to the right.

Some columns have an "eyecatcher" comment /* PK FK U X */:

The PRIMARY KEY constraint includes a comment showing how much disk space is used by the corresponding index.

The Parents of list contains direct links to this table's referential integrity parents: i.e., other tables that are named in this table's FOREIGN KEY constraints.

The Children list contains direct links to this table's referential integrity children: i.e., other tables that name this table in their FOREIGN KEY constraints.

ALTER TABLE ... FOREIGN KEY statements for each referential integrity constraint defined on this table. These statements include comments showing how much disk space is used by the corresponding indexes.

CREATE INDEX statements are shown for each index defined on this table, not including indexes that are created for each primary and foreign key. These statements include comments showing how much disk space is used by the indexes.


Foxhound 4 2. The Display Schema Page 
2.8 The CREATE VIEW Display

Each view is shown in valid CREATE VIEW syntax, suitable for copy and paste.

The title line includes following fields:

The -- CREATE VIEW comment section shows the view column names and base data types as stored in the SQL Anywhere catalog tables. This section is generated and formatted by Foxhound.

The CREATE VIEW statement shows the original SQL statement that was used to create the view. This section is displayed as-is, with its original formatting.


Foxhound 4 2. The Display Schema Page 
2.9 The SET OPTION Display

The SET OPTION display lets you see at a glance which database options have been changed from the defaults.

The Options title line includes following fields:

The Options with non-default values section shows options whose current values are different from the defaults defined for a recent build of SQL Anywhere. Recently-defined default values are used for the comparisons to take advantage of improvements made over the years. For old databases this often results in several differences showing NULL as the current value for modern options that don't exist in the target database at all.

The Full set of options section shows the current values of all the options.


Foxhound 4 2. The Display Schema Page 
2.10 The Engine and Database Properties Display

The properties display gives a snapshot of everything returned by calls to sa_eng_properties() and sa_db_properties().

The Properties title line includes following fields:

The Engine Properties section shows everything returned by a call to sa_eng_properties(), with the property description on the far right.

The Database Properties section shows everything returned by a call to sa_db_properties() for the target database, with the property description on the far right.

Foxhound 4 2. The Display Schema Page