Data Quality Guide for Oracle Customer Hub > Configuring Data Quality with Oracle Data Quality Servers > Sample SQL Scripts >

IDS_IDT_LOAD_ANY_ENTITY.sh

The following sample SQL script can be used for incremental data load.

NOTE:  Use this file for UNIX.

#!/bin/bash

#################################################################################

# Prerequisite check block #

#################################################################################

# Checking IIR system variables are set. If not then throw error and exit.

if [ -z "$SSABIN" ] && [ -z "$SSATOP" ]

then

echo "Err #LOAD-01: Informatica IIR system variables not set. Please use 'idsset' script"

exit

else

# checking if required idsbatch utility exists at $SSABIN location

if [ -f $SSABIN/idsbatch ]

then

echo "idsbatch utility found."

fi

fi

#################################################################################

# Param block #

#################################################################################

# INPUT PARAMETERS

   current=$1

   workdir=$2

   dbcredentials=$3

# ENVIRONMENT RELATED PARAMETERS

# scriptdir=/export/home/qa1/InformaticaIR/initloadscripts

# informaticadir=/export/home/qa1/InformaticaIR

   scriptdir=$SSATOP/initloadscripts

   =$SSATOP

# DEBUG OPTION - 1 for ON and 0 for OFF

   debug=1

# Passing DB credentials as argument

# ISS DATABASE CREDENTIALS and CONNECT INFO

# dbcredentials=ora32155/ora32155@sdchs20n532_qa532a

   dbcredentials=$3

# MACHINE NAME

   machineName=`hostname`

#################################################################################

# Execution block #

#################################################################################

if [ $debug -eq 1 ]; then

   echo using Script dir: $scriptdir

   using Informatica Home: $informaticadir

fi

if [ $# -lt 3 ]

then

   echo "Err #LOAD-03: Error in $0 - Invalid Argument Count"

   echo Usage LoadAnyEntity "<Entity Account,Contact or Prospect> <WorkDir> <dbuser/dbpass@tnsname> [Optional Batch Number]"

   echo Insufficient parameters

   echo e.g "Syntax: $0 Entity_name Account Log_directory /temp"

   exit

fi

if [ -f $scriptdir/idt_$current\_load.txt ]

   then

   if [ $debug -eq 1 ]; then

      echo Using Load file $scriptdir/idt_$current\_load.txt

   fi

   else

   Load file cannot be loaded. Please check and rerun process

fi

if [ $# -eq 4 ]

   then

   Specific bath to be loaded: $4

fi

if [ -d $workdir ]; then

   cd $workdir

   rm -r -f *.err

fi

if [ $# -eq 3 ]

   then

   read_sql_stmt() {

   typeset stmt=$1

   typeset login=$2

   echo "

      set feedback off verify off heading off pagesize 0

      $stmt;

      exit

      " | sqlplus -s $login

   }

   read_sql_stmt "select max(batch_num) from "$current"s_SNAPSHOT_VIEW" "$dbcredentials" | while read u

   do

   batches=$u

   counter=2

   if [ $debug -eq 1 ]; then

      echo current=$current

      echo workdir=$workdir

      echo counter=$counter

      echo number of batches to be processed is: $batches

   fi

# for counter in $(seq 2 $batches);

   for ((counter=2; counter <= $batches; counter++));

currentbatch=$(

sqlplus -S $dbcredentials <<!

set head off feedback off echo off pages 0

UPDATE IDS_IDT_CURRENT_BATCH set batch_num=$counter

/

select batch_num from IDS_IDT_CURRENT_BATCH

/

!

   echo

   echo

   echo "#########################################"

   echo "# Curently Processing Batch: $currentbatch #"

   echo "#########################################"

   cd $informaticadir/bin

   if [ $debug -eq 1 ]; then

   echo InformaticaDrive: ${PWD}

      echo Processing following command:

      echo idsbatch -h$machineName:1669 -i$scriptdir/idt_$current\_load.txt -1$workdir/idt_$current\_load$counter.log -2$workdir/idt_$current\_load$counter.err -3$workdir\idt_$current\_load$counter.dbg

      echo "#########################################"

   fi

   idsbatch -h$machineName:1669 -i$scriptdir/idt_$current\_load.txt -1$workdir/idt_$current\_load$counter.log -2$workdir/idt_$current\_load$counter.err -3$workdir\idt_$current\_load$counter.dbg

   done

   done

else

   counter=$4

   echo "#########################################"

   echo Processing Batch $4....

   currentbatch=$(

         sqlplus -S $dbcredentials <<!

         set head off feedback off echo off pages 0

         UPDATE IDS_IDT_CURRENT_BATCH set batch_num=$counter

         /

         select batch_num from IDS_IDT_CURRENT_BATCH

         /

         !

         )

   echo "#########################################"

   cd $informaticadrive/bin

   idsbatch -h$machineName:1669 -i$scriptdir/idt_$current\_load.txt -1$workdir/idt_$current\_load$counter.log -2$workdir/idt_$current\_load$counter.err -3$workdir\idt_$current\_load$counter.dbg

fi

   echo "Process completed. Please examine error and log files in "$workdir

   # errorcnt=0

if [ -d $workdir ]; then

   cd $workdir

fi

   errorcnt=$(find ./ -depth 1 -name "*.err" ! -size 0 | wc -l)

echo Errors encountered is: $errorcnt

if [ $errorcnt -eq 0 ]; then

   echo Successfully processed all the batches

   else

   echo #########################################

   echo # Failed batch report #

   echo #########################################

   echo $errorcnt batch/es have failed. Please check the following batches:

   find ./ -depth 1 -name "*.err"

fi