Oracle – How to get a list of user permission grants

IBM Infosphere Information Server (IIS), Oracle – How to get a list of user permission grants
IBM Infosphere Information Server (IIS)

Since the Infosphere, information server, repository, has to be installed manually with the scripts provided in the IBM software, sometimes you run into difficulties. So, here’s a quick script, which I have found useful in the past to identify user permissions for the IAUSER on Oracle database’s to help rundown discrepancies in user permissions.

 

SELECT *

FROM ALL_TAB_PRIVS

WHERE  GRANTEE = ‘iauser’

 

If we cannot run against the ALL_TAB_PRIVS view, then we can try the ALL_TAB_PRIVS view:

 

SELECT *

FROM USER_TAB_PRIVS

WHERE  GRANTEE = ‘iauser’

 

Related References

oracle help Center > Database Reference > ALL_TAB_PRIVS view

InfoSphere DataStage – Operations Manual Template

When projects transition to production, the receiving team need some transition documentation to help with knowledge transfer and to guide them while they get accustomed to operating the application and can form their own documentation.  This temple is a shell, which usually provides enough detail, with some updating, to provide a quick reference regarding the application.  Also, having a temple can be a real time-saver, as opposed to writing it from scratch.

Related References

InfoSphere DataStage – DataStage Parallel Job Peer Code Review Checklist Template

Peer code review happens during the development phase and focuses on the overall quality and compliance to standards of code and configuration artifacts. However, the hard part of performing a Peer code review isn’t, performing the review, but rather to achieving consistency and thoroughness in the review.   This is where a checklist can contribute significantly, providing a list of things to check and providing a relative weight for the findings.  I hope this template assists with your DataStage job review process.

DB2 JDBC ISJDBC.CONFIG Configuration

Here are a few pointers for building an IBM InfoSphere Information Server (IIS) isjdbc.config file for an IBM DB2 Universal Driver, Type 4.

Where to place JAR files

For Infosphere Information Server installs, as a standard practice, create a custom jdbc file in the install path.  And install any download Jar file not already installed by other applications in the jdbc folder. Usually, jdbc folder path looks something like this:

  • /opt/IBM/InformationServer/jdbc

CLASSPATH

  • db2jcc.jar
  • Classpath must have a complete path and jar name

CLASS_NAMES

  • ibm.db2.jcc.DB2Driver

JAR Source URL

DB2 DEFAULT PORT

  • 5000

JDBC URL FORMAT

  • jdbc:db2://[:]/

JDBC URL EXAMPLE

jdbc:db2://127.0.0.1:50000/IADB

isjdbc.config EXAMPLE

CLASSPATH=/opt/IBM/InformationServer/ASBNode/lib/java/db2jcc.jar;

CLASS_NAMES=com.ibm.db2.jcc.DB2Driver;

Isjdbc.config FILE PLACEMENT

  • /opt/IBM/InformationServer/Server/DSEngine

Related References

Oracle JDBC ISJDBC.CONFIG Configuration

This jdbc information is based on Oracle Database 11g Release 2 (11.2.0.4), on a RAC (Oracle Real Application Clusters), JDBC for InfoSphere Information Server11.5 on Red Hat Linux.  so, here are a few pointers for building an IBM InfoSphere Information Server (IIS) isjdbc.config file.

Where to place JAR files

For Infosphere Information Server installs, as a standard practice, create a custom jdbc folder in the install path and copy the jar file into the folder (not install activity required).   Usually, jdbc folder path looks something like this:

  • /opt/IBM/InformationServer/jdbc

JAR Source URL

  • In this example, we used the jar files from the client install, but if you want to skip the client install you can download the drivers here: Oracle JDBC Drivers

Oracle DEFAULT PORT

  • 1521

JDBC URL FORMAT

  • jdbc:oracle:thin:@//:/ServiceName

or

  • jdbc:oracle:thin:@<host>:<port>:<SID>

JDBC URL EXAMPLE

  • jdbc:oracle:thin:@//RAC01-scan:1521/DW

Create And Place A jdbc configuration file

The Isjdbc.config file needs to be placed in the DSEngine directory:

Isjdbc.config File Path

  • /opt/IBM/InformationServer/Server/DSEngine

isjdbc.config Example

CLASSPATH=/opt/app/oracle/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar;

CLASS_NAMES=oracle.jdbc.OracleDriver

isjdbc.config Properties Notes

CLASSPATH

  • jar
  • Classpath must have complete path and jar name

CLASS_NAMES

  • oracle.jdbc.OracleDriver

Related References

Infosphere Information Server (IIS) – Metadata Repository Databases

This is a snippet of very useful information, which is buried in the IBM documentation. So, I thought I would bring it up a level for those of you who may be making decisions regarding Information server installations (IIS).  The table below provides an excerpt of the IBM IIS repository databases placement and guidance.  Please, keep in mind that which of these databases may be required for your installation may be a subset of these database depending upon your IIS product offering license.

Metadata Repository Databases

Repository or Database

Description

Default Database And Schema

Active Infosphere Information Server Metadata Repository

Stores the metadata about external data sources that are governed, managed, and analyzed by InfoSphere Information Server components. Normally referred to as the metadata repository.Database: XMETA

Schema: XMETA

Database must be the same database that is used for the staging metadata repository.

Infosphere Information Server Staging AreaStores metadata that is imported from external data sources so that it can be examined before it is moved to the active metadata repository.Database: XMETA

Schema: XMETASR

Database must be the same database that is used for the active metadata repository.

Analysis DatabaseStores results of information analysis by InfoSphere Information Analyzer.Database: IADB

Schema: IAUSER

Database cannot be the same database that is used for the active metadata repository or staging area.

Operations DatabaseStores monitoring data that is displayed by the InfoSphere DataStage® and QualityStage®Operations Console.Database: XMETA

Schema: User-defined repository user name, typically DSODB

Database can be the same or different as the database that is used for the metadata repository.

Standardization Rules Designer DatabaseStores a copy of revisions to InfoSphere QualityStage rule sets that have been made in the IBM InfoSphere QualityStage Standardization Rules Designer.Database: XMETA

Schema: User-defined data store user name, typically SRDUSER

Database can be the same or different as the database that is used for the metadata repository.

Exceptions DatabaseStores exceptions that are generated by InfoSphere Information Server products and components.Database: XMETA

Schema: User-defined repository user name, typically ESDB

Database can be the same or different as the database that is used for the metadata repository.

Match Designer DatabaseStores the results of match test passes by InfoSphere QualityStage Match Designer, a component of InfoSphere QualityStage. This data store is an ODBC data source that is used as a staging area before match designs are checked in to the active metadata repository.

Database: MDDB

User-defined database name and schema name. No default, but typically MDDB.

Database cannot be the same database as that used for the metadata repository.

 analytics, #DataIntegration,#informationserver,#Informationtechnology, #IT,Component,