Reporting Dynamically From System Tables

In this section:

You can issue report requests against a set of synonyms that dynamically gather information about your environment, including its applications, files, columns, directories, tables, indexes, and keys. You can also retrieve information about functions, SET parameters, and error files. These synonyms reside in the catalog directory under the server home directory and have the suffix FMI (FOCUS Metadata Interface).

Overview of System Table Synonyms

Each FMI synonym retrieves information about a specific set of files in your environment. If you examine an FMI Master File, the REMARKS and DESCRIPTION attributes document what data will be returned by each system table and each column within the system table.

Note: The system table synonyms may change in future releases. Therefore, you should not design applications that depend on the structure of the system table synonyms.

For example, following is a version of the SYSFILES Master File, which, by default, retrieves information about Master Files in your application path.

$------------------------------------------------------------------------------------$
$ Copyright (c) 2013 TIBCO, Inc. All rights reserved. @MFSM_NOPROLOG@ $
$------------------------------------------------------------------------------------$
$--CAN BE USED TO RETRIEVE DIRECTORY INFO - USE THE FOLLOWING TO DEFINE DIRECTORY
$--SQL FMI SET SYSFILES EDASYNM
$--SQL FMI SET SYSFILES FOCEXEC
FILE=SYSFILES, SUFFIX=FMI, REMARKS='Metadata: Directory information', $
SEGMENT=FILE,SEGTYPE=S0,$
  FIELD=FILENAME  ,      ,A64  ,A64B,    DESC='MEMBER  NAME                            ',$
  FIELD=LGNAME    ,      ,A8   ,A8B ,    DESC='LOGICAL NAME                            ',$
  FIELD=PHNAME    ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  1ST PART                 ',$
  FIELD=PHNAME2   ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  2ND PART                 ',$
  FIELD=PHNAME3   ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  ETC..                    ',$
  FIELD=PHNAME4   ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  The whole length up to 512 bytes                         ',$
  FIELD=PHNAME5   ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  The last part is 32 but can be up                         ',$
  FIELD=PHNAME6   ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  to 44 because of nlscut of ',$
  FIELD=PHNAME7   ,      ,A80  ,A80B,    DESC='PHYSICAL NAME  previous 6 parts (2 bytes per part)',$
  FIELD=VERSION   ,      ,I4   ,I1,     DESC='MF:VERSION                              ',$
  FIELD=MOD       ,      ,I4   ,I1,     DESC='MF:MODIFICATION NUMBER                  ',$
  FIELD=LINECNT   ,      ,I4   ,I2,     DESC='MF:CURRENT LINE COUNTER.                ',$
  FIELD=DATE      ,      ,A8   ,A8B,     DESC='IBI DATE (DD/MM/YY)                     ',$
  FIELD=TIME      ,      ,A8   ,A8B,     DESC='IBI TIME (HH.MM.SS)                     ',$
  FIELD=USERID    ,      ,A100 ,A100B,   DESC='MF: LAST USER WHO CHANGED. UNIX/NT:owner',$
  FIELD=SIZE      ,      ,I11  ,I4,     DESC='UNIX/NT:SIZE IN BYTES.                  ',$
  FIELD=EXTENSION ,      ,A3   ,A3B,     DESC='ACCEPTED SHORT EXTENSION FOR FILE       ',$

A list of some of the most useful FMI synonyms follows. You can generate a list of system table synonyms by issuing a request against the systable synonym.

SYSAPPS: Reporting on Applications and Application Files

The sysapps synonym retrieves information about applications and the files within them.

Example: Retrieving Application and File Information

The following request retrieves the application name and path and the file name, extension, suffix, keys, and number of segments for Master Files in the ibisamp application, where the file names start with the letters a through g.

TABLE FILE SYSAPPS
PRINT APPNAME AS App APPLOC AS Path  
FNAME AS 'File,Name' SUFFIX AS 'File,Type' KEYS 
NUMSEG AS '# of,Segments'
WHERE APPNAME EQ 'ibisamp' 
WHERE FEXT EQ 'mas'
WHERE FQNAME LT 'C:\ibi\apps\ibisamp\hday'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSCOLUM: Reporting on Tables and Their Columns

The syscolum synonym retrieves table information, including table names creator names, segment names and numbers, segment roles in a dimension view or business view, column names, and column data types. Use it to report on data sources referenced in a Master File.

Example: Retrieving Table and Column Information

The following request retrieves table and column information from tables whose table names start with the characters wf_.

TABLE FILE SYSCOLUM
PRINT TBNAME AS Table TBTYPE AS Suffix NAME AS Field,Name COLTYPE AS Data,Type ACTUAL AS Format
WHERE TBNAME LIKE 'wf_%'
WHERE RECORDLIMIT EQ 20
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSDEFFN: Reporting on DEFINE FUNCTIONS

The sysdeffn synonym retrieves information about DEFINE FUNCTIONs, including function names, arguments, argument formats, function fields, and descriptions.

Example: Retrieving DEFINE FUNCTION Information

The following request retrieves DEFINE FUNCTION names, arguments, and argument formats.

TABLE FILE SYSDEFFN
PRINT DFNAME AS Function,Name ARGNAME AS Argument,Name ARGFORMAT AS Argument,Format
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSERR: Reporting on Error Message Files

The syserr synonym retrieves error file names, the lowest and highest message numbers in each file, message and explanation text, message number, whether the message is a warning, whether the message is informational, and whether the line number is displayed in a procedure.

Example: Retrieving Error Message File Information

The following request retrieves message text and explanations.

TABLE FILE SYSERR
BY ERRNUM NOPRINT SUBHEAD
" <ERRTEXT  "
 "<ERRLINE1 "
 "<ERRLINE2 " 
 "<ERRLINE3 " 
 "<ERRLINE4 " 
WHERE RECORDLIMIT EQ 7
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSFILES: Reporting on Metadata or Procedure Directory Information

The sysfiles synonym retrieves Master Files or FOCEXEC files in your application path. By default, sysfiles retrieves a list of Master Files and their properties. The SET SYSFILES command determines which type of files are retrieved.

The syntax is

SQL FMI SET SYSFILES {EDASYNM|FOCEXEC}

where:

EDASYNM

Retrieves information about Master Files. This is the default value.

FOCEXEC

Retrieves information about procedure files.

Example: Retrieving Master File Information

The following request retrieves the file name, extension, and path for the Master File names that start with the letters a through h in the ibisamp application directory.

TABLE FILE SYSFILES
PRINT FILENAME AS File
EXTENSION AS Extension
PHNAME AS Path
WHERE PHNAME LIKE 'ibisamp/%'
WHERE FILENAME LT 'i'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSIMP: Reporting on Impact Analysis Information

The sysimp synonym retrieves information about where files reside and where they are referenced. Sysimp contains a segment for caller information and a child segment for the files called by each caller.

Example: Retrieving Impact Analysis Information

The following request retrieves the names, types, and descriptions of caller files whose names start with the letters s through z in the ibisamp application and the names, types, and descriptions of the files they called.

TABLE FILE SYSIMP
PRINT CTYPE AS Caller,Type CDESCRIPTION AS Description 
RFILE AS Called,Name
RPT_TYPE AS Called,Type
RLINENUM AS Line RUSAGE AS 'Used In'  
REXTENSION AS Extension RDESCRIPTION AS Description
BY CFILE/A15  AS Caller,Name
WHERE CAPPLICATION EQ 'ibisamp'
WHERE CFILE GE 's'
ON TABLE SET PAGE NOLEAD
ON TABLE SET SHOWBLANKS ON
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The partial output is shown in the following image.

SYSINDEX: Reporting on Index Information

The sysindex synonym retrieves information about indexes defined in a synonym.

Example: Retrieving Index Information

The following request retrieves index field names for files that start with the characters gg.

TABLE FILE SYSINDEX
PRINT NAME AS Index 
BY TBNAME AS File
WHERE TBNAME LIKE 'gg%'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSKEYS: Reporting on Key Information

The syskeys synonym retrieves information about keys defined in a synonym.

Example: Retrieving Key Information

The following request retrieves key field names and sort order for files that start with the characters gg.

TABLE FILE SYSKEYS
PRINT IXNAME AS Key ORDERING AS Order
BY TBNAME AS File
WHERE TBNAME LIKE 'gg%'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSRPDIR: Reporting on Stored Procedures

The sysrpdir synonym retrieves all available FOCEXECs in your application path.

Example: Retrieving Stored Procedure Information

The following request retrieves procedures that start with the characters wf_.

TABLE FILE SYSRPDIR
PRINT RPC_TYPE AS Procedure,Type
BY RPC_NAME AS Procedure,Name
WHERE RPC_NAME LIKE 'wf_%'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSSET: Reporting on SET Parameters

The sysset synonym retrieves information about SET parameters, their allowed values, and their default values.

Example: Retrieving Information About SET Parameters

The following request displays SET parameters that start with the letter D, along with their descriptions and values.

TABLE FILE SYSSET
PRINT SETDESC CURR_VALUE VALUE IS_DEFAULT
BY SETNAME AS Set,Name
WHERE SETNAME GE 'D' AND SETNAME LT 'E'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The partial output is shown in the following image.

SYSSQLOP: Reporting on Function Information

The syssqlop synonym retrieves information about functions, their descriptions, parameters, syntax, and adapter category.

Example: Retrieving Function Descriptions and Syntax

The following request retrieves the names, descriptions, and syntax for the legacy functions whose names begin with the letters A and B.

TABLE FILE SYSSQLOP
SUM FUNCTION_DESC FUNCTION_SYNTAX 
BY FUNCTION
WHERE CATEGORY LIKE 'L%'
WHERE FUNCTION LE 'C'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SYSTABLE: Reporting on Table Information

The systable synonym retrieves information about synonyms in your path, including type of synonym, creator, number of columns, keys, record length, and description.

Example: Retrieving A List of FMI Synonyms

The following request retrieves the names, descriptions, and attributes of the system table synonyms.

TABLE FILE SYSTABLE
PRINT TBTYPE REMARKS COLCOUNT RECLENGTH KEYCOLUMNS 
BY NAME 
WHERE NAME LIKE 'sys%'
WHERE TBTYPE EQ 'FMI'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Reporting on Data Types

The sysvdtp synonym retrieves data types and their corresponding USAGE and ACTUAL formats for the SQL Adapters and for fixed sequential data sources. It is not an FMI synonym, but retrieves the data type information from a delimited sequential file.

Example: Retrieving Data Types for the Adapter for MySQL

The following request retrieves data type information for the Adapter for MySQL

TABLE FILE SYSVDTP
PRINT DATA_TYPE_CATEGORY 
VENDOR_DATA_TYPES
TYPE_RANGE
SERVER_USAGE_CATEGORY
SERVER_USAGE
SERVER_ACTUAL
REMARKS
BY ADAPTER
WHERE SUFFIX EQ 'SQLMYSQL' 
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.