How to Concatenate Values Of 2 ODI Variables in ODI ?

ODI Interview Questions and Answers:

We can use SQL query to concatenate using || symbol for two variables.


select '#PROJ1.VAR1'||'#PROJ2.VAR2' from dual.

Violation of PRIMARY KEY constraint 'PK_REV_COL'. Cannot insert duplicate key in object 'SNP_REV_COL' ?

ODI Interview Questions and Answers:

Violation of PRIMARY KEY constraint 'PK_REV_COL'. Cannot insert duplicate key in object 'SNP_REV_COL'?


The SNP_REV_COL table which is stored in the current Work Repository is a temporary table that Oracle Data Integrator (ODI) uses during reverse engineering operations.
1. Temporarily disable the SNP_REV_COL primary key constraint.

2. In case customized reverse engineering was used, manually execute the query that ODI has generated for the "Get Columns" reverse engineering step (eg. with Squirrel).

3. Use the following query to identify the columns which are causing the problem:
  
    select I_MOD, TABLE_NAME, COL_NAME, COUNT(*) as REPEATS

    from SNP_REV_COL

    group by I_MOD, TABLE_NAME, COL_NAME

    order by COUNT(*) DESC, TABLE_NAME, COL_NAME

4. Note the TABLE_NAME and COL_NAME information for REPEATS greater than 1.

5. Enable the SNP_REV_COL primary key constraint.

Where we can find Work repository details?

ODI Interview Questions and Answers:

We can find all work repository detains in SNP_REM_REP table in Master repository tables list.


SNP_REM_REP table contains all work repositories related to that master repository.

In this table we can Work repository name and Work repository Type .

If it is 12c we can find addition 2 columns for Global ID and GUID.

Where we can find Customised Reverse engineering Metadata in Repository.

ODI Interview Questions and Answers:

We can find Work Repository SNP_REV_* all tables for Customised reverse engineering metadata.

SNP_REV_TABLE: reverse engineered tables
SNP_REV_COL: Columns of a Datastore
SNP_REV_KEY: Keys or Index to reverse engineer
SNP_REV_KEY_COL: Columns of a Key or Index to reverse
SNP_REV_JOIN: Foreign Keys (Joins/Relations) to reverse
SNP_REV_JOIN_COL: the Foreign Key columns
SNP_REV_COND: Conditions (check constraints)
SNP_REV_FOR_TABLE: Tables to be reverse engineered
Note: this Table is used only in Standard Reverse Engineering, it is not used in Customized Reverse Engineering with a KM
SNP_REV_SUB_MODEL: Sub Model of the Model to reverse engineer

How To Get Current Login User Name in ODI

ODI Interview Questions and Answers:

We can use below query to get current login user name.
All users sessions we can find in SNP_SESSION table and based on GetSession method we can find the current login user session and passing in where clause condition.

select USER_NAME from SNP_SESSION where SESS_NO = <%=odiRef.getSession("SESS_NO"

)%>

How to get Current ODI Session Number

ODI Interview Questions and Answers:

We can use below getsession method in ODI procedure to get session no.



OdiOutFile -FILE=C:\sess.txt
<%=odiRef.getSession("SESS_NO"

)%>

Sybase Database users commands

Users and logins in Sybase 

 sp_iqaddlogin
  

Add users and define their password, number of concurrent connections, and password expiration

sp_iqdroplogin
  

Drop users

sp_iqlistexpiredpasswords
  

List users whose passwords have expired

sp_iqlistlockedusers
  

List users who are locked out of the database

sp_iqlistpasswordexpirations
  

List password expiration information for all users

sp_iqlocklogin
  

Lock a user account so that the user cannot connect to the database

sp_iqmodifyadmin
  

Enable Sybase IQ User Administration, or set database defaults for active user or database connections or password expirations

sp_iqmodifylogin
  

Modify the number of concurrent connections or password expiration for one or all users

sp_iqpassword
    

Modify a user’s password. Users can modify their own password. DBAs can modify any password.

 

Logins:

How to add login in Sybase database?

>sp_addlogin login_name,password,db_name

How to drop login from Sybase database?

>sp_droplogin login_name

Note: Need to drop user before dropping login.

How to find authentication mechanism of login in Sybase database?

>sp_showauthmech login_name
 

Users:

How to find user exists in Sybase database?

>sp_helpuser user_name

How to add user to Sybase database?

>sp_adduser loginame [, user_name [, groupname]]

How to drop user in Sybase database?

>sp_dropuser  user_name

How to add alias to the user in Sybase database?

>sp_addalias user_name,dbo

Note: drop the old user_name from db and add new alias to user 

Imp Note: System Database owner permission is required to run above system procedures.

Added Pivot , Unpivot ,Table functions and sub query objects in new ODI 12.1.2 patch number 17053768

Finally we got PIVOT and other three objects UNPIVOT , TABLE Function & Subquery Filter in Mapping after applying the Opatch 17053768

Copy your opatch file into C:\TEMP  directory (odi_1212_opatch\17053768)

C:\Java\jdk1.7.0_55\bin>set PATH=D:\Oracle\Middleware\Oracle_Home\OPatch;%PATH%

C:\Java\jdk1.7.0_55\bin>cd C:\TEMP\odi_1212_opatch

C:\TEMP\odi_1212_opatch>where opatch
D:\Oracle\Middleware\Oracle_Home\OPatch\opatch
D:\Oracle\Middleware\Oracle_Home\OPatch\opatch.bat

C:\TEMP\odi_1212_opatch>where unzi[
INFO: Could not find files for the given pattern(s).

C:\TEMP\odi_1212_opatch>where unzip
D:\ORACLEXE\app\oracle\product\11.2.0\server\bin\unzip.exe

C:\TEMP\odi_1212_opatch>opatch napply 17053768
Oracle Interim Patch Installer version 13.1.0.0.0
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\Oracle\MIDDLE~1\ORACLE~1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 13.1.0.0.0
OUI version       : 13.1.0.0.0
Log file location : D:\Oracle\MIDDLE~1\ORACLE~1\cfgtoollogs\opatch\opatch2014-06
-06_23-11-38PM_1.log


OPatch detects the Middleware Home as "D:\Oracle\Middleware\Oracle_Home"

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17053768

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local sy
stem.
(Oracle Home = 'D:\Oracle\MIDDLE~1\ORACLE~1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '17053768' to OH 'D:\Oracle\MIDDLE~1\ORACLE~1'

Patching component oracle.odi.sdk, 12.1.2.0.0...

Patching component oracle.odi.studio, 12.1.2.0.0...

Verifying the update...
Patch 17053768 successfully applied.
Log file location: D:\Oracle\MIDDLE~1\ORACLE~1\cfgtoollogs\opatch\opatch2014-06-
06_23-11-38PM_1.log

OPatch succeeded.

C:\TEMP\odi_1212_opatch>

Before Starting ODI12c Studio Client first Delete   odi_1212_opatch  directory from

C:\TEMP\odi_1212_opatch

If you are not deleted above folder then you can't find new Pivot and other objects

Next Start your ODI12c Studio Client then we can new objects in Mapping.

Added Pivot , Unpivot ,Table functions and sub query objects in new ODI 12.1.2 patch number 17053768

Added Pivot , Un pivot ,Table functions and sub query objects in new ODI 12.1.2 patch number 17053768

Difference Between ODI 11g (11.1.1.7) and ODI 12c (12.1.2)

Difference Between ODI 11g (11.1.1.7) and ODI 12c (12.1.2)
ODI 11G(11.1.1.7)	ODI 12C (12.1.2)
No Multiple target tables in a single Interface	We can load multiple targets tables In single mapping
Interface	Mappings & Reusable Mappings
No Reusable mappings in Global Objects	Reusable Mappings Added in Global Objects
Stand Alone & J2ee Agent	Stand Alone, Colocated & J2ee Agent
Only NG-Profiles is  NG_DESIGNER	Added aditional NG profiles NG_CONSOLE, NG_METADATA_ADMIN,NG_VERSION_ADMIN
No Wallet Password	Added Wallet Password
No-Direct role selection while creating users.  Manually Drag n Drop	Direct Role selection whilre creating users
No-  Hive Technology For Hadoop in Topology	Added New Hive Technology For Hadoop  in Topology
No - Pivot functions	Available below functions in Opatch 17053768 1) Pivot Component 2) Unpivot Component  3) Table Function Component 4) Subquery Filter Component
Sequence Enhancements only NEXTVAL	Sequence Enhancements  CURRVAL & NEXTVAL
No  Split or Sort or set objects	Declarative Flow-Based User Interface --------------------------------------------------- The new declarative flow-based user interface combines the simplicity and ease-of-use of the declarative approach with the flexibility and extensibility of configurable flows. Mappings (the successor of the Interface concept in Oracle Data Integrator 11g) connect sources to targets through a flow of components such as Join, Filter, Aggregate, Sort,Set, Split, and so on.
	XML Improvements ------------------------- The following XML Schema constructs are now supported: list and union - List or union-based elements are mapped into VARCHAR columns. substitutionGroup - Elements based on substitution groups create a table each for all types of the substitution group. Mixed content - Elements with mixed content map into a VARCHAR column that contains text and markup content of the element. Annotation - Content of XML schema annotations are stored in the table metadata.