DSXchange

Hi All,

Need your help with an issue we are having in our production environment. We did raised an incident with IBM but its taking a longer time.
Wanted to check in the community if there is any work around for this issue.

Datastage Version : 9.1
Job Description : we are performing a change data capture between the source and target table to perform Data reconciliation.
Source : Sybasae DB ( Using Sybase Enterprise - Read DB type ASE)
Target : Netezza
Issue:
When connecting to Sybase DB we are having an getting an error as below, but in the job log its logging as an Information message, but not as an Error.

Because of this the job is not aborting and all the records after the connection failure were getting identified as "Delete" records.

Job Log:

Item #: 39
Event ID: 31561
Timestamp: 2018-08-06 21:55:31
Type: Info
User Name: edsadm
Message Id: IIS-DSEE-SYAS-00019
Message: Src_Sybase_Connection,0: CT-Lib Error: ct_fetch(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect.

Any help is greatly appreciated.

Regards,
Hari

Welcome aboard.

Has someone instituted a Message Handler to demote this message to Informational?

If not, you can institute a Message Handler to promote the message to Warning. Then, in an after-job subroutine, you can test the job's interim status and cause it to abort if a warning has been generated.

A more finely tuned approach in the after-job subroutine would be to interrogate the job log for that particular message, and abort if it's found.

The best approach, though, would be to create an earlier job that does nothing but test the connection (running a small and innocuous SELECT query). Then use the result of that to determine whether to run your "real" job at all.

Hi Ray thanks for the response. No,We checked all the Message Handlers, there are no handlers to demote it to Informational.

There are few other warnings in the job for kind of size mismatch. But if we have to fail based if there is a warning then its very tricky.

We can not actually link the same job log while the job is running to find the message in the log. I am trying to write a routine using the DSmacros, but what is the correct macro to check for an error message. Can you please guide.

Interesting. I spent some time searching about message handles and for some reason all I could find was their ability to demote messages, not promote them in spite of my recollection that you could do both. So didn't chime in.

As to the "very tricky" comment, do you have more than the one message you mentioned that you'd want to handle? The solution to your problem as posted is pretty straight-forward, as Ray explained. Specifically, a small custom routine called After Job leveraging a call to DSGetJobInfo with InfoType DSJ.JOBINTERIMSTATUS as documented here. Then you decide what to do based on the result, which could include causing a job that was going to complete with warnings to abort.

Some of the stages have a property to "Treat Warnings as Fatal" but did not see that in the docs for the Sysbase Enterprise stage.

Is there a Way we can search for a particular string in the Job log using any of the DSMacros? I tried searching but unable to find one.

Using a SyBase OC stage instead of Sybase Enterprise help? I tried connecting using Sybase OC stage and the Db is connecting fine. But this issue is an intermittent issue, so unable to replicate the same in Non prod environment.

Can't answer the second question as I don't have any Sybase experience.

As to the first question, no not via macros but that could just be a terminology issue. You would need to leverage the API built-in for tasks like that, something that can be done from the command line but which (IMHO) you should approach via the BASIC Interface using a custom routine. In particular note the various DSGetLog* functions. Once you are looping through them inside the routine, other string functions could do the search. Note, however, it's not something that a single post would cover, I think there's a full semester course on the topic at Wurlod University.

All that being said, don't think it's an approach you need to take. Just the message handler and the JOBINTERIM status approach should cover this. If you don't believe that to be true, explain to us why so we can provide better targeted answers.