Capturing ORACLE error

[sri1dhar]

I am designing a parallel job (Parallel Extender) and using ORACLE stage.
If there is a reject becuase of ORACLE constraints violation I need to capture the ORACLE error code and message and log into a table.

I was able to do this earlier in a server job (ORA OCI9 stage) by using LinkVariable -> DBMSCODE and using the
following derivation in a transformer stage:
Field(DSGetLogEntry(DSJ.ME,DSGetNewestLogId (DSJ.ME,DSJ.LOGANY)-1) ,":",4,5)

In parallel job oracle stage I am using reject link by setting the output reject record = TRUE to capture reject rows. But the reject link outputs only data and I can't capture the error code. I couldn't locate the equivalent of DBMSCODE in Parallel Extender - Oracle Stage.

Is there a way I can captre ORA error code & message.

Also in case of rejects through reject link, there will not be any warnings generated in the log. How can I write a warning for each reject?

Any help is greatly appreciated.

[kduke]

sri1dhar

It keep seeing people make the same mistakes with the built-in routines to get log information. These routines are not available real-time. You never know when they are going to write to log. It could be at the of a job. The same is true for row counts and other API routines. Most of these routines are not meant to be called from the current running job. They are meant to be called after the fact or from other processes. If you attach to the current running job and then release it then that job hangs.

The best way to use these routines is after the job has finished. Then you need to loop through all the log entries from the last record until you reach the start of the last job run. This is not a simple task to figure out which record is the start of a job. It definitely is not complex either but you have to look at each record to see what is in there because there is little or no documentation on what DataStage stores in these records. Ray, Hester and Ken have figured out the hard way what is stored in these records. I do it only when it is needed to solve the task at hand. If they are willing to share then they can save you some time. Otherwise you can hire one of us to build it for you or you have to spend some time and learn how RT_LOG works.

To do this stuff remotely is not usually worth it. You are talking about a few hours work if we know exactly what you want. We could probably do it and bill it through this web site if all parties can agree on how to resolve disputes. If you tell me you need x done and then you add y to it later then you may not get what you wanted and to do y may take 5 times longer than x. I do think at some point a lot of work is going to be handled in this way. The other issue is who owns the code produced. Can I post the code on this web site when I finish with it? Most of the time I want to own the code. You own your code and use it on your project but I may want to use it again to help someone else. Unless you are in the business of building tools for DataStage users then why should you care what I do with the code. If it takes me 2 hours what would take you 2 days then it would be worth it to outsource it. If I work from home then I charge half my usual rate.

There has been a lot of topics on RT_LOG hash file. Do a search and maybe it has already been posted.

I hope to someday wrap these routines up into a nice package and post them as downloads on this site. Ray and Ken have some cool routines they have already posted on http://www.datastagexchange.com. What you need is a GetOracleErrorsFromLog(JobName) routine. Once we get this then we need the same of other databases. If I do the work at a discounted rate then let me share the code so next time it is free. I think what Craig and others have done is include these errors in their email that is sent when a job fails.

I think we are spending a lot of time reinventing the same thing. Surely there is a better way.

Kim.

[kduke]

I was bored.

Code: Select all

* -----------------------------------------------------------------------
* Program Name              : GetOracleErrorsFromLog
* Program Category          : DwNav
* Created by                : Kim Greggory Duke
* Programmer                : Kim Duke
* Copyright                 : Duke Consulting, Inc 2003
* Date Created              : 12-10-2003
* -----------------------------------------------------------------------
* Last Modified             : 12-10-2003
* -----------------------------------------------------------------------
* Language                  : Universe BASIC
* -----------------------------------------------------------------------
* Usage                     : GetOracleErrorsFromLog(JobName)
* -----------------------------------------------------------------------
* Notes                     :
* -----------------------------------------------------------------------
      Ans = ""
      OracleErr = "ORA-"
* -----------------------------------------------------------------------
* open DS_JOBS to get JobNo to get to log file
* -----------------------------------------------------------------------
      open "DS_JOBS" to DsJobs else stop
      read DsJobsRec from DsJobs, JobName then
         JobNo = DsJobsRec<5>
         LogFile = "RT_LOG":JobNo
* -----------------------------------------------------------------------
* open RT_LOG file
* -----------------------------------------------------------------------
         open LogFile to RtLog else stop
* -----------------------------------------------------------------------
* get starting RT_LOG key of last job run
* -----------------------------------------------------------------------
         read StartRec from RtLog, '//JOB.STARTED.NO' else StartRec = 1
         StartNo = StartRec<1,1>
* -----------------------------------------------------------------------
* get max key and work backwards
* -----------------------------------------------------------------------
         read EndRec from RtLog, '//SEQUENCE.NO' else EndRec = 1
         EndNo = EndRec<1,1>
* -----------------------------------------------------------------------
         for RtLogId=EndNo to StartNo step -1
            read RtLogRec from RtLog, RtLogId then
               Cnt = 0
               RecLen = len(RtLogRec)
               EndOfRec = @FALSE
* -----------------------------------------------------------------------
* Loop until all OracleErr found
* -----------------------------------------------------------------------
               loop
                  ErrPos = index(RtLogRec, OracleErr, Cnt + 1)
                  if ErrPos > 0 then
                     EndOfErr = @FALSE
                  end
               while not(EndOfRec) do
* -----------------------------------------------------------------------
* strip off everything until you find a space
* may want to add logic to extract error message as well
* -----------------------------------------------------------------------
                  Cnt += 1
                  CurrentErr = ""
                  for i=ErrPos to RecLen until EndOfErr
                     tmp = RtLogRec[i, 1]
                     if tmp = " " then
                        EndOfErr = @TRUE
                     end else
                        CurrentErr := tmp
                     end
                  next i
* -----------------------------------------------------------------------
* concatenate errors together
* -----------------------------------------------------------------------
                  Ans<-1> = CurrentErr
               repeat
            end
         next RtLogId
* -----------------------------------------------------------------------
      end ; * end of DsJobs read

This should get you close. I have no way to test this so let me know if it works. Pleae post the final version.

[sri1dhar]

Kim,

Thanks a lot for taking the time and posting your routine and trying to help me. In response to your fist message about outsourcing, unfortunately I my self am a consultant and looking for answers through the fourm. So you know what I am talking about.

As for the routine and reading datastage log: In a Parallel job (PX) if I use a reject link from an ORACLE stage, the rejects with the ORACLE error code will not be writtend to the log. So there is no way I can capture ORA errors from the log. More over my requirement is to capture the errors for each reject while the job is running (though you mentioned, this should not be done). Let me know if what I articulated is wrong.

In any case this routine is definitely helpful in general, if not for my specific case.

Thanks a lot.

[kduke]

I am not sure in PX if that will work the way you wanted. I was talking about server jobs. I am sorry if I confused you. You maybe able to do it in a PX job. I have not used PX. Let me know.

If you can find where the errors are written then the code I posted should get you close.

[Teej]

Is there a way I can captre ORA error code & message.

Okay, here is the situation:

Whenever you use Oracle, the following file get created in your scratch space:

ora.[random number].ctl
ora.[same random numbers].log
ora.[same random numbers].par

They also use ora.[same random number].fifo.[number] during actual streaming of data.

The .ctl file contains the table definition you provided. The .par file contains the parameters (username/password), which is a known issue with Ascential due to the security risk it poses due to non-encrypted password. The log file contains the actual log.

There is one set of these files for every node that Oracle is assigned to run on. So if you have a 8 node job, you have 8 separate Oracle processes with their own file set. If one fails, the others may not fail the same way, and in fact sometimes would not produce a log file due to being prematurely killed.

I have not yet figured out any patterns to those files, and has relied on the dates, and whenever a job failed, its job number (which is the first set of numbers you find) on the logs to identify the oracle files.

Well. There are more to this than that, but I can tell you this right now:

It is possible. But ... it will be tough to implement.

-T.J.

[sri1dhar]

Teej,

Thanks for your input. I guess the log, par, ctl files will be generated only if I choose the WriteMethod="Load" and not if WriteMethod="Upsert"

Thanks
Sridhar

[Teej]

Oh, those logs get created whenever you use the Oracle stage. Input, Output, load, Upsert. Whatever. Those logs are created. That why it is difficult to figure out who does what -- on a job with 5 Oracle stages, you're looking at 40 different logs after a 8 node run.

-T.J.