IBM InfoSphere Metadata Asset Manager Timeout Error

Hi ,

Recently two of our clients had same problem, time out error.

When we try to import metadata of databases that we will investigate about personel data GDPR vise , we couldn’t import any tables.

On a SQL Server via ODBC Connector ;

CDIMR3801E Error Occurred when attempting to browse bridge or connector data CDIMR007E. There was an error querying all tables in the <SCHEMA_NAME> schema.

On an Oracle 12C db via Oracle Native Connector.

CDIMR3790E An error occurred while importing data from bridge or connector(CAS/OracleConnector__11).

The solution is the same.

If you use a Windows server for IBM Information Server go to the installation path. Check if timeout parameter set or not. Then you set time out parameter , in this example I set it to 5 minutes which is 300 seconds. But if you import thousands of data you can set it 50 minutes.

C:\IBM\InformationServer\ASBServer\bin>iisAdmin.bat -display -key cas.agent.timeout

C:\IBM\InformationServer\ASBServer\bin>iisAdmin.bat -set -key cas.agent.timeout -value 300

C:\IBM\InformationServer\ASBServer\bin>iisAdmin.bat -display -key cas.agent.timeout

Be careful when you set parameters , I accidently delete once iisAdmin.bat file. If you do that you need to find same file from other installed information server instances.

for more information;

Finding a table in ODI logs

ODI is the one of the most common ETL tool , especially in Turkey. It is very user friendly , easy to learn , tons of docs at web and there are many other advantages.

Some times when we make a technical analysis about our ETL processes. And we need to find an ODI scenario that includes our table as a source or as a target. We can check our scenarios manually but when it is Oracle , there are always easier ways.

We can use this script for find which scenario log has our tables name in it.

SELECT sess_beg, sess_name 
FROM snp_session 
where 1=1
--AND to_char(sess_beg,'YYYYMMDD')='20191101'
  AND sess_beg >TRUNC(SYSDATE-2)
  AND sess_no in (select distinct sess_no from SNP_SESS_TASK_LOG 
 where upper(def_txt) like '%<<YOUR_TABLE_NAME>>%') 
ORDER BY sess_beg ;

There is one little rule for using this script successfully , scenario must run at least one time. That is a must , because we are using the log that generated by ODI. So it must be generated.

You can download ODI demo VM that link below ;

Stored Procedure: Add Partition

Partitioning ?

In DataWarehouses and OLTP systems we use partitions for keeping data separately. In this way when we run a query database engine doesn’t need to scan all the table , it just accesses the partition that needs.

Oracle defines its partitioning like this ;

Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to data warehouses.

Sometimes we need create new partitions for loading data to a table. When we do this we use this stored procedure ;

CREATE PROCEDURE sp_add_partition 
(p_table_owner varchar2,p_table_name varchar2,p_start_date number, p_end_date number,p_partition_name VARCHAR2 default NULL) 
    v_high_value number;
    v_YEAR_MONTH varchar2(6);
    v_table_name varchar(100);
    CURSOR c1
      DISTINCT  to_char(DAY_ID,'YYYYMM')          YEAR_MONTH ,
      to_number(to_char(DAY_ID,'YYYYMM') ||'99')  high_value
		where to_number ( to_char(DAY_ID ,'YYYYMMDD' ))    between p_start_date and p_end_date
		order by 1;
    OPEN c1;
        FETCH c1 INTO v_YEAR_MONTH , v_high_value;
          execute immediate ' select max(Table_Name) from dba_tables 
                              where owner = UPPER('''||p_table_owner||''') 
                              and table_name = UPPER('''||p_table_name||''')' into v_table_name;
            execute immediate 
                'ALTER TABLE '||p_table_owner||'.'||v_table_name||'
                ADD PARTITION '||nvl(p_partition_name,v_table_name)||'_'||v_YEAR_MONTH||' 
                VALUES LESS THAN ('||v_high_value||')
                STORAGE    (
                INITIAL          64K
                NEXT             50M
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
      END LOOP;
    CLOSE c1;
  END  ;

For get more information about partitioning you can check links below.