ODI Performance Tuning - Array fetch and Batch update size parameters

There are two parameters in Oracle Data Integrator topology that can be tweaked to improve performance of loading. These two parameters are, 

• Array Fetch size 
• Batch Update size

The Array Fetch size parameter can be tweaked for the source data server. This defines the number of records read from the source at a time and this data is stored in the agent. 

The Batch Update size parameter may be tweaked for the target data server. This defines the number of records fetched in one batch to be written to be server stored in the agent. 

When you increase number of records per batch, the time taken to read and write the total records will be less. This would mean you have lesser batches in total, hence improving performance. Since more data is stored in RAM, a good RAM size would be a necessity. 

Oracle recommends the following, 

• For good networks with little latency values less than 30 can be kept. However with a poor network the parameters have to be set as greater than 100. 
• The typical values as per oracle is between 50 and 500. 

Recommendation from Oracle ODI Performance tuning documentation. 

Here is a very interesting read on how this parameter if optimally set improves performance. 

BI Publisher Excel Template Output Date Format Error - 0-Jan-00 [Solved]

Issue: We were facing an issue where a date column in an excel output was shown as 0-JAN-00 instead of the original date. 

Cause: The reason is that when you create the excel(xls) BI Publisher template you have given the corresponding cell format as a date. Now in our case we had to convert the date to a TO CHAR format as MM/DD/YYYY. This meant that excel was not able to identify this as a date and hence defaulted it to 00-Jan-00

Resolution: You can resolve the issue by changing the type of the cell to 'General' from the the date format by going to format cells(right click).

Try exporting excel output again and check. Let us know if this did not solve your issue. 

Read more on dates and Oracle BI Publisher at the Oracle blog. 

Significance of nohup command for Weblogic/OBIEE in Unix/Linux ?

So you must be knowing that you can start an OBIEE server on linux with the following two commands, 

• ./start.sh

• nohup ./start.sh > start_1.log

So why use the nohup command when you can directly run the shell script without it. The basic difference between the two is that when you run a shell script with nohup, even after logging out from the terminal it runs in the background. This is not the case with direct execution, where when you close the terminal the process is also aborted. 

For an example, if we startNodeManager.sh file with direct  execute command, you will be able to see the log of how it is starting up. The nodemanager will eventually startup. The problem with this is that when you close the terminal, the nodemanager will go down. Inorder to avoid this what you can do is to run the shell script with nohup command. 

With nohup command the linux process is not aborted when the terminal is closed. So when you run nodemanager with a nohup appended command. It tells the server to run the process in background.  

There is two ways to write the nohup command for executing scripts.

• nohup ./start.sh

The above command will create a file named nohup.out in the same folder where the logs of the execution will be written to. 

• nohup ./start.sh > start_29112016.log

The above command writes to a file named start_29112016.log . 

I am new to linux, so any suggestions are welcome. Please feel free to write your comments below. 

[Solved] BI Publisher 12c - oracle.xdo.memoryguard.XDODataSizeLimitException - Report data size exceeds the maximum limit

Issue: oracle.xdo.memoryguard.XDODataSizeLimitException: Report data size exceeds the maximum limit.

Cause: The report was having a large number of records. Which exceeded the 300 MB default data size limit for BI Publisher. In-order to overcome this we required to change the memory guard limit in BI Publisher Runtime configuration.

Resolution:

• Log in to Oracle BI Publisher - http://hostname:9502/xmlpserver/
• Go to Administration > Runtime Configuration > Memory Guard

And Administration > Runtime Configuration > Data Model

Note: The values should be in bytes and not in Megabytes.If you want to give in MB or GB make the parameter values as 2GBb instead of 2 GB etc. Remember to remove the space in this case. 

• Here increase the size for maximum report data size from default to increased value as seen in the above screenshot.

Change/Increase the parameters

1. Maximum Report Data Size for online reports  
2. Maximum report data size for offline (scheduled) reports 
3. Maximum data size limit for data generation 

• Apply the changes. 
• Wait for confirmation that configuration has been changed successfully. 
• Run the report again to see whether the changes have been reflected. 

courtesy: Oracle Blog

Print Tick Mark in Oracle SQL Query [Solved]

Issue: Client wanted a column to have a condition in a way that if it is a 'Y' flag then a tick mark should be shown. This was a flat file report.

Resolution: You can't exactly call this a resolution but here is what you can do, As a workaround we searched for a character that matched, we found that in the Western Europe (DOS/OS2-437/US) character set. The 251th character is a symbol that resembles a tick mark.

SELECT CHR(251) FROM DUAL 

You will have to check in a DOS/OS2-437/US characterset database to find an exact match. This character will not be available in the most commonly used WE8ISO8859P1 and AL32UTF8 character sets.

Since our report was based on a case condition we wrote it inside a case statement,

SELECT CASE WHEN 1=1 THEN '√' ELSE NULL END

Another alternative is to use the symbol in query below,

SELECT '✓' FROM DUAL

We wrote the case statement and output data to flat file. The change reflected in Flat File as well. This is a work around, make sure the workaround is properly committed to client to avoid last minute issues.


Note: You will not be able to insert the data to a table with character set other than DOS/OS2-437/US.

Kindly comment if you have any other work-around which would work better.