How to find the group of jobs using the same Dataset

[kashif007]

I am trying to change the datatypes on a dataset which is used by multiple jobs. I manually found 3 jobs which are using this dataset. Is there a way in Datastage that we can find all the jobs using a particular dataset ?

Thanks

[ray.wurlod]

Assuming that you saved/loaded the DataSet table definition (which we all do, right?), it's as easy as performing a "where used" analysis on that table definition.

[kashif007]

Thanks Ray

That is something I didn't knew before. How about if I do not save the dataset table definition. Can we still search the job names using this (unsaved table definition) dataset ?

[ray.wurlod]

Not via a "where used" analysis.

You could possibly interrogate the repository - there have been example queries posted on DSXchange over the years.

[BillB]

Here's a piece of quick and nasty VB code that I have used to obtain information on dataset usage. It reads a V8.0.1 dsx export (without executables), and reports on all the datasets it finds.

Hope you can turn it into something that's useful to you.

Code: Select all

Public Function DataSetUsage()

Dim ExportLine As String
Dim JobFlag As Boolean
Dim CurrJob As String
Dim CurrSet As String
Dim CurrUse As String

Open "YourExport.dsx" For Input Access Read As 1
Open "DataSetUsage.txt" For Output As 2

Do

    Line Input #1, ExportLine
    
    If InStr(1, ExportLine, "BEGIN DSJOB") = 1 Then JobFlag = True
    If JobFlag Then
        If InStr(1, ExportLine, "   Identifier") = 1 Then
            CurrJob = Mid(ExportLine, 16, Len(ExportLine) - 16)
            JobFlag = False
        End If
    End If
    
    If InStr(1, ExportLine, "         Name " & Chr(34) & "dataset" & Chr(34)) = 1 Then
        Line Input #1, ExportLine
        CurrSet = Mid(ExportLine, 17, Len(ExportLine) - 17)
        If CurrSet <> " " Then
            Line Input #1, ExportLine
            Line Input #1, ExportLine
            Line Input #1, ExportLine
            If InStr(1, ExportLine, "         Name " & Chr(34) & "datasetmode" & Chr(34)) = 1 Then
                CurrUse = "Write"
            Else
                CurrUse = "Read"
            End If
            
            Print #2, CurrSet; ","; CurrUse; ","; CurrJob
        End If
    End If
         
Loop Until EOF(1)

Close 1
Close 2

End Function