Problem: 

Solution:

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.

SET TRIM ON
SET HEADING ON
SET PAGESIZE 660
SET NEWPAGE NONE
SET LINESIZE 40
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET UNDERLINE OFF
SPOOL  C:\TEMP\MULTEXPORTLIST.lst
SELECT 1100 || ',' || CHILD_FOLDER.I_FOLDER AS DESIGNER
  FROM OBIA_BIA_ODIREPO.SNP_FOLDER  CHILD_FOLDER
       JOIN OBIA_BIA_ODIREPO.SNP_FOLDER PAR_FOLDER
           ON PAR_FOLDER.I_FOLDER = CHILD_FOLDER.PAR_I_FOLDER
 WHERE PAR_FOLDER.FOLDER_NAME LIKE 'CUSTOM%';
SPOOL OFF;

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;

SET PAGESIZE 1000;
BREAK ON DATABASE;

  SELECT TRIM (SYS_CONTEXT ('userenv', 'instance_name')) AS DATABASE,
         TRIM (OWNER)                                  AS OWNER,
         TRIM (TABLE_NAME)                             AS TABLE_NAME,
         LAST_ANALYZED,
         TO_NUMBER (
             EXTRACTVALUE (
                 XMLTYPE (
                     DBMS_XMLGEN.GETXML (
                            'select count(*) c from '
                         || OWNER
                         || '.'
                         || TABLE_NAME)),
                 '/ROWSET/ROW/C'))
             ROW_COUNTS,
         TO_NUMBER (
             EXTRACTVALUE (
                 XMLTYPE (
                     DBMS_XMLGEN.GETXML (
                            'SELECT COUNT(1) C from ALL_INDEXES WHERE TABLE_OWNER =  '''
                         || OWNER
                         || ''' AND TABLE_NAME= '''
                         || TABLE_NAME
                         || '''')),
                 '/ROWSET/ROW/C'))
             NUM_INDEXES,
         EXTRACT (
             XMLTYPE (
                 DBMS_XMLGEN.GETXML (
                        'SELECT INDEX_NAME ||''; '' C from ALL_INDEXES WHERE TABLE_OWNER =  '''
                     || OWNER
                     || ''' AND TABLE_NAME= '''
                     || TABLE_NAME
                     || '''')),
             '/ROWSET/ROW/C/text()')
             INDEX_NAMES
    FROM ALL_TABLES
   WHERE TABLE_NAME IN ('TAB1',
                        'TAB2',
                        'TAB3',
                        'TAB4')
ORDER BY TABLE_NAME;

SPOOL OFF;

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

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

• 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

1. View > Toad Options

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. 

To extract host names we use a regular expression. You can use Regular expression editor in standard editors like Notepad++ or use http://regexr.com/


Regex:

(HOST\s?=\s?)(.*?)(\))

Extract second group from the matches using:

$2 \n 

Easiest way to use the above regex is in  http://regexr.com/

1. Copy paste the tns file into Text editor
2. Copy paste the expression into expression editor
3. Enable global flag from "flags" on the top right to match entire file
4. In Tools > list type the group extractor

1. We change the existing data server connection details to point to new source - Very simple (Good for one time)
2. Create new data source and update the logical and physical mapping (on GLOBAL context which have to be reverted again)

1. You can react to requests for loading from multiple instances of sources instantaneously without scope for error
2. You have an history of what loads have been performed from what sources. E.g. your testing team might come and hey, when are full load done from this data source. You can preserve the history to answer these questions based on context.

Problem:

Have you ever had a problem when you accidentally changed something in an interface and you forgot what was changed. You wished you had a copy of yesterday's interface so you can restore from it or at the least compare the current interface to previous day's copy.

ODI developers typically use inconsistently internal versioning system to save versions of the code. All these versions are stored the master repository in blob format and litter the repository after a while. This method is also inconsistent, a developer might save an interface but he would have versioned data store used in that interface. Making copies of the objects is even more disastrous, it will make the repository horribly unreadable. Relying on DB backups will mean restore process is all or none. You cannot individually choose which objects to restore. Given these challenges we designed a process which does not involve developers intervention to backup the code nightly.

Solution:

I developed a package to export object by object in the development work repository for the following objects. This code is hosted here. This is scheduled every night. This process backs up the objects to an archive file and retains them for 28 days.

Note: 

Potential uses: 

If you installed ODI agent as a windows service on your host using YAJSW as documented in this blog post on Oracle.

https://blogs.oracle.com/dataintegration/entry/running_odi_11gr1_standalone_a

To Change ODI params you have to follow the following steps. Changing odiparams.bat file will have no effect.

1. Stop the service
2. Make a copy of the %YAJSW%/conf/wrapper.conf.file used to create this service.
3. Edit the %YAJSW%/conf/wrapper.conf for required changes


• If you want to increase the heap size locate the -Xmx  and -Xms parameter
• Change the configuration.
• 
4. Restart the service via Windows services 

Thanks to my very descriptive title. I can skip the obligatory introduction statement. 

Requirement:

Solution:

.\backup_sql_tables_ps.ps1 -ServerInstance INSTANCE_NAME -Database DATABASE_NAME -ExportPath C:\temp\ -TableNames DATABASE_NAME.schema.TABLE1,DATABASE_NAME.schema.TABLE2,DATABASE_NAME.schema.TABLE3