UniVerse - SQL LIST: View List of All Database Tables

Question

I am trying to obtain a list of all the DB Tables that will give me visibility on what tables I may need to JOIN for running SQL scripts.

For example, in TCL when I run "LIST.DICT" it returns "Name of File:" for input. I then enter "PRODUCT" and it returns a list of all available fields.

However, Where can I get a list of all my available Tables or list of my options that I can enter after "Name of File:"?

Here is what I am trying to achieve. In the screen shot below, I would like to run a SQL script that gives me the latest Log File Activity, Date - Time - Description. I would like the script to return '8/13/14 08:40am BR: 3;BuyPkg'

Thank you in advance for your help.

Answer 1

From TCL within the database account containing your database files, type: LISTF

Sample output: FILES in your vocabulary 03:21:38pm 29 Jun 2015 Page 1

Filename........................... Pathname...................... Type Modulo

 File - Contains all logical device names

DICT &DEVICE& /u1/uv/D_&DEVICE& 2 1 DATA &DEVICE& /u1/uv/&DEVICE& 2 3

 File - Used by MAKE.MAP.FILE

DICT &MAP& /u1/uv/D_&MAP& 2 1 DATA &MAP& /u1/uv/&MAP& 6 7

 File - Contains all parts of Distributed Files

DICT &PARTFILES& /u1/uv/D_&PARTFILES& 2 1 DATA &PARTFILES& /u1/uv/&PARTFILES& 18 7

DICT &PH& D_&PH& 3 1 DATA &PH& &PH& 1

DICT &SAVEDLISTS& D_&SAVEDLISTS& 3 1 DATA &SAVEDLISTS& &SAVEDLISTS& 1

 File - Used by uniVerse to access the current directory.

DICT &UFD& /u1/uv/D_UFD 2 1 DATA &UFD& . 19 1

DICT &XML& D_&XML& 18 3 DATA &XML& &XML& 19 1

Answer 2

Firstly, UniVerse has no Log File Activity Date and Time.

However, you can still obtain the table's modified/ accessed date from the file system however. To do this,

1. You need to have a subroutine accepting a path of the table to return a date or a time. e.g. SUBROUTINE GET.FILE.MOD.DATE(DAT.MOD, S.FILE.PATH)

Inside the subroutine, you can use EXECUTE to run shell command like istat for getting these info on a unix e.g. Please beware that for a dynamic file e.g. there are Data and Overflow parts under a directory. You should compare the dates obtained and return only the latest one.

2. Globally catalog the subroutine

3. Create an I-Desc in VOC, e.g. I.FILE.MOD.DATE in the field definition of this I-Desc: SUBR("*GET.FILE.MOD.DATE",F2) and Conv Code as "D/MDY2"

4. Create another I-Desc e.g. I.FILE.MOD.TIME

Finally, you can

LIST VOC I.FILE.MOD.DATE I.FILE.MOD.TIME DESC WITH TYPE LIKE "F..."

alternatively in SQL,

SELECT I.FILE.MOD.DATE, I.FILE.MOD.TIME, VOC.DESC FROM VOC WHERE TYPE LIKE "F%";