Q92. What is AB_LOCAL expression where do you use it in ab-initio?

Answer:

ablocal_expr is a parameter of input table component of Ab Initio. ABLOCAL() is replaced by the contents of ablocal_expr. Which we can make use in parallel unloads.

There are two forms of AB_LOCAL() construct, one with no arguments and one with single argument as a table name(driving table). The use of AB_LOCAL() construct is in Some complex SQL statements contain grammar that is not recognized by the Ab Initio parser when unloading in parallel.

You can use the ABLOCAL() construct in this case to prevent the Input Table component from parsing the SQL (it will get passed through to the database). It also specifies which table to use for the parallel clause.

Q93. How to Create Surrogate Key using Ab Initio?

Answer:

There are many ways to create surrogate key but it depends on your business logic. Here you can try belows ways.

1. next_in_sequence() function in your transform.

2. Assign key values component

3. Write a stored proc to this and call this stor proc wherever u need.

4. If you writing data into multi file then you have to use below formula to write numbers sequentially:
(next_in_sequence()-1)*no_of_partition()+this_partition()

Scenario Questions:

Q94. How to add header after every fifth record?

Q95. How to read a file having a header after 10 records?

Q96. How to develop CDC logic with only single join component?

Questions and Answer:

Q97. For data parallelism, we can use partition components. For component parallelism, we can use replicate component. Like this which component(s) can we use for pipeline parallelism?

Answer:

You can use components that does not require any sorted data (explicit or in memory sort) to get pipeline parallelism. Like Reformat,FBE, Redefine components.

And Components that needed sorted data like join, roll-up, merge, sort, partition by key and sort breaks the pipeline parallelism.

Q98. What is the difference between $ and ${} substitution.

Answer:

${} substitution is similar to $ substitution except that the parameter must be preceded by curly brackets. If we talk about these in parameter definitions then - 1. If the interpretation is $ substitution then we can give the value as both $ substitution and ${} substitution. e.g. Parameter can be of name $AI_SERIAL or ${AI_SERIAL} 2. If the interpretation is ${} substitution then we can only give the value as ${} substitution parameter. e.g. Parameter can only be of name ${AI_SERIAL}

Tip 21:

If you want to create protected tag. In other words, no one should be able add, edit or delete tag then you can use air tag freeze command.

Even owner of the tag can’t unfreeze the tag so be careful while using this option.

Syntax:
air tag freeze <tagname>

Scenario Questions:

Q99. Output should all the records from first appearance of Pune record.
Input data -
Mumbai
Delhi
Pune
Bangalore
Hyderabad
Pune
Chennai

Output-
Pune
Bangalore
Hyderabad
Pune
Chennai

Tip 22:

To read below date time string:
2019-08-30T12:32:52.006-05:00

We can use date time dml:

datetime("YYYY-MM-DDTHH24:MI:SS.NNN-ZO:NE")

Questions and Answer:

Q100. Without opening a graph how should we know how many input files are using in that graph?

Ans:

Suppose you have graph with name ABC.mp. So to find the number of input datasets(IP files/IP tables..etc) using by the ABC.mp you can do the following:
air project get-required-file /Projectname/mp/ABC.mp > ListofInputfiles.dat
/* This wil save list of input files in file ListofInputfiles.dat */
cat ListofInputfiles.dat | wc -l

/* It will give the count of input datasets required by the graph */

Tip 23:

When to use sort within groups:

Use Sort within Groups only if records within a group are processed before being sorted on a minor key.

i.e. if processing "A" needs records to be sorted on field {"1"} and later in the flow processing "B" needs records to be sorted on field {"1", "2"}.

In this case before processing "2" and after processing "1" use sort within groups with major-key as {"1"} and minor key as {"2"}.

If records are not grouped to start with, use Sort with a multi-part key. There is no benefit to using Sort within Groups directly after using a Sort component.

Scenario Questions:

101. Input file
A
B1
1a
1b
C
B2
2a
2b
Output:
B1
1a
1b
B2
2a
2b
Print 2 next records if found B in input file without using vector.

Q102. Input record contains value as
Atul
Output should be
A
At
Atu
Atul

Q103. Input record contains value as
Kishor
Output should be
K
Ki
Kis
Kish
Kisho
Kishor
Kisho
Kish
Kis
Ki
K

Q104. Input contains more than 1 million records. But you need print last 200 records of it. I don’t want to read full file.

Questions and Answer:

Q105. Explain difference between the “truncate” and "delete" commands?                      

Ans1:
Truncate: It is a DDL command, used to delete table/clusters. Since it is a DDL command hence it is auto commit and Rollback can't be performed. It is faster than delete.

Delete: It is DML command, generally used to delete a record, clusters or tables. Rollback command can be performed , in order to retrieve the earlier deleted things. To make deleted things permanently, "commit" command should be used.

Ans2: Truncate belongs to DDL command where as DELETE belongs to DML command. Rollback can’t be performed incase of Truncate stmt where as Rollback can be performed in Delete stmt. WHERE clause cannot be used in Truncate where as WHERE clause can be used in DELETE stmt.

Ans3:
When u use truncate all the space allotted for the records along with records gets deleted & if u use delete only the records are deleted & the space allocated for the records remains unaltered.

Q106. Explain the differences between api & utility mode?                                      
Ans1:
When u load the data into a table in Utility mode all the constraints are disabled & then data is loaded which leads to faster access.
During API Mode constraints will be enabled so that the access will be slow.

Ans2:
API & UTILITY are two possible interfaces to connect to the db’s to perform certain user specific tasks. These interfaces allow the user to access or use certain functions (provided by the db vendor) to perform operation on db’s. The functionality of each of these interfaces depends on  db’s.
API has more flexibility but often considered as a slower process as compared to UTILITY mode. Well the trade off is their performance and usage.

Ans3:
API mode is more diagnostic purpose for retrieving data from db means like selecting particular fields like retrieving the data relatively according to the constraints. Whenever we select API we will get the option of ablocal expression (ab_exp) every time where ever record is passing it will look for the query we write in that ablocal expression block then it will send. That is why it’s slow. Where as utility is like retrieving the data fields without any constraints or any conditions that is why it is fast.

Ans4:
In API mode data processing(load/update/insert/delete) is slow however other process can access the database tables during the update.

Compared to above Utility mode processing(load/update/insert/delete) goes faster as it handles data records in large chunks however during that time no other process can access  db table i.e. the process running in Utility mode locks the table/owns exclusive ownership of that db instance.

In cross functional & largely distributed organizations API mode is recommended considering the performance aspect over Utility mode.

For one time loads/initialization of huge volume data in tables Utility mode can be used.

Questions:

Q107. Explain what is .air-lock file?

Q108. What is the difference between .sandbox.pset and .project.pset?

Q109. What is need of .project-start.ksh and .project-end.ksh?

Q110. What are the various types of sandboxes?

Questions:

Q111. What is ICFF? What are types of it?

Q112. Can blocked compressed ICFF be used with multiple/different keys?

Q113. Explain lookup template?

Q114. Explain working of lookup load function?

Q115. How we can make N number (assume 150) copies of a file in GDE without using replicate?

Q116. In which scenario, .rec files will not get created even if graph fails? How to use in Abinitio graph?

Q117. If a DML is changed, how to find out the impacted objects using a command?

Q118. How to find corrupted record in multifile?

Q119. How to recover a control file that is removed using rm ?

Q120. How to find the schema either Star or Snowflake Schema in our project?

🎉🎊🎊 *Happy Diwali* 🎊🎊🎉
_May the divine light of Diwali spread into your life Peace, prosperity, happiness & good health😊
🌸🏵️🌸🏵️🌸🏵️🌸🏵️