ANOTHER IT CO

List ODBC Tables Used By Each Job

program listodbc
*
***********************************************************
* 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
*
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 processodbc:
        *
        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
*************************************************************
processodbc:


  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<6,n> = dsn


  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>) = 'CODBCStage' then
       *
       stagename = object<3>
       dsn = object<8>
       *
       outputlinks = trim(object<7>)
       numberofoutputlinks = dcount(outputlinks,'|')
       for k = 1 to numberofoutputlinks
         stagenumber += 1
         tablemap<6,stagenumber> = dsn
         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<6,1>
             tablemap<3,stagenumber> = tablename
             numberofcolumns = field(outputlinkobject<16,1>,'/',2)
             for i = 1 to numberofcolumns
               tablemap<4,stagenumber,i> = outputlinkobject<17,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 printtablemap:


  return


*************************************************************
printtablemap:


  crt 'Job: ':tablemap<1>
  for i = 1 to stagenumber
    crt space(2):'Stage: ':tablemap<2,i>:space(5):'Link Name: ':tablemap<5,i>
    crt space(4):'DSN: ':tablemap<6,i>
    crt space(4):'Table Name: ':tablemap<3,i>
    columnnames = tablemap<4,i>
    convert @SM to ' ' in columnnames
    formattedcolumnnames = fmt(columnnames,"70T")
    columnlines = dcount(formattedcolumnnames,@TM)
    formattedcolumnnames = trim(formattedcolumnnames)
    convert ' ' to ',' in formattedcolumnnames
    for j = 1 to columnlines
      crt space(6):field(formattedcolumnnames,@TM,j)
    next j
  next i
  crt


  return


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

Copyright 2016 Another IT Co