Data Integrator Reference Guide

trampknowledgeΛογισμικό & κατασκευή λογ/κού

4 Νοε 2013 (πριν από 4 χρόνια και 3 μέρες)

570 εμφανίσεις

Data Integrator Reference Guide
Data Integrator Reference Guide
Data Integrator 11.7.2
for Windows and UNIX
2 Data Integrator Reference Guide

Patents Business Objects owns the following U.S. patents, which may cover products that are offered
and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and
6,289,352.
Trademarks Business Objects owns the following U.S. patents, which may cover products that are offered
and licensed by Business Objects: 5,555,403; 6,247.008 B1; 6,578,027 B2; 6,490,593; and
6,289,352. Business Objects and the Business Objects logo, BusinessObjects, Crystal
Reports, Crystal Xcelsius, Crystal Decisions, Intelligent Question, Desktop Intelligence,
Crystal Enterprise, Crystal Analysis, WebIntelligence, RapidMarts, and BusinessQuery are
trademarks or registered trademarks of Business Objects in the United States and/or other
countries. All other names mentioned herein may be trademarks of their respective owners.
Copyright © 2007 Business Objects. All rights reserved.
Third-party
contributors
Business Objects products in this release may contain redistributions of software licensed
from third-party contributors. Some of these individual components may also be available
under alternative licenses. A partial listing of third-party contributors that have requested or
permitted acknowledgments, as well as required notices, can be found at: http://
www.businessobjects.com/thirdparty
Date April 26, 2007

Data Integrator Reference Guide 3
Contents
Chapter 1 Introduction 15
Who should read this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Business Objects information resources . . . . . . . . . . . . . . . . . . . . . . . 17
Chapter 2 Data Integrator Objects 19
Characteristics of objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Object classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Reusable objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Single-use objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Object options, properties, and attributes . . . . . . . . . . . . . . . . . . . . . . 22
Descriptions of objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Annotation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Batch Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Catch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
COBOL copybook file format . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Conditional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Data flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Datastore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
DTD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Excel workbook format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
File format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Message function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Outbound message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Query transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Contents
4 Data Integrator Reference Guide
Real-time job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138
Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .139
Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Target . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .149
Template table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192
Try . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
While loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194
Work flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
XML file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .198
XML message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .201
XML Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203
XML template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218
Chapter 3 Smart Editor 219
Accessing the smart editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .220
Smart editor options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222
Smart editor toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222
Editor Library pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .223
Tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .223
Find option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224
Editor pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
Syntax coloring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
Selection list and tool tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
Right-click menu and toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . .227
Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .227
Chapter 4 Data Types 229
date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
datetime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233
decimal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .234
double . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
Contents

Data Integrator Reference Guide 5
int . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
interval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
long . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
numeric . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
real . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
timestamp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
varchar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Data type processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Date arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Type conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Conversion to/from Data Integrator internal data types . . . . . . . 249
Conversion of data types within expressions . . . . . . . . . . . . . . . 258
Conversion among number data types . . . . . . . . . . . . . . . . . . . . 258
Conversion between explicit data types . . . . . . . . . . . . . . . . . . . 260
Chapter 5 Transforms 263
Operation codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
Descriptions of transforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Data_Transfer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Date_Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Effective_Date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Hierarchy_Flattening . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
History_Preserving . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Key_Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Map_CDC_Operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Map_Operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
Pivot (Columns to Rows) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Reverse Pivot (Rows to Columns) . . . . . . . . . . . . . . . . . . . . . . . 327
Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
Row_Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Contents
6 Data Integrator Reference Guide
SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .355
Table_Comparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359
Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .368
XML_Pipeline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .378
Chapter 6 Functions and Procedures 383
About functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .384
Functions compared with transforms . . . . . . . . . . . . . . . . . . . . . . . . .384
Operation of a function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .385
Arithmetic in date functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .385
Including functions in expressions . . . . . . . . . . . . . . . . . . . . . . . . . . .385
Kinds of functions you can use in Data Integrator . . . . . . . . . . . . . . . .388
Custom functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .389
Database and application functions . . . . . . . . . . . . . . . . . . . . . . .395
Descriptions of built-in functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .396
abs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .404
add_months . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .405
ascii . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .406
avg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .407
cast . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .408
ceil . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .410
chr . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .411
concat_date_time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .412
count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .413
count_distinct . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .414
current_configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415
current_system_configuration . . . . . . . . . . . . . . . . . . . . . . . . . . .416
dataflow_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .417
datastore_field_value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .418
date_diff . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .419
date_part . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .420
day_in_month . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421
day_in_week . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .422
Contents

Data Integrator Reference Guide 7
day_in_year . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
db_type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
db_version . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
db_database_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
db_owner . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
decode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
exec . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
extract_from_xml . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
file_exists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
fiscal_day . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
floor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440
gen_row_num_by_group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
gen_row_num . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
get_domain_description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
get_env . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
get_error_filename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
get_file_attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
get_monitor_filename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
get_trace_filename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
greatest . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
host_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
ifthenelse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
init_cap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
interval_to_char . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
is_group_changed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
is_set_env . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
is_valid_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460
is_valid_datetime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
is_valid_decimal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
is_valid_double . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
is_valid_int . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464
is_valid_real . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
Contents
8 Data Integrator Reference Guide
is_valid_time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .466
isempty . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .467
isweekend . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .468
job_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .469
julian . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .470
julian_to_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .471
key_generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .472
last_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .473
least . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .474
length . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .476
literal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .477
ln . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .479
load_to_xml . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .480
log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .482
long_to_varchar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .483
lookup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .484
lookup_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .490
lookup_seq . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .499
lower . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .505
lpad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .506
lpad_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .507
ltrim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .509
ltrim_blanks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .510
ltrim_blanks_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .511
mail_to . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .512
match_pattern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .515
match_regex . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .517
max . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .522
min . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .523
mod . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .524
month . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .525
num_to_interval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .526
nvl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .527
Contents

Data Integrator Reference Guide 9
power . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
previous_row_value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
print . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
pushdown_sql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
quarter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
raise_exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
raise_exception_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
rand . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
rand_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
replace_substr . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
replace_substr_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
repository_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
round . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543
rpad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
rpad_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
rtrim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547
rtrim_blanks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
rtrim_blanks_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549
set_env . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
sleep . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551
sql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
sqrt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
smtp_to . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556
substr . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
sum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 560
sysdate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
system_user_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
systime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
table_attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564
to_char . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565
to_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
to_decimal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569
to_decimal_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570
Contents
10 Data Integrator Reference Guide
total_rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .571
trunc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .572
truncate_table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .573
upper . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .574
varchar_to_long . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .575
wait_for_file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .576
week_in_month . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .579
week_in_year . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .580
WL_GetKeyValue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .581
word . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .582
word_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .583
workflow_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .584
year . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .585
About procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .586
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .586
Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .587
Creating stored procedures in a database . . . . . . . . . . . . . . . . . . . . .588
Creating stored procedures in Oracle . . . . . . . . . . . . . . . . . . . . .588
Creating stored procedures in MS SQL Server or Sybase ASE .590
Creating stored procedure in DB2 . . . . . . . . . . . . . . . . . . . . . . . .591
Importing metadata for stored procedures . . . . . . . . . . . . . . . . . . . . .591
Structure of a stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . .592
Calling stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .593
In general . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .593
From queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .595
Without the function wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . .598
Checking execution status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .599
Chapter 7 Data Integrator Scripting Language 601
Language syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .602
Syntax for statements in scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . .602
Syntax for column and table references in expressions . . . . . . . . . . .603
Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .603
Contents

Data Integrator Reference Guide 11
Quotation marks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604
Escape characters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604
Trailing blanks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Variable interpolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Functions and stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
NULL values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
NULL values and empty strings . . . . . . . . . . . . . . . . . . . . . . . . . 608
Debugging and Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 610
Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611
BEGIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
CATCH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
ELSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
END . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
RETURN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
TRY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
Sample scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
Square function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
RepeatString function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
Chapter 8 Metadata in Repository Tables and Views 615
Auditing metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616
AL_AUDIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616
AL_AUDIT_INFO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
Imported metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618
AL_INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618
AL_PCOLUMN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
AL_PKEY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
ALVW_COLUMNATTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620
ALVW_COLUMNINFO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620
Contents
12 Data Integrator Reference Guide
ALVW_FKREL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .621
ALVW_MAPPING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .621
Example use case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .623
Mapping types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .623
How mappings are computed . . . . . . . . . . . . . . . . . . . . . . . . . . . .624
Mapping complexities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .625
Storing nested column-mapping data . . . . . . . . . . . . . . . . . . . . . .626
ALVW_TABLEATTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .629
ALVW_TABLEINFO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .629
Internal metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .629
AL_LANG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .630
AL_LANGXMLTEXT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .630
AL_ATTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .631
AL_SETOPTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .631
AL_USAGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .632
Example use cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .633
ALVW_FUNCINFO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .635
ALVW_PARENT_CHILD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .635
Metadata Integrator tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .636
AL_CMS_BV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .636
AL_CMS_BV_FIELDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .637
AL_CMS_REPORTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .638
AL_CMS_REPORTUSAGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .639
AL_CMS_FOLDER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .640
AL_CMS_UNV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .640
AL_CMS_UNV_OBJ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .641
Operational metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .641
AL_HISTORY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .641
ALVW_FLOW_STAT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .642
Chapter 9 Locales and Multi-Byte Functionality 643
Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .644
Locale support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .646
Contents

Data Integrator Reference Guide 13
Code page support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
Processing with and without UTF-16 Unicode . . . . . . . . . . . . . . 648
Minimizing transcoding in Data Integrator . . . . . . . . . . . . . . . . . . 650
Guidelines for setting locales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
Job Server locale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
Database, database client, and datastore locales . . . . . . . . . . . 652
File format locales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
XML encodings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
Locales Data Integrator automatically sets . . . . . . . . . . . . . . . . . 654
Multi-byte support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
Multi-byte string functions supported in Data Integrator . . . . . . . . . . 654
Numeric data types: assigning constant values . . . . . . . . . . . . . . . . . 655
Assigning a value as a numeric directly . . . . . . . . . . . . . . . . . . . 655
Assigning a value in string format . . . . . . . . . . . . . . . . . . . . . . . . 655
BOM Characters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 656
Round-trip conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657
Column Sizing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657
List of supported locales and encodings . . . . . . . . . . . . . . . . . . . . . . . . . 657
Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 658
Territories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659
Code pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660
Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662
Chapter 10 Reserved Words 665
Contents
14 Data Integrator Reference Guide
chapter
Data Integrator Reference Guide
Introduction
Introduction
About this guide
1
16 Data Integrator Reference Guide
About this guide
Welcome to the Data Integrator Reference Guide. This guide provides
detailed information about the objects, data types, transforms, and functions
in the Data Integrator Designer.
This book contains the following chapters:

Chapter 2: Data Integrator Objects — Describes options, properties, and
attributes for objects, such as data flows and work flows.

Chapter 3: Smart Editor — Describes the editor that can be used to
create scripts, expressions, custom functions.

Chapter 4: Data Types — Describes the data types used in Data
Integrator, and how Data Integrator handles data type conversions.

Chapter 5: Transforms — Describes the transforms included with Data
Integrator and how to use these transforms.

Chapter 6: Functions and Procedures — Describes the functions
included with Data Integrator and how to use these functions.

Chapter 7: Data Integrator Scripting Language — Describes the Data
Integrator scripting language and how you can use this language to
create scripts, expressions, and custom functions.

Chapter 8: Metadata in Repository Tables and Views — Describes the
repository’s reporting tables and views that you can use to analyze an
Data Integrator application.

Chapter 9: Locales and Multi-Byte Functionality — Describes how Data
Integrator supports the setting of locales and multi-byte code pages for
the Designer, Job Server, and Access Server.

Chapter 10: Reserved Words — Lists words that have special meaning in
Data Integrator. You cannot use these words in names that you create,
such as names of data flows.
For source-specific information, such as information pertaining to a particular
back-office application, consult the supplement for that application.
Who should read this guide
This and other Data Integrator product documentation assumes the following:

You are an application developer, consultant or database administrator
working on data extraction, data warehousing, or data integration.

You understand your source and target data systems, DBMS, legacy
systems, business intelligence, and messaging concepts.

You understand your organization’s data needs.
Introduction
About this guide
1
Data Integrator Reference Guide 17

You are familiar with SQL (Structured Query Language).

If you are interested in using this product to design real-time processing
you are familiar with:

DTD and XML Schema formats for XML files

Publishing Web Services (WSDL, HTTP/S and SOAP protocols, etc.)

You are familiar with Data Integrator installation environments: Microsoft
Windows or UNIX.
Business Objects information resources
Consult the Data Integrator Getting Started Guidefor:

An overview of Data Integrator products and architecture

Data Integrator installation and configuration information

A list of product documentation and a suggested reading path
After you install Data Integrator (with associated documentation), you can
view the technical documentation from several locations. To view
documentation in PDF format:

If you accepted the default installation, select Start > Programs >
Business Objects
>
Data Integrator
>
Data Integrator Documentation
and select:

Release Notes—Opens this document, which includes known and
fixed bugs, migration considerations, and last-minute documentation
corrections

Release Summary—Opens the Release Summary PDF, which
describes the latest Data Integrator features

Technical Manuals—Opens a “master” PDF document that has
been compiled so you can search across the Data Integrator
documentation suite

Tutorial—Opens the Data Integrator Tutorial PDF, which you can
use for basic stand-alone training purposes

Select one of the following from the Designer’s Help menu:

Release Notes

Release Summary

Technical Manuals

Tutorial
Other links from the Designer’s Help menu include:

DIZone—Opens a browser window to the DI Zone, an online resource for
the Data Integrator user community)
Introduction
About this guide
1
18 Data Integrator Reference Guide

Knowledge Base—Opens a browser window to Business Objects’
Technical Support Knowledge Exchange forum (access requires
registration)
You can also view and download PDF documentation, including Data
Integrator documentation for previous releases (including Release Summaries
and Release Notes), by visiting the Business Objects documentation Web site
at http://support.businessobjects.com/documentation/.
You can also open Help, using one of the following methods:

Choose Contents from the Designer’s Help menu.

Click objects in the object library or workspace and press F1.
Online Help opens to the subject you selected.
Use Online Help’s links and tool bar to navigate.
chapter
Data Integrator Reference Guide
Data Integrator Objects
Data Integrator Objects
About this chapter
2
20 Data Integrator Reference Guide
About this chapter
This chapter contains reference information about general Data Integrator
objects, such as data flows, jobs, and work flows. Topics include:

Characteristics of objects

Descriptions of objects
Note: For information about source-specific objects, consult the reference
chapter of the Data Integrator supplement document for that source.
Characteristics of objects
This section discusses common characteristics of all Data Integrator objects.
Specifically, this section discusses:

Object classes

Object options, properties, and attributes
Object classes
An object’s class determines how you create and retrieve the object. There
are two classes of objects:

Reusable objects

Single-use objects
Reusable objects
After you define and save a reusable object, Data Integrator stores the
definition in the repository. You can then reuse the definition as often as
necessary by creating calls to the definition.
Most objects created in Data Integrator are available for reuse. You access
reusable objects through the object library.
A reusable object has a single definition; all calls to the object refer to that
definition. If you change the definition of the object in one place, and then
save the object, the change is reflected to all other calls to the object.
A data flow, for example, is a reusable object. Multiple jobs, such as a weekly
load job and a daily load job, can call the same data flow. If the data flow is
changed, both jobs call the new version of the data flow.
When you drag and drop an object from the object library, you are creating a
new reference (or call) to the existing object definition.
Data Integrator Objects
Characteristics of objects
2
Data Integrator Reference Guide 21
You can edit reusable objects at any time independent of the current open
project. For example, if you open a new project, you can go to the object
library, open a data flow, and edit it. The object will remain “dirty” (that is, your
edited changes will not be saved) until you explicitly save it.
Functions are reusable objects that are not available in the object library. Data
Integrator provides access to these objects through the function wizard
wherever they can be used.
Some objects in the object library are not reusable in all instances:

Datastores are in the object library because they are a method for
categorizing and accessing external metadata.

Built-in transforms are “reusable” in that every time you drop a transform,
a new instance of the transform is created.
Saving reusable objects
“Saving” a reusable object in Data Integrator means storing the language that
describes the object to the repository. The description of a reusable object
includes these components:

Properties of the object

Options for the object

Calls this object makes to other objects

Definition of single-use objects called by this object
If an object contains a call to another reusable object, only the call to the
second object is saved, not changes to that object’s definition.
Data Integrator stores the description even if the object does not validate.
Data Integrator saves objects without prompting you:

When you import an object into the repository.

When you finish editing:

Datastores

Flat file formats

XML Schema or DTD formats
You can explicitly save the reusable object currently open in the workspace by
choosing
Save
from the
Project
menu. If a single-use object is open in the
workspace, the
Save
command is not available.
To save all objects in the repository that have changes, choose
Save All
from
the
Project
menu.
Data Integrator also prompts you to save all objects that have changes when
you execute a job and when you exit the Designer.
Data Integrator Objects
Descriptions of objects
2
22 Data Integrator Reference Guide
Single-use objects
Single-use objects appear only as components of other objects. They operate
only in the context in which they were created.
Saving single-use objects
“Saving” a single-use object in Data Integrator means storing the language
that describes the object to the repository. The description of a single-use
object can only be saved as part of the reusable object that calls the single-
use object.
Data Integrator stores the description even if the object does not validate.
Object options, properties, and attributes
Each object is associated with a set of options, properties, and attributes:

Options control the operation of an object. For example, in a datastore,
an option is the name of the database to which the datastore connects.

Properties document an object. For example, properties include the
name, description of an object, and the date on which it was created.
Properties merely describe an object; they do not affect an object’s
operation.
To view properties, right-click an object and select
Properties
.

Attributes provide additional information about an object. Attribute values
may also affect an object’s behavior.
To view attributes, double-click an object from an editor and click the
Attributes
tab.
Descriptions of objects
This section describes each Data Integrator object and tells you how to
access that object.
The following table lists the names and descriptions of objects available in
Data Integrator:
Object
Class
Description
Annotation Single-use Describes a flow, part of a flow, or a diagram in the workspace.
Catch Single-use Specifies the steps to execute if an error occurs in a given
exception group while a job is running.
Data Integrator Objects
Descriptions of objects
2
Data Integrator Reference Guide 23
COBOL
copybook file
format
Reusable Defines the format for a COBOL copybook file source.
Conditional Single-use Specifies the steps to execute based on the result of a
condition.
Batch Job Reusable Defines activities that Data Integrator executes at a given time
including error, monitor and trace messages.
Jobs can be dropped only in the project tree. The object
created is a direct reference to the object in the object library.
Only one reference to a job can exist in the project tree at one
time.
Data flow Reusable Specifies the requirements for extracting, transforming, and
loading data from sources to targets.
Datastore Single-use Specifies the connection information Data Integrator needs to
access a database or other data source. Cannot be dropped.
Document Reusable Available in certain adapter datastores, documents are data
structures that can support complicated nested schemas.
DTD Reusable A description of an XML file or message. Indicates the format
an XML document reads or writes. See also: XML Schema
Excel workbook
format
Reusable Defines the format for an Excel workbook source.
File format Reusable Indicates how flat file data is arranged in a source or target file.
Function Reusable Returns a value.
Log Single-use Records information about a particular execution of a single
job.
Message
function
Reusable Available in certain adapter datastores, message functions can
accommodate XML messages when properly configured.
Outbound
message
Reusable Available in certain adapter datastores, outbound messages
are XML-based, hierarchical communications that real-time
jobs can publish to adapters.
Project Single-use Groups jobs for convenient access.
Query transform Single-use Retrieves a data set that satisfies conditions that you specify.
Real-time job Reusable Defines activities that Data Integrator executes on-demand.
Real-time jobs are created in the Designer, then configured and
run as services associated with an Access Server in the
Administrator. Real-time jobs are designed according to data
flow model rules and run as a request-response system.
Object
Class
Description
Data Integrator Objects
Descriptions of objects
2
24 Data Integrator Reference Guide
Script Single-use Evaluates expressions, calls functions, and assigns values to
variables.
Source Single-use An object from which Data Integrator reads data in a data flow.
Table Reusable Indicates an external DBMS table for which metadata has been
imported into Data Integrator, or the target table into which data
is or has been placed.
A table is associated with its datastore; it does not exist
independently of a datastore connection. A table retrieves or
stores data based on the schema of the table definition from
which it was created.
Target Single-use An object in which Data Integrator loads extracted and
transformed data in a data flow.
Template table Reusable A new table you want added to a database.
All datastores except SAP R/3 datastores have a default template
that you can use to create any number of tables in the datastore.
Data Integrator creates the schema for each instance of a
template table at runtime. The created schema is based on the
data loaded into the template table.
Transform Reusable Performs operations on data sets.
Requires zero or more data sets; produces zero or one data set
(which may be split).
Try Single-use Introduces a try/catch block.
While loop Single-use Repeats a sequence of steps as long as a condition is true.
Work flow Reusable Orders data flows and operations supporting data flows.
XML file Single-use A batch or real-time source or target. As a source, an XML file
translates incoming XML-formatted data into data that D
ATA

I
NTEGRATOR
can process. As a target, an XML file translates the
data produced by a data flow, including nested data, into an
XML-formatted file.
XML message Single-use A real-time source or target. As sources, XML messages
translate incoming XML-formatted requests into data that a
real-time job can process. As targets, XML messages translate
the result of the real-time job, including hierarchical data, into
an XML-formatted response and sends the messages to the
Access Server.
XML Schema Reusable A description of an XML file or message. Indicates the format
an XML document reads or writes. See also: DTD
XML template Single-use A target that creates an XML file that matches a particular input
schema. No DTD or XML Schema is required.
Object
Class
Description
Data Integrator Objects
Annotation
2
Data Integrator Reference Guide 25
Annotation
Class
Single-use
Access
Click the annotation icon in the tool palette, then click in the workspace.
Description
Annotations describe a flow, part of a flow, or a diagram in a workspace. An
annotation is associated with the job., work flow, or data flow where it
appears. When you import or export that job, work flow, or data flow, you
import or export associated annotations.
For more information, see “Creating annotations” on page 59 of the Data
Integrator Designer Guide.
Note: An annotation has no options or properties.
Data Integrator Objects
Batch Job
2
26 Data Integrator Reference Guide
Batch Job
Class
Reusable
Access

In the object library, click the
Jobs
tab.

In the project area, select a project and right-click
Batch Job
.
Description
Note: For information specific to SAP R/3, see the Data Integrator
Supplement for SAP.
A batch job is a set of objects that you can schedule and execute together.
For Data Integrator to execute the steps of any object, the object must be part
of a job.
A batch job can contain the following objects:

Data flows

Sources

Transforms

Targets

Work flows

Scripts

Conditionals

Try/catch blocks

While Loops
You can run batch jobs such that you can automatically recover from jobs that
do not execute successfully. During automatic recovery, Data Integrator
retrieves the results from steps that were successfully completed in the
previous run and executes all other steps. Specifically, Data Integrator
retrieves results from the following types of steps:

Work flows

Data flows

Script statements

Custom functions (stateless type only)

SQL function

EXEC function

get_env function
Data Integrator Objects
Batch Job
2
Data Integrator Reference Guide 27

rand function

sysdate function

systime function
Batch jobs have the following built-in attributes:
Batch and real-time jobs have properties that determine what information
Data Integrator collects and logs when running the job. You can set the
default properties that apply each time you run the job or you can set
execution (run-time) properties that apply for a particular run. Execution
properties override default properties.
To set default properties, select the job in the project area or the object library,
right-click, and choose
Properties
to open the Properties window.
Execution properties are set as you run a job. To set execution properties,
right-click the job in the project area and choose
Execute
. The Designer
validates the job and opens the Execution Properties window.
You can set three types of execution properties:

Parameters

Trace properties

Global variables
For an introduction to using global variables as job properties and
selecting them at runtime, see “Setting global variable values” on
page 306 of the Data Integrator Designer Guide .
Parameters
Use parameter options to help capture and diagnose errors using log,
auditing, statistics collection, or recovery options.
Data Integrator writes log information to one of three files (in the
$LINK_DIR\log\Job Server name\repository name directory):

Monitor log file

Trace log file

Error log file
You can also select a system configuration and a Job Server or server group
from the
Parameters
tab of the Execution Properties window.
Attribute
Description
Name The name of the object. This name appears on the
object in the object library and in the calls to the object.
Description Your description of the job.
Date created The date when the object was created.
Data Integrator Objects
Batch Job
2
28 Data Integrator Reference Guide
Select the
Parameters
tab to set the following options.
Options
Description
Monitor
sample rate
(# of rows)
Enter the number of rows processed before Data Integrator
writes information to the monitor log file and updates job
events. Data Integrator writes information about the status
of each source, target, or transform.
For example, if you enter 1000, Data Integrator updates the
logs after processing 1,000 rows.
The default is 1000. When setting the value, you must
evaluate performance improvements gained by making
fewer calls to the operating system against your ability to
find errors quickly. With a higher monitor sample rate, Data
Integrator collects more data before calling the operating
system to open the file: performance improves. However,
with a higher monitor rate, more time passes before you are
able to see any errors.
Note: If you use a virus scanner on your files, exclude the
Data Integrator log from the virus scan. Otherwise, the virus
scan analyzes the Data Integrator log repeated during the
job execution, which causes a performance degradation.
Print all trace
messages
Select this check box to print all trace messages to the trace
log file for the current Job Server. (For more information on
log files, see “Log” on page 124)
Selecting this option overrides the trace properties set on
the Trace tab.
Disable data
validation
statistics
collection
Select this check box if you do not want to collect data
validation statistics for any validation transforms in this job.
(The default is cleared.)
For more information about data validation statistics, see
“Data Validation dashboards Settings control panel” on
page 60 of the Data Integrator Management Console:
Metadata Reports Guide.
Enable
auditing
Clear this check box if you do not want to collect audit
statistics for this specific job execution. (The default is
selected.)
For more information about auditing, see “Using Auditing”
on page 363 of the Data Integrator Designer Guide .
Data Integrator Objects
Batch Job
2
Data Integrator Reference Guide 29
Enable
recovery
(Batch jobs only) Select this check box to enable the
automatic recovery feature. When enabled, Data Integrator
saves the results from completed steps and allows you to
resume failed jobs. You cannot enable the automatic
recovery feature when executing a job in data scan mode.
See “Automatically recovering jobs” on page 454 of the
Data Integrator Designer Guide for information about the
recovery options.
This property is only available as a run-time property. It is
not available as a default property.
Recover from
last failed
execution
(Batch Job only) Select this check box to resume a failed
job. Data Integrator retrieves the results from any steps that
were previously executed successfully and re-executes any
other steps.
This option is a run-time property. This option is not
available when a job has not yet been executed or when
recovery mode was disabled during the previous run.
Collect
statistics for
optimization
Select this check box if you want to collect statistics that the
Data Integrator optimizer will use to choose an optimal
cache type (in-memory or pageable). This option is not
selected by default.
For more information, see “Caching sources” on page 57 of
the Data Integrator Performance Optimization Guide.
Collect
statistics for
monitoring
Select this check box if you want to display cache statistics
in the Performance Monitor in the Administrator. (The
default is cleared.)
Note: Use this option only if you want to look at the cache
size.
For more information, see “Monitoring and tuning caches”
on page 61 of the Data Integrator Performance Optimization
Guide.
Use collected
statistics
Select this check box if you want the Data Integrator
optimizer to use the cache statistics collected on a previous
execution of the job. (The default is selected.)
For more information, see “Monitoring and tuning caches”
on page 61 of the Data Integrator Performance Optimization
Guide.
Options
Description
Data Integrator Objects
Batch Job
2
30 Data Integrator Reference Guide
Trace properties
Use trace properties to select the information that Data Integrator monitors
and writes to the trace log file during a job. Data Integrator writes trace
messages to the trace log associated with the current Job Server and writes
error messages to the error log associated with the current Job Server.
Distribution
level
Select the level within a job that you want to distribute to
multiple job servers for processing:

Job - The whole job will execute on an available Job
Server.

Data flow - Each data flow within the job can execute on
an available Job Server.

Sub data flow - Each sub data flow (can be a separate
transform or function) within a data flow can execute on
an available Job Server.
For more information, see “Using grid computing to
distribute data flows execution” on page 102 of the Data
Integrator Performance Optimization Guide.
System
configuration
Select the system configuration to use when executing this
job. A system configuration defines a set of datastore
configurations, which define the datastore connections. For
more information, see “Creating and managing multiple
datastore configurations” on page 113 of the Data Integrator
Designer Guide .
If a system configuration is not specified, Data Integrator
uses the default datastore configuration for each datastore.
This option is a run-time property. This option is only
available if there are system configurations defined in the
repository.
Job Server or
server group
Select the Job Server or server group to execute this job. A
Job Server is defined by a host name and port while a
server group is defined by its name. The list contains Job
Servers and server groups linked to the job’s repository.
For an introduction to server groups, see “Server group
architecture” on page 44 of the Data Integrator
Management Console: Administrator Guide.
When selecting a Job Server or server group, remember
that many objects in the Designer have options set relative
to the Job Server’s location. For example:

Directory and file names for source and target files

Bulk load directories
Options
Description
Data Integrator Objects
Batch Job
2
Data Integrator Reference Guide 31
To set trace properties, click the Trace tab. To turn a trace on, select the trace,
click Yes in the Value list, and click OK. To turn a trace off, select the trace,
click No in the Value list, and click OK.
You can turn several traces on and off.
Trace
Description
Row Writes a message when a transform imports or exports a
row.
Session Writes a message when the job description is read from
the repository, when the job is optimized, and when the
job runs.
Work Flow Writes a message when the work flow description is read
from the repository, when the work flow is optimized,
when the work flow runs, and when the work flow ends.
Data Flow Writes a message when the data flow starts, when the
data flow successfully finishes, or when the data flow
terminates due to error.
This trace also reports when the bulk loader starts, any
bulk loader warnings occur, and when the bulk loader
successfully completes.
Data Integrator Objects
Batch Job
2
32 Data Integrator Reference Guide
Transform Writes a message when a transform starts, completes, or
terminates.
Custom
Transform
Writes a message when a custom transform starts and
completes successfully.
Custom Function Writes a message of all user invocations of the
AE_LogMessage function from custom C code.
SQL Functions Writes data retrieved before SQL functions:

Every row retrieved by the named query before the
SQL is submitted in the key_generation function

Every row retrieved by the named query before the
SQL is submitted in the lookup function (but only if
PRE_LOAD_CACHE is not specified).

When mail is sent using the mail_to function.
SQL Transforms Writes a message (using the Table_Comparison
transform) about whether a row exists in the target table
that corresponds to an input row from the source table.
The trace message occurs before submitting the query
against the target and for every row retrieved when the
named query is submitted (but only if caching is not
turned on).
SQL Readers Writes the SQL query block that a script, query transform,
or SQL function submits to the system. Also writes the
SQL results.
Trace
Description
Data Integrator Objects
Batch Job
2
Data Integrator Reference Guide 33
SQL Loaders Writes a message when the bulk loader:

Starts

Submits a warning message

Completes successfully

Completes unsuccessfully, if the
Clean up bulk loader
directory after load
option is selected
Additionally, for Microsoft SQL Server and Sybase ASE,
writes when the SQL Server bulk loader:

Completes a successful row submission

Encounters an error
This instance reports all SQL that Data Integrator submits
to the target database, including:

When a
truncate table
command executes if the
Delete
data from table before loading
option is selected.

Any parameters included in PRE-LOAD SQL
commands

Before a batch of SQL statements is submitted

When a template table is created (and also dropped,
if the
Drop/Create
option is turned on)

When a
delete from table
command executes if auto
correct is turned on (Informix environment only).
This trace also writes all rows that Data Integrator loads
into the target.
Memory Source Writes a message for every row retrieved from the
memory table.
Memory Target Writes a message for every row inserted into the memory
table.
Optimized
Dataflow
For Business Objects consulting and technical support
use.
Tables Writes a message when a table is created or dropped.
The message indicates the datastore to which the
created table belongs and the SQL statement used to
create the table.
Trace
Description
Data Integrator Objects
Batch Job
2
34 Data Integrator Reference Guide
Scripts and
Script Functions
Writes a message when Data Integrator runs a script or
invokes a script function. Specifically, this trace links a
message when:

The script is called. Scripts can be started any level
from the job level down to the data flow level.
Additional (and separate) notation is made when a
script is called from within another script.

A function is called by the script.

The script successfully completes.
Trace Parallel
Execution
Writes messages describing how data in a data flow is
parallel processed.
Access Server
Communication
Writes messages exchanged between the Access Server
and a service provider, including:

The registration message, which tells the Access
Server that the service provider is ready

The request the Access Server sends to the service
to execute

The response from the service to the Access Server

Any request from the Access Server to shut down
Stored
Procedure
Writes a message when Data Integrator invokes a stored
procedure. This trace reports:

When the stored procedure starts

The SQL query submitted for the stored procedure
call

The value (or values) of the input parameter (or
parameters)

The value (or values) of the output parameter (or
parameters)

The return value (if the stored procedure is a stored
function)

When the stored procedure finishes
Audit Data Writes a message when auditing:

Collects a statistic at an audit point

Determines if an audit rule passes or fails
Trace
Description
Data Integrator Objects
Catch
2
Data Integrator Reference Guide 35
Catch
Class
Single-use
Access
With a work flow diagram in the workspace, click the catch icon in the tool
palette.
Description
A catch is part of a serial sequence called a try/catch block. The try/catch
block allows you to specify alternative work flows if errors occur while Data
Integrator is executing a job. Try/catch blocks “catch” groups of errors, apply
solutions that you provide, and continue execution.
For each catch in the try/catch block, specify the following:

One group of exceptions that the catch handles.
To handle more than one group of exceptions, add more catches to the
try/catch block.

The work flow to execute if an exception in the indicated exception group
occurs.
Use an existing work flow or define a work flow in the catch editor.
If an exception is thrown during the execution of a try/catch block, and if no
catch is looking for that exception group, then the exception is handled by
normal error logic.
Do not reference output variables from a try/catch block in any subsequent
steps if you are using (for batch jobs only) the automatic recovery feature.
Referencing such variables could alter the results during automatic recovery.
Also, try/catch blocks can be used within any real-time job component.
However, try/catch blocks cannot straddle a real-time processing loop and the
initialization or clean up component of a real-time job.
Catches have the following attribute:
The following table describes exception groups that you can catch in a try/
catch block.
Attribute
Description
Name The name of the object. This name appears on the object in the
diagram.
Data Integrator Objects
Catch
2
36 Data Integrator Reference Guide
Exception group
Group
Number
Description
Catch All
Exceptions
All Catch All Exceptions
Parser Errors 1 Parser errors
Resolver Errors 2 Resolver errors
Execution Errors 5 Internal errors that occur during the execution of a data
movement specification
Database Access
Errors
7 Generic Database Access Errors
File Access Errors 8 Errors accessing files through file formats
Repository
Access Errors
10 Errors accessing the Data Integrator repository
Connection and
bulk loader errors
12 Errors connecting to database servers and bulk loading to
tables on them
Predefined
Transforms Errors
13 Predefined Transforms Errors
ABAP Generation
Errors
14 ABAP generation errors
R/3 Execution
Errors
15 R/3 execution errors
Email Errors 16 Email errors
System Exception
Errors
17 System exception errors
Engine Abort
Errors
20 Engine abort errors
Data Integrator Objects
COBOL copybook file format
2
Data Integrator Reference Guide 37
COBOL copybook file format
Class
Reusable
Access
In the object library, click the
Formats
tab.
Description
A COBOL copybook file format describes the structure defined in a COBOL
copybook file (usually denoted with a .cpy extension). You store templates for
file formats in the object library. You use the templates to define the file format
of a particular source in a data flow.
The following tables describe the Import (or Edit) COBOL copybook dialog
box options and the Source COBOL copybook Editor options.
Import or Edit COBOL copybook format options
The Import (or Edit) COBOL copybook format dialog boxes include options on
the following tabs:

Format

Data File

Data Access

Field ID

Record Length Field
Data Integrator Objects
COBOL copybook file format
2
38 Data Integrator Reference Guide
Format
The Format tab defines the parameters of the COBOL copybook format.
Table 2-1 :Format tab
Format
option
Description
File name Type or browse to the COBOL copybook file name (usually
has a .cpy extension). This file contains the schema definition.
For added flexibility, you can enter a variable for this option.
Expand
occurs
Specifies the way to handle OCCURS groups. These groups
can be imported either:

with each field within an OCCURS group getting a
sequential suffix for each repetition: fieldname_1,
fieldname_2, etc. (unrolled view), or

with each field within an OCCURS group appearing only
once in the copybook’s schema (collapsed view). For a
collapsed view, the output schema matches the OCCURS
group definition, and for each input record there will be
several output records.
If a copybook contains more than one OCCURS group, you
must check this box.
Ignore
redefines
Determines whether or not to ignore REDEFINES clauses.
Source
format
The format of the copybook source code. Options include:

Free—All characters on the line can contain COBOL
source code.

Smart mode—Data Integrator will try to determine
whether the source code is in Standard or Free format; if
this does not produce the desired result, choose the
appropriate source format (standard or free) manually for
reimport.

Standard—The traditional (IBM mainframe) COBOL
source format, where each line of code is divided into the
following five areas: sequence number (1-6), indicator area
(7), area A (8-11), area B (12-72) and comments (73-80).
Source
codes [start]
Defines the start column of the copybook source file to use
during the import. Typical value is 7 for IBM mainframe
copybooks (standard source format) and 0 for free format.
Source
codes [end]
Defines the end column of the copybook source file to use
during the import. Typical value is 72 for IBM mainframe
copybooks (standard source format) and 9999 for free format.
Data Integrator Objects
COBOL copybook file format
2
Data Integrator Reference Guide 39
Data File
The Data File tab defines the parameters of the data file.
Table 2-2 :Data File tab
Data file
option
Description
Directory Specify the directory that contains the COBOL copybook data
file to import. For added flexibility, you can enter a variable for
this option.
If you include a directory path here, then enter only the file
name in the Name field.
During design, you can specify a file in one of the following
ways:

For a file located on the computer where the Designer
runs, you can use the Browse button.

For a file located on the computer where the Job Server
runs, you must type the path to the file. You can type an
absolute path or a relative path, but the Job Server must
be able to access it.
File name Type or browse to the COBOL copybook data file Name. You
can use variables or wild cards (* or ?).
If you leave Directory blank, then type a full path and file
name here.
Type Specifies the record format—fixed or variable:

Fixed(F)

Variable(V)
Use record length
Include or
exclude
Specifies whether the length information at the beginning of a
record is a part of total record length.
Type Specifies whether variable-length records of the data file
contain information about the length of each record. The
possible values are:

2-byte integer

2-byte followed by 0x0000 (integer followed by two zero
bytes)

4-byte integer

None—No length information at the beginning of each
record
Data Integrator Objects
COBOL copybook file format
2
40 Data Integrator Reference Guide
Data Access
The Data Access tab specifies how Data Integrator accesses the data file. If
both check boxes are cleared, Data Integrator assumes the data file is on the
same computer as the Job Server.
Table 2-3 : Data Access tab
Other
Record size Defines fixed record length in bytes. All records in the file have
this length (padded, if necessary).
Record
trailer length
Specifies the length of extra character padding in bytes at the
end of each record.
Has record
mark
Defines whether there is an extra byte in the beginning of each
record's data.
Integer
format
Describes how the existing data file stores binary data:

Big endian—the most significant byte comes first

Little endian—the least significant byte comes first
Encoding Specifies the character encoding of character data in the data
file.
Skip first Defines the number of data records to skip before starting to
process the file. For added flexibility, you can enter a variable
for this option.
Read total Defines the number of records to read and process. For added
flexibility, you can enter a variable for this option.
Data access
option
Description
FTP Select to use FTP to access the data file.
Host Type the computer (host) name, fully qualified domain name,
or IP address of the computer where the data file resides.
User Type the FTP user name.
Password Type the FTP user password.
Directory Type or browse to the directory that contains the COBOL
copybook data file to import.
If you include a directory path here, then enter only the file
name in the Name field.
Data file
option
Description
Data Integrator Objects
COBOL copybook file format
2
Data Integrator Reference Guide 41
Field ID
The Field ID tab allows you to create rules for identifying which records
represent which schemas.
Table 2-4 :Field ID tab
Record Length Field
The Record Length Field tab lets you identify the field that contains the length
of the schema’s record.
File name Type or browse to the COBOL copybook data file Name. You
can use variables or wild cards (* or ?).
If you leave Directory blank, then type a full path and file
name here.
Custom Select to use a custom executable to access the data file.
Executable Type the name of the program to read data file.
User Type the user name.
Password Type the password.
Arguments Include any custom program arguments.
Field ID option
Description
Use field <FIELD NAME>
as ID
Select to set a value for the field selected in the
top pane. Clear to not set a value for that field.
Edit Changes the selected value in the Values pane
to editable text.
Delete Deletes the selected value in the Values pane.
Insert above Inserts a new value in the Values pane above the
selected value.
Insert below Inserts a new value in the Values pane below the
selected value.
Data access
option
Description
Data Integrator Objects
COBOL copybook file format
2
42 Data Integrator Reference Guide
Table 2-5 :Record Length Field tab
COBOL copybook source options
The source editor includes the following COBOL copybook options on the
following tabs:

Source

Field clauses

Data File

Data Access
Record Length Field column
Description
Schema The data schemas in the copybook.
Record length field Click to enable a drop-down menu where
you select a field (one per schema) that
contains the record's length.
Offset The value that results in the total record
length when added to the value in the
Record length field. The default value for
offset is 4; however, you can change it to
any other numeric value.
Data Integrator Objects
COBOL copybook file format
2
Data Integrator Reference Guide 43
Source
Table 2-6 :Source tab
Source
option
Description
Make port
Makes the source table an embedded data flow port. For more
information, see “Creating embedded data flows” on page 286
of the Data Integrator Designer Guide.
Performance
Join rank Indicates the rank of the source relative to other tables and
files in the data flow when creating a join. Data Integrator joins
sources with higher join ranks before joining sources with
lower join ranks. For more information, see “Join ordering” on
page 150 of the Data Integrator Performance Optimization
Guide.
Note: Must be a non-negative integer. When set to its default
value (zero), Data Integrator determines join order.
Cache Indicates whether Data Integrator should read the required
data from the source and load it into memory or pageable
cache. For more information, see “Source” on page 139.
Error handling
Log

data
conversion
warnings
Determines whether to include data-type conversion warnings
in the Data Integrator error log. Defaults to Yes.
Maximum
warnings to
log
If Log data conversion warnings is enabled, you can limit how
many warnings Data Integrator logs. Defaults to {no limit}.
Include file name column
Include file
name
column
Determines whether to add a column that contains the source
file name in the source output. Defaults to No.
Change the value to Yes when you want to identify the source
file in situations such as the following:

You specified a wildcard character to read multiple source
COBOL copybooks at one time

You load from different source copybooks on different runs
Modify
If the file name is included, this button enables you to modify
File name column and Column size.
File name
column
If the file name is included, the name of the column that holds
the source file name. Defaults to DI_FILENAME.
Data Integrator Objects
COBOL copybook file format
2
44 Data Integrator Reference Guide
Field clauses
The Field clauses tab displays the attributes for a selected column.
Table 2-7 :Field clauses tab
Column size
If the file name is included, the size (in characters) of the
column that holds the source file name.
Defaults to 100. If the size of the file name column is not large
enough to store the file name, truncation occurs from the left.
Include path
If the file name is included, determines whether to include the
full path name of the source file. Defaults to No.
Field clauses option
Description
Possible values Enter values here to force Data Integrator to only
process rows that contain the specified value(s).
Separate multiple values with the pipe character (|).
You can click the ellipses button to open the smart
editor; for details on how to use the smart editor, see
Chapter 3: Smart Editor.
Level The level number (01-50) assigned to the field in the
source record definition.
Original name The name of the field in the copybook.
Original picture The PICTURE clause of the field in the copybook.
Original usage The USAGE clause of the field in the copybook.
Min occurs Minimum number of occurrences for this field (if this
field is a part of an OCCURS group).
Max occurs Maximum number of occurrences for this field (if this
field is a part of an OCCURS group).
Occurs depending on Specifies the repetition counter field name for the
ODO (OCCURS DEPENDING ON).
Redefines Specifies the name of another field that this one
REDEFINES.
Sign separate Specifies whether the sign is stored separately from
the field’s value.
Source
option
Description
Data Integrator Objects
COBOL copybook file format
2
Data Integrator Reference Guide 45
Data File
See “Data File” on page 39.
Data Access
See “Data Access” on page 40.
Sign position Specifies whether the sign is LEADING or TRAILING.
Multiply by Specifies whether the field needs to be scaled
(multiplied or divided by a certain number). For
example, if the field's PICTURE clause is 9(5)P(3),
the value of the field from the data file will be
multiplied by 1000.
Field clauses option
Description
Data Integrator Objects
Conditional
2
46 Data Integrator Reference Guide
Conditional
Class
Single-use
Access
With a work flow diagram in the workspace, click the conditional icon in the
tool palette.
Description
A conditional implements if/then/else logic in a work flow.
For each conditional, specify the following:

If
: A Boolean expression defining the condition to evaluate.
The expression evaluates to TRUE or FALSE. You can use constants,
functions, variables, parameters, and standard operators to construct the
expression. For information about expressions, see Chapter 3: Smart
Editor.
Note: Do not put a semicolon (;) at the end of your expression in the
If

box.

Then
: A work flow to execute if the condition is TRUE.

Else
: A work flow to execute if the condition is FALSE.
This branch is optional.
The
Then
and
Else
branches of the conditional can be any steps valid in a
work flow, including a call to an existing work flow.
Conditionals have the following attribute:
Attribute
Description
Name The name of the object. This name appears on the object
in the diagram.
Data Integrator Objects
Data flow
2
Data Integrator Reference Guide 47
Data flow
Class
Reusable
Access

In the object library, click the
Data Flows
tab.

With a work flow diagram in the workspace, click the data flow icon in the
tool palette.
Description
A data flow extracts, transforms, and loads data.
You can define parameters to pass values into the data flow. You can also
define variables for use inside the data flow.
When Data Integrator executes data flows, it optimizes the extract, transform,
and load requirements into commands to the DBMS and commands executed
internally. Where it can, Data Integrator runs these operations in parallel.
By definition, a data flow can contain the following objects:
SourcesFiles, tables, XMl files, XML messages (real-time jobs only),
documents, or pre-defined template tables
Targets Files, tables, XML files, XML messages (real-time jobs only),
outbound messages, documents, XML template, or template tables
TransformsQuery is the most commonly used transform
You can view the SQL code Data Integrator generates for table sources in
data flows and improve your data flow design accordingly. See “Viewing SQL”
on page 41 of the Data Integrator Performance Optimization Guide.
Data flows have several built-in properties.
If you delete a data flow from the object library, calls to the object are replaced
with an icon indicating that the calls are no longer valid in the workspace.
Attribute
Description
Name The name of the object. This name appears on the
object in the object library and in the calls to the object.
Description Your description of the data flow.
Data Integrator Objects
Data flow
2
48 Data Integrator Reference Guide
Executing jobs only once
You can ensure that a job executes a data flow only one time by selecting the
Execute only once check box on the data flow Properties window. When you
select this check box, Data Integrator executes only the first occurrence of the
data flow and skips subsequent occurrences of it in the job. You might use
this feature when developing complex jobs with multiple paths, such as those
containing try/catch blocks or conditionals, and you want to ensure that Data
Integrator executes a particular data flow only once. Before selecting the
Execute only once, note that:

If you design a job to execute the same Execute only once data flow in
parallel flows, Data Integrator only executes the first occurrence of that
data flow and you cannot control which one Data Integrator executes first.
Subsequent flows wait until Data Integrator processes the first one. The
engine provides a wait message for each subsequent data flow. Since
only one Execute only once data flow can execute in a single job, the
engine skips subsequent data flows and generates a second trace
message for each, “Data flow n did not run more than one time. It is an
execute only once flow.”

The Execute only once

data flow option overrides the
Recover as a unit

work flow option and the Enable recovery job option.
For example, if you design a job to execute more than one instance of the
same Execute only once data flow and execute the job in recovery
mode, if the job fails Data Integrator checks to see if the data flow ran
successfully. If any instance ran successfully, Data Integrator displays the
following trace message, “Data flow n recovered successfully from
previous run.” If no instance ran successfully, Data Integrator executes
the data flow in the next run and skips subsequent instances of that data
flow.
Parallel processing
You can run certain transforms and functions in parallel by entering a number
in the Degree of parallelism box on your data flow Properties window. When
you drop a transform into the data flow and a function into each transform, the
number you enter in the Degree of parallelism box is the maximum number
of instances that can be generated for each transform or function in the data
flow. For more information, see “Degree of parallelism” on page 74 of the
Data Integrator Performance Optimization Guide.
Data Integrator Objects
Data flow
2
Data Integrator Reference Guide 49
Caching data
You can cache data to improve performance of operations such as joins,
groups, sorts, lookups, and table comparisons. You can select one of the
following values for the Cache type option on your data flow Properties
window:

In-Memory—Choose this value if your data flow processes a small
amount of data that can fit in the available memory.

Pageable—This value is the default.
Note: For data flows that you created prior to version 11.7, the default