TEC and Oracle

Tuning the event database

The event database configuration enables you to tune the database as follows:

• Physically separate the database tables and indexes across various devices so that more than one input/output device is servicing read and write (R/W) requests.
• Separate data tables from their corresponding index tables.
• Use buffer pools and prefetching techniques effectively to ensure that a specified row remains in memory instead of on disk.
• Tune vendor-specific database configuration parameters.
• Avoid disk contention by moving transaction logs and temporary work areas to their own disk device.

IBM - Oracle Database Migration

How can a TEC Oracle database be migrated from one system to another with minimum downtime and disruption to the business?

Answer Here are the recommended steps to accomplish the migration:

1) Create the new Oracle instance and ensure that the client can communicate to that instance
2) Create the tec user, database, and tables/views on this new instance,again ensure that you can communicate to the new instance with the tec login information.
3) You can either create a new RIM object for this or use the old RIM.

We suggest that you create a new RIM object here, calling it tecnew.

Here are the rim setting explanations:

Database Vendor : Oracle

Database Home : Equates to the value in the $ORACLE_HOME environment variable.

Database ID : The name of the database that the application will use.

Database User ID : The RDBMS user that will own the application database. The default is tec.

Database Password : The password for Database User ID.

Database Server ID : The name of the Oracle listener service in the tnsnames.ora file. Equates to the value in the $TWO_TASK environment variable for a client or $ORACLE_SID on the database server or client.

Instance Name : Not required, DB2 only.

After created, you should be able to test at least some of the functionality of the RIM with the wrimtest command to delete from tec_t_isa, add data to the table, then retrieve data. If you can do all of this then the TEC server should start if using the new rim object when we have completed the migration:

wrimtest -l tecnew
....
RIM : Enter Option >d
Delete) Enter Table Name > tec_t_isa
Delete) Enter Where Clause >

The above will delete from tec_t_isa

RIM : Enter Option >i
Table Name > tec_t_isa
Enter <Field Name> [/n] [/s /l /f /d
Editor? [y/n] [Default n] >n
1 > parent [test]
2 > child [data]
3 > <enter>
Where Clause > <enter>
The above will insert "test" and "data" into tec_t_isa

RIM : Enter Option >g
Table Name > tec_t_isa
Enter <Field Name> [/n] [/s /l /f /d
Editor? [y/n] [Default n] >n
1 > parent
2 > child
3 > <enter>
Where Clause > <enter>
Retrieve) Num of Rows [0] > <enter>
Row 0
parent : (o) [test]
child : (0) [data]
RIM : Enter Option >x
The above will retrieve the data from tec_t_isa...then "x" to exit.


4) Now stop the TEC server. Migrate your data to the new instance. Rename your old tec RIM object to tecold:

wsetrim -n tecold tec

Then rename your new RIM object to tec

wsetrim -n tecnew tec

Kill any remaining tec RIM object processes (might be better to just stop and restart oserv at this point, this will kill all RIM processes)

5) Just to say you did, check your RIM connection again:

wrimtest -l tec

If it lets you connect...then

6) Start tec.

7) Test to verify that events are in the database and can be seen with the consoles, wtdumprl and wtdumper commands, ...etc.

NEWS CONTENTS

• 20100519 : Knowledge Base - Tips - Tivoli - Enterprise Console - Perl DBI Quick Reference - Orb Data ( Knowledge Base - Tips - Tivoli - Enterprise Console - Perl DBI Quick Reference - Orb Data, May 19, 2010 )

Old News ;-)

[May 19, 2010] Knowledge Base - Tips - Tivoli - Enterprise Console - Perl DBI Quick Reference - Orb Data

More and more of Tivoli's applications are being based around Relational Databases. TEC and Inventory have always had a database as a key component, but newer applications like User Admin and even the Framework now rely upon a separate database as an information store.

While much of the information held in the database can be retrieved and viewed through the relevant Tivoli tools, there will always be a need to extract information from such repositories yourself.

Retrieving Data from a Database with Perl

Retrieving data from a database is not straight forward, usually because there is a whole load of baggage that is returned along with the data. The following is the output from a TEC Database (Oracle) from a query to count the number of records in a particular table:

,

sqlplus tec/tectec

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Mar 11 06:50:36 2002

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> select count(1) from tec_t_evt_rec_log;

  COUNT(1)
----------
       478

SQL> quit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

,

To simply get the data from this query (ie the value '478'), you have to do a vast amount of filtering. For example :

,

#!/bin/perl
$user = "tec";
$password = "tectec";
$sql = "select count(1) from tec_t_evt_rec_log; quit;";
open(SQL, "$sqlplus $user/$password <&1|");
while () {
	# Filter out stuff we don't want
	next if (/Oracle/);
	next if (/SQL Plus/);
	next if (/Connected to/);
	next if (/Partitioning/);
	next if (/JServer/);
	next if (/COUNT/);
	next if (/-----/);
	
	if (/s+(d+)/) {
		$count = $1;
		last;
	}
}
close (SQL);
print "Number of records = $count
";

,

Whilst this method works ok in performing a query against the database and returning the data, you will see that this method is a little messy. If you have many such queries to perform in a script, then the script can become rather unwieldy.

There is an alternative method however that makes retrieving data from a database far simpler and a little more elegant -Perl DBI.

Perl DBI

Perl DBI is a Perl module that provides a simple / standard interface between the Perl programming language and other data sources. Using Perl DBI and other database modules (DBD) one can access all kinds of databases including flat file, CSV file, Oracle, Sybase, DB2, SQLServer MSAccess etc etc.

This quick reference was written to provide enough information to be able to access, update (insert, delete and update) and query information from a database. It is a huge subject and O'Reilly have published an excellent book - 'Programming the Perl DBI' where you can get more indepth information. As a quick guide however, this reference guide will hopefully give you a good starting point.

Prerequisites

There are a number of prerequisites that need to be in place before you can start using DBI:

• Perl 5.x (I use the latest Perl 5.6.1)
• Perl DBI Module
• The relevant Perl DBD module eg DBD::Oracle
• Access to the data source - either (i) directly, with the Perl scripts being run off the same box as the database, or (ii) indirectly through some sort of access level (eg SQLNet, ODBC etc.)

Getting Perl DBI / DBD

The Perl DBD and DBI modules can be downloaded and installed onto your system using the Perl built in package manager PPM (on NT) and cpan (on Unix). The following instructions are for use with NT / Windows 2000.

To do this ensure you are using Perl 5.x. To do this from a shell prompt type

perl -v

If you are on Perl 4.x (as supplied by Tivoli) then you will also have to have Perl 5.x installed on your system. Once installed you'll either need to unsource the Tivoli environment or goto the Perl 5.x directory and run Perl from there, eg

c:Perlin

Then launch the Perl package manager: ppm

ppm
PPM interactive shell (2.1.5) - type 'help' for available commands.
PPM>

Search for the relevent modules, eg

PPM> search DBI

This should produce a list of the DBI releated modules. Choose the correct module and install it using the install command:

PPM> install DBI

This should download the correect code, install it and test the installation.

Once this is done, do the same for the relevent DBD module. There are loads of these so it's best to search for the database rather than just DBD ie :

PPM> search Oracle

Again once you've identified the module you need, install them eg

PPM> install DBD::Oracle8

Once this is complete, exit and you are ready to go.

Your first Script

The following sections are going to walk through a simple script that will access a local Oracle database (the TEC database) on an NT system, query some data, update it and then disconnect from the database.

In the first part of the script you have to import the DBI and DBD modules. In Perl 5 you do this using the use call. ie

use DBI;

It's also a good idea (especially when getting started) to use the strict module (use strict;). This just forces you to use a correct programmimg methodology, and ensures that you predeclare everything. While this can be a real pain for sloppy programmers, in the long run it is useful!

Connecting to the Database

To connect to the database you need to first provide a little information to DBI. This includes the dsn, user and password. The dsn provides information regarding the database, ie what type of database it is, and how it can be accessed - for Oracle this maps on to the SID. To perform the connection you use the callconnect. This returns a database handle back that you must always use. The database handles contains information about this session.

In the case of my local TEC database I could use the following code:

,

my ($dsn) = "DBI:oracle:tec";
my ($user) = "tec";
my ($password) = "tectec";
my ($dbh) = DBI->connect($dsn, $user, $password);

,

This code will connect you to the local TEC database. There are also a number of other useful options that you can give the connect call. The most useful include :

AutoCommit => 1	This automatically commits all database updates at the end of their calls, without you having to remember to provide the commit statement after each.
RaiseError => 1	Raises all database errors via a diecall

You can use these options in the connect statement

my ($dbh) = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 1});

Performing a Query

The most basic thing you're going to want to use Perl DBI for is extracting data from the database, using a sql statement. In this section we're going to retrieve various information from the TEC Event Repository. First of all we're going to count the number of events in the database, then we're going to get a list of all classes in the database.

To run a query using DBI there are three steps we have to take :- (i) prepare the query, (ii) execute the query and finally (iii) fetch the results from the query.

Preparing the Query

Preparing a query creates the query and assigns it a database query handle. You then use this handle to actually perform the query and get the data from the database. To prepare a query you perform the following:

my ($sth) = $dbh->prepare( 'select class, hostname, msg from tec_t_evt_rep' );

In the above the query we are going to run just retrieves the class, hostname and msg data from each record in the table tec_t_evt_rep (the event repository). The result of the $dbh->prepare is a statement handle ($sth). From this point on we use the statement handle to execute the query and access the results set of the query.

Executing a Query

Once the query is prepared, the query has to be executed against the database. Note that this step does not immediately return the results of the query to you - that is covered in a following step. To execute the query, perform the following:

$sth->execute();

Fetching Data

As we mentioned previously the execute statement only executes the SQL Query (in fact on very large tables it it may have only initiated the query). The result set is actually held in records that haven't yet been passed to your Perl program. To get at the result set you need to fetch the data that the query produced. There are three basic methods you can use for this, and each has it's own benefits. The methods you can use are:

fetchrow_array	This returns a list of values you can use directly
fetchrow_arrayref	This returns reference to an array
fetchrow_hashref	This returns a reference to a hash

Each of these methods return the data from a single row from the database. If you are expecting multiple rows of data in your result then you will have to put the statement in some kind of loop. It should be noted here that the data is being fetched from a cursor, and once each row of data has been retrieved the data is forgotten, so if you wish to use the data again later you need to store it in you own variables, arrays or hashes.

The simplest way to retrieve records is to use the fetchrow_array method. Using the query from the previous section, we will get multiple rows of data, therefore we will use the fetchrow_array in a while loop to print out all of the values. For example :

,

while (my ($class, $hostname, $msg) = $sth->fetchrow_array)
{
	printf("%20s %15s %s
", $class, $hostname, $msg);
}

As fetchrow->array produces a list, we could have retrieved the data into an array of our own - eg :

my (@data) = $sth->fetchrow_array;

Following this, we could then have accessed the data by referring to $array[0] through to $array[$#array].

A further point to remember is that when iterating through the rows, the result set is retrieved in the same order that it was retrieved from the database. This keeps the results consistent with the data (especially worth noting if you are grouping or ordering in your SQL statement).

Another method to retrieve the results set is to use the methodfetchrow_arrayref. This returns a reference to an array rather than the actual array itself. We could use the fetchrow_arrayref in our example as follows:

while  (my ($arrayRef) = $sth->fetchrow_arrayref)
{
	printf("%20s %15s %s
", $arrayRef->[0], $arrayRef->[1], $arrayRef->[2]);
}

This produces the same results as the example using fetchrow-array, but because the data remains in its original array, and is not copied to a new array, there is an efficiency and performance improvement.

Finally the result set can be retrieved into a hash array using fetchrow_hashref. Instead of producing a list (or reference to a list), this method produces a reference to a hash which is keyed on the column names (eg class, hostname and msg in the above example). Again using the previous example, our fetch statement could be re-written as follows:

while  (my ($hashRef) = $sth->fetchrow_hashref)
{
	printf("%20s %15s %s
", $hashRef->{class}, 
	      $hashRef->{hostname}, $hashRef->{msg});
}

The results could also be dereferenced by using $$hashRef{class} as well.

Finishing the Query

Finally, once you have retrieved all the data you need you have to de-allocate the statement handle. This is carried out via the finish method, ie :

$sth->finish();

Non-select queries

So far we have just talked about executing select queries, ie queries that retrieve data from the database. There are however other actions you will want to perform on the database (such as inserting and deleting rows of data) that do not return data. For these types of statements you can use the do method rather than preparing, executing and fetching via a statement handle. The following example deletes all records from the reception log that have a status of 4 (PARSING_FAILED) or a status of 2 (PROCESSED).

$rows = $dbh->do( "DELETE from tec_t_evt_rec_log 
                           WHERE status = 2 or status = 4" );
print "Deleted $rows from the reception log

The do method will perform the action straight away and will return the number of rows deleted. If the action completed succesfully (ie didn't fail due to an error) but did not delete any rows then the return code will be 0E0.

Remember that if you do not include the AutoCommit = > 1 when creating the connection to the database, the action you have just performed will only be visible to this session. Other sessions will still see the data as it was originally.

Disconnecting from the Database

Once you have completed your work on the database you have to diconnect from it. To do this you can use the disconnect method :

$dbh->disconnect;

Error Handling

Most DBI methods return true or false. This means that you can perform your own error checking on each method. The error from a method is held in the DBI variable called errstr which can be included in your error checking. For example, to print a message and stop processing when you cannot connect to the TEC database you could perform something along the following lines:

my ($dbh) = DBI->connect($dsn, $user, $password) 
	|| die "Cannot connect to database.  Reason $DBI::errstr
";

If we couldn't contact the database we would get the error message and the script would end.

This is all very well but in large scripts it can be a real pain adding all these entires to each DBI method call. To solve this, DBI can also employ automatic error checking that will either stop the script where the error occurred and produce an error message (using die), or will allow the program to continue but still send an error message out (using warn).

To switch on this automatic error checking you need to include the attributes PrintError (utilises warn) andRaiseError (utilises die).

To switch on automatic error warnings, you must connect and add the attributes as follows:

my ($dbh) = DBI->connect($dsn, $user, $password, 
		{ PrintError => 1, RaiseError => 0});

Within your script you can switch between automatic error routines from warn to die, just by reseting the value of the attributes. For instance in the following segment we want to create a new tec_log table, but drop it first if it already exists. If the table does not exist we want to send a warning out but continue, if we cannot create the table then we want to stop processing the script:

$dbh->{PrintError} = 1;
$dbh->do( "drop table TEC_LOG");
$dbh->{PrintError} = 0;
$dbh->{RaiseError} = 1;
$dbh->do( "CREATE TABLE TEC_LOG (
	date_reception 	VARCHAR2(12) NOT NULL,
	class		VARCHAR2(50) NOT NULL,
	msg		VARCHAR2(256) NULL,
	hostname 	VARCHAR2(50) NULL,
	status		INTEGER;
	severity 	INTEGER.
	date_entry	DATE NOT NULL
) " );

Example Script

To accompany this tip, I have created a very simple example script that extracts useful statistics from the TEC Database. The script perfoms a query against the TEC database every x seconds and writes the results to a specified logfile in CSV format. The query gets the following information:

• Number of WAITING events
• Number of PROCESSED events
• Number of QUEUED events
• Number of PARSING_FAILED events
• Total number of events in the reception log
• Number of OPEN events
• Number of ACK events
• Number of CLOSED events
• Total number of events in the event repository

The format of the file is not hugely readable in it's raw form, but this can be imported into Excel or the like to generate some trending

Etc

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotes :  Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce :  Bernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS :  Programming Languages History : PL/1 : Simula 67 : C : History of GCC development :  Scripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month :  How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Last modified: March 12, 2019