Troubleshoot IBM DataStage

On the Teradata connector set to ANSI transaction mode, the Before SQL statement may run after the SQL statement instead of before, causing the job to fail.

Workaround: Add a commit statement after each Before SQL statement.

If the mailx command is used in a before-job or after-job subroutine, you must provide the SMTP server info or it will forward execution to sendmail and fail.

If you enter other properties (for example for tables or schemas) with the default Use DataStage properties option selected, and then deselect Use DataStage properties, the properties are not preserved.

Workaround: Deselect the default Use DataStage properties if you intend not to use them before you enter other properties. Otherwise, reselect the properties.

A job with a connector that is processing a large Excel file might fail with this error:

"CDICO9999E: Internal error occurred: IO error: The Excel file is too large. (error code: DATA_IO_ERROR)" 

Try increasing the heap size. The Heap size properties option is in the Other properties section of the connector's Stage tab.

You might have issues when you use the Asset Browser to browse database tables if the selected table contains a column with special characters such as ., $, or #, and you add that table into a DataStage flow. DataStage does not support column names that contain special characters. DataStage flows that reference columns with names that include these special characters will not work.

To work around this problem, create a view over the database table and redefine the column name in the view. For example:

create view view1 as select column1$ as column1, column2# as column2 ... from table

Then, when you use the Asset Browser, find the view and add it to the DataStage flow.

The Asset Browser will read the first 1000 records and infer the schema, such as column name, length, data type, and nullable, based on these first 1000 records in the files in IBM Cloud Object Storage, Amazon S3, Google Cloud Storage, Azure File Storage, Azure Blob Storage, or the Azure Data Lake service. For instance, the Asset Browser might identify a column as an integer based on what is detected in the first 1000 records, however, later records in the file might show that this column ought to be treated as varchar data type. Similarly, the Asset Browser might infer a column as varchar(20) even though later records show that the column ought to be varchar(100).

Error: CDICO9999E: Internal error occurred: Illegal 
state error: INTEGER(32,false) can only annotate INT32.

Workaround: You must use supported data types.

You may face this error while performing the upgrade of Cloud Pak for Data from 5.0.0 to 5.0.1 The upgrade fails in new upgrade tasks for the remote instances.

A timeout may occur while compiling flows that contain the transformer stage.

Workaround: Disable compile time optimization by adding -O0 to compile options in the APT_COMPILEOPT environment variable.

If you create a table in a MySQL database without specifying a primary key in the WHERE column, and then you try to run a job that uses that table with the Write mode Update for the target, the job might fail.

Solution: Specify a primary key name in the Key column names field. If the table is big and does not have a primary column, you can create a separate column with auto-increment values to use as the primary key.

When you run a job that uses data from an FTP data source, the home or login directory is prepended to the path that you specified. This action happens regardless if you specify an absolute path (with a leading forward slash) or a relative path (without a leading forward slash). For example, if you specify the directory as /tmp/SampleData.txt, the path resolves to /home/username/tmp/SampleData.txt.

Workaround: Edit the File name in the FTP connector. Specify the absolute path to the source or target file.

Jobs may fail with error "The connector could not establish a connection to Db2 database".

Workaround: Go to the connection properties and set the Options property to connectTimeout=0.

If your flow includes source data from SAP OData, the flow might fail if you created the flow by manually adding columns that do not follow the SAP naming convention.

Workaround: Update the flow by or adding the columns with the Asset browser or by renaming the columns according to the SAP naming convention. The SAP naming convention follows the SAP object hierarchy with two underscore characters (__) as a separator. For example, if the PurchaseOrder column belongs to PurchaseOrderNote, then the column name should be specified as PurchaseOrderNote__PurchaseOrder.

If your data is from Apache Hive version 2.0 or earlier and your DataStage flow executes UPDATE or DELETE statements, the job might fail. Make sure that the target table has been created according to Hive transactions requirements and that the Apache Hive server is configured to support ACID operations.

The minimum set of parameters (configured in the hive-site.xml file) that you must enable for ACID tables in Apache Hive is:

hive.support.concurrency = true
hive.enforce.bucketing = true (not required as of Hive 2.0)
hive.exec.dynamic.partition.mode = nonstrict
hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager

hive.compactor.initiator.on = true
hive.compactor.worker.threads = <a positive number>

For more information, refer to Hive Transactions.

If you use an SSL certificate in the IBM Db2 for DataStage connection and the connection fails with a "Protocol specific error code 414" error, use this workaround:

1. Identify the root certificate on the Db2 server. You can use this command to view the certificate chain:

   openssl s_client -connect <hostname>:<port> -showcerts

2. Ensure that the certificate has the same subject and issuer.
3. In the Create connection: IBM IBM Db2 for DataStage page, enter the root certificate in the SSL certificate (arm) field.

When the Authentication method property is set to Service credentials (full JSON snippet), do not parameterize the Service credentials field. If a parameter is provided for that field, the flow will not compile

The PostgreSQL connector might fail with a timeout error when a large table (100,000+ rows) is used as a source. To fix this error, try setting a higher timeout value for the APT_RECORD_TIMEOUT environment variable. See Managing environment variables in DataStage.

When you use the HTTP connector to download a file and then upload the same file into IBM Cloud Object Storage or a database, if the file's schema changes over time, the job might fail.

Workaround: Re-create the stage.