Reverse PostgreSQL into model with undefined data types

Recently while importing a new physical schema from PostgreSQL 8.4 into Oracle Data Integrator(ODI) via JDBC driver all of the tables were reversing into the model without data types defined for text, double precision, smallint, integer and boolean.

If one or two fields are missing it’s easy enough to set the data type manually for the column, but for a whole mess of tables and thousands of fields it’s unrealistic.

The solution is to define the proper data types using the physical architecture tab with PostgreSQL internal names for the data types. Even though by default there is one for integer, looking at the data type definitions smallint maps to int2, integer maps to int4, double precision maps to float8, boolean maps to bool.

Using these aliases which are used internally by PostgreSQL for historical reasons in the ODI Physical Architecture will fix the missing data types during the reverse process.

Read Full Post »

Scenarios listed in Operator view are same as Designer view

I’m currently using Oracle Data Integrator (ODI) 10.1.3 and I went to clean out the logs generated by the completed executions. I know there is a purge logs trash can icon, but I couldn’t do this because the ‘UNDO’ tablespace was full.

Since I was getting this error I had to remove the reports manually. Aside from removing the reports from the “Session List” tab I also went to remove them from the “Scenarios” tab. Thinking I would save time by removing the entire scenario’s worth of reports I deleted the root node (see image).

What I didn’t know is removing the generated scenario from the ODI Operator view would also remove it from the Designer view. All I had to do was go back and regenerate the scenario from Designer view, but if your installation had different scenarios for fall back purposes this could lead to trouble.

Read Full Post »

Error when purging log unable to extend tablespace ‘UNDO’

Oracle Data Integrator 10.1.3 is a great tool and can even run fully functional against Oracle Express which is the only free Oracle database product available.

Problem

This limited database has it’s draw backs for production use. If Data Integrator is executing scenarios real time, or if the logs are never cleared it could quickly exceed the 5GB storage limit and this nasty error could show up.

Error When Purging Log
java.sql.SQLException: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

Use Oracle APEX to find the maxed undo tablespace:

The problem is Oracle Express can’t extend the tablespace. Ultimately once the undo tablespace is full all reports need to be removed manually before a purge logs can be executed.

Solution

Sometimes purging the logs from the command line will work but not from the Operator view:

startcmd.bat OdiPurgeLog "-PURGE_REPORTS=1"

Otherwise manually remove all reports and then purge using the command line or trash icon. Hint selecting multiple and right clicking ‘delete’ reduces the selection to one item. Instead select multiple and press delete key.

If this doesn’t work delete the contents of the reports tables. I’d start with the SNP_EXP_TXT table because this could have 50million+ records.

Read Full Post »