How to get job name for list of hash file

arunverma · Post by **arunverma** » Sun Dec 19, 2004 11:59 pm

Hi ,

I am able to get the name of job where a particular hash file using through Universe command , but I have a list of hafile and to get job name I have to run command one by one for all has file name , can any one help me that from that is it possible from one routine or from job ??

command I am using .

select
DS_JOBS.NAME AS JOB_NAME FMT '35L'
from
DS_JOBOBJECTS,
DS_JOBS
where
DS_JOBOBJECTS.OLETYPE = 'CHashedInput'
and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
and EVAL DS_JOBOBJECTS."@RECORD<6>" = 'MyHashFileName'
group by
JOB_NAME ;

ray.wurlod · Post by **ray.wurlod** » Mon Dec 20, 2004 12:56 am

Can't you simply omit and EVAL DS_JOBOBJECTS."@RECORD<6>" = 'MyHashFileName' so that all hashed file names are considered?

arunverma · Post by **arunverma** » Mon Dec 20, 2004 1:41 am

Hi Ray ,

If omit line this will give result for all hash file not only selected ,
but it will solve my problem , i omit line and put this is in select column
for getting hash file name but not working giving error .

select
DS_JOBS.NAME AS JOB_NAME FMT '45L' , DS_JOBOBJECTS."@RECORD<6>" FMT '45L

from
DS_JOBOBJECTS,
DS_JOBS
where
DS_JOBOBJECTS.OLETYPE = 'CHashedOutput'
and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
group by
JOB_NAME ;

ray.wurlod · Post by **ray.wurlod** » Mon Dec 20, 2004 3:36 am

The second column in your result set (in the SELECT clause) is not included in the GROUP BY part of the query or does not have a set function applied to it. But there's no reason to group; ORDER BY will work as well.

Thus there are three possible solutions. I prefer the first. The others may need fiddling to get them just right; I'm doing this from memory.

Code: Select all

SELECT DS_JOBS.NAME AS JOB_NAME,
       EVAL DS_JOBOBJECTS."@RECORD<6> FMT '45L' AS LINK_NAME
FROM   DS_JOBOBJECTS, 
       DS_JOBS
WHERE  DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND    DS_JOBOBJECTS.OLETYPE = 'CHashedInput'
ORDER BY 1, 2;

Code: Select all

SELECT DS_JOBS.NAME AS JOB_NAME,
       EVAL DS_JOBOBJECTS."@RECORD<6> FMT '45L' AS LINK_NAME
FROM   DS_JOBOBJECTS, 
       DS_JOBS
WHERE  DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND    DS_JOBOBJECTS.OLETYPE = 'CHashedInput'
GROUP BY JOB_NAME, LINK_NAME;

Code: Select all

SELECT DS_JOBS.NAME AS JOB_NAME,
       MAX(EVAL DS_JOBOBJECTS."@RECORD<6>) FMT '45L' AS LINK_NAME
FROM   DS_JOBOBJECTS, 
       DS_JOBS
WHERE  DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND    DS_JOBOBJECTS.OLETYPE = 'CHashedInput'
GROUP BY JOB_NAME;

arunverma · Post by **arunverma** » Mon Dec 20, 2004 4:42 am

Thanks Mr. Ray ,

I have added category also , working fine but result is not comming in one line , for ex.

CATGRY.... CSA\S2Xfm
JOB_NAME.. J1CsaXfm
LINK_NAME. Task_Id

CATGRY.... CSS\S2Lor
JOB_NAME.. J1CssLor
LINK_NAME. DateMin

CATGRY.... CST\S1Mas
JOB_NAME.. J1CstMas
LINK_NAME. H_CST_LOGINID_DETAILS

I thing Line lenth size exceding , in oracle we can set linesize , pl. advice how to set linesize in universe , pl. find updated command

SELECT CATEGORY AS CATGRY FMT '25L',
DS_JOBS.NAME AS JOB_NAME FMT '25L' ,
MAX(EVAL DS_JOBOBJECTS."@RECORD<6>") FMT '45L' AS LINK_NAME
FROM DS_JOBOBJECTS,
DS_JOBS
WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND DS_JOBOBJECTS.OLETYPE = 'CHashedInput'
GROUP BY CATGRY,JOB_NAME;

ray.wurlod · Post by **ray.wurlod** » Mon Dec 20, 2004 2:49 pm

Default line length is 80. You have specified 25, 25 and 45, which total to 95.

If you're working interactively, you can use the TERM or SET.TERM.TYPE command to change the line length. (Your telnet terminal emulator may yet wrap lines; you need to change its properties in this case.)

Code: Select all

TERM 132
SET.TERM.TYPE 132

Of course, you could also change the column widths so they total to less than 80 characters.

If you're capturing the output of this query into DataStage (for example using a UV stage), you do not need to worry about column width.

arunverma · Post by **arunverma** » Tue Dec 21, 2004 4:06 am

Hello Mr. Ray ,

I have taken out result through copy resul and past in notepad one by one , In SQL we can create file by sppol file name and spool off ,
is it possible to take output is text file .

ray.wurlod · Post by **ray.wurlod** » Tue Dec 21, 2004 4:26 am

Three methods (at least) are available, and that's without writing a program.

Method 1
Define a "spool channel" output as a disk file.

Code: Select all

SETPTR 3,132,20000,0,0,3,BANNER filename,BRIEF

Channel number (the first argument) can be anything from 0 through 255. The other numeric arguments, in order, are page width (characters), page length (lines), top margin (lines), bottom margin (lines) and mode (must be 3 to spool to disk). Large value for page length avoids page feed characters being generated.
Add the spooling keyword and channel number (LPTR 3) to your query.

Code: Select all

SELECT CATEGORY AS CATGRY FMT '25L', 
DS_JOBS.NAME AS JOB_NAME FMT '25L' , 
MAX(EVAL DS_JOBOBJECTS."@RECORD<6>") FMT '45L' AS LINK_NAME 
FROM DS_JOBOBJECTS, 
DS_JOBS 
WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
AND DS_JOBOBJECTS.OLETYPE = 'CHashedInput' 
GROUP BY CATGRY, JOB_NAME
LPTR 3;

Your file is in the &HOLD& subdirectory in your project. This is the main method I would use.

Method 2
Use the COMO command (it's a bit like UNIX's tee) to capture your screen output to a file

Code: Select all

COMO ON filename
SELECT CATEGORY AS CATGRY FMT '25L', 
DS_JOBS.NAME AS JOB_NAME FMT '25L' , 
MAX(EVAL DS_JOBOBJECTS."@RECORD<6>") FMT '45L' AS LINK_NAME 
FROM DS_JOBOBJECTS, 
DS_JOBS 
WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
AND DS_JOBOBJECTS.OLETYPE = 'CHashedInput' 
GROUP BY CATGRY,JOB_NAME;
COMO OFF

In this case, your file is in the &COMO& subdirectory of your project directory. You could also use the DIVERT.OUT command, to direct output to some other location

Method 3
Run the query from the UNIX prompt and redirect the output.

Code: Select all

uvsh "SELECT CATEGORY AS CATGRY FMT '25L', DS_JOBS.NAME AS JOB_NAME FMT '25L' , MAX(EVAL DS_JOBOBJECTS.'@RECORD<6>') FMT '45L' AS LINK_NAME FROM DS_JOBOBJECTS, DS_JOBS WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND DS_JOBOBJECTS.OLETYPE = 'CHashedInput' GROUP BY CATGRY, JOB_NAME;" > filename

This is probably the most flexible of the three, but you have to be careful that your query does not include any double quote characters, since the query itself is surrounded by a pair of them. Another downside is that the entire query must be on the command line.

(PS Please say hello to Rajiv from me.)

arunverma · Post by **arunverma** » Thu Dec 23, 2004 3:30 am

Hi Mr. ray ,

I got result with using group by class but , if any job using two hash file then i am geeting only one hash file name , i think because of max and group by class , then I remove Max and group by class and again ran , but is is not working , pl help. ( Error - Internal problem with ALIAS, submit GTAR. )

SELECT CATEGORY AS CATGRY FMT '25L',
DS_JOBS.NAME AS JOB_NAME FMT '35L' ,
(EVAL DS_JOBOBJECTS."@RECORD<6>") FMT '45L' AS LINK_NAME
FROM DS_JOBOBJECTS,
DS_JOBS
WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND DS_JOBOBJECTS.OLETYPE = ' CHashedOutput' ;

ray.wurlod · Post by **ray.wurlod** » Thu Dec 23, 2004 2:35 pm

Try changing AS LINK_NAME (which sets up an alias) with COL.HDG 'LINK_NAME' (which merely sets up a column heading).

I got the same alias problem; changing to COL.HDG fixed it.

You may get repeats (for example in jobs where two input links run into the same hashed file stage). You can, of course, eliminate these by putting DISTINCT immediately after SELECT.

And do report the alias problem to your support provider (and insist that they pass it along to Ascential!).

arunverma · Post by **arunverma** » Fri Dec 24, 2004 1:31 am

Thanks Mr. Ray

I have modified SQL and it is working fine , but after result few line it is getting abort . yes , we are going to take support from Ascential.

Error -

CRITICAL ERROR! Notify the system administrator.
Abnormal termination of DataStage.
Fault type is 11. Layer type is Command Language.

Command -

SELECT DISTINCT CATEGORY AS CATGRY FMT '25L',
DS_JOBS.NAME AS JOB_NAME FMT '35L' ,
(EVAL DS_JOBOBJECTS."@RECORD<6>") FMT '45L' COL.HDG 'LINK_NAME'
FROM DS_JOBOBJECTS,
DS_JOBS
WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND DS_JOBOBJECTS.OLETYPE = 'CHashedOutput' ;

arunverma · Post by **arunverma** » Fri Dec 24, 2004 5:50 am

Hi Mr. Ray ,

I am also able to get result from uvsh command , but unable to set
linesize (TERM 200) , pl. help .

Thanks and Regards

arun

ray.wurlod · Post by **ray.wurlod** » Fri Dec 24, 2004 5:28 pm

Can you show exactly what you're doing with the uvsh command? uvsh is a well-formed UNIX (or DOS) executable, and will receive input on stdin and generate output on stdout and stderr.

So your line size is affected by whatever environment variables configure it (this differs for different operating systems) and also by the width setting in your telnet client.

arunverma · Post by **arunverma** » Fri Dec 24, 2004 10:56 pm

I am running following command from my project directory and getting output in file ( test )

/u01/app/datastage/product/Ascential/DataStage/DSEngine/bin/uvsh "SELECT CATEGORY AS CATGRY FMT '25L', DS_JOBS.NAME AS JOB_NAME FMT '25L' , MAX(EVAL DS_JOBOBJECTS.'@RECORD<6>') FMT '45L' AS LINK_NAME FROM DS_JOBOBJECTS, DS_JOBS WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND DS_JOBOBJECTS.OLETYPE = 'CHashedInput' GROUP BY CATGRY, JOB_NAME;" > test

ray.wurlod · Post by **ray.wurlod** » Fri Dec 24, 2004 11:11 pm

Nearly there!

Create a file (say testin) containing two commands; the first to set up the width and length, the second the query.

Code: Select all

SET.TERM.TYPE WIDTH 132 LENGTH 10000

SELECT CATEGORY AS CATGRY FMT '25L', DS_JOBS.NAME AS JOB_NAME FMT '25L' , MAX(EVAL DS_JOBOBJECTS.'@RECORD<6>') FMT '45L' AS LINK_NAME FROM DS_JOBOBJECTS, DS_JOBS WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND DS_JOBOBJECTS.OLETYPE = 'CHashedInput' GROUP BY CATGRY, JOB_NAME;

Execute the uvsh command redirecting stdin from this file.

Code: Select all

PATH=$PATH:/u01/app/datastage/product/Ascential/DataStage/DSEngine/bin ; export PATH

uvsh < testin > test

Of course, you must be attached to a project directory.