Dynamic XFR & DML creation

DMLs and XFRs are consumed and configured when the graph first starts.
If you change any of them during graph execution, the graph will not go
back and get your changes - they will only be visible the next time you
run the graph.

http://datawarehouse.ittoolbox.com/groups/technical-functional/abinitio-l/dynamic-xfr-dml-creation-1595616

Ab Initio : Playing with Vectors

The requirement is prepare the list of Products separated by '~'  for each Customer ID. The sample data looks like below,

Input Data

Cust ID	Product
101	AA
101	AB
101	AC
102	AA
102	AC

Output :

Cust ID	Product
101	AA~AB~AC
102	AA~AC

Solution :
The ROOLUP is used with input SORTED data on Cust ID.

Delimited String Vector is defined and value is assigned to the same by using below functions.

  let  string("\x02", maximum_length=7) [unsigned long] vector_final;
  vector_final = vector_sort_dedup_first(accumulation(in.product) , {machine descending}  );
  out.product:: string_join(vector_final, '~' );

accumulation :   Returns a vector of input values. Available only within the ROLLUP and SCAN components.

vector_sort_dedup_first : Returns a new vector consisting of a sorted version of the input vector, which includes only the first element in each group of elements whose key matches the specified key.

string_join: Concatenates vector string elements into a single string.

Predefined sort sequence modifiers

DML supports the following predefined sort sequence modifiers:

Modifier	Description
phonebook	Treats digits as the lowest-value characters, followed by the letters of the alphabet in the order AaBbCcDd..., followed by spaces. Ignores all other characters, such as punctuation. The order of digits is 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
index	Orders the same as phonebook, except that punctuation characters have lower values than all other characters and are not ignored. The order of punctuation characters is the machine sequence.
machine	Orders character code values in the sequence in which they are arranged in the character set of the string: ASCII-based character sets — Digits are the lowest-value characters, followed by uppercase letters, followed by lowercase letters. EBCDIC character sets — Lowercase letters are the lowest-value characters, followed by uppercase, followed by digits. Unicode character sets — The order is from the lowest character code value to the highest.

Ab Initio: Null keys in lookup file

NULL-valued arguments in functions

Most built-in functions return NULL when any of their arguments is NULL. Exceptions are:

Function category	How the functions handle NULL values
Lookup functions	When passed a NULL argument to use as a key or subkey, these functions try to match the NULL value to the corresponding key or subkey of a record in the lookup file.
Certain inquiry and error functions	These functions never return NULL, because they are specifically designed to return indicators of NULL or invalid values.

In Short : If there are NULL values in the fields of a lookup file and NULL values in the parameters passed to a lookup function, the values will match and the function will return a record.

Ab Initio : Specifying the length of a string in DML

Specifying the length of a string in DML

When specifying the length of a string in DML — where the length of the string is defined as its number of characters excluding any length prefix or delimiter — you must consider whether its character set uses fixed-size storage or variable-size storage.

Fixed-size character sets are those that use fixed-size storage for characters. Examples of these include ISO-8859-1 and EBCDIC, both of which use 1 byte per character, and UTF-16, which uses 2 bytes per character.

To declare strings using fixed-size character sets, specify the number of characters for the string’s length as follows:

For fixed-length strings, specify the number of characters as fixed_length.

For delimited strings, specify the number of characters as max_len.

For length-prefixed strings, specify the number of characters as type_specifier.

Variable-size character sets are those in which character storage size varies, depending on the character. Example of these include UTF-8, EUC-JP-Unicode-0-9, and Windows-932 (commonly called Shift-JIS).

To declare strings using variable-size character sets, use the number of bytes for the string’s length as follows:

For fixed-length strings, specify the number of bytes as fixed_length.

For delimited strings, specify the number of bytes as max_len.

For length-prefixed strings, specify the number of bytes as type_specifier. Ref : Help FIle

Ab Initio : IN and NOT IN

You can create a vector with the possible values and then use operator
"member" to get that the same functionality in Abinitio. For example, lets
say u have 5 values to compare with, output field name is check_value then
in reformat, u can try as
let decimal("|")[5] temp_vec = [vector 10, 12, 13, 23, 33];
out.check_value :: if(in.field_name member temp_vec) "Yes" else "No".
OR if there are 30 or 40 values ..i mean more data to compare, create a
lookup file and perform the operation.
Ref : http://datawarehouse.ittoolbox.com/groups/technical-functional/abinitio-l/in-and-not-in-2890661

Loading MFS file to Oracle Table

Steps for bulk load in Oracle:

1) Use parallel direct path load using SQL loader with native option =
SKIP_INDEX_MAINTENACE=TRUE (use MFS layout in output table component)
2) You can define a generic db stored procedure to rebuild the indexes on
target table. Otherwise define a graph parameter and construct the index
rebuild sql statement (with online parallel option) by using
m_db command and PDL expression.
3) Use RUN SQL to call the db procedure/SQL parameter(constructed in graph
parameter) in order to rebuild the indexes.
4) analyze the table.

Ref : http://datawarehouse.ittoolbox.com/groups/technical-functional/abinitio-l/loading-mfs-file-to-oracle-table-4968984

SQL *Loader : Direct Load : Description

With the conventional path load method, arrays of rows are inserted with standard sql INSERT statements, integrity constraints and insert triggers are automatically applied. But when you load data with the direct path, SQL*Loader disables some integrity constraints and all database triggers. The constraints that remain in force are:
* NOT NULL
* UNIQUE
* PRIMARY KEY (unique-constraints on not-null columns)
and the following constraints are automatically disabled by default:
* CHECK constraints
* Referential constraints (FOREIGN KEY)

Ab Initio Software Versions

To determine which version of the GDE and Co>Operating System you are using, do one of the following:

•  Choose Help > About Graphical Development Environment from the GDE menu bar

To determine the version of Co>Operating System host connection

• Type m_env -version (or m_env -v) in the dialog’s text box
• m_env -features