Export Model Repository Mappings using infacmd oie command

Export Command Without Control File: 

​./infacmd.sh oie ExportObjects -dn infav10_domain -un Admin -pd "@@@@@@" -sdn Native -pn Integration -rs mrs_irpo -fp /tmp/Mappings.xml -ow true

Export Command With Control File: 

./infacmd.sh oie ExportObjects -dn infav10_domain -un Admin -pd "@@@@@@" -sdn Native -pn Integration -rs mrs_irpo -fp /tmp/Mappings.xml -ow true -cp /tmp/ec_mapping.xml

Control File Samples

Control File :    All_Mappings In the Project Without considering Sub Folders

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<exportParams

xmlns="http://www.informatica.com/oie/exportControl/9">

<folders>

<folder recursive="false" >

<objectList type="Mapping" />

</folder>

</folders>

</exportParams>

Control File :    All_Mappings In the Project With consider Sub Folders contents

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<exportParams

xmlns="http://www.informatica.com/oie/exportControl/9">

<folders>

<folder recursive="true" >

<objectList type="Mapping" />

</folder>

</folders>

</exportParams>

Control File :  Export Particular Mapping 

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<exportParams

xmlns="http://www.informatica.com/oie/exportControl/9">

<folders>

<folder recursive="false" >

<objectList type="Mapping">

<object name="m_PassThrough_Mapping"/>

</objectList>

</folder>

</folders>

</exportParams>​

​Control File : Export Particular Folder Mappings in a Project ​

​<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<exportParams

xmlns="http://www.informatica.com/oie/exportControl/9">

<folders>

<folder path="/SourceAdapter" recursive="false" >

<objectList type="Mapping" />

</folder>

</folders>

</exportParams>​

​ 

Continue Reading

Operation not allowed for reason code “7” on table in DB2

This issue generally occurs when we altered/modified already existing table in IBM DB2.

We have to execute below Query when we get this error.

CALL sysproc.admin_cmd('REORG TABLE TABLE_NAME');

Below are the situations when we can ReOrg the table in DB2.

• A high volume of insert, update, and delete activity on tables accessed by queries. If many rows are inserted, there may not be enough free space to keep them in the clustered sequence. If many rows are deleted, the table will still have the space allocated and a REORG can free the unnecessary space.

• Significant changes in the performance of queries that use an index with a high cluster ratio. Some applications access groups of rows using the clustered index and may not perform well if the table becomes unclustered.

• Executing RUNSTATS to refresh statistical information does not improve performance.

•    In some cases, a RUNSTATS Utility can collect the current statistics and resolve performance problems. If a table has become unclustered or if it contains a large amount of free space, the REORG Utility may be needed to improve access efficiency.

• The REORGCHK command indicates a need to reorganize your table.

• If Row compression is being implemented for a table, the REORG Utility can be used to build the compression dictionary and compress the data rows.

Note:  alter table "APPS"."DEPARTMENTS" alter column DEPARTMENT_ID set not null; 

 

Continue Reading

Creation of Informatica connections Using Infacmd

When a connection is created from command line, multiple options need to provide in -o option:

infacmd.sh createconnection
[ICMD_10023] Missing required command option: [<-DomainName|-dn>]
Usage:
<-DomainName|-dn> domain_name
<-UserName|-un> user_name
<-Password|-pd> password
[<-SecurityDomain|-sdn> security_domain]
[<-ResilienceTimeout|-re> timeout_period_in_seconds]
<-ConnectionName|-cn> connection_name
<-ConnectionId|-cid> connection_id
<-ConnectionType|-ct> connection_type
[<-ConnectionUserName|-cun> connection_user_name]
[<-ConnectionPassword|-cpd> connection_password]
<-Options|-o> options, separated by space in the form of name=value. Use single quote to escape any equal sign or space in the value.
Defines a connection and the connection options.

​./infacmd.sh createconnection -dn Domain_orcl -un Administrator -pd Lucid2016 -cn SQLServer -cid SQLServer -ct ODBC -cun ssasuser -cpd Lucid@123 -o "DataAccessConnectString=DW_SQLServer CodePage=UTF-8 EnableConnectionPool=true EnableQuotes=true QuoteChar=3 ODBCProvider='Microsoft SQL Server'"​

Continue Reading

NLS_LANG Variable in Linux

Setting the NLS_LANG environment variable on the client to match that of the Oracle Database (found in v$nls_parameters system view) is NOT NECESSARILY the correct configuration.  NLS_LANG is used to tell the Oracle Client what character set the client's (or Informatica Server's) OS is using, so that appropriate code conversion can take place (or be skipped, when code pages match between Oracle client and server).  Please see Oracle Technical Note 158577.1 - "NLS_LANG Explained" for a more elaborate explanation as well as troubleshooting tips.  Please contact Oracle Support for configuration suggestions, as needed.

• The NLS_LANG variable must to be in uppercase on UNIX.

• If there is any mistake in the value setting of this variable (such as a spelling mistake), you may encounter the ORA-12705 error.
  For a list of valid languages, territories and character sets, the relevant National Language Support Guide for your version should be consulted.

• Completely unsetting the NLS_LANG will use the default value AMERICAN_AMERICA.US7ASCII.
  This may fix the ORA-12705 error. However it may give unexpected results, as you are only dealing correctly with characters that can be represented completely with 7-bits. When you use a special characters (usually stored using the 8th bit), this will result in conversion problems.

• On Windows, if there are several Oracle_HOMEs on the machine, ensure you have set the correct ORACLE_HOME. 
  Once you have determined the correct ORACLE_HOME , open the registry and verify the NLS_LANG entry in the special path.

• On Windows, if you set NLS_LANG as an environment variable, this will take precedence over the value set in the Registry. 
  In most cases this is not preferable, so you may unset this variable.

• On UNIX, depending on the shell, you will have to export the environment variable to make it visible to the sub-processes.

 

Oracle SQL Developer is the only "known-good" client side tool which can be used to view characters correctly and works independently of client-side NLS_LANG settings. 

Continue Reading

Exporting a table that contains a CLOB column

​Problem Statement:​ 

What is the recommended way for exporting a table that contains a CLOB column to a SQL script? 

The standard feature of the SQL Developer only exports all columns that are not CLOBs. 

Solution:

Let us assume table named "Data" contains a clob type column. In order to get the insert statements for the content of the table ,

trigger the below query

SQL> select /*insert*/* from Data;

Note: Above sql should be executed in script mode .

Once the query executed , you can see the insert queries in the Script Output Area.

Continue Reading