DSXchange

Posted: **Tue Aug 02, 2005 2:54 am**

Hi Friends,

I would like to get the list of jobs belonging to a category in a text file.

I am aware of SELECT * FROM DS_JOBS where CATEGORY=''

I tried executing the command in uv and got an error saying DS_JOBS table does not exist.
Can I execute this command in uv?

1) If yes then what am I doing wrong?
2) Also after executing how can I get the result in a text file?

I do not want to execute the command window in the Administrator.

Thanks in Advance,

Sunshine

Posted: **Tue Aug 02, 2005 3:18 am**

Code: Select all

DSHOME=<Your DataStage home directory>
. $DSHOME/dsenv
DSBIN=$DSHOME/bin
 
PROJDIR=<Your project dir>

cd $PROJDIR
$DSBIN/uv "select * from DS_JOBS where CATEGORY='<your category name>'; > <your txt file>"

Note: You will have to give

Code: Select all

<category name>/<sub category name>

if you have sub categories.

Posted: **Tue Aug 02, 2005 3:46 am**

Hi Ashwin,

Thanks for the reply.

I am able to execute the command and see the list of jobs but I am still not able to capture the list in a text file.

It executes the command but does not write to the text file

Thanks,
Sunshine

Posted: **Tue Aug 02, 2005 4:19 am**

Ashwin's line looks correct. If the redirect to the text file is working correctly then you should get no output to your terminal, just to the file. If you are having problems with this you need to post your exact command here or talk to someone savvy in UNIX locally.

Posted: **Tue Aug 02, 2005 4:57 am**

Hi,

select * from ds_jobs where category='ELDM\DIMENSIONS'; >/HOME/USER6/TRIAL.TXT

It lists down 8 records.
It does not write to the txt file.

Thanks,
Sunshine

Posted: **Tue Aug 02, 2005 5:19 am**

In my environment (UNIX) I used

Code: Select all

$DSHOME/bin/uvsh "select * from DS_JOBS" > /tmp/deletemeplease.txt

successfully.

That should give you enough to get it working.

Posted: **Tue Aug 02, 2005 5:53 am**

Hi,
This what I amm trying to do but not able to write to text file.

cd ../DSEngine/bin

$ uv
>Logto LETS_A
>SELECT * FROM DS_JOBS > /home/dsadm/trial1.txt;

DataStage/SQL: syntax error. Unexpected symbol. Token was ">".
Scanned command was FROM DS_JOBS >

Please tell me what am I doing wrong?

Thank You,
Sunshine

Posted: **Tue Aug 02, 2005 6:40 am**

Sunshine,

the advice you got so far will work as listed; you have changed this (from an external call into a TCL one) so it won't work and the message clearly states the syntax error.

The concept in UNIX and DOS of using a ">" to redirect output does not work in the TCL environment.

If you really wish to do this you can execute 3 commands:

Code: Select all

COMO ON SELECTOUTPUT.TXT
 SELECT * FROM DS_JOBS > /home/dsadm/trial1.txt;
COMO OFF

This will create the text output file in &COMO&/SELECTOUTPUT.TXT

Posted: **Tue Aug 02, 2005 7:09 am**

Hi ArndW,

ArndW wrote:In my environment (UNIX) I used
Code: Select all
$DSHOME/bin/uvsh "select * from DS_JOBS" > /tmp/deletemeplease.txt
successfully.

That should give you enough to get it working.

Can you please tell me where the above command has been executed?

Posted: **Tue Aug 02, 2005 7:31 am**

Hi,
Seems this was run under the OS command line, after dsenv was loaded.

Posted: **Tue Aug 02, 2005 8:43 am**

Hi,

select * from ds_jobs where category='ELDM\DIMENSIONS'; >/HOME/USER6/TRIAL.TXT

Take off the ; at the end of the select and try

Code: Select all

select * from ds_jobs where category='ELDM\DIMENSIONS' > /HOME/USER6/TRIAL.TXT

Posted: **Tue Aug 02, 2005 9:34 am**

There are 2 things going on here. You can run these commands under the control of the Universe shell (uvsh) or Korn (ksh) or Bourne shell (sh).

Code:
$DSHOME/bin/uvsh "select * from DS_JOBS" > /tmp/deletemeplease.txt

Has to be under the control of ksh. Once you run uv as a separate command then you cannot redirect because you are under control of the Universe shell and it does not understand > or >>. That is why Arnd suggested using COMO commands. COMO is the Universe way of saving the output of the commands into a file. Where using less than and greater than is the Korn shell way of saving the output by redirecting it. COMO actually duplicates the output.

Posted: **Tue Aug 02, 2005 4:45 pm**

You must be in a project for the DS_JOBS table to exist. If you're in the UV account, there is no DS_JOBS table, which might indicate why you got the "DS_JOBS not found" message.

Redirection using redirection operators does not work at the TCL prompt; instead you can use COMO (as already suggested) or DIVERT.OUT. For example (in a project at TCL):

Code: Select all

DIVERT.OUT ON &UFD& capture.txt
SELECT * FROM DS_JOBS;
DIVERT.OUT OFF

This will capture the output into a file called capture.txt in your project directory.

Posted: **Tue Aug 02, 2005 11:39 pm**

Hi,
Try this in after subroutine.
Input argument is fully qualified path of the text file

Code: Select all

ErrorCode = 0      ;* set this to non-zero to stop the stage/job

FilePath = InputArg

      Cmd = "select * from DS_JOBS where CATEGORY='Your Category';"

      Call DSExecute("UV", Cmd, Output, SystemReturnCode)

      NoOfMessage = DCount(Output,@FM)


      OPENSEQ FilePath TO fv Then WEOFSEQ fv ; *Try to open the sequential file ,if it exists it will clear the file
      Else
         CREATE fv Then Ans = "File Created"       ; * If file not exists it will create the file
      End

      for i=1 to NoOfMessage Step 1
         Msg = Field(Output,@FM,i)
         WriteSeqF Msg To fv Else Exit
      Next i


      Ans = NoOfMessage

Hope this will work

DSXchange

Get the List of Jobs in a text file

Get the List of Jobs in a text file

DS_JOBS not found