ANOTHER IT CO

List Jobs Using An Oracle Table Via An OCI8 Stage

program searchoci
*
***********************************************************
* Copyright 2001 Another IT Co, Chuck Smith
*
* Permission to use this software is conditional upon you
* agreeing to the disclaimer and license terms.  By installing
* or using the software you consent to be bound by and become
* party to this agreement.
* 
* Chuck Smith ("developer") hereby grants you ("user") a 
* non-exclusive personal license to use this software subject 
* to the following disclaimer, terms and conditions:
* 
* user agrees not to distribute, reproduce, transmit or
* transfer, in whole or in part, the software or its
* documentation without express written permission
* from the developer.
* 
* DISCLAIMER: This software is provided on an "as-is"
* basis without warranty of any kind.
* 
* Developer specifically disclaims any other warranty,
* express or implied, including any warrent of 
* merchantability or fitness for a particular purpose.
* In no event shall developer be liable for any 
* consequential, indirect, special or incidental damages,
* even if developer has been advised by user of the 
* possibility of such potential loss or damage.  User 
* agrees to hold developer harmless from and against any
* and all claims, losses, liabilities and expenses.
***********************************************************
*
true = 1
false = 0
*
prompt ''
*
bop:
crt 'Enter table name to search for: ':
input findtablename
if trim(findtablename) = '' then goto eop:
findtablename = upcase(trim(findtablename))
*
open '','DS_JOBS' to DSJOBS then
  *
  clearselect 2
  * select DSJOBS
  execute 'SELECT DS_JOBS BY NAME NO.PAGE TO 2'
  *
  eof = false
  loop
    readnext onejob from 2 else eof = true
  until eof = true
    *
    * crt 'Processing job: ':trim(onejob)
    read onejobrec from DSJOBS,onejob then
      *
      onejobnumber = onejobrec<5>
      * crt space(2):'Processing job number: ':onejobnumber
      jobfile = 'DS_JOB':onejobnumber
      *
      open '',jobfile to DSJOB then
        *
        gosub processoci:
        *
        close DSJOB
      end else
        * crt 'Unable to open ':jobfile:' for job ':onejob:'.'
        * crt
      end
      *
    end else
      * crt 'Can not read DS_JOBS record for ':onejob:'.'
      * crt
    end
    *
  repeat
  *
  close DSJOBS
end else
  crt 'Unable to open DS_JOBS.'
  goto eop:
end
*
eop:
crt 'Done.'
stop
*************************************************************
processoci:


  tablemap = ''
  * tablemap<1> ==> job name
  * tablemap<2,n> = stage name
  * tablemap<3,n> = table name
  * tablemap<4,n,m> = column name
  * tablemap<5,n> = link name


  tablemap<1> = onejob
  stagenumber = 0
  columnnumber = 0


  clearselect
  select DSJOB


  eof0 = false
  loop
    readnext objid else eof0 = true
  until eof0 = true
    *
    read object from DSJOB,objid then
     *
     if trim(object<2>) = 'CCustomStage' and trim(object<8>) = 'ORAOCI8' then
       *
       stagename = object<3>
       *
       outputlinks = trim(object<7>)
       numberofoutputlinks = dcount(outputlinks,'|')
       for k = 1 to numberofoutputlinks
         stagenumber += 1
         tablemap<2,stagenumber> = stagename
         outputlink = trim(field(outputlinks,'|',k))

         if trim(outputlink) = '' then
           tablemap<2,stagenumber> = stagename:' has no output link.'
           * crt 'Stage ':stagename:' has a null output link.'
           outputlink = ''
           inputlinkid = trim(object<6>)
           read inputlinkobject from DSJOB,inputlinkid then
             inputlinkname = trim(inputlinkobject<3>)
             sourcestageid = trim(inputlinkobject<5>)
             read sourcestageobject from DSJOB,sourcestageid then
               numsourcestageobjects = dcount(sourcestageobject<7>,'|')
               for g = 1 to numsourcestageobjects
                 ssooid = field(sourcestageobject<7>,'|',g)
                 read sourcestageoutputobject from DSJOB,ssooid then
                   if trim(sourcestageoutputobject<3>) = inputlinkname then
                     outputlink = ssooid
                   end
                 end else
                   null
                 end
               next g
             end else
               null
             end
           end else
             null
           end
         end

         if trim(outputlink) <> '' then
           read outputlinkobject from DSJOB,outputlink then
             tablemap<5,stagenumber> = trim(outputlinkobject<3>)
             tablename = outputlinkobject<14,1>
             tablemap<3,stagenumber> = tablename
             numberofcolumns = field(outputlinkobject<15,1>,'/',2)
             for i = 1 to numberofcolumns
               tablemap<4,stagenumber,i> = outputlinkobject<16,i>
             next i
           end else
             crt 'Can not read output link object ':outputlink:'.'
           end
         end
       next k
     end
     *
    end else
      crt 'Can not read object id: ':objid:'.'
    end
    *
  repeat


  gosub checktablename:


  return

*************************************************************
checktablename:


  found = false
  for i = 1 to stagenumber
    if upcase(trim(tablemap<3,i>)) = findtablename then
      found = true
    end
    temptablename = tablemap<3,i>
    convert '.' to @SM in temptablename

    j = dcount(temptablename,@SM)
    if upcase(trim(temptablename<1,1,j>)) = findtablename then
      found = true
    end
    
  next i


  if found = true then
    crt 'Job: ':tablemap<1>
    crt
  end


  return

*************************************************************
end

Copyright 2016 Another IT Co