SybasePhone Number

Sybase White Paper: Informatica PowerCenter and Sybase IQ

Using the PowerCenter Sybase IQ External Loader feature with Sybase IQ

Abstract: This white paper provides information about using Informatica PowerCenter 8.6 integration software with Sybase IQ versions 12.7 and 15.1. It focuses on loading data into Sybase IQ using the PowerCenter-provided Sybase IQ External Loader connection type. It includes architectural and configuration information.

Audience: The intended audience are system administrators responsible for configuring a PowerCenter environment that supports Sybase IQ External Loader connections with a target Sybase IQ database. Readers should be familiar with PowerCenter and Sybase IQ.

Author: Jeannette Smith, Sybase Application Engineering Team

Revisions:

Revision Description AuthorDate
1.0 First version white paper. J. Smith March 25, 2010

Contents

The contents are divided into four major sections: information about the Sybase IQ and Informatica PowerCenter environment, configuration of the PowerCenter Sybase IQ External Loader connection, workflow creation, and troubleshooting. Information about related Sybase IQ database options, working with Sybase IQ and Informatica when you are more familiar with Sybase ASE, and modifying the generated LOAD TABLE statement is provided in the appendices.

Overview

Part I: The Informatica PowerCenter and Sybase IQ environment

Part II: Configuring a Sybase IQ External Loader connection in PowerCenter

Part III: Creating a workflow to load data into Sybase IQ

Part IV: Troubleshooting

Appendix A:Viewing the Sybase IQ External Loader generated files

Appendix B: Configuring Sybase IQ with ASE experience

Appendix C: Sybase IQ database options relevant to loading data

Appendix D: Modifying the LOAD TABLE statement

References


Overview

As an integration tool, Informatica PowerCenter is designed to move data. It does so by defining data sources and targets, and allowing for the modification of data along the way. PowerCenter supports several types of targets: flat files, xml files and relational databases, including Sybase IQ. It also supports similar types of sources. Sources and targets are then mapped together and data are moved from one to the other in units of work called "workflows."

PowerCenter provides different connection options for different databases, including: ODBC drivers and native database drivers. PowerCenter also features a connection type called an "External loader" for some target databases, including Sybase IQ. External loaders increase load performance by loading data directly from a file or pipe instead of using SQL commands to insert data. See the section "External Loading" in the PowerCenter Advanced Workflow Guide for a in-depth discussin of external loader connections.

To work specifically with Sybase IQ as a target, PowerCenter provides an external loader called the "Sybase IQ External Loader" (hereafter called the "IQ External Loader" or "IQ Loader"). The IQ External Loader generates data output files and then calls an application external to PowerCenter to make the connection to Sybase IQ and load the data from the files. The application it calls is the Sybase Interactive SQL utlity "dbisql." Details about how the IQ Loader works follow in the section Configuring a Sybase IQ External Loader connection in PowerCenter.


Part I: The Informatica PowerCenter and Sybase IQ environment

The Informatica PowerCenter environment with a target Sybase IQ database and support for an IQ External Loader connection includes the following key components:

  • PowerCenter client applications - these applications, most of which are Microsoft Windows applications, facilitate user interactions with the PowerCenter environment, including system administration, the creation of Informatica projects and objects, and workflow execution.
  • PowerCenter Integration Service - the server application that executes workflows. A PowerCenter environment can include mulitple Integration Services and they can run in a grid.
  • dbisql- the Sybase Interactive SQL utility that is specified as the IQ Loader execution binary. The IQ External Loader connection was designed to work with the version of dbisql that is installed with either a Sybase IQ server or Sybase IQ Database Client. For information about the version of dbisql installed with Sybase ASE or its PC-Client, see Appendix B in this paper.
  • Sybase IQ server - the target Sybase IQ database.

Product configuration options

In order to successfully connect to Sybase IQ, dbisql and its supporting libraries must be installed on the same machine as the Integration Service. There are two options for doing this:

The Integration Service, dbisql and the Sybase IQ server on the same machine

You can install and run the Integration Service on the same machine with the target Sybase IQ server, which includes dbisql and its supporting libraries, as shown in Figure 1 below. When the Integration Service and Sybase IQ run on the same machine, load performance is faster. To be loaded into Sybase IQ, the output files generated by the IQ Loader connection must be written to a directory on the same machine as Sybase IQ. This is easily configured when the Integration Service and Sybase IQ run on the same machine. In this configuration, data may be output to a flat file or a pipe. Howerver, there are inherent risks to using pipes such as disruptions to power.

Integration Service, dbisql & IQ same machine

Figure 1: Informatica Integration Service, dbisql and the target Sybase IQ on the same machine

The Integration Service and dbisql on the same machine and Sybase IQ on a remote machine

You can install the Sybase IQ Network Database Client, which includes dbisql and its supporting libraries, on the same machine with the Integration Service but remote from the target Sybase IQ as shown in Figure 2 below. Because the output files must be written to a directory on the same machine as Sybase IQ, you will need to create an NFS or SAN mount from the machine where the Integration Service and dbisql are installed to a directory on the remote machine where Sybase IQ is running. In this configuration data may be written to a flat file but not a pipe.

Integration Service, dbisql and IQ on separate machines

Figure 2: Informatica Integration Service and dbisql on the same machine and Sybase IQ on a remote machine

Back to Contents


Part II: Configuring a Sybase IQ External Loader connection in PowerCenter

Part II of this paper contains the specifics of configuring a Sybase IQ External Loader connection in PowerCenter. It contains the following sections:

Information about creating a workflow, including how to define a source, target, mapping, session task and workflow is provided later in the section Creating a workflow to load data into Sybase IQ.


About the IQ External Loader

Informatica designed the Sybase IQ External Loader connection type to do the following:

  1. Generate a file containing a LOAD TABLE statement
  2. Write out data either to a file or pipe
  3. Call the Sybase dbisql utility to load the data according generated the LOAD TABLE statement.

Specifically, when a workflow that uses an IQ Loader connection runs, the Informatica Integration service writes out the files and then calls dbisql to connect to the target Sybase IQ database and load the data. The output file containing the generated LOAD TABLE statement is called a "control" file. It has a .ctl extension. If a pipe is not used, the output file that contains the data has a .out extension. By default, the data output file has the target table name and the .out extension, for example, "customer.out." The control file has the same name as the data output file with the additional .ctl extension, for example, "customer.out.ctl." To view sample control or data files, see Appendix A.

At the time this white paper was written, Informatica did not support automatic generation of the client-side loading option (USING CLIENT FILE), which Sybase IQ implemented in version 15.0. For information about this and other enahnced options for the LOAD TABLE statement, see Appendix D: Modifying the LOAD TABLE statement . For more information about the LOAD TABLE statement, see the section "Bulk loading data using the LOAD TABLE statement" in the System Administration Guide: Volume 1 Sybase IQ 15.1. `12io


IQ External Loader connection requirements

To use Sybase IQ with the IQ External Loader to load data into Sybase IQ, you must satisfy the following requirements. Note that these requirements are only for the IQ Loader connection. They do not include the hardware and software requirements for the PowerCenter clients, repository, PowerCenter Integration Service, or a Sybase IQ Server or Database Client.

1. The dbisql utility must be installed on the same machine as the PowerCenter Integration Service

The dbisql utility must be installed on the same machine with every Integration Service that will run workflows using an IQ Loader connection. As stated previously, this means either a Sybase IQ server or Database Client must be installed on the same machine as the Integration Service. Note that the older, Interactive SQL Classic utility "dbisqlc" may also be used instead of dbisql. Information about specifying the external loader executable is provided in the section Configuring the PowerCenter Sybase IQ External Loader connection.

Note: The dbisql utility installed with Sybase Adaptive Server Enterprise (ASE) or its database client, the Sybase PC-Client, uses different libraries than the one installed with Sybase IQ and, therefore, behaves differently. The Sybase IQ External Loader was designed for use with the version of dbisql installed with Sybase IQ and its Database Clients. Therefore, all subsequent references in this paper are to the version installed with the Sybase IQ products. See Appendix B for more information about the version installed with the Sybase ASE products.

2. The Integration Service must run with the environment variables set for dbisql.

To successfully execute dbisql, the Integration Service must run with the dbisql environment set. One way to do this, is to set the environment variables for the user who runs the Integration Service. For example on Solaris, you can execute Sybase IQ environment variable scripts in the user's .login file. The environment scripts are installed with a Sybase IQ server and some Database Clients (ASIQ-12_7.(c)sh for Sybase IQ 12.7 or IQ-15_1.(c)sh for version 15.1). For those database clients that do not provide scripts but describe which variables to set in the README file, you may want to create your own script.

3. The Integration Service must be able to write the control and output files to a directory on the same machine as the target Sybase IQ server.

If the Integration Service, dbisql and the target Sybase IQ are all running on the same machine, then you can more easily configure the IQ Loader connection to write to the output files to a directory on this machine. If the Integration Service and dbisql are on a different machine than the target Sybase IQ, however, you will need to create a SAN or nfs-type mount from the Integration Service machine to a directory on the target Sybase IQ machine, so that the Integration Service can write the output files to the Sybase IQ machine.

4. The Sybase IQ server string_rtruncation option must be disabled

The string_rtruncation option in Sybase IQ raises an error when it is enabled, which it is by default in Sybase IQ version 15.1. Note that it was disabled in version 12.7. Thus, you must set this option to 'OFF' to successfully load data into Sybase IQ with the IQ Loader. See the section Appendix C: Setting Sybase IQ options relevant to loading data later in this paper for more information about this and other database options.

5. Complete the External Loader pre-requisite tasks and follow the rules and guidelines for loading into Sybase IQ documented in the PowerCenter Advanced Workflow Guide.

The PowerCenter Advanced Workflow Guide provides rules and guidelines and a list of pre-requisite tasks to complete before you use IQ External Loader connections.

Read the explanations of these tasks in the guide before you implement them. For example, disabling tracing increases load performance but will not allow you to perform database recovery on failed sessions. The list of tasks include: disabling constraints in the target tables, increasing commit intervals, disabling logging, configuring the code pages of the target database and flat files to match, and configuring the external loader connection as a resource if you will run it on a grid (which is not discussed in this paper).

Also read the Rules and Guidelines in the "Loading to Sybase IQ" section, which include ensuring the target tables do not violate primary key constraints, configuring a Sybase IQ user with read/write access before you use the IQ Loader, specifying that the target flat file is either fixed-width or delimited, and using the IQ Loader only to load data. It cannot perform update or delete operations on targets. This section also provides information about loading multi-byte data.


Creating an IQ External Loader connection definition

Connection definitions are created in the PowerCenter Workflow Manager client application. For additional information, see the topic "Loading to Sybase IQ" in the "External Loading" section of the PowerCenter Advanced Workflow Guide, which is available in the Workflow Manager on-line help and was the main source of information in this section. The following steps describe how to configure a Sybase IQ External Loader connection:

  1. Be sure the requirements are met for the IQ External Loader and Integration Service as described in the previous section IQ Loader Requirements.
  2. Open the PowerCenter Workflow Manager client application (hereafter called "Workflow Manager"). The application will open and prompt you to connect to the repository.
  3. Connect to the Informatica repository, providing the required user and password. Workflow Manager opens with the Repository Navigator, which contains project folders, tasks and workflows, in the left-pane, and the Start page in the right-pane, which is called the "workspace."
  4. When you are connected to the repository, select your project folder and choose Connections | Loader.... from the Workflow Manager menu bar. The Loader Connection Browser dialog box opens.
  5. Choose New. The Select Subtype dialog box opens.
  6. Select Sybase IQ External Loader and click OK. The Connection Object Definition dialog box opens.

IQ Loader Connection Object Definition

  1. In the upper-part of the dialog box, you provide the following connection information:

Name - provide a name for the connection.

Type - the connection type. "Sybase IQ External Loader" fills in by default and may not be edited.

User Name and Password - leave these fields blank. You will provide the user information in the connection string.

Connect String - enter a connection string for the target Sybase IQ server similar to the following: Not that the syntax is similar to but not exactly like that used with dbisql in command line (nogui) mode.

 uid=dba;pwd=sql;eng=IQServerName;links=tcpip(host=IQHostMachineName;port=2638)

where uid= is a valid user name for the target Sybase IQ database, pwd= is its valid password, eng= is the Sybase IQ server name (specified with the -n Sybase IQ start-up parameter), host= the Sybase IQ host machine name, port= the port on which the Sybase IQ server is running. The values are separated with semi-colons and the host and port are enclosed in parenthesis (not brackets like they are in dbisql syntax.)

Code Page - the code page of the of the client machine on which Workflow Manager is running fills in and may not be changed.

  1. In the lower-part of the dialog box, you configure the connection attributes. Note that default values may be used unless noted otherwise.

Block Factor(records) - for fixed-width flat file targets only, it is the number of records per block in the target Sybase table (10000 records by default).

Block size (bytes) - for delimited flat file targets only, it is size of blocks used in used in Sybase database operations (50000 bytes by default).

Checkpoint - issues a checkpoint after successfully loading the table (enabled/selected by default). If it is disabled, the database does not issue any checkpoints.

Notify Interval (rows) - Number of rows the Sybase IQ External loader loads before it writes a status message to the external loader log (1000 rows by default).

Server Datafile Directory - the directory to which the control file and data files will be written (blank by default). There are three important requirements for this directory:

  • The directory must be accessible to both the Informatica Integration Service and the Sybase IQ server, either by being on the same machine or accessible by another means such as an nfs mount, and local to the Sybase IQ server.
  • The user who executes the Integration Service where it will run must be able to write to this directory.
  • The directory specification must be appropriate for the operating system where the target Sybase IQ server is running, for example, c:\directoryname\inputfilename.out for Windows or /directoryname/inputfilename.out for UNIX. If this field is left blank, the Integration Service writes the .out file to the $PMTargetDir directory, which will be similar to /powercenter/server/infa_shared/TgtFiles on UNIX.

External Loader Executable - name of the interactive SQL utility executable that the Sybase IQ external loader will call (Sybase "dbisql" by default). This specifies to use the Sybase dbisql interactive SQL utility and it was designed to work with the version of dbisql installed with Sybase IQ or the Sybase IQ Database Clients. Important! To successfully execute dbisql, the Integration Service that runs a workflow with the Sybase IQ External Loader must be configured with the Sybase IQ environment variables. See the section IQ External Loader connection requirements earlier in this paper for information about dbisql, including differences between the version installed with Sybase IQ and the Sybase IQ Database Client compared to the version installed with Sybase ASE and the Sybase PC-Client. Note that the older, Sybase Interactive SQL Classic utility ("dbisqlc") may be specified here instead.

Is Staged - (Not shown in previous screenshot) specificies whether or not data are staged to a flat file (enabled by default). When this attribute is enabled, the Integration Service writes data to flat file, which is named after the configured target file name with a ".out" extension appended. The external loader starts loading data to the target database after the Integration Service writes all the data to the target flat file. Note that it does not delete the file after loading it to the database. Thus, you need to ensure the target file directory can accommodate the size of the target flat file. The Integration Service can instead write to a namedp pipe if the Integration Service is local to the Sybase IQ database.

Note: The Integration Service rounds numerical values based on the scale of the port when staging data to a flat file. It does not round results when you use an external loader that loads the data to a named pipe or if you configure the target for a normal load.

If a session aborts or fails before the Integration Service writes all the data to the flat file target, the external loader does not start. If a session aborts or fails after the Integration Service writes all the data to the flat file target, the external loader completes loading data to the target database before the external loader exits. The Troubleshooting section later in this white paper, provides information about troubleshooting external loader failures.

  1. Click OK to save.

The connection is now available for use in tasks and workflows. Continue to the next section for information about configuring a session task to use a Sybase IQ External Loader connection, and to the section after that for information about the control (.ctl) and data (.out) files generated by the connection.


Configuring a Session Task with an IQ External Loader connection

The connection definition is specified in a session task. According to the PowerCenter documentation, "a session is a set of instructions that tells the Integration Service how and when to move data from sources to targets." This section of the paper describes how to create a session task and configure it to used a Sybase IQ External Loader connection. The session task is then added to a workflow. Information about creating a workflow is provided later in the section Creating a workflow to load data into Sybase IQ.

Like the connection definition, you use the PowerCenter Workflow Manager client application to create and configure session tasks.

  1. Open the Workflow Manager application and log into the Informatica Repository.
  2. When you are connected, select your project folder and then select Tools | Task Developer. "Task Developer" will show in the workspace indicating it is open.
  3. Choose Tasks | Create... from the menu bar. The Create task dialog box opens prompting you to enter a name for the task and to select the type of task you want to create.

Create task dialog with mapping

  1. Select Session as the type of task you want to create, provide a name for the session task, and click OK. Note that the convention is to prefix session tasks with the "st_". The Mappings dialog box opens with a list of the defined mappings.
  2. Select the mapping you want and click OK. The task opens in the workspace and the Create Task dialog box remains open.
  3. Click Done to close the dialog.
  4. Right-click the task in the workspace and select Edit... to open it. Like the Designer client, the Workflow Manager provides tools for tasks and workflows. The Task Developer tool will be open at this point. The Edit Task dialog box opens with the General tab in focus. The general tab contains the selected task name, the type and the name of the mapping it on which it is based. Note that you could select a different task here.
  5. Click the Mapping tab to display it. See the PowerCenter on-line help for information about the Properties and Config Object tabs. The mapping tab opens with a list of options on the left and Configure Mapping page on the right.

Edit Tasks Mapping tab

  1. Select the Targets page on the left to configure the Sybase IQ connection type. Note that you cannot specify the an IQ Loader connection on the Connections page until you specify the File Writer type for the Target Writer type on the Targets page. The Target writer-type, connection-type and properties open in the right-pane of the dialog box.

Mapping tab Target Connection options

  1. Specify an IQ External Loader connection on the Targets page as follows:

Writers options (top of the right-pane) - choose File Writer from the Writers drop-down list. This specifies that the target will write to a file (or pipe). This changes the types of options available for the Connections.

Connections options (middle of the right-pane) - select Loader from the Connection drop-down list on the left and the name of the Sybase IQ Loader connection definition from the Value drop-down list on the right.

Properties options (bottom of the right-pane) - are fields for specifying output file information. This includes the target directory to which the control and data files will be written (Output file directory) and the data file name (Output file Name). You may need to scroll down to see the fields. The target directory is set to $PMTargetFileDir by default. This will be the directory to which the Integration Service that runs the workflow with the connection writes the output files. Although there is not a field for it, the control file is written to the same directory as the data output file and will have the same name with the addition of ".ctl" appended. Note that if the Integration Service is running on the same machine as the target Sybase IQ, this default value will be fine. If not, you may need to explicitly specify the target directory, for example, of an nfs mount.

Mapping tab - Target properties

  1. Set the target file properties by clicking the Set File Properties link above the Properties. Since the target is now configured as a file, the Integration Service does not know anything about the target Sybase IQ to which it will load data. It only knows about the file to which it will write data. You can set properties for the target files, which will affect bot the control and data files. When you select Set File Properties, the Flat Files - Targets dialog opens with the Fixed Width selected by default. If you want to configure the target file as a fixed-width file, continue to the next step. If you want to configure the target file as a delimited file, skip to step 14.

Mapping tab Target File Properties

  1. With Fixed Width selected, click the Advanced button. The Fixed Width Properties - Targets dialog box opens. It provides the settings for the handling of Null characters and code pages. The Text null character option defaults to an asterisk (*). The codepage defaults to the one on the Windows machine where the Workflow Manager client is running.

Mapping tab Fixed Width File Adv properties

  1. Accept the defaults or modify as appropriate for your environment and click OK. The Fixed Width Properties - Targets dialog box closes and the Targets page on the Mapping tab remains open.
  2. If you want the target files to be delimited, select Delimited on the Flat File - Targets dialog box, instead of the default Fixed Width. The Delimited File Properties - Targets dialog box opens. It contains options for specifying column delimiters (comma by default), quote identifiers (None by default) and the code page (that of the Microsoft Windows machine where the Workflow Manager is running).

Mapping tab advanced delimited file options

  1. To specify a different delimiter than the default comma, you can click the ellipses button next to the Column Delmiters button to open the Delimiters dialog box. Here you can specify a different delimiter. Click OK to close this dialog.

Mapping tab delimiters dialog

  1. Specify the options you want, or accept the defaults, and click OK. The Delimited File Properties - Targets and Edit Tasks dialog boxes remained open.
  2. Click OK to close the Delimited File Properties dialog. The Mapping tab remains in focus on the Edit Tasks dialog.
  3. Leave the defaults on the Components and Metadata Extensions tabs. They will not be discussed in this white paper.
  4. Click OK to save the task and close the Edit Tasks dialog box.
  5. On the Workflow Manager menu bar choose File | Save to save the task to the repository.

The session task is now ready for use in a workflow. Information about the process to create a workflow is provided in the section Creating a Workflow to load data into Sybase IQ.

Back to Contents


Part III: Creating a Workflow to load data into Sybase IQ

This part of the paper describes how to create a PowerCenter workflow to load data into Sybase IQ. The workflow is the PowerCenter object that executes to move data. It links together tasks and, therefore, mappings into a unit of work for execution. Before you can configure a workflow, you must first use the PowerCenter Designer client application to define a source, target and mapping, and then use the PowerCenter Workflow Manager to create a session task and, finally, the workflow.


Defining a source, target and mapping

The first tasks you need to complete before building a workflow, are to define a PowerCenter source, target and mapping with the PowerCenter Designer application. For more information about how to perform these tasks, please see the PowerCenter Designer client (hereafter called "Designer") application product documentation or on-line help.


Source Definition

The PowerCenter Designer supports several types of data sources, including xml files, relational databases and flat files. This section focuses on using the Designer Source Analyzer tool to import data from a flat file.

Importing from Flat Files

When you import data from a flat file for load into Sybase IQ, you need to pay attention to the file attributes because these attributes can affect how the data are loaded into Sybase IQ. Attributes to be aware of include the quote qualifier used, whether the file is fixed-width or delimited, and if delimited what delimiter is used. The Designer Flat File Import wizard allows you to specify this information about the source file.

  1. Launch the PowerCenter Designer client and, when prompted, log into the repository. The Designer application will open with the list of project folders, sources, tasks and mappings in the Repository Navigator on the left and the Start page in the right-pane, which is called the "workspace."
  2. When you are logged in to the repository, select your project folder and then choose Tools | Source Analyzer to open the tool. The right-pane the workspace will show "Source Analyzer" to indicate the tool is open.
  3. Choose Source | Import from File... also from the Designer menu bar. The Open Flat File dialog box, which looks like most Windows operating sytem Open File dialog boxes, opens prompting you to navigate to the directory that contains the flat file you want to import. The flat file must be on a drive or directory accessible to the Designer client machine.
  4. Navigate to the directory containing the flat file you want, select it and click OK. The Flat File Import Wizard opens displaying the Step 1 of 3 panel. As shown in the following screen, the Step 1 panel default values are as follows:

Flat File Type: Delimited - Characters such as commas or tabs separate each field

Enter a name for this source: CustomersFlatFile, the imported file name fills in

Start Import at Row: 1.

Rows of data from the file are also shown in the Preview window.

File Import Wiz Step 1

  1. Modify these settings as appropriate for the format of the file you are importing and click Next. The Step 2 panel opens with the following default values: Delimiters: Comma, Text Qualifier: Double quotes and data from the file by row and field.

Source Analyzer import Step 2

  1. Modify these settings as appropriate for the file you are importing and be mindful of the following as you do so:

Delimiters - If the file contains a delimiter for which there is not a selection box, such as a pipe ('|'), select the Other option and enter it in the field to the right.

Use default text length - select for fixed width files and enter the width in the field to the right.

Escape character - if the file contains escape characters, enter the character in this box. The new-line delimiter ('/n') may be parsed differently on Windows and UNIX platforms in which case it is common to use the hexadecimal representation of it '\0xa'. If you use this method, be sure to specify the escape character here. Important! Keep in mind the influence of the Sybase IQ ESCAPE_CHARCTER option. This option is set to 'ON' for the version of dbisql installed with the Sybase IQ server or database client, which means the escape character is parsed. But, this option is set to 'OFF' for the version of dbisql installed with Sybase ASE or the Sybase PC-Client ASE database client, which means it will not be parsed. You can configure the ESCAPE_CHARACTER database option to handle this or specify how you want to handle it in the Target File properties, as described in the earlier section Configuring a Session Task with a Sybase IQ Loader connection.

  1. Click Next. The Step 3 panel opens. This panel allows you to specify names and data types for the source columns. It shows the default column name and data type values in the Source definition window on the right (FIELD1, FIELD2, FIELD3 and so on), with FIELD1 selected in the Column Information area on the left, and a data preview at the bottom. You can specify source column names and types by selecting each field and modifying it in the Column Information area on the left.

Source File Import Wizard Step 3

  1. Replace the "FIELD" names and datatypes with the names that match those in the target database table. Note that you can accept the defaults names and values and they will be used in the source definition.
  2. Click Finish. The Flat File Source definition opens in the workspace and is listed in the FlatFile folder under Sources in the Repository Navigator in the left pane of the Designer.

Source File Definition

  1. In the Designer menu bar, choose Repository | Save to save the source definition to the Informatica repository.

The source definition is now ready for use in a mapping.


Target definition

To define a target, you use the Target Designer tool within the Designer application. You can use any of several different ways to create a target definition for Sybase IQ:

  • Import from File... - uses the same file import wizard described in the Source Definition
  • Import from Database... - requires that the 32-bit Sybase IQ ODBC driver be installed on the same machine as the Designer. As stated earlier, configuring Sybase IQ as a relational target is outside the scope of this paper. If you do, however, define a Sybase IQ table as a target by importing it as an ODBC relational database, you will not be able to use the "Generate from Mapping" option to create the workflow. The reason is the Workflow Generation wizard will define the target as a relational target and you will not be able to select the Sybase IQ External Loader type connection in the wizard.
  • Import from XML Definition..., which obtains a target table schema from an XML file.
  • Create... - prompts you to select the type of database (for example, Sybase) and opens the target definition in the workspace where you manually enter the information from each field. Note that the Sybase database type is designed for use with Sybase (native) Open Client connectivity, however, Open Client is not actually used when you create a target definition manually. Nor is Open Client used with the Sybase IQ External Loader option, unless the version of dbisql is the one installed with Sybase ASE or the Sybase PC Client ASE database client software, which is not recommended. See the section dbisql earlier in this paper for information about dbisql versions.

Any of these methods will work, although some may require too much manual column definition to be practical. See the Informatica Designer documentation or on-line help for information about defining a target. Note that although you can define relational database targets within the Designer, there is not a way to define Sybase IQ External Loader connection types. Defining and specifying this type of connection is done in the Workflow Manager application.

The target will now be ready for use in mappings, which is where you link sources, transformations and targets together.


Mapping Definition

Create a mapping that includes minimally a source, source qualifier and Sybase IQ target definition. This section describes how to create a very basic mapping with a Sybase IQ target.

  1. Within the PowerCenter Designer application, choose Tools | Mapping Designer from the menu bar. "Mapping Designer" will appear in the workspace to indicate it is open.
  2. Drag the source definition you created to the workspace. The source definition and a source qualifer, which is automatically inserted and linked to the right of the source definition, open in the workspace. Note that you can perform transformations in the source qualifier, although transformation is not discussed in this white paper.
  3. Drag the target definition you created for the Sybase IQ table to the workspace and place it to the right of the source definition and qualifer.
  4. Link the source qualifier to the target definition by choosing Layout | Link columns, and then selecting a column in the source qualifer and dragging to the column in the target definition. Repeat this step until all columns are linked. You can also choose the Autolink or Autolink by name options.
  5. When the columns are linked, choose File | Save to save the mapping to the repository.

The mapping is now ready for use in a task and workflow. Note that the Mapping Desiger tool provides the option to generate the workflow. However, it does not allow you to specify a Sybase IQ Loader connection. If you use this feature, you will have to subsequently modify the workflow to use a Sybase IQ Loader connection. Thus, this workflow generation option is not described here. Instead, building a workflow is described in the section Configuring the workflow later in this paper.


Defining a session task and workflow

The next sections describe defining a session task and worflow to target Sybase IQ.

Session task creation

The Session task is based on a mapping. According to the PowerCenter documentation, "a session is a set of instructions that tells the Integration Service how and when to move data from sources to targets. A session is a type of task...."

To create a Session Task you use the PowerCenter Workflow Manager application to select a mapping and then configure the connection, source and target properties. Instructions for configuring a session task were provided earlier in this paper in the section Configuring a Session Task with an IQ External Loader connection. Instructions for creating a workflow follow in the next section.

Workflow creation

With the source, target, mapping and session task created, you are ready to create the workflow.

  1. If it is not already open, open the Workflow Manager application and log into the Informatica Repository.
  2. When you are connected, select your project folder and then select Tools | Workflow Designer. "Workflow Designer" will show in the workspace indicating it is the tool currently open.
  3. Choose Workflows | Create... from the menu bar. The Create Workflow dialog box opens with the General tab in focus prompting you to enter a name for the workflow and to select the PowerCenter Integration Service on which to run the workflow.

Create workflow

  1. Enter a name for the workflow and select the PowerCenter Integration Service you want to execute it. Important! this Integration Service must be configured with the Sybase IQ environment variables, so it can execute dbisql. Leave the other options on this and the other tabs with the default values. See the Workflow Manager on-line help for information about the rest of these options.
  2. Click OK. The Create Workflow dialog box closes and the new workflow opens in the workspace with a Start task object. The Workflow Designer will be the currently open tool.

Create workflow2  Start task

  1. Drag the session task you created to the workspace and place it to the right of the Start task.
  2. Select Tasks | Link Task. The cursor in the workspace will change to a circle with a cross-hair in it.
  3. Select the start task and then drag to the session task to link them. A line will appear indicating the two tasks are linked.

Create workflow3 link tasks

  1. From the Workflow Manager menu bar, choose File | Save to save the workflow to the Informatica repository. The workflow will now be listed in the Repository Navigator under the Workflows folder.

The workflow is now ready for execution.


Executing a workflow

Following describes how to execute a workflow.

  1. If it is not already open, open the Workflow Manager application and log into the Informatica Repository.
  2. When you are connected, select and expand your project folder, and then choose Tools | Workflow Designer.
  3. Select the workflow, right-click and choose Start workflow. A status indicating the workflow is starting is written to the Integration Service tab at the bottom of the Workflow Manager application. It also may launch the Workflow Monitor application, depending on your configuration, so you can view the workflow run status. For a simple workflow that contains one session task, the run status reported in the Workflow Manager will look similar to the one below.

Workflow execution 1

  1. When you execute a workflow, you can view a detailed status of the execution even during execution by selecting the session task, right-clicking, and selecting SessionLog from the pop-up menu. It records the execution of the task.

Session task log

  1. When the session and workflow finish executing the start and finish execution times are shown along with the "Succeeded" or "Failed" status in the Wokflow Monitor as shown previously. In some cases, the task may fail even though the workflow and session taks both report success.

See the section Verifying workflow execution, which follows in this paper, for information about verifying the success of the workflow and task execution. Or, see the section Troubleshooting for information about resolving failures.

Back to Contents


Verifying workflow execution

When a workflow and task that use a Sybase IQ External Loader connection execute successfully, the control and data files are written out to the specified target directory. You can verify the success of the execution by viewing the control and data files but most importantly by querying the target Sybase IQ table.

Viewing the control file and data files

Log into the machine where the control and data files were written, or to a machine with that drive accessible, and open the files. Note that you may need to use the user who executes the Integration Service to open this file or use a user with administrative privileges to open permissions on the files to open and then view them. Sample output and control files are included in Appendix A.

Querying the Sybase IQ target

To absolutely verify the workflow executed successfully and the Sybase IQ External Loader loaded data into the target Sybase IQ table, you should query the table. For example, you can use the dbisql utility installed with a Sybase IQ server or Sybase IQ database client, to log into the target Sybase IQ and query the target table. Note that these instructions will not work for the version of dbisql installed with Sybase ASE or the PC-Client. See the section Sybase dbisql for information about the different versions of dbisql.

You can use the GUI version of dbisql either on Windows or using an xterm session on UNIX. Or you can run it from a command line on either UNIX or Windows by doing the following:

  1. Log into the system that contains the dbisql application you want.
  2. Set the enviroment variables for dbisql. Note on Microsoft Windows operating systems, the variables will be set if you log in with a user configured for the Sybase IQ variables. On UNIX, locate the environment variable scripts (ASIQ-12_7.(c)sh for Sybase IQ 12.7 or IQ-15_1.(c)sh for Sybase IQ 15.1) and execute the one appropriate for the shell you use.
  3. Change to the directory that contains dbisql (for example, win32, bin or bin64 depending on the installation) in the Sybase IQ server or client installation.
  4. Invoke dbisql using a command similar to the following:
dbisql -c "uid=dba;pwd=sql;eng=hostname_iqdemo;CommLinks=tcpip{host=hostname;port=2638}" -nogui

where -c specifies the connection string with the following parameters uid= and pwd= are a valid Sybase IQ user and password, eng= the target Sybase IQ server name, CommLinks=tcpip specify a TCP/IP connection with the Sybase IQ server host and port; and -nogui specifies to run dbisql in command line mode.

  1. When the dbisql command prompt (DBA>, where DBA is the logged in user name) returns and reports you are connected, enter a select against the target table, for example, customer as shown below:
select * from customer

The table should be populated. If it is not, it means the LOAD TABLE rolled back and you need to troubleshoot.

Back to Contents


Part IV: Troubleshooting

This part of the paper contains suggestions for troubleshooting problems with a PowerCenter workflow that uses the Sybase IQ External Loader connection type.

Viewing the SessionLog

When you execute a workflow, you can view a detailed status of the execution by selecting the session task, right-clicking and selecting SessionLog from the pop-up menu. The session task execution is recorded in the SessionLog. If the session fails, you can look at the SessionLog for indications of failure. An excerpt from a session log is shown in step 4 of the section Executing a workflow. Common causes of failure in session tasks that use a Sybase IQ External Loader are as follows:

  • The External Loader exits with a failure code, for example a "signal(9)" error may indicate a problem related to the dbisql environment variables or the Integration Service and the target Sybase IQ server being on different machines.
  • A file cannot be found error - may indicate a problem locating a source file or writing the control and data files to the specified target file directory

Viewing the IQ Loader generated control and data output files

Check the directory to which the files were written to be sure they are there. They may be overwritten everytime the workflow executes, so check the timestamp on the files to be sure they are timely. To see sample output files, see Appendix A.

Checking the Sybase IQ server configuration

Based on the failure, review the Sybase IQ Server configuration to see if any properties or options may be the cause. See the Appendix C for a list of Sybase IQ database options that can influence data loading.

Viewing the target Sybase IQ message log

You can also look at the target Sybase IQ server message log (with the .iqmsg extension). It is created in the same location as the other Sybase IQ server instance files. Errors commonly reported in the .igmsg log include:

  • string_rtruncation is enabled
  • Sybase IQ is out of dbspace
  • Errors and ROLLBACKs
  • Some rows will be processed in single-threaded mode, Rows skipped - In some cases, a session task will load the data successfully but you will see this message in the .iqmsg log. This needs to be investigated as it indicates that IQ is performing the load using only one thread/processor instead of using multiple threads or processors.

Using dbisql

In addition to being the application the Sybase IQ External Loader calls to execute the data load, you can use dbisql to troubleshoot problems with the environment. While you can use a different instance of dbisql, you may be able to better identify IQ Loader issues if you use the same dbisql environment the Informatica Integration Service uses. This means logging into the machine where the PowerCenter Integration Service and dbisql are installed, running the appropriate environment variable script, and connecting to the target Sybase IQ with dbisql. You can use dbisql for troubleshooting in a couple of different ways:

  • Verify the version of dbisql that the External Loader uses can connect directly to the target Sybase IQ successfully. Consider using dbisql in command line mode and passing a connection string, which is very similar to the way the loader works, as described in the previous section, Querying the Sybase IQ target.
  • Verify the generated control file runs through dbisql by either opening it from the File menu in the dbisql in GUI mode or passing the file as input with the connection string in command line mode, for example:
dbisql -c "uid=dba;pwd=sql;eng=hostname_iqdemo;CommLinks=tcpip{host=hostname;port=2638}" -nogui /datafiles/TgtFiles/customer.out.ctl
Read through any errors or messages reported by dbisql and troubleshoot according to the error. If the control file executes as expected through dbisql, look closer at the Integration Service environment, for example, look at the environment settings for the user who runs it.

Modifying the LOAD TABLE statement sytnax options

As stated previously, the Sybase IQ External Loader connection type generates a control file with the LOAD TABLE statement that will be executed by dbisql. You can modify the LOAD TABLE statement and test running it through dbisql as described previously, or by modifying the control file and then making it "read only," so the Integration Service does not overwrite it when it executes. For information about modifying the LOAD TABLE statement, see Appendix D and the section "Bulk loading data using the LOAD TABLE statement" in the System Administration Guide: Sybase IQ 15.1 Volume 1, which includes a complete list of supported syntax options.

Back to Contents


Appendix A: Viewing the Sybase IQ External Loader generated control and data files

This appendix provides sample control and data files generated by a PowerCenter Sybase IQ External Loader connection.

The format of the data output and control files is specified in the File Properties of the Target configuration in the Task Developer tool of the PowerCenter Workflow Manager application. For Sybase IQ External Loader connections, a File Writer is specified for the target configuration because the output will be written to a file (or pipe). You can set properties for the output files, including whether the output file and LOAD TABLE statement in the control file will be formatted as fixed-width or delimited. This configuration is discussed in the previous section Configuring a Session Task with an IQ External Loader connection.

Following are examples of both control and data files. You will see some Sybase IQ database options set in the control file. For information about these options as well as other options that may influence IQ Loader execution, see Appendix C: Sybase IQ database options relevant to loading data. For information about modifying the LOAD TABLE statement generated in the control file, see Appendix D.

Viewing the control file

The control file contains the LOAD TABLE statement generated by PowerCenter. The control file name is created based on the source name with .out and .ctl appended (for example, customer.out.ctl). You can see the control file by logging into the machine and directory where the files were written, or to a machine with that drive accessible, and opening the files. Note that you may need to use the user who executes the Integration Service to open this file or use a user with administrative privileges to open permissions on the file and then view it. For information about the LOAD TABLE statement, including a complete list of supported syntax options, see its description in the "SQL Statements" section of the Sybase IQ 15.1 Reference: Statements and Options manual.

Two sample control files follow. The first is a for a delimited data file. The second is for a fixed-width data file:

Informatica Generated Control File for a delimited data file

/* Sybase IQ Load Script
* Generated by Informatica PowerMart
* Control File Wizard
* On Thu Feb 18 13:20:48 2010

*/

SET TEMPORARY OPTION ON_ERROR=EXIT;
SET TEMPORARY OPTION ISQL_LOG='/datafiles/TgtFiles/supplier1.out.ldrlog';
SET TEMPORARY OPTION DATE_ORDER='MDY';
LOAD TABLE supplier
(
s_suppkey '|' ,
s_name '|' ,
s_address '|' ,
s_nationkey '|' ,
s_phone '|' ,
s_acctbal '|' ,
s_comment '\n' )
FROM '/datafiles/TgtFiles/supplier1.out'
WITH CHECKPOINT ON
BLOCK SIZE 50000
NOTIFY 1000
ESCAPES OFF
QUOTES OFF
ON FILE ERROR ROLLBACK
/* End of generated Control File */

You can see the file contains a header with information about the generated control file, three explicit Sybase IQ database option settings, the LOAD TABLE statement with pipe ('|') field delimiters and a newline ('\n') row terminator specified, the data (.out) file specified for load in the FROM clause, and the WITH CHECKPOINT ON, BLOCK SIZE 50000, NOTIFY 1000, ESCAPES OFF, QUOTES OFF and ON FILE ERROR ROLLBACK options set by default. Note that the ISQL_LOG option is deprecated in Sybase IQ version 15.1

Informatica Generated Control File for a fixed-width data file

/* Sybase IQ Load Script
* Generated by Informatica PowerMart
* Control File Wizard
* On Wed Jan 27 16:43:18 2010

*/

SET TEMPORARY OPTION ON_ERROR=EXIT;
SET TEMPORARY OPTION ISQL_LOG='/work/jnsmith/infa_datafiles/supplier.out.ldrlog';
SET TEMPORARY OPTION DATE_ORDER='MDY';
LOAD TABLE supplier
(
s_suppkey ASCII( 11) NULL('* ') ,
s_name ASCII( 25) NULL('* ') ,
s_address ASCII( 40) NULL('* ') ,
s_nationkey ASCII( 11) NULL('* ') ,
s_phone ASCII( 15) NULL('* ') ,
s_acctbal ASCII( 22) NULL('* ') ,
s_comment ASCII( 101) NULL('* ') ,
FILLER(1))
FROM '/work/jnsmith/infa_datafiles/supplier.out'
WITH CHECKPOINT ON
BLOCK FACTOR 10000
NOTIFY 1000
ESCAPES OFF
QUOTES OFF
ON FILE ERROR ROLLBACK
/* End of generated Control File */

The fixed-width file contains the same header and option settings as the delimited file above. But instead of a delimiter, it shows ASCII field widths and specifies to fill null values with an asterisk. It also specifies a FILLER of 1. The FILLER option specifies to skip over a specified field in the source input file.

Viewing the data file

The data file contains the data that will be loaded by the LOAD TABLE statement in the control file. The data file name is created based on the source name with .out appended (for example, customer.out). You can see the data file by logging into the machine and directory where the data and control files were written, or to a machine with that drive accessible, and opening the file. Note that you may need to use the user who executes the Integration Service to open this file or use a user with administrative privileges to open permissions on the file and then view it. Excerpts from two sample data files follow. The first is from a fixed-width data file; the second is from a delimited data file:

Fixed-width data file

Excerpt from fixed-width data file

You can see the data and fields with the allocated width.

Delmited data file

Excerpt from delimited data file

You can see the the data with a comma delimiter.

Back to Contents


Appendix B: Configuring Sybase IQ with ASE experience

This appendix contains notes related to using Sybase IQ with Informatica when you have a Sybase Adaptive Server Enterprise (ASE) back-ground.

  • dbisql differences - The PowerCenter Sybase IQ External Loader connection was designed for the version of dbisql installed with Sybase IQ and its database clients. The version of dbisql installed with Sybase IQ and its database clients is a Java application, whereas the dbisql installed with Sybase ASE and its database clients (PC-Client) use Sybase (native) Open Client libraries. Thus, the version of dbisql installed with the ASE products behaves differently than the version installed from Sybase IQ products.

For example, the dbisql installed with Sybase IQ is configured to set the ESCAPE_CHARACTER database option to 'ON' by default, which specifies to parse the escape character, whereas the version installed with Sybase ASE is configured to set this option to 'OFF.' This can severely impact Sybase IQ External Loader connections when, for example, the row delimiter is specified as '\0xa'. In this case, the load will fail because the target Sybase IQ cannot find and parse the end of the row of data. See Appendix D for information about modifications you can make to the LOAD TABLE statement to make it more compatible with other applications.

Another difference to consider is the environment. Integration Services running workflows that execute the IQ Loader connections must have the appropriate variables set for dbisql. They depend on the Sybase IQ environment variables as set by the Sybase-provided scripts or specified in the client README files. The environment varible settings required by the Java version of dbisql are different than those required by the one that uses the Sybase Open Client libraries. None of the Sybase IQ Database Clients include the Sybase Open Client SDK and the 15.1 version of the Sybase IQ server no longer does either. The 12.7 and previous versions of Sybase IQ server did include the Sybase SDK.

  • Transact-SQL (TSQL) support - Sybase IQ supports a subset of TSQL, the dialect of SQL supported by ASE. Sybase IQ also provides database options which may be configured so that Sybase IQ behaves like ASE. Appendix C contains a list of Sybase IQ database options relevant to loading data into Sybase IQ. The list indicates if the option is for TSQL support or dbisql connections. For information about Transact-SQL support, see "Appendix A: Compatibility with other Sybase Databases," in the the Sybase IQ product manual References: Building Blocks, Tables and Procedures.
  • Sybase (native) Open Client connectivity - Sybase IQ is not optimized for use with Sybase (native) Open Client connectivity, although it does support it. Many Sybase IQ options are enabled by default for ODBC and dbisql connections but disabled by default for Sybase Open Client and Sybase jConnect JDBC connections, which are Tabular Data Stream (TDS) applications. Thus, client connections to Sybase IQ may not behave as you expect if you are familiar with Sybase Open Client and Sybase ASE connections. The list of options in Appendix C also includes information about how an option is set for different types of connections. For information about setting up Sybase IQ for use as an Open Server application, also see "Appendix A: Compatibility with other Sybase Databases," in the the Sybase IQ product manual References: Building Blocks, Tables and Procedures.
  • Following is a list of options set by TDS connections. See the following Appendix C for explanations:
    --SET TEMPORARY OPTION ansinull='OFF';
    --SET TEMPORARY OPTION tsql_variables='ON';
    --SET TEMPORARY OPTION ansi_blanks='ON';
    --SET TEMPORARY OPTION chained='OFF';
    --SET TEMPORARY OPTION quoted_identifier='OFF';
    --SET TEMPORARY OPTION allow_nulls_by_default='OFF';
    --SET TEMPORARY OPTION on_tsql_error='CONTINUE';
    --SET TEMPORARY OPTION isolation_level='1';
    --SET TEMPORARY OPTION date_format='YYYY-MM-DD';
    --SET TEMPORARY OPTION timestamp_format='YYYY-MM-DD HH:NN:SS.SSS';
    --SET TEMPORARY OPTION time_format='HH:NN:SS.SSS';
    --SET TEMPORARY OPTION date_order='MDY';
    --SET TEMPORARY OPTION escape_character='OFF';

Back to Contents


Appendix C: Sybase IQ options relevant to loading data

This section contains Sybase IQ database options that affect client connections and database behavior in relation to loading data. For Sybase IQ, the database options are categorized as follows:

  • General database options - are Sybase IQ database options
  • dbisql options - are connection behavior options for the version of dbisql installed with Sybase IQ. Thus, they are especially relevant to IQ Loader configuration.
  • Transact-SQL (TSQL) options - Transact-SQL is the dialog of SQL that Sybase ASE supports. These options are provided for ASE compatibility.

For more informaton about the categories of options and a complete list of options, see the Sybase IQ manual Reference: Statements and Options. If an option is a TSQL or DBISQL option, it will be indicated in the list. The options listed are supported by both Sybase IQ 12.7 and 15.1 unless noted otherwise.

Option Name Type Description

STRING_

RTRUNCATION =

'OFF'

TSQL Indicates whether an error is raised when an insert, update or load truncates a char or varchar string. When it is enabled (set to 'ON'), this option raises an error and performs a ROLLBACK. When it is disabled (set to 'OFF'), it does not. It is disabled by default in Sybase IQ version 12.7 but is enabled by default in versions 15.1. This option inevitably causes errors during loads and insertions when it is enabled. Thus, if you are using Sybase IQ version 15.1, you will need to disable it.

ALLOW_NULLS_BY_DEFAULT=

'ON'

TSQL Indicates whether a column can accept null values. For dbisql connections to Sybase IQ 12.7 and 15.1 , it is enabled by default. For Sybase (native) Open Client and jConnect JDBC connections, it is disabled.
ANSINULL='ON' TSQL Controls interpretation of using = and !=.When this option enabled, which it is by default, results of these comparisons are unknown. When this option is disabled, it allows comparisons with NULL to yield results that are not unknown, which is compatible with ASE.
QUOTED_IDENTIFIER='ON' TSQL Controls the interpretation of strings that are enclosed in double quotes. For dbisql connections, it is enabled by default. For Sybase (native) Open Client connections, it is disabled.
ON_ERROR='PROMPT' dbisql Controls what happens when an error is encountered while executing statements in dbisql. Value options are as follows: PROMPT (default), STOP, CONTINUE, EXIT, NOTIFY_CONTINUE, NOTIFY_STOP, NOTIFY_EXIT. The LOAD TABLE statement in the control file generated by the IQ External Loader connection sets this option to EXIT.
ISQL_LOG='/path/logfile_name.log' dbisql Controls logging behavior. This option is supported in 12.7 but deprecated in 15.1. The LOAD TABLE statement in the control file generated by the IQ External Loader sets this option to the directory and Sybase IQ External Loader log file name.
DATE_FORMAT='YYY-MM-DD' TSQL Sets the format used for dates retrieved from the database. See the descripton of this option Appendix D or in the alphabetical list of options in the aforementioned Sybase IQ reference manual.
DATE_ORDER='MDY' TSQL Controls the interpretation of date formats. Valid options are as follows: 'MDY', 'YMD' (Sybase IQ default) or 'DMY.' The LOAD TABLE statement in the control file generated by the IQ External Loader sets this option 'MDY.'

CHAINED='ON'

TSQL Controls transaction mode in the absence of a BEGIN TRANSACTION statement. When it is enabled ("in chained mode"), the transaction is implicitly started before any data retrieval or modification statement. When it is disabled, each statement is commited individually unless an explicit BEGIN TRANSACTION is executed to start a transaction. the CHAINED option is enabled by default for Sybase IQ and disabled for Sybase Open Client and jConnect JDBC connections.
FLOAT_AS_DOUBLE='OFF' TSQL Controls interpretation of the FLOAT keyword for Sybase IQ 12.7 but is deprecated in 15.1. When it is disabled, which it is by default, Sybase IQ interprets FLOAT values as REAL values. When it is enabled, Sybase IQ treats float values like DOUBLE values, which is how Sybase ASE treats FLOAT values.
CONVERSION_ERROR='OFF' TSQL Controls whether data type conversion failures that occur when data are loaded into Sybase IQ are reported as errors. When this option is enabled, which it is by default in IQ 15.1, Sybase IQ reports an error. When disabled, Sybase IQ reports a warning. Each Sybase IQ thread doing data conversion for a LOAD statement writes at most one warning message to the .iqmsg file. If conversion errors are reported as warnings only, the NULL value is used in place of the value that could not be converted. In embedded SQL, an indicator variable is set to -2 for the column or columns that cause the error.
CASESENSITIVE='OFF'   Controls whether or not the Sybase IQ database is case-sensitive or not. When enabled, it is. When disabled, which it is by default, it is not.

Back to Contents


Appendix D: Modifying the LOAD TABLE statement

This appendix describes modifications you can make to the LOAD TABLE statement in the Informatica-generated control files. It includes suggested syntax for improved performance and support of different types of client connections and platforms.

In the time since the Informatica control file templates were developed, Sybase IQ has released several enhancements that affect the LOAD TABLE statement. These enhancements include the FORMAT BCP option and the option to provide DEFAULT values in the column specification. However, the Informtica templates have not yet been updated to implement these enhancements. Thus, they are not currently supported by Informatica. To take advantage of these enhancements, you can modify the generated LOAD TABLE statement in the control file. Caution! If you do modify the generated control file, you will need to restrict permissions on the file to prevent the Integration Service from overwriting it every time the workflow runs.

FORMAT BCP

A key option to consider is the FORMAT BCP syntax supported by the LOAD TABLE statement. Using FORMAT BCP with the following options option allows the LOAD TABLE statement to run in multi-platform environments and with different client connections. Note that you will need to ensure that the appropriate variable settings are made for the different connections:

  • ESCAPE_CHARACTER = 'ON' - Sybase IQ database option setting
  • DELIMITED BY field delimiter - option for the LOAD TABLE statement in the control file
  • ROW DELIMITED BY row delimiter - option for the LOAD TABLE statement in the control file

The following sample control file shows the example "Informatica Generated Control File for a delimited data file" from Appendix A with modifications to use the FORMAT BCP option. The differences in this LOAD TABLE statement compared with the version generated by Informatica are as follows:

  • Inclusion of the set statement that enables the ESCAPE_CHARACTER database option.
  • Removal of the field delimiter from the column specification. The columns are separated by only a comma in the specification.
  • Inclusion of the FORMAT BCP option after the FROM file specification.
  • Inclusion of the field delimiter after the FROM file specification (DELIMITED BY '|').
  • Inclusion of the row delimiter/terminator after the FROM file specification in hexadecimal representation (ROW DELIMITED BY '\x0a').
  • Inclusion of a COMMIT statement.
/*  Sybase IQ Load Script
 *  Generated by Informatica PowerMart and modified by J. Smith 3-24-10
 *  Control File Wizard *  On Wed Jan 27 15:50:39 2010
 */
SET TEMPORARY OPTION ON_ERROR=EXIT;
SET TEMPORARY OPTION ISQL_LOG='/datafiles/TgtFiles/tpchcustomer1.out.ldrlog';
SET TEMPORARY OPTION DATE_ORDER='MDY';
set temporary option ESCAPE_CHARACTER='ON';
LOAD TABLE  supplier
   (
   S_SUPPKEY,
   S_NAME,
   S_ADDRESS,
   S_NATIONKEY,
   S_PHONE,
   S_ACCTBAL,
   S_COMMENT
   )
   FROM '/datafiles/TgtFiles/supplier.out'
   QUOTES OFF
   FORMAT BCP
   ESCAPES OFF
   DELIMITED BY '|'
   ROW DELIMITED BY '\x0a'
   PREVIEW ON
   NOTIFY 1000000
   ;
   COMMIT;

Row Delimiter (terminator) issues between Operating systems

As shown in the sample LOAD TABLE statement above, you can specify the row delimiter (terminator) in hexadecimal representation (ROW DELIMITED BY '\x0a'). Doing so can prevent problems with different operating systems using different row delimiters. For example, by default, when the Integration Service runs on Sun SPARC Solaris, Informatica generates the LOAD TABLE statement with the new-line ('\n') row terminator. This can cause problems for othe operating systems. The ESCAPE_CHARACTER database option must be set when you use the hexadecimal representation or the load will fail because it will not parse the backslash.

Handling date formats, DATE ORDER and DATE FORMAT options

The Informatica-generated LOAD TABLE statement sets the DATE ORDER option to 'MDY'. While this specifies the order in which to input the date (month, day, year), it does not specify how to format the date. Thus, you may want to include a setting for the DATE_FORMAT option. Consider the following DATE_FORMAT settings for 27-Feb-2010, 27-Feb-2010:

SET TEMPORARY OPTION DATE_FORMAT='dd-MMM-yyyy'

Where the possible choices are:

yy 2-digit year
yyyy 4-digit year
mm 2-digit month, or 2-digit minutes if following a colon (as in'hh:mm')
mmm 3-character name of month
mmmm[m...] Character long form for months—as many characters as there are m's, until the number of m’s specified exceeds the number of characters in the month’s name.
d Single-digit day of week, (0 = Sunday, 6 = Saturday)
dd 2-digit day of month
ddd 3-character name of the day of week.
dddd[d...] Character long form for day of the week—as many characters as there are d's, until the number of d’s specified exceeds the number of characters in the day’s name.
hh 2-digit hours
nn 2-digit minutes
ss[.s...s] Seconds and parts of a second; up to six digits can follow the decimal point
aa AM or PM (12 hour clock)
pp PM if needed (12 hour clock)
jjj Day of the year, from 1 to 366

So "Fri 13-Nov-2010" would be 'ddd dd-mmmmmmmm-yyyy'
(you need that many "m's" to cope with "November")

Specifying DEFAULT values

As of Sybase IQ 12.7 ESD#4, Sybase IQ supports loading a default value into a column as specified in the LOAD TABLE statement using the new DEFAULT clause in the column specification. This requires including the option DEFAULTS ON after the FROM statement. If it is set to 'OFF', the specified load default is not used and a NULL value is inserted instead. The default value specified must be appropriate for the data type, size and constraints of the column. The DEFAULTS option does not support autoincrement, identity or global autoincrement. Consider the following example, which shows a default value of 10 specified for column c3:
LOAD TABLE t1 (c1, c2, c3 DEFAULT '10')
FROM ...
DEFAULTS ON
FORMAT BCP
QUOTES OFF
ESCAPES OFF

For more information, see the section "LOAD TABLE supports loading default values [CR 396843]" in the Sybase IQ Releaes Bulletin for Windows version 12.7, ESD #4.

Handling NULL values and empty strings

The LOAD TABLE statement also allows for the specification of NULL values in the form of NULL (BLANKS, ZEROS, 'literals'). You can specify a value for NULLs in fixed-width files in the PowerCenter 8.6 Session Task Target file properties (accessible in the Task Developer tool in the Workflow Manager); but not for delimited files. Thus, you can modify the column specification in the LOAD TABLE statement to include a NULL value specification. Consider the following example, which specifies to load the word 'NULL' for null values in column c3:

LOAD TABLE t1 (c1, c2, c3 NULL('NULL')
FROM ....
FORMAT BCP ...

Client-side LOADING

With version 15.0, Sybase IQ now supports client-side loading with the addition of the ALLOW_READ_CLIENT_FILE database option and USING CLIENT FILE option of the LOAD TABLE statement. These options allow you to load data from a file on the client machine. The USING CLIENT FILE option replaces the FROM file specification in a regular LOAD TABLE statement. For example,

LOAD TABLE t1(c1,c2)
USING CLIENT FILE 'c:\\clientfile\\file.out'

For details, see “allow_read_client_file option [database]” in SQL Anywhere Server – Database Administration > Configuring Your Database > Database options > Introduction to Database options >Alphabetical list of options.

Other things to consider

Setting database options

Sybase recommends that you set as many of the database options on the target database as possible to reduce the number of temporary option settings you must make in the control file.

What to avoid when using the LOAD TABLE statement

While you may need to experiment with settings to get the control file and LOAD TABLE statement running initially, there are settings you should avoid in production. Some of the settings may degrade performance, by causing Sybase IQ to run in single-threaded mode, or degrade integrity, by ignoring constraints.

  • SKIP - when you use SKIP, the Sybase IQ engine may process a portion of the LOAD TABLE statement in single-threaded mode.
  • IGNORE CONSTRAINTS - ignoring constraints can lead to loss of integrity
  • FILLER() -also may cause the Sybase IQ engine to process a portion of the LOAD TABLE statement in single-threaded mode
  • Fixed-width files - may not process as fast as delimited files
  • Any type of formatting on column mapping

Back to Contents


References

Following are the references used in writing this document.

  • Advanced Workflow Guide, included with Informatica PowerCenter version 8.6, especially the "External Loaders" and "Loading to Sybase IQ" sections
  • PowerCenter version 8.6 on-line help
  • Sybase IQ Reference Manual, included with Sybase IQ versions 12.7 and 15.1
  • System Administration Guide: Volume 1 Sybase IQ 15.1, specifically the section "Bulk loading data using the LOAD TABLE statement," and also "Partial-width insertions," "Moving data to a system with a different endian format" and "Tuning bulk loading of data"
  • "Appendix A: Compatibility with other Sybase Databases," in the the Sybase IQ product manual References: Building Blocks, Tables and Procedures
  • "Sybase IQ Hardware Sizing Guide," Sybase white paper by Mark Mumy

Back to Contents

© Copyright 2014, Sybase Inc. - v 7.6