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 – What are The support Connectors stages for dashDB?

dashDB

dashDB

In a recent discussion, the question came up concern which Infosphere Datastage connectors and/or stages are supported by IBM for dashDB.  So, it seems appropriate to share the insight gained from the question being answered.

What Datastage Connectors and/or stages are Supported for dashDB

You have three choices as to connectors, which may best meet you your needs based on the nature of your environment and the configuration chooses which have been applied:

  1. The DB2 Connector Stage
  2. The JDBC Connector stage
  3. The ODBC Stage

Related References

Connecting to IBM dashDB

InfoSphere Information Server, InfoSphere Information Server 11.5.0, Information Server on Cloud offerings, Connecting to other systems, Connecting to IBM dashDB

DB2 connector

InfoSphere Information Server, InfoSphere Information Server 11.5.0, Connecting to data sources, Databases, IBM DB2 databases, DB2 connector

ODBC stage

InfoSphere Information Server, InfoSphere Information Server 11.5.0, Connecting to data sources, Older stages for connectivity, ODBC stage

JDBC data sources

InfoSphere Information Server, InfoSphere Information Server 11.5.0, Connecting to data sources, Multiple data sources, JDBC data sources

InfoSphere DataStage – Operations Manual Template

Documentation

Documentation

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

SDLC Development Phase

SDLC Development Phase

Peer code review happens during the development phase and focus 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

JDBC ( Java Database Connectivity)

JDBC ( Java Database Connectivity)

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 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

 JDBC ( Java Database Connectivity)

JDBC ( Java Database Connectivity)

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 down load 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

Databases & Schemas

Databases & Schemas

This is 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 database 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 Area Stores 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 Database Stores 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 Database Stores 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 Database Stores 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 Database Stores 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 Database Stores 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,