About Informatica Sessions

SESSION LOGS:

Information that reside in a session log:

- Allocation of system shared memory
- Execution of Pre-session commands/ Post-session commands
- Session Initialization
- Creation of SQL commands for reader/writer threads
- Start/End timings for target loading
- Error encountered during session
- Load summary of Reader/Writer/ DTM statistics

Other Information

- By default, the server generates log files based on the server code page.

Thread Identifier

Ex: CMN_1039

Reader and Writer thread codes have 3 digit and Transformation codes have 4 digits.

The number following a thread name indicate the following:
(a) Target load order group number
(b) Source pipeline number
(c) Partition number
(d) Aggregate/ Rank boundary number


Log File Codes:

Error Codes Description

BR -   Related to reader process, including ERP, relational and flat file.
CMN -  Related to database, memory allocation
DBGR -  Related to debugger
EP-  External Procedure
LM -  Load Manager
TM -  DTM
REP -  Repository
WRT -  Writer

 
Load Summary

(a) Inserted
(b) Updated
(c) Deleted
(d) Rejected

Statistics details

(a) Requested rows shows the no of rows the writer actually received for the specified operation
(b) Applied rows shows the number of rows the writer successfully applied to the target (Without Error)
(c) Rejected rows show the no of rows the writer could not apply to the target
(d) Affected rows shows the no of rows affected by the specified operation

Detailed transformation statistics

The server reports the following details for each transformation in the mapping

(a) Name of Transformation
(b) No of I/P rows and name of the Input source
(c) No of O/P rows and name of the output target
(d) No of rows dropped

Tracing Levels

Normal - Initialization and status information, Errors encountered, Transformation errors, rows skipped, summarize session details (Not at the level of individual rows)

Terse - Initialization information as well as error messages, and notification of rejected data

Verbose Init - Addition to normal tracing, Names of Index, Data files used and detailed transformation statistics.
Verbose Data - Addition to Verbose Init, Each row that passes in to mapping detailed transformation statistics.


NOTE

When you enter tracing level in the session property sheet, you override tracing levels configured for transformations in the mapping.
 
MULTIPLE SERVERS

With Power Center, we can register and run multiple servers against a local or global repository. Hence you can distribute the repository session load across available servers to improve overall performance. (You can use only one Power Mart server in a local repository)

Issues in Server Organization

- Moving target database into the appropriate server machine may improve efficiency
- All Sessions/Batches using data from other sessions/batches need to use the same server and be incorporated into the same batch.
- Server with different speed/sizes can be used for handling most complicated sessions.

Session/Batch Behavior

- By default, every session/batch run on its associated Informatica server. That is selected in property sheet.
- In batches, that contain sessions with various servers, the property goes to the servers, that’s of outer most batch.
 

Session Failures and Recovering Sessions

Two types of errors occurs in the server
- Non-Fatal
- Fatal

(a) Non-Fatal Errors

It is  an error that does not force the session to stop on its first occurrence. Establish the error threshold in the session property sheet with the stop on option. When you enable this option, the server counts Non-Fatal errors that occur in the reader, writer and transformations.

Reader errors can include alignment errors while running a session in Unicode mode.

Writer errors can include key constraint violations, loading NULL into the NOT-NULL field and database errors.

Transformation errors can include conversion errors and any condition set up as an ERROR,. Such as NULL Input.

(b) Fatal Errors

This occurs when the server can not access the source, target or repository. This can include loss of connection or target database errors, such as lack of database space to load data.

If the session uses normalizer (or) sequence generator transformations, the server can not update the sequence values in the repository, and a fatal error occurs.

(c) Others
Usages of ABORT function in mapping logic, to abort a session when the server encounters a transformation error.

Stopping the server using pmcmd (or) Server Manager


Performing Recovery

- When the server starts a recovery session, it reads the OPB_SRVR_RECOVERY table and notes the rowid of the last row commited to the target database. The server then reads all sources again and starts processing from the next rowid.
- By default, perform recovery is disabled in setup. Hence it won’t make entries in OPB_SRVR_RECOVERY table.
- The recovery session moves through the states of normal session schedule, waiting to run, Initializing, running, completed and failed. If the initial recovery fails, you can run recovery as many times.
- The normal reject loading process can also be done in session recovery process.
- The performance of recovery might be low, if 
o Mapping contain mapping variables
o Commit interval is high


Un recoverable Sessions

Under certain circumstances, when a session does not complete, you need to truncate the target and run the session from the beginning.

USER_TAB_COMMENTS Stores comment information on tables.

USER_COL_COMMENTS Stores comment information for columns.

USER_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint.

ALL_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint.

USER_OBJECTS Can be used to verify the validity status of all user database objects.

ALL_OBJECTS Can be used to verify the validity status of all database objects.

USER_UPDATABLE_COLUMNS COLUMN_NAME and UPDATABLE will tell you whether the columns in a complex view can be modified.

USER_INDEXES Contains the name of the index and it uniqueness.

USER_IND_COLUMNS Contains the index name, the table name, and the column name.

USER_SYS_PRIVS Shows all privileges associated with this user.

SESSION_PRIVS Shows all privileges available in this session.

USER_ROLE_PRIVS Identifies the roles granted to you.

ROLE_ROLE_PRIVS Identifies the roles granted to other roles in the database.

ROLE_TAB_PRIVS Identifies object privileges granted to roles.

ROLE_SYS_PRIVS Identifies system privileges granted to roles.

SESSION_ROLES Identifies roles available to the current session.

Grant Privileges..System Privileges and Object Privileges

Granting system and object privileges is accomplished with the grant command. Example: GRANT create session TO turner or GRANT select, update, insert ON emp TO turner (NOTE, this object privilege syntax does not work when granting privileges to a single column - correct syntax is GRANT privilege (column_name) ON table TO user;).

System Privileges:

1. Database Access These privileges control who accesses the database, when he or she can access it, and what he or she can do regarding management of his or her restricted session. Privileges include create session, alter session, and restricted session.
2. Users These privileges are used to manage users in the Oracle database. Typically, these privileges are reserved for DBAs or security administrators. Privileges include create user, become user, alter user, and drop user.
3. Tables These privileges govern which users can create and maintain tables. The privileges include create table, create any table, alter any table, backup any table, drop any table, lock any table, comment any table, select any table, insert any table, update any table, and delete any table. The create table or create any table privilege also enables you to drop the table. The create table privileges also bestows the ability to create indexes on the table and to run the analyze command on the table. To be able to truncate a table, you must have the drop any table privilege granted to you.
4. Indexes The privileges include create any index, alter any index, and drop any index. You should note that no create index system privilege exists. the create table privilege also enables you to alter and drop indexes that you won and that are associated with the table.
5. Synonyms These privileges include create synonym, create any synonym, drop any synonym, create public synonym, and drop public synonym. The create synonym privilege also enables you to alter and drop synonyms that you own.
6. Views Privileges include create view, create any view, and drop any view. The create view privilege also enables you to alter and drop views that you own.
7. Sequences Privileges include create sequence, create any sequence, alter any sequence, drop any sequence, and select any sequence. The create sequence privilege also enables you to drop sequences that you own.
8. Roles Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Privileges include create role, drop any role, grant any role, and alter any role.
9. Transactions These privileges are for resolving in-doubt distributed transactions being processed on the Oracle database. Privileges include force transaction and force any transaction.
10. PL/SQL These privileges enable you to create, run, and manage those different types of blocks. Privileges include create procedure, create any procedure, alter any procedure, drop any procedure, and execute any procedure. The create procedure privilege also enables you to alter and drop PL/SQL blocks that you own.
11. Triggers A trigger is a PL/SQL block in Oracle that executes when a specified DML activity occurs on the table to which the trigger is associated. Privileges include create trigger, create any trigger, alter any trigger, and drop any trigger. The create trigger privilege also enables you to alter and drop triggers that you own.

Object Privileges:

1. select Permits the grantee of this object privilege to access the date in a table, sequence, view, or snapshot.
2. insert Permits the grantee of this object privilege to insert data into a table or, in some cases, a view. You can also restrict this privilege to specified columns of a table.
3. update Permits the granted to update data in a table or view. You can also restrict this privilege to specified columns of a table.
4. delete Permits the granted to delete data in a table or view. You can also restrict this privilege to specified columns of a table.
5. alter Permits the grantee of this object privilege to alter the definition of a table or sequence only; the alter privileges on all other database objects are considered system privileges.
6. index Permits the grantee of this object privilege to create an index on a table already defined.
7. references Permits the grantee of this object privilege to create or alter a table in order to crate a foreign key constraint against data in the reference table.
8. execute Permits the grantee of this object privilege to run a stored procedure or function.