How to Unit Test OBIA Informatica Changes

I've started getting into doing a bit of development with Informatica on our OBIA install (for reference, we are using OBIA 7.9.6.3 + Extension pack). Part of the "best practices" setup by our Oracle Consultant was to create a separate sandbox folder anytime we were making a change to an Infomratica mapping. The difficulty I had was how to test the workflow after I made my changes, as I can't run my change via DAC, without creating a separate execution plan, and I can't test directly in Infomratica, as it references a parameter file that exists on the server.

The solution was to extract the command that DAC would run (pmcmd), create a temporary parameter file, and then run my new mapping from my sandbox folder. And as always, I've created a helpful script to automate this!

Continue on to read for an explanation of what my script does, and a copy of said script.

Note, this is probably fairly specific to our OBIA setup, but should be able to be tweaked to work under your setup.

First, the script, infaUnitTest.sh

Click me to view infaUnitTest.sh

#! /bin/bash

######
# infaUnitTest.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
#
######

TRUNCATE=0
INFA_DB_USER=OBIINFA
INFA_DB_PASS=INFA_PASS
OBI_DB_USER=OBIBAW
OBI_DB_PASS=INFA_PASS
DB_NAME=OBID

# If there are less than 4 parameters, then show help and exit
if [ $# -lt 8 ]; then
   echo -e "\nUsage: infaUnitTest.sh -u infa_user -p infa_pass -s sandbox_folder -w workflow_name [-t]\n\t-u\tInfa user name that owns the sandbox folder.\n\t-p\tPassword for the infa user that owns the sa
ndbox folder.\n\t-s\tName of the Informatica sandbox folder.\n\t-w\tName of the Informatica Workflow.\n\t-t\tTruncate the target table(s) specified in the workflow\n"
   exit -1
fi

set -o pipefail

while getopts "s:w:tu:p:" opt; do
  case $opt in
    s) SANDBOX=${OPTARG} ;;
    w) WORKFLOW_NAME=${OPTARG} ;;
    t) TRUNCATE=1 ;;
    u) USERNAME=${OPTARG} ;;
    p) PASSWORD=${OPTARG} ;;
    \?) echo "Invalid option: -$OPTARG"; exit ;;
    :) echo "Option -$OPTARG requires an argument."; exit ;;
  esac
done

# Create custom Param file

echo -e "Creating temp parameter file."

cd /apps/applinfa/infa_910/server/infa_shared/DacParameters

for file in $(find * -type f -print0 2>/dev/null | xargs -0 grep -l "$WORKFLOW_NAME" | grep -Ev 'sandbox')
do

   echo -e "\nFound parameter file $file."

   NEWFILE=$SANDBOX.$WORKFLOW_NAME.txt
   cp $file $NEWFILE

   echo -e "\nCreating clone of $file as $NEWFILE."

   sed -e 's/ORA_R1213.DATAWAREHOUSE.CUSTOM_SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE
   sed -e 's/ORA_R1213.DATAWAREHOUSE.SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE
   sed -e 's/CUSTOM_SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE
   sed -e 's/SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE

   sed -e 's/DATAWAREHOUSE.DATAWAREHOUSE.CUSTOM_SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE
   sed -e 's/DATAWAREHOUSE.DATAWAREHOUSE.SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE
   sed -e 's/CUSTOM_SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE
   sed -e 's/SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
   mv /tmp/$NEWFILE $NEWFILE

done

cd ~/bmackin

if [ "$TRUNCATE" != "0" ]; then

   TARGET_TABLE=$(${ORACLE_HOME}/bin/sqlplus -s ${INFA_DB_USER}/${INFA_DB_PASS}@${DB_NAME} << END
set feedback off
set head off
set pagesize 0
set heading off
set LINE 200
set DEFINE OFF
set SCAN OFF

select distinct opb_targ.target_name
from opb_subject join opb_task on opb_subject.subj_id = opb_task.subject_id
     join (
               select workflow_id, instance_id, task_id, task_type, instance_name, max (version_number)
               from opb_task_inst sess
               where sess.task_type = 68
               group by workflow_id, instance_id, task_id, task_type, instance_name
          ) tasks on opb_task.task_id = tasks.workflow_id
     join (
               select session_id, mapping_id, max (version_number)
               from opb_session
               group by session_id, mapping_id
          ) sessions on tasks.task_id = sessions.session_id
     join opb_mapping on sessions.mapping_id = opb_mapping.mapping_id
     join opb_widget_inst on opb_mapping.mapping_id = opb_widget_inst.mapping_id
     join opb_targ on opb_widget_inst.widget_id = opb_targ.target_id
where opb_task.is_visible = 1
  and opb_task.task_type = 71
  and opb_mapping.is_visible = 1
  and opb_targ.is_visible = 1
  and opb_widget_inst.version_number = (select max(owi.version_number) from opb_widget_inst owi where owi.mapping_id = opb_mapping.mapping_id)
  and opb_subject.subj_name = '$SANDBOX'
  and opb_task.task_name = '$WORKFLOW_NAME';
exit
END)

   echo -e "\nTarget Table(s) found:\n$TARGET_TABLE"

   for TABLE in $TARGET_TABLE
   do
      echo -e "\nTruncate table $TABLE"

      ${ORACLE_HOME}/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} << END
truncate table $TABLE;
exit
END
   done

   echo -e "\nDetermine if there are any indexs on the table(s):\n$TARGET_TABLE"

   echo -e "set verify off\nset feedback off\nset show off\nset echo off\nset tab off" > tmp_index_ddl.sql

   for TABLE in $TARGET_TABLE
   do
      echo -e "\nExport index DDL for table $TABLE"

      ${ORACLE_HOME}/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} << END >> tmp_index_ddl.sql
set feedback off
set head off
set pagesize 0
set heading off
set LINE 200
set DEFINE OFF
set SCAN OFF

select dbms_lob.substr(DBMS_METADATA.GET_DDL('INDEX',index_name),4000,1) || ';'
from user_indexes
where table_name = UPPER('$TABLE');
END

   done

   echo -e "SHOW ERROR;\nSET FEEDBACK ON\nEXIT\n/" >> tmp_index_ddl.sql

   echo -e "\nDrop all indxes on the table(s):\n$TARGET_TABLE"

   echo -e "set verify off\nset feedback off\nset show off\nset echo off\nset tab off" > tmp_index_drop.sql

   for TABLE in $TARGET_TABLE
   do
      echo -e "\nCreate drop index syntax for table $TABLE"

      ${ORACLE_HOME}/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} << END >> tmp_index_drop.sql
set feedback off
set head off
set pagesize 0
set heading off
set LINE 200
set DEFINE OFF
set SCAN OFF

select 'drop index ' || index_name || ';' from user_indexes where table_name = UPPER('$TABLE');
END

   done

   echo -e "SHOW ERROR;\nSET FEEDBACK ON\nEXIT\n/" >> tmp_index_drop.sql
   echo -e "\nRun the drop index DDL script."
   /apps/oracle/product/11.2.0/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} @tmp_index_drop.sql
   rm tmp_index_drop.sql

fi

echo -e "\nRun the workflow $WORKFLOW_NAME out of folder $SANDBOX."

pmcmd startworkflow -sv OBI_IS_D -d Domain_sappobid02v -u $USERNAME -p $PASSWORD -f $SANDBOX -lpf /apps/applinfa/infa_910/server/infa_shared/DacParameters//$SANDBOX.$WORKFLOW_NAME.txt -wait $WORKFLOW_NAME

if [ "$TRUNCATE" != "0" ]; then

   echo -e "\nRestore any dropped indexes."

   /apps/oracle/product/11.2.0/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} @tmp_index_ddl.sql
   rm tmp_index_ddl.sql

fi

echo -e "\n\n**********\n**********\nAll Done. Please check your results.\n**********\n**********\n"

Now for the explanation.

The script takes the following parameters:

-u     Infa user name that owns the sandbox folder.
-p     Password for the infa user that owns the sandbox folder.
-s     Name of the Informatica sandbox folder where your files are stored.
-w    Name of the Informatica Workflow to run.
-t     Truncate the target table(s) specified in the workflow.

Parameter File Creation

The script will then go into the DacParameters directory, in my case this is:

/apps/applinfa/infa_910/server/infa_shared/DacParameters

It will then do a search file any parameter files that exist with your workflow name mentioned within the file.
If it finds one, it copies it into a new file (prefixed with your sandbox folder name). It then updates the custom parameter file to replace the existing ORAR1213 references to our sandbox folder.

Truncate Tables

If you had supplied the truncate table option, the script will do some additional steps.

First it will determine the target tables by querying the Informatica repository for your workflow. It does this with the following SQL:

select distinct opb_targ.target_name
from opb_subject join opb_task on opb_subject.subj_id = opb_task.subject_id
     join (
               select workflow_id, instance_id, task_id, task_type, instance_name, max (version_number)
               from opb_task_inst sess
               where sess.task_type = 68
               group by workflow_id, instance_id, task_id, task_type, instance_name
          ) tasks on opb_task.task_id = tasks.workflow_id
     join (
               select session_id, mapping_id, max (version_number)
               from opb_session
               group by session_id, mapping_id
          ) sessions on tasks.task_id = sessions.session_id
     join opb_mapping on sessions.mapping_id = opb_mapping.mapping_id
     join opb_widget_inst on opb_mapping.mapping_id = opb_widget_inst.mapping_id
     join opb_targ on opb_widget_inst.widget_id = opb_targ.target_id
where opb_task.is_visible = 1
  and opb_task.task_type = 71
  and opb_mapping.is_visible = 1
  and opb_targ.is_visible = 1
  and opb_widget_inst.version_number = (select max(owi.version_number) from opb_widget_inst owi where owi.mapping_id = opb_mapping.mapping_id)
  and opb_subject.subj_name = '$SANDBOX'
  and opb_task.task_name = '$WORKFLOW_NAME';

The script then uses this target table list to:

• Truncate the list of tables.
• Determine if there are any indexes on the list of tables, and if there are:
• Extract the DDL for said indxes into a script
• Drop the said indexes

Next, the script executes pmcmd to run the workflow. It will then pause and wait until the workflow completes.

Finally, if you had previously truncated the table, it will run the DDL to reapply indexes.

You should be able to monitor the run using Workflow Monitor, and then query the tables when it completes to do data validation.