How to get job name for list of hash file
Moderators: chulett, rschirm, roy
How to get job name for list of hash file
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 ;
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 ;
Arun Verma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 ;
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 ;
Arun Verma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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;
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;
Arun Verma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.)
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.
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
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Three methods (at least) are available, and that's without writing a program.
Method 1
Define a "spool channel" output as a disk file.
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.
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
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.
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.)
Method 1
Define a "spool channel" output as a disk file.
Code: Select all
SETPTR 3,132,20000,0,0,3,BANNER filename,BRIEF
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;
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
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
(PS Please say hello to Rajiv from me.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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' ;
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' ;
Arun Verma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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!).
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!).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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' ;
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' ;
Arun Verma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
/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
Arun Verma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Nearly there!
Create a file (say testin) containing two commands; the first to set up the width and length, the second the query.
Execute the uvsh command redirecting stdin from this file.
Of course, you must be attached to a project directory.
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;
Code: Select all
PATH=$PATH:/u01/app/datastage/product/Ascential/DataStage/DSEngine/bin ; export PATH
uvsh < testin > test
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.