Data Warehouse Center Application Integration Guide

Using SQL to access metadata

You can use SQL to extract metadata directly from the database tables that make up the information catalog; this section provides examples.

To determine what object type definitions exist in the information catalog, enter the following SQL statement:
```
SELECT OBJTYPID, DPNAME, NAME, CREATOR, PTNAME FROM FLG.OBJTYREG
```
This statement returns the following information:

OBJTYPID
Internal identifier for the object type
DPNAME
Object type name
NAME
External object type name
CREATOR,PTNAME
The table (object instance table) where object instances of that type are stored
To determine the property names for a specific object type after you determine the object type ID (from step 1), enter the following SQL statement:
```
SELECT  PHYPRPNM, PROPNAME, DATATYPE, LENGTH, OPTIONS, UUISEQNO,
  PROPSEQ FROM FLG.PROPERTY WHERE OBJTYPID = 'object_type_ID' 
  ORDER BY PROPSEQ
```
This statement returns the following information (in the order that the properties were created):

PHYPRPNM
Physical column name in the object instance table that maps to an object type property
PROPNAME
Business name of the property
DATATYPE
Data type of the property
LENGTH
Length of the property
OPTIONS
Indicates whether a value is required for this property in the object instance
UUISEQNO
UUI indicator, and sequence number if not 0
PROPSEQ
The order that the properties were added to the properties table
To find an instance of a specific object type after you determine the physical tables where the object is stored (from step1) and the properties that you want (from step 2), enter the following SQL statement:
```
SELECT OBJTYPID, INSTIDNT, NAME,phyprpnm1,phyprpnm2...
  FROM creator.ptname
  WHERE phyprpnm LIKE '%search_criteria%'
```
This statement returns the following information:

OBJTYPID
Internal identifier for the object type
INSTIDNT
Internal identifier for an instance of this object type
phyprpnm1
Value for the property specified in the SELECT statement
phyprpnm2
Value for the property specified in the SELECT statement

In addition, you must enter the following SELECT statement to retrieve any property values that are of the data type long variable character (LONG VARCHAR):
```
SELECT PHYPRPNM, ODESC FROM FLG.OVERDESC
  WHERE OBJTYPID = object_type_ID
  AND INSTIDNT = object_instance_ID
  ORDER BY SEQNO
```
Where object_type_ID and object_instance_ID are the values that you obtained after you generated the SELECT statement in step 3. This statement returns the following information:

PHYPRPNM
Physical property name of the property that is a long variable character
ODESC
Value of the long variable character (there might be more than one ODESC for each property value; the order is by sequence)
To retrieve a list of all objects in the information catalog, enter the following SQL statement:
```
SELECT FLGID, INSTNAME, TYPENAME FROM FLG.NAMEINST
```
This statement returns the following information:

FLGID
Concatenated object type and instance IDs for the object
INSTNAME
External name of the object
TYPENAME
Type of object (external name for the object type)
To determine hierarchical or contact relationships between objects, enter the following statement:
```
SELECT SOURCE, TARGET, RELTYPE FROM FLG.RELINST
```
This statement returns the following information:

SOURCE
Concatenated object type and instance ID for the object that is the source in a relationship
TARGET
Concatenated object type and instance ID for the object that is the target of a relationship
RELTYPE
Relationship type (C for container or T for contact)

To determine linked or attachment relationships between objects, enter the following SQL statement:
```
SELECT SOURCE, TARGET, RELTYPE FROM FLG.ATCHREL
```
This statement returns the following information:

SOURCE
Concatenated object type and instance ID for the object that is the source in a relationship
TARGET
Concatenated object type and instance ID for the object that is the target of a relationship
RELTYPE
Relationship type (A for attachment or L for linked)

You can use the SOURCE and TARGET values to look up the object instance information in the object tables. You can also qualify an SQL statement to select specific object values as shown in step 4.

Example: You have an application for which you want to display the metadata about a relational table named Employee, and show all of its columns. The object type for Employee is TABLES, and the object type for the columns is COLUMN. Your application includes the following SQL statements:

To retrieve the name of the table where TABLES object instances are stored:

SELECT OBJTYPID, DPNAME, NAME, CREATOR, PTNAME FROM FLG.OBJTYREG 
WHERE DPNAME = 'TABLES'

The statement returns the following information:

'000001', 'TABLES', 'Relational Tables', 'USERXYZ', 'TABLES'

To retrieve the OBJTYPID of the COLUMN object:

SELECT OBJTYPID, DPNAME, CREATOR, PTNAME from FLG.OBJTYREG 
WHERE DPNAME = 'COLUMN'

The statement returns the following information:

'000007', 'COLUMN', 'Columns or fields', 'USERXYZ', 'COLUMN'

To retrieve the information about the specific TABLES object for which you want to display metadata:

SELECT OBJTYPID, INSTIDNT, NAME, DBNAME, OWNER, TABLES
  FROM USERXYZ.TABLES
  WHERE NAME = 'Employee'

The statement returns the following information:

'000001', '0040608795',  'Employee', 'MYDBASE', 'USERABC', 'EMPL_TAB'

To retrieve the relationships between the TABLES instance SOURCE and COLUMN instance TARGET:

SELECT TARGET FROM FLG.RELINST
WHERE SOURCE = '0000010040608795'
  AND TARGET LIKE '000007%'
  AND RELTYPE = 'C'

The statement returns the following two objects:

('0000079238400354')
('0000079843095410')

To retrieve the information about the two returned COLUMN objects:

SELECT NAME, SHRTDESC, DATATYPE, LENGTH FROM USERXYZ.COLUMNS
WHERE INSTIDNT IN ('9238400354', 9843095410')

The statement returns the following information:

('Name', 'Employee name information', 'CHAR', '80')
('Address', 'Employee address information', 'CHAR', '220')

[ Top of Page | Previous Page | Next Page ]