Infosphere Knowledge Resources

IBM Knowledge Center The IBM Knowledge Center is the IBM product documentation Site designed to help you plan, install, configure, use, tune, monitor, troubleshoot, and maintain the product. URL:  https://www.ibm.com/support/knowledgecenter/ IBM InfoSphere Information Server Version 11.7.0 documentation The IBM InfoSphere Information Server Version 11.7.0 documentation provides IIS specific documentation about how to install and use … More Infosphere Knowledge Resources

Essbase Connector Error – Client Commands are Currently Not Being Accepted

While investigating a recent Infosphere Information Server (IIS), Datastage, Essbase Connect error I found the explanations of the probable causes of the error not to be terribly meaningful.  So, now that I have run our error to ground, I thought it might be nice to jot down a quick note of the potential cause of … More Essbase Connector Error – Client Commands are Currently Not Being Accepted

Parallel jobs on Windows fail with APT_IOPort::readBlkVirt;error

This a known error for windows systems and applies to DataStage and DataQuality jobs using the any or all the three join type stages (Join, Merge, and Lookup). Error Message <<Link name>>,0: APT_IOPort::readBlkVirt: read for block header, partition 0, [fd 4], returned -1 with errno 10,054 (Unknown error) Message ID IIS-DSEE-TFIO-00223 Applies To Windows systems … More Parallel jobs on Windows fail with APT_IOPort::readBlkVirt;error

DataStage – How to Pass the Invocation ID from one Sequence to another

When you are controlling a chain of sequences in the job stream and taking advantage of reusable (multiple instances) jobs it is useful to be able to pass the Invocation ID from the master controlling sequence and have it passed down and assigned to the job run.  This can easily be done with needing to … More DataStage – How to Pass the Invocation ID from one Sequence to another

DataStage – How to use single quoted parameter list in an Oracle Connector

While working with a client’s 9.1 DataStage version, I ran into a situation where they wanted to parameterize SQL where clause lists in an Oracle Connector stage, which honestly was not very straight forward to figure out.  First, if the APT_OSL_PARAM_ESC_SQUOTE is not set and single quotes are used in the parameter, the job creates unquoted … More DataStage – How to use single quoted parameter list in an Oracle Connector

InfoSphere Information Server Suites

The Six Suites of Infosphere Information Server InfoSphere Information Server 11 Series is, primarily, made up of 6 suites / packages, which provide a mix of components: InfoSphere Information Governance Catalog BigInsights BigIntegrate BigInsights InfoSphere Information Server for Data Integration InfoSphere Information Server for Data Quality InfoSphere Information Server Enterprise Edition (is inclusive of all … More InfoSphere Information Server Suites

What are the dashDB isolation levels?

  Isolation levels are part of the ACID (Atomicity, Consistency, Isolation, Durability) paradigms in database control.  Isolation levels allow developers and user to trade-off consistency for a potential gain in performance. Therefore, it is essential to understand them and how the apply in structured Query Language(SQL).  The dashDB RDBMS has four isolations levels: Repeatable Read … More What are the dashDB isolation levels?

SFDC Salesforce Connector – Column Returns Null values, when SOQL Returns Data in Workbench

Recently, encountered a scenario, which is a little out of the norm while using the SFDC Connector.  Once the issue is understood, it is easily remedied. The problem / Error SOQL run in Salesforce workbench and column returns data The DataStage job/ETL runs without errors or warnings The target column output only returns null values … More SFDC Salesforce Connector – Column Returns Null values, when SOQL Returns Data in Workbench

IBM Db2 on Cloud, IBM Db2 Warehouse, IBM Db2 Warehouse on Cloud (Previously IBM dashDB), and IBM Integrated Analytics System – Useful links

Here are a few references for IBM Db2 on Cloud, IBM Db2 Warehouse, IBM Db2 Warehouse on Cloud (Previously IBM dashDB), and IBM Integrated Analytics System – Useful links, which hopefully will be helpful.  Table Of Useful IBM Db2 on Cloud, IBM Db2 Warehouse, IBM Db2 Warehouse on Cloud (Previously IBM dashDB), and IBM Integrated … More IBM Db2 on Cloud, IBM Db2 Warehouse, IBM Db2 Warehouse on Cloud (Previously IBM dashDB), and IBM Integrated Analytics System – Useful links

Datastage – When checking operator: Operator of type “APT_TSortOperator”: will partition despite the preserve-partitioning flag on the data set on input port 0

The APT_TSortOperator  warning happens when there is a conflict in the portioning behavior between stages.  Usually, because the successor (down Stream) stage has the ‘Partitioning / Collecting’ and ‘Sorting’ property set in a way that conflicts with predecessor (upstream) stage’s properties, which it is set to preserver.  This can occur when the successor stage has … More Datastage – When checking operator: Operator of type “APT_TSortOperator”: will partition despite the preserve-partitioning flag on the data set on input port 0

Netezza / PureData – What is the maximum length of CHAR, VARCHAR, and NVARCHAR fields

During a recent project, I had reason to investigate what the maximum character field links for Netezza to be able to fit the data ETL ‘ed from the source.  The constraints on Netezza character fields are not as long as some other databases, therefore, I thought I would document and a quick post for future … More Netezza / PureData – What is the maximum length of CHAR, VARCHAR, and NVARCHAR fields

*DataStage*DSR_PROJECT (Action=8); check DataStage is set up correctly in project

Basically, the Action=8 error, which I normally see when opening the DataStage Director Client application, means that one or more of the RT_LOG files have become corrupted.  Usually, this problem occurs in relation to disk space issues; although, there can be other causes. Error Message Error calling subroutine: *DataStage*DSR_PROJECT (Action=8); check DataStage is set up … More *DataStage*DSR_PROJECT (Action=8); check DataStage is set up correctly in project

*DataStage*DSR_SELECT (Action=3); check DataStage is set up correctly in project

Having encountered this DataStage client error in Linux a few times recently, I thought I would document the solution, which has worked for me. Error Message: Error calling subroutine: *DataStage*DSR_SELECT (Action=3); check DataStage is set up correctly in project (Subroutine failed to complete successfully (30107)) Probable Cause of Error NodeAgents has stopped running Insufficient /temp … More *DataStage*DSR_SELECT (Action=3); check DataStage is set up correctly in project

Netezza/ PureData – how to add a primary key

While primary keys (PK) are not enforced within Netezza, they still provide significant value and should be added.  Among the values that adding primary key provides are: Inform tools, which have meta Data import capabilities; for example, Aginity, ETL tools, data modeling tools, Infosphere Data Architect, DataStage and DataQuality, Infosphere Information Server suite of tools … More Netezza/ PureData – how to add a primary key

Netezza – [SQLCODE=HY000][Native=46] ERROR: External Table : count of bad input rows reached maxerrors limit

While helping a customer we encountered the [SQLCODE=HY000][Native=46] ERROR, which was a new one for me. So here are a few notes to help the next unlucky soul may run into the error. Netezza Error Reason: [SQLCODE=HY008][Native=51] Operation canceled; [SQLCODE=HY000][Native=46] ERROR: External Table : count of bad input rows reached maxerrors limit What Does the … More Netezza – [SQLCODE=HY000][Native=46] ERROR: External Table : count of bad input rows reached maxerrors limit

InfoSphere DataStage – How to calculate age in a transformer

Occasionally, there is a need to calculate the between two dates for any number of reasons. For example, the age of a person, of an asset, age of an event.  So, having recently had to think about how to do this in a DataStage Transformer, rather in SQL, I thought it might be good to … More InfoSphere DataStage – How to calculate age in a transformer

Netezza Connector Stage, Table name required warning for User-defined SQL Write mode

Recently, while working at a customer site and I encountered an anomaly in the Netezza Connector stage, when choosing ‘User-defined SQL’ write mode, the ‘Table name’ displays a caution/warning even though a table name should not be required.  If you are using a user-defined SQL statement and/or have parametrized your SQL scripts to make the job … More Netezza Connector Stage, Table name required warning for User-defined SQL Write mode

Surrogate Key File Effective Practices

Here are a few thoughts on effectively working with IBM Infosphere, Information Server, DataStage surrogate key files, which may prove useful for developers. Placement The main thing about placement is that it be in a consistent location. Developers and production support teams should need to guess or look up where it is for every DataStage … More Surrogate Key File Effective Practices

Productivity Tip – Quickly create a new surrogate key file

This productivity tip, is how we can quickly create a new surrogate key file in Linux.  This example is leveraging native capabilities of Red Hat Enterprise Linux (RHEL) to skip a few commands, by using an existing surrogate key file to create a new surrogate file with a minimum of keys strokes and command line entries. … More Productivity Tip – Quickly create a new surrogate key file

Productivity Tip – Changing Owner and Groups on Surrogate Key File

This practice tip, is how we quickly update surrogate key file owner and group in Linux.  This example is leveraging native capabilities of Red Hat Enterprise Linux (RHEL) to skip a few commands, by using a combined command to set both the owner and group of a surrogate key file in a single command. Surrogate … More Productivity Tip – Changing Owner and Groups on Surrogate Key File

Infosphere Datastage – Client Tier Image Requirements

A frequent question encountered in the early stages of a client engagement is: what are the recommended Windows Client Tier Image Requirements (Virtual machine image or Desktop)?  However, I have never found this information to be satisfactorily documented by IBM.  So, invariably, we end up providing our best guidance based on experience, which in the … More Infosphere Datastage – Client Tier Image Requirements

Are the Infosphere Windows Client 32 bit or 64 bit?

The question of whether or not the IBM InfoSphere Windows client tools 32 bit or 64 bit, actually, comes up rather frequently. The short answer to the question is that the InfoSphere Windows client tools, actually, are 32 bit applications, will run on supported 64 bit windows system.  This is what IBM calls 64-Tolerate: (32-bit … More Are the Infosphere Windows Client 32 bit or 64 bit?

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 … More DataStage – IIS-DSEE-TBLD-00008- Processing Input Record APT_Decimal Error

DataStage – How to add or subtract from a date in a transformer stage

From time to time there is a need to adjust a date within a DataStage transformer.  It could be as small subtracting a day to set a batch processing date to yesterday or something bigger.  If you need to adjust a date by a year, month and/or day the DateOffsetByComponents function is the method.  The … More DataStage – How to add or subtract from a date in a transformer stage

Infosphere DataStage – How To Convert Char(0)

Char(0) is a common hidden character, which sometimes needs to be dealt with to ensure lookups function correctly and other reasons.  The convert function can be used to replace Char(0) or other values as necessary.  Within a DataStage transformer stage the process is straight forward enough, so, here are some examples to get you started. … More Infosphere DataStage – How To Convert Char(0)

IBM InfoSphere Licensing – DataStage and DataQuality

Licensing is one of the major factors which can limit Infosphere performance, flexibility, and extensibility. Licensing can affect you performance through the type of product which being licensed and the Quality of CPU’s Cores being licensed, and the operating system for the license was purchased. The Type Of Product For Which Was Licensed Was Or … More IBM InfoSphere Licensing – DataStage and DataQuality