Wednesday, March 31, 2010
Tuesday, March 30, 2010
Ascential PACK for SAP R/3
ABAP:- Advanced Business Application Programming. The
language developed by SAP for application development
purposes. All R/3 applications are written in ABAP
BAPI:- Business Application Programming Interface. A precisely
defined interface providing access to processes and data
in business application systems. BAPIs are defined as
API methods of SAP objects. These objects and their
methods are stored in the Business Objects Repository.
BOR:- Business Object Repository, which is the object-oriented
Repository in the SAP BW system. It contains, among
other objects, SAP Business Objects and their methods.
Business Object:-The representation of a business entity, such as an
employee or a sales order, in the SAP Enterprise system.
Control record:-A special administrative record within an IDoc, one for each IDoc. The control record contains a standard set of
fields that describe the IDoc as a whole.
ERP:- Enterprise Resource Planning business management
software.
IDoc:- Intermediate document. An IDoc is a report, that is, a
hierarchal package of related records, generated by R/3
Enterprise in an SAP proprietary format. An IDoc, whose
transmission is initiated by the source database,
exchanges data between applications.
IDoc type:-Named meta data describing the structure of an IDoc that
is shared across databases. It consists of a hierarchy of
segment record types.
MATMAS01:- An example of an IDoc type.
PACK:- Packaged Application Connection Kit. Accesses and
extracts data from and loads data to R/3 Enterprise.
PSA:- Persistent Staging Area.
R/3:- Real time/three tiers.
RFC:- Remote Function Call. The SAP implementation of RPC
(Remote Procedure Call) in ABAP. It calls a function
module that runs on a different system from the calling
function. The Remote Function Call can also be called
from within the same system, but usually the caller and
callee are dispersed.
RFM:- Remote Function Module. A function that belongs to a
BOR object type and has a BAPI method name.
SAP:- Systems, Applications, and Products in Data Processing.
SAP is a product of SAP AG, Walldorf, Germany.
SCM:- Supply Chain Management. The solution that tracks
financial, informational, and materials processes and
identifies processing exceptions.
Segment:- A record within an IDoc that is identified by a segment
number.
Segment type:-A named record definition for segments within an IDoc
that is one level in the hierarchy of segment types within
an IDoc type.
tRFC:- port Transactional RFC port.
Variant:- A collection of predefined criteria, similar to a group of
values used as parameters. Variants are attached to various processes used by
DataStage for the ABAP program process, for example. The ABAP Program referenced by the ABAP Program process itself has a variant attached to it.)
language developed by SAP for application development
purposes. All R/3 applications are written in ABAP
BAPI:- Business Application Programming Interface. A precisely
defined interface providing access to processes and data
in business application systems. BAPIs are defined as
API methods of SAP objects. These objects and their
methods are stored in the Business Objects Repository.
BOR:- Business Object Repository, which is the object-oriented
Repository in the SAP BW system. It contains, among
other objects, SAP Business Objects and their methods.
Business Object:-The representation of a business entity, such as an
employee or a sales order, in the SAP Enterprise system.
Control record:-A special administrative record within an IDoc, one for each IDoc. The control record contains a standard set of
fields that describe the IDoc as a whole.
ERP:- Enterprise Resource Planning business management
software.
IDoc:- Intermediate document. An IDoc is a report, that is, a
hierarchal package of related records, generated by R/3
Enterprise in an SAP proprietary format. An IDoc, whose
transmission is initiated by the source database,
exchanges data between applications.
IDoc type:-Named meta data describing the structure of an IDoc that
is shared across databases. It consists of a hierarchy of
segment record types.
MATMAS01:- An example of an IDoc type.
PACK:- Packaged Application Connection Kit. Accesses and
extracts data from and loads data to R/3 Enterprise.
PSA:- Persistent Staging Area.
R/3:- Real time/three tiers.
RFC:- Remote Function Call. The SAP implementation of RPC
(Remote Procedure Call) in ABAP. It calls a function
module that runs on a different system from the calling
function. The Remote Function Call can also be called
from within the same system, but usually the caller and
callee are dispersed.
RFM:- Remote Function Module. A function that belongs to a
BOR object type and has a BAPI method name.
SAP:- Systems, Applications, and Products in Data Processing.
SAP is a product of SAP AG, Walldorf, Germany.
SCM:- Supply Chain Management. The solution that tracks
financial, informational, and materials processes and
identifies processing exceptions.
Segment:- A record within an IDoc that is identified by a segment
number.
Segment type:-A named record definition for segments within an IDoc
that is one level in the hierarchy of segment types within
an IDoc type.
tRFC:- port Transactional RFC port.
Variant:- A collection of predefined criteria, similar to a group of
values used as parameters. Variants are attached to various processes used by
DataStage for the ABAP program process, for example. The ABAP Program referenced by the ABAP Program process itself has a variant attached to it.)
Saturday, March 27, 2010
000-418 DataStage Test
Question # 1
You have a cluster of nodes available to run DataStage jobs. The network configuration between the servers is a private network with a 1 GB connection between each node. The public name is on a 100 MB network, which is what each hostname is identified with. In order to use the private network for communications between each
node you need to use an alias for each node in the cluster. The Information Server Engine
node (conductor node) is where the DataStage job starts.
Which environment variable must be used to identify the hostname for the Engine node?
A. $APT_SERVER_ENGINE
B. $APT_ENGINE_NODE
C. $APT_PM_CONDUCTOR_HOSTNAME
D. $APT_PM_NETWORK_NAME
Question # 2
When reading a delimited Sequential File, you are instructed to interpret two contiguous field delimiters as NULL for the corresponding field regardless of data type.
Which three actions must you take? (Choose three.)
A. Set the data type to Varchar.
B. Set the field to nullable.
C. Set the "NULL Field Value" to two field delimiters (e.g., "||" for pipes).
D. Set the "NULL Field Value" to ''.
E. Set the environment variable $APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.
Question # 3
Which two attributes are found in a Data Set descriptor file? (Choose two.)
A. A copy of the job score.
B. The schema of the Data Set.
C. A copy of the partitioned data.
D. A copy of the configuration file used when Data Set was created.
Question # 4
Test A parallel job reads from a DB2 table containing 15 million records, and uses a Sparse Lookup against an Oracle table containing 30 million records. Both tables are indexed on their join key columns. The Oracle reference table is larger than available
shared memory. Only those records whose key values have matching reference data are sent to the target DB2 table.
Which redesign change would improve performance while still meeting functional requirements?
A. Replace the Sparse Lookup with custom SQL to join the two source tables.
B. Change the Sparse Lookup to a Normal Lookup.
C. Replace the Sparse Lookup with an Inner Join stage.
D. Replace the Sparse Lookup and Oracle read with custom SQL and a DB2
stored procedure to access the remote Oracle table.
Question # 5
A DataStage job contains a parallel Transformer with a single input link and a single output link. The Transformer has a constraint that should produce 1000 records,
however only 900 came out through the output link.
What should be done to identify the missing records?
A. Turn trace on using DataStage Administrator.
B. Add a Reject link to the Transformer stage.
C. Scan generated osh script for possible errors.
D. Remove the constraint on the output link.
Question # 6
You are assigned to write a job which reads a DB2 database, applies business logic, and writes the results to an Oracle database. However, the table name and layout may change from one run to another. You are guaranteed that a core set of columns required to perform the business logic will always be present, though not necessarily in
the same order. Which three features would you use to build this job? (Choose three.)
A. Table read method
B. Partition Table option
C. Load method
D. Partition Column option
E. Runtime Column Propagation
Question # 7
You are using the Change Capture stage in your job design to identify changes made to the input link (before image) to obtain the results in the output link. This job will be using a multi-node configuration file when executed. The Change Capture stage produces an output change link with which two characteristics? (Choose two.)
A. Table Definition based on the keys of the input link
B. Table Definition based on the after input link
C. added column named Change_Code
D. added column named Difference_Value
Question # 8
What are three characteristics of containers? (Choose three.)
A. Containers are used to execute jobs.
B. Containers are a group of stages and links.
C. Containers allow you to simplify complex job designs.
D. Containers allow you to provide pre-configured job design logic.
E. Containers allow multiple users to access a single job.
Question # 9
In the Repository Advanced Find interface, which three properties can be used to constrain the results of impact analysis? (Choose three.)
A. Type
B. Export category
C. Owner name
D. Dependencies of
E. Creation
Question # 10
You are building a job which uses Runtime Column Propagation throughout. However, at run-time, the job aborts with an error message indicating that one of the key columns for a sort is not present on the record when it arrives at the sort.
What would help debug the run-time environment?
A. $OSH_PRINT_DROPPED_COLUMNS
B. $OSH_PRINT_SCHEMAS
C. $OSH_SHOW_COMPONENT_CALLS
D. $OSH_SHOW_STARTUP_STATUS
You have a cluster of nodes available to run DataStage jobs. The network configuration between the servers is a private network with a 1 GB connection between each node. The public name is on a 100 MB network, which is what each hostname is identified with. In order to use the private network for communications between each
node you need to use an alias for each node in the cluster. The Information Server Engine
node (conductor node) is where the DataStage job starts.
Which environment variable must be used to identify the hostname for the Engine node?
A. $APT_SERVER_ENGINE
B. $APT_ENGINE_NODE
C. $APT_PM_CONDUCTOR_HOSTNAME
D. $APT_PM_NETWORK_NAME
Question # 2
When reading a delimited Sequential File, you are instructed to interpret two contiguous field delimiters as NULL for the corresponding field regardless of data type.
Which three actions must you take? (Choose three.)
A. Set the data type to Varchar.
B. Set the field to nullable.
C. Set the "NULL Field Value" to two field delimiters (e.g., "||" for pipes).
D. Set the "NULL Field Value" to ''.
E. Set the environment variable $APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.
Question # 3
Which two attributes are found in a Data Set descriptor file? (Choose two.)
A. A copy of the job score.
B. The schema of the Data Set.
C. A copy of the partitioned data.
D. A copy of the configuration file used when Data Set was created.
Question # 4
Test A parallel job reads from a DB2 table containing 15 million records, and uses a Sparse Lookup against an Oracle table containing 30 million records. Both tables are indexed on their join key columns. The Oracle reference table is larger than available
shared memory. Only those records whose key values have matching reference data are sent to the target DB2 table.
Which redesign change would improve performance while still meeting functional requirements?
A. Replace the Sparse Lookup with custom SQL to join the two source tables.
B. Change the Sparse Lookup to a Normal Lookup.
C. Replace the Sparse Lookup with an Inner Join stage.
D. Replace the Sparse Lookup and Oracle read with custom SQL and a DB2
stored procedure to access the remote Oracle table.
Question # 5
A DataStage job contains a parallel Transformer with a single input link and a single output link. The Transformer has a constraint that should produce 1000 records,
however only 900 came out through the output link.
What should be done to identify the missing records?
A. Turn trace on using DataStage Administrator.
B. Add a Reject link to the Transformer stage.
C. Scan generated osh script for possible errors.
D. Remove the constraint on the output link.
Question # 6
You are assigned to write a job which reads a DB2 database, applies business logic, and writes the results to an Oracle database. However, the table name and layout may change from one run to another. You are guaranteed that a core set of columns required to perform the business logic will always be present, though not necessarily in
the same order. Which three features would you use to build this job? (Choose three.)
A. Table read method
B. Partition Table option
C. Load method
D. Partition Column option
E. Runtime Column Propagation
Question # 7
You are using the Change Capture stage in your job design to identify changes made to the input link (before image) to obtain the results in the output link. This job will be using a multi-node configuration file when executed. The Change Capture stage produces an output change link with which two characteristics? (Choose two.)
A. Table Definition based on the keys of the input link
B. Table Definition based on the after input link
C. added column named Change_Code
D. added column named Difference_Value
Question # 8
What are three characteristics of containers? (Choose three.)
A. Containers are used to execute jobs.
B. Containers are a group of stages and links.
C. Containers allow you to simplify complex job designs.
D. Containers allow you to provide pre-configured job design logic.
E. Containers allow multiple users to access a single job.
Question # 9
In the Repository Advanced Find interface, which three properties can be used to constrain the results of impact analysis? (Choose three.)
A. Type
B. Export category
C. Owner name
D. Dependencies of
E. Creation
Question # 10
You are building a job which uses Runtime Column Propagation throughout. However, at run-time, the job aborts with an error message indicating that one of the key columns for a sort is not present on the record when it arrives at the sort.
What would help debug the run-time environment?
A. $OSH_PRINT_DROPPED_COLUMNS
B. $OSH_PRINT_SCHEMAS
C. $OSH_SHOW_COMPONENT_CALLS
D. $OSH_SHOW_STARTUP_STATUS
Friday, March 26, 2010
Update Action in DataStage
Specifies which SQL statements are used to update the target table. Some update actions require key columns to update or delete rows. There is no default. Choose the option you want from the list:
Clear table then insert rows. Deletes the contents of the table and adds the new rows, with slower performance because of transaction logging. This is the default value.
Truncate table then insert rows. Truncates the table with no transaction logging and faster performance. For DB2/UDB and Informix, this option is the same as Clear table then insert rows.
Insert rows without clearing. Inserts the new rows in the table.
Delete existing rows only. Deletes existing rows in the target table that have identical keys in the source files.
Replace existing rows completely. Deletes the existing rows, then adds the new rows to the table.
Update existing rows only. Updates the existing data rows. Any rows in the data that do not exist in the table are ignored.
Update existing rows or insert new rows. Updates the existing data rows before adding new rows. It is faster to update first when you have a large number of records.
Insert new rows or update existing rows. Inserts the new rows before updating existing rows. It is faster to insert first if you have only a few records.
Insert new rows only. Inserts the new rows in the table but does not report duplicate rows to the log.
Truncate only. Ignores any incoming data and truncates the target table.
Important. A stage cannot stand alone on the canvas. Configure a dummy source stage. To keep the process simple and to preserve the resource, make sure the dummy source stage does not have any source data.
User-defined SQL. Writes the data using a user-defined SQL statement, which overrides the default SQL statement generated by the stage. If you choose this option, you enter the SQL statement on the SQL tab.
User-defined SQL file. Reads the content of the specified file to write the data.
Clear table then insert rows. Deletes the contents of the table and adds the new rows, with slower performance because of transaction logging. This is the default value.
Truncate table then insert rows. Truncates the table with no transaction logging and faster performance. For DB2/UDB and Informix, this option is the same as Clear table then insert rows.
Insert rows without clearing. Inserts the new rows in the table.
Delete existing rows only. Deletes existing rows in the target table that have identical keys in the source files.
Replace existing rows completely. Deletes the existing rows, then adds the new rows to the table.
Update existing rows only. Updates the existing data rows. Any rows in the data that do not exist in the table are ignored.
Update existing rows or insert new rows. Updates the existing data rows before adding new rows. It is faster to update first when you have a large number of records.
Insert new rows or update existing rows. Inserts the new rows before updating existing rows. It is faster to insert first if you have only a few records.
Insert new rows only. Inserts the new rows in the table but does not report duplicate rows to the log.
Truncate only. Ignores any incoming data and truncates the target table.
Important. A stage cannot stand alone on the canvas. Configure a dummy source stage. To keep the process simple and to preserve the resource, make sure the dummy source stage does not have any source data.
User-defined SQL. Writes the data using a user-defined SQL statement, which overrides the default SQL statement generated by the stage. If you choose this option, you enter the SQL statement on the SQL tab.
User-defined SQL file. Reads the content of the specified file to write the data.
DataStage Job Sequences
DataStage provides a graphical Job Sequencer which allows you to specify a sequence of server or parallel jobs to run. The sequence can also contain control information, for example, you can specify different courses of action to take depending on whether a job in the sequence succeeds or fails. Once you have defined a job sequence, it can be scheduled and run using the DataStage Director. It appears in the DataStage Repository and in the DataStage Director client as a job.
Wednesday, March 17, 2010
Ascential DataStage Parallel Transformer Stage Programming Guide
date Field Functions
Orchestrate performs no automatic type conversion of date fields. Either an input data set must match the operator interface or you must effect a type conversion by means of the transform or modify operator.
21 32 Orchestrate 7.5 Operators Reference date format The default format of the date contained in the string is yyyy-mm-dd. However, you can specify an optional format string that defines another format. The format string requires that you provide enough information for Orchestrate to determine a complete date (either day, month, and year, or year and day of year). The date components of a source string (date, month, and year) must be zero-padded to the character length specified by the format string. Orchestrate zero pads the date components of a destination string to the specified length. The possible format components are:
%dd: A two digit day.
%mm: A two digit month.
%yy: A two digit year derived from a year cutoff of 1900.
%year_cutoffyy: A two digit year derived from yy and the specified year cutoff. (For example, if you specify the year format as %2000yy, two-digit values which represent years from 2000-2099 are imported and exported.)
%yyyy: A four digit year.
%ddd: Day of year in three digit form (range of 1 - 366) The default date format is as follows:
%yyyy-%mm-%dd When you specify a date format string, prefix each component with the percent symbol (%). Separate the string’s components with any character except the percent sign (%).
For example, the format string %mm/%dd/%yyyy specifies that slashes separate the string’s date components; the format %ddd-%yy specifies that the string stores the date as a value from 1 to 366, derives the year from the current year cutoff of 1900, and separates the two components with a dash (-).
Function Description
date date_from_days_since
(int32, “date�? | format_variable) Returns date by adding the given integer to the baseline date. Converts an integer field into a date by adding the integer to the specified base date. The date must be in the format yyyy-mm-dd and must be either double quoted or a variable.
date date_from_julian_day(uint32) Returns the date given a Julian day.
date date_from_string
(string, "date_format" | date_uformat | format_variable) Returns a date from the given string formatted in the optional format specification. By default the string format is yyyy-mm-dd.
date date_from_ustring
(string, "date_format" | date_uformat | format_variable) Returns a date from the given ustring formatted in the optional format specification. By default the ustring format is yyyy-mm-dd.
string string_from_date
(date, "date_format" | date_uformat) Converts the date to a string representation using the given format specification. By default the ustring format is yyyy-mm-dd.
ustring ustring_from_date
(date, "date_format" | date_uformat) Converts the date to a ustring representation using the given format specification. By default the ustring format is yyyy-mm-dd.
date date_from_timestamp(timestamp) Returns the date from the given timestamp.
int32 days_since_from_date
(date, “source_date�? | format_variable) Returns a value corresponding to the number of days from source_date to date. source_date must be in the form yyyy-mm-dd and must be double quoted or be a variable.
uint32 julian_day_from_date(date) Returns a Julian date given the date.
int8 month_day_from_date(date) Returns the day of the month given the date. For
example, the date 07-23-2001 returns 23.
int8 month_from_date(date) Returns the month from the given date. For example,
the date 07-23-2001 returns 7.
date next_weekday_from_date
(date, “day�? | format_variable) The value returned is the date of the specified day of the week soonest after date (including the date). The day argument is optional. It is a string or variable specifying a day of the week. You can specify day by either the first three characters of the day name or the full day name. By default, the value is Sunday.
date previous_weekday_from_date
(date, "day" | format_variable) Returns the previous weekday date from date. The destination contains the closest date for the specified day of the week earlier than the source date (including the source date) The day argument is optional. It is a string or variable specifying a day of the week. You can specify day using either the first three characters of the day name or the full day name. By default, the value is Sunday.
int8 weekday_from_date
(date, "origin_day" | format_variable) Returns the day of the week from date. The optional argument origin_day is a string or variable specifying the day considered to be day zero of the week. You can specify the day using either the first three characters of the day name or the full day name. If omitted, Sunday is day zero.
int16 year_day_from_date(date) Returns the day of the year (1-366) from date.
int16 year_from_date(date) Returns the year from date. For example, the date 07-
23-2001 returns 2001.
int8 year_week_from_date(date) Returns the week of the year from date. For example,
the date 07-23-2001 returns 30.
decimal and float Field Functions
You can do the following transformations using the decimal and float field functions.
Assign a decimal to an integer or float or numeric string, or compare a decimal to an integer or float or numeric string.
Specify an optional fix_zero argument (int8) to cause a decimal field containing all zeros to be treated as a valid zero.
Optionally specify a value for the rounding type (r_type) for many conversions. The values of r_type are:
– ceil: Round the source field toward positive infinity. This mode corresponds to the IEEE 754 Round Up mode. Examples: 1.4 -> 2, -1.6 -> -1 – floor: Round the source field toward negative infinity. This mode corresponds to the IEEE 754 Round Down mode. Examples: 1.6 -> 1, -1.4 -> -2 – round_inf: Round or truncate the source field toward the nearest representable value, breaking ties by rounding positive values toward positive infinity and negative values toward negative infinity. This mode corresponds to the COBOL ROUNDED mode. Examples: 1.4 -> 1, 1.5 -> 2, -1.4 -> -1, -1.5 -> -2
– trunc_zero (default): Discard any fractional digits to the right of the rightmost fractional digit supported in the destination, regardless of sign. For example, if the destination is an integer, all fractional digits are truncated. If the destination is another decimal with a smaller scale, round or truncate to the scale size of the destination decimal. This mode corresponds to the COBOL INTEGER-PART function. Examples: 1.6 -> 1, -1.6 -> -1
decimal decimal_from_decimal
(decimal, "r_type" | format_variable) Returns decimal in decimal representation, changing the precision and scale according to the returned type. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default rtype is trunc_zero.
decimal decimal_from_dfloat
(dfloat, "r_type" | format_variable) Returns dfloat in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
decimal decimal_from_string
(string, "r_type" | format_variable) Returns string in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
decimal decimal_from_ustring
(ustring, "r_type" | format_variable) Returns ustring in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
dfloat dfloat_from_decimal
(decimal, “fix-zero�? | format_variable) Returns decimal in dfloat representation.
int32 int32_from_decimal
(decimal, "r_type fix_zero") Returns int32 in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
int64 int64_from_decimal
(decimal, "r_type fix_zero") Returns int32 in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
uint64 uint64_from_decimal
(decimal, "r_type fix_zero") Returns uint64 in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
string string_from_decimal
(decimal, "fix_zero suppress zero") Returns string in decimal representation. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
ustring ustring_from_decimal
(decimal, "fix_zero suppress_zero�? | format_variable) Returns ustring in decimal representation. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
string string_from_decimal
(decimal, “fix_zero suppress_zero�? | format_variable) Returns ustring in decimal representation. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
dfloat mantissa_from_dfloat(dfloat) Returns the mantissa (the digits right of the decimal point) from dfloat.
dfloat mantissa_from_decimal
(decimal) Returns the mantissa (the digits right of the decimal point) from decimal.
raw Field Functions
Use the raw field functions to transform a string into a raw data type and to determine the length of a raw value.
raw raw_from_string(string) Returns string in raw representation.
raw u_raw_from_string(ustring) Returns ustring in raw representation.
int32 raw_length(raw) Returns the length of the raw field.
time and timestamp Field Functions
Orchestrate performs no automatic conversions to or from the time and timestamp data types. You must use the modify or transform operator if you want to convert a source or destination field. Most field conversions extract a portion of the time, such as hours or minutes, and write it into a destination field. Time conversion to a numeric field can be used with any Orchestrate numeric data type. Orchestrate performs the necessary modifications to translate a conversion result to the numeric data type of the destination. For example, you can use the transformation function hours_from_time() to convert a time to an int8, or to an int16, int32, dfloat, and so on. The string_from_time() and time_from_string() conversion functions take a format as a parameter of the conversion. The default format of the time in the string is hh:nn:ss. However, you can specify an optional format string defining another time format. The format string must contain a specification for hours, minutes, and seconds.
time Uformat
The time uformat provides support for international components in time fields. It’s syntax is: string%macroString%macroString%macroString where %macro is a time formatting macro such as %hh for a two-digit hour. See “time Format�? below for a description of the date format macros. Only the String components of time uformat can include multi-byte Unicode characters.
timestamp Uformat
This format is a concatenation of the date uformat and time uformat which are described in “date Uformat�? on page 21--33 and “time Uformat�? on page 21--38. The order of the formats does not matter, but the two formats cannot be mixed.
time Format
The time components of a source string (hours, minutes, and seconds) must be zero padded to the character length specified by the format string; Orchestrate zero pads the time components of a destination string to the specified length. The components of the format string are:
%hh: A two-digit hours component.
%nn: A two-digit minutes component.
%ss: A two-digit seconds component.
%ss.n: A two-digit seconds component plus a fractional part, where n is the number of fractional digits with a maximum value of 6. If n is 0, no decimal point is printed as part of the seconds component. Trailing zeros are not suppressed. For example, a format string of %hh:%nn:%ss.4 indicates that the * string contains the seconds to four decimal places.
The default time format is as follows: %hh:%nn:%ss When you specify a time format string, prefix each component with the percent symbol (%). Separate the string’s components with any character except the percent sign (%).
time Field and timestamp Field Functions
int8 hours_from_time(time) Returns the hour portion of the given time.
int32 microseconds_from_time(time) Returns the number of microseconds from the
given time.
dfloat midnight_seconds_from_time
(time) Returns the number of seconds from midnight to
time.
int8 minutes_from_time(time) Returns the number of minutes from time.
dfloat seconds_from_time(time) Returns the number of seconds from time.
dfloat seconds_since_from_timestamp
(timestamp, "source_timestamp_string" | format_variable) Returns the number of seconds from timestamp to the base timestamp, or optionally the second timestamp argument for the number of seconds between timestamps. The source_timestamp_string argument must be double quoted or be a variable.
time time_from_midnight_seconds(dfloat) Returns the time given the number of seconds (dfloat) since midnight.
time time_from_string
(string, time_format | time_uformat | format_variable) Returns a time representation of string using the optional time_format, time_uformat, or format_variable. By default, the time format is hh:nn:ss.
time time_from_ustring
(ustring, time_format | time_uformat | format_variable) Returns a time representation of ustring using the optional time_format, time_uformat, or format_variable specification. By default, the time format is hh:nn:ss.
string string_from_time
(time, "time_format" | format_variable | time_uformat) Returns a string from time. The format argument is optional.The default time format is hh:nn:ss.
time time_from_timestamp(timestamp) Returns the time from timestamp.
date date_from_timestamp(timestamp) Returns the date from the given timestamp.
timestamp timestamp_from_date_time
(date, time) Returns a timestamp from date and time. The date specifies the date portion (yyyy-nn-dd) of the timestamp. The time argument specifies the time to be used when building the timestamp. The time argument must be in the hh:nn:ss format.
timestamp timestamp_from_seconds_since
(dfloat, "original_timestamp_string" | format_variable) Returns the timestamp from the number of seconds (dfloat) from the base timestamp or the original_timestamp_string argument. The original_timestamp_string must be double quoted or be a variable.
timestamp timestamp_from_string
(string, "timestamp_format" | timestamp_uformat | format_variable) Returns a timestamp from string, in the optional timestamp_format, timestamp_uformat, or format_variable. The timestamp_format must be double quoted or be a variable. The default format is yyyy-nn-dd hh:nn:ss.
timestamp timestamp_from_ustring
(ustring, "timestamp_format" | timestamp_uformat | format_variable) Returns a timestamp from ustring, in the optional format specification. The timestamp_format must be a double quoted string, a uformat, or a variable. The default format is yyyy-nn-dd hh:nn:ss.
string string_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a string from timestamp. The formatting specification is optional. The default format is yyyy-mm-dd hh:mm:ss.
ustring ustring_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a ustring from timestamp. The formatting specification is optional. The default format is yyyy-mm-dd hh:mm:ss.
timestamp timestamp_from_time(time, time_format | time_uformat)
Returns a timestamp from time. date date_from_timestamp(timestamp) Returns the date from the given timestamp.
timestamp timestamp_from_timet(int32) Returns a timestamp from the given UNIX time_t representation (int32).
int32 timet_from_timestamp(timestamp) Returns the UNIX time_t representation of timestamp.
Null Handling Functions
All Orchestrate data types support nulls. As part of processing a record, an operator can detect a null and take the appropriate action, for example, it can omit the null field from a calculation or signal an error condition. Orchestrate represents nulls in two ways.
It allocates a single bit to mark a field as null. This type of representation is
called an out-of-band null.
It designates a specific field value to indicate a null, for example a numeric
field’s most negative possible value. This type of representation is called an inband null. In-band null representation can be disadvantageous because you must reserve a field value for nulls and this value cannot be treated as valid data elsewhere.
The null-handling functions can change a null representation from an out-of-band null to an in-band null and from an in-band null to an out-of-band null.
destination_field handle_null
(source_field, value) Change the source_field NULL representations from out-ofband representation to an in-band representation. The value field assigns the value that corresponds to NULL.
destination_field make_null
(source_field, value) Changes source_field NULL representation from in-band NULL representation to out-of-band. The value field allows multiple valid NULL values to be inputted as arguments.
int8 notnull(source_field) Returns 1 if source_field is not NULL, otherwise returns 0.
int8 null(source_field) Returns 1 if source_field is NULL, otherwise returns 0.
set_null() This function is used with “=�? to set the left side output field, when it is nullable, to null. For example:
a-field = set_null();
int8 is_dfloat_inband_null
(dfloat) Returns 1 if dfloat is an inband null; otherwise it returns 0.
int8 is_int16_inband_null
(int16) Returns 1 if int16 is an inband null; otherwise it returns 0.
int8 is_int32_inband_null
(int32) Returns 1 if int32 is an inband null; otherwise it returns 0.
int8 is_int64_inband_null
(int64) Returns 1 if int64 is an inband null; otherwise it returns 0.
int8 is_sfloat_inband_null
(sfloat) Returns 1 if sfloat is an inband null; otherwise it returns 0.
int8 is_string_inband_null
(string) Returns 1 if string is an inband null; otherwise it returns 0.
int8 u_is_string_inband_null
(ustring) Returns 1 if ustring is an inband null; otherwise it returns 0. Note Null-handling functions cannot be used for subrecord fields.
Mathematical Functions
int32 abs(int32) Returns the absolute value of int32.
dfloat acos(dfloat) Returns the principal value of the arc cosine of dfloat.
dfloat asin(dfloat) Returns the principal value of the arc sine of dfloat.
dfloat atan(dfloat) Returns the principal value of the arc tangent of dfloat.
dfloat atan2(dfloat, dfloat) Returns the principal value of the arc tangent of y/x (where y is the first argument).
int ceil(decimal) Returns the smallest integer value greater than or equal to
decimal.
dfloat cos(dfloat) Returns the cosine of the given angle (dfloat) expressed in
radians.
dfloat cosh(dfloat) Returns the hyperbolic cosine of dfloat.
dfloat exp(dfloat) Returns the exponential of dfloat.
dfloat fabs(dfloat) Returns the absolute value of dfloat.
int floor(decimal) Returns the largest integer value less than or equal to decimal.
dfloat ldexp(dfloat, int32) Reconstructs dfloat out of the mantissa and exponent of int32.
uint64 llabs(int64) Returns the absolute value of int64.
dfloat log(dfloat) Returns the natural (base e) logarithm of dfloat.
dfloat log10(dfloat) Returns the logarithm to the base 10 of dfloat.
int32 max(int32, int32) Returns the larger of the two integers.
int32 min(int32, int32) Returns the smaller of the two integers.
dfloat pow(dfloat, dfloat) Returns the result of raising x (the first argument) to the power y (the second argument).
uint32 rand() Returns a pseudo-random integer between 0 and 232 - 1. The function uses a multiplicative congruential random-number generator with period 232. See the UNIX man page for rand for more details.
dfloat sin(dfloat) Returns the sine of dfloat expressed in radians.
dfloat sinh(dfloat) Returns the hyperbolic sine of dfloat.
dfloat sqrt(dfloat) Returns the square root of dfloat.
int32 quotient_from_dfloat
(dfloat1, dfloat2) Returns the value of the quotient after dfloat1 is divided bydfloat2.
srand(uint32) Sets a new seed (uint32) for the frand() or srand() random number generator.
srandom(uint32) Sets a random seed for the random() number generator. See the UNIX man page for srandom for more details.
dfloat tan(dfloat) Returns the tangent of the given angle (dfloat) expressed in
radians.
dfloat tanh(dfloat) Returns the hyperbolic tangent of dfloat.
random() Returns a random integer between 0 and 231 - 1. The function uses a nonlinear additive feedback random-number generator employing a default state array size of 31 long integers to return successive pseudo-random numbers. The period of this random-number generator is approximately 16 x (231 - 1).
Compared with rand, random is slower but more random. See the UNIX man page for random for more details.
string Field Functions
Strings can be assigned (=), compared (==, <, >=, etc.), and concatenated (+) in the Transformation Language. In addition, the functions described in the first list below are available for string manipulations, and the functions described in second list are available for ustring manipulations. When a long string is assigned to a short string, the long string is truncated to the length of the short string. The term white space refers to spaces, tabs, and any other blank space.
string Conversions and Lookup Tables
You can construct a string lookup table to use when default conversions do not yield satisfactory results. A string lookup table is a table of two columns and as many rows as are required to perform a conversion to or from a string as shown below. Each row of the lookup table specifies an association between a 16-bit integer or unsigned 32-bit integer value and a string or ustring. Orchestrate scans the Numeric Value or the String or Ustring column until it encounters the value or string to be translated. The output is the corresponding entry in the row. The numeric value to be converted may be of the int16 or the uint32 data type. Orchestrate converts strings to values of the int16 or uint32 data type using the same table. If the input contains a numeric value or string that is not listed in the table, Orchestrate operates as follows:
If a numeric value is unknown, an empty string is returned by default. However, you can set a default string value to be returned by the string lookup table.
If a string has no corresponding value, 0 is returned by default. However, you can set a default numeric value to be returned by the string lookup table.
A table definition defines the rows of a string or ustring lookup table and has the following form: {propertyList} ('string' | 'ustring' = value; 'string' | 'ustring'= value; ... ) where:
propertyList is one or more of the following options; the entire list is enclosed in braces and properties are separated by commas if there are more than one:
case_sensitive: perform a case-sensitive search for matching strings; the default is case-insensitive.
default_value = defVal: the default numeric value returned for a string that does not match any of the strings in the table.
default_string = defString: the default string returned for numeric values that do not match any numeric value in the table.
A String Lookup Table Numeric Value String or Ustring numVal1 string1 | ustring1 numVal2 string2 | ustring1 ... ... numVal3 stringn | ustringn
string or ustring specifies a comma-separated list of strings or ustrings associated with value; enclose each string or ustring in quotes.
value specifies a comma-separated list of 16-bit integer values associated with string or ustring.
string Field Functions
int8 is_alnum(string) Returns 1 (true) if string consists entirely of alphanumeric
characters.
int8 is_alpha(string) Returns 1 (true) if string consists entirely of alphabetic
characters.
int8 is_numeric(string) Returns 1 (true) if string consists entirely of numeric
characters, including decimal and sign.
int8 is_valid
("type_string", "value_string") Returns 1 (true) if value_string is valid according to type_string, including NULL. The type_string argument is required. It must specify an Orchestrate schema data type. integer types are checked to ensure the value_string is numeric (signed or unsigned), a whole number, and a valid value (for example, 1024 can not be assigned to an int8 type). Decimal types are checked to ensure the value_string is numeric (signed or unsigned) and a valid value. Float types are checked to ensure the value_string is numeric (signed or unsigned) and a valid value (exponent is valid). String is always valid with the NULL exception below. For all types, if the field cannot be set to NULL and the string is NULL, 0 (false) is returned. Date, time, and timestamp types are checked to ensure they are correct, using the optional format argument, and valid values. Raw cannot be checked since the input is a string.
int16 lookup_int16_from_string
(string , "table_definition" | table_variable) Returns an integer corresponding to string using table_definition string or variable.
string lookup_string_from_int16
(int16 , "table_definition" | table_variable) Returns a string corresponding to int16 using table_definition string or variable.
string lookup_string_from_uint32
(uint32 , "table_definition" | table_variable) Returns a string corresponding to uint32 using table_definition string or variable. * uint32 lookup_uint32_from_string (string , "table_definition" | table_variable) Returns an unsigned integer from string using table_definition string or variable.
string lower_case(string) Converts string to lowercase. Non-alphabetic characters
are ignored in the transformation.
string string_from_date
(date, "date_format" | format_variable | date_uformat) Converts date to a string representation using the specified optional formatting specification. By default, the date format is yyyy-mm-dd.
string string_from_decimal
(decimal, “fix_zero suppress_zero�? | format_variable) Returns a string from decimal. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1 The formatting specification is optional.
string string_from_time
(time, "time_format" | format_variable | time_uformat) Returns a string from time. The format argument is optional.The default time format is hh:nn:ss.
string string_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a string from timestamp. The formatting specification is optional. The default format is yyyy-mmdd hh:mm:ss.
string soundex
(input_string, length, censusOption) Returns a string which represents the phonetic code for the string input word. Input words that produce the same code are considered phonetically equivalent. The empty string is returned if the input string is empty. length is an int8 and can be any value between 4 and 10. The default is 4. censusOption can be 0, 1, or 2 where 0 (the default) is enhanced soundex and not a census code; 1 are the normal census codes used in all censuses from 1920 on; and 2 are special census codes used intermittently in 1880, 1900, and 1910.
string upper_case(string) Converts string to uppercase. Non-alphabetic characters
are ignored in the transformation.
string compact_whitespace
(string) Returns a string after reducing all consecutive white space in string to a single space.
string pad_string
(string, pad_string, pad_length) Returns the string with the pad_string appended to the bounded length string for pad_length number of characters. pad_length is an int16. When the given string is a variable-length string, it defaults to a bounded-length of 1024 characters. If the given string is a fixed-length string, this function has no effect.
string strip_whitespace(string)
Returns string after stripping all white space in the string.
string trim_leading_trailing
(string) Returns string after removing all leading and trailing white space.
string trim_leading(string) Returns a string after removing all leading white space.
string trim_trailing(string) Returns a string after removing all trailing white space.
int32 string_order_compare
(string1, string2, justification) Returns a numeric value specifying the result of the comparison. The numeric values are: -1: string1 is less than string2 0: string1 is equal to string2 1: string1 is greater than string2 The string justification argument is either 'L' or 'R'. It defaults to 'L' if not specified. 'L' means a standard character comparison, left to right. 'R' means that any numeric substrings within the strings starting at the same position are compared as numbers. For example an 'R' comparison of “AB100�? and “AB99�? indicates that AB100 is great than AB99, since 100 is greater than 99. The comparisons are case sensitive.
string replace_substring
(expression1, expression2, string) Returns a string value that contains the given string, with any characters in expression1 replaced by their corresponding characters in expression2. For example: replace_substring (“ABC:, “abZ�?, “AGDCBDA�?) returns “aGDZbDa�?, where any “A�? gets replaced by “a�?, any “B�? gets replaced by “b�? and any “C�? gets replaced by “Z�?. If expression2 is longer than expression1, the extra characters are ignored. If expression1 is longer than expression2, the extra characters in expression1 are deleted from the given string (the corresponding characters are removed.) For example: replace_substring("ABC", "ab", "AGDCBDA") returns "aGDbDa".
int32 count_substring
(string, substring) Returns the number of times that substring occurs in
string. If substring is an empty string, the number of
characters in string is returned.
int32 dcount_substring
(string, delimiter) Returns the number of fields in string delimited by delimiter, where delimiter is a string. For example, dcount_substring(“abcFdefFghi�?, “F�?) returns 3. If delimiter is an empty string, the number of characters in the string + 1 is returned. If delimiter is not empty, but does not exist in the given string, 1 is returned.
string double_quote_string
(expression) Returns the given string expression enclosed in double quotes.
string substring_by_delimiter
(string, delimiter, occurrence, numsubstr) The string and delimiter argumets are string values, and the occurrence and numsubstr arguments are int32 values. This function returns numsubstr substrings from string, delimited by delimiter and starting at substring number occurence. An example is: substring_by_delimiter (“abcFdefFghiFjkl�?, “F�?, 2, 2) The string “defFghi�? is returned. If occurence is < 1, then 1 is assumed. If occurence does not point to an existing field, the empty string is returned. If numsubstr is not specified or is less than 1, it defaults to 1.
int32 index_of_substring
(string, substring, occurrence) Returns the starting position of the nth occurrence of substring in string. The occurrence argument is an integer indicating the nth occurrence. If there is no nth occurrence or string doesn’t contain any substring, -1 is returned. If substring is an empty string, -2 is returned.
string left_substring
(string, length) Returns the first length characters of string. If length is 0, it returns the empty string. If length is greater than the length of the string, the entire string is returned.
string right_substring
(string, length) Returns the last length characters of string. If length is 0, it returns the empty string. If length is greater than the length of string, the entire string is returned.
string string_of_space(count) Returns a string containing count spaces. The empty string is returned for a count of 0 or less.
string single_quote_string
(expression) Returns the expression string enclosed in single quotes.
string string_of_substring
(string, count) Returns a string containing count occurrences of string. The empty string is returned for a count of 0 or less.
string trimc_string
(string [,character [,option]]) If only string is specified, all leading and trailing spaces and tabs are removed, and all multiple occurrences of spaces and tabs are reduced to a single space or tab. If string and character are specified, option defaults to 'R' The available option values are: 'A' remove all occurrences of character 'B' remove both leading and trailing occurrences of character. 'D' remove leading, trailing, and redundant whitespace characters. 'E' remove trailing white-space characters 'F' remove leading white-space characters 'L' remove all leading occurrences of character 'R' remove all leading, trailing, and redundant occurrences of character 'T' remove all trailing occurrences of character
string system_time_date() Returns the current system time in this 24-hour format:
hh:mm:ss dd:mmm:yyyy
int32 offset_of_substring
(string, substring, position) Searches for the substring in the string beginning at character number position, where position is an uint32. Returns the starting position of the substring.
int8 string_case_compare
(string, string) This is a case-insensitive version of string_compare() below.
int8 string_compare
(string, string) Compares two strings and returns the index (0 or 1) of the greater string.
int8 string_num_case_compare
(string, string, uint16) This is a case-insensitive version of string_num_compare() below.
string string_num_concatenate
(string, string, uint16) Returns a string after appending uint16 characters from the second string onto the first string.
int8 string_num_compare
(string, string, uint16) Compares first uint16 characters of two given strings and returns the index (0 or 1) of the greater string.
string string_num_copy
(string, uint16) Returns the first uint16 characters from the given string int32 string_length(string) Returns the length of the string.
string substring
(string, starting_position, length) Copies parts of strings to shorter strings by string extraction. The starting_position specifies the starting location of the substring; length specifies the substring length. The arguments starting_position and length are uint16 types and must be positive (>= 0).
string char_from_num(int32) Returns an ASCII character from the given int32. If given a value that is not associated with a character such as -1, the function returns a space.
An example use is: char_from_num(38) which returns "&"
int32 num_from_char(string) Returns the numeric value of the ASCII-character in the string. When this function is given an empty string, it returns 0; and when it is given a multi-character string, it uses the first character in the string.
An example use is: num_from_char("&") which returns 38.
ustring Field Functions
Orchestrate provides the ustring type for multi-byte Unicode-character strings. ustrings can be assigned (=), compared (==, <, >=, etc.), and concatenated (+) in the Transformation Language. The term white space refers to spaces, tabs, and any other blank space.
ustring ustring_from_date
(date, "date_format" | date_format | format_variable) Converts date to a ustring representation using the optional format specification. By default, the format is yyyy-mm-dd.
ustring ustring_from_decimal
(decimal, "fix_zero suppress_zero�? | format_variable) Returns a ustring from decimal. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1 The format specification is optional. ustring ustring_from_time (time, "time_format" | time_uformat | format_variable) Returns a ustring from time using an optional format specification.The default time format is hh:nn:ss.
ustring ustring_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a ustring from timestamp. The format specification is optional. The default format is yyyy-mm-dd hh:mm:ss.
int8 u_is_alnum(ustring) Returns 1 (true) if ustring consists entirely of alphanumeric characters.
int8 u_is_alpha(ustring) Returns 1 (true) if ustring consists entirely of alphabetic characters.
int8 u_is_numeric(ustring) Returns 1 (true) if ustring consists entirely of numeric
characters, including decimal and sign.
int16 lookup_int16_from_ustring
(ustring , "table_definition" | table_variable) Returns an integer corresponding to ustring using table_definition string or variable.
ustring lookup_ustring_from_int16
(int16 , "table_definition" | table_variable) Returns a ustring corresponding to int16 using table_definition string or variable. * ustring lookup_ustring_from_uint32 (uint32 , "table_definition" | table_variable) Returns a ustring corresponding to uint32 using table_definition string or variable. * uint32 lookup_uint32_from_ustring (string , "table_definition" | table_variable) Returns an unsigned integer from ustring using table_definition string or variable. int8 u_is_valid ("type_ustring", "value_ustring") Returns 1 (true) if value_ustring is valid according to type_ustring, including NULL. The type_ustring argument is required. It must specify an Orchestrate schema data type. Integer types are checked to ensure the value_ustring is numeric (signed or unsigned), a whole number, and a valid value (for example, 1024 can not be assigned to an int8 type). Decimal types are checked to ensure the value_ustring is numeric (signed or unsigned) and a valid value. Float types are checked to ensure the value_ustring is numeric (signed or unsigned) and a valid value (exponent is valid). String is always valid with the NULL exception below. For all types, if the field cannot be set to NULL and the string is NULL, 0 (false) is returned. Date, time, and timestamp types are checked to ensure they are correct, using the optional format argument, and valid values. Raw cannot be checked since the input is a string. ustring u_lower_case(ustring) Converts ustring to lowercase. Non-alphabetic characters are ignored in the transformation.
ustring u_upper_case(ustring)
Converts ustring to uppercase. Non-alphabetic characters are ignored in the transformation.
ustring u_compact_whitespace (ustring)
Returns the ustring after reducing all consecutive white space in ustring to a single space.
ustring u_pad_string
(ustring, pad_ustring, pad_length) Returns the ustring with pad_ustring appended to the bounded length string for pad_length number of characters. pad_length is an int16. When the given ustring is a variable-length string, it defaults to a bounded-length of 1024 characters. If the given ustring is a fixed-length string, this function has no effect.
ustring u_strip_whitespace
(ustring) Returns ustring after stripping all white space in the string.
ustring u_trim_leading_trailing
(ustring) Returns ustring after removing all leading and trailing white space.
ustring u_trim_leading(ustring) Returns ustring after removing all leading white space.
ustring u_trim_trailing
(ustring) Returns a ustring after removing all trailing white space.
int32 u_string_order_compare
(ustring1, ustring2, justification) Returns a numeric value specifying the result of the comparison. The numeric values are: -1: ustring1 is less than ustring2 0: ustring1 is equal to ustring2 1: ustring1 is greater than ustring2 The string justification argument is either 'L' or 'R'. It defaults to 'L' if not specified. 'L' means a standard character comparison, left to right. 'R' means that any numeric substrings within the strings starting at the same position are compared as numbers. For example an 'R' comparison of “AB100�? and “AB99�? indicates that AB100 is great than AB99, since 100 is greater than 99. The comparisons are case sensitive.
ustring u_replace_substring
(expression1, expression2, ustring) Returns a ustring value that contains the given ustring, with any characters in expression1 replaced by their corresponding characters in expression2. For example: u_replace_substring (“ABC", “abZ�?, “AGDCBDA�?) returns “aGDZbDa�?, where any “A�? gets replaced by “a�?, any “B�? gets replaced by “b�? and any “C�? gets replaced by “Z�?. If expression2 is longer than expression1, the extra characters are ignored. If expression1 is longer than expression2, the extra characters in expression1 are deleted from the given string (the corresponding characters are removed.) For example: u_replace_substring("ABC", "ab", "AGDCBDA") returns "aGDbDa".
int32 u_count_substring
(ustring, sub_ustring) Returns the number of times that sub_ustring occurs in ustring. If sub_ustring is an empty string, the number of characters in ustring is returned.
int32 u_dcount_substring
(ustring, delimiter) Returns the number of fields in ustring delimited by delimiter, where delimiter is a string. For example, dcount_substring(“abcFdefFghi�?, “F�?) returns 3. If delimiter is an empty string, the number of characters in the string + 1 is returned. If delimiter is not empty, but does not exist in the given string, 1 is returned.
ustring u_double_quote_string
(expression) Returns the given ustring expression enclosed in double quotes.
ustring u_substring_by_delimiter
(ustring, delimiter, occurrence, numsubstr) The delimiter argument is a ustring value, and the occurrence and numsubstr arguments are int32 values. This function returns numsubstr substrings from ustring, delimited by delimiter and starting at substring number occurence. An example is: u_substring_by_delimiter (“abcFdefFghiFjkl�?, “F�?, 2, 2) The string “defFghi�? is returned. If occurence is < 1, then 1 is assumed. If occurence does not point to an existing field, the empty string is returned. If numsubstr is not specified or is less than 1, it defaults to 1.
int32 u_index_of_substring
(ustring, sub_ustring, occurrence) Returns the starting position of the nth occurrence of sub_ustring in ustring. The occurrence argument is an integer indicating the nth occurrence. If there is no nth occurrence, 0 is returned; if sub_ustring is an empty string, -2 is returned; and if ustring doesn’t contain any sub_ustring, -1 is returned.
ustring u_left_substring
(ustring, length) Returns the first length characters of ustring. If length is 0, it returns the empty string. If length is greater than the length of the ustring, the entire ustring is returned.
ustring u_right_substring
(ustring, length) Returns the last length characters of ustring. If length is 0, it returns the empty string. If length is greater than the length of ustring, the entire ustring is returned.
ustring u_string_of_space(count)
Returns a ustring containing count spaces. The empty string is returned for a count of 0 or less.
ustring u_single_quote_string
(expression) Returns expression enclosed in single quotes.
ustring u_string_of_substring
(ustring, count) Returns a ustring containing count occurrences of ustring. The empty string is returned for a count of 0 or less
ustring u_trimc_string
(ustring [,character [,option]]) If only ustring is specified, all leading and trailing spaces and tabs are removed, and all multiple occurrences of spaces and tabs are reduced to a single space or tab. If ustring and character are specified, option defaults to 'R' The available option values are: 'A' remove all occurrences of character 'B' remove both leading and trailing occurrences of character. 'D' remove leading, trailing, and redundant whitespace characters. 'E' remove trailing white-space characters 'F' remove leading white-space characters 'L' remove all leading occurrences of character 'R' remove all leading, trailing, and redundant occurrences of character 'T' remove all trailing occurrences of character
ustring u_system_time_date() Returns the current system time in this 24-hour format:
hh:mm:ss dd:mmm:yyyy
int32 u_offset_of_substring
(ustring, sub_ustring, position) Searches for the sub_ustring in the ustring beginning at character number position, where position is an uint32. Returns the starting position of the substring.
int8 u_string_case_compare
(ustring, ustring) This is a case-insensitive version of u_string_compare() below. int8 u_string_compare (ustring, ustring) Compares two ustrings and returns the index (0 or 1) of the greater string. int8 u_string_num_case_compare (ustring, ustring, uint16) This is a case-insensitive version of u_string_num_compare() below.
ustring u_string_num_concatenate
(ustring, ustring, uint16) Returns a ustring after appending uint16 characters from the second ustring onto the first ustring.
int8 u_string_num_compare
(utring, ustring, uint16) Compares first uint16 characters of two given ustrings and returns the index (0 or 1) of the greater ustring.
ustring u_string_num_copy
(ustring, uint16) Returns the first uint16 characters from the given ustring. int32 u_string_length(ustring) Returns the length of the ustring.
ustring u_substring
(ustring, starting_position, length) Copies parts of ustrings to shorter strings by string extraction. The starting_position specifies the starting location of the substring; length specifies the substring length. The arguments starting_position and length are uint16 types and must be positive (>= 0).
ustring u_char_from_num(int32) Returns a ustring character value from the given int32. If given a value that is not associated with a character such as -1, the function returns a space. An example use is: u_char_from_num(38) which returns
"&"
int32 u_num_from_char(ustring) Returns the numeric value of the character in the ustring.
When this function is given an empty string, it returns 0; and when it is given a multi-character string, it uses the first character in the string. An example use is: u_num_from_char("&") which returns 38
Bit Manipulation Functions
string bit_expand(uint64) Expands the given uint64 to a string containing the binary representation.
ustring u_bit_expand(uint64) Expands the given uint64 to a ustring containing the
binary representation.
uint64 bit_compress(string) Converts the string binary representation to an uint64
field.
uint64 u_bit_compress(ustring) Converts the ustring binary representation to an uint64 field.
uint64 set_bit
(uint64, list_of_bits, bit_state) Turns the uint64 bits that are listed by number in the string list_of_bits on or off, depending on whether the value of the bit_state integer is 1 or 0. bit_state is an optional argument, and has a default value of 1 which turns the list of bits on. An example use is: set_bit(0, "1,3,5,7") which returns 85.
uint64 u_set_bit
(uint64, list_of_bits, bit_state) This function is a internationalized version of set_bit() above.
Orchestrate performs no automatic type conversion of date fields. Either an input data set must match the operator interface or you must effect a type conversion by means of the transform or modify operator.
21 32 Orchestrate 7.5 Operators Reference date format The default format of the date contained in the string is yyyy-mm-dd. However, you can specify an optional format string that defines another format. The format string requires that you provide enough information for Orchestrate to determine a complete date (either day, month, and year, or year and day of year). The date components of a source string (date, month, and year) must be zero-padded to the character length specified by the format string. Orchestrate zero pads the date components of a destination string to the specified length. The possible format components are:
%dd: A two digit day.
%mm: A two digit month.
%yy: A two digit year derived from a year cutoff of 1900.
%year_cutoffyy: A two digit year derived from yy and the specified year cutoff. (For example, if you specify the year format as %2000yy, two-digit values which represent years from 2000-2099 are imported and exported.)
%yyyy: A four digit year.
%ddd: Day of year in three digit form (range of 1 - 366) The default date format is as follows:
%yyyy-%mm-%dd When you specify a date format string, prefix each component with the percent symbol (%). Separate the string’s components with any character except the percent sign (%).
For example, the format string %mm/%dd/%yyyy specifies that slashes separate the string’s date components; the format %ddd-%yy specifies that the string stores the date as a value from 1 to 366, derives the year from the current year cutoff of 1900, and separates the two components with a dash (-).
Function Description
date date_from_days_since
(int32, “date�? | format_variable) Returns date by adding the given integer to the baseline date. Converts an integer field into a date by adding the integer to the specified base date. The date must be in the format yyyy-mm-dd and must be either double quoted or a variable.
date date_from_julian_day(uint32) Returns the date given a Julian day.
date date_from_string
(string, "date_format" | date_uformat | format_variable) Returns a date from the given string formatted in the optional format specification. By default the string format is yyyy-mm-dd.
date date_from_ustring
(string, "date_format" | date_uformat | format_variable) Returns a date from the given ustring formatted in the optional format specification. By default the ustring format is yyyy-mm-dd.
string string_from_date
(date, "date_format" | date_uformat) Converts the date to a string representation using the given format specification. By default the ustring format is yyyy-mm-dd.
ustring ustring_from_date
(date, "date_format" | date_uformat) Converts the date to a ustring representation using the given format specification. By default the ustring format is yyyy-mm-dd.
date date_from_timestamp(timestamp) Returns the date from the given timestamp.
int32 days_since_from_date
(date, “source_date�? | format_variable) Returns a value corresponding to the number of days from source_date to date. source_date must be in the form yyyy-mm-dd and must be double quoted or be a variable.
uint32 julian_day_from_date(date) Returns a Julian date given the date.
int8 month_day_from_date(date) Returns the day of the month given the date. For
example, the date 07-23-2001 returns 23.
int8 month_from_date(date) Returns the month from the given date. For example,
the date 07-23-2001 returns 7.
date next_weekday_from_date
(date, “day�? | format_variable) The value returned is the date of the specified day of the week soonest after date (including the date). The day argument is optional. It is a string or variable specifying a day of the week. You can specify day by either the first three characters of the day name or the full day name. By default, the value is Sunday.
date previous_weekday_from_date
(date, "day" | format_variable) Returns the previous weekday date from date. The destination contains the closest date for the specified day of the week earlier than the source date (including the source date) The day argument is optional. It is a string or variable specifying a day of the week. You can specify day using either the first three characters of the day name or the full day name. By default, the value is Sunday.
int8 weekday_from_date
(date, "origin_day" | format_variable) Returns the day of the week from date. The optional argument origin_day is a string or variable specifying the day considered to be day zero of the week. You can specify the day using either the first three characters of the day name or the full day name. If omitted, Sunday is day zero.
int16 year_day_from_date(date) Returns the day of the year (1-366) from date.
int16 year_from_date(date) Returns the year from date. For example, the date 07-
23-2001 returns 2001.
int8 year_week_from_date(date) Returns the week of the year from date. For example,
the date 07-23-2001 returns 30.
decimal and float Field Functions
You can do the following transformations using the decimal and float field functions.
Assign a decimal to an integer or float or numeric string, or compare a decimal to an integer or float or numeric string.
Specify an optional fix_zero argument (int8) to cause a decimal field containing all zeros to be treated as a valid zero.
Optionally specify a value for the rounding type (r_type) for many conversions. The values of r_type are:
– ceil: Round the source field toward positive infinity. This mode corresponds to the IEEE 754 Round Up mode. Examples: 1.4 -> 2, -1.6 -> -1 – floor: Round the source field toward negative infinity. This mode corresponds to the IEEE 754 Round Down mode. Examples: 1.6 -> 1, -1.4 -> -2 – round_inf: Round or truncate the source field toward the nearest representable value, breaking ties by rounding positive values toward positive infinity and negative values toward negative infinity. This mode corresponds to the COBOL ROUNDED mode. Examples: 1.4 -> 1, 1.5 -> 2, -1.4 -> -1, -1.5 -> -2
– trunc_zero (default): Discard any fractional digits to the right of the rightmost fractional digit supported in the destination, regardless of sign. For example, if the destination is an integer, all fractional digits are truncated. If the destination is another decimal with a smaller scale, round or truncate to the scale size of the destination decimal. This mode corresponds to the COBOL INTEGER-PART function. Examples: 1.6 -> 1, -1.6 -> -1
decimal decimal_from_decimal
(decimal, "r_type" | format_variable) Returns decimal in decimal representation, changing the precision and scale according to the returned type. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default rtype is trunc_zero.
decimal decimal_from_dfloat
(dfloat, "r_type" | format_variable) Returns dfloat in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
decimal decimal_from_string
(string, "r_type" | format_variable) Returns string in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
decimal decimal_from_ustring
(ustring, "r_type" | format_variable) Returns ustring in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
dfloat dfloat_from_decimal
(decimal, “fix-zero�? | format_variable) Returns decimal in dfloat representation.
int32 int32_from_decimal
(decimal, "r_type fix_zero") Returns int32 in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
int64 int64_from_decimal
(decimal, "r_type fix_zero") Returns int32 in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
uint64 uint64_from_decimal
(decimal, "r_type fix_zero") Returns uint64 in decimal representation. The rounding type, r_type, may be ceil, floor, round_inf, or trunc_zero as described above this table. The default is trunc_zero.
string string_from_decimal
(decimal, "fix_zero suppress zero") Returns string in decimal representation. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
ustring ustring_from_decimal
(decimal, "fix_zero suppress_zero�? | format_variable) Returns ustring in decimal representation. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
string string_from_decimal
(decimal, “fix_zero suppress_zero�? | format_variable) Returns ustring in decimal representation. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
dfloat mantissa_from_dfloat(dfloat) Returns the mantissa (the digits right of the decimal point) from dfloat.
dfloat mantissa_from_decimal
(decimal) Returns the mantissa (the digits right of the decimal point) from decimal.
raw Field Functions
Use the raw field functions to transform a string into a raw data type and to determine the length of a raw value.
raw raw_from_string(string) Returns string in raw representation.
raw u_raw_from_string(ustring) Returns ustring in raw representation.
int32 raw_length(raw) Returns the length of the raw field.
time and timestamp Field Functions
Orchestrate performs no automatic conversions to or from the time and timestamp data types. You must use the modify or transform operator if you want to convert a source or destination field. Most field conversions extract a portion of the time, such as hours or minutes, and write it into a destination field. Time conversion to a numeric field can be used with any Orchestrate numeric data type. Orchestrate performs the necessary modifications to translate a conversion result to the numeric data type of the destination. For example, you can use the transformation function hours_from_time() to convert a time to an int8, or to an int16, int32, dfloat, and so on. The string_from_time() and time_from_string() conversion functions take a format as a parameter of the conversion. The default format of the time in the string is hh:nn:ss. However, you can specify an optional format string defining another time format. The format string must contain a specification for hours, minutes, and seconds.
time Uformat
The time uformat provides support for international components in time fields. It’s syntax is: string%macroString%macroString%macroString where %macro is a time formatting macro such as %hh for a two-digit hour. See “time Format�? below for a description of the date format macros. Only the String components of time uformat can include multi-byte Unicode characters.
timestamp Uformat
This format is a concatenation of the date uformat and time uformat which are described in “date Uformat�? on page 21--33 and “time Uformat�? on page 21--38. The order of the formats does not matter, but the two formats cannot be mixed.
time Format
The time components of a source string (hours, minutes, and seconds) must be zero padded to the character length specified by the format string; Orchestrate zero pads the time components of a destination string to the specified length. The components of the format string are:
%hh: A two-digit hours component.
%nn: A two-digit minutes component.
%ss: A two-digit seconds component.
%ss.n: A two-digit seconds component plus a fractional part, where n is the number of fractional digits with a maximum value of 6. If n is 0, no decimal point is printed as part of the seconds component. Trailing zeros are not suppressed. For example, a format string of %hh:%nn:%ss.4 indicates that the * string contains the seconds to four decimal places.
The default time format is as follows: %hh:%nn:%ss When you specify a time format string, prefix each component with the percent symbol (%). Separate the string’s components with any character except the percent sign (%).
time Field and timestamp Field Functions
int8 hours_from_time(time) Returns the hour portion of the given time.
int32 microseconds_from_time(time) Returns the number of microseconds from the
given time.
dfloat midnight_seconds_from_time
(time) Returns the number of seconds from midnight to
time.
int8 minutes_from_time(time) Returns the number of minutes from time.
dfloat seconds_from_time(time) Returns the number of seconds from time.
dfloat seconds_since_from_timestamp
(timestamp, "source_timestamp_string" | format_variable) Returns the number of seconds from timestamp to the base timestamp, or optionally the second timestamp argument for the number of seconds between timestamps. The source_timestamp_string argument must be double quoted or be a variable.
time time_from_midnight_seconds(dfloat) Returns the time given the number of seconds (dfloat) since midnight.
time time_from_string
(string, time_format | time_uformat | format_variable) Returns a time representation of string using the optional time_format, time_uformat, or format_variable. By default, the time format is hh:nn:ss.
time time_from_ustring
(ustring, time_format | time_uformat | format_variable) Returns a time representation of ustring using the optional time_format, time_uformat, or format_variable specification. By default, the time format is hh:nn:ss.
string string_from_time
(time, "time_format" | format_variable | time_uformat) Returns a string from time. The format argument is optional.The default time format is hh:nn:ss.
time time_from_timestamp(timestamp) Returns the time from timestamp.
date date_from_timestamp(timestamp) Returns the date from the given timestamp.
timestamp timestamp_from_date_time
(date, time) Returns a timestamp from date and time. The date specifies the date portion (yyyy-nn-dd) of the timestamp. The time argument specifies the time to be used when building the timestamp. The time argument must be in the hh:nn:ss format.
timestamp timestamp_from_seconds_since
(dfloat, "original_timestamp_string" | format_variable) Returns the timestamp from the number of seconds (dfloat) from the base timestamp or the original_timestamp_string argument. The original_timestamp_string must be double quoted or be a variable.
timestamp timestamp_from_string
(string, "timestamp_format" | timestamp_uformat | format_variable) Returns a timestamp from string, in the optional timestamp_format, timestamp_uformat, or format_variable. The timestamp_format must be double quoted or be a variable. The default format is yyyy-nn-dd hh:nn:ss.
timestamp timestamp_from_ustring
(ustring, "timestamp_format" | timestamp_uformat | format_variable) Returns a timestamp from ustring, in the optional format specification. The timestamp_format must be a double quoted string, a uformat, or a variable. The default format is yyyy-nn-dd hh:nn:ss.
string string_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a string from timestamp. The formatting specification is optional. The default format is yyyy-mm-dd hh:mm:ss.
ustring ustring_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a ustring from timestamp. The formatting specification is optional. The default format is yyyy-mm-dd hh:mm:ss.
timestamp timestamp_from_time(time, time_format | time_uformat)
Returns a timestamp from time. date date_from_timestamp(timestamp) Returns the date from the given timestamp.
timestamp timestamp_from_timet(int32) Returns a timestamp from the given UNIX time_t representation (int32).
int32 timet_from_timestamp(timestamp) Returns the UNIX time_t representation of timestamp.
Null Handling Functions
All Orchestrate data types support nulls. As part of processing a record, an operator can detect a null and take the appropriate action, for example, it can omit the null field from a calculation or signal an error condition. Orchestrate represents nulls in two ways.
It allocates a single bit to mark a field as null. This type of representation is
called an out-of-band null.
It designates a specific field value to indicate a null, for example a numeric
field’s most negative possible value. This type of representation is called an inband null. In-band null representation can be disadvantageous because you must reserve a field value for nulls and this value cannot be treated as valid data elsewhere.
The null-handling functions can change a null representation from an out-of-band null to an in-band null and from an in-band null to an out-of-band null.
destination_field handle_null
(source_field, value) Change the source_field NULL representations from out-ofband representation to an in-band representation. The value field assigns the value that corresponds to NULL.
destination_field make_null
(source_field, value) Changes source_field NULL representation from in-band NULL representation to out-of-band. The value field allows multiple valid NULL values to be inputted as arguments.
int8 notnull(source_field) Returns 1 if source_field is not NULL, otherwise returns 0.
int8 null(source_field) Returns 1 if source_field is NULL, otherwise returns 0.
set_null() This function is used with “=�? to set the left side output field, when it is nullable, to null. For example:
a-field = set_null();
int8 is_dfloat_inband_null
(dfloat) Returns 1 if dfloat is an inband null; otherwise it returns 0.
int8 is_int16_inband_null
(int16) Returns 1 if int16 is an inband null; otherwise it returns 0.
int8 is_int32_inband_null
(int32) Returns 1 if int32 is an inband null; otherwise it returns 0.
int8 is_int64_inband_null
(int64) Returns 1 if int64 is an inband null; otherwise it returns 0.
int8 is_sfloat_inband_null
(sfloat) Returns 1 if sfloat is an inband null; otherwise it returns 0.
int8 is_string_inband_null
(string) Returns 1 if string is an inband null; otherwise it returns 0.
int8 u_is_string_inband_null
(ustring) Returns 1 if ustring is an inband null; otherwise it returns 0. Note Null-handling functions cannot be used for subrecord fields.
Mathematical Functions
int32 abs(int32) Returns the absolute value of int32.
dfloat acos(dfloat) Returns the principal value of the arc cosine of dfloat.
dfloat asin(dfloat) Returns the principal value of the arc sine of dfloat.
dfloat atan(dfloat) Returns the principal value of the arc tangent of dfloat.
dfloat atan2(dfloat, dfloat) Returns the principal value of the arc tangent of y/x (where y is the first argument).
int ceil(decimal) Returns the smallest integer value greater than or equal to
decimal.
dfloat cos(dfloat) Returns the cosine of the given angle (dfloat) expressed in
radians.
dfloat cosh(dfloat) Returns the hyperbolic cosine of dfloat.
dfloat exp(dfloat) Returns the exponential of dfloat.
dfloat fabs(dfloat) Returns the absolute value of dfloat.
int floor(decimal) Returns the largest integer value less than or equal to decimal.
dfloat ldexp(dfloat, int32) Reconstructs dfloat out of the mantissa and exponent of int32.
uint64 llabs(int64) Returns the absolute value of int64.
dfloat log(dfloat) Returns the natural (base e) logarithm of dfloat.
dfloat log10(dfloat) Returns the logarithm to the base 10 of dfloat.
int32 max(int32, int32) Returns the larger of the two integers.
int32 min(int32, int32) Returns the smaller of the two integers.
dfloat pow(dfloat, dfloat) Returns the result of raising x (the first argument) to the power y (the second argument).
uint32 rand() Returns a pseudo-random integer between 0 and 232 - 1. The function uses a multiplicative congruential random-number generator with period 232. See the UNIX man page for rand for more details.
dfloat sin(dfloat) Returns the sine of dfloat expressed in radians.
dfloat sinh(dfloat) Returns the hyperbolic sine of dfloat.
dfloat sqrt(dfloat) Returns the square root of dfloat.
int32 quotient_from_dfloat
(dfloat1, dfloat2) Returns the value of the quotient after dfloat1 is divided bydfloat2.
srand(uint32) Sets a new seed (uint32) for the frand() or srand() random number generator.
srandom(uint32) Sets a random seed for the random() number generator. See the UNIX man page for srandom for more details.
dfloat tan(dfloat) Returns the tangent of the given angle (dfloat) expressed in
radians.
dfloat tanh(dfloat) Returns the hyperbolic tangent of dfloat.
random() Returns a random integer between 0 and 231 - 1. The function uses a nonlinear additive feedback random-number generator employing a default state array size of 31 long integers to return successive pseudo-random numbers. The period of this random-number generator is approximately 16 x (231 - 1).
Compared with rand, random is slower but more random. See the UNIX man page for random for more details.
string Field Functions
Strings can be assigned (=), compared (==, <, >=, etc.), and concatenated (+) in the Transformation Language. In addition, the functions described in the first list below are available for string manipulations, and the functions described in second list are available for ustring manipulations. When a long string is assigned to a short string, the long string is truncated to the length of the short string. The term white space refers to spaces, tabs, and any other blank space.
string Conversions and Lookup Tables
You can construct a string lookup table to use when default conversions do not yield satisfactory results. A string lookup table is a table of two columns and as many rows as are required to perform a conversion to or from a string as shown below. Each row of the lookup table specifies an association between a 16-bit integer or unsigned 32-bit integer value and a string or ustring. Orchestrate scans the Numeric Value or the String or Ustring column until it encounters the value or string to be translated. The output is the corresponding entry in the row. The numeric value to be converted may be of the int16 or the uint32 data type. Orchestrate converts strings to values of the int16 or uint32 data type using the same table. If the input contains a numeric value or string that is not listed in the table, Orchestrate operates as follows:
If a numeric value is unknown, an empty string is returned by default. However, you can set a default string value to be returned by the string lookup table.
If a string has no corresponding value, 0 is returned by default. However, you can set a default numeric value to be returned by the string lookup table.
A table definition defines the rows of a string or ustring lookup table and has the following form: {propertyList} ('string' | 'ustring' = value; 'string' | 'ustring'= value; ... ) where:
propertyList is one or more of the following options; the entire list is enclosed in braces and properties are separated by commas if there are more than one:
case_sensitive: perform a case-sensitive search for matching strings; the default is case-insensitive.
default_value = defVal: the default numeric value returned for a string that does not match any of the strings in the table.
default_string = defString: the default string returned for numeric values that do not match any numeric value in the table.
A String Lookup Table Numeric Value String or Ustring numVal1 string1 | ustring1 numVal2 string2 | ustring1 ... ... numVal3 stringn | ustringn
string or ustring specifies a comma-separated list of strings or ustrings associated with value; enclose each string or ustring in quotes.
value specifies a comma-separated list of 16-bit integer values associated with string or ustring.
string Field Functions
int8 is_alnum(string) Returns 1 (true) if string consists entirely of alphanumeric
characters.
int8 is_alpha(string) Returns 1 (true) if string consists entirely of alphabetic
characters.
int8 is_numeric(string) Returns 1 (true) if string consists entirely of numeric
characters, including decimal and sign.
int8 is_valid
("type_string", "value_string") Returns 1 (true) if value_string is valid according to type_string, including NULL. The type_string argument is required. It must specify an Orchestrate schema data type. integer types are checked to ensure the value_string is numeric (signed or unsigned), a whole number, and a valid value (for example, 1024 can not be assigned to an int8 type). Decimal types are checked to ensure the value_string is numeric (signed or unsigned) and a valid value. Float types are checked to ensure the value_string is numeric (signed or unsigned) and a valid value (exponent is valid). String is always valid with the NULL exception below. For all types, if the field cannot be set to NULL and the string is NULL, 0 (false) is returned. Date, time, and timestamp types are checked to ensure they are correct, using the optional format argument, and valid values. Raw cannot be checked since the input is a string.
int16 lookup_int16_from_string
(string , "table_definition" | table_variable) Returns an integer corresponding to string using table_definition string or variable.
string lookup_string_from_int16
(int16 , "table_definition" | table_variable) Returns a string corresponding to int16 using table_definition string or variable.
string lookup_string_from_uint32
(uint32 , "table_definition" | table_variable) Returns a string corresponding to uint32 using table_definition string or variable. * uint32 lookup_uint32_from_string (string , "table_definition" | table_variable) Returns an unsigned integer from string using table_definition string or variable.
string lower_case(string) Converts string to lowercase. Non-alphabetic characters
are ignored in the transformation.
string string_from_date
(date, "date_format" | format_variable | date_uformat) Converts date to a string representation using the specified optional formatting specification. By default, the date format is yyyy-mm-dd.
string string_from_decimal
(decimal, “fix_zero suppress_zero�? | format_variable) Returns a string from decimal. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1 The formatting specification is optional.
string string_from_time
(time, "time_format" | format_variable | time_uformat) Returns a string from time. The format argument is optional.The default time format is hh:nn:ss.
string string_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a string from timestamp. The formatting specification is optional. The default format is yyyy-mmdd hh:mm:ss.
string soundex
(input_string, length, censusOption) Returns a string which represents the phonetic code for the string input word. Input words that produce the same code are considered phonetically equivalent. The empty string is returned if the input string is empty. length is an int8 and can be any value between 4 and 10. The default is 4. censusOption can be 0, 1, or 2 where 0 (the default) is enhanced soundex and not a census code; 1 are the normal census codes used in all censuses from 1920 on; and 2 are special census codes used intermittently in 1880, 1900, and 1910.
string upper_case(string) Converts string to uppercase. Non-alphabetic characters
are ignored in the transformation.
string compact_whitespace
(string) Returns a string after reducing all consecutive white space in string to a single space.
string pad_string
(string, pad_string, pad_length) Returns the string with the pad_string appended to the bounded length string for pad_length number of characters. pad_length is an int16. When the given string is a variable-length string, it defaults to a bounded-length of 1024 characters. If the given string is a fixed-length string, this function has no effect.
string strip_whitespace(string)
Returns string after stripping all white space in the string.
string trim_leading_trailing
(string) Returns string after removing all leading and trailing white space.
string trim_leading(string) Returns a string after removing all leading white space.
string trim_trailing(string) Returns a string after removing all trailing white space.
int32 string_order_compare
(string1, string2, justification) Returns a numeric value specifying the result of the comparison. The numeric values are: -1: string1 is less than string2 0: string1 is equal to string2 1: string1 is greater than string2 The string justification argument is either 'L' or 'R'. It defaults to 'L' if not specified. 'L' means a standard character comparison, left to right. 'R' means that any numeric substrings within the strings starting at the same position are compared as numbers. For example an 'R' comparison of “AB100�? and “AB99�? indicates that AB100 is great than AB99, since 100 is greater than 99. The comparisons are case sensitive.
string replace_substring
(expression1, expression2, string) Returns a string value that contains the given string, with any characters in expression1 replaced by their corresponding characters in expression2. For example: replace_substring (“ABC:, “abZ�?, “AGDCBDA�?) returns “aGDZbDa�?, where any “A�? gets replaced by “a�?, any “B�? gets replaced by “b�? and any “C�? gets replaced by “Z�?. If expression2 is longer than expression1, the extra characters are ignored. If expression1 is longer than expression2, the extra characters in expression1 are deleted from the given string (the corresponding characters are removed.) For example: replace_substring("ABC", "ab", "AGDCBDA") returns "aGDbDa".
int32 count_substring
(string, substring) Returns the number of times that substring occurs in
string. If substring is an empty string, the number of
characters in string is returned.
int32 dcount_substring
(string, delimiter) Returns the number of fields in string delimited by delimiter, where delimiter is a string. For example, dcount_substring(“abcFdefFghi�?, “F�?) returns 3. If delimiter is an empty string, the number of characters in the string + 1 is returned. If delimiter is not empty, but does not exist in the given string, 1 is returned.
string double_quote_string
(expression) Returns the given string expression enclosed in double quotes.
string substring_by_delimiter
(string, delimiter, occurrence, numsubstr) The string and delimiter argumets are string values, and the occurrence and numsubstr arguments are int32 values. This function returns numsubstr substrings from string, delimited by delimiter and starting at substring number occurence. An example is: substring_by_delimiter (“abcFdefFghiFjkl�?, “F�?, 2, 2) The string “defFghi�? is returned. If occurence is < 1, then 1 is assumed. If occurence does not point to an existing field, the empty string is returned. If numsubstr is not specified or is less than 1, it defaults to 1.
int32 index_of_substring
(string, substring, occurrence) Returns the starting position of the nth occurrence of substring in string. The occurrence argument is an integer indicating the nth occurrence. If there is no nth occurrence or string doesn’t contain any substring, -1 is returned. If substring is an empty string, -2 is returned.
string left_substring
(string, length) Returns the first length characters of string. If length is 0, it returns the empty string. If length is greater than the length of the string, the entire string is returned.
string right_substring
(string, length) Returns the last length characters of string. If length is 0, it returns the empty string. If length is greater than the length of string, the entire string is returned.
string string_of_space(count) Returns a string containing count spaces. The empty string is returned for a count of 0 or less.
string single_quote_string
(expression) Returns the expression string enclosed in single quotes.
string string_of_substring
(string, count) Returns a string containing count occurrences of string. The empty string is returned for a count of 0 or less.
string trimc_string
(string [,character [,option]]) If only string is specified, all leading and trailing spaces and tabs are removed, and all multiple occurrences of spaces and tabs are reduced to a single space or tab. If string and character are specified, option defaults to 'R' The available option values are: 'A' remove all occurrences of character 'B' remove both leading and trailing occurrences of character. 'D' remove leading, trailing, and redundant whitespace characters. 'E' remove trailing white-space characters 'F' remove leading white-space characters 'L' remove all leading occurrences of character 'R' remove all leading, trailing, and redundant occurrences of character 'T' remove all trailing occurrences of character
string system_time_date() Returns the current system time in this 24-hour format:
hh:mm:ss dd:mmm:yyyy
int32 offset_of_substring
(string, substring, position) Searches for the substring in the string beginning at character number position, where position is an uint32. Returns the starting position of the substring.
int8 string_case_compare
(string, string) This is a case-insensitive version of string_compare() below.
int8 string_compare
(string, string) Compares two strings and returns the index (0 or 1) of the greater string.
int8 string_num_case_compare
(string, string, uint16) This is a case-insensitive version of string_num_compare() below.
string string_num_concatenate
(string, string, uint16) Returns a string after appending uint16 characters from the second string onto the first string.
int8 string_num_compare
(string, string, uint16) Compares first uint16 characters of two given strings and returns the index (0 or 1) of the greater string.
string string_num_copy
(string, uint16) Returns the first uint16 characters from the given string int32 string_length(string) Returns the length of the string.
string substring
(string, starting_position, length) Copies parts of strings to shorter strings by string extraction. The starting_position specifies the starting location of the substring; length specifies the substring length. The arguments starting_position and length are uint16 types and must be positive (>= 0).
string char_from_num(int32) Returns an ASCII character from the given int32. If given a value that is not associated with a character such as -1, the function returns a space.
An example use is: char_from_num(38) which returns "&"
int32 num_from_char(string) Returns the numeric value of the ASCII-character in the string. When this function is given an empty string, it returns 0; and when it is given a multi-character string, it uses the first character in the string.
An example use is: num_from_char("&") which returns 38.
ustring Field Functions
Orchestrate provides the ustring type for multi-byte Unicode-character strings. ustrings can be assigned (=), compared (==, <, >=, etc.), and concatenated (+) in the Transformation Language. The term white space refers to spaces, tabs, and any other blank space.
ustring ustring_from_date
(date, "date_format" | date_format | format_variable) Converts date to a ustring representation using the optional format specification. By default, the format is yyyy-mm-dd.
ustring ustring_from_decimal
(decimal, "fix_zero suppress_zero�? | format_variable) Returns a ustring from decimal. fix_zero causes a decimal field containing all zeros to be treated as a valid zero. suppress_zero argument specifies that the returned ustring value will have no leading or trailing zeros. Examples: 000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1 The format specification is optional. ustring ustring_from_time (time, "time_format" | time_uformat | format_variable) Returns a ustring from time using an optional format specification.The default time format is hh:nn:ss.
ustring ustring_from_timestamp
(timestamp, "timestamp_format" | format_variable) Returns a ustring from timestamp. The format specification is optional. The default format is yyyy-mm-dd hh:mm:ss.
int8 u_is_alnum(ustring) Returns 1 (true) if ustring consists entirely of alphanumeric characters.
int8 u_is_alpha(ustring) Returns 1 (true) if ustring consists entirely of alphabetic characters.
int8 u_is_numeric(ustring) Returns 1 (true) if ustring consists entirely of numeric
characters, including decimal and sign.
int16 lookup_int16_from_ustring
(ustring , "table_definition" | table_variable) Returns an integer corresponding to ustring using table_definition string or variable.
ustring lookup_ustring_from_int16
(int16 , "table_definition" | table_variable) Returns a ustring corresponding to int16 using table_definition string or variable. * ustring lookup_ustring_from_uint32 (uint32 , "table_definition" | table_variable) Returns a ustring corresponding to uint32 using table_definition string or variable. * uint32 lookup_uint32_from_ustring (string , "table_definition" | table_variable) Returns an unsigned integer from ustring using table_definition string or variable. int8 u_is_valid ("type_ustring", "value_ustring") Returns 1 (true) if value_ustring is valid according to type_ustring, including NULL. The type_ustring argument is required. It must specify an Orchestrate schema data type. Integer types are checked to ensure the value_ustring is numeric (signed or unsigned), a whole number, and a valid value (for example, 1024 can not be assigned to an int8 type). Decimal types are checked to ensure the value_ustring is numeric (signed or unsigned) and a valid value. Float types are checked to ensure the value_ustring is numeric (signed or unsigned) and a valid value (exponent is valid). String is always valid with the NULL exception below. For all types, if the field cannot be set to NULL and the string is NULL, 0 (false) is returned. Date, time, and timestamp types are checked to ensure they are correct, using the optional format argument, and valid values. Raw cannot be checked since the input is a string. ustring u_lower_case(ustring) Converts ustring to lowercase. Non-alphabetic characters are ignored in the transformation.
ustring u_upper_case(ustring)
Converts ustring to uppercase. Non-alphabetic characters are ignored in the transformation.
ustring u_compact_whitespace (ustring)
Returns the ustring after reducing all consecutive white space in ustring to a single space.
ustring u_pad_string
(ustring, pad_ustring, pad_length) Returns the ustring with pad_ustring appended to the bounded length string for pad_length number of characters. pad_length is an int16. When the given ustring is a variable-length string, it defaults to a bounded-length of 1024 characters. If the given ustring is a fixed-length string, this function has no effect.
ustring u_strip_whitespace
(ustring) Returns ustring after stripping all white space in the string.
ustring u_trim_leading_trailing
(ustring) Returns ustring after removing all leading and trailing white space.
ustring u_trim_leading(ustring) Returns ustring after removing all leading white space.
ustring u_trim_trailing
(ustring) Returns a ustring after removing all trailing white space.
int32 u_string_order_compare
(ustring1, ustring2, justification) Returns a numeric value specifying the result of the comparison. The numeric values are: -1: ustring1 is less than ustring2 0: ustring1 is equal to ustring2 1: ustring1 is greater than ustring2 The string justification argument is either 'L' or 'R'. It defaults to 'L' if not specified. 'L' means a standard character comparison, left to right. 'R' means that any numeric substrings within the strings starting at the same position are compared as numbers. For example an 'R' comparison of “AB100�? and “AB99�? indicates that AB100 is great than AB99, since 100 is greater than 99. The comparisons are case sensitive.
ustring u_replace_substring
(expression1, expression2, ustring) Returns a ustring value that contains the given ustring, with any characters in expression1 replaced by their corresponding characters in expression2. For example: u_replace_substring (“ABC", “abZ�?, “AGDCBDA�?) returns “aGDZbDa�?, where any “A�? gets replaced by “a�?, any “B�? gets replaced by “b�? and any “C�? gets replaced by “Z�?. If expression2 is longer than expression1, the extra characters are ignored. If expression1 is longer than expression2, the extra characters in expression1 are deleted from the given string (the corresponding characters are removed.) For example: u_replace_substring("ABC", "ab", "AGDCBDA") returns "aGDbDa".
int32 u_count_substring
(ustring, sub_ustring) Returns the number of times that sub_ustring occurs in ustring. If sub_ustring is an empty string, the number of characters in ustring is returned.
int32 u_dcount_substring
(ustring, delimiter) Returns the number of fields in ustring delimited by delimiter, where delimiter is a string. For example, dcount_substring(“abcFdefFghi�?, “F�?) returns 3. If delimiter is an empty string, the number of characters in the string + 1 is returned. If delimiter is not empty, but does not exist in the given string, 1 is returned.
ustring u_double_quote_string
(expression) Returns the given ustring expression enclosed in double quotes.
ustring u_substring_by_delimiter
(ustring, delimiter, occurrence, numsubstr) The delimiter argument is a ustring value, and the occurrence and numsubstr arguments are int32 values. This function returns numsubstr substrings from ustring, delimited by delimiter and starting at substring number occurence. An example is: u_substring_by_delimiter (“abcFdefFghiFjkl�?, “F�?, 2, 2) The string “defFghi�? is returned. If occurence is < 1, then 1 is assumed. If occurence does not point to an existing field, the empty string is returned. If numsubstr is not specified or is less than 1, it defaults to 1.
int32 u_index_of_substring
(ustring, sub_ustring, occurrence) Returns the starting position of the nth occurrence of sub_ustring in ustring. The occurrence argument is an integer indicating the nth occurrence. If there is no nth occurrence, 0 is returned; if sub_ustring is an empty string, -2 is returned; and if ustring doesn’t contain any sub_ustring, -1 is returned.
ustring u_left_substring
(ustring, length) Returns the first length characters of ustring. If length is 0, it returns the empty string. If length is greater than the length of the ustring, the entire ustring is returned.
ustring u_right_substring
(ustring, length) Returns the last length characters of ustring. If length is 0, it returns the empty string. If length is greater than the length of ustring, the entire ustring is returned.
ustring u_string_of_space(count)
Returns a ustring containing count spaces. The empty string is returned for a count of 0 or less.
ustring u_single_quote_string
(expression) Returns expression enclosed in single quotes.
ustring u_string_of_substring
(ustring, count) Returns a ustring containing count occurrences of ustring. The empty string is returned for a count of 0 or less
ustring u_trimc_string
(ustring [,character [,option]]) If only ustring is specified, all leading and trailing spaces and tabs are removed, and all multiple occurrences of spaces and tabs are reduced to a single space or tab. If ustring and character are specified, option defaults to 'R' The available option values are: 'A' remove all occurrences of character 'B' remove both leading and trailing occurrences of character. 'D' remove leading, trailing, and redundant whitespace characters. 'E' remove trailing white-space characters 'F' remove leading white-space characters 'L' remove all leading occurrences of character 'R' remove all leading, trailing, and redundant occurrences of character 'T' remove all trailing occurrences of character
ustring u_system_time_date() Returns the current system time in this 24-hour format:
hh:mm:ss dd:mmm:yyyy
int32 u_offset_of_substring
(ustring, sub_ustring, position) Searches for the sub_ustring in the ustring beginning at character number position, where position is an uint32. Returns the starting position of the substring.
int8 u_string_case_compare
(ustring, ustring) This is a case-insensitive version of u_string_compare() below. int8 u_string_compare (ustring, ustring) Compares two ustrings and returns the index (0 or 1) of the greater string. int8 u_string_num_case_compare (ustring, ustring, uint16) This is a case-insensitive version of u_string_num_compare() below.
ustring u_string_num_concatenate
(ustring, ustring, uint16) Returns a ustring after appending uint16 characters from the second ustring onto the first ustring.
int8 u_string_num_compare
(utring, ustring, uint16) Compares first uint16 characters of two given ustrings and returns the index (0 or 1) of the greater ustring.
ustring u_string_num_copy
(ustring, uint16) Returns the first uint16 characters from the given ustring. int32 u_string_length(ustring) Returns the length of the ustring.
ustring u_substring
(ustring, starting_position, length) Copies parts of ustrings to shorter strings by string extraction. The starting_position specifies the starting location of the substring; length specifies the substring length. The arguments starting_position and length are uint16 types and must be positive (>= 0).
ustring u_char_from_num(int32) Returns a ustring character value from the given int32. If given a value that is not associated with a character such as -1, the function returns a space. An example use is: u_char_from_num(38) which returns
"&"
int32 u_num_from_char(ustring) Returns the numeric value of the character in the ustring.
When this function is given an empty string, it returns 0; and when it is given a multi-character string, it uses the first character in the string. An example use is: u_num_from_char("&") which returns 38
Bit Manipulation Functions
string bit_expand(uint64) Expands the given uint64 to a string containing the binary representation.
ustring u_bit_expand(uint64) Expands the given uint64 to a ustring containing the
binary representation.
uint64 bit_compress(string) Converts the string binary representation to an uint64
field.
uint64 u_bit_compress(ustring) Converts the ustring binary representation to an uint64 field.
uint64 set_bit
(uint64, list_of_bits, bit_state) Turns the uint64 bits that are listed by number in the string list_of_bits on or off, depending on whether the value of the bit_state integer is 1 or 0. bit_state is an optional argument, and has a default value of 1 which turns the list of bits on. An example use is: set_bit(0, "1,3,5,7") which returns 85.
uint64 u_set_bit
(uint64, list_of_bits, bit_state) This function is a internationalized version of set_bit() above.
Saturday, March 13, 2010
Exporting and Importing DSX Files
Using dscmdexport command line utility:
1. Open a dos command line window.
2. Go to the DataStage Client directory or specify this in your PATH environment variable.
3. The syntax for dscmdexport is
dscmdexport /H=hostname /U=username /P=password /O=omitflag project pathname /V
The arguments are as follows:
Hostname - specifies the DataStage Server from which the file will be exported.
Username - is the user name to use for connecting to the DataStage Server.
Password - is the user’s password
omitflag - set this to 1 to omit the username and password
project - Specify the project to export the components from.
Pathname - The file to which to export.
V - Use this flag to switch the verbose option on.
4. Sample output of dscmdexport command:
Using dscmdimport command line utility:
1. Open a dos command line window.
2. Go to the DataStage Client directory or specify this in your PATH environment variable.
The syntax for dscmdimport is
dsimport.exe /H=hostname /U=username /P=password /O=omitflag /NUA project|/ALL|/ASK dsx_pathname1 dsx_pathname2 ...
The arguments are as follows:
Hostname - The DataStage Server to which the file will be imported.
Username - The user name to use for connecting to the DataStage Server (not needed if omitflag = 1)
Password - The user’s password (not needed if omitflag = 1).
Omitflag - Set this to 1 to omit the username and password (only possible if you are connected to the DataStage Server via LAN Manager).
NUA - Include this flag to disable usage analysis. This is recommended if you are importing a large project.
Project, /ALL, or /ASK - Specify a project to import the components to, or specify /ALL to import to all projects or /ASK to be prompted for the project to which to import.
dsx_pathname - The file to import from. You can specify multiple files if required.
3. Sample output of dscmdimport command:
1. Open a dos command line window.
2. Go to the DataStage Client directory or specify this in your PATH environment variable.
3. The syntax for dscmdexport is
dscmdexport /H=hostname /U=username /P=password /O=omitflag project pathname /V
The arguments are as follows:
Hostname - specifies the DataStage Server from which the file will be exported.
Username - is the user name to use for connecting to the DataStage Server.
Password - is the user’s password
omitflag - set this to 1 to omit the username and password
project - Specify the project to export the components from.
Pathname - The file to which to export.
V - Use this flag to switch the verbose option on.
4. Sample output of dscmdexport command:
Using dscmdimport command line utility:
1. Open a dos command line window.
2. Go to the DataStage Client directory or specify this in your PATH environment variable.
The syntax for dscmdimport is
dsimport.exe /H=hostname /U=username /P=password /O=omitflag /NUA project|/ALL|/ASK dsx_pathname1 dsx_pathname2 ...
The arguments are as follows:
Hostname - The DataStage Server to which the file will be imported.
Username - The user name to use for connecting to the DataStage Server (not needed if omitflag = 1)
Password - The user’s password (not needed if omitflag = 1).
Omitflag - Set this to 1 to omit the username and password (only possible if you are connected to the DataStage Server via LAN Manager).
NUA - Include this flag to disable usage analysis. This is recommended if you are importing a large project.
Project, /ALL, or /ASK - Specify a project to import the components to, or specify /ALL to import to all projects or /ASK to be prompted for the project to which to import.
dsx_pathname - The file to import from. You can specify multiple files if required.
3. Sample output of dscmdimport command:
DRS Stage - Dynamic RDBMS Stage
Dynamic RDBMS Stage
The Dynamic RDBMS stage is a database stage which can read or write from the following supported RDBMS: Informix, UDB/DB2, Microsoft SQL/Server, Oracle and Sybase.
The Dynamic RDBMS (DRS) stage is to be used for all database reads and writes.
In the General tab of the properties dialog of the DRS stage the Database type, Connection name, User Id and the DB password is to be specified. Environmental variables can be defined for these values and these are used for parameterising of the database connection parameters.
Note: The above job is related to Peoplesoft EPM using DRS stage.
The Dynamic RDBMS stage is a database stage which can read or write from the following supported RDBMS: Informix, UDB/DB2, Microsoft SQL/Server, Oracle and Sybase.
The Dynamic RDBMS (DRS) stage is to be used for all database reads and writes.
In the General tab of the properties dialog of the DRS stage the Database type, Connection name, User Id and the DB password is to be specified. Environmental variables can be defined for these values and these are used for parameterising of the database connection parameters.
Note: The above job is related to Peoplesoft EPM using DRS stage.
Thursday, March 11, 2010
Wednesday, March 10, 2010
Sequential File Stage
Importing Table Definitions:
---------------------------------------
# Table Definitions describe the format and columns of files and tables
# You can import Table Definitions for :
- Sequential files
- Relational tables
- COBOL files
Sequentila File Import Procedure
---------------------------------------
# Click Import > Table Definitions > Sequential File Definitons
# Select directory containing sequential file and then the file
# Select a repository folder to store the Table Definition in
# Examined for mat a column definitions and edit as necessary.
---------------------------------------
# Table Definitions describe the format and columns of files and tables
# You can import Table Definitions for :
- Sequential files
- Relational tables
- COBOL files
Sequentila File Import Procedure
---------------------------------------
# Click Import > Table Definitions > Sequential File Definitons
# Select directory containing sequential file and then the file
# Select a repository folder to store the Table Definition in
# Examined for mat a column definitions and edit as necessary.
Sunday, March 7, 2010
IBM DataStage MQ Stages
MQ Stages
- MQ Connector stage
- MQ Stage
Read messages from and write messages to a WebSphere MQ enterprise messaging system
A queue manager manages one or more message queues
- The MQ stage or connector establishes a connection to a queue manager in order to read messages from or write messages to a queue.
Connecting to the queue manager
Server mode: The queue manager resides on the same machine as the MQ Connector stage
Client mode: The queue manager resides on a remote machine Specify channel name, transfor type ( TCP ), and remote connection name or IP address.
- Not supported by the MQ stage
- Supported by the MQ Connector stage
Queues
- Store messages
- Must be opened before messages can be written or read.
There are two stages that can be used to read and write MQ messages: MQ Connector stage, which has the same GUI as the ODBC Connector stage, and the MQ stage. The MQ Connector stage is the latest technology.
The MQ Connector stage is not available in the first release of DataStage v8.
MQ Messages
Message types
- Request: Reply is to be sent to the Reply-to-Queue
- Reply: Sent in response to a request message
- Report: No response message is required
Logical messages
- Composed of one or more physical messages on the queue
# Each physical message is called a segment
- Each segment has an offset
- The last segment contains a flag
Message groups
- Composed of one or more logical messages
- Each logical message has a sequence number
- The last message contains a flag
All record types are supported by the MQ stages. In this introductory course we will consider only the simplest type, datagram.
The user can specify the assembly of segments into a logical message.
The user can specify the assembly of messages into a message group.
Message Structure
# Two or three parts
- Header: Information about the content and structure of the data
- Optional format header: Information about the message format
- Payload: Message data
# Message schema
- Defines the type and structure of the data.
Client mode connection is only available in the MQ Connector.
This shows the WebSphere MQ Explore application. A message queue named QUEUE1 is displayed with some messages it contains.
Here the MQ Connector and MQ stages used to read messages from a queue. The stage has a single output link. Messages read are sent to Peek stages.
By default, the message limit is 0, meaning no limit, In that case the job will have no termination condition.
There can be multiple payload columns. This allows the read data to be parsed for later processing.
MQ Connector Stage
The queue manager can be selected from a list of discovered queue managers.
The queue can be selected from a list of discovered queues.
Here, we are filtering messages by payload size. In this messages with between 1 and 5 bytes of data and messages with 8 bytes of data will be read. Other messages will be left in the queue.
Fieleds defined without specified data elements are treated as payload fields.Multiple payload fields can created. The first payload field will extract the first n characters of the payload, where n is the length of the field. The second will extract the next characters in the payload. And so on.
Here the MQ Connector is used to write messages to the queue. So it has a single input link. Messages are read from a Row Generator stage.
Subscribe to:
Posts (Atom)
Posts
