Infosphere – decimal_from_string Conversion Error

This is another one of those nebulas error, which can kick out of DataStage, DataQuality, and/or DataClick.  This error can be particularly annoying because it doesn’t identify the field or even the precise command, which is causing the error.  So, there can be more than a field and/or more than one command causing the problem.

Error

Conversion error calling conversion routine decimal_from_string data may have been lost

Resolution

To resolve this error, check for the correct formatting (date format, decimal, and null value handling) before passing to DataStage StringToDate, DateToString, DecimalToString or StringToDecimal functions.  Additionally, even if the formatting is correct, you may need to embed commands to completely clear the issue.

Example

Here is a recent example of command embedding, which has cleared the issue, but I’m sure you will adapt this concept in other ways to meet all your needs.

DecimalToString( DecimalToDecimal( <>,’trunc_zero’),”suppress_zero”)

How to suppress a Change_Capture_Host warning in Datastage

Occasionally, I run into this Change Capture Host defaulting warming, so, I thought this information may be useful.

Event Type

  • Warning

Message ID

  • IIS-DSEE-TFXR-00017

Example Message

  • Change_Capture_Host: When checking operator: Defaulting “<<FieldName>>” in transfer from “beforeRec” to “outputRec”.

Setting Variable

  • Set APT_DISABLE_TFXR0017=1
  • This environment variable can be added either at the project level or at the job level.

Alternative Solution

  • Within the Change Capture stage properties:
    • Stage tab
    • Option
    • Property: “Change Mode
    • Value:  “Explicit key, All Values”.

How to Export a Job Log from Director to a file

Question

  • How do I save the log of a DataStage Job into a file using DataStage Director?

Answer

  • Basically, the log is exported by being printed to a file.

To export and/or Print a Datastage Director log

Set filter

  • Select View > Filter Entries
  • Select ‘Start of Last Run’ to print only the most recent run log.
  • Make sure all Types of Messages are selected to export a complete set of log entries.

Print/export out the Job Log

  • Make sure you are viewing the log details, then,
  • Print the current view:
    1. Display the Print dialog box by:
      1. Choose Project > Print, or
      2. Click the Print button on the toolbar.
    2. Choose the Range of items to print in the Range area:
      1. Select print ‘All entries’ in the current view.
    3. Choose the Print what area:
      1. Select ‘Full details’ to print all details each log entry.
    4. Select the ‘Print to file’ checkbox.
    5. Click OK.
    6. When the Print to file dialog box appears:
      1. Choose the path to which the file will be saved, and
      2. Enter the name of a text file to use.

Note: The default is DSDirect.txt in the client directory.