9 Replies Latest reply: Sep 5, 2013 5:06 AM by DecaXD RSS

    how to get the Interface inserted rows fom ODI Reporsitory

    AMSI
      hi,
      I need select query that will select the Interface inserted rows
      (Count) from ODI repository tables. because I want to maintain these records into another Oracle tables?????
        • 1. Re: how to get the Interface inserted rows fom ODI Reporsitory
          897978
          import datetime
          import sys
          import optparse

          document = []
          def docprint(string):
          document.append('%s' % string)n
          def docprintnocr(string):
          document.append('%s' % string)

          p = optparse.OptionParser()
          p.add_option('-a','--server',dest='server',default='XXX',help='The server with the ODI_W catalog')
          p.add_option('-b','--beginningday',dest='beginningday',type=int,default=1,help='The day to begin retrieval')
          p.add_option('-e','--endingday',dest='endingday',type=int,default=0,help='The day to end retrieval')
          p.add_option('-n','--session',dest='session',default='',help='Session to retrieve')
          p.add_option('-s','--step',dest='step',action='store_true',help='Print the step data')
          p.add_option('-t','--task',dest='task',action='store_true',help='Print the task data')

          p.add_option('-x','--recipientlist',dest='recipientlist',default='XXX',help='report recipient(s)')
          p.add_option('-y','--mailserver',dest='mailserver',default='XXX',help='mail server')
          p.add_option('-z','--mailuser',dest='mailuser',default='XXX',help='mail user')
          p.add_option('-p','--printonly',dest='printonly',action='store_true',help='Print, no e-mail')
          options,args = p.parse_args()

          docprint( '%s %s' \
          '\n\tserver=%s' \
          '\n\tbeginningday=%s' \
          '\n\tendingday=%s' \
          '\n\tsession=%s' \
          '\n\tstep=%s' \
          '\n\ttask=%s' \
          '\n\tprintonly=%s'
          % (
          sys.argv[0]
          ,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
          ,options.server
          ,options.beginningday
          ,options.endingday
          ,options.session
          ,options.step
          ,options.task
          ,options.printonly
          )
          )

          if options.server.upper() in ('XXX','YYY'):
          catalog = 'ODI_W'
          elif options.server in ('WWW','ZZZ'):
          catalog = 'SNP_W'
          else:
          print 'Unknown server %s' % options.server
          sys.exit(1)

          TimeEnd = datetime.datetime.now() - datetime.timedelta(options.endingday)
          TimeBegin = datetime.datetime.now() - datetime.timedelta(options.beginningday)
          TimeFormat = '%Y-%m-%d %H:%M:%S'

          if options.task:
          options.step = True

          docprint( '\n%s between %s and %s' %(
          options.server
          ,TimeBegin.strftime(TimeFormat)
          ,TimeEnd.strftime(TimeFormat)
          )
          )
          OptionString = ''
          if options.session:
          OptionString = '\nPrinting session %s' % options.session
          else:
          OptionString = '\nPrinting all sessions'

          if options.step:
          OptionString = '%s %s' % (OptionString,'with step detail')
          if options.task:
          OptionString = '%s %s' % (OptionString, 'and task detail')

          docprint(OptionString)

          import pyodbc
          ConnectString = 'DRIVER={SQL SERVER};SERVER=%s;DATABASE=%s;Trusted_Connection=yes' % (options.server.upper(),catalog)
          try:
          Connection = pyodbc.connect(ConnectString,autocommit=False)
          Cursor = Connection.cursor()
          except Exception, e:
          raise RuntimeError, '%s %s connect failed\n%s' % (options.server,catalog,e)

          SelectSession = """
          select
          S.SESS_NO
          ,S.SESS_NAME
          ,S.SESS_BEG
          ,S.SESS_END
          ,coalesce(S.SESS_DUR,0)
          ,S.SESS_STATUS
          ,S.CONTEXT_CODE
          from SNP_SESSION as S
          where S.SESS_BEG between ? and ?
          and S.SESS_BEG = (
          select max(SESS_BEG)
          from SNP_SESSION
          where SESS_NAME = S.SESS_NAME)
          order by S.SESS_BEG ASC
          """

          SelectSessionHistory = """
          select Top 3
          SESS_NO
          ,SESS_NAME
          ,SESS_BEG
          ,SESS_END
          ,coalesce(SESS_DUR,0)
          ,SESS_STATUS
          ,CONTEXT_CODE
          from SNP_SESSION
          where SESS_NAME = ?
          and SESS_NO <> ?
          order by SESS_BEG DESC
          """

          SESS_NO = 0
          SESS_NAME = 1
          SESS_BEG = 2
          SESS_END = 3
          SESS_DUR = 4
          SESS_STATUS = 5
          CONTEXT_CODE = 6

          SelectStep = """
          select
          LOG.STEP_BEG
          ,LOG.STEP_END
          ,coalesce(LOG.STEP_DUR,0)
          ,LOG.STEP_STATUS
          ,coalesce(LOG.NB_ROW,0)
          ,coalesce(LOG.NB_INS,0)
          ,coalesce(LOG.NB_UPD,0)
          ,coalesce(LOG.NB_DEL,0)
          ,coalesce(LOG.NB_ERR,0)
          ,STEP.STEP_NAME
          ,STEP.NNO
          from SNP_STEP_LOG LOG
          inner join SNP_SESS_STEP STEP
          on STEP.SESS_NO = LOG.SESS_NO
          and STEP.NNO = LOG.NNO
          WHERE LOG.SESS_NO = ?
          ORDER BY STEP.NNO
          """

          STEP_BEG = 0
          STEP_END = 1
          STEP_DUR = 2
          STEP_STATUS = 3
          NB_ROW = 4
          NB_INS = 5
          NB_UPD = 6
          NB_DEL = 7
          NB_ERR = 8
          STEP_NAME = 9
          STEP_NO = 10

          SelectTask = """
          select
          LOG.TASK_BEG
          ,LOG.TASK_END
          ,coalesce(LOG.TASK_DUR,0)
          ,LOG.TASK_STATUS
          ,coalesce(LOG.NB_ROW,0)
          ,coalesce(LOG.NB_INS,0)
          ,coalesce(LOG.NB_UPD,0)
          ,coalesce(LOG.NB_DEL,0)
          ,coalesce(LOG.NB_ERR,0)
          ,TASK.TASK_NAME3
          from SNP_SESS_TASK_LOG LOG
          inner join SNP_SESS_TASK TASK
          on TASK.SESS_NO = LOG.SESS_NO
          and TASK.NNO = LOG.NNO
          and TASK.SCEN_TASK_NO = LOG.SCEN_TASK_NO
          WHERE LOG.SESS_NO = ?
          AND LOG.NNO = ?
          ORDER BY LOG.SCEN_TASK_NO
          """
          TASK_BEG = 0
          TASK_END = 1
          TASK_DUR = 2
          TASK_STATUS = 3
          TASK_ROW = 4
          TASK_INS = 5
          TASK_UPD = 6
          TASK_DEL = 7
          TASK_ERR = 8
          TASK_NAME = 9

          SessionStatuses = {'M':'Warning','E':'Err','D':'Done','R':'Run'}
          StepStatuses = {'M':'Warn','E':'Err','D':'Done','W':'Wait','R':'Run'}

          SessionRows =Cursor.execute(SelectSession,(TimeBegin,TimeEnd)).fetchall()

          for SessionRow in SessionRows:

          if options.session and options.session.upper() != SessionRow[SESS_NAME].upper():
          # Not requested
          continue

          if SessionRow[SESS_NAME] in ('SOCKETSERVER','PROCESSHUB'):
          # Skip these utilities
          continue

          """
          if SessionRow[SESS_STATUS] == 'R':
          # Still running, nothing to print(
          docprint( '\n%s, status %s' % (
          SessionRow[SESS_NAME]
          ,SessionStatuses[SessionRow[SESS_STATUS]]
          ))
          continue
          """
          if SessionRow[SESS_END]:
          SessionEnd = SessionRow[SESS_END].strftime(TimeFormat)
          else:
          SessionEnd = ' '

          SessionHistories = Cursor.execute(SelectSessionHistory,(SessionRow[SESS_NAME],SessionRow[SESS_NO])).fetchall()

          docprintnocr( '\n%-20s\n\t%s / %s %6i secs %s' % (
          SessionRow[SESS_NAME][:20]
          ,SessionRow[SESS_BEG].strftime(TimeFormat)
          ,SessionEnd
          ,SessionRow[SESS_DUR]
          ,SessionStatuses[SessionRow[SESS_STATUS]]
          ))

          for SessionHistory in SessionHistories:
          if SessionHistory[SESS_END]:
          SessionHistoryEnd = SessionHistory[SESS_END].strftime(TimeFormat)
          else:
          SessionHistoryEnd = ' '
          docprintnocr( '\t%s / %s %6i secs %s' % (
          SessionHistory[SESS_BEG].strftime(TimeFormat)
          ,SessionHistoryEnd
          ,SessionHistory[SESS_DUR]
          ,SessionStatuses[SessionHistory[SESS_STATUS]]
          ))

          if not options.step:
          # Step detail not requested
          continue

          docprint( '\n %-22s %5s %4s %8s %8s %8s %8s %8s' % (
          ' '
          ,'Secs'
          ,'Stat'
          ,'Rows'
          ,'Inserts'
          ,'Updates'
          ,'Deletes'
          ,'Errors'
          ))

          for StepRow in Cursor.execute(SelectStep,SessionRow[SESS_NO]).fetchall():
          try:
          docprint( ' %-22s %5i %-4s %8i %8i %8i %8i %8i' % (
          StepRow[STEP_NAME][:22]
          ,StepRow[STEP_DUR]
          ,StepStatuses[StepRow[STEP_STATUS]]
          ,StepRow[NB_ROW]
          ,StepRow[NB_INS]
          ,StepRow[NB_UPD]
          ,StepRow[NB_DEL]
          ,StepRow[NB_ERR]))
          except Exception, e:
          docprint(e)
          continue

          if not options.task:
          # Task detail not requested
          continue

          try:
          for TaskRow in Cursor.execute(SelectTask,(SessionRow[SESS_NO],StepRow[STEP_NO])).fetchall():
          docprint( ' %-21s %5i %-4s %8i %8i %8i %8i %8i' % (
          TaskRow[TASK_NAME][:21]
          ,TaskRow[TASK_DUR]
          ,TaskRow[TASK_STATUS]
          ,TaskRow[TASK_ROW]
          ,TaskRow[TASK_INS]
          ,TaskRow[TASK_UPD]
          ,TaskRow[TASK_DEL]
          ,TaskRow[TASK_ERR]
          ) )
          except Exception, e:
          docprint( e )

          docprint( '\nEnd of report')

          Connection.close()

          if options.printonly:
          for line in document:
          print line
          sys.exit(0)

          import smtplib
          Message = """From: %s
          To: %s
          MIME-Version: 1.0
          Content-type: text/html
          Subject: %s

          <font face="courier" size="4"><b>%s</b></font>

          """ % (
          options.mailuser
          ,options.recipientlist
          ,'Session Report'
          ,'<br>'.join(document).replace('\n','<br>').replace('\t',' ').replace(' ',' ')
          )
          server = smtplib.SMTP(options.mailserver)
          server.sendmail(options.mailuser,options.recipientlist,Message)
          server.quit()
          • 2. Re: how to get the Interface inserted rows fom ODI Reporsitory
            AMSI
            THanks,
            but i need code in sql. is there any short code that give me the only total number inserted records after loading the data in Interface. >>>Operator???

            I just want to select this total Inserted number of record into my Table.???

            Regards,
            • 3. Re: how to get the Interface inserted rows fom ODI Reporsitory
              Bhabani Ranjan
              Hi AMSI
              You can just right simple insert query and provide values with below codes.

              *<%=odiRef.getPrevStepLog("INSERT_COUNT")%>*
              <%=odiRef.getPrevStepLog("DELETE_COUNT")%>
              <%=odiRef.getPrevStepLog("UPDATE_COUNT")%>
              <%=odiRef.getPrevStepLog("ERROR_COUNT")%>
              <%=odiRef.getPrevStepLog("STEP_NAME")%>

              Thanks
              Bhabani
              http://bhabaniranjan.com/
              • 4. Re: how to get the Interface inserted rows fom ODI Reporsitory
                Maqs-Oracle
                Hi,

                ODI work repository schema has SNP_SESSION, SNP_SESS_STEP, SNP_STEP_LOG tables which stored information like package name ,Step name, inserted records, updated records etc.

                Try this query

                Select step.step_name, steplog.step_beg, steplog.step_end, steplog.nb_ins
                from snp_session sess, snp_sess_step step, snp_step_log steplog
                where sess.sess_no = step.sess_no and steplog.sess_no = sess.sess_no


                Hope this helps.

                Thanks
                • 6. Re: how to get the Interface inserted rows fom ODI Reporsitory
                  Maqs-Oracle
                  Hi,

                  Please mark as helpful /correct if the above query helped :)

                  Let me know if you were looking for something else.

                  Thanks
                  • 7. Re: how to get the Interface inserted rows fom ODI Reporsitory
                    AMSI
                    I need Current session no. Not previous method..????
                    • 8. Re: how to get the Interface inserted rows fom ODI Reporsitory
                      1037485

                      Hi , Can you pelase explain where call this code , i am also having the similar requirement

                      • 9. Re: how to get the Interface inserted rows fom ODI Reporsitory
                        DecaXD

                        Hi,

                         

                        no one is replying you because you are necroposting. Open a new thread.