DSXchange

Posted: **Tue May 27, 2008 5:16 am**

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

Posted: **Tue May 27, 2008 5:59 am**

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.

Posted: **Tue May 27, 2008 6:26 am**

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.

Posted: **Wed May 28, 2008 12:54 am**

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

Victor

Posted: **Wed May 28, 2008 1:40 am**

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.

Posted: **Wed May 28, 2008 6:06 am**

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

Posted: **Wed May 28, 2008 6:22 am**

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

Posted: **Wed May 28, 2008 6:27 am**

Posted: **Wed May 28, 2008 7:24 am**

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!

Posted: **Wed Jun 18, 2008 10:35 pm**

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?

DSXchange

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

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