FROM: SYBASE
The following information relates to the setup and configuration of this environment. In this sample we show how to transfer data into IQ using Informatica's Relational ODBC method. We have noted throughout the report where you would need to change properties to transfer data into IQ using Informatica's Bulk Load method, which utilizes Sybase's IQ External Loader. We recommend using the Adaptive Server IQ Installation and Configuration Guide to assist in the setup and configuration of IQ. We recommend using the Informatica Installation and Configuration Guide, Workflow Administration Guide and Designer Guide distributed with the Informatica PowerCenter product to assist in the setup and configuration of Informatica. Additionally, to quickly get started building workflows, we recommend the tutorials available within the Informatica Getting Started documentation.
1. Install and configure Sybase ASE on the server machine, according to the product documentation. The 12.5.3 version used in the test environment consisted of the 12.5.2 Enterprise server installation, followed by installation of EBF 12461 to upgrade it to 12.5.3.
2. Install and configure the ASE client, including the ODBC drivers, according to the product documentation.
3. Create the Informatica repository and source databases in ASE using the Sybase Central utility, which is installed with the ASE. The repository database will be used later for the Informatica configuration. The Informatica Installation and Configuration Guide recommends the repository database be 100-150MB in size. For issues related to creating the repository database, see Test Note 11 and Test Note 2. Set the option Allow nulls by default to true on the database. You can display the database options in Sybase Central by right-clicking the database, selecting Properties from the pop-up menu and then displaying the Options tab. Sybase also recommends setting the Truncate log on checkpoint option to true.
4. Create a new "repository" login within Sybase ASE and define the repository database as its default. Then change the "repository" database owner from "sa" to "repository". Set the logins to be the database owners (dbo) of the databases.
5. Create a new "source" login within ASE and define the source database as its default. Then change the "source" database owner from "sa" to "source".
6. Install and configure Sybase IQ on the server machine, according to the Adaptive Server IQ Installation and Configuration Guide. Important! Do not install Sybase IQ 12.7 on the same Windows machine with Sybase ASE 12.5.x. The two products install and use different versions of the Sybase native connectivity program, Open Client. Thus, they are incompatible. Note that you can install the Sybase IQ 12.7 Windows Client on the same machine with Sybase ASE 12.5.3 and will need to for the Sybase IQ ODBC driver.
7. Install Informatica PowerCenter, according to the Informatica Installation and Configuration Guide. Note that the Informatica documentation recommends to avoid installing PowerCenter on machines running DHCP. The installing user and Windows Start Service user must have administrator privileges. Also, in the test environment we accepted and used the default code page, which is MS Windows Latin 1 (ANSI) superset of Latin 1.
8. Create the Sybase IQ target database. Refer to the IQ Reference Manual for more information. Alternately, you may use the asiqdemo server and database installed during the IQ installation. See Test Note 12 for issues related to using the asiqdemo server and database.
9. Define ODBC datasources (System DSNs) for the ASE repository, ASE source and IQ target databases using the ODBC Administrator. Refer to Test Note 13 and Test Note 14 for detailed information on the ASE and IQ ODBC definitions. Refer to Test Note 10 for issues related to Informatica and the ODBC definitions.
10. Configure the Repository Server, according to the Informatica Installation and Configuration Guide. Note that there are actually two Informtica servers you need to configure: 1) the Repository Server, which is on the Start menu as RepServer, and 2) the PowerCenter Server, which is on the Start menu as the Server.
a. From the Windows Start menu, launch Programs -> Informatica PowerCenter 7.1 -> Informatica PowerCenter 7.1 - RepServer -> Informatica Repository Server Setup. The Configure Repository Server dialog box appears.
b. Enter the Server Port Number value to be used for the Repository Server. The default value is 5001, which was already in use by the Sybase ASE Backup Server. To avoid conflicts in the test environment, we used port 7001 for the Informatica Repository Server instead. For information about the Sybase ASE Backup Server, refer to the Sybase ASE documentation. The Server Port Number value will be used again for the Repository Server Port Number definition within the Informatica Server configuration in the next step.
c. Enter an Administrator password. In the test environment, we entered "password."
d. Enter the Minimum Port Number in the Repository area. The default is 5002, which is another port used by Sybase ASE. In the test environment, we set this to 7002.
11. Configure the PowerCenter Server, according to the Installation and Configuration Guide. From the Windows Start menu, launch Programs -> Informatica PowerCenter 7.1 -> Informatica PowerCenter 7.1 - Server -> Informatica Server Setup. The Configure Informatica Service - Version[7.1] dialog box appears.
a. On the Server tab, assign a name to the Informatica PowerCenter server (not the host machine name) in the Server Name field and ip address of the machine where the Informatica Server will be running in the TCP/IP Host Address field.
b. Within the Licenses tab, enter the license keys within the with Key: field and choose Update for each of the following: Platform, Sybase and ODBC. Licensed products that will be supported within Informatica based on the entered keys will display.
c. On the Repository tab, enter the following field values:
Repository Name - name of the Repository Server
Repository User - ASE login for the repository database
Repository Password - the corresponding ASE login password for the repository database
Repository Server Host Name - the ASE server name, as defined within the SQL.INI file, where the repository database resides
Repository Server Port Number - the port number that the Repository Server will be using. This is the same port number configured in the Repository Server configuration (7001 in the test environment).
d. Select OK to save the Informatica Server information.
12. Display the Licenses tab. It lists all licenses entered during installation. If you have optional licenses, for example, for connectivity or options, you may enter them here. For the remaining tabs (DB Compatibility, Configuration and Java SDK), the default values were used.
13. Click OK to complete the Informatica Repository Server configuration.
14. Configure the Repository. Refer to the Informatica PowerCenter Installation and Configuration Guide for instructions to configure the repository.
15. Start the Informatica Repository Server service within the Windows Services.
Note: If you cannot start the Informatica Server, use the Windows Event Viewer to check the application log. If possible, correct any errors described in the application log. Refer to the Informatica Troubleshooting Guide for more information.
16. Populate the repository for the Informatica server using the PowerCenter Repository Manager:
a. The ASE repository database created prior will be used to populate the repository. Launch the Repository Manager and select Repository -> Add Repository from its menu.
b. Connect to the repository: Right-click on the repository name and select Connect. Within the Connect to Repository screen, enter the Username and Password to connect to the ASE repository database (EX: repository
). Enter the Host Name and Port Number for the Informatica Server.
c. Create a new folder within the repository: Within the Repository Manager, select Folder -> Create. This will be your workspace as you define workflows.
17. To allow the Informatica Workflow Manager and Designer to connect to Sybase IQ, add the following entry to [ODBCDLL] section of the Informatica powrmart.ini file, which is located in the %INFORMATICA%\Client directory, where %INFORMATICA% is the Informatica PowerCenter 7.1 installation directory. Note that powrmart.ini is the correct spelling of the file name. Also note, you will see other DLLs listed in this section. In the test environment, we add this entry to the top of the list.
[ODBCDLL]
Adaptive Server IQ=PMODBC.DLL
18. Define the database connections within the Workflow Manager
a. Launch the Workflow Manager from the Windows Start menu.
b. Define Relational connections for the target IQ database, the source ASE database and the ASE repository database:
c. Select Connections -> Relational from the menu. The Relational Connection Browser panel will display.
d. Select ODBC from the Select Type drop down. Choose the New button to create a new connection.
e. Enter the User Name and Password for the given database. Enter the ODBC System DSN (defined prior) into the Connect String field.
Note: We used the default Code Page (MS Windows Latin 1)
f. Define a Loader connection to the target IQ database for External Load (external file load):
Select Connections -> Loader from the menu.
Select Sybase IQ External Loader from the Select Type drop down. Choose the New button to create a new connection.
Enter the full Connect String for the target Sybase IQ database, similar to the following example that connects to a default asiqdemo database:
uid=dba; pwd=SQL; eng=humor_asiqdemo; links=tcpip(host=humor;port=2638)
Note: In the above example, "humor" was the hostname where our asiqdemo database was installed. Change this according to the hostname where your asiqdemo database is installed. Also, note that the default password is case-sensitive and must be all-caps.
See Test Note 3 for further details.
19. Import source and target definitions, according to the Informatica Designer Guide, Chapters 1, 2, 4:
a. Launch the Informatica Designer application from the Windows Start menu.
b. Connect to the repository.
c. Open the new folder (each folder contains sections for sources, targets, mappings, etc.).
d. From the menu, choose Tools -> Source Analyzer. This will bring up the workspace for the Source Analyzer and shortcut icons for the other designer tools (Warehouse Designer, Transformation Developer, Mapplet Designer, Mapping Designer)
e. From the menu, choose Sources -> Import from Database. Select the appropriate ODBC data source, enter Username and Password to connect to this database, and select the Connect icon. The available tables within this database will be displayed.
f. Highlight each table you would like to use as a source (the shift key will enable multiple tables to be highlighted at once), and select OK to import them into your Source Analyzer workspace.
g. Repeat this step to import target tables into the Warehouse Designer.
20. Create mappings, according the Informatica Designer Guide, Chapters 5:
a. Within the Designer tool, switch to the Mapping Designer.
b. From the menu, select Mappings -> Create.
c. In the Mapping Name dialog box enter the name of your new mapping and click OK. This will open a new, blank workspace for the mapping.
d. In the Repository Navigator on the left, click and drag the desired Source and Target tables into the mapping workspace.
e. Create a new Source Qualifier within the workspace: From the Designer menu, select Transformation -> Create. Select the Source Qualifier transformation type, enter a unique name for it and select OK. Select the sources (tables) you want this transformation to use and OK. You may add additional transformations at this time or select Done to finish. The source table columns will be automatically linked to the source qualifier columns.
f. Link each of the source qualifier columns to the target table columns: This can be done row by row by clicking each of the source qualifier column's datatype and dragging it into the corresponding target column. Or you can select all the columns on the source qualifier and then drag them into the target. Verify all the connections are correct. If a connection is made incorrectly, highlight the connector and press Delete.
g. From the Designer menu, select Repository -> Save to save the mapping.
Note: During testing, it was observed that the target table within the mapping is sometimes renamed, appending a number "1". This would cause the workflow to fail later on. To correct this, the target table needs to be renamed within the mapping. See Test Note 9 for further details.
21. Create and configure a workflow within the Workflow Manager, according to the Informatica Workflow Administration Guide, Chapter 4.
a. Create the new workflow:
From the Workflow Manager menu, select Workflows -> Create.
Give the workflow a unique name and select OK. The new workflow workspace will contain a Start icon.
b. Add a session task to the workflow:
From the Task Developer menu, select Tasks -> Create.
Select Session from the Task Type drop-down, enter a unique name for the task and select Create.
Select a Mapping (defined prior) to associate with this session.
At this time additional tasks may be added or select Done when finished.
c. Link the Start and session tasks using the Link Tasks icon.
d. Configure the session task:
Double click on the session task to update the configuration.
Update the Properties Tab values:
$Source Connection Value - highlight the Value field and click on the drop down arrow. The Connection Browser window will appear.
Within the Connecton Browser window, select ODBC from the Select Type field drop down list. Within the Objects field, select the ODBC connection defined prior for the ASE source database.
$Target Connection Value - highlight the Value field and click on the drop down arrow. The Connection Browser window will appear.
Within the Connecton Browser window, select ODBC from the Select Type field drop down list. Within the Objects field, select the ODBC connection defined prior for the IQ target database.
Update the Mapping Tab values:
Under the Task Type (left), highlight each Source and update the following:
Define the Instance | Reader as Relational.
Define the Connections | Value as the ODBC connection defined prior for the ASE source database.
Under the Task Type (left), highlight each Target and update the following:
Define the Instance | Writer as Relational Writer.
Note: While using the External Loader to load the target table data from a flat file, define this value as File Writer.
Define the Connection | Value as the ODBC connection defined prior for the IQ target database.
Note: While using the External Loader to load the target table data from a flat file, define the Connections | Type as Loader and the Value as the previously defined Sybase IQ External Loader connection definition.
Define the Properties | Attribute | Target Load Type as Normal.
Note: While using the External Loader to load the target table data from a flat file, define the Target Load Type as Bulk.
See Test Note 5 for information on which Insert, Update and Delete attributes should be selected.
Select OK to apply the modifications and close the Task window.
From the Workflow Manager menu, select Repository -> Save. The workflow tasks will be saved and validated. Verify the validation was successful within the Output Window at the bottom of the Workflow Manager.
22. Execute the workflow:
a. Right click in the workflow workspace and select Start Workflow.
b. The PowerCenter Workflow Monitor will open and display the status of the execution. Once the Status is shown as Succeeded, check the target table for the loaded data.
23. Verify the data was successfully loaded into the target table.
a. Use IQ's Interactive SQL (dbisql) utility to connect to the IQ server and verify that the data load process has been successful.
Note: If you keep your dbisql session open while doing your testing, don't forget to first execute a "commit" before executing a "select * from table" to validate data has been loaded properly.
--------------------------------------------------------------------------------
Test Notes
This section contains information gained during testing, which may be helpful.
Test Note 2: IQ cannot be used as an Informatica repository database
Currently, Informatica does not support IQ for the repository database.
Test Note 3: Defining your workflow to use the Sybase IQ External Loader (external file):
To use the IQ External Loader from Informatica, the Sybase IQ External Loader connection string needs to be defined. Within the Workflow Manager, select Connections -> Loader to start the Loader Connection Browser. Select the New button to open the SubType panel.
Highlight Sybase IQ External Loader and select OK. Within the Connection Object Definition panel, complete the following properties as follows:
Connect String: uid=dba; pwd=SQL; eng=humor_asiqdemo; links=tcpip(host=humor;port=2638)
Server Datafile Directory: %INFORMATICA%\Server\TgtFiles (where %INFORMATICA% is the Informatica software install directory)
External Loader Executable: dbisql
Test Note 5: To successfully load UPDATES into IQ, specific properties must be set.
To successfully apply UPDATES and INSERTS via Informatica against IQ, the following parameters must be set:
Launch the Workflow Manager, right click on the session task and select Edit.
Within the Edit Tasks panel, select the Properties tab and change the following:
Treat source rows as: Update
Commit Type: Target
Select the Mapping tab, highlight each Target instance on the left, and make sure the following Property Attribute is selected (checked):
Insert
Update Else Insert
Delete
Also, verify that the Writers | Instance | Writers is set to Relational Writer, not File Writer.
Test Note 9: Target tables imported into the Designer have a "1" appended to the name.
When creating a mapping that uses source and target tables with the same name, the name of the target table within the map is modified, appending a '1' to the original table name. For example, test12 will be changed to test121.
Workaround: Edit the table properties and rename it to the correct name (removing the extra "1" at the end of table name).
Test Note 10: ODBC User DSN's fail when used within Informatica workflows.
Trying to connect to an ODBC User DSN fails with the error: "Can't find source"
Workaround: Define the ODBC definitions as System DSN's within the ODBC Administrator. See Test Note 13 and Test Note 14 for information on creating the ODBC System DSN definitions.
Test Note 11: The creation of the repository database fails.
Trying to create the repository for the first time fails with the following error:
ERROR : OBJM_54543 [Mon Jan 13 10:56:54 2003] : (371|428) DataBase error:
Mon Jan 13 10:56:54 2003 SQL Server Message 233 : The column VARIABLE_PREFIX in table OPB_ATTR does not allow null values.
Workaround: Using Sybase Central, set the database option allow nulls by default on the ASE repository database, prior to populating the repository from within the Repository Manager.
Test Note 12: Workflows appear to hang in a "Running" state.
Informatica workflows are started and appear to hang in a "Running" state. No error messages are reported within the workflow's session log. The asiqdemo session window reports the following error:
You have run out of IQ STORE dbspace in database d:\Sybase\ASIQ-12_6\demo/asiqdemodb. In another session, please issue a CREATE DBSPACE ... IQ STORE command and add a dbspace of at least 8 MB.
Workaround: Within an IQ Interactive SQL session (dbisql), extend the asiqdemo database space using the CREATE DBSPACE command. See the Adaptive Server IQ Reference Manual for further details.
Test Note 13: Defining an ASE ODBC System DSN
Within the ODBC Administrator, define the ASE ODBC System DSN as follows:
Use the Sybase ASE ODBC Driver for the ASE ODBC DSNs definitions.
Network Address: , For example, Network Address: humor,5000
Database Name: For example, Database Name: source
Test Note 14: Defining an IQ ODBC System DSN
Within the ODBC Administrator, define the IQ ODBC System DSN as follows:
Use the Adaptive Server IQ driver for the IQ ODBC DSNs definitions.
Within the Login tab, enter a valid IQ database User ID and Password. For example, User ID: dba, Password: sql
Within the Database tab, enter a valid IQ Server Name and Database File. For example, Server Name: humor_asiqdemo, Database File: D:\Sybase\ASIQ-12_6\demo\asiqdemo.db
--------------------------------------------------------------------------------
Issues and Incompatibilities
This section describes the issue(s) found during testing.
Issue 1: The following datatype issues were identified during testing:
MONEY (Open Client)
When Informatica moves some large money values from ASE to IQ using Open Client connectivity, the decimal places are rounded. For example, 922337203685477.48 is rounded to 922337203685477.50
MONEY (ODBC)
When Informatica moves some money values from ASE to IQ using ODBC connectivity, the decimal places are rounded. For example, 123456789.1230 is rounded to 123456789.1229
DECIMAL
When Informatica moves the maximum and minimum decimal values from ASE to IQ using ODBC connectivity, the values are modified. For example:
9999999999999999999999999999999 is converted to 9999999999999998509996388122624
-9999999999999999999999999999999 is converted to -9999999999999998509996388122624
DATETIME
When Informatica moves datetime values from ASE to IQ using ODBC connectivity, if the datetime value falls outside the "1600-01-01 23:59:59" to "7911-01-01 00:00:00" range, the HH:MM:SS.ss portion is not saved within IQ, thus they are set to 00:00:00.00. For example:
7912-12-31 00:00:59.333 is converted to 7912-12-31 00:00:00.000
TIMESTAMP
ASE and IQ use the TIMESTAMP datatypes in different manners, thus our TIMESTAMP testing was limited. Within ASE, you are only allowed one TIMESTAMP field per table. This field is used as a binary counter that gets incremented every time the row changes. Within IQ, this datatype is used as a data field much like a DATETIME value. When loading TIMESTAMP values from ASE to IQ, PowerCenter loads them into IQ as null values instead of hexadecimal value. CR17761 is open with Informatica engineering to address this issue.
Issue 2: Sybase IQ External Loader fails to run across platforms
Workflows that attempt to load data into remote Sybase IQ installations on different platforms via a Sybase IQ External Loader connection fail to load the data. This is true even though the workflow may report that it ran successfully. This problem occurred in the test environment where the PowerCenter server node, repository, integration service and clients were running on a Windows PC and a second server node was running on a remote Sun Solaris machine with the target Sybase IQ installation.
--------------------------------------------------------------------------------
Test Environment
This section provides details about the test environment.
Server and Client Machine
Hardware or Software Description or Version
Compaq Proliant 3000 2 Pentium 300 Mhz processors; 512 MB RAM
Microsoft Windows 2000 Server Service Pack 4
Sybase Adaptive Server Enterprise 12.5.3, EBF 12461
Sybase IQ 12.7 (060427)
Informatica PowerCenter 7.1 (R162 D73)
Sybase Adaptive Server IQ ODBC Driver (DBODBC9.DLL) 9.00.02.1014 (dated 4/17/2006)
Sybase ASE ODBC Driver (SYODASE.DLL) 4.20.00.67 (dated 3/23/2004)