DataStage – IIS-DSEE-TBLD-00008- Processing Input Record APT_Decimal Error

This another one of those nebulas error messages, which can cost a lot of time in research, if you don’t know how to simplify the process a bit.  However, determining where the error is can be a bit of a challenge if you have not encountered this error before and figured out the trick, which isn’t exactly intuitive.

In this case, as it turned out after I had determined where the error was, it was as simple as having missed resetting the stage variable properties, when the other decimal fields increased.

How to identify where this error occurs?

Disabling the APT_DISABLE_COMBINATION environment variable by:

  • adding the APT_DISABLE_COMBINATION environment variable to the job properties
  • setting the  APT_DISABLE_COMBINATION environment variable it to true in the job properties
  • compiling the job and running the job again

This approach will, usually, provide a more meaningful identification of the stage with the error.

Note:  Please remember to remove the APT_DISABLE_COMBINATION environment variable before moving it to testing and/or releasing your code in production.

Message ID

IIS-DSEE-TBLD-00008

Error Message with combine enabled:

APT_CombinedOperatorController(1),0: Exception caught in processingInputRecord() for input “0”: APT_Decimal::ErrorBase: From: the source decimal has even precision, but non-zero in the leading nybble, or is too large for the destination decimal… Record dropped. Create a reject link to save the record if needed.

Error message with combine disabled

Tfm_Measures_calc,0: Exception caught in processingInputRecord() for input “0”: APT_Decimal::ErrorBase: From: the source decimal has even precision, but non-zero in the leading nybble or is too large for the destination decimal… Record dropped. Create a reject link to save the record if needed.

Note: Measures_calc is the stage name

Related References

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”)

Infosphere Director Scheduler

First let us start with dispelling a common myth, The InfoSphere scheduler is not an Enterprise Scheduler application.

The scheduling service in InfoSphere leverages the operating system (OS) scheduler, in the case of Linux, this is CRON, and provides graphical User Interface, which a provides time-based capability to schedule Jobs at the Suite component level.  The Director Client scheduler can:

  • Schedule individual jobs and sequence jobs
  • Schedule Jobs/Sequencers to run:
  • Today
  • Tomorrow
  • Every
  • Next
  • Daily

How to set-up the Daily Schedule

Steps to set the Daily Schedule are below:

  1. Open the DataStage Director
  2. Once the browser is open click on Job>Add to Schedule
  3. Click “Daily” under the “Run Job” and choose the time

Related References

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”.

Netezza JDBC Error – Unterminated quoted string

The ‘Unterminated quoted string’ error occurs from time to time when working with the InfoSphere DataStage Netezza JDBC Connector stage and is nebulas, at best.  However, the solution is, normally, straight forward enough once you understand it.  Usually, this error is the result of target table fields or field being shorter than the input data.  The fix is, normally, to compare you input field lengths (or composite field length, if consolidation fields into one field) and adjusting the field length higher.  In some cases, if business rules allow you may be able to substring or truncate the input data length (not a recommended approach), but information can be lost with this approach.

Error

org.netezza.error.NzSQLException: ERROR:  Unterminated quoted string

Example Error Message

Tgt_IIS_Job_Dim,0: The connector encountered a Java exception:  org.netezza.error.NzSQLException: ERROR:  Unterminated quoted string    at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:287)    at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)  at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)       at org.netezza.sql.NzStatement._execute(NzStatement.java:885)           at org.netezza.sql.NzPreparedStatament.executeUpdate(NzPreparedStatament.java:229)   at com.ibm.is.cc.jdbc.CC_JDBCRecordDataSetConsumer.executeStatements(CC_JDBCRecordDataSetConsumer.java:2846)               at com.ibm.is.cc.jdbc.CC_JDBCBigBufferRecordDataSetConsumer.consumeBigBuffer(CC_JDBCBigBufferRecordDataSetConsumer.java:712)

How to Schedule an Infosphere Datastage Job in crontab

This is a quick, easy, shortcut way to schedule an InfoSphere DataStage job in Linux crontab to take advantage of capabilities within crontab not available in the InfoSphere graphical user interface (GUI).

For this example, the job has been adjusted from the stand InfoSphere scheduler graphical user interface (GUI) setting to run every 15 minutes, which is not available in the GUI.

The Basic crontab Scheduling Process

  • Schedule the job in DataStage Director
  • Login into Linux as the user, who created the schedule
  • Run ‘crontab -e’ command
  • Edit crontab command line using VI commands
  • Saves changes

Note: The revised schedule if different from the InfoSphere scheduler GUI standard setting will not display as changed in the GUI.  However, the jobs will run as scheduled, if edited correctly, and can be verified in the Director Client.

Related links:

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.