DS repository

dweller · Post by **dweller** » Mon Jan 23, 2006 11:54 pm

Hi All,

Is there a model or DDL available of the DS Universe repository? I'm wanting to get certain job information from the UV repo using SQL?

Any help will be great

Thanx

dweller

ray.wurlod · Post by **ray.wurlod** » Tue Jan 24, 2006 12:44 am

It's non-first-normal-form, and not in the public domain.

The nearest you can get is by opening doc_tool.mdb using Microsoft Access and getting it to analyze the relationships. But this is not the same as the UniVerse Repository - what doc_tool.mdb takes 29 tables to do the UniVerse Repository does with seven (using nested structures).

dweller · Post by **dweller** » Tue Jan 24, 2006 12:50 am

Thanx Ray.

I've also stumbled across some good work Kim has done. Thanx for the reply. Yeah, the doc_tool.mdb does not look anything like the Universe structure.

dweller

kumar_s · Post by **kumar_s** » Tue Jan 24, 2006 1:50 am

Hi dweller,

I could see a post by Ray for the tables available in universe.

Code: Select all

UniVerse SQL System Tables 
UV_SCHEMA      schemas 
UV_TABLES      tables 
UV_VIEWS       views 
UV_ASSOC       associated multi-valued columns 
UV_COLUMNS     columns 
UV_USERS       users with CONNECT privilege 

UniVerse Admin Account Tables (synonyms exist) 
&DEVICE&        logical to physical device mapping 
&MAP&           output from MAKE.MAP.FILE command 
&PARTFILES&     Distributed files 
ADMIN_BP        BASIC programs for admin utilities 
APP.PROGS       BASIC progams for applications 
BLTRS           data file for BLOCK.TERM command 
BP              BASIC programs for UniVerse 
DATA.TYPES      data type validation for GCI 
DICT.DICT       PI-style dictionary metadata 
DICT.PICK       Pick-style dictionary metadata 
ERRMSG          data for Pick-style STOP, ABORT 
GCI             GCI subroutine definitions 
GLOBAL.CATDIR   global BASIC Catalog Space 
GTARS           technical requests (empty) 
INCLUDE         header files for BASIC programmers 
MENU.FILE       UniVerse menu definitions 
NEWACC          data for VOC file in new accounts 
PTERM.FILE      data for PTERM command 
REVISE.DISCUSSIONS    extended help for REVISE 
REVISE.PROCESSES      templates for REVISE 
STAT.FILE       output from LIST.FILE.STATS command 
SYS.HELP        help topics 
SYS.MESSAGE     tokenized output strings (error codes, etc.) 
UDRSYS          systems participating in database replication 
UDRPUB          tables published for replication 
UDRSUB          subscriptions to published tables 
UV.ACCESS       control of access to certain admin functions 
UV.ACCOUNT      UniVerse accounts 
UV.FLAVOR       control of account creation 
UV.LOGIN        maps Windows loginID styles 
UV.TRANS        transaction logging recoverable files 
UV_LOGS         transaction logging log activities 
VCOMM            obsolete 
VCOMM.PORTS      obsolete 

Tables in every UniVerse Account 
&SAVEDLISTS&    saved Select Lists 
VOC             UniVerse vocabulary file 
VOCLIB          UniVerse vocabulary extensions 

Optional tables in every UniVerse account 
&COMO&          command output interactive processes 
&HOLD&          alternate "printer" destination 
&PH&            output from phantom processes 
&TEMP&          VOC records changed by upgrade 

File Dictionaries (metadata) 
D_filename

BTW can you post the useful information or the link that you have collected.

-Kumar

ray.wurlod · Post by **ray.wurlod** » Tue Jan 24, 2006 2:39 am

That set of tables is NOT the set of tables you would find in the Repository. It is the set of tables that support the DataStage Engine, found in the Engine account (not in any project, though a small number of them are also visible from projects).

The Repository has four sets of tables (hashed files, primarily hashed files).

Design-Time Information
DS_DATATYPES
DS_JOBS
DS_CONTAINERS
DS_JOBOBJECTS
DS_ROUTINES
DS_STAGETYPES
DS_METADATA
DS_TRANSFORMS
DS_TEMPnn

Run-Time Information (one per job, job number nn)
RT_BPnn
RT_BPnn.O
RT_SCnn
RT_CONFIGnn
RT_LOGnn
RT_STATUSnn

Miscellaneous
DS_AUDIT
VOC
VOCLIB
&SAVEDLISTS&
&PH&
&COMO&

Your Hashed Files
Any hashed files you create in the account.

In addition there are some operating system files in the project, such as uvodbc.config, DSParams, .developer.adm and so on.

There is almost no metadata in the public domain for most of the above.

If you are specific about your needs, they may already have been solved (you can search), or may be solvable.

kumar_s · Post by **kumar_s** » Tue Jan 24, 2006 3:43 am

Hi,

Is there a way we can can find the metadata of indivudial table, using command something like "Describe table DS_JOBS"

-Kumar

ArndW · Post by **ArndW** » Tue Jan 24, 2006 3:45 am

Kumar,

you can enter the command "LIST DICT {tablename}" from TCL to get the UniVerse style DDL of most of those hashed files.

kumar_s · Post by **kumar_s** » Tue Jan 24, 2006 4:42 am

ArndW wrote:Kumar,

you can enter the command "LIST DICT {tablename}" from TCL to get the UniVerse style DDL of most of those hashed files.

This is wonderful. Thanks Arnd.

-Kumar

kduke · Post by **kduke** » Tue Jan 24, 2006 7:22 am

If you download EtlStats then you get several jobs which extract job names, job categories, link names, link types which you can look at and see how to ake this metadata and make something useful with it. Once you get these jobs there is a whole series of reports written in SQL to get things like job logs and mail them to someone. The SQL is stored in SqlScripts when you unzip the files included in EtlStats. The whole thing is free. I think these are good examples on how to use job metadata to do something useful.