ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

February 13, 2011
by Cezar Santos
9 Comments

Categories: Architecture , How to , Logic , ODI , Tips and Tricks

Tags: , , ,

Single Post View


Killing session in ODI 11g

Hi everyone…

This post is about a something new on ODI 11g…

I must confess that  when I saw the first time I didn’t get the concept but now, it’s more clear!

I hope you all enjoy!

In ODI 10g we had this big issue of killing the job especially its big headache when we need to ping or disturb DBA . ODI 11g seems to handle this situation smartly by introducing new options called STOP NORMAL and STOP IMMEDIATE.

You can find this option under the running session.Lets look how this two options work.

image

STOP NORMAL

I am killing the running session using the option STOP NORMAL. The SID for this query is 143  and so I right click and click on Stop Normal

image

A pop up will be shown for  Stop Session dialogue and click OK to kill it.

image

After running a few test I have seen that Stop Normal takes time to kill the session and  after a while we get the Session Stopped dialogue. Generally in the Normal  kill command,  Oracle marks the query with status of Kill so most of the time the Oracle will wait for the operation or query to be processed and mark the query as Killed and pretty much the similar logic is being carried out for the STOP NORMAL, so this kill operation takes time.

image

image

My SID query also return nothing as the Session is killed in the Database too.

STOP IMMEDIATE

image

Similar to the Stop Normal the  Stop Immediate will ask the permission to be killed or not . Click OK for the confirmation.

image

Being an Immediate clause ,  the query is killed immediately.

image

Note:- This process can only work when the User used in Topology have the necessary Kill  permissions.

That is it! I hope be useful to all of you!

Thanks for visiting us!

January 25, 2011
by Cezar Santos
0 comments

Categories: Administration , Architecture , How to , Logic , ODI , Tips and Tricks

Tags: , , , ,

Single Post View


11g Oracle Data Integrator – Part 8/11g – Table Partitioning supported

Hi everyone…

Some new features on Oracle Data Integrator 11g. A lot of  “Oracle people” will enjoy it even that still needs some improvements…

We have seen that in ODI 11g the support for table partition . We wanted to see the ability and support and recognition of it , so we have done some sample run through test and here we are sharing what we have seen .

Here we are creating a Partition based table.

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER(6,0),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0) )
PARTITION BY RANGE (EMPLOYEE_ID)
( PARTITION P1 VALUES LESS THAN (120) TABLESPACE USERS ,
PARTITION P2 VALUES LESS THAN (140) TABLESPACE USERS,
PARTITION P3 VALUES LESS THAN (160) TABLESPACE USERS,
PARTITION P4 VALUES LESS THAN (200) TABLESPACE USERS ,
PARTITION P5 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

After running the script in database , We have used the selective reverse and have found that it failed to recognizing the partition, so we reran the reverse using the RKM Oracle.

clip_image002

The Oracle RKM successfully recognized and captured the Partition and was able to see in the Datastore also as a standard practice its always great to go for Oracle RKM.

clip_image004

Since database handles the partition allocation, handling the insert was easy task.

clip_image006

SELECT * FROM EMPLOYEES PARTITION (P1);

clip_image008

Now creating a temporary interface with partition on it and loading the data

we also wanted to check if the Create table can create a partition based table and we have created database structure with the required partition type and values and used the same into the target.

clip_image016

clip_image018

clip_image020

clip_image022

clip_image024

As you can see the Create table was created without Partition, so still the KM don’t have the support to handle the partition and would need to be created via either database or Generate DDL and run the same in the target.

clip_image030

clip_image026

 Well, as I told at the beginning still needs some improvements but already useful.

Thank you for visit us!!!!

January 17, 2011
by Cezar Santos
7 Comments

Categories: Administration , Common Errors , How to , ODI , ODI Bug

Tags: , , , , ,

Single Post View


Jython Scripts fails to recognize the module during scenario run in ODI 11g

Hi Friends!

2011… we are back! 😉

During a recent Oracle forum question i was looking at an interesting question where the user said the ODI Scenarios throws the error ‘ No module names re ‘ . Initially i thought might be the user is making some mistake but then he also wrote ‘the codes are working perfectly in the Designer’. Strange .

I thought let me do a faster run in ODI 11g and check if that is really the case and I wrote a small Jython procedure based on ‘re’ module and ran via designer no issue. Now i made the scenario and ran via startscen . Hmm same error as the user reported.

I thought ok it looks like some issue with odiparams and initial scan made me realize that path is taking <WEBLOGIC_HOME>Oracle_ODI1oraclediagent as ODI_HOME and its reading directories in it namely lib,drivers and bin . I drilled and started looking for other such path and found that <WEBLOGIC_HOME>Oracle_ODI1oracledi.sdklib had the Jython.jar and folder Lib had required Jython module , which gave me the hint that becuase of odiparams the startscen is reading from the agent directory and there is no Jython modules present so i copied the folder to under lib/Lib with all the modules and re ran the scenarios and it worked. 🙂

Although i thought of changing the path of odiparams but then it might cause other links broken so copying the Lib folder from Oracle_ODI1oracledi.sdklib to Oracle_ODI1oraclediagentlib is the easiest solution.

Thank you for following ODIExperts.com

January 10, 2011
by Cezar Santos
6 Comments

Categories: Administration , Architecture , How to , ODI , Tips and Tricks

Tags: , , , , , ,

Single Post View


Scenarios, a lot in the first delivery makes maintenance very simple!

Hi Everyone!

First I wish to all of you a really Happy New Year! I hope that 2011 can be the best year ever to all of us!

Second, sorry, sorry and sorry. We “abandoned” our blog this last few weeks. I got a lot of new responsibilities on my formal job and Dev was in vacation with his family. We are going to try to be more present from now forward.

Third (and last) let’s talk about scenarios!

I created this title “Scenarios, a lot in the first delivery makes maintenance very simple!” in the hope of a full understanding about what this post is in just one phrase once I would like that after we finish to discuss it you can tell me if the title is right or not…

I Oracle Data Integrator, our famous ODI, is very common to develop all objects that you need to your integration and, at end of the development, add all of these at a package, create a scenario and that is it. Test and go to production.

That is the common way in all ODI environments that I saw until today… let’s discuss this.

When we are developing a it’s normal to have several tables to load in some order (parents, children, etc), procedures to performs some “not interfaceble” task and variables that uses code (refreshing type). Then, all we do is add all of this in a package, generate de scenario and our development is done.

OK, now let’s think about it from the Software Development Life Cycle (SDLC)  perspective, I mean, everything that is developed will get some maintenance in one of his “pieces” some day.

As we change just one piece from all our package, of course we should test just that piece right?

NO!  And here is the problem!

Who can guarantee that none of the others pieces (or ODI objects) was changed? In fact, using a trusted SDLC, you need to execute the complete test plan (the same that allows the first delivery) once all code was “compiled”, I mean, regenerated (in ODI terms).

Do you agree?

And if we split the development in small pieces of scenarios, as small as possible, even at object level (as we can generate scenarios from objects ) and then create a package (or packages) that orchestrate the execution of this small pieces????

Of course that the first delivery will have a lot of scenarios to be imported at production but when some change is necessary you can update only the relevant code and, too, test only it.

In this way your SDLC will get less errors and the cost (HR, Financial, Hardware, etc) of tests will decrease substantially and plus, you can always re-use any of the object scenario.

If you pay attention, you will see that we are talking about the old concept of Object Orientation, nothing new.

And now, what do you think? Is the title right? 🙂

A tip to use this approach is to have the Operator (scenarios tab) organized in folders. This will avoid the endless list of scenarios and brings organization to the environment.

Well friends, that’s all for now. I hope you keep traking us!

Once again, Happy 2011!!!!

Cezar Santos

December 3, 2010
by Cezar Santos
15 Comments

Categories: Architecture , Logic , ODI , Tips and Tricks

Tags: , , , ,

Single Post View


Direct load by interface with no work tables (C$, I$)

Hi friends…

I got this video from Craig Stewart that shows us how to direct load data from source to target with no work (C$ and I$) table use.

http://s3.amazonaws.com/Ora/ODI-Simple_SELECT_and_INSERT-interface.swf

Simple, fast and very clever!

Thank you for share your expertize Craig!

November 16, 2010
by Cezar Santos
0 comments

Categories: Administration , Architecture , Drivers , How to , Logic , ODI , Tips and Tricks

Tags: , , , , ,

Single Post View


11g Oracle Data Integrator – Part 7/11g – SUNOPSIS MEMORY ENGINE

Hello everyone.

As this is a feature that comes from 10g but still present at 11g and pretty useful, we decide to discuss it as “11g part”. You will see that it’s really useful in several situations.

We all have seen the Sunopsis Memory Engine but have never used it or played with it. This post is all about it and using it smartly for various activities, without using target RDBMS for temporary activities.

If you are wondering what actually is Sunopsis Memory Engine , its nothing but the HyperSQL , which is an 100% java database. The Demo environment  runs on the HyperSQL.

[ Note : HyperSQL stores its data in memory ,so once the ODI is closed and restarted the data and the tables are flushed out ,so HyperSQL is great for storing or processing data for a particular session ]

ScreenClip(3)

You can find more about it here http://hsqldb.org/ and Documentation at this link – http://hsqldb.org/web/hsqlDocsFrame.html

we believe that Sunopsis Memory engine can be great for creating temporary table or storing data temporarily for ODI logic implementation.

Lets look at various implementation uses and method.

For ODI 10G ,  Technology  – Sunopsis Engine and Schema – SUNOPSIS_MEMORY_ENGINE

For ODI 11G , In-Memory Engine and Schema – SUNOPSIS_MEMORY_ENGINE

create table temp_table ( id INTEGER, name varchar(20))
insert into temp_table values ( 1 ,’abc’)
insert into temp_table values ( 2 ,’abcd’)
insert into temp_table values ( 3 ,’abcdef’)

ScreenClip

Calling it in the Variable

Similar to any RDBMS you can call these tables into the Variable too , just select the SCHEMA as SUNOPSIS_MEMORY_ENGINE and write your query.

For example select id from temp_table.

ScreenClip(1)

ScreenClip(2)

Similar to Oracle , you can read it via Jython or Java and fetch the required information.

If you ever need to read the data from File via jython or Java and write into Sunopsis Memory. Here are the codes.

Jython.

import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
read_file=open('c://table_name.txt','r')
read_line=read_file.readline()
while read_line:
     sqlstring.executeUpdate("INSERT INTO TABLE_NAME VALUES ('"+read_line+"')")
     read_line=read_file.readline()

sourceConnection.close()
read_file.close()

Java

<@
import java.io.*;
import java.sql.*;
sourceConnection = odiRef.getJDBCConnection("SRC") ;
sqlstring = sourceConnection.createStatement();
FileInputStream fstream = new FileInputStream("C:/table_name.txt");
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;    
while ((strLine = br.readLine()) != null)   {
            sqlstring.executeUpdate ("INSERT INTO table_name values('"+strLine+"')") ;
}    
in.close();
sourceConnection.close();
@>

Temporary Data storage.

Some time we would like to store the data into Temporary purpose for testing or debugging purpose.

image

We can also reverse engineer the table using the select reverse too.

image

If you want to play with via  external Sql editor ,you can access the jar file located at  <ODI_HOME>oraclediagentlib   hsqldb.jar  for ODI 11g image

Now you can use it as you wish!!!

Please, keep visintig us!

November 12, 2010
by Cezar Santos
9 Comments

Categories: How to , Logic , ODI , Tips and Tricks

Tags: , , , , , , ,

Single Post View


11g Oracle Data Integrator – Part 6/11g – Union , Minus, Intersect

Hello Friends ,

We have shows you few important features of the new ODI 11g ,today we are going to show you how to implement Union , Minus etc logic in ODI 11g which was lacking in older version and thus maturing ODI in the ETL world.

After the initial step of the Interface name ,Click on the Add/Remove Dataset

clip_image001

Rename the Dataset Name to Source 1, Source 2 for more meaningful or else you can provide the Table name too and in the Second Dataset name select the Operator as Union.

clip_image002

Drag in the Source tables and Target table and map the columns accordingly.

clip_image004

Go the Flow tab and select the Staging Area and select the appropriate LKM and IKM.

[ since my Source are in the Same database so no LKM ) and Execute

clip_image005

ODI 11g provide a new feature called Simulation as that generates all the required codes without running the Interface , useful functionality when you trying carry some complete logic where you want to test in SQL Developer or Toad because carrying out in ODI .

It worked and we got 107 inserts

clip_image007

Lets see the codes and as we see it has carried out the required UNION operation between two tables.

clip_image008

Let’s try the same interface for Minus with truncate table option yes in Target.

To do so, we have just changed the operator for Source 2 to Minus and execute

clip_image009

clip_image010

Similarly it goes for UNION_ALL and INTERSECT

What if we want to do UNION between three tables? Just select the right operator and the ODI will create the logic as defined.

clip_image011

clip_image012

This functionality give an extra edge for ODI on ETL side.

Keep looking odiexperts for more ODI 11g  functionalities .

November 5, 2010
by Cezar Santos
30 Comments

Categories: How to , ODI , Tips and Tricks

Tags: ,

Single Post View


Jython’s Beginner Guide for ODI

Hello Friends,

There are few days that we don’t have new posts around here… We are pretty busy in the business in the past days!!!

But, here we are with a new post that we, personally, think will be really helpful to those who like “deep” programming in ODI.

I hope you all enjoy it!

When it comes to ODI sooner or later we might have to deal with Jython and some one who have used Java would find that easy ,but not for every one . Considering that we thought of writing this post in understanding how to deal with Jython in relation to ODI . We are not going to teach you Jython 🙂

Generally most of the uses of Jython in ODI is either to capture and raise error in the operator, run OS command and do the appropriate if and else condition , reading and processing records for RDBMS , File processing based on different logic and so on.

When we use jython like java we need to import different modules , Example

import os
import re

we can also import java libraries something like you have seen few of the past codes

import java.lang.sql as sql
import java.lang.io as io

Lets look on few codes which we might use more and more.

Reading , Writing , Re-writing the file

There are different format in jython to process files, but we personally like this format
srcfile=open(‘file_name’,’r’)
Here in the above syntax, we can change the parameters of file by

r – only for reading
r+ – re writing into the same file
w – writing to a new file

When we use W (writing) on an existing file the whole file gets flushed or cleared out,so be careful when pointing to any file.

There are again different format to read the lines of the Files either complete or line by line.
We can write codes using the list format to process records into the memory but if we are dealing many millions of records calling and storing into memory and processing it can throw memory error something like  java.lang.memory exception . Keeping that in mind , we  write codes to read line by line and process accordingly.

Reading Line by Line

srcfile=open('file_name','r')
first_line=srcfile.readline() - Reading First Line
second_line=srcfile.readline() - Reading Second Line

This way when we need to read just one line we can use to readline() command . The pointer of the line keeps moving one line to next line.

Lets say if we want to keep reading all the lines

srcfile=open('file_name','r')
line=srcfile.readline()
i=0
while line:
    print line
    i+=1
    line=srcfile.readline()

print 'Total Number of Lines', i
srcfile.close()

This above simple program reads all the line one by and incremented i for each line it read, so that at the last we are also print the value of ‘i ,denoting the number of lines in File.

Let me cover step by step

srcfile=open('file_name','r')
# opening the File in Read only mode
line=srcfile.readline()
# Reading the First Line
i=0
while line:
# while loop started to read the lines one by one
    print line
    # printing the line . Please note that when ever you write codes under while or for loop ,you need to write after a tab space else the codes wont get  executed.
    i+=1
    # increment the value of i by 1
    line=srcfile.readline()
    #reading the next line of the File

print 'Total Number of Lines', i
# print the value of i
srcfile.close()
#Always close the file which you have open for read , write or any other mode else the file is open by memory in jython and unnecessary data is lying in its #memory.

Reading all the Lines at one shot

srcfile=open('file_name','r')
lines=srcfile.readlines()
print lines
srcfile.close()

Here all the lines of the Files are read a list and stored in the memory error, so in case if we have huge record it can throw memory . To solve either go for Record by record processing which can be a little time consuming or increase the Java memory to handle the processing.

Here  readlines() reads all the lines of the source file and stores in the form of array and pass it to variable lines.

Writing the data into File.

There are different ways we can write the records into File .

If you are writing a string , you can use the write(string )  to write into file

output_write=open('<file_name>','w')
str1='This is a sample program'
output_write.write(str1)
output_write.close()

In case you are planning to write multiple lines say array or list, you can use the writelines()

source_file=open('<file_name>','r')
output_write=open('<file_name>','w')
read_lines=source_file.readlines()
output_write.writelines(read_lines)
source_file.close()
output_write.close()

You can also use the java BufferedWriter.

import java.io as io
writer=io.BufferedWriter(io.OutputStreamWriter(io.FileOutputStream('<file_name_to_be_writtne>')))
writer.write('string or content')
writer.flush()
writer.close()

One of the fastest way to write into File is to store data  into Variable and write them once rather than in a while or for loop that way the overall time is reduced . This idea is great when you running your codes in Server and as we have high memory to process  and store larger records

seek

If you ever want to go to the First line of the file use seek(0) command , so in the above program if i want to go to first line it will be

srcfile.seek(0)  #( seek(zero))

t and n

t moves the pointer to include a tab space
n move the pointer to next line

Concatenate strings

str1='abcd'
str2='efgh'
print str1+str2
#output abcdefgh

Using + we can concatenate two strings .

str1='abcd'
str2=25
print str1+str2
#output Error - TypeError: __add__ nor __radd__ defined for these operands

Reason being we can’t concatenate string and a number

solution 1 - define number in the format of string so - str2='25'
(or)
solution 2 - Convert it into string while printing - str(str2) ,so print str1+str(str2)

to convert from anything to string use str()  function .

Replace

to replace any string  or character with another string or character

str1='123'
str2='456'
action='dist;item'
long_string='ds;sfsdf,sfsdf//'
print str1.replace(str1,str2)
# output  456
print str1.replace('1','7')
# output 723
print action.replace(';',',')
# output dist,item
#you can also have multiple replace at the same time
print long_string.replace(';','').replace(',','').replace('/','')
# output dssfsdfsfsdf

Arrays

Arrays are fun way to handle string and number.To declare a null array use this syntax

str1=['action','item','123']
print str1[0]
# arrays always starts with 0 and so on
# output action
print str1[2]
#output 123

To declare any as null array to this  str1=[]

Converting Arrays to string

str1=['action','item','123']
print ''.join(map(string.strip,str1))
# using this syntax we can convert the array to string
#output actionitem123

Converting String to Array

str1='this is a string'
print list(str)
#output ['t', 'h', 'i', 's', ' ', 'i', 's', ' ', 'a', ' ', 's', 't', 'r', 'i', 'n', 'g']

Converting characters into Array

str1='this is a string'
list1=str1.split(' ')
print list1
#output ['this','is','a','string']
print list1[0]
#output -  this
print list1[1]
#output - is

this split command is useful when you wish to split the string into words and fetch the required on as appropriate.

Raise

Raise is a great command to raise an error and we can use raise to publish the required error into the Operator.Example

raise " This is the following Error " , string
raise str1

Capturing position in String

#first step import module
str='12345'
# assigning value to String
print str[:-1]
# print command is used to print in the output
## when we use -1 it means it starts from the end of the string
## output 1234 as end is 5 and so backwards leave 1 character.
print str[:-2]
## so leave 2 position from end , so output 123
print str[0]
# 0 is treated as the first position
print str[0:3]
#expected output 123
print str[2:4]
#expected output 34

String search

There are some great high level search we can do used regex but here i will be showing you here simple search method using string.find

str1=' This is a program '
print string.find(str1,"is")
if string.find(str1,'is')> 0:
    print str1
else:
    print ' Not available '

# output - This is a program

whenever string.find , finds the particular string in it refers to the position and that’s the reason I use > 0 .

Another simple and efficient search is using Regular Expression (re) .

import re
str1='This is a program'
if  re.search("is",str1):
print ' Match found'
else:
print ' Not Found'

Please look into link for more way to handle simple and complete expression methods and functions.

http://www.jython.org/docs/library/re.html

OS.system

os module helps us to do Operating system based activity either in windows or unix.
os.system run an operating system command fetches the error if fails. If the process is successful then 0 else the required error message .
ODI  have the OS Command and OdiOSCommand to do pretty much the same work without writing any jython
codes .

Lets take another example

import os
if os.system ("copy test.py test1.py"  ) == 0 :
     print "success"
else:
     print " failure "

For os.system  the 0 (zero) is always marked by success and rest of the return codes by failure or error.

Look at this documentation link for os  –   http://www.jython.org/docs/library/os.html

AND  and OR condition

For and condition use this & (and symbol)
OR condition use this | (pipe symbol )

For example

str1= 'Example1'
str2='Example2'

if (( str1 =='Example1' ) & (str2 == 'Example2')):
     print  ' And works '
#output And works
if (( str1 =='Example1' ) | (str2 == 'Example1')):
    print ' OR works '
#output OR works
if ((( str1 =='Example1' ) & (str2 == 'Example3')) | (str2 == 'Example1')):
   print  ' AND and OR combination does not works '
else:
   print  ' AND and OR combination works  '
#output - AND and OR combination works

IF condition

General format
if  (condition )
results
elif (condition)
results
else
results

Ex

str1='Examples'
if str1 == 'Example' :
   print ' The String is Example '
elif str1 =='example' :
   print ' The String is example'
else:
   print ' The str1 is neither example (nor) Example '

[ Note – there is always a tab space after if condition ]

While Condition

While condition is great when we need to process something one by one and execute something till the end.

Example

File C:output.txt

abc
dba
dsg

file_open=open('C:output.txt','r')
line =file_open.readline()
while line:
    print line
    line =file_open.readline()

[ Note – there is always a tab space after while condition , till the condition is complete ]

Jython + SQL

We also use Jython to play with SQL and write the result either in File , Operator etc. To use the SQL we use the Java libraries.

import java.lang.* as lang
import java.sql.* as sql

When it comes to Jython+SQL the logic , format is pretty much the same as Java  , so in case you find an example with Java you can modify with few simple syntax change and write the codes for Jython.

The most efficient way is to use the sunopsis API getJDBCConnection for connecting to Source system using Java or Jython. This way we don’t have to worry about providing the driver parameters or hard coding them since they can change from the environment to environment. In case if you ever need to get particular info about driver , url or user you can use API getInfo()

Initially create the connection to the Source System

sourceConnection = odiRef.getJDBCConnection("SRC")

Here in Command on source we select the required technology and associated schema.

sqlstring = sourceConnection.createStatement()

Next step is to create the statement for execution .

sqlstmt="SELECT 'Errored Interface t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1,
LENGTH(ORIGIN))||'nError Message tt- '||ERR_MESS||'nNo of Errored Records t- '||
ERR_COUNT AS OUTPUT FROM  ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)"

In sqlstmt we define the complete select , insert, update string and pass it to this variable. This way we can call it multiple time and if we have to modify , we can change it at one place.
Here we have used t to have the tab space between the column and the output and n to have the next result in second line this way the result can be more organized. For an output example Please visit this link –http://odiexperts.com/error-records-log

result=sqlstring.executeQuery(sqlstmt)

To execute a select query use executeQuery(sqlstmt) and to insert /update query use executeUpdate(sqlstmt) .

while result.next():

In order for you to read and process the select query we tend read it records using a while loop and finally you can use getInt , getString and fetch the required records . Please look into the Java documentation for other APIs.

Also you can other similar example related to Jython + SQL

http://odiexperts.com/displaying-sql-results-in-operator
http://odiexperts.com/writing-sql-counts-using-jython

Looking for Editor to learn and practise Jython.
1. Eclipse [ Plugins such as Pydev , JyDT ]
2. Jython.bat

Jython Sources and Documentation

If you are looking for Documentation on Jython , the best resource would be to look into official documentation of jython

http://www.jython.org/docs/

http://www.jython.org/docs/library/ [ Very Important Link ]

[ Note : The links are for Jython 2.5 and so you might find functions and methods which might not be still supported as ODI 10g is on 2.1  ]

Also you might not find so many codes or example for Jython , but you can refer to Python documentation and example too , as Jython is Java + Python .

You can find Python documentation here  http://python.org/doc/

also look into the Oracle documentation ( http://download.oracle.com/docs/cd/E15985_01/doc.10136/reference/DIJQR.pdf ) there  are some good example covered .

Jython not recognizing Windows 7 as NT

Please look into this link http://odiexperts.com/jython-os-bug-in-windows-7

Handling  spaces in path

While passing the OS system path especially in windows we have issue with spaces causing the error Path does not exists or File not Found etc . In order to solve that , Please have the spaces between folder name between double parenthesis. Say for example

import os
ospath=(r'C:Program" "FilesMicrosoft" "SQL" "Server\100\Tools\Binn\bcp')
if os.system(ospath)  > 0:
    raise 'BCP Unload Failes'

Program File is – Program” “File ( Program<double_quote><space><double_quote>File).
Microsoft SQL Server is Microsoft” “SQL” “Server

\ in windows path

Its good to use the two slashes in windows path , reason being some time some of the combination yields values which are understood by Jython differently  , say for example

c:numberten

In this example the n and t would be understood by Jython as next line and tab space causing to read the line wrong  and would throw errors like file not found or path does not exists etc ,so the correct way to write is (ie using double slash)

C:\number\ten

Hope this should provide beginners a guide towards the world of Jython+ODI .
We have covered few codes of Jython and  might update this post in future too so that we have consolidated Jython guide  at one place.

[ Note: There are numerous other ways to do the above codes in different way , you can explore more by looking into Jython books and documentation. ]

Keep looking into the odiexperts for more tips and tricks .

“is”,str1

October 19, 2010
by Cezar Santos
11 Comments

Categories: How to , Logic , ODI , Reports , SQL , Technology , Tips and Tricks

Tags: , ,

Single Post View


Send SQL by Email

Hello Friends,

We have shown you in the past how to write SQL using Jython to file , SQL in Operator and Rows counts into File and various other technique but some time we don’t want to send as an attachment but rather as a direct Email.

Here is a very simple Technique ,which can be done in one to two steps.

Step 1.  Java Codes

Command on Source – Database Technology and Schema

Command on Target , Technology- Java Bean Shell.

and paste the following codes and just change the Query  string , if required delimiter.

<@

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

conn=odiRef.getJDBCConnection("SRC");
Statement stmt=conn.createStatement();
String result="";

char delimiter=(char)44;
// Please change the delimiter here,i.e just change the number
// TAB - 9
// COMMA 44
// GET THE CODING LIST AT http://www.zytrax.com/tech/codes.htm

my_query="SELECT * FROM ODI_TEMP.PERIOD_YR ";

// Either provide the columns or select * from all columns

ResultSet rs=stmt.executeQuery(my_query);
ResultSetMetaData md=rs.getMetaData();
int numColumns =md.getColumnCount();

// Fetch column names
for (int i=1; i<numColumns+1; i++) {
 String columnName = md.getColumnName(i)+ delimiter;
 result+=columnName;
 }

result=result.substring(0,result.length()-1);
result+=(char)13;

int times=result.length();

for (int i=1; i<times ;i++){
 result+="-";
 }

result+=(char)13;

// Fetching Rows

result=result.substring(0,result.length()-1);
result+=(char)13;

while (rs.next()) {
 for (int i=1; i<numColumns+1; i++) {
 String  output=rs.getString(md.getColumnName(i))+ delimiter;
 result+=output;
 }
result=result.substring(0,result.length()-1);   
result+=(char)13;
 }

// Close Connection

stmt.close();
conn.close();
@>

Step 2.  Email using OdiSendMail

Command on Target  – Sunopsis API

and paste the following codes.

OdiSendMail -MAILHOST=<mail_host> -FROM=<from_user> -TO=<address_list>

<@=result@>

(or)

You can also call the OdiOutFile etc in the package too and in the message call <@=result@>, but make sure you call the above java procedure before it.

Simple and Easy .

Sample Output

Look for more tips and tricks from odiexperts.com 🙂 . Also keep looking for more ODI 11g   posts.

October 6, 2010
by kdevendr
2 Comments

Categories: Administration , Architecture , How to , ODI

Tags: , , , , ,

Single Post View


Security Module – Main concepts and use

Hi Friends,

We wrote this article to try to send some light on Security Module (10g for now) once several emails asked us for do it.

I hope that it can be useful!

The creation of users is relatively simple. Just open the Security module, go to the tab “Users” (Users) clicking the right mouse button and choose “Add User”

clip_image002

After opening the window only enough to fill the required information.

IMPORTANT: IF YOU CHECKED THE CHECK BOX OF “SUPERVISOR” CREATED YOU WILL HAVE NO RESTRICTION ON USE OF ENVIRONMENTAL ODI. This option enables the user CALLED “superuser”

At the end of user creation, you must add this to the desired profiles. I will use as an example, a developer can access any object in the repository development.

To do so, simply drag (drag and drop) the desired profiles to the newly created user:

clip_image004

IMPORTANT: THE PROFILE “CONNECT” IS MANDATORY FOR ALL USERS

Completed these steps the user is able to connect the repository with rights that were signed.

Granting privileges on projects and their components.

Granting privileges to a specific project can only be assigned to users have “Supervisor” and also with the profile of the “NG” (not generic).

In our example we will use a user named “Developer” which has only the profiles of “Connect” and “Designer NG

clip_image006

To determine if access should open side by side on screen, both modules, and Security Developer and then drag the desired design for the user who is entitled to access it as:

clip_image008

It will be shown a confirmation window

After confirming opens a window where rights of association have the following concepts:

a. The mark “Checked” means that the user can perform that action in any repository.

clip_image010

  1. The mark of “Denied” means that the user can not use that method:

clip_image012

  1. It is the mark of “Interrogation” means that the user can only use that method in the specified repository:

clip_image014
Established these conditions must then use them in the security window to set the user access to each object of that project:
clip_image016
Note that the methods “Edit Project” and “Duplicate Project” will only be available in the Repository “REPWORK.”
Only the method “Edit in new Window” will be allowed to all repositories
All other methods are denied (this is the default, should be configured as needed.)
We can see that after connecting the User “Developer” he has no rights other than those set:
clip_image018
It is interesting to inform you that the “rights” controlled by the security window can be understood as the options that appear when you right click on an object.
Other relevant information and that in this case example, there were signed on Direct Folders , Packages, Interfaces, etc.. And with that, the project does not bring any of your folders and if there is an attempt to create, an error such as:
clip_image020
The configuration of users without the standards should be made at the level of each method on that object.

These are the main tasks and concepts on Security Module.

Thanks for visit us!