How to find out if a certain stagetype is used in a project

[victorbos]

Hi all,

I am working at a quite large site: we have 70 datastage projects. And for some reason you really don't want to know I need to find out in which of the 70 projects the Oracle Bulkloader (OraOCIBLK) is used.
I know that I can manually browse each project, and each of our 5000 datastage jobs, but I have a little hope that there might be some smart trick to automatically retreive this info from Universe, or the /Projects directory structure on Unix.
I have found the dsjob -lstages command, but for some reason bulk loaders are not listed with this command.

Any tips, tricks or hints for this?

Victor

[throbinson]

Go to Manager, navigate to the Stage type you want, highlight it in the right hand window and select Usage Analysis from the Tools Menu. This will give you every job that uses the Stage type.

[ray.wurlod]

That will have to be done in each project (but, then that was your question). However, without writing some fairly "hacky" code, so will any other method if you want the answer for all projects.

[victorbos]

Okay thanks.
I guess there is no other way than to go through all 70 projects in manager....

Victor

[ArndW]

Export all your projects to .dsx files, then write a DataStage program to search these files for the tags found only in the Oracle Bulk Loader.

[chulett]

Or download the one Chuck wrote and gives away on his website.

[ArndW]

Craig - good point, I must have been in "consultant" mode (the mantra is "Billable Hours....Billable Hours...")

[chulett]

[AmeyJoshi14]

Or you can use this one also...
Hi,
There is one more method....
You can use unix script to achieve the result....but it will take some time to give the results.
I have developed one script which will achieve the desired result:

Code: Select all

#!/bin/ksh
. $DSHOME/dsenv
PARMDIR=Directory path
function Remove
{                                               
 for FILENAME in projectlist.lst projectnew.lst joblist.lst stage.txt
do
 if [ -f $PARMDIR/$FILENAME ]
  then
  rm $PARMDIR/$FILENAME
 fi
done
}
Remove
`$DSHOME/bin/dsjob -lprojects > $PARMDIR/projectlist.lst`
indx=1
cat $PARMDIR/projectlist.lst | while read line
    do
       prjct_arry[$indx]="$line"
       echo [$indx] $line  >>  $PARMDIR/projectnew.lst
       indx=`expr $indx + 1`
    done 
#for viewing the project
more $PARMDIR/projectnew.lst
echo Please enter the number
read projctnum
  if [ $projctnum -eq 0 ]
   then
    exit 1
  else  
   proj=`more $PARMDIR/projectnew.lst |head -$projctnum | tail -1 | cut -d' ' -f2`
  . $DSHOME/dsenv
   $DSHOME/bin/dsjob -ljobs $proj   > $PARMDIR/joblist.lst
  fi
  cat  $PARMDIR/joblist.lst | while read line
                do
                  jobname=$line
                  . $DSHOME/dsenv
                  $DSHOME/bin/dsjob -lstages $proj $jobname 2>/dev/null > $PARMDIR/stage.txt
                if [[  -s $PARMDIR/stage.txt ]] 
                 then
                  cat $PARMDIR/stage.txt |while read line
                      do
                       stagename=`echo $line`
                       . $DSHOME/dsenv
                       val=`$DSHOME/bin/dsjob -stageinfo $proj $jobname $stagename 2>/dev/null | grep "Stage Type" |cut -d':' -f2|awk '{printf $1}'`
                         if [ -n $val ] 
                           then
                            if [ ${val} == 'ORAOCIBL' ]
                             then
                              echo $jobname
                            fi
                         else
                                echo THE JOB IS GETTING STOP FOR $jobname 
                         fi             
                      done 
                  fi   		
                  done
Remove          

The output of you the script is :

Code: Select all

	Status code = 0 
[1] AA_dev
[2] BB_ver
[3] VERSION
[4] CC_dev
[5] DD_dev
[6] FF_uat
[7] CCCC_ver
[8] install
Please enter the number
1
 
Status code = 0 
Jobname1
Jobname2
Jobname3
Jobname4

The script asks user to enter the desired project name ,if you do not want this you can modify the above the script.
Hope this will help you!

[asingh]

I'd like to take the logic in the above script one step further. I'm looking for any type of Teradata stage which contains reserved words.

We are upgrading our Teradata database to V12 and have to make sure the new reserved words introduced are not being used.

How can the script above be modified to do this?