Extended Systems OneBridge 4.1 Betriebsanweisung

User’s GuideSybase IQ ETL4.1

x Sybase IQ ETL 4.1

Source components 86 Sybase IQ ETL 4.1PropertiesCharacter Encoding• Select a character set from the list.• Press Enter to confirm.Support UnicodeAct

CHAPTER 6 ComponentsUser’s Guide 87• Press Enter to confirm.Read column names from row• Enter the number of the line containing the column headings

Source components 88 Sybase IQ ETL 4.1Working with fixed-length file typeWhen a file has been qualified as fixed-length, the Add Column option in th

CHAPTER 6 ComponentsUser’s Guide 89You do not need to understand XML in detail to use this component. It is useful for data-centric XML documents (

Source components 90 Sybase IQ ETL 4.1DB SchemaSelect the file containing the database schema setup (create tables) script. Use this option to enfor

CHAPTER 6 ComponentsUser’s Guide 91<PRODUCTS><PR_ID>435673</PR_ID><PR_NAME>Notebook 235</PR_NAME><PR_GROUP1>Not

Source components 92 Sybase IQ ETL 4.1The tables are linked through foreign keys. Table TAB_PRODUCTS is linked to TAB_dataroot via attribute ATT_FK_

CHAPTER 6 ComponentsUser’s Guide 93Creating a SELECT statementYou can enter a SELECT statement for the port straight into the port field, or you ca

Transformation components 94 Sybase IQ ETL 4.1❖ To create a mapping1 Select the Graph tab of the component window.2 Map the IN-Port and Out-Port str

CHAPTER 6 ComponentsUser’s Guide 95❖ To simulate a single attribute1 Change the value of any attribute in the Current Input Record.2Press Enter. Th

About This BookUser’s Guide xiAbout This BookAudienceThis guide is for users of Sybase IQ ETL. Sybase IQ ETL provides extract, transform, and loa

Transformation components 96 Sybase IQ ETL 4.14 Right-click and select OUT or TMP.5 Select one of the attributes from the menu.The added attribute c

CHAPTER 6 ComponentsUser’s Guide 97You can freely change the mapping by re-connecting the lines to the connection points. Note After you apply a t

Transformation components 98 Sybase IQ ETL 4.1Using lookups in the Data CalculatorThe Data Calculator can perform lookups on attribute level. The lo

CHAPTER 6 ComponentsUser’s Guide 99Building Lookup rulesTo set up Lookup rules open the Tabular Tab of the Data Calculator window. If Lookup ports

Transformation components 100 Sybase IQ ETL 4.1❖ To set up required properties1 Add the component to the project and connect the ports of the compon

CHAPTER 6 ComponentsUser’s Guide 101There is no impact on the simulation sequence.Using Lookup componentsIn general, a Lookup operation looks up a

Using Lookup components 102 Sybase IQ ETL 4.1Required propertiesKey AttributeSelect a Key Attribute from the list of IN-Port attributes. This attrib

CHAPTER 6 ComponentsUser’s Guide 103Use Key ValueIf Use Key Value is activated, the key value will be assigned to the value attribute instead of th

Using Lookup components 104 Sybase IQ ETL 4.1• Schema• Standardize Data Format• Database OptionsThere is no impact on the simulation sequence.View P

CHAPTER 6 ComponentsUser’s Guide 105Enter the Connection Parameters as described in “Entering database connection parameters” on page 71.QueryTo op

xii Sybase IQ ETL 4.1• Appendix C, “Queuing and Executing Process Calls”describes ProcessQ, which is an application that can queue and execute proce

Using Lookup components 106 Sybase IQ ETL 4.1For example, assume you want to replace the product number used for German products by the product numb

CHAPTER 6 ComponentsUser’s Guide 107Staging componentsDB Staging componentThe DB Staging component loads the incoming data streams into a staging a

Staging components 108 Sybase IQ ETL 4.15 Right-click the component and select either Create Staging Table from Input or Create Staging Table from P

CHAPTER 6 ComponentsUser’s Guide 109The DB Staging component impacts the flow of the simulation by first retrieving all data from the original data

Destination components 110 Sybase IQ ETL 4.1Customizing port conditionsA condition can be assigned to each port. A condition consists of one ore mor

CHAPTER 6 ComponentsUser’s Guide 111Use this component if you want to add all records from the IN-port of the component to a database table.❖ To us

Destination components 112 Sybase IQ ETL 4.1Pre-processing SQLThis property provides one or more SQL statements to be executed during initialization

CHAPTER 6 ComponentsUser’s Guide 113DB Data Sink Update componentThis component updates records in a destination table based on a selected key. All

Destination components 114 Sybase IQ ETL 4.1Optional propertiesUpdate optionsClick Update Options to open the Update Options window. By default all

CHAPTER 6 ComponentsUser’s Guide 115DB Data Sink Delete componentThis component removes records from the destination table that match the incoming

About This BookUser’s Guide xiii3 Select a product.4 Specify a time frame and click Go. A list of EBF/Maintenance releases is displayed.Padlock i

Destination components 116 Sybase IQ ETL 4.1Post-processing SQLThis property provides one or more SQL statements to be executed when all components

CHAPTER 6 ComponentsUser’s Guide 1174 Click Save to exit the component window.5 Connect the component with the OUT-Port of an adjacent component.6

Destination components 118 Sybase IQ ETL 4.1PropertiesCharacter EncodingSelect a character set from the drop-down list and press Enter to confirm.Ty

CHAPTER 6 ComponentsUser’s Guide 119AppendActivate this option to append the incoming data to an existing file. Header Enter text for a header to b

Destination components 120 Sybase IQ ETL 4.1Optional propertiesTruncateActivate this option to remove all records from the destination index set whe

CHAPTER 6 ComponentsUser’s Guide 121• Database OptionsThere is no impact on the simulation sequence.There are no Projects in DemoRepository or Help

Job components 122 Sybase IQ ETL 4.1Optional propertiesContinue on DB Write ErrorsIf you activate this option, project execution will continue even

CHAPTER 6 ComponentsUser’s Guide 123Multi-Project componentThis component is the visual representation of a project group within the job. It combin

Job components 124 Sybase IQ ETL 4.1Finish componentThis component visually represents the end of a successful job execution. Use it to mark the suc

User’s Guide 125APPENDIX AFunction ReferenceThis appendix provides a reference for the Sybase IQ ETL functions. Note Even if your original data is no

xiv Sybase IQ ETL 4.1If you need helpEach Sybase installation that has purchased a support contract has one or more designated people who are author

uAvg 126 Sybase IQ ETL 4.1Aggregation functionsuAvg Description Calculates and returns the average value of all input values.Syntax real uAvg(value,

APPENDIX A Function ReferenceUser’s Guide 127Parameters numeric valueA list of numeric arguments.Examples To find the highest value from a set of v

uBitXOr 128 Sybase IQ ETL 4.1uBitXOrDescription Calculates the bitwise Exclusive OR over all parameters given and returns the result.Syntax number u

APPENDIX A Function ReferenceUser’s Guide 129uIsAscending("2004-03-03", "2004-03-05", "2004-03-07") // returns 1uIsA

ulsDescending 130 Sybase IQ ETL 4.1string format(optional)The format of the input date.Note Omitting the format parameter can slow down the functio

APPENDIX A Function ReferenceUser’s Guide 131Parameters paramAn expression or value to investigate.Examples uIsInteger (“1”) // returns 1uIsInteg

uNot 132 Sybase IQ ETL 4.1Syntax number uIsNumber (params)ParametersparamsAn expression or value to investigate.Examples To check for a numeric valu

APPENDIX A Function ReferenceUser’s Guide 133uBase64EncodeDescription Decodes a string into a Base64 representation.Syntax string uBase64Encode(inp

uConvertDate 134 Sybase IQ ETL 4.1Table 6-1: Date Conversion identifiersSyntax string uConvertDate(datestring, inputformat [, outputformat])Paramete

APPENDIX A Function ReferenceUser’s Guide 135uFromHexDescription Converts a string of hexadecimal numbers into an integer.Syntax integer uFromHex (

User’s Guide 1CHAPTER 1Sybase IQ ETLSybase IQ ETL architectureWhen you install Sybase IQ ETL on Windows, you install Sybase IQ ETL Development and Syb

uHexEncode 136 Sybase IQ ETL 4.1uHexEncodeDescription Encodes every character of a string into its hexadecimal notationSyntax string uHexEncode(inpu

APPENDIX A Function ReferenceUser’s Guide 137uURIEncodeDescription Returns a new version of a complete URI, replacing each instance of certain char

uURIEncode 138 Sybase IQ ETL 4.1If no date is given, the time string now is assumed and the date is set to the current date and time.uDate() // retu

APPENDIX A Function ReferenceUser’s Guide 139The “unixepoch” modifier (12) works only if it immediately follows a time string in the DDDD.DDDDD for

uDate 140 Sybase IQ ETL 4.1•The localtime() C function normally only works for years between 1970 and 2037. For dates outside this range, you can at

APPENDIX A Function ReferenceUser’s Guide 141uDateTimeDescription Returns year, month, and day from a date in the format YYYY-MM-DD HH:MM:SS.Note

uDayOfYear 142 Sybase IQ ETL 4.1uDayOfYearDescription Returns the number of days since the beginning of the year.Syntax string uDayOfYear([modifiers

APPENDIX A Function ReferenceUser’s Guide 143uHour("now") // returns current quarteruHour("1969-03-13 10:22:23.231") // retu

uJuliandate 144 Sybase IQ ETL 4.1uJuliandateDescription Returns the number of days since noon in Greenwich on November 24, 4714 B.C. in the format D

APPENDIX A Function ReferenceUser’s Guide 145uMonthDescription Returns the month of the date specified.Syntax string uMonth([modifiers])Parameterss

Projects and jobs 2 Sybase IQ ETL 4.1Sybase ETL ServerThe Sybase ETL Server provides the GRID engine service, which, by default, processes desktop r

uSeconds 146 Sybase IQ ETL 4.1Parametersstring modifiers (optional)String specifying a date or date calculation. The default is the now modifier.Exa

APPENDIX A Function ReferenceUser’s Guide 147Examples uTimeDiffMs("18:34:20”, “18:34:21”) // returns 1000 uTimeDiffMs("18:34:20”, “18:34:

uWeekdayName 148 Sybase IQ ETL 4.1uWeekdayNameDescription Returns the week name of the date specified in the current locale language. Note Refer to

APPENDIX A Function ReferenceUser’s Guide 149To set the locale to English:uSetLocale("English")uWeekdayNameShort("1969-03-13 10:22:2

uErrortext 150 Sybase IQ ETL 4.1uErrortextDescription Returns the last error message. The error text will be internally cleared, so it can only be r

APPENDIX A Function ReferenceUser’s Guide 151uTraceDescription Writes a trace message into the log. The function returns the message written.Syntax

uFileInfo 152 Sybase IQ ETL 4.1File functionsuFileInfoDescription Returns information about a file. When uFileInfo is set to EXISTS the function ret

APPENDIX A Function ReferenceUser’s Guide 153integer bytes (optional)The number of bytes to read. The default is 0, which means the whole file.inte

uFormatDate 154 Sybase IQ ETL 4.1ExamplesTo write data to a file via CIFS:uFileWrite("hello", "//myServer/myShare/data/test.txt"

APPENDIX A Function ReferenceUser’s Guide 155Table 6-2: Format identifiersNote Refer to the “Working with Dates and Times” section for detailed in

CHAPTER 1 Sybase IQ ETLUser’s Guide 3Component variables and portsAll data within a project flow through component ports called IN-ports and OUT-po

uGlob 156 Sybase IQ ETL 4.1Fuzzy search functionsuGlobDescription Compares values case sensitive and similar to uLike but uses the UNIX file globbin

APPENDIX A Function ReferenceUser’s Guide 157Parameters string patternA string describing a match pattern.string textA string to investigate.Exampl

uFirstDifferent 158 Sybase IQ ETL 4.1string valuesList of values.Examples Example 1 IF construct:uChoice(0, "A", "B") // return

APPENDIX A Function ReferenceUser’s Guide 159uElementsDescription Returns the number of elements in a delimited string. If the second parameter is

uCommandLine 160 Sybase IQ ETL 4.1Miscellaneous functionsuCommandLineDescription Returns the command line string of the current process.Syntax strin

APPENDIX A Function ReferenceUser’s Guide 161Examples To create a unique identifier:uGuid() // returns for example A8A10D9F-963F-4914-8D6F-C8527A50

uSet Locale 162 Sybase IQ ETL 4.1string valueThe value to setExamples uSetEnv(“LOAD_MAX_VALUE”, IN.Date) uSet LocaleDescription Changes the current

APPENDIX A Function ReferenceUser’s Guide 163Czech Czech “csy” or “czech”Danish Danish “dan”or “danish”Dutch Dutch (Belgian) “belgian”, “dutch-belg

uSet Locale 164 Sybase IQ ETL 4.1Country/Region StringsFollowing is a list of country/regions strings recognized by uSetLocale. The uSetLocaleString

APPENDIX A Function ReferenceUser’s Guide 165uSleepDescription Suspends the process for a specified amount of milliseconds.Syntax string uSleep(mse

Understanding repositories 4 Sybase IQ ETL 4.1• To generate SELECT statements inside Queries, Lookup Definitions, Pre- and Post Processing SQL, use

uSystemFolder 166 Sybase IQ ETL 4.1Parametersstring foldertype (optional)Specifies the folder to return. The default is “System”.Examples uSystemFol

APPENDIX A Function ReferenceUser’s Guide 167Windows CDBURN_AREA The file system directory acting as a staging area for files waiting to be written

uSystemFolder 168 Sybase IQ ETL 4.1Windows COMMON_STARTMENU The file system directory that contains the programs and folders that appear on the Star

APPENDIX A Function ReferenceUser’s Guide 169Windows HISTORY The file system directory that serves as a common repository for Internet history item

uSystemFolder 170 Sybase IQ ETL 4.1Windows PERSONAL The virtual folder representing the My Documents desktop item. This is equivalent to MYDOCUMENTS

APPENDIX A Function ReferenceUser’s Guide 171Network functionsuHostnameDescription Returns the local network name.Syntax string uHostname()Examples

uAbs 172 Sybase IQ ETL 4.1Numeric functionsuAbsDescription Returns the magnitude of a real number, ignoring its positive or negative sign.Note This

APPENDIX A Function ReferenceUser’s Guide 173uDivDescription Returns the division integer.Syntax number uDiv(value)Parametersnumber valueA number t

uLn 174 Sybase IQ ETL 4.1uLnDescription Returns the natural logarithm (base E) of a number.Syntax number uLn(input);Parametersnumber valueA number t

APPENDIX A Function ReferenceUser’s Guide 175uPow, uPowerDescription Returns the value of a base expression taken to a specified power.Syntax numbe

CHAPTER 1 Sybase IQ ETLUser’s Guide 5Multiple repositories are accessible in parallel during a session. Projects can be copied and transferred betw

uSgn 176 Sybase IQ ETL 4.1uRound(10.9) // returns "11"uRound(1.235, 2) // returns "1.24"uSgnDescription Returns the sign of a

APPENDIX A Function ReferenceUser’s Guide 177Script functionsuEvaluateDescription Evaluates a function or Java Script expression and returns the re

uChr, uUniChr 178 Sybase IQ ETL 4.1Parametersstring valueAn input string.number indexCharacter position for reading ASCII value.Examples To get a Un

APPENDIX A Function ReferenceUser’s Guide 179uCap(‘fArmeR, ASTROnaut’) // returns ‘Farmer, Astronaut’uCap(‘the first weekend’) // returns ‘The Firs

uLeft 180 Sybase IQ ETL 4.1returns "James-Tiberius-Kirk"uLeftDescription Returns the leftmost N characters from a stringSyntax string uLef

APPENDIX A Function ReferenceUser’s Guide 181number positionThe position where to start reading.number lengthThe number of characters to read.Examp

uLStuff 182 Sybase IQ ETL 4.1uLStuffDescription Fills the left side of a string up to a specified length. By default, the string is stuffed with spa

APPENDIX A Function ReferenceUser’s Guide 183Syntax string uRepeat(input, repeats)Parametersstring inputThe string to be repeated.number repeatsThe

uRight 184 Sybase IQ ETL 4.1uRightDescription Returns the rightmost N characters from a string.Syntax string uRight(input, chars)Parametersstring in

APPENDIX A Function ReferenceUser’s Guide 185number lengthThe new length of the result string.string stuffstringThe string to append.Examples To ex

DOCUMENT ID: DC00608-01-0410-01LAST REVISED: October 2006Copyright © 2003-2006 by Sybase, Inc. All rights reserved.This publication pertains to Sybase

Tools 6 Sybase IQ ETL 4.1ToolsStructural and catalog information from all connected data sources is accessible through Sybase IQ ETL Development too

uUpper, uUpp 186 Sybase IQ ETL 4.1uTrim(" 3.5 ") // returns "3.5"uTrim("003.500", "0") // returns "3

APPENDIX A Function ReferenceUser’s Guide 187Syntax number uNE(value1, value2)Parametersvalue1, value 2Numeric or string values to compare.Examples

uLT 188 Sybase IQ ETL 4.1uLTDescription Returns 1 if the first parameter is less than the second parameter and no parameter is NULL.Syntax number uL

APPENDIX A Function ReferenceUser’s Guide 189Syntax number uAcos(value)Parametersnumber value The input value.Examples Not available.uAsinDescripti

uSin 190 Sybase IQ ETL 4.1uSinDescription Returns the sine (in radians) of a number.Syntax number uSin(value)Parametersnumber valueThe input value.E

User’s Guide 191APPENDIX BSybase ETL ServerThe Sybase ETL Server is part of the Sybase IQ ETL product suite. It is the central component of the GRID a

GRID architecture 192 Sybase IQ ETL 4.1CommunicationThe server instances are using UDP broadcasts in order to inform other nodes about urgent events

APPENDIX B Sybase ETL ServerUser’s Guide 193Linux/Unix:GridNode.shGridNode.sh --port 5500Stop a serverYou can stop a server from the console if it

Troubleshooting 194 Sybase IQ ETL 4.1Remove System ServiceSyntaxGridNode.exe --removeWhen running as System Service, basic events (failures, success

APPENDIX B Sybase ETL ServerUser’s Guide 195Machine ID: 9TuH/ioF6Wt/Gig=File: customProduct: Sybase IQ ETLVersion: 4.1License: Enterprise EditionSt

CHAPTER 1 Sybase IQ ETLUser’s Guide 7• Implements most of SQL92. • A complete database is stored in a single disk file. • Database files can be fre

INI file settings 196 Sybase IQ ETL 4.1INI file settingsIn the etc directory of the product installation you will find INI files which you can use i

APPENDIX B Sybase ETL ServerUser’s Guide 197Default.iniGridNode.iniGroup Key Values Default DescriptionNetwork proxy host:port explorerexplorer Set

INI file settings 198 Sybase IQ ETL 4.1

User’s Guide 199APPENDIX CQueuing and Executing Process CallsProcessQ is an application that can queue and execute process calls parallel or in sequen

Configuring ProcessQ calls 200 Sybase IQ ETL 4.1-ICustomized ID for exclusion.-W nWindow style; see “Controlling the appearance of a new process” on

APPENDIX C Queuing and Executing Process CallsUser’s Guide 201Controlling the appearance of a new processBy providing a Window Style with the -W op

Controlling the appearance of a new process 202 Sybase IQ ETL 4.110 SHOWDEFAULT Sets the show state based on the value specified in the STARTUPINFO

User’s Guide 203Aaggregation functionsuAVg 126uMax 126uMin 126architectureGRID 191IQ ETL 1Bbit functionsuBitAnd 127uBitNot 128uBitOr 127uBitXOr 128bo

Index204 Sybase IQ ETL 4.1adding to a project 24data formatsconverting 5data provideradding to a project 22data sinkadding to a project 23setting p

IndexUser’s Guide 205Ffatal.log 49file functionsuFileInfo 152uFileRead 152uFileWrite 153Finish component 124formatting functionsuFormatDate 154functi

Internal database 8 Sybase IQ ETL 4.1

Index206 Sybase IQ ETL 4.1DB Lookup 101DB Lookup Dynamic 104lookup functionsuChoice 157uElements 159uFirstDifferent 158uFirstNotNull 158uToken 159Mm

IndexUser’s Guide 207adding a data sink 23controlling multiple data streams 35copying a project 28creating a project 27creating data transformatio

Index208 Sybase IQ ETL 4.1uRepeat 182uReplace 183uReverse 183uRight 184uRPos 184uRStuff 184uRTrim 185uSubstr, uMid 180uTrim 185uUpper, uUpp 186Struc

User’s Guide 9CHAPTER 2Sybase IQ ETL Development DesktopDesktop layoutThe Sybase IQ ETL Development desktop consists of the following major sections:•

Using the Navigator 10 Sybase IQ ETL 4.1Figure 2-1: Sybase IQ ETL Development desktopUsing the NavigatorThe following objects and functions are avai

CHAPTER 2 Sybase IQ ETL Development DesktopUser’s Guide 11Administering the repositoryA Sybase IQ ETL repository is a collection of tables that sav

Using the Navigator 12 Sybase IQ ETL 4.12 Enter a client user name.3 Enter a password.4 Select the Register New check box. 5 If the client user is e

CHAPTER 2 Sybase IQ ETL Development DesktopUser’s Guide 13Administering user accountsWith the built-in user account administration in Sybase IQ ETL

Using the Properties section 14 Sybase IQ ETL 4.1Using the Properties sectionThe Properties section contains information and functions that let you:

CHAPTER 2 Sybase IQ ETL Development DesktopUser’s Guide 152 Right-click in the Properties section to open a pop-up menu.3 Select Edit and enter the

ContentsUser’s Guide iiiAbout This Book...

Using the Component Store section 16 Sybase IQ ETL 4.1• Simulate and run projects• Run jobsTo create a project or job, you must add and connect comp

CHAPTER 2 Sybase IQ ETL Development DesktopUser’s Guide 17• Drag and drop the component to an empty spot or to an existing connection in the Design

Customizing preferences 18 Sybase IQ ETL 4.1• Create new project on startup — specify whether to automatically start a new project each time you sta

CHAPTER 2 Sybase IQ ETL Development DesktopUser’s Guide 19• Grid Engine Server —specify the IP address of the primary GRID engine server. • Grid En

Customizing preferences 20 Sybase IQ ETL 4.1

User’s Guide 21CHAPTER 3Getting StartedStarting Sybase IQ ETL Development❖ To start Sybase IQ ETL Development1 Double-click the Sybase IQ ETL Developm

Creating your first project 22 Sybase IQ ETL 4.15 Select an existing project, or right-click on Projects to create a new project. Creating your firs

CHAPTER 3 Getting StartedUser’s Guide 232 Select ETLDEMO_US from the Host Name drop-down list.After you confirm the initial component settings, the

Creating your first project 24 Sybase IQ ETL 4.1Your project should now consist of two components. The link between the components had been created

CHAPTER 3 Getting StartedUser’s Guide 25The Data Calculator window has a Tabular and Graph view:•Use the Tabular view to enter transformation rules

ContentsivSybase IQ ETL 4.1Using the pop-up menu to process commands... 16Using the Component Store section ...

Simulating the project 26 Sybase IQ ETL 4.1

User’s Guide 27CHAPTER 4Projects and JobsManaging projectsProjects are the working units of Sybase IQ ETL. A project consists of components and links,

Managing projects 28 Sybase IQ ETL 4.12 Drag components from the Component Store onto the Design section as your project requires it.Executing a pro

CHAPTER 4 Projects and JobsUser’s Guide 29Simulating and executing a projectSimulating a project is a highly interactive process that lets you moni

Simulating and executing a project 30 Sybase IQ ETL 4.1• All data for all static Lookup components are retrieved and cached. Any change of data in l

CHAPTER 4 Projects and JobsUser’s Guide 31Viewing current mappingsThe Mapping Definition window shows the current mapping between attributes of the

Simulating and executing a project 32 Sybase IQ ETL 4.1Managing port attributesThe Structure Viewer is available at the port of a component. You can

CHAPTER 4 Projects and JobsUser’s Guide 33The number of records being processed within each single step is dependent on the current value of Read B

Simulating and executing a project 34 Sybase IQ ETL 4.1To resume simulation from the selected component, select Step from the pop-up menu. To resume

CHAPTER 4 Projects and JobsUser’s Guide 35Partial execution or initialization during simulationIt is extremely time consuming to start the entire s

ContentsUser’s Guide vUsing multiple engines to reduce job execution time... 61Defining multi-engine jobs...

Managing jobs 36 Sybase IQ ETL 4.1If you have multiple data streams it is highly advisable to design a project for each data stream so that all comp

CHAPTER 4 Projects and JobsUser’s Guide 37A Start component is always followed by one or multiple Project components. Figure 4-3: A Job with multip

Managing jobs 38 Sybase IQ ETL 4.1• Stop: The stop date and time.• Engine Name: The name of the executing engine.• Engine Host: The host of the exec

CHAPTER 4 Projects and JobsUser’s Guide 39Copying a jobTo copy a job to a specified destination, open it and select Save As from the File menu.Note

Using templates to create projects and jobs 40 Sybase IQ ETL 4.1Using templates to create projects and jobsTemplates provide a way to automatically

CHAPTER 4 Projects and JobsUser’s Guide 41Stage properties — Enter the general part for the Load Stage properties of the DB Bulk Load IQ component

Using templates to create projects and jobs 42 Sybase IQ ETL 4.1Select Truncate to remove all records from the destination table before loading. Thi

CHAPTER 4 Projects and JobsUser’s Guide 43To finish collecting data and perform the selected tasks, click Finish.Note Please ensure that you selec

Using templates to create projects and jobs 44 Sybase IQ ETL 4.1Creating a data model from a templateTo set up the destination data model according

User’s Guide 45CHAPTER 5Advanced Concepts and ToolsContent ExplorerContent Explorer consists of a menu, a toolbar, a SQL structure area, a Navigation

ContentsviSybase IQ ETL 4.1Project component ... 121Synchronizer component...

Content Explorer 46 Sybase IQ ETL 4.1The examples in this chapter open the project Demo Getting Started from the Demo Repository.Opening Content Exp

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 47Creating queriesThe Content Explorer allows you to create queries in a convenient graphical env

Content Explorer 48 Sybase IQ ETL 4.1❖ To modify the default setting of a joinA join between two tables is indicated by a line that connects the joi

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 49Inspecting log file informationThe File Log Inspector window allows you to inspect log file inf

Managing jobs and scheduled tasks 50 Sybase IQ ETL 4.1To open Runtime Manager, select Tools | Runtime Manager.❖ To create a new schedule1 Create a n

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 51Job execution state codes in the Task Scheduler are shown in the following table.Table 5-1: Job

Customizing SQL and transformation rules 52 Sybase IQ ETL 4.1Using expressions and proceduresAn expression is a combination of identifiers and opera

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 53Port variablesThe values of the port structure are referenced as Port variables within a compon

Customizing SQL and transformation rules 54 Sybase IQ ETL 4.1Using Square Bracket NotationExpressions and SQL statements can contain SBN expressions

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 55From the Query window you can:• Enter a query• Run a query• Save a query• Open the Query Design

User’s Guide viiuMonthNameShort ... 145uSeconds...

Customizing SQL and transformation rules 56 Sybase IQ ETL 4.1SELECT * FROM CUSTOMERS WHERE CU_NO = '12345678'With SBN you can use a more f

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 57The following examples show pre-processing and post-processing SQL:delete from products;update

Customizing SQL and transformation rules 58 Sybase IQ ETL 4.1Starting the JavaScript Editor and DebuggerWithin the Data Calculator JavaScript compon

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 59To validate JavaScript code, click Compile. The result of the validation is displayed in the Ta

Customizing SQL and transformation rules 60 Sybase IQ ETL 4.12 Click Go on the last breakpoint to execute the rest of the script.Inline inspection o

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 61function IncA (a){var b = 3;a = IncB(b) + a++;return a;function IncB(b){b = b + 1;return b;}}Co

Using multiple engines to reduce job execution time 62 Sybase IQ ETL 4.1• Install multiple GRID engines• Register your GRID engines• Prepare jobs fo

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 632 Overwrite the current values with new values.3 Click OK.❖ To delete an engine registration• S

Analyzing performance data 64 Sybase IQ ETL 4.1Performance data model and contentThe performance data is stored in a single, de-normalized, reposito

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 65General informationEach execution of project or job is identified by a global unique ID. You wi

viii Sybase IQ ETL 4.1uPow, uPower ... 175uRandom...

Analyzing performance data 66 Sybase IQ ETL 4.1Project execution historyFigure 5-1: Example of a project execution history report

CHAPTER 5 Advanced Concepts and ToolsUser’s Guide 67Project execution time and records movedTabular Figure 5-2: Example of a tabular report capturi

Analyzing performance data 68 Sybase IQ ETL 4.1

User’s Guide 69CHAPTER 6ComponentsOverviewThe Sybase IQ ETL components are used to create projects and jobs. (See Chapter 4, “Projects and Jobs.”) The

Overview 70 Sybase IQ ETL 4.1Transformation components, Lookup components, and Staging components have at least one IN-port and one OUT-port and app

CHAPTER 6 ComponentsUser’s Guide 71Setting up a componentEach component is dedicated to a specific task and therefore incorporates task-specific fe

Overview 72 Sybase IQ ETL 4.1• ODBC Note The ODBC driver must be installed on the same machine as Sybase IQ ETL Development desktop and a system da

CHAPTER 6 ComponentsUser’s Guide 73DB Option Default DescriptionShow error location 1 1 = yes 0 = noDatabase errors will include the position of th

Overview 74 Sybase IQ ETL 4.19 Set the Read Block Size. The Read Block Size option defines the number of records retrieved by the component within i

CHAPTER 6 ComponentsUser’s Guide 75One or more SQL statements can be executed when all components finished processing the project. Note When using

User’s Guide ixGRID Engine Server... 192GRID Engine Server as Windows System Service...

Overview 76 Sybase IQ ETL 4.1You can use the Content Explorer from the Tools menu to manipulate or browse objects of the SQLite database connected t

CHAPTER 6 ComponentsUser’s Guide 77❖ To edit a component variable1 Right-click the component variable in the Property section to open the pop-up me

Overview 78 Sybase IQ ETL 4.1Modifying componentsTo initialize a component, right-click it and select Initialize or select Initialize and Step from

CHAPTER 6 ComponentsUser’s Guide 79When stepping the component repetitively, the same set of records at the IN-port are reprocessed in each step an

Overview 80 Sybase IQ ETL 4.13 Click Save to confirm.❖ To delete an attribute from the port1 Click the line containing the attribute.2 Click Delete.

CHAPTER 6 ComponentsUser’s Guide 81In Display structure mode, all attributes of the connected port and their current mappings are shown. The Displa

Source components 82 Sybase IQ ETL 4.1SELECT A.CU_NO, A.CU_NAME, A.CU_CITY, B.SA_ORDERDATE, B.SA_TOTALFROM CUSTOMERS A, SALES BWHERE A.CU_NO = B.CU_

CHAPTER 6 ComponentsUser’s Guide 83• Database• Schema• Database optionsImpact on simulation sequenceRead Block Size value impacts the number of rec

Source components 84 Sybase IQ ETL 4.13 Select an attribute with values that increase whenever data is changed or added to the source, such as an au

CHAPTER 6 ComponentsUser’s Guide 85• Post-processing SQL — One or more SQL statements to be executed after all components finished execution. See “