OIM Tables Descriptions

Note: Custom Tables are created for user defined Object / Process Forms.

TABLE NAME IN OIM	DESCRIPTION OF TABLE
AAD	List To Define The Administrators For Each Organization And Their Delegated Admin Privileges
AAP	Table for storing Resource - Organization level parameter Values
ACP	ACP - Link Table That Holds Reference To ACT And PKG Tables, Table That Defines The Objects (Resources) Allowed For A Particular Organization
ACS	Link Table for Account Table(ACT) and Server Table(SVR)
ACT	Defines information about all organizations created through Xellerate
ADJ	Contains the Java API information for the constructor with parameters and method name with parameters chosen for an adapter task of type JAVA, UTILITY, TAME,REMOTE, or XLAPI.
ADL	Contains the all of the necessary parameters for an adapter task of type IF, ELSE IF,FOR, WHILE, SET, and VARIABLE tasks. These type of tasks are known as LOGICTASKS
ADM	Data mapping between parameters input/output parameters and source/sink
ADP	Defines an adapter created through the Adapter Factory
ADS	Database,schema and procedure name selections which define a stored procedure adaptertask
ADT	Defines a task attached to an adapter
ADU	Contains the web service and method chosen for a task of the Adapter Factory
ADV	Adapter variable table contains variables that have been created for specific adapters.
AFM	Links an adapter with a form
AGS	Holds the definition of organization/contact groups
AOA	Contains the OpenAdapter property file for OpenAdapter
APA	To store attestation process administrators
APD	To store attestation Process definition
APT	To store the attestation tasks
ARS	Contains custom response codes for 'Process Task' Adapters only
ATD	To store entitlement details for each attestation task
ATP	Defines input and output parameters for the constructor and method of an adapter taskof type JAVA, UTILITY, TAME, REMOTE, and XLAPI
ATR	To store attestation requests
ATS	Stores which services or can be ordered by which organizations and which rates apply
AUD	Define the Auditors
AUD_JMS
CRT	Trusted Certificate Information
DAV	Stores the runtime data mappings for 'Entity' & 'Rule Generator' adapters. The data source being an Xellerate form or child table,or a user defined process form.
DEP	Dependencies among Tasks Within A Workflow Process
DOB	Data Resource definition consisting of the fully qualified class name of the dataobject
DVT	Defines the one to many relationship between Data Resources and Event Handlers (this includes adapters)
EIF	Export Import Files. Each row contains one single file used in export/import operation. For export there is only one file
EIH	Export Import History. Each row represents one Data Deployment Management session.
EIL	DB Based lock for export operation. Used to make sure only one user can import at atime. This is currently not managed through data objects
EIO	Export Import Objects. Each row represents one object exported/imported
EIS	Substitutions used during import process
EMD	Core --Email Definition Information Table That Holds The Email Template Definitions
ERR	Error codes
ESD	Encrypted columns not within the bounds of the SDK
EVT	Defines event handlers by providing a process and class name. In addition the scheduling time of when the event handler can execute is set to pre (insert, update, delete) or post (insert, update, delete)
FUG	List to define the administrators for each user defined object in the 'StructureUtility' form or for each user defined field in the 'User Defined FieldDefinition' form
GPG	List to define the (nested) group members of User Group in the 'User Group' form.
GPP	List to define the Administrators and their delegated admin rights over a User Group
GPY	Joins Properties (PTY) and Groups (UGP).
IEI	Table where all the imports and exports are defined
LAY	Table where the layouts are defined for the various imports and exports
LIT	Import/export table.
LKU	Lookup definition entries
LKV	Lookup values
LOB	Import/export table.
LOC	Holds information about locations
MAP	XML MapSchema Information
MAV	Stores the runtime data mappings for 'Process Task' adapters. The data source being a process form, Location, User, Organization, Process, IT Resource, orLiteral data.
MEV	E-mail notification events
MIL	Holds information about tasks of a process
MSG	Defines the user groups that have permission to set the status of a process task.
MST	Task Status And Object Status Information. Holds All The Task Status To Object Status Mappings
OBA	Object Authorizer Information
OBD	Object Dependencies
OBI	Object Instance Information
OBJ	Resource Object definition information.
ODF	Holds Object To Process Form Data Flow Mappings.
ODV	Object Events/Adapters Information
OIO	Object Instance Request Target Organization Information.
OIU	Object Instance Request Target User Information.
OOD	Object Instance Request Target Organization Dependency Information.
ORC	This Entity Holds The Detail On Each Order. This Could Be Considered The Items Section Of An Invoice. This Entity Is The Instance Of A Particular Process
ORD	Holds information that is necessary to complete an order regardless of a processbeing ordered
ORF	Resource Reconciliation Fields
ORR	Object Reconciliation Action Rules
OSH	Task Instance Assignment History
OSI	Holds information about tasks that are created for an order
OST	Object Status Information
OUD	Object Instance Request Target User Dependency Information. Holds The Dependency Between Different Resource Instances Provisioned To A User.
OUG	List to define the administrators for each Resource
PCQ	Holds the challenging questions and answers for a user
PDF	Package data flow table holds the data flow relationships between packages
PHO	Holds all communication addresses for this contact -- e.g., contact telephone numbers,fax numbers, e-mail, etc.
PKD	Package dependency table holds the dependency relationships between child packages of a parent package
PKG	Consists of names and system keys of service processes, which consist of a group ofservices from the TOS table. Defines a Process in Xellerate.
PKH	Package Hierarchy Table Holds The Parent-child Relationships Between Processes
POC	Stores values for the child tables of the Object/Process form of a resource being provisioned by an access policy
POF	Policy field table holds the field value pairs that constitute the definition of apolicy
POG	Join table between Policy and User Groups, Specifies the groups to whom an access policy will apply.
POL	Policy Table Holds A Policy, Defines An Access Policy In The System
POP	Policy Package Join Table Holds The Packages That A Particular Policy Orders For User, Defines Which Resources Will Be Provisioned Or Denied For A Particular Access Policy.
PRF	Process Reconciliation Field Mappings
PRO	Defines a process name, scheduling frequency, and priority. A process is made up of oneor more tasks
PTY	Client Properties Table
PUG	List to define The Administrators And Their Delegated Admin Rights For Each Process.
PWR	Table forPassword Rule Policies
PXD	Table that holds the list of all Proxies Defined
QUE	Administrative queues definition
QUM	Administrative queue members
RAV	Stores the runtime data mappings for 'Pre-populate' adapters. The data source being an Xellerate form or child table, or a user defined form
RCA	Reconciliation Event Organizations Matched
RCB	Reconciliation Event Invalid Data
RCD	Reconciliation Event Data
RCE	Reconciliation Events
RCH	Reconciliation Event Action History
RCM	Reconciliation Event Multi-Valued Attribute Data
RCP	Reconciliation Event Processes Matched
RCU	Reconciliation Event Users Matched
REP	Table that contains all information about reports in the system
REQ	This table holds request information
RES	This table is used to stored adapter resources entered by the user.
RGM	Table for Response Code Generated Milestones
RGP	Rules To Apply To A User Group, Defines The Auto-group Membership Rules Attached To AParticular Group.
RGS	Defines all known registries. These are used by Web Service tasks in an Adapter to communicate with a web service
RIO	Request Organizations Resolved Object Instances
RIU	Request Users Resolved Object Instances
RLO	This table contains directory URLs which are referenced by Adapter Factoryjar/class files.
RML	Rules To Apply To Task, Defines The Task Assignment Rules Attached To A Process Task.
ROP	Rules To Apply To An Object-process Pair, Defines The Process Determination Rules Attached To A Resource Object.
RPC	Reconciliation Event Process Child Table Matches
RPG	Link table between Group table and Report Table. Specifies which group has accessto which reports
RPP	Parameters passed to report.
RPT	Stores information related to the creation of reports
RPW	Rules To Apply To A Password Policy, Defines The Policy Determination Rules Attached To A Password Policy.
RQA	Request target organization information.
RQC	Request comment information
RQD	Contains self-registration request data for web admin.
RQE	Request administrative queues
RQH	Requeststatus history
RQO	Request object information.
RQU	Request object target user information
RQY	Request Organizations Requiring Resolution
RQZ	Request Users Requiring Resolution
RRE	Reconciliation User Matching Rule Elements
RRL	Reconciliation User Matching Rules
RRT	Reconciliation User Matching Rule Element Properties
RSC	Defines the All The Possible Response Code For A Process Task.
RUE	Defines the Elements In A Rule Definition.
RUG	List to define the administrators for each Request
RUL	Rule definitions
RVM	Holds Recovery Milestones
SCH	Holds specific information about an instance of a ask such as its status orscheduled dates
SDC	Column metadata.
SDH	Meta-Table Hierarchy.
SDK	User define data object meta data definition
SDL	SDK version labels
SDP	User defined column properties
SEL	Data Object Permissions For Groups On A Specified Data object
SIT	The SIT table contains information about sites. Sites are subsets of locations.
SPD	IT Resource parameter definition
SRE	Defines Which Pre-populate Rule Generator Will Run For A Field Of User Defined DataObject.
SRP	Should be replaced by the rate table from a billing system. Here it holdspecific rates for specific services.
SRS	IT Resource - IT Resource join
STA	Status Codes
SUG
SVD	IT Resource type definition
SVP	IT Resource property definition
SVR	IT Resource instance definition
SVS	IT Resource - Site Join
TAP	Holds parameter values for a task, which is an instantiation of Valid Task,i.e. value for parameter Company Name, etc.
TAS	Holds instances of Valid Task. Examples of Valid Tasks would be reports, imports, etc. Valid TaskParameters indicate what parameters can be assassigned to an instance of a task, i.e
TDV	Used by event manager/data objects, joins data objects, types of service, and events
TLG	Keeps logof SQL transactions.
TMP	Indicates which tasks are in a process. Tasks are defined in table; this way, one task can be in many processes.
TOD	To do list settings table.
TOS	Holds information about a process
TSA	Stores initialization params (name/value pairs) forscheduler tasks
TSK	Scheduler task definition information
UDP	User-defined field table
UGP	Defines a group of users
UHD	User Policy Profile History Details table
ULN	This table hold UHD allow / deny list
UNM	"UnDoMilestone" Feature
UPA
UPA_FIELDS	Stores changes only for user profile audit history in de-normalized format
UPA_GRP_MEMBERSHIP	Stores groups membership history in de-normalized format
UPA_RESOURCE	Stores user profile resource history in de-normalized format
UPA_USR	Stores user profile history in de-normalized format
UPD	User Policy Profile Details table
UPH	User Policy Profile History table
UPL	User-defined field table
UPP	User Policy Profile table
UPT	User-defined field table
UPY	Joins Properties (PTY) and User (USR) tables.
USG	This table stores which users are in which groups.
USR	Stores all information regarding a user.
UWP	Window sequence, nesting in CarrierBase explorer for each user group.
VTK	Defines automation task types such as reports, imports, and exports.
VTP	Valid Task Parameters. Indicates which parameters can be defined for an instance of a task.
WIN	Windows table: Windows keys, descriptions, and class names.
XSD	This table holds Xellerate System Data

OIM SQL SERVER OUT OF THE BOX CONNECTOR AND IMPLEMENTATION

Connecting to SQL Server with JDBC

All you need is 3 jar files in your classpath

• Msbase.jar
• Msutil.jar
• Mssqlserver.jar


When you install the Microsoft drivers, by default, they go to the following folder:
c:\program files\Microsoft SQL Server 2000 Driver for JDBC

So, going by this, you can set your CLASSPATH the following way:

CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver

Code to connect SQL Server

import java.*;
public class Connect

{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "user";
private final String password = "password";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";

// Constructor
public Connect(){}

private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}

private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}

/*
Display the driver properties, database details
*/

public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}

private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}

Output

If this code is successful, the output is similar to the following:

Connection Successful!
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022

Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
May 23 2001 00:02:52
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )

Avalilable Catalogs
catalog: master
catalog: msdb
catalog: pubs
catalog: tempdb

This is an excerpt from microsoft article. Here is the full article:

http://support.microsoft.com/kb/313100

Backup / Restore OIM Oracle DB

I am showing you the statements to backup and import an OIM Database. Most of the statements are on a linux box. Windows should be same or nearly same. I'll also provide with a few useful OIM / Oracle statements (besides Backup/Restore) that will be handy all the time.

OIM Backup

I always recommended that whenever you reach a logical step in implementing OIM, you should take a backup. By logical step, I mean when your first clean install is completed take one backup. Once your connectors are loaded, take another backup. So on and so forth. Coming straight to the point of backup, here are the statements:

Assuming you have a linux machine, connect with the user that has the privileges to do an export of oracle. Usually user is oracle, but if you have a different one - go with that user.

[oracle@idm ~]$mkdir exports
[oracle@idm ~]$cd exports
[oracle@idm exports]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Here is the syntax we are going to use to export the database:
exp <schemaOwner>/<schemaOwnerPassword>@<DatabaseSID> file=<filename>.dmp log=<logname>.log full=y

In this example assume:
Schema owner is xladm
Password is xladmpwd
databaseSID is IDM
filename is base_clean_install.dmp
logname is base_clean_install_log.log

So, issue the command like this:

[oracle@aelidm1 exports]$ exp xladm/xladmpwd@IDM file=base_clean_install.dmp log=base_clean_install_log.log full=y

If you need to, you may set your environment variables as follows:
export ORACLE_HOME=/ora/oim_infra
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=IDM

or in windows as follows:
set ORACLE_HOME=C:\ora\oim_infra
set PATH=%PATH%;%ORACLE_HOME%;
set ORACLE_SID=IDM

Note: Please change values according to your environment.

And your backup is done.

OIM Restore

Step 1

Firstly, delete user/schema owner & associated datafiles from Enterprise Manager. You may also use the following scripts to achieve it:

sqlplus sys/password@IDM as sysdba
SQL>drop user xladm cascade;
SQL>drop tablespace oim_dev including datafiles and contents;
SQL>drop tablespace oim_dev including contents;
SQL>quit

Note, we are logging in as sys user (with sysdba privilege) to delete the xladm schema owner / user and drop oim_dev tablespace completely.

Step 2

====================================
To Re-Prepare the OIM database:
====================================
log in as oracle (or login root and su - oracle)
cd /oim903/OIM903/installServer/Xellerate/db/oracle
sh ./prepare_xl_db.sh IDM /oracle/10_2 xladm xladmpwd OIM_DEV /oracle/OIM OIM_DEV_D01 TEMP sys_user_password

Here is the description of the parameters you need send to the script:
# Arguments : $1 --> ORACLE_SID
# $2 --> ORACLE_HOME
# $3 --> Oracle Identity Manager User Name
# $4 --> Password for the Oracle Identity Manager user
# $5 --> Name of the Tablespace to be created
# $6 --> Directory to store the datafile for the tablespace
# $7 --> Name of the datafile
# $8 --> Temporary tablespace for Oracle Identity Manager User
# $9 --> Password of the SYS user


Step 3

cd ~/exports
imp xladm/xladmpwd@IDM

Specify the filename from where the dump has to be imported. This will be the .dmp file name you specified when you took the backup. Also, specify xladm as the user when asked and press enter on all the other options presented.

Step 4

Next, we will recompile all invalid objects.

To recompile as user, save this file and run it from sqlplus:
Go to the folder that holds recompile_as_user.sql file. If you do not have this file, copy the text from below and save it one of the folder. Please note, you might have to run this twice to make sure all invalid objects are compiled.

sqlplus xladm/xladmpwd@IDM
SQL>@recompile_as_user.sql;
SQL>@recompile_as_user.sql;
SQL>quit

Then you can quit from sqlplus. If you are one of those who are intrigued to know which objects are rendered invalid, here is the sql you can use to find out this.

SQL>select object_name from user_objects where status='INVALID';
Here is the file:

----------------------------------------------------------
-- recompile_as_user.sql
-- connected as the SCHEMA_OWNER
----------------------------------------------------------
set feedback off;
set heading off;
set linesize 100;
set pagesize 1000;

spool recompile_as_user.lst;
prompt spool recompile_as_user.log;

select 'alter '||
DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||
object_name||' compile '||
DECODE(object_type,'PACKAGE BODY','BODY','PACKAGE','PACKAGE',' ')||';',
'show errors;'
from user_objects
where status = 'INVALID'
order by created,
DECODE(object_type, 'PACKAGE BODY', 'AAA', 'PACKAGE', 'AAB', substr(object_type, 1, 3)) DESC,
object_name;

prompt spool off;
spool off;

@@recompile_as_user.lst;
----------------------------------------------------------

Step 5

All set with the import. Restart your application server and you should be all set.

cd /opt/oracle/xellerate/bin
./xlStartServer.sh &
or ./xlStartServer.sh | tee /tmp/oim.log | less
or however you start your application server.

If you need to kill your application server, just do a ps -ef | grep java
and then you can kill the process.

Solving Oracle Issues if it is not up
===========================================

Check all the services are up (started) in Control Panel ->services

C:\oracle\product\10.2.0\db_1\BIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 27 16:01:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 188746604 bytes
Database Buffers 419430400 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL>quit

----------------------------------
Other DB Related Operations
----------------------------------
To ping TNS - tnsping idm
To start listener - lnsrctrl start
To start enterprise manager - emctl start dbconsole
To login to enterprise manager webconsole - http://server:1158/em
To startup database in sqlplus - startup
To shutdown in sqlplus - shutdown
To reach sqlplus address - http://server:5560/isqlplus
To initiate sqlplus - sqlplus

Another Backup Variant:
exp system/systempwd file=C:\DB_backup.dmp owner=xladm