Archive for 2017

Steps to create new index in OBIA.

In the previous blog post we explored how to create new index in OBIA and how to set proper flex fields on the constraints object to enable proper index maintenance. find that blog post here
After setting parameters on the flex fields we have to load the table W_ETL_ALL_INDEXES. This can be done using the following scenario.

This steps can be skipped if you are running a domain load plan.

BI Apps Project > Components > DW >  Oracle > Load Index Metadata > Load Index Metadata > EXEC_LOAD_INDEX_METADATA Version 001

Monday, September 11, 2017
Posted by Arjun Lagisetty

How to purge an Year's worth of ODI logs?


What happens when you realize that you a need to clear years worth of data from the ODI work repository?

Whats wrong with just purging using purge option using Operator?
    You are most likely to run out of undo table space with error. 

ORA-30036: unable to extend segment by 8 in undo tablespace '<Undo Tablespace name>'


    Delete logs in small batches one day of logs at a time. Using OdiTools and a procedure.

1. Create a procedure with the following "Command on Target" code

  2. Add the following "Command on Source" code

Tuesday, June 13, 2017
Posted by Arjun Lagisetty

MultiExport list file ".lst" file create from ODI Rep SQL

Use the following code for creating a .lst file from ODI repository database. This file can be used for backup or migration.

This query gives the list of all project folders whose parent folder name start with 'CUSTOM'. 1100 is the object type code for folder objects in SNP_FOLDER table.

You can modify the SQL query as per your needs, query spooling options will deliver a clean file which can be directly used in the multi-export interface.

Thursday, April 20, 2017
Posted by Arjun Lagisetty

SQL to get last analyzed date and row counts of multiple tables.

When we have performance problem we often have to retrieve certain data for multiple tables for example row counts, last analyzed date from multiple databases. In an effort to simply that I create the following query which spools following details to the file c:\temp\tabledetailslist.txt. 

If you want to capture the same details for multiple databases run this SQL on each database, you should see all the results in one file

  1. Row count of each table 
  2. Last analyzed date of each table
  3. Number of Indexes on each table
  4. List of all indexes names on the table
SPOOL C:\temp\countlist.txt APPEND;


  SELECT TRIM (SYS_CONTEXT ('userenv', 'instance_name')) AS DATABASE,
         TRIM (OWNER)                                  AS OWNER,
         TRIM (TABLE_NAME)                             AS TABLE_NAME,
         TO_NUMBER (
             EXTRACTVALUE (
                 XMLTYPE (
                     DBMS_XMLGEN.GETXML (
                            'select count(*) c from '
                         || OWNER
                         || '.'
                         || TABLE_NAME)),
         TO_NUMBER (
             EXTRACTVALUE (
                 XMLTYPE (
                     DBMS_XMLGEN.GETXML (
                            'SELECT COUNT(1) C from ALL_INDEXES WHERE TABLE_OWNER =  '''
                         || OWNER
                         || ''' AND TABLE_NAME= '''
                         || TABLE_NAME
                         || '''')),
         EXTRACT (
             XMLTYPE (
                 DBMS_XMLGEN.GETXML (
                        'SELECT INDEX_NAME ||''; '' C from ALL_INDEXES WHERE TABLE_OWNER =  '''
                     || OWNER
                     || ''' AND TABLE_NAME= '''
                     || TABLE_NAME
                     || '''')),

Wednesday, April 12, 2017
Posted by Arjun Lagisetty

How to login to remote "Cloud" based ODI repository over ssh port forwarding?


When a database is hosted on a public cloud, security policies often prevent the admins from exposing direct db port i.e. 1521 to the world. In this scenario, admin asks oracle developers to use ssh tunneling a.k.a port forwarding to connect to the database from our ODI Studio. So, how we do this?

High-Level Steps:

  1. You need to have ssh access to the database server in addition to DB login credentials. (One time)
  2. Download and install putty (One time)
  3. Create putty profile with port forward called mysshprofile  (One time)
  4. Connect to putty and launch a session with mysshprofile. (Before every login)
  5. Create a new connection pointing to local port in ODI studio (One time)
  6. Launch ODI Studio and connect to the remote database.

The Nitty Gritty:

How to download and install putty?

How does port forwarding work?

How connect to the remote database using port forward?


Remote database host IP:
SSH Port: 22
Unused local port on localhost: 9000
Remote database port: 1521
Remote database sid: orclsid

Connecting to remote server, creating a tunnel and creating a putty profile:

  1. Open Putty and enter IP address  and SSH port

  1. Navigate to Tunnels menu on the right-hand side.

  1. Add Source Port, this is a local port which listens for traffic and forwards it to remote DB port. This port should not be used by any system, I used 9000 port. Add Destination in  Remote IP: DB Port format (See assumptions)

  1. Click on Add button

  1. Go back to session tab on left menu and add profile name "tunnelremotedb" in the Saved Sessions field

  1. Click save and open.
  2. You will now be prompted to enter username and password: Please enter ssh login credentials which you got from DBA
  3. Completing these steps will create a successful tunnel into the remote server. Please note, if you close the ssh session you will close the tunnel.
  4. Subsequently, you can connect to the database using putty saved session. You will not need to repeat steps 1-5

Connecting to ODI repository from ODI Studio:

Creating a connection to cloud repository and a regular database have very similar steps but the major different would be the jdbc URL.
Where 9000 is the Source port entered in Step 4 of the putty connection section.

Putting it all together:

Once the setup is done you should
  1. Create SSH connection
    1. Open putty
    2. Load the saved session
    3. Log in to the remote server.
  2. Connect to ODI repo using MyRemoteRepConnection

In the next installment, we will talk about how to automate putty connection using plink
Monday, April 10, 2017
Posted by Arjun Lagisetty

How add and maintain custom indexes in OBIA using ODI?

How add and maintain indexes in OBIA using ODI?
Monday, April 10, 2017
2:05 PM
If you look at the ODI code base for OBIA (all 11.X versions) before every SDE mapping step or SIL mapping steps. We will see two sets of steps.
  1. Initialize steps (Before the map is run)
    1. Indexes are dropped
  2. Finalize steps (After the map is run)
    1. Indexes are created
    2. Stats are gathered

When we add a new index to a table in DW schema or when we delete an index, we do not have to add CREATE INDEX, DROP INDEX or GATHER STATS steps to the ODI code base. There are hooks in the code to take care of these steps irrespective of the number of indexes. Index maintenance steps can be achieved by a couple of simple steps in Designer without having to write a single line of code.

Let's start by understanding what are the steps involved in adding an index to a table.
Step 1: Create indexes on the database
Step 2: Reverse engineer the model (typically named Oracle BI Applications)
Customized Reverse-Engineering using OBIA specific RKM
Step 3: Modify options on the index.
Step 4: Execute EXEC_LOAD_INDEX_METADATA scenario under BI Apps Project > Components > DW >  Oracle > Load Index Metadata > Load Index Metadata > EXEC_LOAD_INDEX_METADATA Version 001 (to load W_ETL_ALL_INDEXES table). If you are running domain load this step can be skipped.

There are three main changes to be made to the index (Constraint object).
  1. Choose "Key or Index Type" under "Description" Tab
    1. Not Unique Index: If the index is not unique
    2. Primary Key: If the Index is Primary key
    3. Alternate key: If the index is unique but not the primary key.
  2. Choose "OBI Index Type" under "Flexfields" tab
  3. Choose "OBI Bitmap Index" under "Flexfields" tab

Most indexes can be categorized into following types. Let's evaluate what are the options to choose for each type of index as mentioned in Step 3

  • Primary key index (BTREE)
    • "Key or Index Type": Primary Key
    • "OBI Index Type": ETL
    • "OBI Bitmap Index": N

  • Alternate key index (BTREE)
    • "Key or Index Type": Alternate Key
    • "OBI Index Type": ETL
    • "OBI Bitmap Index": N

  • Non-Unique Index (BTREE )
    • "Key or Index Type": Not Unique Index
    • "OBI Index Type": ETL
    • "OBI Bitmap Index": N

  • Non-Unique Index (BITMAP) 
    • "Key or Index Type": Not Unique Index
    • "OBI Index Type": QUERY
    • "OBI Bitmap Index": Y
Posted by Arjun Lagisetty

How to get rid of the (Annoying) "Croak" sound at TOAD startup?

1. View > Toad Options

2. Click on "Startup"

3. Uncheck "Toad Wav File"

4. Click "Ok" [I know this is Obvious]
Monday, January 9, 2017
Posted by Arjun Lagisetty

Use Option values within Substitution API calls (Hint: Substitution Passes) [Nested odiRef Calls]

Here is a simple question: How to use Option values with in a substitution API?

In our design we created a procedure which look table name as option value (TABLE_NAME) and it created a DML statement around it. For this procedure to work we needed to use odiRef.getOption() function with in odiref.getTableName() function.

My first take on it was to write the following code

OK, here is my first take at the answer. HINT: This does not work.

<%= odiRef.getTableName("<%=odiRef.getOption("TABLE_NAME")%>") %>

Why does it not work?

As some of you are aware, Odi's procedure language is based on Java Bean Shell. It is interpreted i.e. the code is executed top down and left to right. So, the first function it encounters is getTableName function and it executes that first. At this point getOption function is not called yet. So the instead of receiving the option value, getTableName function receives entire string "odiRef.getOption("TABLE_NAME")" as input.

Typically in most languages code is executed once. Since ODI is based on java beanshell code, the interpreter takes multiple passes through the code. To understand multiple passes in detail, please refer to the ODI cookbook chapter 5.

To cut through the chase here is the final answer for the above problem.
<%= odiRef.getTableName("<?=odiRef.getOption("TABLE_NAME")?>") %>

Also, refer to this blog post by Uli Bethke at Sonra for another example on substitution passes. 
Sunday, January 1, 2017
Posted by Arjun Lagisetty

Popular Post


Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty