Sybase ETL

Users Guide Sybase ETL 4.9

DOCUMENT ID: DC00608-01-0490-01 LAST REVISED: September 2009 Copyright 2009 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. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845. Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All other international customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduled software release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc. Sybase trademarks can be viewed at the Sybase trademarks page at http://www.sybase.com/detail?id 1011207. Sybase and the marks listed are trademarks of Sybase, Inc. indicates registration in the United States of America. Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries. Unicode and the Unicode Logo are registered trademarks of Unicode, Inc. All other company and product names mentioned may be trademarks of the respective companies with which they are associated. Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies. Sybase, Inc., One Sybase Drive, Dublin, CA 94568.

Contents About This Book . xi CHAPTER 1 Sybase ETL. Sybase ETL architecture . Sybase ETL concepts . Repositories . Projects and jobs . Components . SQL statements. JavaScript. Datatypes and data formats . Unicode support . Tools. 1 1 3 3 4 6 6 7 7 7 8 CHAPTER 2 Getting Started . 9 Starting Sybase ETL . 9 Setting up a new user account on the demo repository . 10 Working with the Sybase ETL Development interface . 10 Navigator . 11 Assistant window . 15 Properties window . 16 Design window . 18 Component Store . 19 Customizing preferences . 19 Troubleshooting . 23 CHAPTER 3 Projects and Jobs . Managing projects . Simulating a project . Executing a project . Scheduling a project . Managing jobs . Job components . Users Guide 25 25 27 35 36 36 36 iii

Contents Controlling job execution . Executing a job. Scheduling a job. Using templates to create projects and jobs . Building a migration template using the template assistant . Managing a migration template . Creating and simulating a sample project . Adding a data provider . Adding a data sink. Adding a Data Calculator . Starting the simulation. CHAPTER 4 iv 38 39 39 39 39 43 45 45 46 48 48 Advanced Concepts and Tools . 51 Query Designer . 51 Opening the Query Designer. 52 Query Designer interface . 52 Creating queries . 53 Content Explorer . 55 File Log Inspector. 56 Managing jobs and scheduled tasks . 57 Customizing SQL and transformation rules . 60 Expressions and procedures. 61 Variables . 62 Functions. 63 Square Bracket Notation . 63 Working with SQL properties. 64 Using SBN expressions. 66 Using the JavaScript Editor and Debugger . 67 Executing SQL queries and commands. 71 Parameter sets. 72 Managing parameter sets. 72 Assigning parameter values . 74 Using multiple engines to reduce job execution time . 76 Defining multiengine jobs . 77 Executing multi-engine jobs . 78 Engine Monitor . 78 Execution Monitor . 78 Cancelling job execution . 79 Analyzing performance data . 80 Viewing performance data. 80 Viewing project performance data. 81 View job performance data. 81 Performance data model and content . 83 Configuring alerts for runtime events . 84 Sybase ETL

Contents CHAPTER 5 Components . 87 Overview . 87 Setting up component properties . 88 Providing descriptions to components. 89 Configuring port structure . 90 Simulating components . 92 Database connection settings . 94 Source components . 95 DB Data Provider Full Load. 96 DB Data Provider Index Load. 99 Text Data Provider . 103 XML via SQL Data Provider . 107 CDC Provider Sybase Replication Server. 115 Transformation components . 131 Character Mapper . 132 Copy Splitter. 135 Data Calculator JavaScript . 136 Data Splitter JavaScript . 143 SQL Executor . 147 Lookup components. 149 DB Lookup. 150 DB Lookup Dynamic. 153 Staging components . 156 DB Staging . 157 Destination components. 163 Preconditions for using DB Data Sink components for bulk loading 163 DB Bulk Load Sybase IQ. 164 DB Data Sink Delete . 175 DB Data Sink Insert . 180 DB Data Sink Update . 186 Text Data Sink. 192 Loader components . 197 IQ Loader File via Load Table . 198 IQ Loader DB via Insert Location . 204 Job components. 210 Start. 211 Project . 211 Synchronizer . 212 Multi-Project . 213 Finish. 215 Error . 215 CHAPTER 6 Sybase ETL Server . 217 Users Guide v

Contents AP PE ND IX A vi Starting and stopping Sybase ETL Server . Starting Sybase ETL Server. Starting Sybase ETL Server as a Windows system service. Stopping Sybase ETL Server . Command line parameters . Using ETL Server to execute projects and jobs . Executing multiple projects concurrently. INI file settings. Default.ini . Monitoring projects and jobs using a Web browser . Troubleshooting Sybase ETL Server . 218 218 218 219 219 221 222 223 224 226 229 Function Reference . uAvg . uMax . uMin . uBitAnd. uBitOr . uIsAscending. uIsBoolean . uIsDate. uIsDescending . uIsEmpty . uIsInteger . uIsFloat . uIsNull . uIsNumber. uBase64Decode. uBase64Encode. uConvertDate . uFromHex . uToHex. uHexDecode . uHexEncode. uToUnicode. uURIDecode. uURIEncode. Time Strings . Modifiers. Date and time calculations . Known limitations . Date and time function list . uDate. uDateTime. 232 232 232 233 233 234 235 235 236 236 237 237 237 238 238 239 239 241 241 241 242 242 242 242 243 244 245 246 246 248 248 231 Sybase ETL

Contents uDay. uDayOfYear . uHour . uQuarter . uIsoWeek . uJuliandate. uMinute . uMonth . uMonthName. uMonthNameShort . uSeconds . uTime . uTimeDiffMs . uWeek . uWeekday . uWeekdayName. uWeekdayNameShort . uYear. uError . uErrortext . uInfo . uWarning. uTrace . uTracelevel. uFileInfo . uFileRead. uFileWrite . uFormatDate . uGlob. uLike. uMatches. uChoice . uFirstDifferent. uFirstNotNull . uElements . uToken . uCommandLine. uGetEnv . uGuid. uMD5. uScriptLoad . uSetEnv. uSetLocale . uSleep . Users Guide 248 249 249 250 250 251 251 251 252 252 253 253 254 254 254 255 256 256 257 257 258 258 258 259 260 260 261 262 263 264 265 266 266 266 267 267 268 268 269 269 269 270 270 274 vii

Contents uSystemFolder . uHostname. uSMTP . uAbs . uCeil . uDiv . uExp . uFloor . uLn . uLog . uMod . uPow, uPower . uRandom. uRound. uSgn. uSqrt. uEvaluate . uAsc, uUnicode . uChr, uUniChr . uCap. uCon, uConcat . uJoin. uLeft . uLength, uLen . uSubstr, uMid . uLPos . uLower, uLow . uLStuff. uLTrim . uRepeat. uReplace . uReverse. uRight. uRPos . uRStuff . uRTrim . uTrim . uUpper, uUpp. uAcos . uAsin . uAtan. uCos. uSin . uTan . viii 274 279 279 282 282 282 283 283 283 284 284 284 285 285 285 286 286 288 288 289 289 289 290 290 290 291 291 291 292 292 293 293 293 294 294 294 295 295 296 296 296 297 297 297 Sybase ETL

Contents AP PE ND IX B Connection Parameters. Interface-specific database options. Database and interface support . Working with the SQLite Persistent interface. Connecting to a SQLite database . Creating a SQLite table . Extracting data from a SQLite database . Working with the Oracle interface . 299 299 304 305 305 306 306 307 AP PE ND IX C Using ETL for Slowly Changing Dimensions . Overview . Case study scenario. Setting up ETL projects for SCD . Understanding target dimension table. Detecting source changes . Filtering the records. Populating the target dimension table . 309 309 310 313 315 315 320 320 AP PE ND IX D Best Practices . 323 Best practices for working with ETL Server . 323 Avoid starting multiple ETL Server sessions . 323 Enter the default port number for command line execution. 323 Use column aliases when entering queries. 324 Do not perform DDL operations in transactional projects. 324 Best practices for working with ETL components . 325 Migrating wide tables. 325 Importing XML file with more than 32 sibling elements . 325 To load last row of source text file to Sybase IQ . 326 Configure Adaptive Server Enterprise for bulk copying. 326 Add less than 35 Data Calculator JavaScript and DB Staging components . 326 Increase the text size for the Adaptive Server ODBC driver . 326 Delimiters in the source text file should not change when project is executed on different platforms . 327 Setting named pipe permission on Windows . 327 Migrating tables to IQ containing LOB columns . 328 Best practices for working with internationalization. 328 Parsing source files with byte-order mark correctly. 328 Set ETL to support UTF-8 encoding. 328 Select correct character set encoding to display Unicode characters properly. 329 Index . 331 Users Guide ix

Contents x Sybase ETL

About This Book Audience This guide is for users of Sybase ETL Development. How to use this book This book contains these chapters: Users Guide Chapter 1, “Sybase ETL,” is an overview of the Sybase ETL architecture and the feature set of Sybase ETL Development and Sybase ETL Server. Chapter 2, “Getting Started,” describes how to get started using Sybase ETL. It familiarizes you with the Sybase ETL Development interface and describes the functions you can perform using the interface. Chapter 3, “Projects and Jobs,” tells you how to create, simulate, and execute projects and jobs. It discusses how to use the simulation mode, and how to use templates to create projects and jobs. Chapter 4, “Advanced Concepts and Tools,” describes the built-in tools that simplify design work. Chapter 5, “Components,” describes the Sybase ETL components that are used to create projects and jobs. Chapter 6, “Sybase ETL Server,” provides information on how to use Sybase ETL Server. Appendix A, “Function Reference,” describes the built-in functions available in Sybase ETL. Appendix B, “Connection Parameters,” describes database configuration options, and provides additional information for some of the supported interfaces. Appendix C, “Using ETL for Slowly Changing Dimensions,” describes slowly changing dimensions (SCDs), including some common SCD scenarios, and explains how to implement these scenarios using Sybase ETL. Appendix D, “Best Practices,” describes recommend

Audience This guide is for users of Sybase ETL Development. How to use this book This book contains these chapters: † Chapter 1, "Sybase ETL," is an overview of the Sybase ETL architecture and the feature set of Sybase ETL Development and Sybase ETL Server. † Chapter 2, "Getting Started," describes how to get started using Sybase ETL.