BODI FAQS

1)Is it necessary to put Input Primarykey column in Table Comparison Transform

It is mandatory to select primary key column

2)Parallel Processing

You can run transforms in parallel by entering a number in the Degree of Parallelism box on a data flow’s Properties window. The number is used to replicate transforms in the data flow which run as separate threads when the Job Server processes the data flow.

** Entering a number in the Degree of Parallelism box on a data flow’s Properties window.
** The number is used to replicate transforms in the data flow

3) Soure table Partitioning in Data Integrator , How will u load the data?


4) Performance tuning of a job

The following sections describe ways you can adjust Data Integrator
performance:

• Source-based performance options

• Using array fetch size
• Caching data
• Join ordering
• Minimizing extracted data

• Target-based performance options

• Loading method and rows per commit
• Staging tables to speed up auto-correct loads

• Job design performance options

• Improving throughput
• Maximizing the number of pushed-down operations
• Minimizing data type conversion
• Minimizing locale conversion
• Improving Informix repository performance


5) Parameters in Dataflow

LOCAL VARIABLES, GLOBAL VARIABLES, PARAMETERS.


Dataflow Level---Only Parameters.

Workflow Level---Parameters,Local Variables.

Job Level --- Local Variables, Global Variables.

You create local variables, parameters, and global variables using the Variables and Parameters window in the Designer.

You can set values for local or global variables in script objects. You can also
set global variable values using external job, execution, or schedule properties.


6) SCD Type 2 flow

1 way……….

In the DataFlow where the Intermediate Table and the Ultimate Target table are joined, we will be creating a port named DML_FLAG Which consists of {0/1/2} based on the following.

a) The SCD Columns that are retrieved from the Intermediate table & Ultimate Target table are compared over here.
b) If they are not matching we will assign with 2.
If they are matching we will assign with 1.
If the Surrogate Key of the Ultimate Target table is NULL then we will assign with 0.

So For DML_FLAG having value “2” we will have two dataflows one for insert and one for updating the target table.

Other way……….

SRC----TABLECOMPARISON----HISTORY PRESERVING----KEYGENERATION---TARGET.

Table Comparison-----Primary Key Columns,,,,,Compare columns(SCD Columns)
History Preserving----- Compare columns(SCD Columns)


7) Audit Strategy Implementation in the project



8) Different logs available in the Data Integrator

3 Data Integrator logs :

• Examining trace logs
• Examining statistics logs
• Examining error logs

**Use the trace logs to determine where an execution failed, whether the execution steps occur in the order you expect, and which parts of the execution are the most time consuming.

**The statistics log (also known as the monitor log) quantifies the activities of the components of the job. It lists the time spent in a given component of a job and the number of data rows that streamed through the component.

**Data Integrator produces an error log for every job execution. Use the error logs to determine how an execution failed. If the execution completed without error, the error log is blank.


9) Different Types of Embedded dataflows? Are they reusable.


An embedded data flow is a data flow that is called from inside another data flow.

There are four types of embedded data flows:

One input ---------Add an embedded data flow at the end of a data flow

One input and one output---------- Add an embedded data flow in the middle of a data flow.

One output --------Add an embedded data flow at the beginning of a data flow

No input or output---------- Replicate an existing data flow.

Yes. They are reusable.


10) Passing the parameters of one dataflow to other dataflows.

11) Bulk Loading

12)In how many ways Can we execute a job in BODI ?

3 ways :

a)Manually
b)Thru Scheduling in WEB ADMIN.
c) Export Execution Command which is used to export the job into a batch file which is again executable.


12)What are the different types of Caches in LOOKUP?

NO_CACHE — Does not cache any values.

• PRE_LOAD_CACHE — Preloads the result column and compare column
into memory (it loads the values before executing the lookup).
Use this option if the table can fit in memory.

• DEMAND_LOAD_CACHE — Loads the result column and compare
column into memory as the function executes.
Use this option when looking up highly repetitive values that are a small
subset of the data and when missing values are unlikely.


13)What is the significance of USE INPUT KEYS in Target table Options ?

By setting this Data Integrator will use the primary keys of the source table in order to update the data in the target table.

14)Tell about QUERY TRANSFORM

Retrieves a data set that satisfies conditions that you specify. A query transform is similar to a SQL SELECT statement.
Select-----Distinct Rows
Outer Join -----Outer Source----Inner Source
Where………..You can put any kind of codition like lookup()/any built-in function which returns a single value.
By using Propose Joins DI automatically joins the columns that are having the same name and data type.
Group By ……Drag the columns that r needed to group.
Order By……..Drag the columns you want.

Can Use SQL().
Can use LookUp,LookUpEXT(),LookupSeq() or Built-In functions.
Can use relational operators in the conditions.
Can use Global variables.
Can use Custom Functions.

15)Implementation of INCREMENTAL LOADING

Using the Last_Load_Timestamp of the target table.

If the source table's timestamp is > than the Maximum Last_Load_Timestamp

Using a Table Without Importing It

In SQL Transform :

1)Create Output Columns that are required by seeing the table structure in the backend.

2)Do not import the table in the respective DataStore(The Schema i.e Connected with in which the table is available).

3)Select the appropriate DataStore in which the actual table is residing in the Backend but not in the datastore.

4)Write the appropriate SQL Query as per the columns created.

5)If you want, you can use the global variable which represent the respective schema name.

6) Do not click on the Update Schema.

7)Implement the Transformation Logic i.e required in the job.

8)Execute the job

DOWNLOAD ATL : http://www.divshare.com/download/6200214-d42

Column having muliple values separated with ATTHERATE symbol

(b)DATA RECEIVED VIA FLATFILE (STRUCTURE)
Header :
REGNO,MAKE,YEAR ,DATES LEASED FROM,DATES LEASED TO,CUSTID

Ist Row :
SV53AYC,FORD,2003,28/10/06@22/10/06@12/10/06,30/10/06@27/10/06@20/10/06,HK113@TU141@BK121

IInd Row Data :

SV54KGH,HONDA,2004, 24/08/06@19/8/06, 28/08/2006@22/08/2006, BB141@TM186

IIIrd Row Data :

SV07XKY, TOYOTA, 2007, , ,

Iv Row Data :

SV06VWY,BMW, 2006, 11/03/07, 23/03/07, MK129

Plz Remove Header :, Ist Row Data :, II Row Data :, III Row Data :, Iv Row Data

Solution :





Downloadable Atl Link :
------------------------

http://www.divshare.com/download/6199434-52c

Contains Source CSV file ...and .....Solution ATL file.

Renaming the Datastores of Soure / Target / Intermediate Tables

In order to Rename the Datastores of Source / Target / Intermediate Tables.

1) Take the atl of the job / jobs / Dataflows / Workflows / Datastores

2)Open in Textpad / Notepad.

3) Thru Find & Replace rename the Datastores of the desired one.

4) Save and Import the modified atl into the Repository.

Extract Only the File Name from the relevant/Absolete Path

$G_FILENAME = 'C:\RAMEHS\PRINCE\KDKD.TXT';

$G_VAR1 = word_ext($G_FILENAME,-1,'\\');

PRINT('Filename Is...... '||$G_VAR1);

Strings Embedded with Commas enclosed with " "

Flat File... Comma Delimiter....Strings Embedded with Commas enclosed with " "....

Eg :

Id,Name,Address,Amt
123,"Ramesh Babu","2/16,KAD Road,GZB",50000


Solution :

In the Delimiters Section of Flat File Definition Set the properties like :


Column....... ,
Row..........{New Line}
Text.........." (By default this is None}

Dataflow In a Infinite Loop searching for any Flat file Existance.

Job Is executed 1 time.

Check for Any Flat file(Column structure will be same) exists in "X" Folder.

Name of the flat file changes every time. But the Column Structure will be same.

If any Flat file exists then Dataflow will be executed.

Once the data is loaded into the target table the Source flat file in "X" folder should be deleted.

Dataflow will be in a Infinite Loop searching for any Flat file Existance.


Place a Script & fill the following code:

$G_Stop =1;

Place a While Loop Icon i.e available on the right hand side slider:

put the following condition :

($G_Stop = 1)

Inside the While Loop :

Place a Script & fill the following code :

exec('D:\Flat\b.bat','',2);

$G_a =file_exists('D:\zz.txt');


Code of b.bat :

cd\

d:

cd d:\flat

if not exist *.txt del d:\zz.txt

if exist *.txt dir > d:\zz.txt

Place a Conditional Icon next to the Script :

Put the following condition :

($G_a=1)

Inside the If Condition :

Place the Dataflow which u want to execute.

Next to it place a script and fill the following code.

exec('D:\Flat\Del_Flat.bat','',2);

Code of Del_Flat.bat :

cd\
d:
cd d:\flat
del *.txt

--------------------------------------------

Download Atl :

http://www.divshare.com/download/6200214-d42

Dynamic Target File Names in BODI

Pre-Requisites :

In Data Files Section Set the properties like

Root Directory : D:\
File Location : $Dynamic_Name

Code in the Script File :

$Dynamic_Name='DYNAMIC_'to_char(sysdate(),'YYYY.MM.DD.HH.MI.SS')'.txt';

exec('D:\dy.bat',$Dynamic_Name,8);

Code in Batch File :

d:

echo %1 > d:\z.txt

type k.txt > %1

ATL Downloadable Link :
http://www.divshare.com/download/6199552-ba0

Custom Function For Different Date Formats

Script of Custom Function For Converting Different Date Formats into a single Date Format" YYYYMMDD"

if( is_valid_date($P_Date_In ,'DD/MM/YYYY') = 1 AND SUBSTR($P_Date_In,3,1) <> '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'DD/MM/YYYY'),'YYYYMMDD') ;
print($P_Date_In'......1');
return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'DD-MM-YYYY') = 1 AND SUBSTR($P_Date_In,3,1) = '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'DD-MM-YYYY'),'YYYYMMDD') ;
print($P_Date_In'......2');return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'MM/DD/YYYY') = 1 AND SUBSTR($P_Date_In,3,1) <> '-')
begin$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'MM/DD/YYYY'),'YYYYMMDD') ;
print($P_Date_In'......3');return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'MM/DD/YYYY') = 1 AND SUBSTR($P_Date_In,3,1) <> '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'MM/DD/YYYY'),'YYYYMMDD') ;
print($P_Date_In'......4');return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'MMDDYYYY') = 1 AND SUBSTR($P_Date_In,3,1) <> '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'MMDDYYYY'),'YYYYMMDD') ;
print($P_Date_In'......5');
return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'YYYYMMDD') = 1 AND SUBSTR($P_Date_In,5,1) <> '-')
begin
print($P_Date_In'......6');
return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'MM-DD-YYYY') = 1 AND SUBSTR($P_Date_In,3,1) = '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'MM-DD-YYYY'),'YYYYMMDD') ;
print($P_Date_In'......7');
return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'YYYY-MM-DD') = 1 AND SUBSTR($P_Date_In,5,1) = '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'YYYY-MM-DD'),'YYYYMMDD') ;
print($P_Date_In'......8');return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'YYYY/MM/DD') = 1 AND SUBSTR($P_Date_In,5,1) <> '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'YYYY/MM/DD'),'YYYYMMDD') ;
print($P_Date_In'......9');return($L_Date_Out);
end
else
begin
if( is_valid_date($P_Date_In ,'DDMMYYYY') = 1 AND SUBSTR($P_Date_In,3,1) <> '-')
begin
$L_Date_Out = TO_CHAR(TO_DATE($P_Date_In,'DDMMYYYY'),'YYYYMMDD') ;
print($P_Date_In'......10');
return($L_Date_Out);
end
else
begin
print($P_Date_In'......11');
return($P_Date_In);
end
end
end
end
end
end
end
end
end
end

Speeding up the BODI Repository

Repo Maintenance

I

1) Take Full Backup

2) Take Only Wanted Backup.

3) Then Project ---> Compact Repository

4) Reimport the Data From ATL files



If the repo is Still Slow

II

1) Go to Repo in Toad

2) Take AL_STATISTICS Backup

3) Truncate the table AL_STATISTICS

4) Reimport the data from ATL files


If the repo is Still Slow

III


1)Take the Backup of AL_MACHINE_INFO table.

2)Create a new repo

3)You will be losing AL_MACHINE_INFO, without this

jobs may/may not execute. So insert the data

back from backup table.


Finally


Clean the Bin Dir DMP Files.

ERRORS....IN.....BODI

1)For a pre/Post Load Command you have called a procedure.
Procedure is not declared is the error u r getting.

A)U Got to mention the Schema Name in which the Procedure resides.

2)Datatype Mismatch in Toad Date---Number

The Data in the table might have number instead of Date.

Check for the schemas bcoz the same table might contain in the same schema and also in other schemas.

3) ora-24334:

No Descriptor for this position in SQL Transfrom (Gives the entire query i.e written in SQL Transform?

A) This error comes If some column of the table has been added/removed in the background i.e in the database, but not reflected in the SQL Transform. you got to remove all the columns from the SQL Transfrom and update the schema again in SQL Transform.

Else Click on Update schema is enough.

Even If you have a query like “SELECT b.sno, b.effdate,b.data FROM scott.b b” in the SQL Transform and the columns that are visible are sno, effdate then you won’t be getting any error.

4) In Flat File Lookup the file i.e we are looking for, in that file definition we don't have to specify the root directory. Instead directly specify the path which is enough.
If you specify both while looking the file the path will come 2 times like C:\C:\.....
which is wrong.


5)A Single Job Server can be configured with multiple repositories and Viceversa.

6)Create a CTL file using SQL Loader .

This can be acheived thru dos or Toad.

If you are creating the CTL file thru Toad, at the same time you can load the data into the database also.But Make sure that you already created the table with the necessary structure in the database priorly.

If you want to load the data into the table manually via Dos or thru Data Integrator then

you got 2 use the SQLLDR Command of dos .

SQLLDR USERID=SCOTT/TIGER@ORADB5 CONTROL=D:\A.CTL LOG=D:\A.LOG

If you r using Data Integrator then Use the following command in Data Integrator.

EXEC('D:\BAT','D:\A.CTL',1);

Put the following command in a Batch file.

SQLLDR USERID=SCOTT/TIGER@ORADB5 CONTROL=%1 LOG=D:\A.LOG

%1 is the parameter(D:\A.CTL) i.e coming from the EXEC() command of Data Integrator


7)ORA-01861: literal does not match format string

A) If the datatype in the table is date and you r passing a Varchar2/NUmber datatype then this error will occur.

8)BODI -1111340 ERROR ……….OCI.dll file cannot find.

Go to \Oracle\Ora92\Network\Admin\Tnsnames.ora

Open the file and check for SID=connection string thru which u r logging.

If not available over there add the SID=Connection String, Under the

**Connect Data**

9)I have a custom function that suddenly fails. The function is called in a script transform, passing global variables as parameters. The error msg says "Incorrect syntax near......due to error 70401" and it lists the function call w/ a lot more param values than actually defined in the function.

Sol) Might be using the D.I version 11.7.0.X, So please upgrade to 11.7.3.X.

Job Failed----Error Log should be printed automatically.

Script In BODI :

$G_PRINT = replace_substr(get_error_filename(),' ','*');
$G_PRINT2 = word_ext($G_PRINT, 8, '/');
$g_print3 = substr($g_print,1,index( $g_print,'/error_',1)-1);
exec('c:\a1.bat', $g_print3||' '||$g_print2 , 8);

Batch File Script
c:
cd\
cd %1
cd\

type %2 > c:\echo11.txt
lpr -S 192.168.10.99 -P M3027 -J error_logs c:\echo11.txt

=======================================

Other commands that can be written in Batch file Script
For copying the error file to other folder
------------------------
copy %2 c:\echo11.txt

type %2 > c:\echo11.txt

For Appending the data of 1 file in another
---------------------
type echo11.txt >> abc.txt




******************************************