Help for Foxhound 4.0.4740a

Table of Contents     [RisingRoad]

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

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.

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.

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.

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

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.

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.

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 table owner and name,
• the SYSTABLE.table_id number which is used as the *.DAT file name when dbunload is run,
• the DSN or connection string name identifying the target database,
• the date and time Foxhound produced this display, and
• a Print link you can use to print the right frame.

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 */:

• PK -- this column is part of the PRIMARY KEY constraint defined later in the CREATE TABLE statement,
• FK -- this column is part of one or more FOREIGN KEY constraints defined later as ALTER TABLE statement(s),
• U -- this column is part of one or more unique indexes, defined later as either UNIQUE constraint(s) or CREATE INDEX statement(s), and
• X -- this column is part of one or more non-unique indexes defined later as CREATE INDEX statement(s).

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.

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

The title line includes following fields:

• The view owner and name,
• the SYSTABLE.table_id number for the view,
• the DSN or connection string name identifying the target database,
• the date and time Foxhound produced this display, and
• a Print link you can use to print the right frame.

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.

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 DSN or connection string name identifying the target database,
• the date and time Foxhound produced this display, and
• a Print link you can use to print the right frame.

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.

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 DSN or connection string name identifying the target database,
• the date and time Foxhound produced this display, and
• a Print link you can use to print the right frame.

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.