IBM® InfoSphere™ Information Server 8.7

IBM® InfoSphere™ Information Server 8.7 helps you take delivery of trusted information to the next level with a more comprehensive integration platform. New InfoSphere Information Server capabilities help strengthen collaboration between business and IT professionals to make it easier for strategic initiatives, such as business analytics, master data management, data warehousing, and application consolidation and migration projects, to utilize and act on trusted information.
IBM InfoSphere Information Server 8.7 helps you turn insight into actionable results by:

• Capitalizing on productivity enhancements
  Deliver better business results and reduce implementation time and cost by using new features that make sure your data integration platform is working for you. Take advantage of new operational intelligence capabilities, smart management of metadata and metadata imports, and new parallel debugging capabilities that enhance developer productivity when working with partitioned data.
• Leveraging comprehensive information governance capabilities
  Establish smarter, sustainable governance practices - including enriched data quality capabilities - that meet your business requirements and support consistent business outcomes. Gain more control when managing terms through a review and approval process, and increase your insight with holistic and personalized views of business terms, data models, assets, data flows and their relationships.
• Harnessing the power of new and stronger integration
  Promote collaboration and streamline the process of delivering enterprise business solutions through tight and deep metadata integration with key systems to take advantage of new opportunities.
• Using big data support to successfully address the most challenging data volumes in your business
  Organizations are increasingly adopting big data tooling to address the most challenging data volumes in their business. As part of this release, InfoSphere Information Server 8.7 will support big data and Hadoop, enabling you to directly access big data on a distributed file system

Examples of the significant new integration capabilities delivered as part of InfoSphere Information Server 8.7 include:

• New Netezza connectivity is delivered as part of this release, via a Netezza Connector that provides native connectivity to the Netezza appliance and supports rapid loading of data to the appliance as well as balanced optimization, so you can easily determine and adjust where transformation will occur—in the source database using the high-performance InfoSphere DataStage engine, and/or in the target Netezza database(s).
• A new configuration option for the database connectivity layer of Information Server creates an audit log for any database operation directly into InfoSphere Guardium
• A new InfoSphere Change Data Capture stage, as well as expanded metadata capture with Data Replication/Change Data Delivery for end-to-end data lineage
• A new Teradata Connector TMSM and dual load support to help Teradata users better manage disaster recovery
• Advanced capabilities to identify, cleanse, and manage metadata for SAP projects help SAP project managers meet their go-live dates

Conversion error calling conversion routine date from string data may have been lost

I have a file which has five columms. A field "pdate" is char data type which has date values (format ddmmyy eg 011008). This date value is need to be converted before loading into a target table in oracle 10g.
I have used the converion function StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd-%mm-%yy") to convert the char value into date.
.

The job is: Seq file--->TRANSFORMER---->SeqFile

When I'm executing this job it gives the following error:

Tr_Split,1: Conversion error calling conversion routine date_from_string data may have been lost


In the output file
The "pudate" has '*******' being propagated.


The input date column will not have null values.

Input Data- Char data type

011008

Output Data - date data type

******
-------------------------------------------

Solution::::

The conversion function should not have hyphens as your data doesn't have it. StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy") should work. or   This warning will also occurs if data itself contains junk data that does not support the required format even though conversion function StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy") is present. Best case is replace the source file which does not have junk data.

TNS entry error when trying to connect to a database using DS in order to import the table definition?

This usually indicates that either the DataStage userid running the job does not have read access to the Oracle run-time client files, or that the environment variable ORACLE_HOME is not defined.


ORA-12154: TNS:could not resolve service name
The above  error indicate that the connection identifier or service name specified in the DataStage job (or in the ORACLE_SID environment variable or ODBC definition) was not known to either the Oracle client or the Oracle server. The connection/service identifiers known to the Oracle run-time client are defined in the tnsnames.ora file, $ORACLE_HOME/network/admin/tnsnames.ora
Verify that the identifier specified for the failing Oracle connection has been defined in tnsnames.ora. If it is correctly defined, then next verify that the ORACLE_HOME environment variable is correctly defined, and that tnsnames.ora file has correct read permissions.
If the above items are configured correctly, also check the listener.log on Oracle server to confirm that the service id (or the database it maps to) are known to the Oracle server.
Setting up environment variables required to use Oracle run-time client
The Oracle client requires that the following environment variables be defined. These should be set in the .dsenv file in the DataStage DSEngine directory.

ORACLE_HOME=/home/oracle
LIBPATH=$LIBPATH:$ORACLE_HOME/lib:
PATH=$PATH:$ORACLE_HOME/bin

Change the path defined for ORACLE_HOME to the correct path for your system. ORACLE_HOME should be set to the absolute path to the home Oracle directory which is the directory level directly above the lib and bin directories.

Please also note that the name of the library path environment variable varies with different operating systems:

• AIX - use LIBPATH
• Solaris - use LD_LIBRARY_PATH
• HP-UX - use SHLIB_PATH
• Linux - use LD_LIBRARY_PATH

Parallel job: Retrieve sql codes on a failed upsert 

Introduction

When an enterprise database stage such as DB2 or Oracle is set to upsert it is possible to create a reject link to trap rows that fail any update or insert statements. By default this reject link holds just the columns written to the stage, they do not show any columns indicating why the row was rejected and often no warnings or error messages appear in the job log.

Steps

There is an undocumented feature in the DB2 and Oracle enterprise stage where a reject link out of the stage will carry two new fields, sqlstate and sqlcode. These hold the return codes from the RDBMS engine for failed upsert transactions. The fields are called sqlstate and sqlcode.
To see these values add a peek to your reject link, the sqlstate and sqlcode should turn up for each rejected row in the job log. To trap these values add a copy stage to your reject link, add sqlstate and sqlcode to the list of output columns, on the output columns tab check the "Runtime column propagation" check box, this will turn your two new columns from invalid red columns to black and let your job compile. If you do not see this check box use the Administrator tool to turn on column propagation for your project.
When the job runs and a RDBMS reject occurs the record is sent down the reject link, two new columns are propagated down that link and are defined by the copy stage and can then be written out to an error handling table or file.
If you do not want to turn on column propagation for your project you can still define the two new columns with a Modify stage by creating them in two specifications. sqlcode=sqlcode and sqlstate=sqlstate. Despite column propagation being turned off the Modify stage will still find the two columns on the input link and use the specification to add them to the output schema.

Examples

Oracle By default, oraupsert produces no output data set. By using the -reject option, you can specify an optional output data set containing the records that fail to be inserted or updated. It’s syntax is: -reject filename For a failed insert record, these sqlcodes cause the record to be transferred to your reject dataset: -1400: cannot insert NULL -1401: inserted value too large for column -1438: value larger than specified precision allows for this column -1480: trailing null missing from string bind value
For a failed update record, these sqlcodes cause the record to be transferred to your reject dataset: -1: unique constraint violation -1401: inserted value too large for column -1403: update record not found -1407: cannot update to null -1438: value larger than specified precision allows for this column -1480: trailing null missing from string bind value An insert record that fails because of a unique constraint violation (sqlcode of -1) is used for updating.
DB2 When you specify the -reject option, any update record that receives a status of SQL_PARAM_ERROR is written to your reject data set. It’s syntax is: -reject filename

Conclusion

Always place a reject link on a Database stage that performs an upsert. There is no other way to trap rows that fail that upsert statement.
For other database actions such as load or import a different method of trapping rejects and messages is required.