artrange data ltd

OFA tips

This page lists some of the Oracle Financial Analyzer code we make use of. Some scripts are pure Express, some deal with getting Express information out to an external (usually Unix) process for further action, some are more GL related and therefore SQL based.

status of Express database objects

The following commands report the size in GB of all database objects using more than 10000 pages of disk space plus the number of segments used for their storage. Adjust the disk size gt limit if you want to see larger or smaller objects.

limit name to all
limit name to obj(disksize name) gt 10000
sort name a obj(disksize name)
show tod
rpr heading 'Type' obj(type name) heading 'Size GB' obj(disksize name)*database(pagesize)/(1024*1024*1024) heading 'Segments' obj(numsegs name)heading 'Dimmax' obj(dimmax name)

emailing express output under Unix

This command emails  the output from an express session to a number of users, we use this script to email information about the Express database (object sizes, free space etc) to a number of administrators on a regular basis using cron.

The setup_olap scriptjust sets the environment variables for Express usage. go_express just executes oescmd -b -portno for the database we wish to attach to.

The script uses a Unix here file to present input to the oescmd session, and pipes the output from that session into mailx. You must ensure mailx is properly configured on your machine to route email to the recipients. The easiest way to check this is to type the command date | mailx -s Test me@testhost

.~/setup_olap

mail_recips=me@some.co.uk,someoneelse@some.co.uk
~/bin/go_express <<CHECKSIZES | mailx -s "UK Ofas Objects" $mail_recips
dtb attach /d05/ukofa6/ukreport/shared/ofas.db
limit name to all
limit name to obj(disksize name) gt 10000
sort name a obj(disksize name)
show tod
rpr heading 'Type' obj(type name) heading 'Size GB' obj(disksize name)*database(pagesize)/(1024*1024*1024) heading 'Segments' obj(numsegs name)
CHECKSIZES

reporting on GL_BUDGET_INTERFACE

The following script can be used to interrogate the records in the gl_budget_interface. If run before the GL load it gives a record of the budget lines being written back, if run after the budgets have been loaded then it shows the rejections from GL.

set echo off
set verify off
set pages 60
set tab off
column budget_name format a15
column segment1 format a3 heading Cpy
column segment2 format a5 heading Accnt
column segment1 format a3 heading Cpy
column tot_amount format 99,999,999,999 heading Amount
column abs_amount format 99,999,999,999 heading "Abs Amount"
column num_rows format 99,999 heading "Rowcount"
column num_values format 99,999 heading "Values"
break on budget_name skip 2 nodup on segment1 nodup skip 1
compute sum of abs_amount tot_amount num_rows num_values on segment1
compute sum of abs_amount tot_amount num_rows num_values on report
compute sum of abs_amount tot_amount num_rows num_values on budget_name
select budget_name,
       segment1,
       segment2,
       sum(nvl(period1_amount,0)+
           nvl(period2_amount,0)+
           nvl(period3_amount,0)+
           nvl(period4_amount,0)+
           nvl(period5_amount,0)+
           nvl(period6_amount,0)+
           nvl(period7_amount,0)+
           nvl(period8_amount,0)+
           nvl(period9_amount,0)+
           nvl(period10_amount,0)+
           nvl(period11_amount,0)+
           nvl(period12_amount,0) tot_amount,
       sum(abs(nvl(period1_amount,0))+
           abs(nvl(period2_amount,0))+
           abs(nvl(period3_amount,0))+
           abs(nvl(period4_amount,0))+
           abs(nvl(period5_amount,0))+
           abs(nvl(period6_amount,0))+
           abs(nvl(period7_amount,0))+
           abs(nvl(period8_amount,0))+
           abs(nvl(period9_amount,0))+
           abs(nvl(period10_amount,0))+
           abs(nvl(period11_amount,0))+
           abs(nvl(period12_amount,0))) abs_amount,
       count(rowid) num_rows,
       sum(decode(abs(nvl(period1_amount,0))+
           abs(nvl(period2_amount,0))+
           abs(nvl(period3_amount,0))+
           abs(nvl(period4_amount,0))+
           abs(nvl(period5_amount,0))+
           abs(nvl(period6_amount,0))+
           abs(nvl(period7_amount,0))+
           abs(nvl(period8_amount,0))+
           abs(nvl(period9_amount,0))+
           abs(nvl(period10_amount,0))+
           abs(nvl(period11_amount,0))+
           abs(nvl(period12_amount,0)),0,0,1)) num_values
from   gl_budget_interface
group by budget_name, segment1, segment2
/

The report shows the first two account segments (in this case company and account) and a sum of the row values, a sum of the absolute row values, a count of rows and a count of the rows holding real numbers (as opposed to zeros which will not create a GL reconciliation issue).

It is simple to amend the report to show other segments, or to embed this statement in a smart report which can show whatever segments you choose at runtime.

redirecting daemon temporary files

The OFA daemons xsauthz and xsauthn use named pipes which by default are created in the /tmp or /var/tmp directory. Many sites have scripts to delete files from this directory which have not been written to in a set amount of time (often just 24 hours). If this happens the instance will in effect become unusable (error messages such as 'connect is failing - possibly exceeding the max.' will appear in the xsauth log file).

The placement of these named pipes can be changed by editing the express.sh file in your $ORACLE_HOME/olap/bin directory to modify the line which starts the xsdaemon process as follows:-

$OLAP_HOME/bin/xsdaemon -s /some/other/path/xsauthz \
                            -r /some/other/path/xsauthn



Site Map | Contact Us | ©1992-2007 artrange data ltd.