ODI Object Queries

Following query prints mapping folder path:

set serveroutput on

declare
mapping_name varchar2(200):='MAPPING_NAME';
folder_id int;
parent_folder_id int;
folder_name varchar(200);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Mapping: '||mapping_name);
select i_folder into folder_id from SNP_MAPPING where name=mapping_name;
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=folder_id;
while parent_folder_id is not null
loop
dbms_output.put_line('Folder: '||folder_name);
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=parent_folder_id;
end loop;
dbms_output.put_line('Folder: '||folder_name);
end;


Following query prints package folder path:


set serveroutput on
declare
pck_name varchar2(200):='PACKAGE_NAME';
folder_id int;
parent_folder_id int;
folder_name varchar(200);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Pck: '||pck_name);
select i_folder into folder_id from SNP_PACKAGE where pack_name=pck_name;
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=folder_id;
while parent_folder_id is not null
loop
dbms_output.put_line('Folder: '||folder_name);
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=parent_folder_id;
end loop;
dbms_output.put_line('Folder: '||folder_name);
end;

ODI 10g: ORA-00001: unique constraint (PK_EXP_TXT) violated

There are 2 type of logging in SNP_EXP_TXT table. First type has the I_TXT column value ending with "101" and second type has value ending with "010". Two types have their own incremental sequences. First type is for persistent definitions. There are not many rows with this type so this type of rows will not cause trouble. However second type(ending with "010") is used for logging and history text. There may be a lot of rows in this type. When I_TXT value reaches "9999999010" (actually 9999999, ignore last 3 digits) it cycles back to "1010", then this problem may occur if old "010" records have not been purged.


following query will print 9999999010 as result:


select max(I_TXT) from SNP_EXP_TXT where mod(I_TXT,1000)=10


following query prints how many rows each type has:


select mod(I_TXT,1000), count(*) from SNP_EXP_TXT group by mod(I_TXT,1000) order by 1


Let say the row with the I_TXT has first_date value X, then following query will print the value until with cycled sequence will go until "ORA-00001: unique constraint (PK_EXP_TXT) violated " occurs:


select min(I_TXT) from SNP_EXP_TXT where mod(I_TXT,1000)=10 and first_date < X


And to find out last row:


select max(I_TXT) from SNP_EXP_TXT where mod(I_TXT,1000)=10 and first_date > X


Following query prints current sequence number:


select * from SNP_ID where id_tbl= upper('snp_exp_txt')


Here it is explained that there is a “bug” in Oracle Data Integrator that doesn’t clean the SNP_EXP_TXT when the “parent” records are deleted. Following query can be used to find useless recods (i added time and type conditions):


select * from snp_exp_txt where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10
 and not exists
          (select 0
             from (     select distinct I_TXT_TASK_MESS I_TXT
                          from SNP_SESS_TASK_LOG
                   union all
                        select distinct I_TXT_SCEN I_TXT
                          from SNP_SCEN
                   union all
                        select distinct I_TXT_SESS_MESS I_TXT
                          from SNP_SCEN_REPORT
                   union all
                        select distinct I_TXT_DESCRIPTION I_TXT
                          from SNP_SCEN_FOLDER
                   union all
                        select distinct I_TXT_SESS_MESS I_TXT
                          from SNP_SESSION
                   union all
                       select distinct I_TXT_SESS_PARAMS I_TXT
                         from SNP_SESSION 
                   union all
                       select distinct I_TXT_STEP_MESS I_TXT
                         from SNP_STEP_REPORT
                   union all
                        select distinct I_TXT_STEP_MESS I_TXT
                          from SNP_STEP_LOG
                   union all
                        select distinct I_TXT_VAR I_TXT
                          from SNP_VAR_SESS
                   union all
                        select distinct I_TXT_DEF_T I_TXT
                          from SNP_VAR_SESS
                   union all
                        select distinct I_TXT_VAR I_TXT
                          from SNP_VAR_SCEN
                   union all
                         select distinct I_TXT_DEF_T I_TXT
                           from SNP_VAR_SCEN
                   union all
                         select DISTINCT I_TXT as I_TXT     
                          FROM SNP_TXT
                   union all
                         select DISTINCT I_TXT_VAR_IN as I_TXT
                          FROM SNP_VAR
                   union all
                         select DISTINCT I_TXT_VAR_T as I_TXT
                          FROM SNP_VAR_DATA
                  ) UNION_TABLES
              where UNION_TABLES.I_TXT = snp_exp_txt.I_TXT)


You can delete these records (don't forget to take a backup). However, also there may be records which have references. To find out these you can execute previous query with "exists" instead of "not exists". If so look at the following tables to find corresponding records:


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_SESS_MESS from snp_session)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_SESS_PARAMS from snp_session)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_VAR from snp_var_sess)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_DEF_T from snp_var_sess)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_VAR_T from SNP_VAR_DATA)


Sum of all records from these queries should be equal to the number of records returned from the query with unions executed with "exists " instead of "not exists". If not look further in repository tables...


All of them except SNP_VAR_DATA should have no effect on scenario execution. However SNP_VAR_DATA is important because if a variable reused, it takes last value from SNP_EXP_TXT history record. If you delete this, variable cannot get its last value.


In order to find out which variables has history in our range and what are their last values:


select VAR_NAME, max(last_date) from SNP_VAR_DATA where VAR_NAME in (
select VAR_NAME from SNP_VAR_DATA where I_TXT_VAR_T in (select I_TXT from snp_exp_txt where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10))
group by VAR_NAME order by 2 desc


Variables which have history in our range but refreshed recently should not be effected by deleting the rows in SNP_EXP_TXT within our time range. However if variable has not been refreshed recently (max(last_date) is within our time range) then these variable should be refreshed manually.


Then it is safe to delete referenced rows from SNP_EXP_TXT table. History records will print null for these expressions (do not forget to tale backup)


delete from snp_exp_txt where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10