OBIEE: Call SAPurgeCacheByDatabase So the reason I am investigating the ODBC Function extensions is that we are trying to tie this into our existing ETL process. After load, and within a designated time frame, we'd like to clear the cache for a given set of tables. Fortunately, all those tables exist in a single database. Unfortunately, I'm wasting your time by putting these into separate posts. :)

First, nothing up my sleeve.



So you understand the mapping, I made a pretty picture for that (it's not readily available in the cache manager)



I go off and run the reports (no need for screenshots of those).

Refresh the cache manager



So now I have cache entries for both "databases" (in quotes because they are actually the same physical database, just 2 separate connections).

I then create the SQL file and put the following:

CALL SAPurgeCacheByDatabase( 'local-sh' );

Then I just follow the same procedure as SAPurgeCacheAll()

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s sapurgecachebydatabase.sql

The output is almost identical as well

-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

CALL SAPurgeCacheByDatabase( 'local-sh' )
CALL SAPurgeCacheByDatabase( 'local-sh' )
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
RESULT_CODE  RESULT_MESSAGE

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
1            [59118] Operation SAPurgeCacheByDatabase succeeded!

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
Row count: 1
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------

Processed: 1 queries

And voila! The Sales cache has been cleared.

Labels: howto, obiee, tools

  ¶ 2/22/2010 10:08:00 PM   0 Comments Links to this post

  OBIEE: Call SAPurgeAllCache() Just a note to myself on how to clear the entire BI Server cache.

Mr. Minkjan has a great article here and Gerard Nico has one here.

Caching has been enabled (via <BI_SERVER_HOME>\server\Config\NQSConfig.ini)

For a list of ODBC Function extensions, read here.

First up, I run a simple report and then verify that there is an entry in the cache



I then create a small SQL script and put it in my c:\ folder:



(OK, just ignore what it says in the Title bar, I moved it)

Open up a cmd prompt and issue the following:

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s purgeallcache.sql

Hit enter and you should see the following:

-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

CALL SAPurgeAllCache()

CALL SAPurgeAllCache()

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
RESULT_CODE  RESULT_MESSAGE

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
1            [59118] Operation SAPurgeAllCache succeeded!

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
Row count: 1
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------

Processed: 1 queries

Check the Cache Manager and voila!



For future reference, here are the options available for the nqcmd utility:

Command: nqcmd - a command line client which can issue SQL statements
                 against either Oracle BI server or a variety
                 of ODBC compliant backend databases.
SYNOPSIS
         nqcmd [OPTION]...
DESCRIPTION
         -d
         -u
         -p
         -s
         -o

         -D
         -C<# number of fetched rows by column-wise binding>
         -R<# number of fetched rows by row-wise binding>
         -a (a flag to enable async processing)
         -f (a flag to enable to flush output file for each write)
         -H (a flag to enable to open/close a request handle for each query)
         -z (a flag to enable UTF8 instead of ACP)
         -utf16 (a flag to enable UTF16 instead of ACP)
         -q (a flag to turn off row output)
         -NoFetch (a flag to disable data fetch with query execution)
         -NotForwardCursor (a flag to disable forwardonly cursor)
         -SessionVar =

Labels: howto, obiee, tools

  ¶ 2/22/2010 11:58:00 AM   0 Comments Links to this post

  SQL Developer: Drill Down Reports Finally, finally I've figured this out. I've googled "SQL Developer Drillable Reports" to no avail. The solution kept alluding me.

The first result you should get back is one from a fellow Tampan (Tampon?), Lewis Cunningham, from July 2006. OK, it's a bit old (I think it was still called Raptor back then), but I'll give it a try.

In it, Lewis talks about creating additional "pseudo" columns, SDEV_LINK_NAME, SDEV_LINK_OWNER, SDEV_LINK_OBJECT which appear to map to the corresponding columns in DBA_OBJECTS.



I tried that, and got...nothing. I tried changing the alias(es) to match the column I was using, again, to no avail.

Let me back up just a tad, I'm trying to create some reports based on the PLSQL_PROFILER_% tables:

* PLSQL_PROFILER_RUNS
* PLSQL_PROFILER_UNITS
* PLSQL_PROFILER_DATA

It's annoying to have to rewrite the SQL everytime. I did create a @profile script, but I had to pass the RUNID; so first, I had to know the RUNID.

So I took to Twitter as I know Kris Rice hangs out there sometimes.







That was last week, and I have been unable to get this to work. I could have sworn Kris had a good tutorial on it, but I think I confused it with the extensions you can create.

Anyway, I'm at it again tonight and I end up back at the link Kris originally pointed me to. For some reason (cough) I missed this crucial little nugget this first time

(the bind variable is case-sensitive)

Really? Could it be that easy? I UPPERed RUNID and voila! It worked!

To recap, go to the Reports tab, right click on a folder (I have one named "profiler") and select Add Report.



I fill out the Name, Description and Tooltip (optional)



Hit Apply which saves my report. Now I want a report that on PLSQL_PROFILER_UNITS that accepts the RUNID as an IN parameter.

First, create the report:



Go to the Binds tab and fill in the fields



Go to the Advanced tab and fill in the name of the report



Now, select your first report, right click, go to Reports and select the report you just created





Perfect!

Just a small reminder, the bind parameters are CASE SENSITIVE!

Labels: reports, sql, sql developer, tools

  ¶ 9/09/2009 09:49:00 PM   4 Comments Links to this post

  COLLABORATE 09: SwingBench Session: Q352
Presenter: Debra Scarpelli
Time: 12:15-12:45
Room: 224 G
Title: A "Stress-Free" Free Stress Testing Tool From Oracle

I saw this on the schedule "free" jumped out at me. I don't believe the schedule made any mention of the tool, but I could be wrong.

Swingbench

I've read or seen about this somewhere but never got around to investigating it.

For those that don't know, it's a level load testing tool for the Oracle database. It was written by Dominic Giles (of Oracle). It has been used by Oracle internally for a time that I am unable to determine. You can find the page for it here. This tool is free but it is not supported by Oracle.

Specifics
- Currently works against 9i, 10g and 11g
- Built in Java and works on Unix, Linux and Windows
- Can use against RAC, TimesTen and Stand-alone databases.
- Three interfaces, command line (charbench), a mini GUI (minibench) and SwingBench (full fledged GUI)
  - SwingBench interface provides real-time feedback

There are 4 supplied (pre-configured) benchmark tests:
- Calling Circle with a read/write ratio of 70/30
- Order Entry with a read/write ratio of 60/40
- Stress Test with a read/write ratio of 50/50
- Sales History with a read/write ratio of 100/0

You can also write your own.

To run it you need to have Java (not sure which version) and the Oracle client installed on your machine. The output is xml so you can do pretty much anything you want with that.

There are some other tools that you can use in conjunction which you can find on Dominic Giles site.
- Data Generator
- BMCompare (yes, I snickered too) which compares the results of 2 benchmark tests.
- TraceAnalyzer

Pretty cool little tool. I'm definitely going to give it a try to see how it works and what it can teach me.

Labels: collaborate_09, tools

  ¶ 5/05/2009 12:35:00 PM   1 Comments Links to this post

  The Computer Setup I get to setup my new computer.

Here's a list of tools I'm installing:

SQLDeveloper, for those moments I wanted to be able to easily download data. Great database navigator as well. I create reports here and in JDeveloper as well.
JDeveloper, though I won't be using Subversion for work, I'm hoping there is a plugin for source control that I can use. Still my favorite editor of choice.
7zip
CubicExplorer, which is a tabbed windows explorer and fairly nice.
WinMerge, always nice to be able to easily compare files.
FastStone Capture - screen grab utility. You can also add arrows and stuff to your captured images.

Firefox was already installed so I imported my JSON file with my standard links (Oracle Documentation, Google Stuff, etc). I needed some FF Add-ons though:
Meebo, which I'm trying out for the first time.
ScribeFire, for the occasional quick post.
Shareaholic, for quickly sending links out.
Delicious, naturally.

Oracle Database was already installed along with BI Publisher, so I didn't have to worry about those. I'm sure I'll have others, but these are the basics.

Labels: tools, work

  ¶ 4/10/2009 03:00:00 PM   6 Comments Links to this post

  Scribefire

Labels: tools

  ¶ 3/19/2009 04:16:00 PM   5 Comments Links to this post

  JDeveloper, SQL Developer, Subversion and Tortoise I've been trying for a month now to get JDeveloper, SQL Developer, Tortoise and Subversion to play nicely together. They don't really fight, but it seems JDev and SQLDev won't recognize versioned files if I check them out using Tortoise.

Very annoying because I like both tools.

So I went searching and found this document (online) in the help files. So I tried to put that DLL in the Tortoise bin directory, but of course that wouldn't work because they've renamed everything with a leading "T."

So I downloaded the actual SVN client from Subversion. Then put the dll in that directory...now I open up SQLDev and get this seay32.dll (or something like that) missing. Awesome.

Back to searching and I found this one. user594768 has only 2 posts but this one did the trick.

First, download the 1.2.0 SVNKit from here.

Then, do the following:
copy
- jna.jar
- svnkit-javahl.jar (rename into svnjavahl.jar)
- svnkit.jar
- svnkitsrc.zip
- trilead.jar

into your /sqldeveloper/jdev/extensions/oracle.jdeveloper.subversion/ directory. If you rename svnkit-javahl.jar correctly the first time, you won't have to suffer like me for another week.

Start SQL Developer and go to Tools --> Preferences --> Versioning and it should read:
Subversion Client
SVN/1.5.2 SVNKit/1.2.0 (http://svnkit.com/) r4949

This works for both JDeveloper and SQL Developer.

Labels: tools

  ¶ 10/21/2008 06:17:00 PM   15 Comments Links to this post

  EDI Fun or Electronic Data Interchange, just a fancy phrase for sending and receiving files. We (IT) do love to complicate things don't we?

I've put the change data capture stuff on hold as my never-ending project goes into it's 12th week past deadline. It's at nine 9s: 99.9999999. It's finance related stuff and nothing less than 100% is acceptable. I'm tired.

Part of my project was to move from an already built in house table to the raw (files) tables. My feisty colleague took on that fun challenge for 6 months or so. He's heading up a new project though so he's had to pass the baton on to me. I've accepted it...reluctantly. ;)

Anyway, we store these inbound files all over the place (seemingly to me). I started writing a little Java application that would scan a directory, read these x12-820 files and tell me the interchange date, control number, total amount and some other useful information.

I plan on either putting this in the database and wrapping it up in PL/SQL or creating a service (Java Service Wrapper) and pushing this useful data to a table. So if you have to deal with the wonderful x12/820 formats, you may want to check back soon for the code. I can't promise it will be good, but it will work!

Labels: edi, java, plsql, tools, utilities, work

  ¶ 2/27/2008 08:25:00 PM   0 Comments Links to this post

  Instrumentation: DEBUG/LOGGING In my previous entry on instrumenting code I detailed the way in which you could use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to help monitor your long running code.

In this one, I will detail my home grown version of Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.

You can find the source code here.

Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file

debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.

Here's an example of it in practice:

CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
  l_age_in_months INTEGER;
BEGIN
  --instrumentation calls
  debug( 'GET_AGE_IN_MONTHS' );
  debug( 'P_ID:  ' || p_id );
  debug( 'select value into variable' );

  SELECT age_in_months
  INTO l_age_in_months
  FROM people_tab
  WHERE id = p_id;

  debug( 'L_AGE_IN_MONTHS:  ' || l_age_in_months );

  RETURN l_age_in_months;

EXCEPTION
  WHEN no_data_found THEN
    debug( 'no data found' );
    RETURN NULL;
END get_age_in_months;
/
show errors

I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.

This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.

I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.

On large systems this will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.

It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.

Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!

Labels: code, development, howto, instrumentation, plsql, tools, utilities, work

  ¶ 12/05/2007 09:03:00 PM   1 Comments Links to this post

  Code Style Having a specific coding style has become more apparent to me now that I work in a team environment. I have my way, which is of course the best, and everybody else has there way, which isn't nearly as good.

I'm sure if I could make the rules, I'd have everyone writing code according to my ways. Power corrupts and all that.

I have learned to accept others' ways though. It's been difficult at times. If the code is readable and it works, I usually just bite my tongue.

Strangely, I love to type. I manually create all of my scripts. I make my code "pretty." I don't use GUI tools to auto-generate table definitions, and I especially don't use tools to format my code.

Toad Formatter, or whatever it's called, is my enemy. I'm sure Toad is not the only one with some sort of auto-formatter and I don't like any of them. For some reason I believe it is an abomination.

When I get to do the technical interview, I scan their resume for SQL*Plus. If I don't see it, I ask them what tools they use. If they don't mention SQL*Plus, I'm skeptical of their abilities...until I get to the five constraints question.

This is not to say that people that use Toad or SQLDeveloper are bad coders or anything, it's just my preference.

So what's your preference?

Labels: tools, work

  ¶ 9/13/2007 01:13:00 AM   6 Comments Links to this post

  Oracle Tools I've Used Tools:
SQL*Plus
APEX - Application Express (formerly HTMLDB)
JDeveloper
SQL Developer
Reports Builder
OC4J
Discoverer
Oracle Server 8i, 9i, 10g (Windows)
Oracle Application Server 9i, 10g (Windows)
rman
oradim
lsnrctl
tkprof

Coming Soon:
Oracle Warehouse Builder

Database Features:
Java
Object Types
Workflow
Advanced Queueing (in conjunction with Workflow)
Heterogenous Services

Features I'd like to use:
Change Data Capture
XML
Spatial
interMedia
Regular Expressions

Database Supplied Packages:
DBMS_OUTPUT
DBMS_LOB
UTL_FILE
DBMS_APPLICATION_INFO
DBMS_CRYPTO
DBMS_UTILITY
DBMS_METADATA
DBMS_EPG
DBMS_HS_PASSTHROUGH
DBMS_JAVA
DBMS_JOB
DBMS_LOCK
DBMS_MVIEW
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SESSION
DBMS_SQL
DBMS_STATS
DBMS_XDB
DBMS_XPLAN
HTMLDB_APPLICATION
HTMLDB_ITEM
HTMLDB_UTIL
HTP
OWA_COOKIE
UTL_MAIL
UTL_RAW
UTL_SMTP
WPG_DOCLOAD

Packages I'd like learn to use (10g):
UTL_DBWS
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_DATA_MINING
DBMS_DATAPUMP
DBMS_SCHEDULER
DBMS_OLAP
DBMS_PIPE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MESSAGING

Labels: database, oracle, tools, utilities

  ¶ 9/10/2007 12:12:00 AM   0 Comments Links to this post