User’s Guide Sybase IQ ETL 4.
DOCUMENT ID: DC00608-01-0410-01 LAST REVISED: October 2006 Copyright © 2003-2006 by Sybase, Inc. All rights reserved. This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement. To order additional documents, U.S.
Contents About This Book ........................................................................................................................... ix CHAPTER 1 CHAPTER 2 User’s Guide Sybase IQ ETL ................................................................................. Sybase IQ ETL architecture ............................................................. Sybase IQ ETL Development.................................................... Sybase ETL Server .........................................
Contents Using the pop-up menu to process commands....................... 16 Using the Component Store section .............................................. 16 Customizing preferences ............................................................... 17 CHAPTER 3 Getting Started............................................................................... 21 Starting Sybase IQ ETL Development ........................................... 21 Creating your first project .......................................
Contents Using multiple engines to reduce job execution time ..................... Defining multi-engine jobs ....................................................... Executing multi-engine jobs .................................................... Analyzing performance data .......................................................... Performance data model and content ..................................... Example reports ......................................................................
Contents APPENDIX A vi Project component ................................................................ Synchronizer component....................................................... Multi-Project component........................................................ Finish component .................................................................. Error component.................................................................... 121 122 123 124 124 Function Reference ....................................
uMonthNameShort ....................................................................... 145 uSeconds...................................................................................... 146 uTimeDiffMs ................................................................................. 146 uWeek .......................................................................................... 147 uWeekday..................................................................................... 147 uWeekdayName ...........
uPow, uPower .............................................................................. 175 uRandom ...................................................................................... 175 uRound ......................................................................................... 175 uSgn ............................................................................................. 176 uSqrt .............................................................................................
GRID Engine Server .............................................................. 192 GRID Engine Server as Windows System Service................ 193 Troubleshooting............................................................................ 194 Command line parameters ........................................................... 195 INI file settings .............................................................................. 196 Default.ini.................................................................
x Sybase IQ ETL 4.
About This Book About This Book Audience This guide is for users of Sybase IQ ETL. Sybase IQ ETL provides extract, transform, and load (ETL) capabilities that you can use to transform data from data providers to data targets. Sybase IQ ETL supports a variety of transformation capabilities and enables you to convert, cleanse, merge, and split data streams. The resulting data stream can then insert, update, or delete data in a given data target.
• Sybase certifications on the Web Appendix C, “Queuing and Executing Process Calls”describes ProcessQ, which is an application that can queue and execute process calls parallel or in sequence. Technical documentation at the Sybase Web site is updated frequently. ❖ ❖ ❖ Finding the latest information on product certifications 1 Point your Web browser to Technical Documents at http://www.sybase.com/support/techdocs/. 2 Click Certification Report.
About This Book 3 Select a product. 4 Specify a time frame and click Go. A list of EBF/Maintenance releases is displayed. Padlock icons indicate that you do not have download authorization for certain EBF/Maintenance releases because you are not registered as a Technical Support Contact. If you have not registered but have valid information provided by your Sybase representative or through your support contract, click Edit Roles to add the “Technical Support Contact” role to your MySybase profile.
If you need help xiv Each Sybase installation that has purchased a support contract has one or more designated people who are authorized to contact Sybase Technical Support. If you cannot resolve a problem using the manuals or online help, please have the designated person contact Sybase Technical Support or the Sybase subsidiary in your area. Sybase IQ ETL 4.
CH A PTE R 1 Sybase IQ ETL Topic Sybase IQ ETL architecture Page 1 Projects and jobs Component concepts 2 2 Running a project Customizing a project 3 3 Understanding repositories Converting datatypes and data formats 4 5 SQL Tools 5 6 Unicode support Expressions 6 6 Internal database 6 Sybase IQ ETL architecture When you install Sybase IQ ETL on Windows, you install Sybase IQ ETL Development and Sybase ETL Server.
Projects and jobs Sybase ETL Server The Sybase ETL Server provides the GRID engine service, which, by default, processes desktop requests locally. The GRID engine is designed for massively parallel transformation processing across operating system boundaries and machines. To make GRID engines available for parallel execution, you must register them in Engine Manager (see “Using multiple engines to reduce job execution time” on page 61 for more information).
CHAPTER 1 Sybase IQ ETL Component variables and ports All data within a project flow through component ports called IN-ports and OUT-ports. Each port owns the structure of the data flow. You can change the mapping of port structures by applying a mapping on the link that connects two components.
Understanding repositories • To generate SELECT statements inside Queries, Lookup Definitions, Preand Post Processing SQL, use the Query Designer. • To freely map attributes between data sources and data sinks, use the data mapping features of the links between the components. • To create temporary or persistent staging tables, use the built-in Create Table from port command of the respective component.
CHAPTER 1 Sybase IQ ETL Multiple repositories are accessible in parallel during a session. Projects can be copied and transferred between repositories, so you can separate your production repository from the repository used for development purposes. A repository usually belongs to a single client (such as a department or firm). It is possible to have more than one client using the same repository, providing a complete logical separation within a single physical repository.
Tools Tools Structural and catalog information from all connected data sources is accessible through Sybase IQ ETL Development tools, such as the Content Explorer and the Query Designer. You can browse through schema information, the data, or even create new database objects. The Runtime Manager lets you create job schedules. Unicode support All components are designed to process and support virtually any representation of data.
CHAPTER 1 Sybase IQ ETL • Implements most of SQL92. • A complete database is stored in a single disk file. • Database files can be freely shared between machines with different byte orders. • Supports databases up to 2 terabytes (241 bytes) in size. • Sizes of strings and BLOBs limited only by available memory. • Small code footprint: less than 30K lines of C code, less than 250KB code space (gcc on i486). • Faster than popular client/server database engines for most common operations.
Internal database 8 Sybase IQ ETL 4.
CH A PTE R 2 Sybase IQ ETL Development Desktop Topic Desktop layout Page 9 Using the Navigator Using the Properties section 10 14 Using the Design section Using the Component Store section 15 16 Customizing preferences 17 Desktop layout The Sybase IQ ETL Development desktop consists of the following major sections: • The Navigator section lets you select the repositories, projects or jobs that you want to work on.
Using the Navigator Figure 2-1: Sybase IQ ETL Development desktop Using the Navigator The following objects and functions are available from the Navigator: • Administer the repository • Navigate and browse the repositories • Administer projects and jobs • Administer user accounts You can access Navigator commands from the menu bar. You can also rightclick to open the pop-up menu on any desktop item in the Navigator. 10 Sybase IQ ETL 4.
CHAPTER 2 Sybase IQ ETL Development Desktop Administering the repository A Sybase IQ ETL repository is a collection of tables that save and maintain all data related to projects, jobs, and session parameters. Sybase IQ and Microsoft Access can be used to store an Sybase IQ ETL repository. Note Do not manually manipulate data in the repository tables. Sybase cannot guarantee the functionality of a repository after it has been manually manipulated.
Using the Navigator 2 Enter a client user name. 3 Enter a password. 4 Select the Register New check box. 5 If the client user is entitled to see all existing projects within the client, select the Show All Objects check box. 6 Click Logon and confirm the password.
CHAPTER 2 Sybase IQ ETL Development Desktop Administering user accounts With the built-in user account administration in Sybase IQ ETL Development, you can: • Create a user • Remove a user • Change password • Change visibility Only a registered user (client user) has access to a repository. You can register a client user in the Repository Logon dialog box or in the User Accounts dialog box.
Using the Properties section Using the Properties section The Properties section contains information and functions that let you: • Review and modify all property items of the selected component • Identify mandatory items • Add component variables • Allow dynamic evaluation of property items • Encrypt property items Whenever you select a component in the Design section, the property settings of the selected component appear in the Properties section.
CHAPTER 2 Sybase IQ ETL Development Desktop 2 Right-click in the Properties section to open a pop-up menu. 3 Select Edit and enter the new settings. Removing a component variable ❖ Allowing dynamic expressions To remove a component variable 1 Click the line containing the variable. 2 Right-click in the Properties section to open a pop-up menu. 3 Select Remove. Select the Evaluate option if you want to allow evaluation of dynamic, indirect expressions (SBN expressions).
Using the Component Store section • Simulate and run projects • Run jobs To create a project or job, you must add and connect components and then set the component properties. Adding components To add a component, select the component in the Component Store and drag it to the Design section. You can also right-click the component and select Add, or double-click the component.
CHAPTER 2 Sybase IQ ETL Development Desktop • Drag and drop the component to an empty spot or to an existing connection in the Design section. • Right-click a component and select Add. • Double click a component. • Right-click a component to connect with a new component, select Add Right Component or Add Left Component, and select a component to add it to.
Customizing preferences 3 4 5 18 • Create new project on startup — specify whether to automatically start a new project each time you start Sybase IQ ETL. This option is selected by default. • Create new GRID projects — Not available. • Create automatic link when components are added — specify whether to create a link to an existing component is automatically when dragging a new component onto an empty spot within the project Design section. This option is selected by default.
CHAPTER 2 6 7 Sybase IQ ETL Development Desktop • Grid Engine Server —specify the IP address of the primary GRID engine server. • Grid Engine Port — specify the port address of the primary GRID engine server. • Grid Default Port — specify the default port address of the primary GRID engine server. The default is 5124. • Grid Engine Ping Timeout (sec) — specify the amount of time (in seconds) allowed for accessing the GRID engine before restarting. The default is 60 seconds.
Customizing preferences 20 Sybase IQ ETL 4.
CH A PTE R Getting Started 3 Topic Starting Sybase IQ ETL Development Page 21 Creating your first project Simulating the project 22 25 Starting Sybase IQ ETL Development ❖ To start Sybase IQ ETL Development 1 Double-click the Sybase IQ ETL Development icon or select it from the Sybase product group on the Windows Start menu. By default, the “Welcome to Sybase IQ ETL Development” page appears. It provides information that explains Sybase IQ ETL Development projects and jobs.
Creating your first project 5 Select an existing project, or right-click on Projects to create a new project. Creating your first project This section describes how to create and simulate a sample project with sample components. It does not explain all of the components, nor does it explain their properties and features. For details about components, see Chapter 6, “Components.
CHAPTER 3 2 Getting Started Select ETLDEMO_US from the Host Name drop-down list. After you confirm the initial component settings, the settings appear in the Properties section. 3 To define what information should be retrieved from the data source, click Edit on the Query property. The Query window appears. 4 Enter a SQL Query or click Query Designer to generate the necessary SQL. The left section of the Query Designer window lets you navigate the table catalog of the connected database.
Creating your first project Your project should now consist of two components. The link between the components had been created automatically (provided the setting Create Automatic Link When Components Are Added has been activated in your Sybase IQ ETL Development preferences). If the line has not been automatically created, you can easily draw one by clicking on the output port and dragging it onto the input port of the Data Sink.
CHAPTER 3 Getting Started The Data Calculator window has a Tabular and Graph view: 3 • Use the Tabular view to enter transformation rules. • Use the Graph view to visually define the mapping sequence between the input port and the output port. Click the Graph tab. Two sections IN and OUT represent the current structure of the port attributes. You are prompted to assign a default mapping by order. 4 Click Yes. 5 Click the Tabular tab to return to tabular view.
Simulating the project 26 Sybase IQ ETL 4.
CH A PTE R Projects and Jobs 4 Topic Managing projects Page 27 Simulating and executing a project Managing jobs 29 36 Using templates to create projects and jobs 40 Managing projects Projects are the working units of Sybase IQ ETL. A project consists of components and links, which connect components through their ports. There are basic operations that involve projects (such as creating, deleting, renaming, saving) and there are complex operations like simulation.
Managing projects 2 Drag components from the Component Store onto the Design section as your project requires it. Executing a project To execute a project, right-click the project in the repository and select Execute from the pop-up menu. Modifying a project To modify a project, double click the project name in the Navigator section. The project opens and you can make changes. Unlocking a project If a project is locked by another user client, the project can generally be opened in read-only mode.
CHAPTER 4 Projects and Jobs Simulating and executing a project Simulating a project is a highly interactive process that lets you monitor and validate your transformation process step by step. In contrast to executing a project, you can view the data during a simulation at any stage of the transformation process. During the final steps of a simulation, data is written into the data sinks.
Simulating and executing a project • 2 All data for all static Lookup components are retrieved and cached. Any change of data in lookup tables that happens while the project is simulated is not reflected in the simulation process. Select a component and click Step to execute the component. Stepping a component means to execute or process a single component with the data that is currently available at its input ports.
CHAPTER 4 Projects and Jobs Viewing current mappings The Mapping Definition window shows the current mapping between attributes of the adjacent IN- and OUT-structures. To open the Mapping Definition window, right-click the connecting link and select Mappings from the pop-up menu. Selecting Display Structure and values from the list box in the toolbar displays the fields, as well as the current values. Note This view shows the current content of the port connecting to the link.
Simulating and executing a project Managing port attributes The Structure Viewer is available at the port of a component. You can add and delete port attributes, or modify the settings or existing attributes. To open the Structure Viewer, right-click the port and select Edit Structure from the pop-up menu. ❖ Modifying datatypes To add an attribute to the port structure 1 Click the line number you want the attribute to be inserted. The current and following attributes will be shifted downward.
CHAPTER 4 Projects and Jobs The number of records being processed within each single step is dependent on the current value of Read Block Size of the previous component with a Read Block Size property. Selecting a small number is useful while performing a simulation. A large number for Read Block Size can significantly enhance performance while a project is executed.
Simulating and executing a project To resume simulation from the selected component, select Step from the popup menu. To resume simulation from the current component, select Step from the toolbar. Note Selecting Step from the pop-up menu for a component that has not been processed yet forces all previous components to be stepped first. Forwarding and backward-forwarding components The visible flow of the simulation as indicated by the box is straight forward in many projects.
CHAPTER 4 Projects and Jobs Partial execution or initialization during simulation It is extremely time consuming to start the entire simulation after making modifications to a single component, especially when working with a large amount of input records in a project that consists of dozens of components. On the other hand, it can be frustrating to single step through a large project when you are only interested in simulating a component somewhere in the middle of a complex simulation flow.
Managing jobs If you have multiple data streams it is highly advisable to design a project for each data stream so that all components within a project are connected to each other. By following this design guideline, it is easy control the data streams by connecting the projects to form a job process flow. Managing jobs A job lets you easily set up the powerful control flows for one or multiple projects.
CHAPTER 4 Projects and Jobs A Start component is always followed by one or multiple Project components. Figure 4-3: A Job with multiple components Executing a job A job can be executed directly from the desktop or at specific time intervals as a scheduled task of the operating system Task Manager. To schedule a job, click Start Time in the Property section of the Job Start component. Or, you can select Runtime Manager from the Tools menu.
Managing jobs • Stop: The stop date and time. • Engine Name: The name of the executing engine. • Engine Host: The host of the executing engine. • Engine Port: The port of the executing engine. • Message: Error message. Cancelling job execution To cancel job execution, click Cancel Execution. The engines attempt to cancel running projects. Projects still waiting for execution are not started.
CHAPTER 4 Copying a job Projects and Jobs To copy a job to a specified destination, open it and select Save As from the File menu. Note You can copy a job into a different repository only when working with multiple repositories. Deleting a job ❖ To delete a job 1 Right-click the job in the Navigator. 2 Select Delete from the pop-up menu. The Confirm Job Delete dialog box appears. 3 Click Delete. By default, only the job is deleted.
Using templates to create projects and jobs Using templates to create projects and jobs Templates provide a way to automatically create projects and jobs for special purposes. Currently, only Migration templates are available. This section describes how to set up and use a template for migrating tables from one database to Sybase IQ. Building a migration template ❖ To build a migration template 1 Right-click the Templates entry in the Navigator section, and select New | Template from the pop-up menu.
CHAPTER 4 Projects and Jobs Stage properties — Enter the general part for the Load Stage properties of the DB Bulk Load IQ component into the Stage and Stage Server properties, that is the paths to the staging files ended by the path delimiter (for example C:\ETLStage\) or pipe:// for a named pipe. Refer to “DB Bulk Load Sybase IQ component” on page 119 for a detailed description of these properties. 5 Enter transfer settings. Additional properties for each table to be transferred are provided.
Using templates to create projects and jobs Select Truncate to remove all records from the destination table before loading. This option corresponds to the Truncate Table property of the target component. The failure of a Critical project will cause the job to stop execution and signal failure. This and the Ignore Errors options correspond to the properties of the Multi-Project job component. The Ignore Errors setting does not affect the projects generated through this template.
CHAPTER 4 Projects and Jobs To finish collecting data and perform the selected tasks, click Finish. Note Please ensure that you selected at least Save template or Build Transformations. Otherwise the collected data will be lost. Note Before you can execute the generated job, you must register engines or open the job and deactivate the Multi Engine Execution option (see“Using multiple engines to reduce job execution time” on page 61 for a detailed description).
Using templates to create projects and jobs Creating a data model from a template 44 To set up the destination data model according to the data model options stored with the template, select Create Data Model from the pop-up menu. Sybase IQ ETL 4.
CH A PTE R 5 Advanced Concepts and Tools Topic Content Explorer Page 51 Inspecting log file information Managing jobs and scheduled tasks 49 49 Customizing SQL and transformation rules Using multiple engines to reduce job execution time 51 61 Analyzing performance data 63 Content Explorer Content Explorer consists of a menu, a toolbar, a SQL structure area, a Navigation area, and the Design area. From the Content Explorer window, you can customize the size of the areas.
Content Explorer The examples in this chapter open the project Demo Getting Started from the Demo Repository. Opening Content Explorer To open Content Explorer, select Content Explorer from the Tools menu. The Choose Data Source dialog box appears and lists all components currently connected to data sources. The names in the list of currently connected databases is a combination of a user defined name and the generic name of the component type.
CHAPTER 5 Advanced Concepts and Tools Creating queries The Content Explorer allows you to create queries in a convenient graphical environment. Little or no SQL knowledge is required. Note You can only use the Content Explorer to generate ad hoc queries, which cannot be saved to a file or to the repository. However, if you want to save the generated SQL for other purposes, you can copy it from the Generated Query window that can be opened by selecting Generated Query from the View menu.
Content Explorer ❖ To modify the default setting of a join A join between two tables is indicated by a line that connects the joining fields. The line is labeled with a join operator. The default is Equi Join. 1 Right-click the line connecting the two joining fields. 2 Select the Modify command. 3 Choose a join type from the list: • = EQUI Join • += Left outer Join • =+ Right outer Join • +=+ Full outer Join The default setting of the join changes to the type you selected.
CHAPTER 5 Advanced Concepts and Tools Inspecting log file information The File Log Inspector window allows you to inspect log file information about job execution, fatal errors, and the system log. The log files are located in the \log subdirectory of the installation directory. The log files are: • execution.log — captures all information regarding job execution errors. • fatal.log — captures low-level information that is written when the system encounters serious unexpected behavior.
Managing jobs and scheduled tasks To open Runtime Manager, select Tools | Runtime Manager. ❖ To create a new schedule 1 Create a new task by clicking Create a New Schedule on the toolbar or select Actions | Create. The New Schedule window is displayed. 2 Select a Job in the New Schedule window. 3 Edit Name and Description. 4 Enter Username and Password for the Windows user account that will run the job. Note Because this is a separate task, Windows expects a valid username and password.
CHAPTER 5 Advanced Concepts and Tools Job execution state codes in the Task Scheduler are shown in the following table.
Customizing SQL and transformation rules Using expressions and procedures An expression is a combination of identifiers and operators that can calculate a single value. A simple expression can be a variable, a constant, an attribute, or a scalar function. Operators can be used to join two or more simple expressions into a complex expression.
CHAPTER 5 Port variables Advanced Concepts and Tools The values of the port structure are referenced as Port variables within a component. There are automatic Port variables for both IN-Port and OUT-Port. Port variables are valid within the component and they inherit the name and datatype of the port structure. The name of the variable is either prefixed with IN. for the IN-Ports or OUT. for the OUT-Ports. IN-Port variables are readonly, OUT-Port variables can be written.
Customizing SQL and transformation rules Using Square Bracket Notation Expressions and SQL statements can contain SBN expressions that are evaluated before the expression or SQL statement is executed by the Sybase ETL Server. An SBN expression is surrounded by square brackets [..]. The notation SBN expression is used as a synonym for an indirect expression.
CHAPTER 5 Advanced Concepts and Tools From the Query window you can: • Enter a query • Run a query • Save a query • Open the Query Designer • Look up the database schema Entering queries You can manually enter a SELECT statement into the Query field. You can use any valid SQL notation of the connected database to build the query. To open the Query Designer, click Query Designer. To look up the database schema, click Lookup Schema. To run the query, click Execute Query.
Customizing SQL and transformation rules SELECT * FROM CUSTOMERS WHERE CU_NO = '12345678' With SBN you can use a more flexible approach by assigning the constant value of CU_NO to a component variable. Assuming that value ‘12345678’ was assigned to CustNo, the SELECT statement with the dynamic expression would look like the following example: SELECT * FROM CUSTOMERS WHERE CU_NO = '[REF.CustNo]' You can use any of the Sybase IQ ETL functions inside the SBN.
CHAPTER 5 Advanced Concepts and Tools The following examples show pre-processing and post-processing SQL: delete from products; update customers set cu_desc = 'valid'; Using the JavaScript Procedure Editor and Debugger JavaScript is an object-oriented scripting language designed for embedding into other products and applications. The language is divided into the core JavaScript and client-side JavaScript.
Customizing SQL and transformation rules Starting the JavaScript Editor and Debugger Within the Data Calculator JavaScript component, click Edit to open the JavaScript Editor and Debugger. The JavaScript Editor contains the following areas: Edit and Debug mode • Toolbar — consists of option buttons that you select to perform various JavaScript Editor functions. Refer to the online help for details about the toolbar. • Object Navigator — consists of the Variables tab and the JavaScript tab.
CHAPTER 5 Advanced Concepts and Tools To validate JavaScript code, click Compile. The result of the validation is displayed in the Tasks tab of the Monitor area at the bottom area of the Procedure Editor. The Editor offers some efficient features to trace the execution of a script. You can step through a code line-by-line or step through from one Breakpoint to another. At any time, you can check the current value of a variable.
Customizing SQL and transformation rules 2 Click Go on the last breakpoint to execute the rest of the script. Inline inspection of variables You can perform an inline inspection of the current value of a variable while stepping through the code in Debug mode or after the code has been executed. Right-click the variable to open the pop-up menu, which displays the variable name and value.
CHAPTER 5 Advanced Concepts and Tools function IncA (a) { var b = 3; a = IncB(b) + a++; return a; function IncB(b) { b = b + 1; return b; } } Converting datatypes All variables in the Sybase IQ ETL are represented as strings. This may result in unexpected behavior when working with numeric values.
Using multiple engines to reduce job execution time Registering GRID engines • Install multiple GRID engines • Register your GRID engines • Prepare jobs for multi-engine execution Once you have GRID engines installed, you can register a subset or all of them for a special repository. When executing a multi-engine job from that repository all projects contained will be executed on those engines. To register GRID engines, select the Engine Manager option from the Tools menu.
CHAPTER 5 ❖ Advanced Concepts and Tools 2 Overwrite the current values with new values. 3 Click OK. To delete an engine registration • Select the engine from the Select Engine | Delete menu, or click Delete. Defining multi-engine jobs You can run a job on multiple engines using the parallel Grid architecture. A typical multi-engine job contains multiple projects with no or little dependencies between them. Thus, the projects can be executed on multiple engines at the same time.
Analyzing performance data Performance data model and content The performance data is stored in a single, de-normalized, repository table named TRON_PERFORMANCE. This section describes the information included. Events The performance log is based on events. For each event the starting time (in three variations: a full timestamp, a date, and a time) and the duration (in ms) is stored. The description of an event is made up by a class, a name and a text. Some events have a result (like succeeded or failed).
CHAPTER 5 Advanced Concepts and Tools General information Each execution of project or job is identified by a global unique ID. You will find the execution starting time in three variations: a full timestamp, a date, and a time. Additional information is provided about the account that initiated the execution and the repository the project or job is located in. Job execution information For a job you will find the ID, the version (modification date), and the name.
Analyzing performance data Project execution history Figure 5-1: Example of a project execution history report 66 Sybase IQ ETL 4.
CHAPTER 5 Advanced Concepts and Tools Project execution time and records moved Tabular Figure 5-2: Example of a tabular report capturing project execution time and records moved Graph Figure 5-3: Example of graph report capturing project execution time and records moved User’s Guide 67
Analyzing performance data 68 Sybase IQ ETL 4.
CH A PTE R 6 Components Topic Overview Page 69 Source components Transformation components 81 93 Using Lookup components Staging components 101 107 Processing components Destination components 109 110 Job components 121 Overview The Sybase IQ ETL components are used to create projects and jobs. (See Chapter 4, “Projects and Jobs.”) They are located in the Component Store.
Overview Transformation components, Lookup components, and Staging components have at least one IN-port and one OUT-port and apply specific transformations to the data in the transformation stream. Destination components (also called data sinks) write data to specific targets. This component type has one IN-port and no OUT-port. While different from a functional point of view, all components share common concepts.
CHAPTER 6 Components Setting up a component Each component is dedicated to a specific task and therefore incorporates taskspecific features. However, all components that connect to database objects follow the same procedure to set up the database connection parameters. This chapter summarizes common characteristics that are available for most components. When in doubt, refer to the detailed description of the specific component later in this chapter.
Overview • ODBC Note The ODBC driver must be installed on the same machine as Sybase IQ ETL Development desktop and a system data source name (DSN) must be defined for the target. If the component is to be sent to an ETL Server, then the ETL Server must also have access to the proper ODBC drivers and DSN. • 2 Oracle Click the Host Name list and select one of the available host names. If you are interfacing to a SQLite Persistent interface, you can enter a database file name.
CHAPTER 6 Components DB Option Default Description Show error location 1 1 = yes 0 = no 0 Database errors will include the position of the record within the result set, when Show error location = 1. Always use logon credentials. Always use logon credentials Extended connection option Extended connection option. Connect timeout 0 Stops trying to connect after Connect timeout seconds.
Overview 9 DB Option Default Description Truncate reject log 1 Write error code to reject log 1 Log will be truncated on database connect. A value of 0 will append data to an existing log file. The database error code for each record will be written to the log. Write error text to reject log 1 The database error text for each record will be written to the log. Write header to reject log 0 If set to 1, a column header will be written to the log.
CHAPTER 6 Components One or more SQL statements can be executed when all components finished processing the project. Note When using multiple statements, you can separate the statements by using a semicolon. Connecting to a SQLite database A SQLite database is represented as a single file with the extension .db. The database file can contain any number of tables. ❖ To connect to or to create a SQLite database 1 Select SQLite Persistent from the Interface drop-down list.
Overview You can use the Content Explorer from the Tools menu to manipulate or browse objects of the SQLite database connected to components in your project. Also, use client applications from the at www.sqlite.org to connect to SQLite database files. Note Before using external client applications to connect to your SQLite database files, become familiar with the locking strategy of SQLite. Providing descriptions for components You can assign a name and a description to a component.
CHAPTER 6 ❖ ❖ Components To edit a component variable 1 Right-click the component variable in the Property section to open the pop-up menu. 2 Select Edit. 3 Apply any modifications to your current settings. To remove a component variable 1 Right-click the Component variable in the Property section. 2 Select Remove. Evaluating SBN expressions Select the Evaluate command if you want to allow SBN expressions that are evaluated prior to using the property value.
Overview Modifying components To initialize a component, right-click it and select Initialize or select Initialize and Step from the pop-up menu. If you modify one of the existing property settings of a component during simulation re-initialize the component prior to applying the next step. Data blocks and visualization When working in simulation mode, the entire result set of the data source (as defined by a query in the source or staging component) can be divided into data blocks.
CHAPTER 6 Components When stepping the component repetitively, the same set of records at the INport are reprocessed in each step and forwarded to the output port without increasing the number of the record set at the OUT-port. For many components, the stepping can be done from inside the component window or by using the pop-up menu from the desktop.
Overview 3 ❖ ❖ Copying port structures from other ports Click Save to confirm. To delete an attribute from the port 1 Click the line containing the attribute. 2 Click Delete. 3 Click Save to confirm. To modify an attribute 1 Click on the line containing the attribute. 2 Modify the attribute settings. 3 Click Save to confirm. A port structure can be assigned to a port based on any other available port in the current project: • Click the port you want to assign a new structure to.
CHAPTER 6 Components In Display structure mode, all attributes of the connected port and their current mappings are shown. The Display structure and values mode displays the value of the current record. ❖ To change a mapping 1 Click on a connecting line. 2 Remap it to a new port attribute by dragging the line end to a new connection point. (See “Viewing current mappings” on page 31.
Source components SELECT A.CU_NO, A.CU_NAME, A.CU_CITY, B.SA_ORDERDATE, B.SA_TOTAL FROM CUSTOMERS A, SALES B WHERE A.CU_NO = B.CU_ID ORDER BY A.CU_NO For database schema lookups, click Database Lookup. Note The SELECT statement must be compliant with the SQL language supported by the connected database system. Be especially aware of differences regarding quote characters, date functions, and expressions.
CHAPTER 6 • Database • Schema • Database options Components Impact on simulation sequence Read Block Size value impacts the number of records loaded in a single simulation step. Projects in DemoRepository and Help Flash movie: • Demo Transfer US Products • Demo Transfer German Sales DB Data Provider Index Load This component allows you to perform incremental data loads. Data records already extracted by previous executions of the project will be skipped.
Source components 3 Select an attribute with values that increase whenever data is changed or added to the source, such as an auto incremental ID or a modification date. It is not required to have this attribute indexed on the database schema level; however, for performance reasons Sybase recommends that you create an index. 4 Enter a Query for the incremental load. The selection criteria in the WHERE clause needs to be qualified using the predefined variable LoadIndex.
CHAPTER 6 • Components Post-processing SQL — One or more SQL statements to be executed after all components finished execution. See “Entering database connection parameters” on page 71 for more information about the optional properties and for more information about the following properties: • Database • Schema • Standardize Data Format • Database Options Impact on simulation sequence Read Block Size value impacts the number of records loaded in a single simulation step.
Source components Properties Character Encoding • Select a character set from the list. • Press Enter to confirm. Support Unicode Activate this option when the text file contains unicode data. Type • Specify the structure type of the input file. • Press Enter to confirm. Delimited The fields in the file are separated by a specific character or string. Fixed (fixed Line) The fields and lines in the file are of fixed length. There is no line delimiter.
CHAPTER 6 • Components Press Enter to confirm. Read column names from row • Enter the number of the line containing the column headings. • Press Enter to confirm. • If any rows are to be skipped on reading (for instance lines containing headings), provide the number of lines to be skipped. • Press Enter to confirm. • If field values in the source text file are quoted, provide the respective character. • Press Enter to confirm. • Enter the character to replace a Null Byte (0x00 ASCII).
Source components Working with fixed-length file type When a file has been qualified as fixed-length, the Add Column option in the Output Port Content area is activated. For each column to be added, click Add Column and edit Column Name, From Position and To Position. Or, you can select the range of the column to be specified in the upper File Content section. Right-click and select Assign Position to Column. Then, enter the Column Name.
CHAPTER 6 Components You do not need to understand XML in detail to use this component. It is useful for data-centric XML documents (such as, sales order, stock quotes, scientific data) that are characterized by a regular hierarchical structure. Use this component to represent the data from the XML document in a relational table structure for further data transformation. ❖ To enter required properties 1 Select the XML Source file. This can be an HTTP, FTP, URL, or a file name.
Source components DB Schema Select the file containing the database schema setup (create tables) script. Use this option to enforce a fixed data model. DB Schema Options Customize the settings for tables and attributes being generated from the XML structure, such as the prefixes for table and attribute names. DB Options These are advanced options to configure the underlying internal database. Read Block Size This option defines how many records are read from the query result set in one step.
CHAPTER 6 Components 435673 Notebook 235 Notebook 1455 XML Port Manager Click the XML button of the Data Output property to open the XML Port Manager. In the upper area of the window, you can view the source XML document. The left section of the lower window contains two tabs: • The Data Model tab displays the generated relational schema.
Source components Root Level Elements Level 1 Elements Level 2 TAB_PR_ID ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_ID TAB_dataroot ATT_ROW_ID ATT_FK_generated ATT_xmlns_od ATT_xsi_no TAB_PRODUCTS ATT_ROW_ID ATT_FK_dataroot TAB_PR_NAME ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_NAME TAB_PR_GROUP1 ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_GROUP1 TAB_PR_PRICE ATT_ROW_ID ATT_FK_PRODUCTS ATT_PR_PRICE The tables are linked through foreign keys. Table TAB_PRODUCTS is linked to TAB_dataroot via attribute ATT_FK_dataroot.
CHAPTER 6 Components Creating a SELECT statement You can enter a SELECT statement for the port straight into the port field, or you can open the Query Designer by clicking Query Designer. Note To control the automatic join generation (enable/disable), go the File | Preferences menu. Adding and removing ports Right-click on the port section and select Add port or Remove port. An Info Port can be added to forward the XML document to the next component.
Transformation components ❖ To create a mapping 1 Select the Graph tab of the component window. 2 Map the IN-Port and Out-Port structures by either using pre-defined mapping sequences or by connecting the IN-Port and OUT-Port attributes individually. The two most common mapping methods are available through the Create mapping by order and Create mapping by name buttons. After selecting a mapping sequence, the port color changes to green.
CHAPTER 6 ❖ Components To simulate a single attribute 1 Change the value of any attribute in the Current Input Record. 2 Press Enter. The result of transformation based on the new field value is displayed in the area to the right. Transformation rules and current output record In the Transformation Rule column, you can add, modify, or delete transformation rules. You can also edit single-line functions by changing the current attribute input field. Adding the function uUpper to the attribute IN.
Transformation components 4 Right-click and select OUT or TMP. 5 Select one of the attributes from the menu. The added attribute can now be used for further assignments or calculations. To delete transformation rules, right-click the desired line in the Transformation Rule or Current Output Port column and select Remove. To change the order of the transformation rules, right-click the desired line in the Transformation Rule or Current Output Port column and select UP or DOWN.
CHAPTER 6 Components You can freely change the mapping by re-connecting the lines to the connection points. Note After you apply a transformation rule to an IN.attribute, the mapping line between the IN.attribute and the OUT.attribute will no longer be displayed. Simulating the Data Calculator The Data Calculator is designed to visualize the changes applied to the data as it moves through the transformation rules.
Transformation components Using lookups in the Data Calculator The Data Calculator can perform lookups on attribute level. The lookup data has to be provided at special lookup ports. Adding Lookup Ports To add a lookup port to a Data Calculator component, connect the output port of the data providing component with the Data Calculator component. Or, you can select Add Input Port from the Data Calculator component menu and connect the output port of the data providing component with the new port.
CHAPTER 6 Components Building Lookup rules To set up Lookup rules open the Tabular Tab of the Data Calculator window. If Lookup ports are available an additional Lookup column is displayed. You must provide the following information for each lookup rule: • Key Expression — This is the value to search for in the first column of the lookup list. You enter the key expression (for example IN.PR_ID) as the Transformation Rule.
Transformation components ❖ To set up required properties 1 Add the component to the project and connect the ports of the component to adjacent components. 2 Open the Mapping window by clicking Mapping in the Properties section. 3 Enter at least one mapping. The Character Mapper component is designed to visualize the result of a character mapping immediately after the mapping has been entered. To switch of this kind of automatic synchronization, click Auto Synchronization.
CHAPTER 6 Components From To Description <0x20A4> (Unicode Decimal) Replace the Lira currency symbol with Euro currency symbol (€). (Unicode Hexadecimal) Replace the Lira currency symbol with Euro currency symbol (€). There is no impact on the simulation sequence. Using Lookup components In general, a Lookup operation looks up a value corresponding to a key in a Lookup table containing a list of (key, value) pairs.
Using Lookup components Required properties Key Attribute Select a Key Attribute from the list of IN-Port attributes. This attribute corresponds to the first column of the lookup table. Value Attribute Select the attribute to receive the value returned by the lookup from the Value attribute list. The lookup value returned will overwrite any existing value.
CHAPTER 6 Components Use Key Value If Use Key Value is activated, the key value will be assigned to the value attribute instead of the default, if the lookup does not find a match. Lookup Empty/Null Keys If activated, the lookup will also be performed for empty or NULL key values; otherwise, the selected default method applies. Lookup Size Enter the estimated number of lookup records to optimize memory allocation and lookup performance.
Using Lookup components • Schema • Standardize Data Format • Database Options There is no impact on the simulation sequence. View Projects in DemoRepository and Help Flash movie Demo DB Lookup. DB Lookup Dynamic component This component performs a dynamic lookup by using a query referencing the key value in the WHERE clause. Unlike the DB Lookup component, the DB Lookup Dynamic performs one SQL lookup for each record that passes the component and caches no lookup information.
CHAPTER 6 Components Enter the Connection Parameters as described in “Entering database connection parameters” on page 71. Query To open the Query window, click Query and enter the query for the lookup. The Lookup is made of a SELECT query returning a single value that corresponds with the Value Attribute. The query required in the DB Lookup Dynamic component is slightly different from the one used in the DB Lookup component.
Using Lookup components For example, assume you want to replace the product number used for German products by the product number used in the U.S. The German products are in the table PRODUKTE(PR_NUMMER, PR_NAME, PR_PREIS). Your IN-Port at DB LOOKUP component therefore contains those three attributes. The table to lookup the US product number is table LOOKUP_PRODUCTS(SOURCE, DESTINATION). The SOURCE column contains the German product numbers and the DESTINATION column contains the U.S. product number.
CHAPTER 6 Components Staging components DB Staging component The DB Staging component loads the incoming data streams into a staging area. The data is buffered until the incoming data stream has finished, Then an outgoing data stream, which is the result set of a given SELECT statement, is created. The staging tables can be created based on the output port structure of the preceding component.
Staging components 5 Right-click the component and select either Create Staging Table from Input or Create Staging Table from Port. The commands let you create the staging tables optionally based on the IN-port structure of the component or the structure of any other port within the project. However, you can also create the staging tables manually by using third party tools. Staging options are defined by using the Stage Options window.
CHAPTER 6 Components The DB Staging component impacts the flow of the simulation by first retrieving all data from the original data sources, then they act as a new data source for subsequent components. Both components allow overwriting of Read Block Size value of the original source components. View Projects in DemoRepository and Help Flash movies Demo DB Staging and Demo DB Staging Double.
Destination components Customizing port conditions A condition can be assigned to each port. A condition consists of one ore more expressions. Multiple expressions are concatenated by operators. The result of a condition is either TRUE (1) or FALSE (0). A condition consists of functions and logical operators. ❖ To modify a port condition 1 Click the port. 2 Click the Edit calculation button to open the Condition window. 3 Enter the condition in the right section of the window.
CHAPTER 6 Components Use this component if you want to add all records from the IN-port of the component to a database table. ❖ To use DB Data Sink Insert 1 Add the component to the project and connect the ports to adjacent components. 2 Enter the Connection Parameters as described in “Entering database connection parameters” on page 71. 3 Create a destination table. If the destination table you are going to use already exists, continue with step 5.
Destination components Pre-processing SQL This property provides one or more SQL statements to be executed during initialization of the component. Post-processing SQL This property provides one or more SQL statements to be executed when all components finished execution. Opening Attribute Quote The open quote (“x) character begins attribute names in SQL statements. Closing Attribute Quote The closing quote (x”) ends the attribute names in SQL statements.
CHAPTER 6 Components DB Data Sink Update component This component updates records in a destination table based on a selected key. All records matching that key will be updated or overwritten. If no matching record exists, no error messages appear. Use this component to correct or overwrite records of an existing table but to not insert new records.
Destination components Optional properties Update options Click Update Options to open the Update Options window. By default all attributes (key attributes are not listed) are selected. Deselect those attributes you want to exclude from the update. In the SQL UPDATE SET clause column, you can overwrite the value of the incoming attribute with a new one. The new value can be a constant or an expression.
CHAPTER 6 Components DB Data Sink Delete component This component removes records from the destination table that match the incoming values of a selected key. There will be no error message if no matching record exists. Use the component to clean your table from records no longer required. ❖ To use DB Datasink Delete 1 Add the component to the project and connect the ports to adjacent components. 2 Enter the Connection Parameters as described in “Entering database connection parameters” on page 71.
Destination components Post-processing SQL This property provides one or more SQL statements to be executed when all components finished execution. Opening Attribute Quote The open quote (“x) character begins the attribute names in SQL statements. Closing Attribute Quote The close quote (x”) character ends the attribute names in SQL statements.
CHAPTER 6 Components 4 Click Save to exit the component window. 5 Connect the component with the OUT-Port of an adjacent component. 6 Click Open to open the Text Data Sink component window and verify the result. You may add or remove columns and reassign the port structure. Exporting and importing File Definitions files Use the Export command in the Properties menu to save your current settings to a file definition file.
Destination components Properties Character Encoding Select a character set from the drop-down list and press Enter to confirm. Type Specify the type of the input file and press Enter to confirm. Delimited The fields in the file will be separated by a specific character or string. Fixed (Fixed Line) The fields and lines in the file are of fixed length. There is no line delimiter. Fixed (Variable Line) The fields in the file are of fixed length.
CHAPTER 6 Components Append Activate this option to append the incoming data to an existing file. Header Enter text for a header to be written to the file before writing out the incoming data. Expressions are allowed in Square Bracket Notation. There is no impact on simulation sequence. View Projects in DemoDatastore and Help Flash movie Demo Text Data Sink Delimited/Fixed.
Destination components Optional properties Truncate Activate this option to remove all records from the destination index set when initializing the transformation process. Load Script If the Load Script (SQL command performing the bulk load) property is empty, it is generated on execution. To generate the script while designing the project, use the Generate Load Script option of the component pop-up menu. To customize the script, click the property option and modify the script.
CHAPTER 6 • Components Database Options There is no impact on the simulation sequence. There are no Projects in DemoRepository or Help Flash movies. Job components Start component This component is the start component within any job. Use this component as a starting component for a job. Add the component to the Design section after you have created a new job. Optionally, you can enter the properties to schedule the job.
Job components Optional properties Continue on DB Write Errors If you activate this option, project execution will continue even if an error occurs on loading data into a database via a DB Data Sink component. If errors have been ignored due to this option the project will be stated as 'failed'. Combined with the Reject Log (see “Entering database connection parameters” on page 71) this option lets you “post-process” rejected records.
CHAPTER 6 Components Multi-Project component This component is the visual representation of a project group within the job. It combines properties of the Project and Synchronizer components. Use this component when your job consists of a large amount of independent projects, in other words, projects that can be executed in any order, even in parallel when used in multi-engine jobs. ❖ To use this component 1 Add the component to the job and connect it with its adjacent component.
Job components Finish component This component visually represents the end of a successful job execution. Use it to mark the successful end of a job. Connect it with the previous component, which is either a Synchronize, Project or a Multi-Project component. View the following jobs in DemoDatastore: • Demo Transfer all German Data • Demo Transfer all US Data • Demo Transfer US Sales on an incremental basis Error component This component visually represents the end of a failed job execution.
A P PE N DI X A Function Reference This appendix provides a reference for the Sybase IQ ETL functions. Note Even if your original data is not coded in UNICODE, and you have used one of the "u"-functions, the returned data has been converted to UNICODE and needs to be handled accordingly.
uAvg Aggregation functions uAvg Description Calculates and returns the average value of all input values. Syntax real uAvg(value, ...); Parameters numeric value A list of numeric arguments to calculate the average value over. Examples To calculate the average value: uAvg(1,2,3,4,5) // returns 3 uMax Description Returns the highest value from a set of values. This function compares the values depending on their datatype. Syntax uMax(value, ...
APPENDIX A Parameters numeric value A list of numeric arguments. Examples To find the highest value from a set of values: Function Reference uMin(1, 6, 4, -6) // returns -6 uMin("b", "A", "a") // returns "A" uMin("2004-05_02", "2006-12-12", "1999-05-30") // returns "1999-05-30" Bit functions uBitAnd Description Calculates the bitwise AND over all parameters given and returns the result. Syntax number uBitAnd(value, ...) Parameters numeric value A list of numeric arguments.
uBitXOr uBitXOr Description Calculates the bitwise Exclusive OR over all parameters given and returns the result. Syntax number uBitXOr(value, ...) Parameters numeric value A list of numeric arguments. Examples uBitXOr(10, 3) // returns "9" uBitNot Description Calculates the bitwise NOT over all parameters given and returns the result. Syntax number uBitNot(value, ...); Parameters numeric value A list of numeric arguments.
APPENDIX A Function Reference uIsAscending("2004-03-03", "2004-03-05", "2004-03-07") // returns 1 uIsAscending("2004-03-03", "2004-03-07", "2004-03-05") // returns 0 ulsBoolean Description Returns true if the parameter is either “1,” “true,” or “yes.” Syntax number uIsBoolean(params, ...) Parameters params A list of expressions or values of any datatype.
ulsDescending string format(optional) The format of the input date. Note Omitting the format parameter can slow down the function execution time. Examples To check for a valid date value: uIsDate(“2004-02-29”) // returns 1 uIsDate(“2003-02-29”) // returns 0, because 2003 was not a leap year ulsDescending Description Returns 1 if every parameter is equal to or lower than its predecessor. Syntax number uIsDescending(params, ...) Parameters params A list of expressions or values of any datatype.
APPENDIX A Parameters Examples Function Reference param An expression or value to investigate. uIsInteger (“1”) // returns 1 uIsInteger (“2.34”) // returns 0 uIsInteger (“ABC”) // returns 0 ulsFloat Description Returns 1 if the parameter can be interpreted as a floating point value. Syntax number ulsFloat (params) Parameters params An expression or value to investigate. Examples uIsFloat(“1”) //returns 1 uIsFloat (“2.
uNot Syntax number uIsNumber (params) Parameters params An expression or value to investigate. Examples To check for a numeric value: uIsNumber("1") // returns 1 uIsNumber("2.34") // returns 1 uIsNumber("ABC") // returns 0 uNot Description Calculates the logical not from the input expression. This function is only used in conjunction with the uIs-Functions, because the Boolean values returned are not true and false, but are 0 and 1.
APPENDIX A Function Reference uBase64Encode Description Decodes a string into a Base64 representation. Syntax string uBase64Encode(input) Parameters string input The string to decode.
uConvertDate Table 6-1: Date Conversion identifiers Identifier Y Description Year 2-digits (06) y C Year 4-digits (2006) Century (20) M m Month (03) Month (JUN) D H Day (12) Hour (00..23) h N Hour (01..12) Minutes S s Seconds Hundreths of seconds t A Thousandth of seconds AM/PM d D Day of week (5) Day of week (Friday) E G Day of year (001..366) Week of year (01..52) F Week of month (1..
APPENDIX A Function Reference uFromHex Description Converts a string of hexadecimal numbers into an integer. Syntax integer uFromHex (input) Parameters string input The string to convert. Examples Convert a hexadecimal string uFromHex("A3F") // returns 2623 uFromHex("B") // returns 11 uToHex Description Converts an integer value into a hexadecimal string. Syntax string uToHex (input) Parameters integer input The integer to convert.
uHexEncode uHexEncode Description Encodes every character of a string into its hexadecimal notation Syntax string uHexEncode(input) Parameters string input The string to encode. Examples To convert a string to hex values: uHexEncode("170") // returns "3313730" uHexEncode(170) // returns "3313730" uToUnicode Description Converts a string into Unicode. Syntax string uToUnicode (input , [codepage] Parameters string input The input string.
APPENDIX A Function Reference uURIEncode Description Returns a new version of a complete URI, replacing each instance of certain characters with escape sequences representing the UTF-8 encoding of the characters. Syntax string uURIEncode(uri) Parameters string uri The URI to encode. Examples To encode a URI: uURIEncode("www.sybase.com/filename with spaces.txt") // returns "www.sybase.com/filename%20with%20spaces.
uURIEncode If no date is given, the time string now is assumed and the date is set to the current date and time. uDate() // returns something like "2006-03-01" uDate() is equivalent to uDate("now") Getting a special date IN.Date = uDate("2004-01-04 14:26:33") // returns the date part "2004-01-04" Modifiers The time string can be followed by zero or modifiers that alter the date or alter the interpretation of the date.
APPENDIX A Function Reference The “unixepoch” modifier (12) works only if it immediately follows a time string in the DDDD.DDDDD format. This modifier causes the DDDD.DDDDD to be interpreted not as a Julian day number as it normally would be, but as the number of seconds since 1970. This modifier allows unixbased times to be converted to Julian day numbers easily. The “localtime” modifier (13) adjusts the previous time string so that it displays the correct local time. “utc” undoes this.
uDate • The localtime() C function normally only works for years between 1970 and 2037. For dates outside this range, you can attempt to map the year into an equivalent year within this range, do the calculation, then map the year back. • Date computations do not give correct results for dates before Julian day number 0 (-4713-11-24 12:00:00). • All internal computations assume the Gregorian calendar system.
APPENDIX A Function Reference uDateTime Description Returns year, month, and day from a date in the format YYYY-MM-DD HH:MM:SS. Note Please refer to the “Working with Dates and Times” section for detailed information about the possible modifier arguments. Syntax string uDateTime([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier. Examples To get the datetime part out of a timestamp.
uDayOfYear uDayOfYear Description Returns the number of days since the beginning of the year. Syntax string uDayOfYear([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier. Examples To get the day number out of a timestamp: uDayOfYear("now") // returns how many day have already passed this year uDayOfYear("1969-03-13 10:22:23.231") // returns "72" uHour Description Returns the hour of the date specified.
APPENDIX A uHour("now") Function Reference // returns current quarter uHour("1969-03-13 10:22:23.231") // returns "10" uIsoWeek Description Returns the week number as defined by ISO 8601.
uJuliandate uJuliandate Description Returns the number of days since noon in Greenwich on November 24, 4714 B.C. in the format DDDD.DDDD. For date and time calculation, the juliandate function is the best choice. Syntax string uJuliandate([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier. Examples To convert a date into a numerical value for calculation. uJuliandate(‘now’) // returns current date in the form "DDDD.
APPENDIX A Function Reference uMonth Description Returns the month of the date specified. Syntax string uMonth([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier. Examples To get the month from a date: uMonth("now") // returns current month uMonth("1969-03-13 10:22:23.231") // returns "03" uMonthName Description Returns the name of month of the date specified in the current locale language.
uSeconds Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier. Examples To get the short-form name of month from a date: uMonthNameShort("now") month. // returns current name of To set the locale to English: uSetLocale("English") uMonthNameShort("1969-03-13 10:22:23.231") "Mar" // returns To set the locale to German: uSetLocale("German") uMonthNameShort("1969-03-13 10:22:23.
APPENDIX A Examples Function Reference uTimeDiffMs("18:34:20”, “18:34:21”) // returns 1000 uTimeDiffMs("18:34:20”, “18:34:21.200”) // returns 1200 uWeek Description Returns the week of the date specified. Note Please refer to the “Working with Dates and Times” section for detailed information about the possible modifier arguments. Syntax string uWeek([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier.
uWeekdayName uWeekdayName Description Returns the week name of the date specified in the current locale language. Note Refer to the “Working with Dates and Times” section for detailed information about the possible modifier arguments. Syntax string uWeekdayName([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier.
APPENDIX A Function Reference To set the locale to English: uSetLocale("English") uWeekdayNameShort("1969-03-13 10:22:23.231") returns "Thu" // To set the locale to German: uSetLocale("German") uWeekdayNameShort("1969-03-13 10:22:23.231") returns "Don" // uYear Description Returns the year of the date specified. Syntax string uYear([modifiers]) Parameters string modifiers (optional) String specifying a date or date calculation. The default is the now modifier.
uErrortext uErrortext Description Returns the last error message. The error text will be internally cleared, so it can only be read once. Syntax string uErrortext() Examples To retrieve the last errortext: uErrortext() // returns last error text uInfo Description Writes an informal message into the log file. The function returns the message written. Syntax string uInfo(infotext) Parameters string infotext Text to write to log file.
APPENDIX A Function Reference uTrace Description Writes a trace message into the log. The function returns the message written. Syntax string uTrace(tracetext) Parameters string tracetext Text to write to log file. Examples To log a trace message: uTrace("CUSTOMER_NAME = " + CUSTOMER_NAME) uTracelevel Description Sets the detail level of trace messages in the log. The range of tracelevel is from 0 (no trace) to 5 (very verbose). Note Heavy logging dramatically slows down performance.
uFileInfo File functions uFileInfo Description Returns information about a file. When uFileInfo is set to EXISTS the function returns the whole path to the file if it exists or an empty string if it does not exist. uFileInfo is set to SIZE the size of the file is returned or an empty string if the file does not exist. Note Be aware that you have to double the backslashes in JavaScript environments because the backslash is used as escape sequence.
APPENDIX A Function Reference integer bytes (optional) The number of bytes to read. The default is 0, which means the whole file. integer offset (optional) The number of bytes to skip from the beginning of the file. The default is 0. Examples Example 1 To access local files: uFileRead("c:\myFile.txt”) uFileRead(“/home/testuser/myfile.txt”) uFileRead(“file:///c:/myFile.txt”) Example 2 To read files from a Windows share: uFileRead(“\\fileserver\freeShare\testfile.
uFormatDate Examples To write data to a file via CIFS: uFileWrite("hello", "//myServer/myShare/data/test.txt") Formatting functions uFormatDate Description The function uFormatDate returns a user-defined string with date information. Special escape sequences in the format string will be replaced by the referring date part. The following table shows the format identifiers. 154 Sybase IQ ETL 4.
APPENDIX A Function Reference Table 6-2: Format identifiers Identifier %A Date part Weekday name %a %B Weekday name short Month name %b %d Month name short Day of month %f %H Fractional seconds SS.
uGlob Fuzzy search functions uGlob Description Compares values case sensitive and similar to uLike but uses the UNIX file globbing syntax for its wildcard characters. Syntax bool uGlob(pattern, text) Parameters string pattern A string describing a match pattern. string text A string to investigate. Examples To compare values using UNIX file-globbing syntax: uGlob("Mr. *", "Mr. Smith") // returns 1, indicating a match uGlob("Mr. *", "Mrs.
APPENDIX A Parameters Function Reference string pattern A string describing a match pattern. string text A string to investigate. Examples To compare values using pattern-matching: uLike("% happy %", "A happy man.") // returns 1 uLike("% happy %", "A sad man.") // returns 0 uMatches Description Returns true if a given string matches a regular expression. Syntax number uMatches(input, regexpr) Parameters string text A string to investigate. string regexpr Regular expression specification.
uFirstDifferent string values List of values. Examples Example 1 IF construct: uChoice(0, "A", "B") // returns "A" uChoice(1, “A”, “B”) // returns “B” Example 2 CASE constructs: uChoice(2, "n.a.", "Jan", "Feb", "Mar") //returns "Feb" Example 3 Simulate a lookup function where you want to replace a color ID with a corresponding color name: uChoice(IN.Color, “n.a.”, “Red”, “Blue”, “Green”) uFirstDifferent Description Returns the first parameter value that is different from the first parameter.
APPENDIX A Function Reference uElements Description Returns the number of elements in a delimited string. If the second parameter is omitted, a space (ASCII 32) will be taken as a delimiter. Syntax string uElements(text [, delimiter]) Parameters string text A string to investigate. string delimiter(optional) The delimiter to be used. The default delimiter is a space character. Examples To count tokens in a delimited string: uElements("James T.
uCommandLine Miscellaneous functions uCommandLine Description Returns the command line string of the current process. Syntax string uCommandLine() Parameters None The name of the environment variable to read. Examples uGetEnv(“LOAD_MAX_VALUE”) uGetEnv Description Returns the value of an environment variable. Syntax string uGetEnv(variable) Parameters string variable The name of the string variable. Examples uCommandLine() // returns something like "D:\Sybase\IQETL\win32\Engine.
APPENDIX A Examples Function Reference To create a unique identifier: uGuid() // returns for example A8A10D9F-963F-4914-8D6FC8527A50EF2A uMD5 Description Allows you to generate a checksum with a fixed length of 32 characters from a given string. Syntax string uMD5(text) Parameters string text The text to build a checksum on. Examples uMD5("Austin Powers") // returns "C679A893E3DA2CC0741AC7F527B1D4EB" uScriptLoad Description Loads and evaluates JavaScript and returns the result.
uSet Locale string value The value to set Examples uSetEnv(“LOAD_MAX_VALUE”, IN.Date) uSet Locale Description Changes the current date and time settings to a different language. Syntax string uSetLocale([language] [, country] [, codepage]) Parameters string language The name of the language. string country The string for the country. string codepage The codepage string.
APPENDIX A Primary Language Czech Danish Dutch User’s Guide Sub-language Czech Danish Dutch (Belgian) Function Reference Language String “csy” or “czech” “dan”or “danish” “belgian”, “dutch-belgian”, or “nlb” “dutch” or “nld” “australian”, “ena”, or “englishaus” Dutch English Dutch (default) English (Australian) English English (Canadian) English English English (default) “english” English (New Zealand) “english-nz” or “enz” English English Engish (UK) English (USA) Finnish Finnish “eng”, “en
uSet Locale Primary Language Sub-language Language String Portuguese Portuguese (default) “portuguese” or “ptg” Russian Slovak Russian (default) Slovak “rus” or “russian” “sky” or “slovak” Spanish Spanish Spanish (default) Spanish (Mexican) “esp” or “spanish” “esm” or “spanish-mexican” Spanish Spanish (modern) “esn” or “spanish-modern” Swedish Turkish Swedish Turkish “sve” or “swedish” “trk” or “turkish” Country/Region Strings Following is a list of country/regions strings recognized by
APPENDIX A Function Reference Country/Region Country/Region String Netherlands New Zealand “nld”, “holland” or “netherlands” “newzealand”, “new-zealand”, “nz”, or “nzl” Norway People’s Republic of China Poland “nor” or “norway” “china”, “chn”, “pr china”, or “pr-china” Portugal Russia “prt” or “portugal” “rus” or “russia” Singapore Slovak Republic “sgp” or “singapore” “svk” or “slovak” Spain Sweden “esp” or “spain” “swe” or “sweden” Switzerland Taiwan “che” or “switzerland” “taiwan” or “twn”
uSystemFolder Parameters Examples uSystemFolder(“APP LOG”) // returns the path to the log directory You can specify the folders listed in the following table. Usage 166 string foldertype (optional) Specifies the folder to return. The default is “System”. Group Application Name APP_MAIN Description The base application path. A typical path is “c:\Program Files\Sybase\IQETL41”. Shared library directory, typically in the “lib” folder of the application’s directory.
APPENDIX A Function Reference Group Name Description Windows CDBURN_AREA The file system directory acting as a staging area for files waiting to be written to CD. A typical path is “C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\CD Burning”. Windows COMMON_ADMINTOOLS The file system directory containing administrative tools for all users of the computer.
uSystemFolder 168 Group Name Description Windows COMMON_STARTMENU The file system directory that contains the programs and folders that appear on the Start menu for all users. A typical path is “C:\Documents and Settings\All Users\Start Menu”. Valid only for Windows NT systems. Windows COMMON-STARTUP The file system directory that contains the programs that appear in the Startup folder for all users. A typical path is “C:\Documents and Settings\All Users\Start Menu\Programs\Startup”.
APPENDIX A Function Reference Group Name Description Windows HISTORY The file system directory that serves as a common repository for Internet history items. Windows INTERNET A virtual folder for Internet Explorer (icon on desktop). Windows INTERNET_CACHE Windows LOCAL_APPDATA Windows MYDOCUMENTS The file system directory that serves as a common repository for temporary Internet files. A typical path is “C:\Documents and Settings\username\Local Settings\Temporary Internet Files”.
uSystemFolder 170 Group Name Description Windows PERSONAL Windows PRINTERS The virtual folder representing the My Documents desktop item. This is equivalent to MYDOCUMENTS. The virtual folder containing installed printers. Windows PRINTHOOD Windows PROFILE Windows PROGRAM_FILES Windows PROGRAM_FILES_COMMON Windows PROGRAMS Windows RECENT Windows SENDTO The file system directory that contains Send To menu items. A typical path is “C:\Documents and Settings\username\SendTo”.
APPENDIX A Function Reference Group Name Description Windows STARTUP Windows SYSTEM Windows TEMPLATES The file system directory that corresponds to the user's Startup program group. The system starts these programs whenever any user logs onto Windows NT or starts Windows 95. A typical path is “C:\Documents and Settings\username\Start Menu\Programs\Startup”. The Windows System folder. A typical path is “C:\Windows\System32”.
uAbs Numeric functions uAbs Description Returns the magnitude of a real number, ignoring its positive or negative sign. Note This function can only calculate with numbers. In all other cases, it returns zero. Syntax number uAbs(value) Parameters number value A number to calculate on. Examples To calculate the absolute value: uAbs(1522) // returns 1522 uAbs(‘-123.45’) // returns 123.45 uAbs(‘123ABC’) // returns 0 uCeil Description Returns the least integer greater than or equal to argument.
APPENDIX A Function Reference uDiv Description Returns the division integer. Syntax number uDiv(value) Parameters number value A number to calculate on. Examples To calculate the integer: uDiv(10, 3) // returns 3 uExp Description Returns the exponential, base e. Syntax number uExp(value) Parameters number value A number to calculate on. Examples To calculate an exponential: uExp(1) ;; returns "2.
uLn uLn Description Returns the natural logarithm (base E) of a number. Syntax number uLn(input); Parameters number value A number to calculate on. Examples uLn(2.718281828) // returns 0.999999 uLog Description Returns the logarithm of a number. If the base parameter is omitted, a base of 10 is taken. Syntax number uLog(value [, base]) Parameters number value A number to calculate on. number base The base for the logarithm. If omitted, a base of 10 will be used.
APPENDIX A Function Reference uPow, uPower Description Returns the value of a base expression taken to a specified power. Syntax number uPow(value) Parameters number value A number to calculate on. Examples uPow(10, 3) ;; returns "1000" uRandom Description Returns a random number greater than or equal to zero and smaller than one. Syntax number uRandom(value) Parameters number input A number to calculate on. Examples To return a random number: uRandom() // returns "0.
uSgn uRound(10.9) // returns "11" uRound(1.235, 2) // returns "1.24" uSgn Description Returns the sign of a given value Syntax number uSgn(value) Parameters number value A number to calculate on. Examples To detecting the sign of a given value: uSgn(-10.4) uSgn(0) uSgn(10.4) uSgn(null) // // // // returns returns returns returns -1 0 1 null uSqrt Description Returns the square root of a given value. Syntax number uSqrt(value) Parameters number value A number to calculate on.
APPENDIX A Function Reference Script functions uEvaluate Description Evaluates a function or Java Script expression and returns the result. Syntax string uEvaluate(expression) Parameters number expression JavaScript code to evaluate. Examples To evaluate functions and procedures: uEvaluate(“if (IN.CUSTNO > 22) {return 22;} else {return IN.CUSTNO;} )”) // Return IN.CUSTNO if IN.CUSTNO is below 22 or return 22 if above. You can also define your functions for later use.
uChr, uUniChr Parameters string value An input string. number index Character position for reading ASCII value. Examples To get a Unicode value from a string: uAsc("Big Ben") // returns 66 uAsc("Big Ben", 2) // returns 105 uChr, uUniChr Description Similar to the common chr function but returns the Unicode character specified by the number given. In addition you can also specify more than one character and unicode escape sequences in order to create a string. Syntax string uChr(params, ...
APPENDIX A Function Reference uCap(‘fArmeR, ASTROnaut’) // returns ‘Farmer, Astronaut’ uCap(‘the first weekend’) // returns ‘The First Weekend’ uConcat, uCon Description Returns the concatenation of all input parameters. Syntax string uConcat(params, ...) Parameters params A list of expressions or values of any datatype. Examples To concatenate a string: uConcat(“For “, 3, “ years.”) returns “For 3 years.” uJoin Description Concatenates a delimited string respecting NULL values.
uLeft returns "James-Tiberius-Kirk" uLeft Description Returns the leftmost N characters from a string Syntax string uLeft(input, chars) Parameters string input The input string. number chars The amount of characters to be retrieved. Examples To get the leftmost part of a string: uLeft("James T. Kirk", 5) // returns "James" uLeft(null, 5) // returns null uLength, uLen Description Returns the length of a string Syntax String uLength(input) Parameters string input The input string.
APPENDIX A Function Reference number position The position where to start reading. number length The number of characters to read. Examples To get a substring out of a string: uSubstr("James T. Kirk", 7, 2) // returns "T." uLPos Description Returns the position of a substring within a string. A result of zero indicates that the substring has not been found Syntax string uLPos(input, substring) Parameters string input The input string. string substring The substring to search.
uLStuff uLStuff Description Fills the left side of a string up to a specified length. By default, the string is stuffed with spaces (ASCII 32). Syntax string uLStuff(input, length, [ stuff) Parameters string input The input string. number length New length of string. string stuff(optional) String to append, default is an empty space (ASCII 32). Examples To expand a string by filling its left side: uLStuff(“3.5”, 5) // returns “ 3.5” uLStuff(“3.5”, 5, 5, “0”) // returns “0003.
APPENDIX A Syntax string uRepeat(input, repeats) Parameters string input The string to be repeated. Function Reference number repeats The number of times to repeat the input string. Examples To repeat a string multiple times: uRepeat("Hello ", 4) Hello " // returns "Hello Hello Hello uReplace Description Replaces parts of a string. Syntax string uReplace(input, search, replace) Parameters string input The string to worked on. string search The pattern to be searched.
uRight uRight Description Returns the rightmost N characters from a string. Syntax string uRight(input, chars) Parameters string input The input string. number chars The number of chars to be read. Examples To get the rightmost part of a string: uRight("James T. Kirk", 4) // returns "Kirk" uRight(null, 5) / / returns null uRPos Description Returns the position of a substring within a string. A result of zero indicates that the substring has not been found.
APPENDIX A Function Reference number length The new length of the result string. string stuffstring The string to append. Examples To expand a string by filling its right side: uRStuff("3.5", 5) // returns "3.5 " uRStuff("3.5", 5, "0") // returns "3.500" uRTrim Description Removes characters from the right side of the string. If the second parameter is omitted, it defaults to spaces (ASCII 32). Syntax string uRTrim(input, trimstring) Parameters string input The input string.
uUpper, uUpp uTrim(" 3.5 ") // returns "3.5" uTrim("003.500", "0") // returns "3.5" uUpper, uUpp Description Returns the input string in uppercase letters Syntax string uUpper(input) Parameters string input The input string. Examples To convert a string into uppercase letters: uUpper("James T. Kirk") // returns "JAMES T. KIRK" Operator functions uEQ Description Returns 1 if both parameters are equal and no parameter is NULL.
APPENDIX A Syntax number uNE(value1, value2) Parameters value1, value 2 Numeric or string values to compare. Examples Function Reference uNE(1,2) // returns 1 uNE(1,1) // returns 0 uNE(null,1) // returns 0 uGT Description Returns 1 if the first parameter is greater than the second parameter and no parameter is NULL. Syntax number uGT(value1, value2) Parameters value1, value 2 Numeric or string values to compare.
uLT uLT Description Returns 1 if the first parameter is less than the second parameter and no parameter is NULL. Syntax number uLT(value1, value2) Parameters value1, value 2 Numeric or string values to compare. Examples uLT(1,2) // returns 0 uLT(1,2) // returns 1 uLT(1,1) // returns 0 uGT(null,1) // returns 0 uLE Description Returns 1 if the first parameter is greater than the second parameter and no parameter is NULL.
APPENDIX A Syntax number uAcos(value) Parameters number value The input value. Examples Not available. Function Reference uAsin Description Returns the arcsine (in radians) of a number. Syntax number uAsin(value) Parameters number input The input value. Examples Not available. uAtan Description Returns the arctangent (in radians) of a number. Syntax number uAtan(value) Parameters number value The input value. Examples Not available.
uSin uSin Description Returns the sine (in radians) of a number. Syntax number uSin(value) Parameters number value The input value. Examples Not available. uTan Description Returns the tangent (in radians) of a number. Syntax number uTan(value) Parameters number value The input value. Examples Not available. 190 Sybase IQ ETL 4.
A P PE N DI X B Sybase ETL Server Topic Page GRID architecture Troubleshooting 191 194 Command line parameters INI file settings 195 196 The Sybase ETL Server is part of the Sybase IQ ETL product suite. It is the central component of the GRID architecture. This appendix describes installation, usage, and architecture of the Sybase IQ ETL application. The following terms are used: • Sybase ETL Server — The server application itself, which provides several independent services.
GRID architecture Communication The server instances are using UDP broadcasts in order to inform other nodes about urgent events, such as start and stop, as well as system failure or crash immediately. The default port for communication is port 5124. You can change this in the INI file or on the command line. All communication between the nodes is done over TCP/IP on the same port.
APPENDIX B Sybase ETL Server Linux/Unix: GridNode.sh GridNode.sh --port 5500 Stop a server You can stop a server from the console if it is a local or remote process. If the process is currently executing projects, it will finish afterwards, so it may take a while. Syntax Windows: GridNode --shutdown GridNode --shutdown --server [remotehost] Linux/Unix: GridNode.sh --shutdown GridNode.
Troubleshooting Remove System Service Syntax GridNode.exe --remove When running as System Service, basic events (failures, success messages, and so on) are written to the Windows Event Log. Troubleshooting ❖ To prepare the support contact 1 Check the error text. 2 Check the log file. 3 Run again with system trace switched on. 4 Check the version and revision number as well as your machine identification. Syntax: GridNode --version Output: Sybase IQ ETL (4.1.0.676) Copyright © Sybase, Inc.
APPENDIX B Sybase ETL Server Machine ID: 9TuH/ioF6Wt/Gig= File: custom Product: Sybase IQ ETL Version: 4.1 License: Enterprise Edition Status: Valid Expiration: Command line parameters This section describes all available parameters of the Sybase IQ ETL server. For more detailed information on the usage of these parameters, refer to the usage section. You can always get an overview of the parameters available by typing GridNode --help, or GridNode -h on the command line.
INI file settings Group Full Short Unix Win System Service Logging no_pidfile console nopid con yes yes no yes Description Logging tracelevel tl yes yes Communication server s yes yes Communication port p yes yes Identifies the port number to operate on Info version V yes yes Identifies the application version information Info help h yes yes Info licenses ll yes yes Node nodelist nl yes yes Identifies the short list of parameters Identifies the short informat
APPENDIX B Sybase ETL Server Default.ini Group Network Key proxy Values host:port explorer Default explorer Description Sets the proxy for Internet access. You can fine-tune the proxy for a certain protocol (http, https, ftp, ftps) by using the keys “http_proxy”, “https_proxy”, “ftp_proxy”, or “ftps_proxy”. The proxy value “explorer” takes the system proxy in Windows environments.
INI file settings 198 Sybase IQ ETL 4.
A P PE N DI X C Queuing and Executing Process Calls Topic Page Configuring ProcessQ calls Controlling the appearance of a new process 199 201 ProcessQ is an application that can queue and execute process calls parallel or in sequence. The serialization is implemented with a mutex (mutual exclusion) semaphore. The application uses an internal GUID to name the mutex, so the exclusion is system-wide (over all products). It is possible to set an own ID for special purposes.
Configuring ProcessQ calls -I Customized ID for exclusion. -W n Window style; see “Controlling the appearance of a new process” on page 201. -D The working directory for the application. -E The executable and its command line parameters. If this value contains spaces, it must be quoted with double quotes. -P[,< ExtVar=Value>..,< ExtVar=Value>] This option allows you to set parameters from the command line that can be used by the job or project during execution.
APPENDIX C Queuing and Executing Process Calls Code Error 8 Terminating the process failed. Controlling the appearance of a new process By providing a Window Style with the -W option, you can control the appearance of the new process. User’s Guide Value Name Meaning 0 HIDE 1 SHOWNORMAL 2 SHOWMINIMIZED 3 SHOWMAXIMIZED Hides the window and activates another window. Activates and displays a window.
Controlling the appearance of a new process 202 Value Name Meaning 10 SHOWDEFAULT 11 FORCEMINIMIZE Sets the show state based on the value specified in the STARTUPINFO structure passed to the CreateProcess function by the program that started the application. Windows 2000/XP: Minimizes a window, even if the thread that owns the window is hanging. This flag should be used only when minimizing windows from a different thread. Sybase IQ ETL 4.
Index A adding a component 16 adding component variables 76 allowing dynamic expressions 15 applying component variables 14 Component Store 16 deleting a component 16 editing component variables 14 encrypting properties 15, 77 entering database connection parameters evaluating SBN expressions 77 grouping components 16 port structure and mapping 3 providing descriptions 76 removing a component variable 15 setting required properties 70 setting up a component 71 stepping record-by-record 2 variables and por
Index adding to a project 24 data formats converting 5 data provider adding to a project 22 data sink adding to a project 23 setting properties 24 Data Splitter 109 data transformation projects creating 3, 4 database connection connecting to SQLite database 75 entering parameters 71 datatypes converting 5 date and time functions format of time strings 137 uDate 140 uDateTime 141 uDay 141 uDayOfYear 142 uHour 142 uIsoWeek 143 uJuliandate 144 uMinute 144 uMonth 145 uMonthName 145 uMonthNameShort 145 uQuarter
Index F J fatal.
Index DB Lookup 101 DB Lookup Dynamic lookup functions uChoice 157 uElements 159 uFirstDifferent 158 uFirstNotNull 158 uToken 159 O M migration template 40 miscellaneous functions uCommandLine 160 uGetEnv 160 uGuid 160 uMD5 161 uScriptLoad 161 uSetEnv 161 uSetLocale 162 uSleep 165 uSystemFolder 165 multi-engine execution define multi-engine jobs 63 reducing job execution time 61 registering GRID engines 62 N Navigator browsing repositories Development desktop network functions uHostname 171 numeric func
Index adding a data sink 23 controlling multiple data streams 35 copying a project 28 creating a project 27 creating data transformation projects 3 creating data transformation projects, complex creating your first project 22 customizing a project 3 defined 2 deleting a project 28 executing a project 28 managing projects 27 mappings 31 modifying a project 28 renaming a project 28 resetting execution properties 28 running a project 3 simulating a project 3 simulating and executing a project 29 starting a sim
Index uRepeat 182 uReplace 183 uReverse 183 uRight 184 uRPos 184 uRStuff 184 uRTrim 185 uSubstr, uMid 180 uTrim 185 uUpper, uUpp 186 Structure Viewer 32 Synchronizer component system.
