Business objects DATA INTEGRATOR 11.0.1 User Manual

Download

Data Integrator Designer Guide

Data Integrator 11.0.1

for Windows and UNIX

Trademarks

Patents

If you find any problems with this documentation, please report them to Business Objects S.A. in writing at documentation@businessobjects.com.

Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners.

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.

2 Data Integrator Designer Guide

Chapter 1 Contents 3

Chapter 2 Welcome 17

Overview of this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Audience and assumptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

More Data Integrator product documentation . . . . . . . . . . . . . . . . . . . . . . 20

Chapter 3 Logging in to the Designer 21

Creating a Data Integrator repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Associating the repository with a Job Server . . . . . . . . . . . . . . . . . . . . . . . 23

Entering repository information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Version restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Oracle login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Microsoft SQL Server login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Informix login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

IBM DB2 login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Sybase ASE login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Resetting users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Chapter 4 Designer user interface 29

Data Integrator objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Reusable objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Single-use objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Object hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Designer window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

Menu bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

Project menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Edit menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

View menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Data Integrator Designer Guide 3

Contents

Tools menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36

Debug menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37

Validation menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38

Window menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38

Help menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39

Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39

Project area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Tool palette . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Workspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44

Moving objects in the workspace area . . . . . . . . . . . . . . . . . . . . . . . . . 44

Connecting and disconnecting objects . . . . . . . . . . . . . . . . . . . . . . . . .45

Describing objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Scaling the workspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46

Arranging workspace windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

Closing workspace windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

Local object library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

Object editors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50

Working with objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Creating new reusable objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Changing object names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52

Viewing and changing object properties . . . . . . . . . . . . . . . . . . . . . . . .53

Creating descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57

Creating annotations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Saving and deleting objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60

Searching for objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62

General and environment options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64

Designer — Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Designer — General . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66

Designer — Graphics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67

Designer — Central Repository Connections . . . . . . . . . . . . . . . . . . . .67

Data — General . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68

Job Server — Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Job Server — General . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68

4 Data Integrator Designer Guide

Contents

Chapter 5 Projects and Jobs 69

Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Objects that make up a project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Creating new projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Opening existing projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Saving projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Creating jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Naming conventions for objects in jobs . . . . . . . . . . . . . . . . . . . . . . . . 75

Chapter 6 Datastores 77

What are datastores? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

Database datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Mainframe interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Defining a database datastore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Changing a datastore definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Browsing metadata through a database datastore . . . . . . . . . . . . . . . 90

Importing metadata through a database datastore . . . . . . . . . . . . . . . 96

Memory datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Adapter datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Defining an adapter datastore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

Browsing metadata through an adapter datastore . . . . . . . . . . . . . . . 111

Importing metadata through an adapter datastore . . . . . . . . . . . . . . . 111

Creating and managing multiple datastore configurations . . . . . . . . . . . . 112

Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

Portability solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Job portability tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120

Rename Owner dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Defining system configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

Chapter 7 File Formats 129

What are file formats? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

File format editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

Data Integrator Designer Guide 5

Contents

Creating file formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133

Creating COBOL copybook file formats . . . . . . . . . . . . . . . . . . . . . . . . . .141

Editing file formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142

Replicating and renaming file formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

File format features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143

Reading multiple files at one time . . . . . . . . . . . . . . . . . . . . . . . . . . . .143

Number formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .144

Ignoring rows with specified markers . . . . . . . . . . . . . . . . . . . . . . . . . 144

Date formats at the field level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145

Error handling for flat-file sources . . . . . . . . . . . . . . . . . . . . . . . . . . . .146

File transfers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .150

Custom transfer system variables for flat files . . . . . . . . . . . . . . . . . .150

Custom transfer options for flat files . . . . . . . . . . . . . . . . . . . . . . . . . .152

Setting custom transfer options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .153

Design tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .155

Web log support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .156

Word_ext function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157

Concat_date_time function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157

WL_GetKeyValue function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

Chapter 8 Data Flows 161

What is a data flow? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

Naming data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .163

Data flow example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .163

Steps in a data flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164

Data flows as steps in work flows . . . . . . . . . . . . . . . . . . . . . . . . . . . .164

Intermediate data sets in a data flow . . . . . . . . . . . . . . . . . . . . . . . . .165

Operation codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .165

Passing parameters to data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .166

Creating and defining data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

Source and target objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .168

Source objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .169

Target objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .169

6 Data Integrator Designer Guide

Contents

Adding source or target objects to data flows . . . . . . . . . . . . . . . . . . 170

Template tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

Transforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173

Transform editors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

Adding transforms to data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176

Query transform overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

Adding a Query transform to a data flow . . . . . . . . . . . . . . . . . . . . . . 178

Query editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

Data flow execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Audit Data Flow Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Chapter 9 Work Flows 183

What is a work flow? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Steps in a work flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Order of execution in work flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186

Example of a work flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Creating work flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Conditionals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189

While loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Design considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Defining a while loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

Using a while loop with View Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Try/catch blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Categories of available exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . 196

Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197

Debugging scripts using the print function . . . . . . . . . . . . . . . . . . . . . 199

Chapter 10 Nested Data 201

What is nested data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203

Representing hierarchical data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203

Operations on nested data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206

Overview of nested data and the Query transform . . . . . . . . . . . . . . 207

FROM clause construction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Data Integrator Designer Guide 7

Contents

Nesting columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .210

Using correlated columns in nested data . . . . . . . . . . . . . . . . . . . . . . 212

Distinct rows and nested data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

Grouping values across nested schemas . . . . . . . . . . . . . . . . . . . . . .213

Unnesting nested data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .214

How transforms handle nested data . . . . . . . . . . . . . . . . . . . . . . . . . .217

Formatting XML documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217

.Using XML Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217

Using Document Type Definitions (DTDs) . . . . . . . . . . . . . . . . . . . . .221

Generating DTDs and XML Schemas from an NRDM schema . . . . .224

XML extraction and parsing for columns . . . . . . . . . . . . . . . . . . . . . . . . . . 225

Example Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .226

Chapter 11 Real-time jobs 233

Request-response message processing . . . . . . . . . . . . . . . . . . . . . . . . . . 235

What is a real-time job? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236

Real-time versus batch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236

Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236

Real-time job examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238

Creating real-time jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240

Real-time job models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240

Using real-time job models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242

Creating a real-time job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244

Real-time source and target objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . .246

Secondary sources and targets . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248

Transactional loading of tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248

Design tips for data flows in real-time jobs . . . . . . . . . . . . . . . . . . . . .249

Testing real-time jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250

Executing a real-time job in test mode . . . . . . . . . . . . . . . . . . . . . . . . 250

Using View Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251

Using an XML file target . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251

Building blocks for real-time jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .252

Supplementing message data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .252

8 Data Integrator Designer Guide

Contents

Branching data flow based on a data cache value . . . . . . . . . . . . . . 254

Calling application functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259

Designing real-time applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

Reducing queries requiring back-office application access . . . . . . . . 260

Messages from real-time jobs to adapter instances . . . . . . . . . . . . . 261

Real-time service invoked by an adapter instance . . . . . . . . . . . . . . 261

Chapter 12 Embedded Data Flows 263

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265

Example of when to use embedded data flows . . . . . . . . . . . . . . . . . . . . 266

Creating embedded data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Using the Make Embedded Data Flow option . . . . . . . . . . . . . . . . . . 267

Creating embedded data flows from existing flows . . . . . . . . . . . . . . 269

Using embedded data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Testing embedded data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

Troubleshooting embedded data flows . . . . . . . . . . . . . . . . . . . . . . . 273

Chapter 13 Variables and Parameters 275

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

The Variables and Parameters window . . . . . . . . . . . . . . . . . . . . . . . . . . 278

Using local variables and parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281

Passing values into data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281

Defining local variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Defining parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283

Using global variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

Creating global variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

Viewing global variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Setting global variable values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Local and global variable rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

Naming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

Replicating jobs and work flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

Importing and exporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

Data Integrator Designer Guide 9

Contents

Environment variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .293

Setting file names at run-time using variables . . . . . . . . . . . . . . . . . . . . . . 293

Chapter 14 Executing Jobs 295

Overview of Data Integrator job execution . . . . . . . . . . . . . . . . . . . . . . . .297

Preparing for job execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297

Validating jobs and job components . . . . . . . . . . . . . . . . . . . . . . . . . .298

Ensuring that the Job Server is running . . . . . . . . . . . . . . . . . . . . . . . 299

Setting job execution options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .299

Executing jobs as immediate tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .300

Monitor tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .301

Log tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .302

Debugging execution errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .303

Using Data Integrator logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .303

Examining target data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307

Changing Job Server options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

Chapter 15 Design and Debug 311

Using View Where Used . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .313

From the object library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .314

From the workspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317

Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317

Using View Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .318

Accessing View Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320

Viewing data in the workspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .321

View Data properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .323

View Data tool bar options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .327

View Data tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

Using the interactive debugger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .333

Before starting the interactive debugger . . . . . . . . . . . . . . . . . . . . . . .333

Starting and stopping the interactive debugger . . . . . . . . . . . . . . . . . .338

Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .341

Menu options and tool bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .348

10 Data Integrator Designer Guide

Contents

Viewing data passed by transforms . . . . . . . . . . . . . . . . . . . . . . . . . . 350

Push-down optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351

Comparing Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351

Overview of the Difference Viewer window . . . . . . . . . . . . . . . . . . . . 354

Navigating through differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

Using Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

Auditing objects in a data flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

Accessing the Audit window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363

Defining audit points, rules, and action on failure . . . . . . . . . . . . . . . 364

Guidelines to choose audit points . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Auditing embedded data flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Resolving invalid audit labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373

Viewing audit results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374

Chapter 16 Exchanging metadata 377

Metadata exchange . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

Importing metadata files into Data Integrator . . . . . . . . . . . . . . . . . . . 379

Exporting metadata files from Data Integrator . . . . . . . . . . . . . . . . . . 380

Creating Business Objects universes . . . . . . . . . . . . . . . . . . . . . . . . . . . 382

Mappings between repository and universe metadata . . . . . . . . . . . 383

Attributes that support metadata exchange . . . . . . . . . . . . . . . . . . . . . . . 384

Chapter 17 Metadata Reporting 385

Repository reporting tables and views . . . . . . . . . . . . . . . . . . . . . . . . . . . 387

Metadata reporting tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389

Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389

Viewing metadata reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390

Metadata reporting tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Viewing metadata reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392

Metadata analysis categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393

Repository summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393

Data Integrator Designer Guide 11

Contents

Datastore analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .397

Operational statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .408

Universe analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416

Business view analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .419

Report analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421

Dependency analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425

Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .429

Chapter 18 Recovery Mechanisms 435

Recovering from unsuccessful job execution . . . . . . . . . . . . . . . . . . . . . .437

Automatically recovering jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .437

Enabling automated recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .437

Marking recovery units . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438

Running in recovery mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .440

Ensuring proper execution path . . . . . . . . . . . . . . . . . . . . . . . . . . . . .441

Using try/catch blocks with automatic recovery . . . . . . . . . . . . . . . . .442

Ensuring that data is not duplicated in targets . . . . . . . . . . . . . . . . . .443

Using preload SQL to allow re-executable data flows . . . . . . . . . . . . .444

Manually recovering jobs using status tables . . . . . . . . . . . . . . . . . . . . . .446

Processing data with problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .448

Using overflow files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .448

Filtering missing or bad values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .449

Handling facts with missing dimensions . . . . . . . . . . . . . . . . . . . . . . . 452

Chapter 19 Data Cleansing 453

Cleansing data in Data Integrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .455

Parse and standardize names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .455

Correct and standardize addresses . . . . . . . . . . . . . . . . . . . . . . . . . .456

Match and consolidate data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .456

Installation and configuration for data cleansing . . . . . . . . . . . . . . . . . . . .456

Using data cleansing transforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458

Data cleansing job templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .459

Business logic in pre-defined templates . . . . . . . . . . . . . . . . . . . . . . .460

12 Data Integrator Designer Guide

Contents

Template tokens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

Storing and finding templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462

Creating template files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463

Support files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464

Migrating Firstlogic jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465

Debugging data cleansing jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465

Debugging with Address_Enhancement or Name_Parsing . . . . . . . . 465

Debugging with Match_Merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465

Data cleansing limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466

Data cleansing transforms and LONG data type . . . . . . . . . . . . . . . . 466

Data cleansing transforms and column dependencies . . . . . . . . . . . 466

Chapter 20 Techniques for Capturing Changed Data 467

Understanding changed-data capture . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Full refresh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Capturing only changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Source-based and target-based CDC . . . . . . . . . . . . . . . . . . . . . . . . 470

Using CDC with Oracle sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472

Setting up Oracle CDC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

CDC datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

Importing CDC data from Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474

Configuring a CDC source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477

Using CDC with DB2 sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482

Guaranteed delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483

Setting up DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483

Setting up Data Integrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484

CDC Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485

CDC datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487

Importing CDC data from DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489

Configuring a DB2 CDC source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489

Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

Using CDC with IBM mainframe sources . . . . . . . . . . . . . . . . . . . . . . . . . 492

Guaranteed Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493

Data Integrator Designer Guide 13

Contents

Setting up IBM mainframe data sources . . . . . . . . . . . . . . . . . . . . . . . 493

Setting up Data Integrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496

CDC Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .497

IBM Connector CDC Datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498

Importing CDC data from IBM Connect . . . . . . . . . . . . . . . . . . . . . . .500

Configuring an IBM Connector CDC source . . . . . . . . . . . . . . . . . . . .500

Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502

Using CDC with Attunity mainframe sources . . . . . . . . . . . . . . . . . . . . . .503

Setting up Attunity CDC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .505

Setting up Data Integrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506

Importing mainframe CDC data . . . . . . . . . . . . . . . . . . . . . . . . . . . . .508

Configuring a mainframe CDC source . . . . . . . . . . . . . . . . . . . . . . . .509

Using mainframe check-points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .510

Using CDC with Microsoft SQL Server databases . . . . . . . . . . . . . . . . . . 512

Overview of CDC for SQL Server databases . . . . . . . . . . . . . . . . . . .512

Setting up SQL Replication Server for CDC . . . . . . . . . . . . . . . . . . . .514

Setting up Data Integrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515

Importing SQL Server CDC data . . . . . . . . . . . . . . . . . . . . . . . . . . . . .517

Configuring an SQL Server CDC source . . . . . . . . . . . . . . . . . . . . . .519

Using CDC with timestamp-based sources . . . . . . . . . . . . . . . . . . . . . . . .522

Processing timestamps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523

Overlaps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .526

Types of timestamps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532

Timestamp-based CDC examples . . . . . . . . . . . . . . . . . . . . . . . . . . .534

Additional job design tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .543

Using CDC for targets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .544

Chapter 21 Monitoring jobs 545

Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .547

SNMP support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

About the Data Integrator SNMP agent . . . . . . . . . . . . . . . . . . . . . . .547

Job Server, SNMP agent, and NMS application architecture . . . . . . .548

About SNMP Agent’s Management Information Base (MIB) . . . . . . .549

14 Data Integrator Designer Guide

Contents

About an NMS application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552

Configuring Data Integrator to support an NMS application . . . . . . . 553

Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565

Index 567

Data Integrator Designer Guide 15

Contents

16 Data Integrator Designer Guide

Data Integrator Designer Guide

Welcome

chapter

Welcome

Welcome to the Data Integrator Designer Guide. The Data Integrator Designer provides a graphical user interface (GUI) development environment in which you define data application logic to extract, transform, and load data from databases and applications into a data warehouse used for analytic and on-demand queries. You can also use the Designer to define logical p aths for processing message-based queries and transactions from Web-based, frontoffice, and back-office applications.

This chapter discusses these topics:

• Overview of this document

• Audience and assumptions

• More Data Integrator product documentation

18 Data Integrator Designer Guide

Overview of this document

The book contains two kinds of information:

• Conceptual information that helps you understand the Data Integrator

Designer and how it works

• Procedural information that explains in a step-by-step manner how to

accomplish a task

You will find this book most useful:

• While you are learning about the product

• While you are performing tasks in the design and early testing phase of

your data-movement projects

• As a general source of information during any phase of your projects

Audience and assumptions

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 data systems, RDBMS, business

intelligence, and messaging concepts.

• You understand your organization’s data needs.

• You are familiar with SQL (Structured Query Language).

• If you are interested in using this product to design real-time processing,

you should be familiar with:

• DTD and XML Schema formats for XML files

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

• You are familiar Data Integrator installation environments—Microsoft

Windows or UNIX.

Welcome

Overview of this document

Data Integrator Designer Guide 19

Welcome

Logging in to the Designer

chapter

Logging in to the Designer

This chapter describes how to log in to the Data Integrator Designer. When you log in to the Data Integrator Designer, you are actually logging in to the database you defined for the Data Integrator repository.

Data Integrator repositories can reside on Oracle, Microsoft SQL Server, Informix, IBM DB2, Sybase ASE.

This chapter discusses:

• Creating a Data Integrator repository

• Associating the repository with a Job Server

• Entering repository information

• Resetting users

22 Data Integrator Designer Guide

Logging in to the Designer

Creating a Data Integrator repository

You must configure a local repository to log in to Data Integrator. Typically, you create a repository during installation. However, you can create a repository at any time using the Data Integrator Repository Manager.

To create a local repository



1. Define a database for the local repository using your database

management system.

2. From the Start menu, choose Programs > BusinessObjects Data

Integrator version

Integrator in the Data Integrator program group).

3. In the Repository Manager window, enter the database connection

information for the repository and select Local for repository type.

4. Click Create. This adds the Data Integrator repository schema to the

specified database.

> Repository Manager (assuming you installed Data

Associating the repository with a Job Server

Each repository must be associated with at least one Job Server, which is the process that starts jobs. When running a job from a repository , you select one of the associated repositories. In production environments, you can balance loads appropriately. You can link any number of repositories to a single Job Server. The same Job Server can run jobs stored on multiple repositories.

Typically, you define a Job Server and link it to a repository during installation. However, you can define or edit Job Servers or links between repositories and Job Servers at any time using the Data Integrator Server Manager.

To create a Job Server for your local repository



1. Open the Data Integrator Server Manager.

From the Start menu, choose Programs > BusinessObjects Data Integrator version > Server Manager (assuming you installed Data

Integrator in the Data Integrator program group).

2. See “To configure Job Servers” on page 79 of the Data Integrator Getting

Started Guide for detailed instructions.

Data Integrator Designer Guide 23

Logging in to the Designer

Entering repository information

To log in, enter the connection information for your Data Integrator repository. The required information varies with the type of database containing the repository. This section discusses:

• Version restrictions

• Oracle login

• Microsoft SQL Server login

• Informix login

• IBM DB2 login

• Sybase ASE login

Version restrictions

Your repository version must be associated with the same major release as the Designer and must be less than or equal to the version of the Designer. For example, Designer 5.2 can access repositories 5.0, 5.1, and 5.2 (equal to or less than), but not repository 4.4 (different major release version). So, in this example, repository 5.0 is the earliest repository version that could be used with Designer version 5.2.

During login, Data Integrator alerts you if there is a mismatch between your Designer version and your repository version.

After you log in, you can view Data Integrator and repository versions by selecting Help > About Data Integrator.

24 Data Integrator Designer Guide

Some features in the current release of the Designer might not be supported if you are not logged in to the latest version of the repository.

Oracle login

From the Windows Start menu, select Programs > BusinessObjects Data Integrator version > Data Integrator Designer.

In the Repository Login window, complete the following fields:

• Database type — Select Oracle.

• Databa se connection name — The TNSnames.ora entry or Net Service

• User name and Password —The user name and password for a Data

• Remember — Check this box if you want the Designer to store this

Logging in to the Designer

Entering repository information

Name of the database.

Integrator repository defined in an Oracle database.

information for the next time you log in.

Microsoft SQL Server login

From the Windows Start menu, select Programs > BusinessObjects Data Integrator version > Data Integrator Designer.

For a Microsoft SQL Server repository, you must complete the following fields:

• Database type — Select Microsoft_SQL_Server.

• Database server name —The database server name.

Data Integrator Designer Guide 25

Logging in to the Designer

Entering repository information

Informix login

• Database name — The name of the specific database to which you are

connecting.

• Windows authentication — Select to have Microsoft SQL Server

validate the login account name and password using information from the Windows operating system; clear to authenticate using the existing Microsoft SQL Server login account name and password and complete the User name and Password fields.

• User name and Password — The user name and password for a Data

Integrator repository defined in a Microsoft SQL Server database.

• Remember — Check this box if you want the Designer to store this

information for the next time you log in.

From the Windows Start menu, select Programs > BusinessObjects Data Integrator version > Data Integrator Designer.

For an Informix repository, you must complete the following fields:

• Database type — Select Informix.

• Informix datasource — The data source name.

• User name and Password — The user name and password for a Data

Integrator repository defined in an Informix database.

• Remember — Check this box if you want the Designer to store this

information for the next time you log in.

IBM DB2 login

From the Windows Start menu, select Programs > BusinessObjects Data Integrator version > Data Integrator Designer.

For a DB2 repository, you must complete the following fields:

• Database type — Select DB2.

• DB2 datasource — The data source name.

• User name and Password — The user name and password for a Data

Integrator repository defined in a DB2 database.

• Remember — Check this box if you want the Designer to store this

information for the next time you log in.

26 Data Integrator Designer Guide

Sybase ASE login

From the Windows Start menu, select Programs > BusinessObjects Data Integrator version > Data Integrator Designer.

For a Sybase ASE repository, you must complete the following fields:

• Database type — Select Sybase ASE.

• Database server name — Enter the database’s server name.

• Databa se name — Enter the name of the specific dat abase to which you

are connecting.

• User name and Password — Enter the user name and password for this

database.

• Remember — Check this box if you want the Designer to store this

information for the next time you log in.

Resetting users

Occasionally, more than one person may attempt to log in to a single repository . If this happens, the Reset Users window appears, listing the users and the time they logged in to the repository.

Logging in to the Designer

Resetting users

From this window, you have several options. You can:

• Reset Users to clear the users in the repository and set yourself as the

currently logged in user.

• Continue to log in to the system regardless of who else might be

connected.

• Exit to terminate the login attempt and close the session.

Data Integrator Designer Guide 27

Logging in to the Designer

Resetting users

Note: Only use Reset Users or Continue if you know that you are the only

user connected to the repository. Subsequent changes could corrupt the repository.

28 Data Integrator Designer Guide

Data Integrator Designer Guide

Designer user interface

chapter

Designer user interface

This chapter provides basic information about the Designer’s graphical user interface. It contains the following topics:

• Data Integrator objects

• Designer window

• Menu bar

• Toolbar

• Project area

• Tool palette

• Workspace

• Local object library

• Object editors

• Working with objects

• General and environment options

30 Data Integrator Designer Guide

Data Integrator objects

All “entities” you define, edit, or work with in Data Integrator Designer are called objects. The local object library shows objects such as source and target metadata, system functions, projects, and jobs.

Objects are hierarchical and consist of:

• Options, which control the operation of objects. For example, in a

datastore, the name of the database to which you connect is an option for the datastore object.

• Properties, which document the object. For example, the name of the

object and the date it was created are properties. Properties describe an object, but do not affect its operation.

Data Integrator has two types of objects:

• Reusable objects

• Single-use objects

The object type affects how you define and retrieve the object.

Reusable objects

Designer user interface

Data Integrator objects

You can reuse and replicate most objects defined in Data Integrator. After you define and save a reusable object, Data Integrator stores the

definition in the local repository . You can then reuse the definition as often as necessary by creating calls to the definition. Access reusable objects through the local 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, you are changing the object in all other places in which it appears.

A data flow , for example, is a reusable object. Multiple jobs, like a weekly load job and a daily load job, can call the same data flow. If the data flow changes, both jobs use the new version of the data flow.

The object library contains object definitions. When you drag and drop an object from the object library, you are really creating a new reference (or call) to the existing object definition.

Single-use objects

Some objects are defined only within the context of a single job or data flow, for example scripts and specific transform definitions.

Data Integrator Designer Guide 31

Designer user interface

Data Integrator objects

Object hierarchy

Data Integrator object relationships are hierarchical. The following figure shows the relationships between major Data Integrator object types:

Projects

Jobs

Workflows &

Scripts

Conditionals*

Adapter Datastores

Tables

Documents

Outbound Messages

Message Functions

Datastores

Scripts

Data Sources and Targets

Database Datastores

Tables

Template Tables

Functions

Flat Files

Work flows and conditionals are

optional and can be embedded. Data flows can also be embedded.

Data Flows**

Transforms

Formats

DTDs & XML Schemas

XML Files

Template XML Files

XML Messages

Batch and Real-time

Real-time only

32 Data Integrator Designer Guide

Designer window

The Data Integrator Designer user interface consists of a single application window and several embedded supporting windows.

Designer user interface

Designer window

Menu bar

ToolbarProject area Workspace

Tool palette

Local object library

Menu bar

The application window contains the Menu bar, Toolbar, Project area, Tool

palette, tabbed Workspace, and tabbed Local object library.

This section contains a brief description of the Designer’s menus:

• Project menu

• Edit menu

• View menu

Data Integrator Designer Guide 33

Designer user interface

Menu bar

Project menu

• Tools menu

• Debug menu

• Validation menu

• Window menu

• Help menu

The project menu contains standard Windows as well as Data Integratorspecific options.

• New — Define a new project, batch job, real-time job, work flow, data

flow, transform, datastore, file format, DTD, XML Schema, or custom function.

• Open — Open an existing project.

• Close — Close the currently open project.

• Delete — Delete the selected object.

• Save — Save the object open in the workspace.

• Save All — Save all changes to objects in the current Designer session.

• Print — Print the active workspace.

• Print Setup — Set up default printer information.

• Compact Repository — Remove redundant and obsolete objects from

the repository tables.

• Exit — Exit Data Integrator Designer.

34 Data Integrator Designer Guide

Edit menu

Designer user interface

Menu bar

The Edit menu provides standard Windows commands with a few restrictions.

• Undo — Undo the last operation (text edits only).

• Cut — Cut the selected object or text and place it on the clipboard.

• Copy — Copy the selected object or text to the clipboard.

Note: You cannot copy reusable objects using the Copy command;

instead, use Replicate in the object library to make an independent copy of an object.

• Paste — Paste the content s of the clipboard into the active workspace or

text box.

Note: You can only paste clipboard contents once. To paste again, you

must cut or copy the objects again.

• Delete — Delete the selected object.

• Clear All — Clear all objects in the active workspace (no undo).

View menu

A check mark indicates that the tool is active.

• Toolbar — Display or remove the toolbar in the Designer window.

• Status Bar — Display or remove the status bar in the Designer window.

• Palette — Display or remove the floating tool palette.

• Enabled Descriptions — View descriptions for objects with enabled

descriptions.

Data Integrator Designer Guide 35

Designer user interface

Menu bar

Tools menu

• Refresh — Redraw the display.

Use this command to ensure the content of the workspace represents the most up-to-date information from the repository.

A check mark indicates that the tool is active.

• Object Library — Open or close the object library window. For more

information, see “Local object library” on page 47.

• Project Area — Display or remove the project area from the Data

Integrator window. For more information, see “Project area” on page 41.

• Variables — Open or close the Variables and Parameters window. For

more information, see “Variables and Parameters” on page 275.

• Output — Open or close the Output window. The Output window shows

errors that occur such as during job validation or object export.

• Custom Functions — Display the Custom Functions window. For more

information, see “Custom functions” on page 382 of the Data Integrator Reference Guide.

• System Configurations — Display the System Configurations editor.

For more information, see “Creating and managing multiple datastore

configurations” on page 112.

36 Data Integrator Designer Guide

Designer user interface

Menu bar

• Export — Export individual repository objects to another repository or

file. This command opens the Export editor in the workspace. You can drag objects from the object library into the editor for export. To export your whole repository, in the object library right-click and select Repository > Export to file. For more information, see “Exporting/

importing objects in Data Integrator” on page 28 of the Data Integrator

Advanced Development and Migration Guide.

• Import From File — Import objects into the current repository from a file.

For more information, see “Importing from a file” on page 33 of the Data Integrator Advanced Development and Migration Guide.

• Metadata Exchange — Import and export metadata to third-party

systems via a file. See “Metadata exchange” on page 379.

• BusinessObjects Universes — Export (create or update) metadata in

Business Objects Universes. See “Creating Business Objects universes” on page 382.

• Central Repositories — Create or edit connections to a central

repository for managing object versions among multiple users. See

Chapter 6, “Multiuser environment setup,” in the Data Integrator

Advanced Development and Migration Guide.

• Options — Display the Options window. See “General and environment

options” on page 64.

• Metadata Reports — Display the Metadata Reports window. Select the

object type, report type, and the objects in the repository that you want to list in the report. See “Metadata reporting tool” on page 389.

Debug menu

The only options available on this menu at all times are Show Filters/ Breakpoints and Filters/Breakpoints. The Execute and Start Debug

options are only active when a job is selected. All other options are available as appropriate when a job is running in the Debug mode. For more information, see “Using the interactive debugger” on page 333.

• Execute - Opens the Execution Properties window which allows you to

• Start Debug - Opens the Debug Properties window which allows you to

• Show Filters/Breakpoints - Shows and hides filters and breakpoints in

execute the selected job.

run a job in the debug mode.

workspace diagrams.

Data Integrator Designer Guide 37

Designer user interface

Menu bar

Validation menu

• Filters/Breakpoints - Opens a window you can use to manage filters

and breakpoints. For more information, see “Filters and Breakpoints

window” on page 347.

The Designer displays options on this menu as appropriate when an object is open in the workspace.

• Validate — Validate the objects in the current workspace view or all

objects in the job before executing the application.

• Display Language — View a read-only version of the language

associated with the job.

• Display Optimized SQL — Display the SQL that Data Integrator

generated for a selected data flow. See “V iewing SQL” on page 34 of the Data Integrator Performance Optimization Guide.

Window menu

The Window menu provides standard Windows options.

• Back — Move back in the list of active workspace windows.

• Forward — Move forward in the list of active workspace windows.

• Cascade — Display window panels overlapping with titles showing.

• Tile Horizontally — Display window panels side by side.

• Tile Vertically — Display window panels one above the other.

• Close All Windows — Close all open windows.

38 Data Integrator Designer Guide

Help menu

Designer user interface

Toolbar

• A list of objects open in the workspace also appears on the Windows

menu. The name of the currently-selected object is indicated by a check mark. Navigate to another open object by selecting its name in the list.

• Contents — Display on-line help. Data Integrator‘s on-line help works

with Microsoft Internet Explorer version 5.5 and higher.

• Technical Manuals— Display a PDF version of Data Integrator

documentation. This file contains the same content as on-line help. It is provided for users who prefer to print out their documentation. This format prints graphics clearly and includes a master index and page numbers/references. You can also access the same file from the Help menu in the Administrator or from the <linkdir>\Doc\Books directory.

• Release Notes — Display current release notes.

• Release Summary — Display summary of new features in the current

release.

• About Data Integrator — Display information about Data Integrator

including versions of the Designer, Job Server and engine, copyright information, and a link to the Business Objects Web site.

Toolbar

In addition to many of the standard Windows tools, Data Integrator provides application-specific tools, including:

Icon Tool Description

Close all windows Closes all open windows in the workspace.

Local Object Library

Opens and closes the local object library window .

Data Integrator Designer Guide 39

Designer user interface

Toolbar

Icon Tool Description

Central Object Library

Variables Opens and closes the variables and parameters

Project Area Opens and closes the project area.

Output Opens and closes the output window.

Opens and closes the central object library window.

creation window.

View Enabled Descriptions

Validate Current View

Validate All Objects in View

Audit Objects in Data Flow

View Where Used Opens the Output window, which lists parent

Go Back Move back in the list of active workspace

Go Forward Move forward in the list of active workspace

Metadata Reports Opens and closes the Metadata Reports window.

Enables the system level setting for viewing object descriptions in the workspace.

Validates the object definition open in the workspace. Other objects included in the definition are also validated.

Validates the object definition open in the workspace. Objects included in the definition are also validated.

Opens the Audit window to define audit labels and rules for the data flow.

objects (such as jobs) of the object currently open in the workspace (such as a data flow). Use this command to find other jobs that use the same data flow, before you decide to make design changes. To see if an object in a data flow is reused elsewhere, right-click one and select View Where Used.

windows.

About Opens the Data Integrator About box, with

Use the tools to the right of the About tool with the interactive debugger. See

“Menu options and tool bar” on page 348.

40 Data Integrator Designer Guide

product component version numbers and a link to the Business Objects Web site.

Designer user interface

Project area

The project area provides a hierarchical view of the objects used in each project. Tabs on the bottom of the project area support different tasks. Tabs include:

To control project area location, right-click its gray border and select/deselect Allow Docking, or select Hide from the menu.

• When you select Allow Docking, you can click and drag the project area

• When you select Hide, the project area disappears from the Designer

Here’s an example of the Project window’s Designer tab, which shows the project hierarchy:

Create, view and manage projects. Provides a hierarchical view of all objects used in each project.

View the status of currently executing jobs. Selecting a specific job execution displays its status, including which steps are complete and which steps are executing. These tasks can also be done using the Data Integrator Administrator.

View the history of complete jobs. Logs can also be viewed with the Data Integrator Administrator.

to dock at and undock from any edge within the Designer window. When you drag the project area away from a Designer window edge, it stays undocked. To quickly switch between your last docked and undocked locations, just double-click the gray border.

When you deselect Allow Docking, you can click and drag the project area to any location on your screen and it will not dock inside the Designer window.

window. To unhide the project area, click its toolbar icon.

Data Integrator Designer Guide 41

Designer user interface

Tool palette

Project Job

Work flow Data flow

As you drill down into objects in the Designer workspace, the window highlights your location within the project hierarchy.

The tool palette is a separate window that appears by default on the right edge of the Designer workspace. You can move the tool palette anywhere on your screen or dock it on any edge of the Designer window.

The icons in the tool palette allow you to create new objects in the workspace. The icons are disabled when they are not allowed to be added to the diagram open in the workspace.

To show the name of each icon, hold the cursor over the icon until the tool tip for the icon appears, as shown.

When you create an object from the tool palette, you are creating a new definition of an object. If a new object is reusable, it will be automatically available in the object library after you create it.

For example, if you select the data flow icon from the tool palette and define a new data flow, later you can drag that existing data flow from the object library, adding a call to the existing definition.

42 Data Integrator Designer Guide

Designer user interface

Tool palette

The tool palette contains the following icons:

Icon Tool Description (class) Available

Pointer Returns the tool pointer to a

selection pointer for selecting and moving objects in a diagram.

Work flow Creates a new work flow.

(reusable)

Everywhere

Jobs and work flows

Data flow Creates a new data flow.

(reusable)

R/3 data flow Used only with the SAP licensed

extension. See the .

Query transform

Template table Creates a table for a target.

Template XML Creates an XML template.

Data transport Used only with the SAP Licensed

Script Creates a new script object.

Conditional Creates a new conditional object.

Try Creates a new try object.

Catch Creates a new catch object.

Creates a template for a query. Use it to define column mappings and row selections. (single-use)

(single-use)

extension. See the .

(single-use)

Jobs and work flows

Data flows

Jobs and work flows

Annotation Creates an annotation.

(single-use)

Data Integrator Designer Guide 43

Jobs, work flows, and data flows

Designer user interface

Workspace

When you open or select a job or any flow within a job hierarchy, the workspace becomes “active” with your selection. The workspace provides a place to manipulate system objects and graphically assemble data movement processes.

These processes are represented by icons that you drag and drop into a workspace to create a workspace diagram. This diagram is a visual representation of an entire data movement application or some part of a data movement application.

This section describes major workspace area tasks, such as:

• Moving objects in the workspace area

• Connecting and disconnecting objects

• Describing objects

• Scaling the workspace

• Arranging workspace windows

• Closing workspace windows

Moving objects in the workspace area

Use standard mouse commands to move objects in the workspace.

To move an object to a different place in the workspace area



1. Click to select the object.

2. Drag the object to where you want to place it in the workspace.

44 Data Integrator Designer Guide

Connecting and disconnecting objects

You specify the flow of data through jobs and work flows by connecting objects in the workspace from left to right in the order you want the data to be moved:

To connect objects



1. Place the objects you want to connect in the workspace.

2. Click and drag from the triangle on the right edge of an object to the

triangle on the left edge of the next object in the flow.

To disconnect objects



1. Click the connecting line.

Designer user interface

Workspace

2. Press the Delete key.

Describing objects

You can use descriptions to add comments about objects. You can use annotations to explain a job, work flow, or data flow. You can view object descriptions and annotations in the workspace. Together, descriptions and annotations allow you to document a Data Integrator application. For example, you can describe the incremental behavior of individual jobs with numerous annotations and label each object with a basic description.

Data Integrator Designer Guide 45

Designer user interface

Workspace

For more information, see “Creating descriptions” on page 57 and “Creating

annotations” on page 59.

Scaling the workspace

You can control the scale of the workspace. By scaling the workspace, you can change the focus of a job, work flow , or data flow. For example, you might want to increase the scale to examine a particular part of a work flow, or you might want to reduce the scale so that you can examine the entire work flow without scrolling.

To change the scale of the workspace



1. In the drop-down list on the tool bar, select a predefined scale or enter a

custom value.

2. Alternatively, right-click in the workspace and select a desired scale.

46 Data Integrator Designer Guide

Note: You can also select Scale to Fit and Scale to Whole:

• Select Scale to Fit and the Designer calculates the scale that fits the

entire project in the current view area.

• Select Scale to Whole to show the entire workspace area in the current

view area.

Arranging workspace windows

The Window menu allows you to arrange multiple open workspace windows in the following ways: cascade, tile horizontally, or tile vertically.

Closing workspace windows

When you drill into an object in the project area or workspace, a view of the object’s definition opens in the workspace area. The view is marked by a tab at the bottom of the workspace area, and as you open more objects in the workspace, more tabs appear. (You can show/hide these tabs from the Tools > Options menu. Go to Designer > General options and select/deselect Show tabs in workspace. For more information, see the “General and

environment options” section.)

Note: These views use system resources. If you have a large number of

open views, you might notice a decline in performance. Close the views individually by clicking the close box in the top right corner of

the workspace. Close all open views by selecting Window > Close All Windows or clicking the Close All Windows icon on the toolbar.

Designer user interface

Local object library

The local object library provides access to reusable objects. These objects include built-in system objects, such as transforms, and the objects you build and save, such as datastores, jobs, data flows, and work flows.

The local object library is a window into your local Data Integrator repository and eliminates the need to access the repository directly. Updates to the repository occur through normal Data Integrator operation. Saving the objects you create adds them to the repository. Access saved objects through the local object library. To learn more about local as well as central repositories, see “Central versus local repository” on page 38 of the Data Integrator Advanced Development and Migration Guide.

Data Integrator Designer Guide 47

Designer user interface

Local object library

To control object library location, right-click its gray border and select/deselect Allow Docking, or select Hide from the menu.

• When you select Allow Docking, you can click and drag the object

library to dock at and undock from any edge within the Designer window. When you drag the object library away from a Designer window edge, it stays undocked. To quickly switch between your last docked and undocked locations, just double-click the gray border.

When you deselect Allow Docking, you can click and drag the object library to any location on your screen and it will not dock inside the Designer window.

• When you select Hide, the object library disappears from the Designer

window. To unhide the object library, click its toolbar icon.

T o open the object library



Choose Tools > Object Library, or click the object library icon in the icon bar.

Object library

window

Transform object

Tabs for other

object types

The object library gives you access to the object types listed in the following table. The table shows the tab on which the object type appears in the object library and describes the Data Integrator context in which you can use each type of object.

Tab Description

list

Projects are sets of jobs available at a given time.

Jobs are executable work flows. There are two job types: batch jobs and real-time jobs.

Work flows order data flows and the operations that support data flows, defining the interdependencies between them.

48 Data Integrator Designer Guide

Designer user interface

Local object library

Tab Description

Data flows describe how to process a task.

Transforms operate on data, producing output data sets from the sources you specify. The object library lists both built-in and custom transforms.

Datastores represent connections to databases and applications used in your project. Under each datastore is a list of the tables, documents, and functions imported into Data Integrator.

Formats describe the structure of a flat file, XML file, or XML message.

Custom Functions are functions written in the Data Integrator Scripting Language. You can use them in Data Integrator jobs.



To display the name of each tab as well as its icon, do one of the

following:

• Make the object library window wider until the names appear.

• Hold the cursor over the tab until the tool tip for the tab appears, as

shown.

To sort columns in the object library



• Click the column heading.

For example, you can sort data flows by clicking the Data Flow column heading once. Names are listed in ascending order. To list names in descending order, click the Data Flow column heading again.

Data Integrator Designer Guide 49

Designer user interface

Object editors

Input schema Output schema

Parameter tabs

To work with the options for an object, in the workspace click the name of the object to open its editor . The editor displays the input and output schemas for the object and a panel below them listing options set for the object. If there are many options, they are grouped in tabs in the editor.

A schema is a data structure that can contain columns, other nested schemas, and functions (the contents are called schema elements ). A table is a schema containing only columns.

A common example of an editor is the editor for the query transform, as shown in the following illustration:

Tabs of open windows

For specific information about the query editor, see “Query editor” on page 179. In an editor, you can:

• Undo or redo previous actions performed in the window (right-click and

choose Undo or Redo)

• Find a string in the editor (right-click and choose Find)

• Drag-and-drop column names from the input schema into relevant option

boxes

• Use colors to identify strings and comments in text boxes where you can

edit expressions (keywords appear blue; strings are enclosed in quotes and appear pink; comments begin with a pound sign and appear green)

50 Data Integrator Designer Guide

Working with objects

This section discusses common tasks you complete when working with objects in the Designer. With these tasks, you use various parts of the Designer—the toolbar, tool palette, workspace, and local object library.

Tasks in this section include:

• Creating new reusable objects

• Changing object names

• Viewing and changing object properties

• Creating descriptions

• Creating annotations

• Saving and deleting objects

• Searching for objects

Creating new reusable objects

You can create reusable objects from the object library or by using the tool palette. After you create an object, you can work with the object, editing its definition and adding calls to other objects.

Designer user interface

Working with objects

To create a reusable object (in the object library)



1. Open the object library by choosing Tools > Object Library.

2. Click the tab corresponding to the object type.

3. Right-click anywhere except on existing objects and choose New.

4. Right-click the new object and select Properties. Enter options such as

name and description to define the object.



To create a reusable object (using the tool palette)

1. In the tool palette, left-click the icon for the object you want to create.

2. Move the cursor to the workspace and left-click again.

The object icon appears in the workspace where you have clicked.



T o open an object’s definition

You can open an object’s definition in one of two ways:

• From the workspace, click the object name.

Data Integrator Designer Guide 51

Designer user interface

Working with objects

Click the object name to open its definition

Data Integrator opens a blank workspace in which you define the object

• From the project area, click the object.

Y ou define an object using other objects. For example, if you click the name of a batch data flow, a new workspace opens for you to assemble sources, targets, and transforms that make up the actual flow.

To add an existing object (create a new call to an existing object)



1. Open the object library by choosing Tools > Object Library.

2. Click the tab corresponding to any object type.

3. Select an object.

4. Drag the object to the workspace. Note: Objects dragged into the workspace must obey the hierarchy logic

explained in “Object hierarchy” on page 32. For example, you can drag a data flow into a job, but you cannot drag a work flow into a data flow.

Changing object names

You can change the name of an object from the workspace or the object library. You can also create a copy of an existing object.

Note: You cannot change the names of built-in objects.

To change the name of an object in the workspace



1. Click to select the object in the workspace.

2. Right-click and choose Edit Name.

52 Data Integrator Designer Guide

3. Edit the text in the name text box.

4. Click outside the text box or press Enter to save the new name.



To change the name of an object in the object library

1. Select the object in the object library.

2. Right-click and choose Properties.

3. Edit the text in the first text box.

4. Click OK.



To copy an object

1. Select the object in the object library.

2. Right-click and choose Replicate.

Data Integrator makes a copy of the top-level object (but not of objects that it calls) and gives it a new name, which you can edit.

Viewing and changing object properties

You can view (and, in some cases, change) an object’s properties through it s property page.

Designer user interface

Working with objects

To view, change, and add object properties



1. Select the object in the object library.

2. Right-click and choose Properties. The General tab of the Properties

window opens.

3. Complete the property sheets. The property sheets vary by object type,

but General, Attributes and Class Attributes are the most common and are described in the following sections.

4. When finished, click OK to save changes you made to the object

properties and to close the window. Alternatively, click Apply to save changes without closing the window.

Data Integrator Designer Guide 53

Designer user interface

Working with objects

General tab

The General tab contains two main object properties: name and description.

From the General tab, you can change the object name as well as enter or edit the object description. You can add object descriptions to single-use objects as well as to reusable objects. Note that you can toggle object descriptions on and off by right-clicking any object in the workspace and selecting/deslecting View Enabled Descriptions.

Depending on the object, other properties may appear on the General tab. Examples include:

• Execute only once — See “Creating and defining dat a flows” in Chapter

7: Data Flows for more information.

• Recover as a unit — See “Marking recovery units” in Chapter 17:

Recovery Mechanisms for more information.

• Degree of parallelism — See the “Data Integrator Performance

Optimization Guide” for more information on this advanced feature.

54 Data Integrator Designer Guide

Attributes tab

Designer user interface

Working with objects

The Attributes tab allows you to assign values to the attributes of the current object.

To assign a value to an attribute, select the attribute and enter the value in the Value box at the bottom of the window.

Some attribute values are set by Data Integrator and cannot be edited. When you select an attribute with a system-defined value, the Value field is unavailable.

Data Integrator Designer Guide 55

Designer user interface

Working with objects

Class Attributes tab

The Class Attributes tab shows the attributes available for the type of object selected. For example, all data flow objects have the same class attributes.

To create a new attribute for a class of objects, right-click in the attribute list and select Add. The new attribute is now available for all of the objects of this class.

To delete an attribute, select it then right-click and choose Delete. Y ou cannot delete the class attributes predefined by Data Integrator.

56 Data Integrator Designer Guide

Creating descriptions

Use descriptions to document objects. You can see descriptions on workspace diagrams. Therefore, descriptions are a convenient way to add comments to workspace objects.

A description is associated with a particular object. When you import or export that repository object (for example, when migrating between development, test, and production environments), you also import or export its description.

The Designer determines when to show object descriptions based on a system-level setting and an object-level setting. Both settings must be activated to view the description for a particular object.

The system-level setting is unique to your setup. The system-level setting is disabled by default. To activate that system-level setting, select View > Enabled Descriptions, or click the View Enabled Descriptions button on the toolbar.

The object-level setting is saved with the object in the repository. The objectlevel setting is also disabled by default unless you add or edit a description from the workspace. To activate the object-level setting, right-click the object and select Enable object description.

Designer user interface

Working with objects

An ellipses after the text in a description indicates that there is more text. To see all the text, resize the description by clicking and dragging it. When you move an object, its description moves as well. To see which object is associated with which selected description, view the ob ject ’ s name in the status bar.

Data Integrator Designer Guide 57

Designer user interface

Working with objects



T o add a description to an object

1. In the project area or object library, right-click an object and select

Properties.

2. Enter your comments in the Description text box.

3. Click OK.

The description for the object displays in the object library.



To display a description in the workspace

1. In the project area, select an existing object (such as a job) that contains

an object to which you have added a description (such as a work flow).

2. From the View menu, select Enabled Descriptions.

Alternately , you can select the View Enabled Descriptions button on the toolbar.

3. Right-click the work flow and select Enable Object Description.

The description displays in the workspace under the object.

To add a description to an object from the workspace



1. From the View menu, select Enabled Descriptions.

2. In the workspace, right-click an object and select Properties.

3. In the Properties window, enter text in the Description box.

4. Click OK.

The description displays automatically in the workspace (and the object’s Enable Object Description option is selected).



T o hide a particular object’s description

1. In the workspace diagram, right-click an object.

Alternately, you can select multiple objects by:

• Pressing and holding the Control key while selecting objects in the

workspace diagram, then right-clicking one of the selected objects.

• Dragging a selection box around all the objects you want to select,

then right-clicking one of the selected objects.

2. In the pop-up menu, deselect Enable Object Description.

The description for the object selected is hidden, even if the View Enabled Descriptions option is checked, because the object-level

switch overrides the system-level switch.

58 Data Integrator Designer Guide



To edit object descriptions

1. In the workspace, double-click an object description.

2. Enter, cut, copy, or paste text into the description.

3. In the Project menu, select Save.

Alternately, you can right-click any object and select Properties to open the object’s Properties window and add or edit its description.

Note: If you attempt to edit the description of a reusable object, Data

Integrator alerts you that the description will be updated for every occurrence of the object, across all jobs. You can select the Do not show me this again check box to avoid this alert. However, after deactivating the alert, you can only reactivate the alert by calling Technical Support.

Creating annotations

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.

To annotate a workspace diagram



1. Open the workspace diagram you want to annotate.

You can use annotations to describe any workspace such as a job, work flow, data flow, catch, conditional, or while loop.

2. In the tool palette, click the annotation icon.

3. Click a location in the workspace to place the annotation.

An annotation appears on the diagram.

Designer user interface

Working with objects

Data Integrator Designer Guide 59

Designer user interface

Working with objects

You can add, edit, and delete text directly on the annotation. In addition, you can resize and move the annotation by clicking and dragging. You can add any number of annotations to a diagram.

You cannot hide annotations that you have added to the workspace. However, you can move them out of the way or delete them.

To delete an ann o tation



1. Right-click an annotation.

2. Select Delete.

Alternately, you can select an annotation and press the Delete key.

Saving and deleting objects

“Saving” an object in Data Integrator means storing the language that describes the object to the repository. You can save reusable objects; singleuse objects are saved only as part of the definition of th e reusable object that calls them.

You can choose to save changes to the reusable object currently open in the workspace. When you save the object, the object properties, the definitions of any single-use objects it calls, and any calls to other reusable objects are recorded in the repository . The content of the included reusable object s is not saved; only the call is saved.

Data Integrator stores the description even if the object is not complete or contains an error (does not validate).

60 Data Integrator Designer Guide

Designer user interface

Working with objects



To save changes to a single reusable object

1. Open the project in which your object is included.

2. Choose Project > Save.

This command saves all objects open in the workspace.

Repeat these steps for other individual objects you want to save.



To save all changed objects in the repository

1. Choose Project > Save All.

Data Integrator lists the reusable objects that were changed since the last save operation.

2. (optional) Deselect any listed object to avoid saving it.

3. Click OK. Note: Data Integrator also prompts you to save all objects that have

changes when you execute a job and when you exit the Designer. Saving a reusable object saves any single-use object included in it.



To delete an object definition from the repository

1. In the object library, select the object.

2. Right-click and choose Delete.

• If you attempt to delete an object that is being used, Data Integrator

provides a warning message and the option of using the View Where Used feature.

For more information, see “Using View Where Used” on page 313.

• If you select Yes, Data Integrator marks all calls to the object with a

red “deleted” icon to indicate that the calls are invalid. Y ou must remove or replace these calls to produce an executable job.

Data Integrator Designer Guide 61

Designer user interface

Working with objects

Note: Built-in objects such as transforms cannot be deleted from the

object library.

To delete an obje ct call



1. Open the object that contains the call you want to delete.

2. Right-click the object call and choose Delete.

If you delete a reusable object from the workspace or from the project area, only the object call is deleted. The object definition remains in the object library.

Searching for objects

From within the object library, you can search for objects defined in the repository or objects available through a datastore.

To search for an object



1. Right-click in the object library and choose Search.

Data Integrator displays the Search window.

2. Enter the appropriate values for the search.

Options available in the Search window are described in detail following this procedure.

3. Click Search.

The objects matching your entries are listed in the window. From the search results window you can use the context menu to:

• Open an item

• View the attributes (Properties)

62 Data Integrator Designer Guide

Designer user interface

Working with objects

• Import external tables as repository metadata

You can also drag objects from the search results window and drop them in the desired location.

The Basic tab in the Search window provides you with the following options:

Option Description

Name The object name to find.

If you are searching in the repository, the name is not case sensitive. If you are searching in a datastore and the name is case sensitive in that datastore, enter the name as it appears in the database or application and use double quotation marks (") around the name to preserve the case.

You can designate whether the information to be located Contains the specified name or Equals the specified name using the drop-down box next to the Name field.

Description The object description to find.

Objects imported into the Data Integrator repository have a description from their source. By default, objects you create in the Designer have no description unless you add a one.

The search returns objects whose description attribute contains the value entered.

Type The type of object to find.

When searching the repository, choose from Tables, Files, Data flows, Work flows, Jobs, Hierarchies, IDOCs, and Domains.

When searching a datastore or application, choose from object types available through that datastore.

Look in Where to search.

Choose from the repository or a specific datastore. When you designate a datastore, you can also choose to

search the imported data (Internal Data) or the entire datastore (External Data).

The Search window also includes an Advanced tab. From the Advanced tab, you can choose to search for objects based on their Data Integrator attribute values. You can search by attribute values only when searching in the repository.

Data Integrator Designer Guide 63

Designer user interface

General and environment options

The Advanced tab provides the following options:

Option Description

Attribute The object attribute in which to search.

Value The attribute value to find. Match The type of search performed.

The attributes are listed for the object type specified on the Basic tab.

Select Contains to search for any attribute that contains the value specified. Select Equals to search for any attribute that contains only the value specified.

General and environment options

To open the Options window, select Tools > Options. The window displays option groups for Designer, Data, and Job Server options.

Expand the options by clicking the plus icon. As you select each option group or option, a description appears on the right.

SAP options appear if you install these licensed extensions. See the for more information about these options.

The standard options include:

• Designer — Environment

• Designer — General

• Designer — Graphics

64 Data Integrator Designer Guide

• Designer — Central Repository Connections

• Data — General

• Job Server — Environment

• Job Server — General

Designer — Environment

Default Administrator for Metadata Reporting:

Administrator — Select the Administrator that the metadata reporting

tool uses. An Administrator is defined by host name and port.

Default Job Server:

If a repository is associated with several Job Servers, one Job Server must be defined as the default Job Server to use at login.

Current — Displays the current value of the default Job Server. New — Allows you to specify a new value for the default Job Server from

a drop-down list of Job Servers associated with this repository. Changes are effective immediately.

Note: Job-specific options and path names specified in Designer refer to

the current default Job Server. If you change the default Job Server, modify these options and path names.

Designer Communication Ports:

Allow Designer to set the port for Job Server communication — If

checked, Designer automatically sets an available port to receive messages from the current Job Server. The default is checked. Uncheck to specify a listening port or port range.

Specify port range — Only activated when you deselect the previous control. Allows you to specify a range of ports from which the Designer can choose a listening port.

You may choose to constrain the port used for communication between Designer and Job Server when the two components are separated by a firewall.

Designer user interface

General and environment options

Data Integrator Designer Guide 65

Designer user interface

General and environment options

Enter port numbers in the From port and To port text boxes. T o specify a specific listening port, enter the same port number in both the From port and To port text boxes. Changes will not take effect until you restart Data Integrator.

Interactive Debugger — Allows you to set a communication port for the Designer to communicate with a Job Server while running in Debug mode. For more information, see “Changing the interactive debugger

port” on page 337.

Server group for local repository — If the local repository that you logged in to when you opened the Designer is associated with a server group, the name of the server group appears.

Designer — General

View data sampling size (rows) — Controls the sample size used to display the data in sources and targets in open data flows in the workspace. View data by clicking the magnifying glass icon on source and target objects. For more information, see “Using View Data” on page 318.

Number of characters in workspace icon name — Controls the length of the object names displayed in the workspace. Object names are allowed to exceed this number, but the Designer only displays the numb er entered here. The default is 17 characters.

Maximum schema tree elements to auto expand — The number of elements displayed in the schema tree. Element names are not allowed to exceed this number. Enter a number for the Input schema and the Output

schema. The default is 100. Default parameters to variables of the same name — When you declare a

variable at the work-flow level, Data Integrator automatically passes the value as a parameter with the same name to a data flow called by a work flow.

Automatically import domains — Select this check box to automatically import domains when importing a table that references a domain.

Perform complete validation before job execution — If checked, Data Integrator performs a complete job validation before running a job. The default is unchecked. If you keep this default setting, you should validate your design manually before job execution.

Open monitor on job execution — Affects the behavior of the Designer when you execute a job. With this option enabled, the Designer switches the workspace to the monitor view during job execution; otherwise, the workspace remains as is. The default is on.

66 Data Integrator Designer Guide

Calculate column mapping while saving data flow — Calculates information about target tables and columns and the sources used to populate them. Data Integrator automatically stores this information in the AL_COLMAP table (ALVW_MAPPING view) when you save a data flow. You can see this information when you generate metadata reports. If you select this option, be sure to validate your entire job before saving it. This functionality is highly sensitive to errors and will skip data flows with validation problems. For more information, see, “Tools” on page 429.

Show dialog when job is completed — Allows you to choose if you want to see an alert or just read the trace messages.

Show tabs in workspace — Allows you to decide if you want to use the tabs at the bottom of the workspace to navigate.

Designer — Graphics

Choose and preview stylistic elements to customize your workspaces. Using these options, you can easily distinguish your job/work flow design workspace from your data flow design workspace.

• Workspace flow type — Switch between the two workspace flow types

(Job/Work Flow and Data Flow) to view default settings. Modify settings for each type using the remaining options.

• Line Type — Choose a style for object connector lines.

• Line Thickness — Set the connector line thickness.

• Background style — Choose a plain or tiled background pattern for the

selected flow type.

• Color scheme — Set the background color to blue, gray, or white.

• Use navigation watermark — Add a watermark graphic to the

background of the flow type selected. Note that this option is only available with a plain background style.

Designer user interface

General and environment options

Designer — Central Repository Connections

Displays the central repository connections and the active central repository. To activate a central repository, right-click one of the central repository connections listed and select Activate.

Reactivate automatically — Select if you want the active central repository to be reactivated whenever you log in to Data Integrator using the current local repository.

Data Integrator Designer Guide 67

Designer user interface

General and environment options

Data — General

Century Change Year — Indicates how Data Integrator interpret s the century for two-digit years. Two-digit years greater than or equal to this value are interpreted as 19##. Two-digit years less than this value are interpreted as 20##. The default value is 15.

For example, if the Century Change Year is set to 15:

Two-digit year Interpreted as

99 1999 16 1916 15 1915 14 2014

Convert blanks to nulls for Oracle bulk loader — Converts blanks to NULL values when loading data using the Oracle bulk loader utility and:

• the column is not part of the primary key

• the column is nullable

Job Server — Environment

Maximum number of engine processes — Sets a limit on the number of engine processes that this Job Server can have running concurrently.

Job Server — General

Use this window to reset Job Server options (see “Changing Job Server

options” on page 307) or with guidance from Business Objects Customer

Support. For contact information, visit Business Objects Customer Support

Online.

68 Data Integrator Designer Guide

Data Integrator Designer Guide

Projects and Jobs

chapter

Projects and Jobs

Project and job objects represent the top two levels of organization for the application flows you create using the Designer. This chapter contains the following topics:

• Projects

• Jobs

70 Data Integrator Designer Guide

Projects

A project is a reusable object that allows you to group jobs. A project is the highest level of organization offered by Data Integrator. Opening a project makes one group of objects easily accessible in the user interface.

You can use a project to group jobs that have schedules that depend on one another or that you want to monitor together.

Projects have common characteristics:

• Projects are listed in the object library.

• Only one project can be open at a time.

• Projects cannot be shared among multiple users.

Objects that make up a project

The objects in a project appear hierarchically in the project area. If a plus sign (+) appears next to an object, expand it to view the lower-level objects contained in the object. Data Integrator shows you the contents as both names in the project area hierarchy and icons in the workspace.

In the following example, the Job_KeyGen job contains two data flows, and the DF_EmpMap data flow contains multiple objects.

Projects and Jobs

Projects

Click here to open the first level

Click here to open the next level

Each item selected in the project area also displays in the workspace:

Data Integrator Designer Guide 71

Projects and Jobs

Projects

Creating new projects



T o crea te a new project

1. Choose Project > New > Project.

2. Enter the name of your new project.

The name can include alphanumeric characters and underscores (_). It cannot contain blank spaces.

3. Click Create.

The new project appears in the project area. As you add jobs and other lowerlevel objects to the project, they also appear in the project area.

Opening existing projects



T o open an existing project

1. Choose Project > Open.

2. Select the name of an existing project from the list.

3. Click Open. Note: If another project was already open, Data Integrator closes that project

and opens the new one.

Saving projects



To save all changes to a project

1. Choose Project > Save All.

Data Integrator lists the jobs, work flows, and data flows that you edited since the last save.

2. (optional) Deselect any listed object to avoid saving it.

3. Click OK. Note: Data Integrator also prompts you to save all objects that have

changes when you execute a job and when you exit the Designer. Saving a reusable object saves any single-use object included in it.

72 Data Integrator Designer Guide

Jobs

Projects and Jobs

Jobs

A job is the only object you can execute. You can manually execute and test jobs in development. In production, you can schedule batch jobs and set up real-time jobs as services that execute a process when Data Integrator receives a message request.

A job is made up of steps you want executed together. Each step is represented by an object icon that you place in the workspace to create a job diagram. A job diagram is made up of two or more objects connected together. You can include any of the following objects in a job definition:

• Data flows

• Sources

• Targets

• Transforms

• Work flows

• Scripts

• Conditionals

• While Loops

• Try/catch blocks

If a job becomes complex, organize its content into individual work flows, then create a single job that calls those work flows. For more information on work flows, see Chapter 8: Work Flows.

Real-time jobs use the same components as batch jobs. You can add work flows and data flows to both batch and real-time jobs. When you drag a work flow or data flow icon into a job, you are telling Data Integrator to validate these objects according the requirements of the job type (either batch or realtime).

There are some restrictions regarding the use of some Data Integrator features with real-time jobs. For more information, see Chapter 10: Real-time

jobs.

Creating jobs



1. In the project area, select the project name.

To create a job in the project area

Data Integrator Designer Guide 73

Projects and Jobs

Jobs

2. Right-click and choose New Batch Job or Real Time Job.

3. Edit the name.

The name can include alphanumeric characters and underscores (_). It cannot contain blank spaces.

Data Integrator opens a new workspace for you to define the job.

To create a job in the object library



1. Go to the Jobs tab.

2. Right-click Batch Jobs or Real Time Jobs and choose New.

3. A new job with a default name appears.

4. Right-click and select Properties to change the object’s name and add a

description. The name can include alphanumeric characters and underscores (_). It

cannot contain blank spaces.

5. To add the job to the open project, drag it into the project area.

74 Data Integrator Designer Guide

Projects and Jobs

Naming conventions for objects in jobs

We recommend that you follow consistent naming conventions to facilitate object identification across all systems in your enterprise. This allows you to more easily work with metadata across all applications such as:

• Data-modeling applications

• ETL applications

• Reporting applications

• Adapter software development kits

Examples of conventions recommended for use with jobs and other objects are shown in the following table.

Prefix Suffix Object Example

DF_ Data flow DF_Currency EDF_ _NoOutput_Input Embedded data flow EDF_ERP_NoOutput_Input EDF_ _Input Embedded data flow EDF_Example_Input EDF_ _Output Embedded data flow EDF_Example_Output EDF_ _NoInput_Output Embedded data flow EDF_ERP_NoInput_Output RTJob_ Real-time job RTJob_OrderStatus WF_ Work flow WF_SalesOrg JOB_ Job JOB_SalesOrg

_DS Datastore ORA_DS DC_ Datastore configuration DC_DB2_production SC_ System configuration SC_ORA_test

_Memory_DS Memory datastore Catalog_Memory_DS PROC_ Stored procedure PROC_SalesStatus

Jobs

Although Data Integrator Designer is a graphical user interface with icons representing objects in its windows, other interfaces might require you to identify object types by the text alone. By using a prefix or suffix, you can more easily identify your object’s type.

In addition to prefixes and suffixes, you might want to provide standardized names for objects that identify a specific action across all object types. For example: DF_OrderStatus, RTJob_OrderStatus.

In addition to prefixes and suffixes, naming conventions can also include p ath name identifiers. For example, the stored procedure naming convention can look like either of the following:

Data Integrator Designer Guide 75

Projects and Jobs

Jobs

76 Data Integrator Designer Guide

Data Integrator Designer Guide

Datastores

chapter

Datastores

This chapter contains the following topics:

• What are datastores?

• Database datastores

• Adapter datastores

• Creating and managing multiple datastore configurations

78 Data Integrator Designer Guide

What are datastores?

Datastores represent connection configurations between Data Integrator and databases or applications. These configurations can be direct or through adapters. Datastore configurations allow Data Integrator to access metadata from a database or application and read from or write to that database or application while Data Integrator executes a job.

Data Integrator datastores can connect to:

• Databases and mainframe file systems. See “Database datastores” on

page 80.

• Applications that have pre-packaged or user-written Data Integrator

adapters. See “Adapter datastores” on page 108.

• J.D. Edwards One World and J.D. Edwards World, Oracle Applications,

PeopleSoft, SAP R/3 and SAP BW, and Siebel Applications. See the appropriate Data Integrator Supplement.

Note: Data Integrator reads and writes data stored in flat files through flat file

formats as described in Chapter 6: File Formats. Data Integrator reads and writes data stored in XML documents through DTDs and XML Schemas. See

“Formatting XML documents” on page 217.

The specific information that a datastore object can access depends on the connection configuration. When your database or application changes, make corresponding changes in the datastore information in Data Integrator—Data Integrator does not automatically detect the new information.

Note: Objects deleted from a datastore connection are identified in the

project area and workspace by a red “deleted” icon . This visual flag allows you to find and update data flows affected by datastore changes.

Y ou can create multiple configurations for a dat astore. This allows you to plan ahead for the different environments your datastore may be used in and limit s the work involved with migrating jobs. For example, you can add a set of configurations (DEV, TEST, and PROD) to the same datastore name. These connection settings stay with the datastore during export or import. Group any set of datastore configurations into a system configuration. When running or scheduling a job, select a system configuration, and thus, the set of datastore configurations for your current environment.

For more information, see “Creating and managing multiple datastore

configurations” on page 112.

Datastores

What are datastores?

Data Integrator Designer Guide 79

Datastores

Database datastores

Database datastores can represent single or multiple Data Integrator connections with:

• Legacy systems, using BusinessObjects Data Integrator Mainframe

Interfaces

• IBM DB2, Informix, Microsoft SQL Server, Oracle, Sybase ASE, Sybase

IQ, and Teradata databases (using native connections)

• Other databases (through ODBC)

• A Data Integrator repository, using a memory datastore

This section discusses:

• Mainframe interfaces

• Defining a database datastore

• Browsing metadata through a database datastore

• Importing metadata through a database datastore

• Memory datastores

Mainframe interfaces

Data Integrator supports two legacy database connection options Attunity Connect and IBM DB2 II Classic Federation for z/OS. The Designer provides these options as database datastores with the following names:

• Attunity Connector

• IBM Connector

Attunity Connector

Data Integrator provides the Attunity Connector datastore that accesses mainframe data sources through Attunity Connect. The data sources that Attunity Connect accesses include the following list. For a complete list of sources, refer to Attunity documentation.

• Adabas

• DB2 UDB for OS/390 and DB2 UDB for OS/400

• IMS/DB

• VSAM

• Flat files on OS/390 and flat files on OS/400

80 Data Integrator Designer Guide

Datastores

Database datastores

Attunity Connector accesses mainframe data using software that you must manually install on the mainframe server and the local client (Job Server) computer. Data Integrator connects to Attunity Connector using its ODBC interface.

It is not necessary to purchase a separate ODBC driver manager for UNIX and Windows platforms.

Servers

Install and configure the Attunity Connect product on the server (for example, an zSeries computer).

Clients

To access mainframe data using Attunity Connector, install the Attunity Connect product. The ODBC driver is required. Attunity also offers an optional tool called Attunity Studio, which you can use for configuration and administration.

Configure ODBC data sources on the client (Data Integrator Job Server). When you install a Data Integrator Job Server on UNIX, the installer will

prompt you to provide an installation directory path for Attunity connector software. In addition, you do not need to install a driver manager, because Data Integrator loads ODBC drivers directly on UNIX platforms.

For more information about how to install and configure these products, refer to their documentation.

Configuring an Attunity datastore

To use the Attunity Connector datastore option, upgrade your repository to Data Integrator version 6.5.1 or higher.

To create an Attunity Connector datastore



1. In the Datastores tab of the object library, right-click and select New.

2. Enter a name for the datastore.

3. In the Datastore type box, select Database.

4. In the Database type box, select Attunity Connector.

5. Finish entering values in the remainder of the dialog.

Data Integrator Designer Guide 81

Datastores

Database datastores

• To create an Attunity Connector datastore, you must know the

Attunity data source name, location of the Attunity daemon, and the Attunity daemon port number. You specify a unique Attunity server workspace name.

Data Integrator’s format for accessing Attunity tables is unique to Data Integrator. Since a single datastore can access multiple software systems that do not share the same namespace, the name of the Attunity data source must be specified when referring to a table. With an Attunity Connector, precede the table name with the data source and owner names separated by a colon. The format is as follows:

AttunityDataSource:OwnerName.TableName

When using the Designer to create your jobs with imported Attunity tables, Data Integrator automatically generates the correct SQL for this format. However, when you author SQL, be sure to use this format. You can author SQL in the following constructs:

• SQL function

• SQL transform

• Pushdown_sql function

• Pre-load commands in table loader

• Post-load commands in table loader

For information about how to specify multiple data sources in one Attunity datastore, see ““Specifying multiple data sources in one

Attunity datastore” on page 83.

82 Data Integrator Designer Guide

Datastores

Database datastores

6. If you want to change any of the default options (such as Rows per

Commit or Language), click the Advanced button. For general

information about these options see, “Defining a database datastore” on page 86.

7. Click OK.

Y ou can now use the new dat astore connection to import metadat a tables into the current Data Integrator repository.

Specifying multiple data sources in one Attunity datastore

You can use the Attunity Connector datastore to access multiple Attunity dat a sources on the same Attunity Daemon location. If you have several types of data on the same computer, for example a DB2 database and VSAM, you might want to access both types of data using a single connection. For example, you can use a single connection to join tables (and push the join operation down to a remote server), which reduces the amount of data transmitted through your network.

To specify multiple sources in the Datastore Editor, separate data source names with semicolons in the Attunity data source box using the following format:

AttunityDataSourceName;AttunityDataSourceName

For example, if you have a DB2 data source named DSN4 and a VSAM data source named Navdemo, enter the following values into the Data source box:

DSN4;Navdemo

Requirements for an Attunity Connector datastore

Data Integrator requires the following for Attunity Connector datastores:

• For any table in Data Integrator, the maximum size of the owner name is

64 characters. In the case of Attunity tables, the maximum size of the Attunity data source name and actual owner name is 63 (the ":" accounts for 1 character). Data Integrator cannot access a table with an owner name larger than 64 characters.

• If you list multiple data source names for one Attunity Connector

datastore, ensure that you meet the following requirements:

• All Attunity data sources must be accessible by the same user name

and password.

• All Attunity data sources must use the same workspace. When you

setup access to the data sources in Attunity Studio, use the same workspace name for each data source.

Data Integrator Designer Guide 83

Datastores

Database datastores

IBM Connector

Limitations

All Data Integrator features are available when you use an Attunity Connector datastore except the following:

• Bulkloading

• Imported functions (imports metadata for tables only)

• Template tables (creating tables)

• The datetime data type supports up to 2 sub-seconds only

• Data Integrator cannot load timestamp dat a into a timest amp column in a

table because Attunity truncates varchar data to 8 characters, which is not enough to correctly represent a timestamp value.

Data Integrator provides the IBM Connector datastore that accesses mainframe data sources through IBM DB2 Information Integrator Classic Federation for z/OS. The data sources that IBM Connector accesses include the following list. For a complete list of sources, refer to IBM documentation.

• Adabas

• DB2 UDB for z/OS

• CA-Datacom/DB

• CA-IDMS/DB

• IMS/DB

• VSAM under CICS

• VSAM

• flat files on OS/390

IBM Connector accesses mainframe data using software that you must manually install on the mainframe server and the local client (Job Server) computer. Data Integrator connects to IBM DB2 II Classic Federation for z/OS using its respective ODBC interface.

It is not necessary to purchase a separate driver manager for UNIX and Windows platforms.

Servers

Install and configure the IBM product on a server (for example, a zSeries computer).

84 Data Integrator Designer Guide

Datastores

Database datastores



To set up the mainframe environment for IBM Connector

1. Use DataMapper to create a relational description of mainframe data

sources.

2. Install DB2 II Classic Federation Server and components.

Clients

To access mainframe data using IBM DB2 II Classic Federation for z/OS, install its ODBC driver.

Configure ODBC data sources on the client. For IBM, the name of the ODBC data source on the client must match the data source name on the server. For example, if the name on the server side is CACSAMP, the name of the ODBC data source on the client must be CACSAMP. These names are case- sensitive.

When you install a Data Integrator Job Server on UNIX, the installer will prompt you to provide an installation directory path for IBM connector software. In addition, you do not need to install a driver manager, because Data Integrator loads ODBC drivers directly on UNIX platforms.

For more information about how to install and configure these products, refer to their documentation.

Configuring an IBM datastore

To use the IBM Connector datastore option, upgrade your repository to Data Integrator version 6.5.1 or higher.

To create an IBM Connector datastore



1. In the Datastores tab of the object library, right-click and select New.

2. Enter a name for the datastore.

3. In the Datastore type box, select Database.

4. In the Database type box, select IBM Connector.

5. In the Data source box, enter the ODBC data source name. Ensure that

this name matches the data source name on the mainframe server.

6. If you want to change any of the default options (such as Rows per

Commit or Language), click the Advanced button.

For general information about these options see, ““Defining a database

datastore” on page 86.

7. Click OK.

Y ou can now use the new dat astore connection to import metadat a tables into the current Data Integrator repository.

Data Integrator Designer Guide 85

Datastores

Database datastores

Limitations

All Data Integrator features are available when you use an IBM Connector datastore, except the following:

• Bulkloading

• Date, time, and timestamp data types are recognized as varchar,

because IBM's DB2 II Classic Federation for z/OS product does not natively support these types

• Template tables (creating as a target on Attunity)

• The datetime data type supports up to two-digit sub-seconds

• Importing primary and foreign keys

• Template tables with a primary key on DB2 for z/OS (MVS)

• Varchar columns of size 255 or greater

• View Data’s Column Profile feature

Defining a database datastore

Define at least one database datastore for each database or mainframe file system with which you are exchanging data.

T o define a datastore, get appropriate access privileges to the database or file system that the datastore describes.

For example, to allow Data Integrator to use parameterized SQL when reading or writing to DB2 databases, authorize the user (of the datastore/ database) to create, execute and drop stored procedures. If a user is not authorized to create, execute and drop stored procedures jobs will still run. However, they will produce a warning message and will run less efficiently.

T o define a Database datastore



1. In the Datastores tab of the object library, right-click and select New.

2. Enter the name of the new datastore in the Datastore Name field.

The name can contain any alphabetical or numeric characters or underscores (_). It cannot contain spaces.

3. Select the Datastore type.

Choose Database. When you select a Datastore Type, Data Integrator displays other options relevant to that type.

4. Select the Database type.

Choose from Attunity Connector, DB2, IBM Connector, Informix, Memory, Microsoft SQL Server, ODBC, Oracle, Sybase ASE, Syba se IQ, or Teradata.

86 Data Integrator Designer Guide

Datastores

Database datastores

5. Enter the appropriate information for the selected database type.

6. At this point, you can save the datastore or add more information to it:

• To save the datastore and close the Datastore Editor, click OK.

Data Integrator Designer Guide 87

Datastores

Database datastores

• To add more information, select Advanced.

To enter values for each configuration option, click the cells under each configuration name.

See “Datastore” on page 54 of the Data Integrator Reference Guide for a description of the options in the grid for each database.

88 Data Integrator Designer Guide

Datastores

Database datastores

For the datastore as a whole, the following buttons are available:

Buttons Description

Import unsupported data types as VARCHAR of size

Edit Opens the Configurations for

Show ATL Opens a text window that

OK Saves selections and closes

Cancel Cancels selections and closes

Apply Saves selections.

The data types that Data Integrator supports are documented in the Reference Guide. If you want Data Integrator to convert a data type in your source that it would not normally support, select this option and enter the number of characters that you will allow.

Datastore dialog. Use the tool bar on this window to add, configure, and manage multiple configurations for a datastore.

displays how Data Integrator will code the selections you make for this datastore in its scripting language.

the Datastore Editor (Create New Datastore) window.

the Datastore Editor window.

For more information about creating multiple configurations for a single datastore, see “Creating and managing multiple datastore configurations” on page 112.

7. Click OK.

8. See “Ways of importing met adata” on page 97 for the procedures you will

use to import metadata from the connected database or application.

Changing a datastore definition

Like all Data Integrator objects, datastores are defined by both options and properties:

• Options control the operation of objects. For example, the name of the

database to connect to is a datastore option.

Data Integrator Designer Guide 89

Datastores

Database datastores

• Properties document the object. For example, the name of the datastore

and the date on which it was created are datastore properties. Properties are merely descriptive of the object and do not affect its operation.

T o ch ange datastore options



1. Go to the Datastores tab in the object library.

2. Right-click the datastore name and choose Edit.

The Datastore Editor appears in the workspace (the title bar for this dialog displays Edit Datastore). You can change the connection information for the current datastore configuration, click Advanced and change properties for the current configuration, or click Edit to add, edit, or delete additional configurations. Once you add a new configuration to an existing datastore, you can use the fields in the grid to change connection values and properties for the new configuration. See

“Database datastores” on page 66 of the Data Integrator Reference

Guide for a detailed description of the options on the Configurations for Datastore dialog (opens when you select Edit in the Datastore Editor).

3. Click OK.

The options take effect immediately.

T o chang e datastore properties



1. Go to the datastore tab in the object library.

2. Right-click the datastore name and select Properties.

The Properties window opens.

3. Change the datastore properties.

The individual properties available for a datastore are described in

“Datastore” on page 54 of the Data Integrator Reference Guide.

4. Click OK.

Browsing metadata through a database datastore

Data Integrator stores metadata information for all imported objects in a datastore. You can use Data Integrator to view metadata for imported or nonimported objects and to check whether the metadata has changed for objects already imported.

To view imported objects



1. Go to the Datastores tab in the object library.

90 Data Integrator Designer Guide

Datastores

Database datastores

2. Click the plus sign (+) next to the datastore name to view the object types

in the datastore. For example, database datastores have functions, tables, and template tables.

3. Click the plus sign (+) next to an object type to view the objects of that

type imported from the datastore. For example, click the plus sign (+) next to tables to view the imported

tables.

Data Integrator Designer Guide 91

Datastores

Database datastores



T o so rt the lis t of objects

Click the column heading to sort the objects in each grouping and the groupings in each datastore alphabetically. Click again to sort in reversealphabetical order.

Column heading

T o view datastore metadata



1. Select the Datastores tab in the object library.

2. Choose a datastore, right-click, and select Open. (Alternatively, you can

double-click the datastore icon.) Data Integrator opens the datastore explorer in the workspace. The

datastore explorer lists the tables in the datastore. You can view tables in the external database or tables in the internal repository. You can also search through them. For more information about the search feature, see

“To import by searching” on page 101.

3. Select External metadata to view tables in the external database.

If you select one or more tables, you can right-click for further options.

Command Description

Open Import Imports (or re-imports) metadata from the

Reconcile Checks for differences between metadata in the

1. Only available if you select one table.

92 Data Integrator Designer Guide

Opens the editor for the table metadata.

database into the repository.

database and metadata in the repository.

Datastores

Database datastores

4. Select Repository metadata to view imported tables.

If you select one or more tables, you can right-click for further options.

Command Description

Open Reconcile Checks for differences between metadata in the

Reimport Reimports metadata from the database into the

Delete Deletes the table or tables from the repository. Properties

View Data Opens the View Data window which allows you to see

1. Only available if you select one table.



1. In the browser window showing the list of repository tables, select

2. Choose the table or tables you want to check for changes.

3. Right-click and choose Reconcile.

To determine if a schema is has changed since it was imported

External Metadata.

The Changed column displays YES to indicate that the database tables differ from the metadata imported into Data Integrator. To use the most recent metadata from Data Integrator, reimport the table.

The Imported column displays YES to indicate that the table has been imported into the repository.

Opens the editor for the table metadata.

repository and metadata in the database.

repository.

Shows the properties of the selected table.

the data currently in the table.



To browse the metadata for an external table

1. In the browser window showing the list of external tables, select the table

you want to view.

Data Integrator Designer Guide 93

Datastores

Database datastores

2. Right-click and choose Open.

A table editor appears in the workspace and displays the schema and attributes of the table.

94 Data Integrator Designer Guide

Database datastores



To view the metadata for an imported table

1. Select the table name in the list of imported tables.

2. Right-click and select Open.

A table editor appears in the workspace and displays the schema and attributes of the table.

Datastores



To view secondary index information for tables

Secondary index information can help you understand the schema of an imported table.

1. From the datastores tab in the Designer, right-click a table to open the

shortcut menu.

2. From the shortcut menu, click Properties to open the Properties window.

3. In the Properties window, click the Indexes tab. The left portion of the

window displays the Index list.

Data Integrator Designer Guide 95

Datastores

Database datastores

4. Click an index to see the contents.

Importing metadata through a database datastore

For database datastores, you can import metadata for tables and functions. This section discusses:

• Imported table information

• Imported stored function and procedure information

• Ways of importing metadata

• Reimporting objects

Imported table information

Data Integrator determines and stores a specific set of metadata information for tables. After importing metadata, you can edit column names, descriptions, and data types. The edits are propagated to all objects that call these objects.

Metadata Description

Table name The name of the table as it appears in the

Table description The description of the table. Column name The name of the table column. Column description The description of the column. Column data type The data type for each column.

96 Data Integrator Designer Guide

database.

If a column is defined as an unsupported data type, Data Integrator converts the data type to one that is supported. In some cases, if Data Integrator cannot convert the data type, it ignores the column entirely.

Metadata Description

Primary key column The column(s) that comprise the primary key for the

table. After a table has been added to a data flow

diagram, these columns are indicated in the column list by a key icon next to the column name.

Table attribute Information Data Integrator records about the table

such as the date created and date modified if these values are available.

Owner name Name of the table owner.

Imported stored function and procedure information

Data Integrator can import stored procedures from DB2, MS SQL Server, Oracle, and Sybase ASE, and Sybase IQ databases. You can also import stored functions and packages from Oracle. You can use these functions and procedures in the extraction specifications you give Data Integrator.

Information that is imported for functions includes:

• Function parameters

• Return type

• Name, owner

Imported functions and procedures appear on the Datastores tab of the object library. Functions and procedures appear in the Function branch of each datastore tree.

You can configure imported functions and procedures through the function wizard and the smart editor in a category identified by the datastore name. For more information, see “About procedures” on page 552 of the Data Integrator Reference Guide.

Datastores

Database datastores

Ways of importing metadata

This section discusses methods you can use to import metadata:

• To import by browsing

• To import by name

• To import by searching

Data Integrator Designer Guide 97

Datastores

Database datastores



T o import by browsing

Note: Functions cannot be imported by browsing.

1. Open the object library.

2. Go to the Datastores tab.

3. Select the datastore you want to use.

4. Right-click and choose Open.

The items available to import through the datastore appear in the workspace.

In some environments, the tables are organized and displayed as a tree structure. If this is true, there is a plus sign (+) to the left of the name. Click the plus sign to navigate the structure.

The workspace contains columns that indicate whether the table has already been imported into Data Integrator (Imported) and if the table schema has changed since it was imported (Changed). To verify whether the repository contains the most recent metadata for an object, right-click the object and choose Reconcile.

5. Select the items for which you want to import metadata.

For example, to import a table, you must select a table rather than a folder that contains tables.

6. Right-click and choose Import.

7. In the object library, go to the Datastores tab to display the list of

imported objects.

98 Data Integrator Designer Guide

Datastores

Database datastores



To import by name

1. Open the object library.

2. Click the Datastores tab.

3. Select the datastore you want to use.

4. Right-click and choose Import By Name.

5. In the Import By Name window, choose the type of item you want to

import from the Type list. If you are importing a stored procedure, select Function.

6. Specify the items you want imported. Note: Options vary by database type.

• For tables:

• Enter a table name in the Name box to specify a particular table,

or select the All check box, if available, to specify all tables. If the name is case-sensitive in the database (and not all

uppercase), enter the name as it appears in the database and use double quotation marks (") around the name to preserve the case.

• Enter an owner name in the Owner box to limit the specified

tables to a particular owner. If you leave the owner name blank, you specify matching tables regardless of owner (that is, any table with the specified table name).

Data Integrator Designer Guide 99

Datastores

Database datastores

• For functions and procedures:

• In the Name box, enter the name of the function or stored procedure.

If the name is case-sensitive in the database (and not all uppercase), enter the name as it appears in the database and use double quotation marks (") around the name to preserve the case. Otherwise, Data Integrator will convert names into all upper-case characters.

You can also enter the name of a package. An Oracle package is an encapsulated collection of related program objects (e.g., procedures, functions, variables, constants, cursors, and exceptions) stored together in the database. Data Integrator allows you to import procedures or functions created within packages and use them as top-level procedures or functions.

If you enter a package name, Data Integrator imports all stored procedures and stored functions defined within the Oracle package. You cannot import an individual function or procedure defined within a package.

• Enter an owner name in the Owner box to limit the specified

functions to a particular owner. If you leave the owner name blank, you specify matching functions regardless of owner (that is, any function with the specified name).

• If you are importing an Oracle function or stored procedure and

any of the following conditions apply, clear the Callable from SQL expression check box. A stored procedure cannot be pushed down to a database inside another SQL statement when the stored procedure contains a DDL statement, ends the current transaction with COMMIT or ROLLBACK, or issues any ALTER SESSION or ALTER SYSTEM commands.

7. Click OK.

100 Data Integrator Designer Guide

Business objects DATA INTEGRATOR 11.0.1 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Welcome

Overview of this document

Audience and assumptions

More Data Integrator product documentation

Logging in to the Designer

Creating a Data Integrator repository

Associating the repository with a Job Server

Entering repository information

Version restrictions

Oracle login

Microsoft SQL Server login

Informix login

IBM DB2 login

Sybase ASE login

Resetting users

Designer user interface

Data Integrator objects

Reusable objects

Single-use objects

Object hierarchy

Designer window

Menu bar

Project menu

Edit menu

View menu

Tools menu

Debug menu

Validation menu

Window menu

Help menu

Toolbar

Project area

Tool palette

Workspace

Moving objects in the workspace area

Connecting and disconnecting objects

Describing objects

Scaling the workspace

Arranging workspace windows

Closing workspace windows

Local object library

Object editors

Working with objects

Creating new reusable objects

Changing object names

Viewing and changing object properties

General tab

Attributes tab

Class Attributes tab

Creating descriptions

Creating annotations

Saving and deleting objects

Searching for objects

General and environment options

Projects and Jobs

Projects

Objects that make up a project

Creating new projects

Opening existing projects

Saving projects

Jobs

Creating jobs

Naming conventions for objects in jobs

Datastores

What are datastores?

Database datastores

Mainframe interfaces

Attunity Connector

Servers

Clients

Configuring an Attunity datastore

Specifying multiple data sources in one Attunity datastore

Requirements for an Attunity Connector datastore

IBM Connector

Limitations