Sequence Job - retrieving Logs

[ray.wurlod]

The expression "can of worms" comes to mind.

A "dictionary item" is hashed file terminology for "column definition"; these are actually optional for hashed files, as navigation of "real" data is positional rather than by name. For virtual columns, such as those postulated, a column definition can be placed in the "file dictionary" for the hashed file, which is its repository of metadata.

You can execute these from the Administrator client Command window or the TCL prompt on the server. Note that the DICT keyword is a required part of the syntax.

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC)
VALUES ('DEPEND_TYPE', 'D', '32', 'Type', '3R', 'M', 'DEPENDS');

INSERT INTO DICT DS_JOBOBJECTS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC)
VALUES ('DEPEND_JOBS', 'D', '31', 'Depend Jobs', '35L', 'M', 'DEPENDS');

INSERT INTO DICT DS_JOBOBJECTS (FIELD, CODE, EXP)
VALUES ('DEPENDS', 'PH', 'DEPEND_TYPE DEPEND_JOBS');

Maybe "Pandora's Box" was really the term we need!

[dhiraj]

Thanks ray. This discussion has been a great learning for me. It has inspired me to learnuniverse. what should be the best source to learn universe?



Thanks

Dhiraj

P.S. well and on pandora's box , i think i'm not matured enough in datastage to comment on it.

[kduke]

Thanks Ray. So here is the final solution:

Code: Select all

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC) 
VALUES ('DEPEND_TYPE', 'I', 'RAISE(TRANS("DS_JOBOBJECTS","J\": @RECORD<5> :"\ROOT",32,"X"))', 'DependType', '3R', 'M', 'DEPENDS'); 

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC) 
VALUES ('DEPEND_JOBS', 'I', 'RAISE(TRANS("DS_JOBOBJECTS","J\": @RECORD<5> :"\ROOT",31,"X"))', 'DependJobs', '35L', 'M', 'DEPENDS'); 

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP) 
VALUES ('DEPENDS', 'PH', 'DEPEND_TYPE DEPEND_JOBS');

Now to get the job list.

Code: Select all

LIST DS_JOBS WITH NAME = "MyJob" AND WHEN DEPEND_TYPE = 0 DEPEND_JOBS ID.SUP HDR.SUP COL.HDR.SUPP COUNT.SUP

[kduke]

The dictionary items need to be in DS_JOBS to get the list based on job name. The job name is the key to DS_JOBS. Field 5 is job number. The key to DS_JOBOBJECTS where the depend jobs are stored is "J\xxx\ROOT" where xxx is the job number. Now you have a complete solution.

Ray how do you use a WHEN clause on a SQL SELECT? Do ID.SUP and all the other suppress statements work with SQL SELECT?

[ray.wurlod]

The best place to learn UniVerse is from IBM. Most countries outsource UniVerse training; contact IBM to find out to whom in your country.


Kim (anyone else reading this beware of brain implosion!)

WHEN clauses, if used, follow and are constructed like WHERE clauses, and precede GROUP BY clauses. Unlike RetrieVe, in SQL a WHEN clause does not generate selection; you need both WHERE and WHEN clauses ("structured" query language I guess - then again, using WITH and WHEN in RetrieVe is much faster if there's an index).

ID.SUP isn't necessary, as @ID is not displayed unless requested. The other keywords, including field modifiers (BREAK ON, TOTAL, CALC, etc.), field qualifiers (CONV, FMT, etc.) and report and output options (HEADING, LPTR and so on) are all supported. The preferred constructs are non-abbreviated and do not contain punctuation, for example COLUMN SUPPRESS.

There's other good stuff for handling multi-valued columns as well, such as the ability to have the engine preserve sorted order when new "rows" are inserted in an association.

Really don't know why this hasn't become more popular in the U2 community - there's stuff you can do with this (like nested queries, even correlated sub-queries) that are simply impossible with RetrieVe.
And, yes, you can work with Select Lists; use the forms "SELECT column(s) TO SLIST 8" and "FROM tablename SLIST 9" in a SELECT statement.

[kduke]

Thanks Ray. I never used SQL at any Universe site. I can see several advantages with it. I know lots of Universe experts and none of them know Uv SQL. Most of them are DataStage developers now. Your SQL examples are very useful and educational.

[dhiraj]

can you please explain what the 3rd dictionary item means?

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP)
VALUES ('DEPENDS', 'PH', 'DEPEND_TYPE DEPEND_JOBS');

Thanks

Dhiraj

[kduke]

There are multiple record types in the dictionary. Field 1 tells the type. If there is a PH there then it is a phrase. It is just as if you added everything on line 2 to your command. This is a special phrase required by an association. An association is how you define a nested table. A nested table is all the mutivalued fields that work as a group. For each DEPEND_TYPE there should an associated DEPEND_JOBS. If you normalized these fields then they would be in a separate table with the same key as this table plus the line item or multivalue number which is the order they are stored in this table. Multivalues are hard to grasp but very powerful.

[dhiraj]

1) Is 'DEPENDS' some kind of reserved word. i.e is the code given below equivalent of what u've given?

Code: Select all

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC) 
VALUES ('DEPEND_TYPE', 'I', 'RAISE(TRANS("DS_JOBOBJECTS","J\": @RECORD<5> :"\ROOT",32,"X"))', 'DependType', '3R', 'M', 'dhiraj'); 

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC) 
VALUES ('DEPEND_JOBS', 'I', 'RAISE(TRANS("DS_JOBOBJECTS","J\": @RECORD<5> :"\ROOT",31,"X"))', 'DependJobs', '35L', 'M', 'dhiraj'); 

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP) 
VALUES ('dhiraj', 'PH', 'DEPEND_TYPE DEPEND_JOBS');

2) can i specify the association directly when creating the dictionary item instead of using a phrase as shown below?

Code: Select all

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC) 
VALUES ('DEPEND_TYPE', 'I', 'RAISE(TRANS("DS_JOBOBJECTS","J\": @RECORD<5> :"\ROOT",32,"X"))', 'DependType', '3R', 'M', 'DEPEND_JOBS'); 

INSERT INTO DICT DS_JOBS (FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC) 
VALUES ('DEPEND_JOBS', 'I', 'RAISE(TRANS("DS_JOBOBJECTS","J\": @RECORD<5> :"\ROOT",31,"X"))', 'DependJobs', '35L', 'M', 'DEPEND_TYPE'); 

Thanks

Dhiraj

[kduke]

Number 1 will work. Number 2 will not. The DEPENDS is not a reserve word. It is whatever we want to call our association.