Informatica 7.1: How to import export using shell script

The following script is useful to export various mappings from DEV environment and then import into QA environment.

1/ Firstly run.sh which will export mappings from DEV and then will import into QA.You have total flexibility while this export-import process.

$ cat run.sh
################################################################
# Script: This script will do import export of mappings using two sub scripts
# Author: M.A.Hasim
# Modification: Create on 28th Dec 2006
# Modification
# History:
# Calling: exportHasim_v1.sh,importHasim_v1.sh
# Called By:
##################################################################
echo "Info...Starting Export.\n"
exportHasim_v1.sh
echo "Info...Completed Export.\n"
sleep 5
echo "Info...Starting Import.\n"
importHasim_v1.sh
echo "Info...Completed Import.\n"
sleep 3
echo "Info...Everything Successful...Good Luck [ Hasim ].\n"

2/ This is an environment file to make things alive.Like as INFORMATICA repository name,folder name.Database parameters,Scrript location,log location etc.

$ cat .my_env
# INFA DEV PARAMETERS
REPODEV=devuser; export REPODEV
REPODEVUSER=Hasim; export REPODEVUSER
REPODEVUSERPASSWORD=Hasim; export REPODEVUSERPASSWORD
REPODEVHOST=utopia; export REPODEVHOST
REPODEVPORT=6410; export REPODEVPORT

# INFA QA PARAMETERS
REPOQA=qauser; export REPOQA
REPOQAUSER=Hasim; export REPOQAUSER
REPOQAUSERPASSWORD=Hasim; export REPOQAUSERPASSWORD
REPOQAHOST=utopia; export REPOQAHOST
REPOQAPORT=6510; export REPOQAPORT

# DATABASE LEVEL PARAMETERS #
$CNCTSTRNGORACLEDEV=devuser/devpasswd@devdatabase; export $CNCTSTRNGORACLEDEV
$CNCTSTRNGORACLEQA=qauser/qapasswd@qadatabase; export $CNCTSTRNGORACLEQA


# LOG ETC
LOG=/mnt/home/abulhasim/IMPEXPMAPPINGS/log; export LOG
SCRPT=/mnt/home/abulhasim/IMPEXPMAPPINGS/script; export SCRPT

3/ This is the main script used for exporting mappings.

$ cat exportHasim_v1.sh
####################################################
# Script: This script will export mappings from INFA Repository .
# Author: M.A.Hasim
# Modification: Create on 28th Dec 2006
# Modification
# History:
# Calling: clctexpobj_v1.sql
# Called By:
####################################################

# Setting all of the environment variables
. .my_env
DT=`date +%m%d%y%H%M%S`
echo "Info....Starting Export."

# Collecting Mappings to be imported and then creating Objectexport statement for pmrep
# and then making a unix script on the fly to be used in command
# the following sql will create a exportHasimmapng_v1.run script which we will use in
# command.So we need change the mode also.
sqlplus -s $CNCTSTRNGORACLE @clctexpobj_v1.sql
CHK1=`wc -l exportHasimmapng_v1.runcut -d" " -f1`
if [ $CHK1 -eq 0 ]; then
echo "Err....Please make exp_stat as 'W' in tmp_infa \n"
exit;
fi
chmod 777 exportHasimmapng_v1.run
mv exportHasimmapng_v1.run /mnt/home/abulhasim/informatica/repositoryserver

# Changing directory to INFA repository
cd /mnt/home/abulhasim/informatica/repositoryserver

# Lets connect to the repository first and then export
pmrep connect -r $REPODEV -n $REPODEVUSER -x $REPODEVUSERPASSWORD -h $REPODEVHOST -o $REPODEVPORT
if [ $? -ne 0 ];then
echo "Err...Connection with the repository $REPODEV failed.\n"
exit;
fi

# Now we will export
# The content of exportHasimmapng_v1.run is like
# objectexport -u 123T456_exportHasim.xml -n Trial -f GDC_PRACTICE -o Mapping -l 123T456_exportHasim.log
pmrep run -f exportHasimmapng_v1.run -o exportHasim$DT.log -e Hasim

if [ $? -ne 0 ];then
echo "Err...Connection with the repository $REPODEV failed.\n"
exit;
fi

# Now we will collect all three files(two logs having the string exportHasim and the other pmrep script
# generated into pmrepository directory for clean up purpose and dump into this local dir

#mv *exportHasim*.log $LOG
cp *exportHasim*.xml $SCRPT
#mv *exportHasim.xml $SCRPT

#rm -f exportHasimmapng_v1.run
cd -
# Update tmp_infa exp_stat uncomment in original run
#sqlplus -s $CNCTSTRNGORACLE @updtMasterTblExpStat.sql

4/ This is the main script for importing mappings.

$ cat importHasim_v2.sh
##########################################################################################################
# Script: This script will import mappings from one Repository to the other
# Author: M.A.Hasim
# Modification: Create on 28th Dec 2006
# Modification
# History:
# Calling:
# Called By:
##########################################################################################################

# Setting environment
. .my_env
# Removing all lst files
rm -f ImpExpData.lst conflictstatfrmtbl.lst
#rm -f *.lst *.xml
echo "Info....Starting Import."

# Collecting SOURCEFOLDERNAME,SOURCEREPOSITORYNAME,TARGETFOLDERNAME,TARGETREPOSITORYNAME
sqlplus -s $CNCTSTRNGORACLE @colctSrcTgtData_v1.sql

# Grep the conflictstatfrmtbl.lst file to get those values into UNIX variable
SOURCEFOLDERNAME=`cat ImpExpData.lstcut -d"" -f1`
SOURCEREPOSITORYNAME=`cat ImpExpData.lstcut -d"" -f2`
TARGETFOLDERNAME=`cat ImpExpData.lstcut -d"" -f3`
TARGETREPOSITORYNAME=`cat ImpExpData.lstcut -d"" -f4`
CHECKINCOMMENTS=`cat ImpExpData.lstcut -d"" -f5`
CHECKINLABEL=`cat ImpExpData.lstcut -d"" -f6`

echo "----- Source and Target Info -----"
echo "Info....SOURCEFOLDERNAME:$SOURCEFOLDERNAME"
echo "Info....SOURCEREPOSITORYNAME:$SOURCEREPOSITORYNAME"
echo "Info....TARGETFOLDERNAME:$TARGETFOLDERNAME"
echo "Info....TARGETREPOSITORYNAME:$TARGETREPOSITORYNAME"
echo "Info....CHECKINCOMMENTS:$CHECKINCOMMENTS"
echo "Info....CHECKINLABEL:$CHECKINLABEL"
echo "-----------------------------------"
echo "Info....Sleeping 1 secs.Press CNTRL-C if you not satisfied with the Info"
sleep 1

# Collecting conflict info from table tmp_infa_conflict and collect those into spool file conflictstatfrmtbl.lst
sqlplus -s $CNCTSTRNGORACLE @createCntrlXml_v1.sql

# Grep the conflictstatfrmtbl.lst file to get those values into UNIX variable
changeno=`cat conflictstatfrmtbl.lstcut -d"" -f1`
cpeno_ttno=`cat conflictstatfrmtbl.lstcut -d"" -f2`
mapng_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f3`
src_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f4`
trg_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f5`
exp_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f6`
fltr_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f7`
agg_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f8`
rank_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f9`
norm_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f10`
rtr_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f11`
seq_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f12`
sort_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f13`
updstrt_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f14`
custtrnsfrmtn_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f15`
lkp_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f16`
trnsctncntrl_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f17`
sp_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f18`
extproc_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f19`
joiner_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f20`

echo "----- Control XML Info -----"
echo "Info....CHANGE NO:$changeno"
echo "Info....CPENO TTNO:$cpeno_ttno"
echo "Info....MAPPING CONFILCT:$mapng_cnflct"
echo "Info....SOURCE CONFLICT:$src_cnflct"
echo "Info....TARGET CONFLICT:$trg_cnflct"
echo "Info....EXPRESSION CONFLICT:$exp_cnflct"
echo "Info....FILTER CONFLICT:$fltr_cnflct"
echo "Info....AGGREGATOR CONFLICT:$agg_cnflct"
echo "Info....RABK CONFLICT:$rank_cnflct"
echo "Info....NORMALIZER CONFLICT:$norm_cnflct"
echo "Info....ROUTER CONFLICT:$rtr_cnflct"
echo "Info....SEQUENCE CONFLICT:$seq_cnflct"
echo "Info....SORTER CONFLICT:$sort_cnflct"
echo "Info....UPDATE STRATEGY CONFLICT:$updstrt_cnflct"
echo "Info....CUSTOM TRANSFORMATION CONFLICT:$custtrnsfrmtn_cnflct"
echo "Info....LOOKUP CONFLICT:$lkp_cnflct"
echo "Info....TRANSACTION CONFLICT:$trnsctncntrl_cnflct"
echo "Info....STORED PROCEDURE CONFLICT:$sp_cnflct"
echo "Info....EXTERNAL PROCEDURE CONFLICT:$extproc_cnflct"
echo "Info....JOINER CONFLICT:$joiner_cnflct"
echo "-----------------------------------"
echo "Info....Sleeping 1 secs.Press CNTRL-C if you not satisfied with the Info"
sleep 1

# The control xml file for inporting objects will be of naming convention like 123T456.xml
CNTRLXML=importHasim$changeno_$cpeno_ttno.xml
# .
# .
# Check the pic above for this portions code.Blogger mess up with angular brackets of XML
# .
# .
# Creting run script for pmrep
IMPRTRUN=importHasim$changeno.$cpeno_ttno.run
echo "ObjectImport -i $cpeno_ttno"_exportHasimCore.xml" -c $CNTRLXML" >$IMPRTRUN
cp $IMPRTRUN /mnt/home/abulhasim/informatica/repositoryserver
cp $CNTRLXML /mnt/home/abulhasim/informatica/repositoryserver
cp $cpeno_ttno"_exportHasimCore.xml" /mnt/home/abulhasim/informatica/repositoryserver >>/dev/null 2>&1
if [ $? -ne 0 ];then
echo "Err....There are no xml files to import."
echo "Err....There should must be matching entry in master tables tmp_infa and tmp_infa_conflict"
exit;
fi
# Changinf directory to PMREp
cd /mnt/home/abulhasim/informatica/repositoryserver

pmrep connect -r $REPOQA -n $REPOQAUSER -x $REPOQAUSERPASSWORD -h $REPOQAHOST -o $REPOQAPORT
pmrep run -f $IMPRTRUN -o importHasim$DT.log -e Hasim
#if [ $? -ne 0 ];then
#echo "Err....Error occurred while importing"
cat importHasim.log
#else
#echo "Update exp status"
#sqlplus -s $CNCTSTRNGORACLEDEV @updtMasterTblImpStat_v1.sql
#fi
mv *importHasim* $LOG
cd -

5/ This a master table which keeps track of the changes you are about to made
The structure of tmp_infa is as follows:-


CREATE TABLE tmp_infa
(
changeno NUMBER(5) NOT NULL,
cpeno_ttno VARCHAR2(300) NOT NULL,
exp_fldr VARCHAR2(300),
exp_rep VARCHAR2(300),
exp_objct VARCHAR2(300),
exp_objcttyp VARCHAR2(300),
exp_objctsubtyp VARCHAR2(300),
exp_stat VARCHAR2(90) DEFAULT 'W',
imp_fldr VARCHAR2(300),
imp_rep VARCHAR2(300),
imp_chkincmnts VARCHAR2(600),
imp_chkinlbl VARCHAR2(600),
imp_stat VARCHAR2(90) DEFAULT 'W'
)

6/ This is another details table which will be used to resolve conflict issues while importing back mappings into QA repository.

Table structure of tmp_infa_conflict is as follows:-

CREATE TABLE tmp_infa_conflict
(
changeno NUMBER(5) NOT NULL,
cpeno_ttno VARCHAR2(300) NOT NULL,
mapng_cnflct VARCHAR2(24),
src_cnflct VARCHAR2(24),
trg_cnflct VARCHAR2(24),
exp_cnflct VARCHAR2(24),
fltr_cnflct VARCHAR2(24),
agg_cnflct VARCHAR2(24),
rank_cnflct VARCHAR2(24),
norm_cnflct VARCHAR2(24),
rtr_cnflct VARCHAR2(24),
seq_cnflct VARCHAR2(24),
sort_cnflct VARCHAR2(24),
updstrt_cnflct VARCHAR2(24),
custtrnsfrmtn_cnflct VARCHAR2(24),
lkp_cnflct VARCHAR2(24),
trnsctncntrl_cnflct VARCHAR2(24),
sp_cnflct VARCHAR2(24),
extproc_cnflct VARCHAR2(24),
joiner_cnflct VARCHAR2(24)
)




7/ This query is used to collect information from a master table named tmp_infa

$ cat clctexpobj_v1.sql
SET NEWPAGE 1
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET VERIFY OFF

spool exportHasimmapng_v1.run
select 'objectexport -n 'exp_objct' -o 'exp_objcttyp' -f 'exp_fldr' -u 'cpeno_ttno'_exportHasimCore.xml'' -l 'cpeno_ttno'_exportHasim.log' from TMP_INFA where exp_stat = 'W' order by changeno;
spool off;
exit;

8/ Update script to update tmp_infa export status as 'S' after successfully exported.

$ cat updtMasterTblExpStat.sql
update tmp_infa set exp_stat = 'S' where exp_stat = 'W';
commit;
exit;

9/ colctSrcTgtData_v1.sql is to collect informations to which QA informatica folder we are to import of which informatica repository.

$cat colctSrcTgtData_v1.sql
SET NEWPAGE 1
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET VERIFY OFF

spool ImpExpData.lst
select exp_fldr''exp_rep''imp_fldr''imp_rep''imp_chkincmnts''imp_chkinlbl from tmp_infa where exp_stat = 'W';
spool off;
exit;

10/ createCntrlXml_v1.sql is to collect all conflict related issues while importing.
You will have all informations related to confilct in tmp_infa_conflict table.

$ cat createCntrlXml_v1.sql
SET NEWPAGE 1
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET VERIFY OFF

spool conflictstatfrmtbl.lst

select
cnflct.changeno ''
cnflct.cpeno_ttno ''
mapng_cnflct ''
src_cnflct ''
trg_cnflct ''
exp_cnflct ''
fltr_cnflct ''
agg_cnflct ''
rank_cnflct ''
norm_cnflct ''
rtr_cnflct ''
seq_cnflct ''
sort_cnflct ''
updstrt_cnflct ''
custtrnsfrmtn_cnflct ''
lkp_cnflct ''
trnsctncntrl_cnflct''
sp_cnflct ''
extproc_cnflct ''
joiner_cnflct
from tmp_infa_conflict cnflct,tmp_infa main
WHERE
cnflct.CHANGENO=main.CHANGENO
AND cnflct.CPENO_TTNO=main.CPENO_TTNO
AND main.imp_stat = 'W';

spool off;
exit;

11/ updtMasterTblImpStat_v1.sql is to update the status of tmp_infa table import status to S after successfully imported the mappings.

$ cat updtMasterTblImpStat_v1.sql
update tmp_infa set imp_stat = 'S' where imp_stat = 'W';
commit;
exit;


---: My blog is not responsible for any damages happened from the suggestion of my blog :---
Reach me at : m_a_hasim@yahoo.com