Known Issues for ETL Projects

There are known issues for ETL projects that require workarounds, when available.

ETL Project Issues
CR# Description
643098

No records loaded during DB Staging simulation.

Data fails to load successfully if you open the Database Configuration window for the DB Staging component during the simulation process.

Workaround: None.
593295

CDC Provider Replication Server component fails if SYBASE_OCS points to incorrect Open Client/Open Server installation on Windows.

If Sybase IQ InfoPrimer has been installed on a Windows machine that already has a Sybase server installed, and the SYBASE_OCS environment variable points to the Open Client/Open Server installation for the preexisting server, the CDC Provider Replication Server Component may fail:

INFO: Rep CDC server instance is ready
ERROR: Unresolved symbol 327: srv_version

Workaround: Set SYBASE and PATH to the location of your IQ InfoPrimer Open Client/Open Server installation:

set SYBASE=<iqip_install>\ocs\OCS-15_0
set SYBASE_OCS=OCS-15_0
set PATH=<iqip_install>\ocs\OCS-15_0\bin;<iqip_install>\ocs\OCS-15_0\dll;
<iqip_install>\ocs\OCS-15_0\lib;%PATH%

where <iqip_install> is your Sybase IQ InfoPrimer installation.
590001

Structure Viewer does not display datatype for the XML Via SQL Data Provider component.

When you right-click the OUT-port of the XML via SQL Data Provider component to view its structure, the Data Type field in the Structure Viewer appears empty. This prevents subsequent components with an editable structure from getting the correct port structure.

Workaround: Manually assign the port structure for subsequent components.

589603

Oracle data changes are received even if commit time is later than project or job start time.

If the primary database is Oracle, the CDC Provider Sybase Replication Server component receives data changes even if the commit time is later than the job or project start time.

Workaround: Do not write to the primary database during project execution.
587092

Error encountered using DB Data Provider Full Load with schema option.

The schema of the DB Data Provide Full Load component does not support the “automatically add missing schema information” option. You may encounter an error message when a project with the DB Data Provide Full Load component is executed.

Workaround: Specify the schema in your query statement.

586711

Text Data Provider cannot correctly read quote character and delimiters.

If you specify a quote character for the Text Data Provider component and the first character of the value is the same as the specified quote character, data is read incorrectly.

Workaround: None.

585575

CDC Provider Replication Server component fails if grid engines are in multiple subnets.

The CDC Provider Replication Server component may not work if the grid engines are running in more than one subnet, or if the subnet is unstable.

Workaround: All grid engines that start the Replication CDC Service must be in the same subnet.
584015

Error connecting to the CDC Provider Replication Server component.

Replication Server throws a connection error while connecting to the ETL CDC Provider Replication Server component, if you have not properly configured Replication Server, the interfaces file, and the Open Client/Server (OCS) library.

Although replication is created successfully, an error results when the project is executed. Additionally, you cannot drop replication.

Workaround: Verify that:

  • The interfaces file of Replication Server and Sybase IQ InfoPrimer Server are configured correctly. See the Sybase IQ InfoPrimer Users Guide.

  • All grid engines that start the Replication CDC Service are in the same subnet.

  • The primary database has already been added to Replication Server.

  • The grid engine name and the Replication CDC Service names are unique. To check, run:

    GridNode --repcdcinstancename 
<my rep cdc instance name> --nodename 
<my node name>
581370

Selecting the delete function displays inconsistent result.

If you have selected the Delete function for the DB Bulk Load Sybase IQ component, the results that appear during simulation and execution may not be consistent.

Workaround: None.

580679

Wait time ignored by projects before acquiring an exclusive lock.

For DB Data Sink, DB Staging, Loader, and DB Bulk Load Sybase IQ components, if you select the “IQ Lock Table in Exclusive Mode” option and specify the maximum blocking time that the project should wait before acquiring a lock, the project attempts to acquire the lock without waiting, and fails if the lock is acquired by another project.

Workaround: Serialize project execution in a job.

577621

DB Staging component cannot bulk load large amount of data.

The DB Staging component fails to bulk-load large amounts of data to a Sybase IQ 15.0 database with the ODBC interface. Sybase QI InfoPrimer Server generates this error message:

std::bad_alloc

Workaround: Use a small write block size for bulk-loading data.

575891

INSERT operation does not work when the Upsert function is processed.

For the DB Bulk Load Sybase IQ component, when the Upsert function is processed, the DELETE operation succeeds, but the INSERT operation fails because of a datatype conversion error in one of the columns being inserted. If you select “Execute post-processing for successful execution” to commit the transaction, the unsuccessful INSERT operation is not committed. The original row that was supposed to have been updated is deleted from the table.

Workaround: Select “Execute post-processing for failed execution” to roll back the DELETE operation and to reset the table to its original state.
572569

Square bracket notation (SBN) evaluated incorrectly if Evaluate option not enabled during project execution.

Workaround: Select the Evaluate option and save your project to allow expressions within component property values.
566615

DB Staging component fails to connect to Sybase IQ database with ODBC interface when the source data is large.

Workaround: Set the Force_No_Scroll_Cursors option to off in your Sybase IQ 15.0 database. See the Sybase IQ Reference Manual.

564099

Insert Location component does not support preprocessing SQL.

The Source Pre Processing SQL property of the IQ Loader DB via Insert Location component is ignored if "Use remote server definition for source database" is enabled.

Workaround: None.

560814

DB Bulk Load Sybase IQ component cannot load data on SuSE machines.

On SuSE, the DB Bulk Load Sybase IQ component fails to load data if the ODBC environment is not properly configured. When the project executes, Sybase IQ InfoPrimer Server exits unexpectedly without any error message.

Workaround:

  1. Install the SQL Anywhere 11 or Sybase IQ 15 ODBC drivers.

  2. Add ODBC to the LD_LIBRARY_PATH environment variable.

  3. Link libodbc.so to libdbodbc11.so in the ASA11/IQ15 installation directory.

  4. Restart Sybase IQ InfoPrimer Server.
560036

Using multiple writers with IQ Loader DB via Insert Location component.

To use multiple writers, you must have execute permission on the sp_iqtable and sp_iqcolumn stored procedure privileges in the target IQ database, as well as the appropriate permissions on create table and execute sp_iqstatistics.

Workaround: Set the required permission using Sybase Central:

  1. In Sybase Central, connect to the Sybase IQ 15.3 server as a DBA user or as a member of dbo role.

  2. Expand Users & Groups, then select the user or group for whom you want to set the permission.

  3. Right-click the user or group and select Properties.

  4. Select the Permissions tab, then select Procedures & Functions to see a list of all the available permissions.

  5. Select sp_iqtable and sp_iqcolumn, then click the corresponding Execute column to grant the user permission to execute the stored procedure in the IQ database.

  6. Click OK to save the settings.
556364

Cannot execute projects if database value is specified.

If you enable the “Use IQ Multiplex” option in the DB Bulk Load Sybase IQ, IQ Loader File via Load Tables, and IQ Loader DB via Insert Location components, to select writers in an IQ multiplex environment, specifying a database in the “Database” property field may display an error during project execution.

Workaround: Do not specify a database in the “Database” property field when “Use IQ Multiplex” option is selected.
549539

Using named pipes on Windows Vista.

On Windows Vista, you cannot specify a pipe name for the Load Stage property of the DB Bulk Load IQ component.

Workaround: Change the firewall setting on the Vista machine.

  1. Go to Start > Control Panel > Security Center > Windows Firewall.

  2. Click the Exceptions tab.

  3. Under Programs and Services, select the File and Printer Sharing checkbox and then click OK.
549397

File names with multibyte characters cannot be loaded using DB Bulk Load Sybase IQ component.

The DB Bulk Load Sybase IQ component fails to load data from Sybase IQ InfoPrimer generated files with multibyte characters in the name. You see this error message:

Could not execute statement. Right truncation of string data.

Workaround: Do not use multibyte characters as the Load Stage file name for the DB Bulk Load Sybase IQ component if your target database is Sybase IQ 15.3.
540626

Text Data Provider fails if “Skip First Rows=0” for FTP source.

The Text Data Provider component does not transfer data if you keep the default value of 0 (zero) in the Skip First Rows field when you enter a FTP URL as the text source for the component.

Workaround: Enter 1 in the Skip First Rows field, save the configuration, change the value for Skip First Rows to 0, and save the configuration again.
536684

Insert Location project fails if network packet size setting is incorrect.

Projects that use Adaptive Server as the source and that contain the IQ Loader DB via Insert Location component fail if the network packet size for the component is larger than or equal to the maximum network packet size of the Adaptive Server.

Workaround: Before you execute the project:

  1. Connect to the Adaptive Server and execute sp_configure ‘max network packet size’ to display the maximum network packet size Run Value for your Adaptive Server.

  2. In Sybase IQ InfoPrimer, enter a packet size for IQ Loader DB via Insert Location that is less than or equal to the Adaptive Server maximum network packet size Run Value obtained in step 1.

For example, if your Adaptive Server maximum network packet size Run Value is 2048 bytes, your packet size for the IQ Loader DB via Insert Location component must be less than to equal to 4 as 4 translates to a Adaptive Server Run Value of 4*512=2048 bytes.
536482

Insert Location component does not support encrypted passwords.

Sybase IQ InfoPrimer 15.3 does not support the Encrypted Password option in the IQ Loader DB via Insert Location component connection information if the source database is Sybase IQ 12.7 or Sybase IQ 15.x.

Workaround: None.
493550

Extra characters truncated from column length of source table.

If the column length of a source table is more than the column length of a target table, Sybase IQ InfoPrimer truncates the extra characters without any warning. This occurs only if you use Adaptive Server as the stage database in a Staging component, using Sybase interface.

Workaround: When transferring data, make sure the column length of the target table is equal to or more than the column length of the source table.
492443

Splitter components cannot inherit port structure.

The Data Splitter JavaScript and Copy Splitter components do not inherit the structure of the existing ports when they are reassigned.

Workaround: Select the port and assign the structure.
447948

Improving performance in accessing Sybase IQ.

Using components such as DB Staging, DB Data Sink Insert, DB Data Sink Update, or DB Data Sink Delete to access Sybase IQ can degrade performance.

Workaround: To improve performance, you can do one of the following:

  • Use the IQ Loader File via Load Table and IQ Loader DB via Insert Location loader components to speed up loading into Sybase IQ.

  • Use Adaptive Server Enterprise, Adaptive Server Anywhere, or Microsoft Access instead of Sybase IQ for the staging portion of your project.

  • If you still want to use Sybase IQ for staging, split the project into new staging and loading projects. Use DB Bulk Load Sybase IQ instead of DB Staging in the new staging project and use IQ Loader File via Load Table and IQ Loader DB via Insert Location to speed up loading into Sybase IQ in the new loading project.

  • Use IQ Loader File via Load Table component for loading binary data that are extracted from Sybase IQ. You must create a custom script to do the binary extract from a Sybase IQ table, using isql utility:

    • Extract Data on Source IQ system

      set TEMPORARY OPTION Temp_Extract_Name1='C:\myfolder\mybinfile.bin';
      set TEMPORARY OPTION Temp_Extract_Binary='ON';
      SELECT * FROM mytable

    • Create a custom load script in IQ Loader File via Load Table component by setting a dummy file name in the Text Source property window. 

      LOAD TABLE mytable
{
[myCol1] BINARY WITH NULL BYTE,
	[myCol2] BINARY WITH NULL BYTE,
	[myCol3] BINARY WITH NULL BYTE
)
FROM '<path&filename on destination system>'
QUOTES OFF
ESCAPES OFF
FORMAT binary

    See Chapter 7, “Moving Data In and Out of Databases” of the Sybase IQ 12.7 System Administration Guide.
447096, 447097

Recommended settings for IQ database options.

The IQ database option FORCE_NO_SCROLL_CURSORS should be at the default setting (OFF) or a fetch error may occur while moving data with Sybase IQ InfoPrimer.

To adjust database option settings, use the SET OPTION command in dbisql or, in Sybase Central, right-click the database, and select Set Options.
Parent topic: Known Issues for Sybase IQ InfoPrimer Development