Reporting Dynamically From System Tables

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. Retrieves information about applications and the files within them.
• SYSCOLUM. Retrieves information about tables and their columns.
• SYSDEFFN. Retrieves DEFINE FUNCTION information.
• SYSERR. Retrieves information about error message files.
• SYSFILES. Retrieves directory information.
• SYSIMP. Retrieves impact analysis information.
• SYSINDEX. Retrieves index information.
• SYSKEYS. Retrieves information about keys.
• SYSRPDIR. Retrieves information about all available FOCEXECs in your application path.
• SYSSET. Retrieves information about SET commands and global Dialogue Manager variables.
• SYSSQLOP. Retrieves function information.
• SYSTABLE. Retrieves table information.
• SYSVDTP. Retrieves data type information for Relational Adapters.

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

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 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.

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 sysdeffn synonym retrieves information about DEFINE FUNCTIONs, including function names, arguments, argument formats, function fields, and descriptions.

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 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.

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 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.

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 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.

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 sysindex synonym retrieves information about indexes defined in a synonym.

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 syskeys synonym retrieves information about keys defined in a synonym.

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 sysrpdir synonym retrieves all available FOCEXECs in your application path.

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 sysset synonym retrieves information about SET parameters, their allowed values, and their default 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 syssqlop synonym retrieves information about functions, their descriptions, parameters, syntax, and adapter category.

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 systable synonym retrieves information about synonyms in your path, including type of synonym, creator, number of columns, keys, record length, and description.

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 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.

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