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

  • jar
  • Classpath must have complete path and jar name

CLASS_NAMES

  • jdbc.OracleDriver

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

DB2 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

isjdbc.config EXAMPLE

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

CLASS_NAMES=oracle.jdbc.OracleDriver

Isjdbc.config FILE PLACEMENT

  • /opt/IBM/InformationServer/Server/DSEngine

Related References

Oracle SQL – Removing Non-Numeric Characters

SQL (Structured Query Language, Oracle, SQL, Removing Non-Numeric Characters

SQL (Structured Query Language)

Sometimes, when converting fields from text to numeric fields in SQL, there is a need to remove one or more non-numeric values.  These approaches have been useful for handling multiple non-numeric values.

Removing non numeric values – Approach 1:

I have found this approach useful, when working with character (Char, Varchar, NVARCHAR) fields being converted to non-integers outputs (e.g. Decimal).

CAST(( CASE WHEN TRIM(<<SourceFieldName>>) > ‘9999999999’ or  length(REGEXP_SUBSTR(<<SourceFieldName>>, ‘[A-z]’, 1, 1))>0 or trim(<<SourceFieldName>>) in (‘-‘,’+’,’?’,’/’,’*’) THEN NULL ELSE trim(<<SourceFieldName>>) END) AS    DECIMAL(10,2)) AS <<TargetFieldName>>,

Removing non numeric values – Approach 2:

 

If you need to know if a content of a field is numeric, I have most this approach useful when working with character (Char, Varchar, NVARCHAR) fields being converted to integers:

CAST(( CASE WHEN LENGTH(TRANSLATE(<<SourceFieldName>>,’0123456789′,”))=0 THEN (<<SourceFieldName>> ELSE NULL END) as INTEGER) as <<TargetFieldName>>,

Related Posts

Oracle SQL Date Formatting Examples

Oracle Database, SQL Date Formatting Examples, TO_CHAR

Oracle Database – SQL Date Formatting Examples

Below is a SQL of various examples of Oracle date formats, which I have found to be a useful reference.

 

Select

SYSDATE,

TO_CHAR(SYSDATE,’YYYY-MM-DD AD’) AS ANNO_DOMINI,

TO_CHAR(SYSDATE,’YYYYMMDD’) AS SORT_PATTERN1,

TO_CHAR(SYSDATE,’YYYY/MM/DD’) AS DATE_PATTERN1,

TO_CHAR(SYSDATE,’DD/MM/YYYY’) AS DATE_PATTERN2,

TO_CHAR(SYSDATE,’DD/MON/YYYY’) AS DATE_PATTERN3,

TO_CHAR(SYSDATE,’DD-MON-YY’) AS DATE_PATTERN4,

TO_CHAR(SYSDATE,’CC’) AS CENTURY,

TO_CHAR(SYSDATE,’YYYY’) AS FOUR_DIGIT_YEAR,

TO_CHAR(SYSDATE,’YY’) AS YEAR_OF_CENTURY,

TO_CHAR(SYSDATE,’J’) AS JUALIAN_DATE,

TO_CHAR(SYSDATE,’Q’) AS CALENDAR_QUARTER_OF_YEAR,

TO_CHAR(SYSDATE,’WW’) AS CALENDAR_WEEK_OF_YEAR,

TO_CHAR(SYSDATE,’DDD’) AS NUMBER_OF_CALENDAR_DAY_OF_YEAR,

TO_CHAR(SYSDATE,’W’) AS WEEK_OF_MONTH,

TO_CHAR(SYSDATE,’DAY’) AS NAME_OF_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’DY’) AS ABBREVIATED_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’MM’) AS NUMERIC_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MON’) AS ABBREVIATED_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MONTH’) AS SPELLED_OUT_MONTH_OF_YEAR

FROM DUAL;

 

Related References

 

Oracle SQL – Trimming a Specific Literal from a Field

Oracle Database

Oracle Database

I have frequently used the trim command for one purpose or another, but I have not used it to extract a literal out of a field, which I recently used to extract invalid values from a field in SQL.  So, I am providing a sample SQL for Future reference.

 

SQL Pattern:

  Select trim (‘I’ FROM <<FieldName>>)  AS <<FieldName>>

From <<TableName>>;

 

Substitution example:

In this example an invalid indicator (‘Y’) is being removed, performing a Null Value Logic (NVL) substitution to a integer compatible value, then the field is converted to an integer.

 

Select

CAST( NVL(trim (‘Y’ FROM AA.Status_ID) ,’0′)AS                   INT)         AS PACK_SZ

From  Inventory_code_TBL AA;

 

Reference Links: