bods reference en

SAP Data ServicesDocument Version: 4.2 Support Package 1 (14.2.1.0) - 2014-02-06

Reference Guide

Table of Contents1 Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .131.1 Welcome to SAP Data Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

1.1.1 Welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .131.1.2 Documentation set for SAP Data Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.1.3 Accessing documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .151.1.4 SAP information resources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

1.2 Overview of this guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .171.2.1 About this guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171.2.2 Who should read this guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

2 Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182.1 Characteristics of objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

2.1.1 Object classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .182.1.2 Object options, properties, and attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

2.2 Descriptions of objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202.2.1 Annotation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232.2.2 Batch Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232.2.3 Catch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312.2.4 COBOL copybook file format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352.2.5 Conditional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .422.2.6 Data flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432.2.7 Datastore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .452.2.8 Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 902.2.9 DTD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 912.2.10 Excel workbook format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 992.2.11 File format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1052.2.12 Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1162.2.13 HDFS file format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1182.2.14 Log. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1202.2.15 Message function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1242.2.16 Outbound message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1252.2.17 Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1262.2.18 Query transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1262.2.19 Real-time job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1272.2.20 Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1312.2.21 Source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1322.2.22 Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1412.2.23 Target. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1452.2.24 Target Writer migrated from Data Quality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .181

2 2014 SAP AG or an SAP affiliate company. All rights reserved.Reference Guide

Table of Contents

2.2.25 Template table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1812.2.26 Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1842.2.27 Try . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1852.2.28 While loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1862.2.29 Work flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1872.2.30 XML file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1892.2.31 XML message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1902.2.32 XML schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1932.2.33 XML template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205

3 Smart editor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2073.1 Accessing the smart editor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2073.2 Smart editor options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

3.2.1 Smart editor toolbar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2083.2.2 Editor Library pane. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2083.2.3 Editor pane. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

3.3 To browse for a function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2113.4 To search for a function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

4 Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2134.1 Descriptions of data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .213

4.1.1 date. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2134.1.2 datetime. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2154.1.3 decimal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2164.1.4 double . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2164.1.5 int (integer). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2174.1.6 interval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2174.1.7 Limitations for long and blob. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2174.1.8 numeric. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2214.1.9 real. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2224.1.10 time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2224.1.11 timestamp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2234.1.12 varchar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

4.2 Data type conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2254.2.1 Date arithmetic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2254.2.2 Conversion to or from internal data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2264.2.3 Conversion of data types within expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2474.2.4 Conversion among number data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2484.2.5 Conversion between explicit data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2504.2.6 Conversion between native data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .250

5 Transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2545.1 Operation codes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

Reference GuideTable of Contents 2014 SAP AG or an SAP affiliate company. All rights reserved. 3

5.2 Descriptions of transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2555.3 Data Integrator transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

5.3.1 Data_Transfer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2575.3.2 Date_Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2655.3.3 Effective_Date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2675.3.4 Hierarchy_Flattening . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2705.3.5 History_Preserving . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2775.3.6 Key_Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2835.3.7 Map_CDC_Operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2845.3.8 Pivot (Columns to Rows) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2905.3.9 Reverse Pivot (Rows to Columns) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2945.3.10 Table_Comparison. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2965.3.11 XML_Pipeline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305

5.4 Data Quality transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3095.4.1 Transform configurations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3095.4.2 Downloading blueprints and other content objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3145.4.3 Dynamic transform settings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3155.4.4 About Data Quality fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3165.4.5 Associate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3185.4.6 Country ID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3325.4.7 Data Cleanse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3345.4.8 DSF2 Walk Sequencer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3585.4.9 Geocoder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3645.4.10 Global Address Cleanse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3835.4.11 Global Suggestion List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4405.4.12 Match. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4495.4.13 USA Regulatory Address Cleanse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5035.4.14 User-Defined . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5575.4.15 Address Cleanse reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5655.4.16 Data Cleanse reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611

5.5 Platform transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6195.5.1 Case. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6195.5.2 Map_Operation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6225.5.3 Merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6265.5.4 Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6275.5.5 Row_Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6535.5.6 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6555.5.7 Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6585.5.8 XML_Map. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668

5.6 Text Data Processing transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6885.6.1 Entity Extraction transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 688


Table of Contents

6 Functions and Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6986.1 About functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .698

6.1.1 Functions compared with transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6986.1.2 Operation of a function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6986.1.3 Arithmetic in date functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6996.1.4 Including functions in expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .699

6.2 Built-in functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7016.2.1 Database and application functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 701

6.3 Descriptions of built-in functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7026.3.1 abs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7106.3.2 add_months. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7106.3.3 ascii. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7116.3.4 avg. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7126.3.5 base64_decode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7136.3.6 base64_encode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7146.3.7 before_image. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7146.3.8 cast. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7156.3.9 chr. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7176.3.10 ceil. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7186.3.11 concat_date_time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7196.3.12 count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7196.3.13 count_distinct. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7206.3.14 current_configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7226.3.15 current_system_configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7226.3.16 dataflow_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7236.3.17 datastore_field_value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7236.3.18 date_diff. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7246.3.19 date_part . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7256.3.20 day_in_month. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7276.3.21 day_in_week. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7276.3.22 day_in_year. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7286.3.23 db_type. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7296.3.24 db_version. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7306.3.25 db_database_name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7326.3.26 db_owner. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7336.3.27 decode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7346.3.28 decrypt_aes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7356.3.29 decrypt_aes_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7366.3.30 double_metaphone. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7376.3.31 encrypt_aes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7396.3.32 encrypt_aes_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 740


6.3.33 error_context. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7416.3.34 error_message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7426.3.35 error_number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7426.3.36 error_timestamp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7436.3.37 exec. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7436.3.38 extract_from_xml. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7476.3.39 file_exists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7486.3.40 fiscal_day. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7496.3.41 floor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7506.3.42 gen_row_num_by_group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7516.3.43 gen_row_num . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7536.3.44 gen_uuid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7536.3.45 get_domain_description. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7546.3.46 get_env. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7546.3.47 get_error_filename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7556.3.48 get_file_attribute. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7566.3.49 get_monitor_filename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7576.3.50 get_trace_filename . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7586.3.51 greatest. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7596.3.52 host_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7606.3.53 ifthenelse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7616.3.54 index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7626.3.55 init_cap. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7636.3.56 interval_to_char. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7646.3.57 is_group_changed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7656.3.58 is_set_env. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7666.3.59 is_valid_date. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7676.3.60 is_valid_datetime. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7686.3.61 is_valid_decimal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7706.3.62 is_valid_double. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7716.3.63 is_valid_int. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7716.3.64 is_valid_real. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7726.3.65 is_valid_time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7736.3.66 isempty . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7746.3.67 isweekend . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7756.3.68 job_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7766.3.69 Job_Run_ID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7766.3.70 julian. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7776.3.71 julian_to_date. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7786.3.72 key_generation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7786.3.73 last_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779


Table of Contents

6.3.74 least. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7806.3.75 length . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7816.3.76 literal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7826.3.77 ln. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7846.3.78 load_to_xml. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7856.3.79 log. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7866.3.80 long_to_varchar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7876.3.81 lookup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7886.3.82 lookup_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7916.3.83 lookup_seq. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7976.3.84 lower . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8006.3.85 lpad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8016.3.86 lpad_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8026.3.87 ltrim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8036.3.88 ltrim_blanks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8056.3.89 ltrim_blanks_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8056.3.90 mail_to. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8066.3.91 match_pattern. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8086.3.92 match_regex . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8116.3.93 match_simple. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8166.3.94 max. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8186.3.95 min. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8186.3.96 mod. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8196.3.97 month. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8206.3.98 num_to_interval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8216.3.99 nvl. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8226.3.100 power. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8236.3.101 previous_row_value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8246.3.102 print . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8256.3.103 pushdown_sql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8266.3.104 quarter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8286.3.105 raise_exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8296.3.106 raise_exception_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8296.3.107 rand. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8306.3.108 rand_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8316.3.109 replace_substr . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8326.3.110 replace_substr_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8326.3.111 repository_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8346.3.112 round. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8356.3.113 rpad. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8356.3.114 rpad_ext. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 836


6.3.115 rtrim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8386.3.116 rtrim_blanks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8386.3.117 rtrim_blanks_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8396.3.118 sap_openhub_processchain_execute. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8406.3.119 sap_openhub_set_read_status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8436.3.120 search_replace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8446.3.121 set_cdc_checkpoint. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8496.3.122 set_env . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8496.3.123 sleep . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8506.3.124 soundex. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8516.3.125 sql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8526.3.126 sqrt. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8546.3.127 smtp_to. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8546.3.128 substr. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8576.3.129 sum. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8586.3.130 sysdate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8596.3.131 system_user_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8606.3.132 systime. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8606.3.133 table_attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8616.3.134 to_char. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8626.3.135 to_date. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8646.3.136 to_decimal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8656.3.137 to_decimal_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8666.3.138 total_rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8676.3.139 trunc. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8686.3.140 truncate_table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8696.3.141 upper. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8706.3.142 varchar_to_long. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8726.3.143 wait_for_file. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8726.3.144 week_in_month. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8746.3.145 week_in_year. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8746.3.146 WL_GetKeyValue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8766.3.147 word. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8766.3.148 word_ext . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8776.3.149 workflow_name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8786.3.150 year. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 879

6.4 Custom functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8806.4.1 To create a custom function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8816.4.2 To edit an existing function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8826.4.3 To replicate a custom function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8826.4.4 To delete a custom function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 882


Table of Contents

6.5 About procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8836.5.1 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8836.5.2 Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8836.5.3 Creating stored procedures in a database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8846.5.4 Importing metadata for stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8886.5.5 Structure of a stored procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8896.5.6 Calling stored procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8906.5.7 Checking execution status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .894

7 Scripting Language. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8967.1 To use the scripting language. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8967.2 Language syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 896

7.2.1 Syntax for statements in scripts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8967.2.2 Syntax for column and table references in expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 8977.2.3 Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8977.2.4 Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8997.2.5 Variable interpolation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8997.2.6 Functions and stored procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9007.2.7 Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9007.2.8 NULL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9027.2.9 Debugging and Validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9047.2.10 Keywords. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 905

7.3 Sample scripts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9077.3.1 Square function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9077.3.2 RepeatString function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 907

8 Metadata in Repository Tables and Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9098.1 Auditing metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .909

8.1.1 AL_AUDIT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9098.1.2 AL_AUDIT_INFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 910

8.2 Imported metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9118.2.1 AL_INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9118.2.2 AL_PCOLUMN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9118.2.3 AL_PKEY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9128.2.4 ALVW_COLUMNATTR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9128.2.5 ALVW_COLUMNINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9138.2.6 ALVW_FKREL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9148.2.7 ALVW_MAPPING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9158.2.8 ALVW_TABLEATTR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9218.2.9 ALVW_TABLEINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .921

8.3 Internal metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9228.3.1 AL_LANG. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 922


8.3.2 AL_LANGXMLTEXT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9238.3.3 AL_ATTR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9238.3.4 AL_SETOPTIONS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9248.3.5 AL_USAGE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9258.3.6 ALVW_FUNCINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9288.3.7 ALVW_PARENT_CHILD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 928

8.4 Operational metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9298.4.1 AL_HISTORY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9298.4.2 ALVW_FLOW_STAT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .930

9 Data Quality repository statistics tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9329.1 Data Quality repository statistics tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9329.2 Repository tables common columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9349.3 Repository tables for USA and Global address cleanse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 934

9.3.1 ADDRINFOCODEDATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9369.3.2 ADDRINFOCODESUMMARY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9379.3.3 ADDRSTATUSCODEDATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9389.3.4 ADDRTYPESUMMARY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9389.3.5 ADDRVALIDATESUMMARY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9409.3.6 CSLSTATS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9419.3.7 DPVLACSLINKSUMMARY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9429.3.8 DSFAUGMENTSTATS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9449.3.9 DSFSEQUENCESTATS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9459.3.10 MEDSTATS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9469.3.11 NCOALCERTIFICATIONDATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9479.3.12 NCOALINKSUMMARY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9519.3.13 PAFBALASTATS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9529.3.14 PSFORM3553DATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9539.3.15 SENDRIGHTADDRACCURACY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9569.3.16 SERPADDRACCURACY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9579.3.17 USREGULATORYLOCKING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .959

9.4 Geocoder repository statistics tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9609.4.1 GEO_ASSIGN_LEVEL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9609.4.2 GEO_INFO_CODE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .961

9.5 Match repository statistics tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9629.5.1 MTBRACTION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9639.5.2 MTBRINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9649.5.3 MTBRKGRP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9659.5.4 MTBRKGRPINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9669.5.5 MTCMPCRIT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9679.5.6 MTCRITDEF. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9689.5.7 MTDUPESDATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 970


Table of Contents

9.5.8 MTGSSRCBYSRCSTS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9709.5.9 MTGSSRCSTS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9719.5.10 MTINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9729.5.11 MTINSRCBYSRC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9729.5.12 MTINSRCGRPINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9739.5.13 MTINSRCINFO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9749.5.14 MTINSRCMSRC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9759.5.15 MTINSRCSELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9769.5.16 MTINSRCSTATS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9799.5.17 MTKEYDEF. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9809.5.18 MTPROCESS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9819.5.19 MTRULESRES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 982

10 Locales and Multi-byte Functionality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98310.1 Language packs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 983

10.1.1 To set locales in the Designer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98310.1.2 To set locales in the Locale Selector. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98410.1.3 To set locales in UNIX or Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98410.1.4 Impact of locale settings on Data Services components. . . . . . . . . . . . . . . . . . . . . . . . . . . 984

10.2 Locale support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98510.2.1 Locale selection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98610.2.2 Code page support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98810.2.3 Guidelines for setting locales. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .991

10.3 Multi-byte support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99510.3.1 Multi-byte string functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99510.3.2 Numeric data types: assigning constant values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99510.3.3 Byte Order Mark characters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99610.3.4 Round-trip conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99710.3.5 Column sizing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 997

10.4 Limitations of multi-byte support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99710.5 Definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99810.6 Supported locales and encodings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 999

11 Python. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100311.1 Python. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1003

11.1.1 About Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100311.1.2 Create an expression with the Python Expression editor. . . . . . . . . . . . . . . . . . . . . . . . . . 100711.1.3 Built-in objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101011.1.4 Defined classes and methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101111.1.5 FlDataCollection class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101211.1.6 FlDataManager class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101711.1.7 FlDataRecord class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1019


11.1.8 FlProperties class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102211.1.9 FlPythonString class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102311.1.10 Python examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1025

12 Hadoop. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103112.1 Prerequisites. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1031

12.1.1 Common commands for correct setup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103212.1.2 Configuring Hadoop for text data processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1033

12.2 Connecting to HDFS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103412.3 Connecting to Hive. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1034

12.3.1 Adding, configuring, and starting a Hive adapter instance . . . . . . . . . . . . . . . . . . . . . . . . . 103412.3.2 Adding and configuring a Hive adapter datastore. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103512.3.3 About partitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1036

13 Reserved Words. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103713.1 About Reserved Words. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1037

14 Glossary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1040


Table of Contents

1 Introduction

1.1 Welcome to SAP Data Services

1.1.1 Welcome

SAP Data Services delivers a single enterprise-class solution for data integration, data quality, data profiling, and text data processing that allows you to integrate, transform, improve, and deliver trusted data to critical business processes. It provides one development UI, metadata repository, data connectivity layer, run-time environment, and management consoleenabling IT organizations to lower total cost of ownership and accelerate time to value. With SAP Data Services, IT organizations can maximize operational efficiency with a single solution to improve data quality and gain access to heterogeneous sources and applications.

1.1.2 Documentation set for SAP Data Services

You should become familiar with all the pieces of documentation that relate to your SAP Data Services product. The latest Data Services documentation can be found on the SAP Help Portal.Document What this document providesAdapter SDK Guide Information about installing, configuring, and running the Data Services Adapter SDK Administrator Guide Information about administrative tasks such as monitoring, lifecycle management,

security, and so on.Customer Issues Fixed Information about customer issues fixed in this release.

NoteIn some releases, this information is displayed the Release Notes.

Designer Guide Information about how to use Data Services Designer.Documentation Map Information about available Data Services books, languages, and locations.Installation Guide for Windows

Information about and procedures for installing Data Services in a Windows environment.

Installation Guide for UNIX

Information about and procedures for installing Data Services in a UNIX environment.

Integrator Guide Information for third-party developers to access Data Services functionality using web services and APIs.

Master Guide Information about the application, its components and scenarios for planning and designing your system landscape. Information about SAP Information Steward is also provided in this guide.

Reference GuideIntroduction 2014 SAP AG or an SAP affiliate company. All rights reserved. 13

Document What this document providesManagement Console Guide

Information about how to use Data Services Administrator and Data Services Metadata Reports.

Performance Optimization Guide

Information about how to improve the performance of Data Services.

Reference Guide Detailed reference material for Data Services Designer.Release Notes Important information you need before installing and deploying this version of Data

Services.Technical Manuals A compiled, searchable, master PDF of core Data Services books:

Administrator Guide Designer Guide Reference Guide Management Console Guide Performance Optimization Guide Integrator Guide Supplement for J.D. Edwards Supplement for Oracle Applications Supplement for PeopleSoft Supplement for Salesforce.com Supplement for Siebel Supplement for SAP Workbench Guide

Text Data Processing Extraction Customization Guide

Information about building dictionaries and extraction rules to create your own extraction patterns to use with Text Data Processing transforms.

Text Data Processing Language Reference Guide

Information about the linguistic analysis and extraction processing features that the Text Data Processing component provides, as well as a reference section for each language supported.

Tutorial A step-by-step introduction to using Data Services.Upgrade Guide Information to help you upgrade from previous releases of Data Services and re

lease-specific product behavior changes from earlier versions of Data Services to the latest release.

What's New Highlights of new key features in this SAP Data Services release. This document is not updated for support package or patch releases.

Workbench Guide Provides users with information about how to use the Workbench to migrate data and database schema information between different database systems.

In addition, you may need to refer to several Supplemental Guides.Document What this document providesSupplement for SAP Information about interfaces between Data Services, SAP Applications, SAP

Master Data Services, SAP NetWeaver BW, and SAP Master Data Services.


Introduction

Document What this document providesSupplement for SuccessFactors Information about interfaces between Data Services and SuccessFactors.Supplement for Salesforce.com Information about how to install, configure, and use the SAP Data Services

Salesforce.com Adapter Interface.Supplement for J.D. Edwards Information about interfaces between Data Services and J.D. Edwards World

and J.D. Edwards OneWorld.Supplement for Oracle Applications

Information about the interface between Data Services and Oracle Applications.

Supplement for PeopleSoft Information about interfaces between Data Servicesand PeopleSoft.Supplement for Siebel Information about the interface between Data Services and Siebel.

We also include these manuals for information about SAP BusinessObjects Information platform services.Document What this document providesInformation platform services Administrator Guide Information for administrators who are responsible for

configuring, managing, and maintaining an Information platform services installation.

Information platform services Installation Guide for UNIX

Installation procedures for SAP BusinessObjects Information platform services on a UNIX environment.

Information platform services Installation Guide for Windows

Installation procedures for SAP BusinessObjects Information platform services on a Windows environment.

1.1.3 Accessing documentationYou can access the complete documentation set for SAP Data Services in several places.

1.1.3.1 Accessing documentation on WindowsAfter you install SAP Data Services, you can access the documentation from the Start menu.1. Choose Start Programs SAP Data Services 4.2 Data Services Documentation All Guides .2. Click the appropriate shortcut for the document that you want to view.

1.1.3.2 Accessing documentation on UNIXAfter you install SAP Data Services, you can access the documentation by going to the directory where the printable PDF files were installed.1. Go to /doc/book/en/.


2. Using Adobe Reader, open the PDF file of the document that you want to view.

1.1.3.3 Accessing documentation from the WebYou can access the complete documentation set for SAP Data Services from the SAP Business Users Support site.To do this, go to http://help.sap.com/bods.You can view the PDFs online or save them to your computer.

1.1.4 SAP information resourcesA global network of SAP technology experts provides customer support, education, and consulting to ensure maximum information management benefit to your business.Useful addresses at a glance:

Address ContentCustomer Support, Consulting, and Education services

http://service.sap.com/

Information about SAP support programs, as well as links to technical articles, downloads, and online forums. Consulting services can provide you with information about how SAP can help maximize your information management investment. Education services can provide information about training options and modules. From traditional classroom learning to targeted e-learning seminars, SAP can offer a training package to suit your learning needs and preferred learning style.

Product documentationhttp://help.sap.com/bods/

SAP product documentation.

Supported Platforms (Product Availability Matrix)

https://service.sap.com/PAM

Get information about supported platforms for SAP Data Services.Use the search function to search for Data Services. Click the link for the version of Data Services you are searching for.

SAP Data Services Community Network

http://scn.sap.com/community/data-services

Get online and timely information about SAP Data Services, including forums, tips and tricks, additional downloads, samples, and much more. All content is to and from the community, so feel free to join in and contact us if you have a submission.

Blueprints

http://scn.sap.com/docs/DOC-8820

Blueprints for you to download and modify to fit your needs. Each blueprint contains the necessary SAP Data Services project, jobs, data flows, file formats, sample data, template tables, and custom functions to run the data flows in your environment with only a few modifications.


Introduction

Address ContentSAPTerm

https://portal.wdf.sap.corp/go/sapterm

SAPs terminology database, the central repository for defining and standardizing the use of specialist terms.

1.2 Overview of this guide

1.2.1 About this guideThe Data Services Reference Guide provides detailed information about the objects, data types, transforms, and functions in the Designer.For source-specific information, such as information pertaining to a particular back-office application, refer to the supplement for that application.

1.2.2 Who should read this guideThis and other SAP Data Services software documentation assume the following: You are a software developer, consultant, or database administrator working on data extraction, data

warehousing, data integration, or data quality. You understand your source and target data systems, DBMS, legacy systems, 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 software to design real-time processing, you are familiar with:

DTD and XML Schema formats for XML files Publishing Web Services (WSDL, HTTP/S and SOAP protocols, and so on.)

You are familiar with the installation environments: Microsoft Windows or UNIX.


2 ObjectsThis section provides a reference of detailed information about the objects, data types, transforms, and functions in the Designer.

NoteFor information about source-specific objects, consult the reference section in the supplement document for that source.

Related Information

Characteristics of objects [page 18]Descriptions of objects [page 20]

2.1 Characteristics of objectsThis section discusses common characteristics of all the objects.

Related Information

Object classes [page 18]Object options, properties, and attributes [page 20]

2.1.1 Object classes An object's class determines how you create and retrieve the object. There are two classes of objects: Reusable objects Single-use objects

2.1.1.1 Reusable objectsAfter you define and save a reusable object, SAP Data Services stores the definition in the repository. You can then reuse the definition as often as necessary by creating calls to the definition.


Objects

Most objects created in the software are available for reuse. You access reusable objects through the object library.A reusable object has a single definition; all calls to the object refer to that definition. If you change the definition of the object in one place, and then save the object, the change is reflected to all other calls to the object.A data flow, for example, is a reusable object. Multiple jobs, such as a weekly load job and a daily load job, can call the same data flow. If the data flow is changed, both jobs call the new version of the data flow.When you drag and drop an object from the object library, you are creating a new reference (or ) to the existing object definition.You can edit reusable objects at any time independent of the current open project. For example, if you open a new project, you can go to the object library, open a data flow, and edit it. The object will remain "dirty" (that is, your edited changes will not be saved) until you explicitly save it.Functions are reusable objects that are not available in the object library. The software provides access to these objects through the function wizard wherever they can be used.Some objects in the object library are not reusable in all instances: Datastores are in the object library because they are a method for categorizing and accessing external

metadata. Built-in transforms are "reusable" in that every time you drop a transform, a new instance of the transform is

created."Saving" a reusable object means storing the language that describes the object to the repository. The description of a reusable object includes these components: Properties of the object Options for the object Calls this object makes to other objects Definition of single-use objects called by this objectIf an object contains a call to another reusable object, only the call to the second object is saved, not changes to that object's definition.The description is stored even if the object is not successfully validated.Objects are saved without prompting you: When you import an object into the repository. When you finish editing:

Datastores Flat file formats XML Schema or DTD formats

You can explicitly save the reusable object currently open in the workspace by choosing Save from the Project menu. If a single-use object is open in the workspace, the Save command is not available.To save all objects in the repository that have changes, choose Save All from the Project menu.You are prompted to save all objects that have changes when you execute a job and when you exit the Designer.

Reference GuideObjects 2014 SAP AG or an SAP affiliate company. All rights reserved. 19

2.1.1.2 Single-use objects

Single-use objects appear only as components of other objects. They operate only in the context in which they were created."Saving" a single-use object means storing the language that describes the object to the repository. The description of a single-use object can only be saved as part of the reusable object that calls the single-use object.The description is stored even if the object does not validate.

2.1.2 Object options, properties, and attributes

Each object is associated with a set of options, properties, and attributes: Options: Controls the operation of an object. For example, in a datastore, an option is the name of the

database to which the datastore connects. Properties: Documents an object. For example, properties include the name, description of an object, and the

date on which it was created. Properties merely describe an object; they do not affect an object's operation.To view properties, right-click an object and select Properties.

Attributes: Provides additional information about an object. Attribute values may also affect an object's behavior.To view attributes, double-click an object from an editor and click the Attributes tab.

2.2 Descriptions of objects

This section describes each object and tells you how to access that object.The following table lists the names and descriptions of the objects.

Object Class Description

Annotation Single-use Describes a flow, part of a flow, or a diagram in the workspace.

Catch Single-use Specifies the steps to execute if anerror occurs in a given exception group while a job is running.

COBOL copybook file format

Reusable Defines the format for a COBOL copybook file source.

Conditional Single-use Specifies the steps to execute based on the result of a condition.

Batch Job Reusable Defines activities that the software executes at a given time including error, monitor and trace messages.


Objects


Jobs can be dropped only in the project tree. The object created is a direct reference to the object in the object library. Only one reference to a job can exist in the project tree at one time.

Data flow Reusable Specifies the requirements for extracting, transforming, and loading data from sources to targets.

Datastore Single-use Specifies the connection information needed to access a database or other data source. Cannot be dropped.

Document Reusable Available in certain adapter datastores, documents are data structures that can support complicated nested schemas.

DTD Reusable A description of an XML file or message. Indicates the format an XML document reads or writes.

Excel workbook format

Reusable Defines the format for an Excel workbook source.

File format Reusable Indicates how flat file data is arranged in a source or target file.

Function Reusable Returns a value.

Log Single-use Records information about a particular execution of a single job.

Message function Reusable Available in certain adapter datastores, message functions can accommodate XML messages when properly configured.

Outbound message Reusable Available in certain adapter datastores, outbound messages are XML-based, hierarchical communications that real-time jobs can publish to adapters.

Project Single-use Groups jobs for convenient access.

Query transform Single-use Retrieves a data set that satisfies conditions that you specify.

Real-time job Reusable Defines activities that the software executes on-demand.Real-time jobs are created in the Designer, then configured and run as services associated with an Access Server in the Administrator. Real-time jobs are designed according to data flow model rules and run as a request-response system.

Script Single-use Evaluates expressions, calls functions, and assigns values to variables.

Source Single-use An object from which the software reads data in a data flow.



Table Reusable Indicates an external DBMS table for which metadata has been imported, or the target table into which data is or has been placed.A table is associated with its datastore; it does not exist independently of a datastore connection. A table retrieves or stores data based on the schema of the table definition from which it was created.

Target Single-use An object in which the software loads extracted and transformed data in a data flow.

Template table Reusable A new table you want added to a database.All datastores except SAP datastores have a default template that you can use to create any number of tables in the datastore.The software creates the schema for each instance of a template table at runtime. The created schema is based on the data loaded into the template table.

Transform Reusable Performs operations on data sets.Requires zero or more data sets; produces zero or one data set (which may be split).

Try Single-use Introduces a try/catch block.

While loop Single-use Repeats a sequence of steps as long as a condition is true.

Work flow Reusable Orders data flows and operations supporting data flows.

XML file Single-use A batch or real-time source or target. As a source, an XML file translates incoming XML-formatted data into data that the software can process. As a target, an XML file translates the data produced by a data flow, including nested data, into an XML-formatted file.

XML message Single-use A real-time source or target. As sources, XML messages translate incoming XML-formatted requests into data that a real-time job can process. As targets, XML messages translate the result of the real-time job, including hierarchical data, into an XML-formatted response and sends the messages to the Access Server.

XML Schema Reusable A description of an XML file or message. Indicates the format an XML document reads or writes.

XML template Single-use A target that creates an XML file that matches a particular input schema. No DTD or XML Schema is required.


Objects

2.2.1 Annotation

Class

Single-use

Access

Click the annotation icon in the tool palette, then click in the workspace.

Description

Annotations describe a flow, part of a flow, or a diagram in a workspace. An annotation is associated with the job., work flow, or data flow where it appears. When you import or export that job, work flow, or data flow, you import or export associated annotations.

NoteAn annotation has no options or properties.

Related Information

Designer Guide: Creating annotations

2.2.2 Batch Job


Class

Reusable

Access

In the object library, click the Jobs tab. In the project area, select a project and right-click Batch Job.

Description

A batch job is a set of objects that you can schedule and execute together. To execute the steps of any object, the object must be part of a job.A batch job can contain the following objects: Data flows

Sources Transforms Targets

Work flows Scripts Conditionals Try/catch blocks While LoopsYou can run batch jobs such that you can automatically recover from jobs that do not execute successfully. During automatic recovery, SAP Data Services retrieves the results from steps that were successfully completed in the previous run and executes all other steps. Specifically, the software retrieves results from the following types of steps: Work flows Data flows Script statements Custom functions (stateless type only) SQL function EXEC function get_env function rand function sysdate function systime functionBatch jobs have the following built-in attributes:


Objects

Attribute Description

Name The name of the object. This name appears on the object in the object library and in the calls to the object.

Description Your description of the job.

Date created The date when the object was created.

Batch and real-time jobs have properties that determine the information collected and logged when running the job. You can set the default properties that apply each time you run the job or you can set execution (run-time) properties that apply for a particular run. Execution properties override default properties.To set default properties, select the job in the project area or the object library, right-click, and choose Properties to open the Properties window.Execution properties are set as you run a job. To set execution properties, right-click the job in the project area and choose Execute. The Designer validates the job and opens the Execution Properties window.You can set several types of execution properties: Execution options Trace properties Global variables Substitution parameters

Related Information

Designer Guide: Setting global variable values

2.2.2.1 Execution Options

Use execution options to help capture and diagnose errors using log, auditing, statistics collection, or recovery options.Log information goes to one of three files (in the \log\\ directory): Monitor log file Trace log file Error log fileWhen you execute a job, you can also select a system configuration and a Job Server or server group from the Execution Options tab of the Execution Properties window.Select the Execution Options tab to set the following options.


Options DescriptionCollect statistics for monitoring

Select this check box if you want to display cache statistics in the Performance Monitor in the Administrator. (The default is cleared.)

NoteUse this option only if you want to look at the cache size.

For more information, see the Using Caches section in the Performance Optimization Guide.

Collect statistics for optimization

Select this check box if you want to collect statistics that the Data Services optimizer will use to choose an optimal cache type (in-memory or pageable). This option is not selected by default.For more information, see Caching sources in the Performance Optimization Guide.For more information, see the Using Caches section in the Performance Optimization Guide.

Disable data validation statistics collection

Select this check box if you do not want to collect data validation statistics for any validation transforms in this job. (The default is cleared.)For more information about data validation statistics, see Data Validation dashboards Settings control panel" in the Management Console Guide.For more information about data validation statistics, see the Data Validation Dashboard Reports section in the Management Console Guide.

Distribution level Select the level within a job that you want to distribute to multiple job servers for processing: Job - The whole job will execute on an available Job Server. Data flow - Each data flow within the job can execute on an available Job Server. Sub data flow - Each sub data flow (can be a separate transform or function) within

a data flow can execute on an available Job Server.For more information, see Using grid computing to distribute data flows execution in the Performance Optimization Guide.For more information, see the Distributing Data Flow Execution section in the Performance Optimization Guide.

Enable auditing Clear this check box if you do not want to collect audit statistics for this specific job execution. (The default is selected.)For more information about auditing, see Using Auditing in the Designer Guide.For more information about auditing, see the Data Assessment section in the Designer Guide.

Enable recovery (Batch jobs only) Select this check box to enable the automatic recovery feature. When enabled, the software saves the results from completed steps and allows you to resume failed jobs. You cannot enable the automatic recovery feature when executing a job in data scan mode.


Objects

Options Description

See Automatically recovering jobs in the Designer Guide for information about the recovery options.See the Recovery Mechanisms section in the Designer Guide for information about the recovery options.See Loading Big Data file with Recovery option in the Designer Guide for information about big data file recovery options.This property is only available as a run-time property. It is not available as a default property.

Export Data Quality reports

Generates and exports all specified job reports to the location specified in the Report Server Configuration node in the Management Console Administrator. By default, the reports are exported to \DataQuality\reports\.

Job Server or Server group

Select the Job Server or server group to execute this job. A Job Server is defined by a host name and port while a server group is defined by its name. The list contains Job Servers and server groups linked to the job's repository.For an introduction to server groups, see Server group architecture in the Management Console Guide.For an introduction to server groups, see the Server Groups section in the Management Console Guide.When selecting a Job Server or server group, remember that many objects in the Designer have options set relative to the Job Server's location. For example: Directory and file names for source and target files Bulk load directories

Monitor sample rate(# of seconds)

Enter the number of seconds elapsed before the software writes information to the monitor log file and updates job events. The software writes information about the status of each source, target, or transform.For example, if you enter 30, the logs update every 30 seconds.The default is 5 seconds. When setting the value, you must evaluate performance improvements gained by making fewer calls to the operating system against your ability to find errors quickly. With a higher monitor sample rate, more data collects before calling the operating system to open the file: performance improves. However, with a higher monitor rate, more time passes before you are able to see any errors.Setting the rate to 0 disables the monitoring feature.

NoteIf you use a virus scanner on your files, exclude the log from the virus scan. Otherwise, the virus scan analyzes the log repeated during the job execution, which causes a performance degradation.


Options DescriptionPrint all trace messages

Select this check box to print all trace messages to the trace log file for the current Job Server.Selecting this option overrides the trace properties set on the Trace tab.

Recover from last failed execution

(Batch Job only) Select this check box to resume a failed job. The software retrieves the results from any steps that were previously executed successfully and re-executes any other steps.This option is a run-time property. This option is not available when a job has not yet been executed or when recovery mode was disabled during the previous run.

System configuration Select the system configuration to use when executing this job. A system configuration defines a set of datastore configurations, which define the datastore connections.For more information, see Creating and managing multiple datastore configurations in the Designer Guide.For more information, see the Datastores section in the Designer Guide.If a system configuration is not specified, the software uses the default datastore configuration for each datastore.This option is a run-time property. This option is only available if there are system configurations defined in the repository.

Use collected statistics

Select this check box if you want the optimizer to use the cache statistics collected on a previous execution of the job. (The default is selected.)For more information, see Monitoring and tuning caches in the Performance Optimization Guide.For more information, see section 6: Using Caches in t