Where is DS_JOBS documented?

[sbass1]

Hi,

I inherited this query from a job in another project:

Code: Select all

SELECT
	'job' as ObjectType,
	DS_JOBS.NAME as ObjectName,
	DS_JOBS.CATEGORY as ObjectCategory
from
	DS_JOBS 
order by 1,2

I'd like to change this query, but need to know the schema for this table.

I've downloaded every document (ok, not the platform specific ones) from here:

http://www-01.ibm.com/software/data/u2/ ... y/952univ/

I've done a local "search all PDF documents" for "DS_JOB" and got 0 hits.

However, a search for "DS_JOBS" on DSXchange gets 1170 hits (1171 now ), so clearly it's a popular topic.

Short of skimming the 1170 hits looking for my desired information, is there an official document that details the schema for:

1) DS_JOB specifically, and
2) other project metadata tables in general?

If the answer is no, then any idea why it would be undocumented?

[ray.wurlod]

The full structure of DS_JOBS can be had by getting its metadata. Alas you are blocked from importing its table definition into DataStage. The column names can be had with the command

Code: Select all

LIST.DICT DS_JOBS 

Note that this is not an SQL statement and so lacks a trailing semi-colon.

None of the Repository tables is officially documented. This is a deliberate decision of the vendor. Take it up with them if you don't like it. It changes substantially in version 8 anyway.

DS_JOBS and DS_AUDIT are the only tables in the Repository for which complete metadata are revealed by the LIST.DICT command. The remainder are deliberately under-documented, mainly because they use varying record structures.

[sbass1]

Thanks Ray.

Since these tables are undocumented, can someone post a list of the tables they find most useful? I'll then search DSXchange for further hits, and how I might use these tables in my use of DS.

[ray.wurlod]

In general I don't bother, but use the DataStage API to get what I need.

This is even more relevant in version 8, because the metadata delivery service knows where the metadata reside, and I probably don't.

[sbass1]

Code: Select all

LIST.DICT DS_JOBS 

Note that this is not an SQL statement and so lacks a trailing semi-colon.

Sorry to belabour this...

OK, this is my output:

Code: Select all

$<50>DICT DS_JOBS    01:44:19pm  01 Apr 2009  Page    1

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            DS_JOBS         10L    S
NAME           D    0                            Job name        20T    S
READONLY       D    1                            Reserved        8L     S
OLETYPE        D    2                            Reserved        20L    S
CATEGORY       D    3                            Category        20L    S
DESC           D    4                            Description     60T    S
JOBNO          D    5                            No.             5L     S
JOBTYPEIND     D    6                            Reserved        5L     S
JOBID          D    7                            Job ID          20L    S
JOBTYPE        I      IF JOBTYPEIND                                     S
                      = "" THEN 0
                      ELSE
                      JOBTYPEIND
@              PH     ID.SUP NAME
                      DESC JOBNO
                      CATEGORY _

Press any key to continue...

And in a Universe stage in my job, where Data source name is localuv, this SQL works fine:

Code: Select all

SELECT JOBTYPE, CATEGORY, NAME, READONLY, OLETYPE, "DESC", JOBNO, JOBTYPEIND, JOBID FROM DS_JOBS;

However, this SQL results in an error:

Code: Select all

SELECT case jobtype when 0 then "job" when 2 then "sequence" else "" end as JOBTYPE, CATEGORY, NAME, READONLY, OLETYPE, "DESC", JOBNO, JOBTYPEIND, JOBID FROM DS_JOBS;

So, does the SQL available in a Universe stage support the full SQL language, specifically the CASE statement?

[ray.wurlod]

No. You have the UniVerse SQL Reference Guide. UniVerse/SQL supports compiled UniVerse BASIC expressions on the command line instead.

And, by the way, all identifiers are case-sensitive.

Code: Select all

SELECT EVAL "FIELD('Server|Mainframe|Sequence|Parallel','|',JOBTYPE,1)" AS JOB_TYPE FMT '10L' COL.HDG 'Job Type', CATEGORY, NAME FMT '60T', READONLY COL.HDG 'ReadOnly', OLETYPE COL.HDG 'OLE Type', "DESC" FMT '60T', JOBNO COL.HDG 'Job#' FMT '4R', JOBID FROM DS_JOBS WHERE NAME NOT LIKE '\\%';

OLETYPE is not relevant for jobs. JOBTYPEIND and JOBTYPE are the same - that one maps "" to 0 (for historical compatibility) being the only difference.