Tivoli Storage Manager – Informix ontape Backup-Restore Configuration

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 1:50 pm - 1,713 views

(No Ratings Yet)

Loading ...

If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Tivoli Storage Manager – Informix ontape Backup-Restore Configuration

Level: Intermediate
Amitava Chakraborty, IBM INDIA

Prerequisites

· Knowledge of Informix Installation along with Server and Database configuration.
· Basic UNIX commands depend upon the OS used.

TSM Server Installation:

Version: TSM Server 5.3.0 and above

In the base directory of CD mount or the un-tar directories of the TSM Server there will be a README file. In this file, the directory structures and the installable software information are there depending upon different OS and their versions.

Note: If older version of TSM already exists then remove the software first then install the new version. The removal commands are also specified in the README file.

Creating required setup at TSM-SRERVER :

Please follow the below mentioned steps as a sequence:
·   Go to TSM Server “bin” directory, there is a file called dsmserv.opt which is the configuration file for TSM server. Open the file in a text editor and search for the word “COMMTimeout”. Uncomment the line if it is commented and make the value of this property as 600, which is 10 minutes. This means a client will be connected for continuous 10 minutes while backup/restore. You may have to increase the number depending upon the size of the database and network speed and while you get an error in the administrative console as “Communication time out” while backup-restore. But the value 600 is a big enough value for at least 2 GB Dbspaces.
·   Go to TSM Server “bin” directory and start the TSM server by running the command < ./dsmserv> as user root , you will get a prompt like “TSM:SERVER1> “ where “SERVER1” is your TSM server name.
·   You can change the name of the server from “SERVER1” to the name you like by running the command
set servername <Your Server Name>
in the administrative console. After running the command immediately your
command prompt will change to “TSM: ’New server name’>”
·   Register the license by running the command in the server console.
Register license file= tsmbasic.lic number=10
Make sure the tsmbasic.lic file exists in the current dir otherwise give the full
Path.
·   Update the password for admin as your own password from the server console.
update admin admin <password >
·   Now create a logical space allocation for taking the backup by using this command at console.
Define stgpool <Logical Pool Name> disk access=readwrite
maxsize=500M
·   Now direct the server to store the data in this new logical pool when default backup storage space BACKUPPOOL is full by using this command.
Update stgpool BACKUPPOOL next stgpool=<Logical Pool Created>
·   Now create the physical storage space under the logical pool
define volume <name of the logical pool created>
<path with name , where the space will be created> access=readwrite
formatsize=500
·   Now make sure that the space has created successfully by running the command
Query stgpool
Query volume
·   Now register the TSM client machine by running the command
register node < client node name > < admin password for user admin >
domain=STANDARD
Now your server is setup for Informix-Ontape backup.

Note:You can find these server commands more in details in the link

http://publib.boulder.ibm.com/tividd/td/SMAS4N/GC35-0316-01/en_US/HTML/a454rf02.htm#ToC_335

TSM client and Informix should be installed in the same machine.

TSM Client Installation:

Version:

·   For aix32, aix64, hpux32, hpux64, linux32, solaris32, and solaris64 TSM Client 5.2.0 or later
·   For Windows 32 and zlinux64 TSM Client 5.2.2 or later
·   For ppc64 and Linux on x86_64 TSM Client 5.3.0 or later

In the base directory of CD mount or the un-tar directories of the TSM Client there will be a README file. In this file, the directory structures and the installable software information are there depending upon different OS and their versions. Under those directories you will get the required information to install the TSM Client. For Informix Back-up and Restore through ontape, TSM Backup & Achieve Client installation is required. No need to install the TSM API Client.

Informix Installation:

Please follow the Informix Installation guide to install the Informix on the required machine. Create an Informix Server and test databases on which the backup and restore will be performed.

Configuring the TSM Client for Ontape Backup-Restore:

Environment settings:
—————————————
Please ensure that the below settings are done at your environment:
INFORMIXDIR <Informix Dir>
PATH $INFORMIXDIR/bin:.:/usr/local/bin:/bin:/usr/sbin:$PATH
INFORMIXSERVER <Instance Name>
ONCONFIG <onconfig file>
INFORMIXSQLHOSTS $INFORMIXDIR/etc/<sqlhost file>
DBSERVERNAME <server name>
DSM_DIR <tsm client/ba/bin >
DSM_LOG /tmp/tsmlog
DSM_CONFIG $DSM_DIR/dsm.opt #< You may have to copy the dsm.opt.smp to dsm.opt (if not exists) >
PATH <tsm client/ba/bin>:$PATH

TERMCAP <Informix Dir>/etc/termcap

Parameters settings for Informix onconfig file:

# System Archive Tape Device

TAPEDEV         <Informix Chunk Path >/tapedev # Tape device path
TAPEBLK         16              # Tape block size (Kbytes)
TAPESIZE        <Value of LOGSIZE>           # Maximum amount of data to put on tape (Kbytes)

# Log Archive Tape Device

LTAPEDEV        <Informix Chunk Path >/ltapedev # Log tape device path
LTAPEBLK        16              # Log tape block size (Kbytes)
LTAPESIZE       <Value of LOGSIZE>          # Max amount of data to put on log tape (Kbytes)

Note: Please do not mention the same name for both tape device.

After making these changes you have to bounce the Informix server to take effect of these new settings.

Parameters settings for DSM_CONFIG file:

servername <TSM server name>

Now in the $DSM_DIR search for file named dsm.sys. If not exists copy the dsm.sys.smp to dsm.sys

Now change the dsm.sys

SErvername <TSM server name>
COMMMethod         TCPip <do not change>
TCPPort            1500 <do not change>
TCPServeraddress   <TSM server ip address>
PASSWORDACCESS GENERATE <- add this line if not there.>

Check the permission of /tmp/tsmlog , the directory tsmlog must be 777. Then create a file dsierror.log and change the file permission to 777.

IMP: After making these changes, please run the command dsmc as super user.It will ask for TSM Server user id and password .Please use user id “admin” and password as same as we set during the TSM server configuration. Once you enter all those correctly, it will enable you to login and give you a “tsm>” prompt. Please enter the command “quit” to come out from this prompt. Now as “Informix” user, please run the command dsmc, this time it should not asks for any user verification and directly prompt you as “tsm>”.Use command “quit” again to come out from the “tsm>”

IBM Tivoli Storage Manager
Command Line Backup/Archive Client Interface
Client Version 5, Release 3, Level 4.0
Client date/time: 09/14/06 09:44:36
(c) Copyright by IBM Corporation and other(s) 1990, 2006. All Rights Reserved.

Node Name: SUN3500.LENEXA.IBM.COM
Session established with server SERVER1: Linux/i386
Server Version 5, Release 3, Level 2.0
Server date/time: 09/14/06 09:40:25 Last access: 09/14/06 09:40:17

tsm>

Installing the Ontape Backup-Restore Patch:

Please copy these 3 files to the directory $INFORMISDIR/bin

Ontape Backup-Restore through TSM:

Backup

Pre-Condition: Database Server is online mode

Archive full system

Go to Informix Log directory. Now run the command

ontape_backup.sh | ontape –s | tee auto_ontape.log

You do not have to press any option, every thing is automated .You can keep look into your terminal to know what is happening .Sometimes you may see some error like “Bad tape”, the program is intelligent enough to take care of those error. You should receive the message like this in your terminal:

sun3500% ontape_backup.sh | ontape -s | tee auto_ontape.log
Please enter the level of archive to be performed (0, 1, or 2)
Please mount tape 1 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …

Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:

Please mount tape 2 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …

Please label this tape as number 2 in the arc tape sequence.

Please mount tape 3 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …

Please label this tape as number 3 in the arc tape sequence.

Please mount tape 4 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …

Please label this tape as number 4 in the arc tape sequence.

Please mount tape 5 on /usr/informix/data/tapedev and press Return to continue … 100 percent done.

Please label this tape as number 5 in the arc tape sequence.

Program over.
sun3500%

Continuous backup of logical logs

Go to Informix Log directory. Now run the command

ontape_backup.sh | ontape –c | tee auto_ontape.log

You do not have to press any option, every thing is automated .You can keep look into your terminal to know what is happening .Sometimes you may see some error like “Bad tape”, the program is intelligent enough to take care of those error. As soon the logical log fills the tape, it will take the backup and transfer the backed up tape files to tsm. You should receive the message like this in your terminal:

sun3500% ontape_backup.sh | ontape -c | tee auto_ontape.log

Performing continuous backup of logical logs.

Please mount tape 1 on /usr/informix/data/ltapedev and press Return to continue…

Tape is full …

Please label this tape as number 1 in the log tape sequence.

This tape contains the following logical logs:
7 (partial)

Please mount tape 2 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …

Please label this tape as number 2 in the log tape sequence.

This tape contains the following logical logs:
7 - 8

Please mount tape 3 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …

Please label this tape as number 3 in the log tape sequence.

This tape contains the following logical logs:
8 - 9

Please mount tape 4 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …

Please label this tape as number 4 in the log tape sequence.

This tape contains the following logical logs:
9 (partial)

Please mount tape 5 on /usr/informix/data/ltapedev and press Return to continue…

Note: For Automatic backup of the logical log, Please use ontape_backup.sh | ontape –a | tee auto_ontape.log

Restore

Physical restore for HDR

Pre-Condition: Database Server is in shutdown mode

Go to Informix Log directory. Now run the command

ontape_p_restore.sh | ontape –p | tee auto_ontape.log

It will show you the list of 1st tape drive from different full system backup taken on different days and time .You have to choose one of them which is required and press enter and then you do not have to press any option it will bring the different backup tapes from TSM Server and restore them. The terminal message will be like this only:

sun3500% ontape_p_restore.sh | ontape -p | tee auto_ontape.log

Please mount tape 1 on /usr/informix/data/tapedev and press Return to continue .
.. /usr/informix/data/main950.09-12-2006-06:16.1.tapedev
/usr/informix/data/main950.09-12-2006-06:24.1.tapedev
/usr/informix/data/main950.09-12-2006-06:28.1.tapedev
/usr/informix/data/main950.09-12-2006-06:36.1.tapedev
/usr/informix/data/main950.09-12-2006-06:38.1.tapedev
/usr/informix/data/main950.09-12-2006-06:45.1.tapedev
/usr/informix/data/main950.09-12-2006-06:48.1.tapedev
/usr/informix/data/main950.09-12-2006-08:09.1.tapedev
/usr/informix/data/main950.09-14-2006-05:11.1.tapedev
/usr/informix/data/main950.09-14-2006-05:25.1.tapedev
/usr/informix/data/main950.09-14-2006-06:40.1.tapedev
Please enter the file number with path
/usr/informix/data/main950.09-14-2006-06:40.1.tapedev (I choose this file)
Files retrieved successully from tsm …

Archive Tape Information

Tape type:      Archive Backup Tape
Online version: IBM Informix Dynamic Server Version 7.31.UN233
Archive date:   Thu Sep 14 06:40:38 2006
User id:        informix
Terminal id:    ?
Archive level: 0
Tape device:    /usr/informix/data/tapedev
Tape blocksize (in k): 16
Tape size (in k): 400
Tape number in series: 1

Spaces to restore:1 [rootdbs ]

Archive Information

IBM Informix Dynamic Server Copyright(C) 1986-2004 IBM Informix Software, Inc.
Initialization Time       09/14/2006 06:38:50
System Page Size          2048
Version                   6
Archive CheckPoint Time   09/14/2006 06:40:38

Dbspaces
number flags fchunk nchunks flags owner name
1 1 1 1 N informix rootdbs

Chunks
chk/dbs offset size free bpages flags pathname
1 1 0 10000 4663 PO- /usr/informix/data/rootdbs

Continue restore? (y/n)Do you want to back up the logs? (y/n)
Please mount tape 2 on /usr/informix/data/tapedev and press Return to continue … Tape sequence number is 1, expecting 2

Bad tape.

Please mount tape 2 on /usr/informix/data/tapedev and press Return to continue .
..
Please mount tape 3 on /usr/informix/data/tapedev and press Return to continue .
..
Please mount tape 4 on /usr/informix/data/tapedev and press Return to continue … Restore a level 1 archive (y/n)
Program over.
sun3500%

Logical restore for HDR

Pre-Condition: Database Server is in Fast Recovery mode ( Physical restore Completed)

1. Open 2 sessions and set the environment for both sessions
2. Now from one session run the command
ontape –l
It will flash you the message like :

Roll forward should start with log number 7

Please mount tape 1 on /usr/informix/data/ltapedev and press Return to continue…

3.Now from another session run the command ontape_l_restore.sh

It will list you all the logical logs back-up.Please choose the correct one and press enter.It will mount the desired tape on the logical tape device. And Confirm you on that.The message will flash in the screen like this way:

/usr/informix/data/main950.7(partial).ltapedev
/usr/informix/data/main950.7-8.ltapedev
/usr/informix/data/main950.8-9.ltapedev
/usr/informix/data/main950.9(partial).ltapedev
/usr/informix/data/main950.Pleaselabelthistapeasnumber7inthearctapesequence..lta
pedev
Please enter the file name to restore
/usr/informix/data/main950.7(partial).ltapedev ( I enter this logical log)
IBM Tivoli Storage Manager
Command Line Backup/Archive Client Interface
Client Version 5, Release 3, Level 4.0
Client date/time: 09/14/06 09:17:21
(c) Copyright by IBM Corporation and other(s) 1990, 2006. All Rights Reserved.

Node Name: SUN3500.LENEXA.IBM.COM
Session established with server SERVER1: Linux/i386
Server Version 5, Release 3, Level 2.0
Server date/time: 09/14/06 09:13:10 Last access: 09/14/06 09:13:01

Restore function invoked.

Restoring 409,600 /usr/informix/data/main950.7(partial).ltapedev [Done]

Restore processing finished.

Total number of objects restored:         1
Total number of objects failed:           0
Total number of bytes transferred:    400.07 KB
Data transfer time:                    0.02 sec
Network data transfer rate:        15,086.17 KB/sec
Aggregate data transfer rate:        131.65 KB/sec
Elapsed processing time:           00:00:03
File restored successfully
File mounted on tapedev … Please continue the backup
Press <q> when you are finished else press enter ..

4. After getting the confirmation message like “File mounted on tapedev … Please continue the backup” , Please press enter for the session showing “ Please mount tape 1 on /usr/informix/data/ltapedev and press Return to continue…”

5.Log Back up will be restore and after the restore is complete it will flash a message “Do you want to restore another log tape? (y/n)” , If you wish please press Y else N.
On Pressing Y , it will show the Message like this “Please mount tape 2 on /usr/informix/data/ltapedev and press Return to continue…” and pressing N it will come out saying “Program Over” and put the database server as “Quotient” mode.”

6.If you want more logical log restore , Press enter to the 2nd session, It will again show you the list of logical logs and you have to choose the correct one , after mounting the logical log on Log tape device correctly you should press enter in the 1st session to restore the mounted log. And repeat the step 5, 6 as soon as your restoration will be in progress.

Ontape simple test:

Please do a simple test to make sure ontape is working fine.

1.Backup data through ontape by running the command ontape_backup.sh | ontape –s | tee auto_ontape.log.
2.Now start the continues log backup by ontape_backup.sh | ontape –c | tee auto_ontape.log.
3.Make some transaction so that the atleast one logical log fulls
4.Make the Informix-Server down.
5.Restore the database by running the command ontape_p_restore.sh | ontape –p | tee auto_ontape.log
6.Restore the database by running the command ontape_p_restore.sh | ontape –p | tee auto_ontape.log
7.Restore the backed up logical log with the combination of the command ontape_r_restore.sh and ontape –l as described in the document
7.Make the Informix server online and try to see the changes you made after the archive backup.

0 Comments

Java databasing with Derby

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 1:46 pm - 669 views

(No Ratings Yet)

Loading ...

By Pan Pantziarka

+ More by this author

Java’s own open source database

It sometimes feels as if open source databases are a dime a dozen. There are the big names like MySQL, PostgreSQL, and Ingres. There are plenty of other lesser known but no less powerful open source databases: Firebird, SQLite, HSQLDB, Apache Derby, IBM Cloudscape, and Java DB.

Actually, I cheated there at the end, those last three are the same database sporting different branding. Apache Derby – which is the name we’ll stick with throughout the rest of this article – used to belong to Informix, who had bought a company called Cloudscape, who had developed a SQL-compliant Java relational database. Informix was in turn swallowed up by IBM, who eventually open sourced Cloudscape by contributing the code to Apache Software Foundation. Cloudscape then became Apache Derby; though when Sun Microsystems decided to add it to the Java 6.0 SDK, they christened it the Java DB.

Now that we’ve got all that clear, what actually is Apache Derby and why should you be interested? Derby is a relational database management system written completely in Java. It offers a high level of SQL standards-compliance, native access using JDBC, works both as an embedded database or in client/server mode and has a relatively small footprint (around 2MB).

But it’s not just compact, it’s powerful too, and supports transactions, referential integrity, stored procedures (written in Java), and in client/server mode supports bindings to ODBC, PHP, Perl, and Python. Being Java also makes it multi-platform, and it uses any certified Java Virtual Machine so that its availability is maximised across platforms.

This tutorial will concentrate mainly on using Derby as an embedded database for a Java application. In other words Derby will be used as a persistent data store, and it is the application which will manage the database. This is in contrast to client/server mode, where the database is loaded onto a server and waits for client applications to connect to it.

Installation

Installation is remarkably painless. It’s a simple case of downloading the zip files (fromhere) and unzipping to an appropriate directory. We can easily check that installation has been successful by using the sysinfo tool in the bin directory (sysinfo.bat for Windows). To do this requires the setting up of an environment variable called DERBY_HOME to point to the bin directory, and for this to be added to the PATH. For example under Windows:

set DERBY_HOME=C:\Apache\db-derby-10.2.1.6-bin
set PATH=%DERBY_HOME%\bin;%PATH%

With these environment variables in place running sysinfo at the command prompt will spool out a listing of information about the Java environment and the installation of Derby that it has found.

Command-line Access

Sticking to the command-line for the moment, we can use the ij tool to interact with Derby. This provides a command-line from which we can connect to a database instance and issue SQL commands to create tables, enter data, and submit queries.

The first thing we’ll do is create a new database, called AssetDB, which we’ll use later with some Java code. It will be a simple DB with just two tables to hold user names and a basic asset register. Assuming that we want the database to be in the C:\DerbyDB directory we create it by loading the ij command-line (type ij from a command-prompt), and then at the ij> prompt entering:

connect 'jdbc:derby:/DerbyDB/AssetDB;create=true';

This creates the directory tree C:\DerbyDB\AssetDB. To populate the database we can either enter the CREATE TABLE statements direct from the ij> prompt, or we can run them from a text file. We’ll do the latter and enter the following commands into a file called create_sql.txt in the DerbyDB directory:

CREATE TABLE USERS (
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
EMP_NO INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY
);

CREATE TABLE PC (
TYPE VARCHAR(10) NOT NULL,
SERIAL VARCHAR(50),
OS VARCHAR(20),
EMP_NO INTEGER,
CODE_NO INTEGER NOT NULL CONSTRAINT CODE_NO_PK PRIMARY KEY
);

To run these from ij> enter the following command: run '/ DerbyDB/create_sql.txt';

The SQL commands will be executed in turn and ij will report that ‘0 rows have been inserted/updated/deleted’. Running describe USERS; will produce a listing of the columns and column meta-data for the table.

We can also use ij to enter some data. Again we’ll use a text file to contain the following INSERT queries:

INSERT INTO USERS VALUES('Bill','Gates',1);
INSERT INTO USERS VALUES('Joe','Bloggs',2);
INSERT INTO USERS VALUES('Peter','Kropotkin',3);
INSERT INTO PC VALUES('Desktop','01010','Linux',1,1);
INSERT INTO PC VALUES('Laptop','101010','BSD',2,2);
INSERT INTO PC VALUES('Desktop','101010','XP',3,12);

Finally, we can run a SELECT query: SELECT * FROM USERS;

To quit ij simply enter: exit;

And Java?

So far we’ve used a command-line tool to create a simple database and enter a few rows of data; big deal. Now we’ll show how simple it is to embed that database in a Java application. The first step is to make sure that the derby.jar file is on your classpath or is included with your project in Eclipse, NetBeans or other IDE.

Setting up the database just uses standard JDBC functionality. Register the JDBC driver, provide a URL for the connection, and attempt the connection:

String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbName="/DerbyDB/AssetDB";
String connectionURL = "jdbc:derby:" + dbName;
Connection conn = null;

try{
    Class.forName(driver);
} catch(java.lang.ClassNotFoundException e) {
    e.printStackTrace();
}

try {
    conn = DriverManager.getConnection(connectionURL); 

    //body of code to go here

}  catch (Throwable e)  {
    e.printStackTrace();
} finally {
    conn.close();
}

Once the connection is made it’s possible to create a statement object and to use it to access the database. Here we want to add another record to the database:

Statement st=conn.createStatement()
int m=st.executeUpdate("INSERT INTO USERS VALUES('Adam','Smith',4)");
System.out.println("Updated " + m + " rows");

Executing the above should result in the message “Updated 1 rows” appearing in console output.

SELECT queries are no more difficult:

ResultSet rs=st.executeQuery("SELECT * FROM USERS");
while (rs.next()){
    String first=rs.getString("FIRST_NAME");
    String last=rs.getString("LAST_NAME");
    System.out.println("Name: " + first + " " + last);
}
rs.close();

Joins are straightforward too:

String qry="SELECT USERS.FIRST_NAME, USERS.LAST_NAME,"
    + " PC.OS FROM USERS, PC WHERE USERS.EMP_NO=PC.EMP_NO"
    + " AND USERS.EMP_NO=1";
rs=st.executeQuery(qry);
while (rs.next()){
    String first=rs.getString(1);
    String last=rs.getString(2);
    String os=rs.getString(3);
    System.out.println(first + " " + last + " uses " + os);
}
rs.close();

This prints out the message: Bill Gates uses Linux

Conclusion

If you add to its simplicity of setup, ease of use, and small footprint, the fact that Derby supports referential integrity and ACID-compliant transactions, you have a pretty powerful database tool for your Java applications.

0 Comments

XA Transaction and Its implementation on IDS through WebSphere

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 1:38 pm - 771 views

(No Ratings Yet)

Loading ...

By Amitava Chakraborty, Primary Contact - IBM Informix –Interoperability

Email: amitacha@in.ibm.com

What is a XA Transaction?

Most of the people related to web development may heard about XA, But many of them not very much sure about it. By definition XA is a standard protocol that allows coordination, commitment, and recovery between transaction managers (e.g. CICS, Tuxedo, and even BEA Web Logic Server) and resource managers (e.g. databases, message queuing products such as JMS or Web Sphere MQ, mainframe applications, ERP packages) managers.

XA is used to coordinate what is commonly called a two-phase commit (2PC) transaction. The classic example of a 2PC transaction is when two different databases need to be updated atomically. Most people think of something like a bank that has one database for savings accounts and a different one for checking accounts. If a customer wants to transfer money between his checking and savings accounts, both databases have to participate in the transaction or the bank risks losing track of some money.

Does XA transaction is needed even if the application uses a single database?

The problem is that most developers think, “Well, my application uses only one database, so I don’t need to use XA on that database.” This may not be true. The question that should be asked is, “Does the application require shared access to multiple resources that need to ensure the integrity of the transaction being performed?” If the application needs to update the database and any of these other resources in the same transaction, then both the database and the other resource need to be treated as XA resources.

Some Simple Examples

An extremely common use case for WebSphere Application server that calls for XA is to pull a message from WebSphere MQ, do some business processing with the message, make updates to a database, and then place another message back on MQ. Usually this whole process has to occur in a guaranteed and transactional manner. There is a tendency to shy away from XA because of the performance penalty it imposes. Still, if transaction coordination across multiple resources is needed, there is no way to avoid XA. If the requirements for an application include phrases such as “persistent messaging with guaranteed once and only once message delivery,” then XA is probably needed.

Another example, though extremely simplified, WebSphere application server definition that needs to use XA. A JMS message is received to start the process. Assume the message is a customer order. The order then has to be placed in the order shipment database and placed on another message queue for further processing by a legacy billing application. Unless XA is used to coordinate the transaction between the database and JMS, we risk updating the shipment database without updating the billing application. This could result in the order being shipped, but the customer might never be billed.

Once you’ve determined that your application does in fact need to use XA, how do we make sure it is used correctly?

Fortunately, J2EE and the Java Transaction API (JTA) hide the implementation details of XA. Coding changes are not required to enable XA for your application. Using XA properly is a matter of configuring the resources that need to be enrolled in the same transaction. Depending on the application, the WebSphere Application Server resources that most often need to be configured for XA are connection pools, data sources, JMS Servers, JMS connection factories, and messaging bridges. Fortunately, the entire configuration needed on the WebSphere side can be done from the WebSphere Server Console.

What is the minimum requirement to implement a XA transaction ?

Before worrying about the WebSphere configuration for XA, we have to ensure that the resources we want to access are XA enabled. Check with the database administrator, the WebSphere MQ administrator, or whoever is in charge of the resources that are outside WebLogic. These resources do not always enable XA by default, nor do all resources support the X/Open XA interface, which is required to truly do XA transactions. For example, some databases require that additional scripts be run in order to enable XA.

For those resources that do not support XA at all, some transaction managers allow for a “one-phase” optimization. In a one-phase optimization, the transaction manager issues a “prepare to commit” command to all of the XA resources. If all of the XA resources respond affirmatively, the transaction manager will commit the non-XA resource. The transaction manager will then commit all of the XA resources. This allows the transaction manager to work with a non-XA resource, but normally only one XA resource per transaction is allowed. There is a small chance that something will go wrong after committing the non-XA resource and before the XA resources all commit, but this is the best alternative if a resource just doesn’t support XA.

If only some of the database access needs to be done under XA, create two connection pools for the same database. Use an XA driver on one of the connection pools and a non-XA driver on the other. This will avoid the performance overhead of XA transactions for database calls that don’t need 2PC.

How do you Configure XA data sources in WebSphere Application server?

Step 1: Creation of Environment Variable “INFORMIX_JDBC_DRIVER_PATH”

From the WebSphere Administrative console à Environment à WebSphere Variables , then select node and Apply.

Now all the environment variables related to the Node will be displayed. Search for the variable “INFORMIX_JDBC_DRIVER_PATH”, if it is already defined, please ensure it is pointed to a correct directory where the JDBC drivers for Informix is located. If it is not defined please create a new environment variable with the same name and its value will be the directory where the JDBC driver for Informix is located.

Step 2: Creation of JDBC Provider

From the Main Administrative console screen choose the option Resources àJDBC Providers à Node à Apply. All the JDBC providers related to that node will be displayed. Check of any provider name like “Informix JDBC Driver (XA)”, if it is already defined ensures that the JDBC drivers for Informix are correctly mentioned for that provider. If not please create a new one pointed to the correct JDBC drivers for Informix. Please ensure these three ifxsqlj.jar, ifxjdbc.jar, ifxjdbcx.jar is mentioned in the “Informix JDBC Driver (XA)”

Note: For XA data source JDBC providers, set the implementation class name to com.informix.jdbcx.IfxXADataSource. Also, create the following CLASSPATH entries,

${INFORMIX_JDBC_DRIVER_PATH}/ifxsqlj.jar

${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbc.jar

${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbcx.jar

where ${INFORMIX_JDBC_DRIVER_PATH} is the path where your JDBC driver is installed. The ifxsqlj.jar is used when using SQLJ with WebSphere Application Server.

Step 3: Create a Data source Under XA

You can create a data source under either of the two JDBC providers that are described in the previous section, depending on the application requirements. After you create the data source, you can use the Custom Property link in the Application Server Administrative Console to add or modify environment parameters that Informix Dynamic Server requires. You can navigate to the Custom properties link from Resource -> JDBC Provider -> JDBC provider Name (XA) -> Data source name used by the application.

You can also add new environment parameters that are required by Informix Dynamic Server using the new button under Custom Properties. To modify an existing parameter, click the required parameter and type the new value. Click Apply to immediately make the change in the local configuration. Click Save at the top of the page (inside the message box) to save the changes to the master configuration. You might need to restart Application Server for these changes to take effect.

After step 3 has completed please test the data source which will ensure that the data base is connected properly with WebSphere Application Server.

Troubleshooting

This section addresses common problems and their resolutions.

XA transaction problems

Both of these issues mentioned below have a common resolution.

* Symptom: SQL Exceptions because tightly coupled transactions are not enabled in IDS.

When tightly coupled transactions are not enabled in IDS, you might receive one of the following exceptions:

*
o java.sql.SQLException: ISAM error: The record is locked.
o java.sql.SQLException: Could not do a physical-order read to fetch next row.

The ISAM message is slightly different, depending on whether SET LOCK MODE TO WAIT [seconds] is set. If you set it to SET LOCK MODE NOT WAIT, the message is ISAM error: record is locked. With SET LOCK MODE TO WAIT [seconds], the application waits for the timeout, and then throws the exception ISAM error: Lock Timeout Expired.

* Symptom: Message “Could not do a physical-order read to fetch next row” error in the SystemOut.log of Application Server.

When multiple users or clients access the same Informix database doing XA transactions concurrently, you might get the following error in the Application Server file SystemOut.log:

Caused by: java.sql.SQLException: Could not do a physical-order read to fetch next row.

DSRA0010E: SQL State = IX000, Error Code = -244

at com.informix.jdbc.IfxSqli.a (IfxSqli.java (Compiled Code))

at com.informix.jdbc.IfxSqli.E (IfxSqli.java(Compiled Code))

Resolution: You might need to tune IDS and set some Informix environment variables in Application Server custom properties. The following Informix configuration parameter needs to be set:

DISABLE_B162428_XA_FIX:

* 0 (Default): Frees transactions only when an XA rollback is called.
* 1: Frees transactions if transaction rollback for other than an XA rollback. Takes effect when the database server is shut down and restarted.

According to the XA/Open specifications, if a transaction is rolled back by the server for other than an XA rollback by the client, the XID is not forgotten, and the transaction is in rollback only state. This behavior is the default for Informix Dynamic Server, Version 10.0. To return to the previous behavior, set the following ONCONFIG parameter in IDS:

Set DISABLE_B162428_XA_FIX to 1 to immediately free all global transactions after a transaction rollback, which is the default for Dynamic Server V9.40 and earlier versions. The default behavior for Dynamic Server 10.0 is to free global transactions after an xa_rollback is called, and this behavior is required to confirm to the XA state table that a transaction can be freed only after xa_rollback is called. Setting DISABLE_B162428_XA_FIX to 1 ensures that applications written for the earlier version of Dynamic server work properly.

For Informix Dynamic Server Version 9.40, the ONCONFIG parameter ENABLE_B162428_XA_FIX is set to 1 for XA_SPEC+ compliant behavior.

You can override the DISABLE_B162428_XA_FIX configuration parameter of the server for a client session with the IFX_XASTDCOMPLIANCE_XAEND environment variable. This environment variable can be particularly useful when the server instance is disabled for new behavior by the ONCONFIG parameter, but one client requires the new behavior. The behavior of XA_END when XA_RB* is returned is specified by the setting of IFX_XASTDCOMPLIANCE_XAEND. It can take the following values:

IFX_XASTDCOMPLIANCE_XAEND

* 1: XID is not forgotten. Transaction is in Rollback-only state. This is XA_SPEC+ compliant and is the default behavior with IDS 10.0.
* 0: XID is forgotten. Transaction is Nonexistent. This is default behavior with IDS V9.40.

The following Informix-specific environment variables must be set in Application Server custom properties:

* Turn on the IFX_XASPEC variable.

Activating this variable enforces tight coupling of XA transactions within the same global transaction ID, and enables the transactions to share lock space. IFX_XASPEC only applies to XA connections and cannot be specified in a database URL. It can be specified by data source or by setting a system property (of the JVM) with the same name. The data source property overrides the system property. Any values for the properties other than y, Y, n, or N are ignored. IfxDataSource.getIfxIFX_XASPEC returns the final IFX_SPEC value, which is either y or n. For example if the value of data source IFX_XASPEC equals n and the value of the system IFX_XASPEC equals Y or y, n is returned.

How to set this property in Application Server custom property:

*
1. Log on to the Application Server Administrative Console.
2. Create a new custom property ifxIFX_XASPEC for the Informix XA data source; set it to Y or y. This value is not a Boolean value, and your setting for the property overrides the database system setting.

* Set Informix Lock Mode Wait for the data source to a higher value.

Although not required, this property enables you to set the number of seconds that Informix dynamic server waits for a lock. By default, Informix Dynamic Server throws an exception if it cannot acquire a lock immediately. Informix Lock Mode Wait can be set to 1000 seconds.

* Add XA_CONN_LEVEL_SETTING 0 in the Informix ONCONFIG file and restart the Informix Server engine to make the affect for this change.

You can try the following tuning steps:

* Run the command UPDATE STATISTICS HIGH against the database. This command updates the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan.
* If many transactions will be updating individual rows, change to row locking mode for a particular table using the following SQL statement:

ALTER TABLE [table name] LOCK MODE (ROW)

* Create indexes for the column that your application is querying or updating.

Software with the update:

* Informix Dynamic Server, Version 9.40.UC6
* Informix Dynamic Server, Version 10.00.UC1
* 3.00. JC1 JDBC driver

* Symptom: Informix transaction isolation settings in a session are not getting propagated across XA global transactions.

You might see the following exception message:

A SQLException “Could not position within a table” with a nested

SQLException “Lock Timeout Expired” thrown.

This exception is caused because IDS resets the transaction isolation setting to the database default when the XA transaction ends. The change happens on the database, so WebSphere is not aware that it changed and might not reset the transaction isolation level the next time the connection is retrieved from the pool. This causes the connection and IDS to get out of synchronization.

When the transaction isolation setting is reset, it differs depending on the Informix database logging type:

Transaction isolation defaults differ by IDS logging type

Logging type

Default transaction isolation

Database without transaction logging

Read uncommitted

Database with logging that is not ANSI-compliant

Read committed

Database with logging that is ANSI-complaint

Serializable

If you attempt to use READ_UNCOMMITTED default read transaction isolation with the WebSphere scheduler service, all scheduler read operations will be blocked, and you might receive lock timeout expired SQLException exceptions for long-running tasks that were part of the read operation.

Resolution: To enable the tightly coupled XA transactions in Informix Dynamic Server, follow the resolution for Symptom: Enabling tightly coupled XA transactions in Informix Dynamic Server. With the fix, the values for isolation level and lock mode are complete-connection level setting. Complete-connection level setting allows propagation of values that were set in the local environment to all transactions. If the value is changed within a transaction, the changed value is propagated back to the local environment and to all subsequent (new and resume) transactions.

Software with the fix:

*
o 9.40. UC7W1 and later versions of Informix Dynamic Server
o 10.00. UC3W4 and later versions of Informix Dynamic Server
o 3.00. JC3 JDBC driver

The Missing Index

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 12:59 pm - 487 views

(No Ratings Yet)

Loading ...

SQL Plan Cache Snapshot, Visual Explain, and other tools solve a DB2 for i5/OS performance mystery.
By Jim Flanagan and Shantan Kethireddy

The story in this column illustrates a methodology DBAs can use to solve SQL query performance issues in DB2 for i5/OS V5R4 when crucial evidence is missing.

Mr. D.B. Admin of Company A wants to hire famed detective Shantan Kolmes and his sidekick Dr. Flatson to help with a serious performance problem. As of last week, Company A’s main order application has been experiencing drastic performance degradation. Mr. D.B. Admin calls Kolmes and Flatson to explain the problem.

“Every day at 10 a.m., our customers’ orders are at their peak, and our operators can’t process them fast enough. We’ve had customers disconnect mid-order. We’ve lost several long-time customers and who knows how many new customers. Our chief competitor, Company Z, is quickly taking over several of our accounts. Performance wasn’t an issue until very recently. Please help — the CEO is ready to fire the entire IT staff!”

“Do you have a SQL Performance Monitor from when the system was running well?” asks Kolmes.

“No, we didn’t see the need for it.”

“I suppose you don’t have an SQL Plan Cache Snapshot either?” Kolmes says.

“No, does that matter?”

“It will make things a bit more difficult.” But Kolmes remained confident. “Has anyone changed the application since you started seeing this problem?” Kolmes asks.

“No, all changes are supposed to be approved through me.”

Kolmes finds Mr. Admin’s choice of words curious. “You say, ‘supposed to.’ It sounds like you might have had the same issue in the past?”

“Well, yes. I have one developer who is a bit of a maverick. But he assures me he didn’t change anything.”

“Have you noticed anything else unusual?” Kolmes asks.

“We did have a contract programmer recently for a minor update, but I approved that change myself.”

Kolmes says he’ll arrive before 10 a.m. the next day. “Whatever you do, please don’t IPL the system!”

In the meantime, Flatson researches Company Z. As he looks through his notes from the questioning of Mr. Admin, Kolmes begins to develop a preliminary strategy for clue collection and analysis.
Mr. Kolmes and Dr. Flatson arrive at Company A at 9 the next morning. Mr. Admin reports that he successfully stopped the system operator from performing an IPL of the system.

Snapshot Significance

If you’re in the situation facing Mr. Kolmes, the first order of business is to capture the SQL performance environment by taking a SQL Plan Cache Snapshot. To do so, go to the iSeries Navigator interface, expand the Databases folder and the specific database folder, and then right-click it. Select the SQL Plan Cache option and then the Show Statements menu action.

On the statements screen, you can fill in the filters you’re interested in. In the Kolmes case, we know which application is causing the problems, so you’d fill in the schema name for the queries that use or reference these objects. You can also set the filter for Top ‘n’ queries with the largest total accumulated runtime, if you were sure you’d catch the queries for KEYPROD1. Figure 1 shows what Kolmes retrieved. You can save the list of queries to a schema for later viewing by choosing Create Snapshot and giving the schema and snapshot name.

Figure 1. List of queries referencing KEYPROD1.

Figure 1 shows the names of the contract programmer, Mr. Admin, and the maverick programmer. But row six shows something interesting; the User Name in that row matches the last name of Company Z’s CIO.

Notice how easily we (and the two detectives) uncovered preliminary evidence. Before V5R4, you had to turn on a monitor during a period when the application was experiencing difficulties and then analyze the monitor data using monitor analysis queries. This approach could cause system performance degradation if appropriate filtering wasn’t used. You also had to try to duplicate the environment in which the application was running. Now, you can simply take a SQL Plan Cache Snapshot with no system overhead, and have all of the same information and analysis tools at your disposal. Keep in mind that the SQL Plan Cache only contains information about queries that went down the SQL Query Engine (SQE). In-depth analysis of queries that went down the Classic Query Engine (CQE) may still require use of the Database Monitor. See Resources for more information about the difference between SQE and CQE.

If we had had a SQL Plan Cache Snapshot or a monitor from before the point of the initial degradation, we could have run the comparison menu action between the old and new environment to see what was changed. Instead, we’ll need to analyze the worst running queries from the application under the present conditions, figure out the root cause of the performance bottleneck, and then try to figure out what changed (and why) to cause this bottleneck.

A Visual Clue

In order to view the detailed performance information for a given query, you would use a tool called Visual Explain. Visual Explain is a database tool that graphically represents the optimizer implementation of a query request. It provides a method of identifying and analyzing database performance problems. The tree-like output provides a comprehensive understanding of the optimized plan. Visual Explain shows all the attributes necessary for users and developers to understand implementations, performance bottlenecks, indexing strategies, and so on.

Going back to the SQL Plan Cache viewer dialog, you can simply select the desired statement and click Run Visual Explain.

The Final Select icon shows all environmental attributes such as pool size, number of processors, QAQQINI information, and so on. Based on the list of environmental settings Mr. Admin provided, the detectives find these attributes to be in order. Mr. Admin’s company doesn’t have SMP installed, so the lack of parallel processing is expected. You can see the area of the tree that was estimated to be the most costly by going to the View menu and selecting the Highlight Index Advised menu option. In Mr. Admin’s case, the most costly area of the tree was a table scan on the two-billion-row ORDERS table, which populated a hash table used for joining.

The Index Advisor (available as an icon in the toolbar) suggests indexes to improve performance. In Mr. Admin’s case, the Index Advisor suggests an index over a heavily used join column from the two-billion-row ORDERS table, which would explain the poor performance.

If you look at the rest of Mr. Admin’s poorly performing statements, you see that all the statements reference the same join column from the same ORDERS table. To find out if this missing index is behind the performance problems, you have to determine whether this index, or a comparable index, had existed before the performance degradation and that it was removed immediately prior to the degradation.

Reconstructing Details

To find a record of objects in the database from a period when the application was running well, you would open the iSeries Navigator application and expand the Systems folder. Right click on the database folder and select the Health Center menu option. You should see the Overview page. Click on the Size Limits tab. In Mr. Admin’s system, the settings are already set to the schema the application is running in. Hit Refresh to show the current size limits for KEYPROD1. If you want to save the results on this screen, choose save, and the save dialog displays with the history file name filled in.

Now that the current environment is saved to the history file, you can do a comparison to the last capture. Click View History… and fill in the history filename.

When Mr. Kolmes sees the history file that contains the two collections of the Size Limits for this application, he expands the Maximum number of index entries node in the two collections so that he can compare the history files of the current and last months (see Figure 2).

Figure 2. Comparison of current and past history files.

His examination shows that there is one less index in the current environment than in the previous environment. The missing index is IMPORTANT_IDX1.”

Kolmes tells Flatson that they need one final piece of data: the auditing journal (QSYS/QAUDJRN). Using the Display Journal (DSPJRN) command on the auditing journal reveals the precise moment when the index was removed — and who removed it.

Who Done It?

Mr. Kolmes calls a meeting with Company A’s CEO, Mr. Admin, the system operator, and the maverick programmer.

“We found a DROP INDEX entry in the audit journal. Does anyone know why that was done?”

The system operator says it sounds just like the maverick programmer.

Kolmes replies, “We thought that might be the case, but the queries in the SQL Plan Cache Snapshot show your User Name, Mr. System Operator.”

“That’s not unusual; I do queries when diagnosing a system problem.”

“Ah, but your name also shows up in the audit journal as the User Name who did the DROP INDEX. We also found out that the CIO of Company Z has a nephew that works in this company,” Flatson chimes in.

The system operator yells out, “It was me alright, I admit it! I would have gotten away with it too if it hadn’t been for those new tools, and if you hadn’t stopped me from IPLing the system.”

“Tell it to the judge,” Kolmes replies.

In Mr. Admin’s case, a company wanted to steal a competitor’s business, so they used an insider to remove a key index. Once the index is added back, order processing performance should return to its expected sub-second timeframe.

The moral of the story? Keep an SQL Plan Cache Snapshot of your environment so you have something to check against if you encounter performance problems in the future.

Jim Flanagan is an advisory software engineer at IBM in database development for DB2 for i5/OS and team leader of iSeries Navigator — Database.

Shantan Kethireddy is a member of the IBM SQL Query Engine team and a frequent speaker at System i technical conferences.

0 Comments

DB2 9 Data Warehouse Edition: Information Under One Roof

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 12:45 pm - 390 views

(No Ratings Yet)

Loading ...

Sleek, simple, and efficient, DB2 9 Data Warehouse Edition promises a modern enterprise warehouse without the hassle.
By Bob Rumsby

Analysts across many industries — retail, banking, insurance, healthcare, telecommunications, government — need timely, accurate, and new information about their businesses to help them prosper in the marketplace. Retail companies, for example, collect huge amounts of transactional data about daily sales activity. But simply having that information isn’t enough. They need to know why sales have dropped for a particular brand or store and how to promote new products in the most cost-effective way.

Data warehousing project managers have tried to address these requirements for a long time — at great cost. They have had to manage the purchasing, maintenance, and learning curve of pulling together multiple products from multiple vendors. What if the software infrastructure and tools to solve these kinds of business problems were all under one roof? What if customers could:

Adopt a single design and deployment tool for data warehouse and OLAP development
Eliminate custom-developed SQL scripts that maintain warehouse tables
Simplify and accelerate the discovery of new business insights from data mining
Create interactive portals, with custom reports based on relational warehouse data.

DB2 Data Warehouse Edition Version 9.1 (available since mid-2006 on Linux, Unix, and Windows platforms) responds to these questions by providing the tools to build and maintain DB2 enterprise data warehouses. DWE 9.1 also supports analytic structures required for data mining and online analytic processing (OLAP) and the development of embedded analytics for business access on the Web.

DWE 9.1 brings many functional components together and exposes many of their functions through two common client tools: the Design Studio and the Administration Console. DWE components include:

DB2 UDB Enterprise Server
Database Partitioning Feature (DPF)
WebSphere Application Server
SQL Warehousing Tool
Rational Data Architect (RDA) physical data modeling
Intelligent Miner
Cube Views (OLAP)
DB2 Alphablox
DB2 Query Patroller.

DWE Design Studio

The Eclipse-based Design Studio is an integrated graphical development environment for designing:

Physical data models, including DDL and SQL generation and deployment features, JDBC connectivity to data sources and targets, comparison and synchronization of database objects, and basic metadata support for modeling SQL-based data flows and mining flows (functions derive from the RDA tool set)
SQL-based flows, including both data flows that perform transformation functions inside the DB2 database and mining flows that perform easy data mining discovery and visualization
Control flows, which sequence data flows and mining flows, integrate external commands, programs, and stored procedures, and provide conditional processing logic
OLAP cube models, including recommendations for materialized query tables (MQTs).

The Eclipse Workbench provides some inherent benefits, including import and export capabilities for project management, online update features for new plug-ins, a dynamic help system, and the ability to integrate a ClearCase or CVS-based version control system.

DWE Administration Console

The Administration Console is the DWE Web client, providing a common administration environment for data warehouse applications. The console is hosted by WebSphere Application Server and recognizes data sources and system resources already defined in the WebSphere environment.

Applications and models that warehouse architects have developed and tested in the Design Studio are deployed to WebSphere, where administrators and operators can schedule and monitor jobs on production systems.

These two new tools make key tasks much easier for warehouse architects and administrators. I’ll explain how these tools make some key tasks easier.

Physical Data Modeling

RDA-based physical data modeling features provide the metadata foundation for warehouse sources and targets. You can reverse engineer a model from either a DDL script or a database connection. You can also create new database objects, add constraints, and compare the structure of different objects.

After creating or reverse-engineering the model, you can drag and drop tables and other objects into data flows and mining flows. You can also connect to live data sources via JDBC, sample the contents of tables, and deploy generated DDL scripts (see Figure 1).

SQL Warehousing Tool

Data warehouse architects use the SQL Warehousing Tool (SQW) to build and maintain database tables and other analytic structures. Design functions are exposed to users in the Design Studio; deployment and scheduling functions are exposed in the Administration Console. The driving force behind these functions is a SQL code generation system optimized for the DB2 engine.

SQW data flows provide an extensive set of data transformation “operators.” The core transformations correspond to SQL operations, such as table joins, UNION, and GROUP BY. Several operators offer specialized warehouse building capabilities, such as slowly changing dimension updates, key lookups on dimension tables, and fact key replace operations that update surrogate keys in a warehouse fact table (see Figure 2).

Data flows are easy to develop as graphical free-form assemblies of operators, with specific properties defined at each step (see Figure 3). Reusable “subflows” can be created to capture units of work that can be shared across flows; interim results can be staged at different points in a flow; and the generated code can be tested and inspected before production use. You can define variables for properties such as schemas, tables, and files, then supply the correct information at runtime, based on the specific requirements of the job.

You can set properties to take advantage of the Database Partitioning Feature for expensive operations such as select distinct processing and key lookups. SQW can generate bulk loads from flat files or relational sources, using the native DB2 LOAD and MERGE utilities. For example, the code in Listing 1 uses a SQL merge operator to perform an “upsert” operation into a fact table based on two conditions that define the criteria for updating and inserting rows.

Before deployment, data flows are packaged inside control flows, which dictate the sequence in which data flows run and define error handling, iterative processing, and other activities (see Figure 4). You can run mining flows, SQL scripts, and DB2 stored procedures; embed WebSphere DataStage ETL jobs that are run by a DataStage server; and invoke DB2 utilities such as REORG and RUNSTATS.

Control flows are deployed inside data warehouse applications on the WebSphere Application Server machine, making them visible to the Administration Console for scheduling. Role-based users can see and work with applications in different ways. Each control flow becomes a manageable process, and each process instance that you schedule is equivalent to a job (see Figure 5).

Who benefits from using SQW? Here’s a short list:

Existing DB2 customers who build warehouses with hand-coded SQL and stored procedures
New DB2 customers who have database skills and data sources that tend to be flat files and relational tables
Customers building a layered warehouse architecture, with a conventional ETL system populating tables from external sources at the detail level
Customers who want to automate tedious DBA tasks for warehouse maintenance.

SQW supports a full life cycle of warehouse building tasks, from physical data modeling through to the scheduling and monitoring of repeatable processes that maintain the performance and business access layers of the warehouse. Data mining and multidimensional cube objects, as well as any other in-database structures, can be reliably staged and maintained.

Integrated Data Mining

Data mining discovers patterns and unexpected insights that are hidden in large data sets. You can interactively create and visualize a mining model in the Design Studio to gain valuable insights about the data in your organization’s warehouse. You can generate SQL code to compute a mining model or to deploy the model’s related scoring function.

DWE 9.1’s Intelligent Miner component offers a framework for building and visualizing data mining models. These models can make use of several different data mining functions:

Associations. What are the affinities in the data? Which products sell together? What combination of items does your supermarket basket contain?
Sequences. What kinds of sequential patterns occur? What will customers buy next, given information about what they are buying now?
Clustering. Which groups or segments appear in the data? What kinds of similarities exist? What are my customer profiles?
Classification. How is the data classified and what categorical predictions can be made based on those classifications? Which types of customers are likely to default on a loan? What is the likely outcome of this treatment?
Regression analysis. How is the data classified and what numeric predictions can be made? How much money will each customer spend this year? How likely is it, in numeric probability terms, that a customer will buy this item?

The integration of data modeling, SQL-based data flows, and mining flows in a common data warehouse project makes data mining in the Design Studio less of a technological challenge. Users who are familiar with data flows use the same drag-and-drop interface to build mining flows without writing code or learning complex statistical algorithms (see Figure 6).

For preprocessing steps, mining flows use many of the same operators as data flows, such as table sources, order by operators, and joins (see Figure 7).

The interactive flow editor provides stepwise execution, and data exploration features present an overview of the content and quality of your data. You can work from design through to the development of embedded applications that produce reports based on mining models.

OLAP Modeling

You can use Cube Views OLAP modeling features directly in the Design Studio to create metadata that describes your data in a multidimensional model or cube model. This metadata can also be used to create recommendations for materialized query tables, which contain aggregated data for query optimization.

In the Design Studio, the foundation for a cube model can be the same physical data model that you use to build data flows and mining flows. Based on the physical model, you can define all of the OLAP objects you need, including fact and dimension objects, measures, and hierarchies. You can also import and export OLAP metadata.

DB2 Alphablox

DB2 Alphablox is a custom development platform for BI applications that are visible to business executives and analysts through web pages and portals. You can create customized analytic components that are embedded into existing business processes and web applications. Business users depend on these portals and can take real actions based on the analysis of large volumes of warehouse data.

Developers can assemble applications by customizing modular, reusable Blox components that provide solutions for data access, user interfaces, HTML forms, business logic, and more. The DB2 Alphablox programming model consists of:

An extensive set of JavaServer Pages (JSP) custom tag libraries
Server-side APIs to Blox components based on JavaBeans technology
Server-side access to the Blox user interface model
JavaScript methods to invoke server-side APIs from web browsers.

Developers don’t need to know the technical details behind the Blox components — just the syntax and functionality that they provide. Developers can build a DB2 Alphablox cube based on Cube Views metadata and use the cube as the foundation for building and viewing custom reports. They can also generate dynamic, interactive reports from relational data sources — for example, an application that invokes a data mining model, extracts data from the model, and displays reports in a portal.

DB2 9 and DWE

A DWE 9.1.1 refresh available this quarter upgrades the DWE package to DB2 9 and addresses requests from early DWE 9.1 adopters. DB2 9 includes a number of critical enhancements in its role as the underlying engine for DWE, including self-tuning memory management, range partitioning, and row compression. To learn more about these features, search for “Software Announcement 206-128” on ibm.com.

Intelligence On Demand

DWE 9.1 removes complexity as a barrier to enterprise data warehousing. Measurable benefits include time to value, managed cost for warehouse-based analytics, and avoidance of multiple purchase justifications for BI tools. In this regard, DWE 9.1 is closely aligned with the IBM Information On Demand initiative, providing customers with reliable information that is readily available and easy to manage throughout the data warehousing life cycle. Customers can use this information to make fast, strategic business decisions and sustain success in the marketplace.

LISTING 1. SQL merge code generated from a data flow.

MERGE INTO DB2ADMIN.NEW_PRCHS_PROFILE USING ( SELECT STR_IP_ID AS STR_IP_ID, PD_ID AS PD_ID, TIME_ID AS TIME_ID, NMBR_OF_MRKT_BSKTS AS NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS AS NUMBER_OF_ITEMS, PRDCT_BK_PRC_AMUNT AS PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS AS CST_OF_GDS_SLD_CGS, SALES_AMOUNT AS SALES_AMOUNT FROM DB2ADMIN.STAGE_PRCHS_PROFILE INPUT_051) INPUT_01 ON (DB2ADMIN.NEW_PRCHS_PROFILE.STR_IP_ID = INPUT_01.STR_IP_ID) WHEN MATCHED AND DB2ADMIN.NEW_PRCHS_PROFILE.SALES_AMOUNT > 10000 THEN UPDATE SET (STR_IP_ID, PD_ID, TIME_ID, NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS, PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS, SALES_AMOUNT) = (INPUT_01.STR_IP_ID, INPUT_01.PD_ID, INPUT_01.TIME_ID, INPUT_01.NMBR_OF_MRKT_BSKTS, INPUT_01.NUMBER_OF_ITEMS, INPUT_01.PRDCT_BK_PRC_AMUNT, INPUT_01.CST_OF_GDS_SLD_CGS, INPUT_01.SALES_AMOUNT) WHEN NOT MATCHED THEN INSERT (STR_IP_ID, PD_ID, TIME_ID, NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS, PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS, SALES_AMOUNT) VALUES (INPUT_01.STR_IP_ID, INPUT_01.PD_ID, INPUT_01.TIME_ID, INPUT_01.NMBR_OF_MRKT_BSKTS, INPUT_01.NUMBER_OF_ITEMS, INPUT_01.PRDCT_BK_PRC_AMUNT, INPUT_01.CST_OF_GDS_SLD_CGS, INPUT_01.SALES_AMOUNT) ELSE IGNORE

The author thanks Qi Jin, senior manager of DB2 Data Warehouse Edition, for his technical assistance.

Bob Rumsby has worked as an editor, technical writer, course developer, and trainer in Silicon Valley for 16 years. He is currently an information development team lead in the User Technology group at IBM SVL in San Jose, Calif.

0 Comments

Salzburg - Classical Europe and Home of Mozart

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 12:39 pm - 436 views

(No Ratings Yet)

Loading ...

The city of Salzburg is classical charm embodied. There is so much to see, and here we have a selection of our favourite sights in Salzburg.

Walk the cobbled streets of the old city, admiring the beautiful facades of buildings built before the time of Mozart.

Make sure you visit the Hohensalzburg Fortress, perched high above the old city, yet easily reached by funicular railway departing from the Festungsgasse, which is in the centre of the old city.

Mirabell Palace, boasting world-famous beautiful gardens, was commissioned to be built by Wolf Dietrich for Salome Alt in 1606. The “angel staircase” by Raphael Donner that leads to Europe’s “most beautiful wedding hall” is of special interest. The Gardens were remodelled in 1690 according to plans by Johann Bernhard Fischer von Erlach and redesigned around 1730 by Franz Anton Danreiter

Hellbrunn Palace, built in 1615, is a fine example of manneristic architecture. It was commissioned as a country residence to Salzburg’s Prince Archbishop Markus Sittikus von Hohenems in 1612. Water was the central theme in the palace’s design with the numerous streams of Hellbrunn Mountain giving the estate effervescent life. Hidden in the shade of bushes and trees or jetting out from unexpected hiding places - the world-famous Wasserspiele have been the main attraction at Hellbrunn for almost 400 years. Today, the Hellbrunn palace houses the folklore museum of Salzburg’s Carolino Augusteum Museum. The manor, idyllically situated overlooking Hellbrunn Park, houses a collection of regional folklore with objects of popular customs and piety, furnishings, popular medicine and a number of beautiful Trachten (traditional costumes) worn in the Salzburg valley regions.

Monuments to W A Mozart

Mozartsplatz, or Mozart’s Square, is dominated by a statue of Mozart, by Ludwig Schwanthaler, was created in 1842 after tearing down some houses to make way. It remains a beautiful place to sit and enjoy the city go by on a sunny summer day.

Mozart´s birthplace, located in Getreidegasse no. 9, is where the Mozart family lived from 1747 to 1773. Designed in the typical style of houses in the historical city centre, this is where Wolfgang Amadeus Mozart was born here on January 27, 1756. Today the rooms house a museum dedicated to Mozart.

0 Comments

The New Data Warehouse: From Information to Action

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 12:33 pm - 783 views

(No Ratings Yet)

Loading ...

Combining new technologies with traditional business intelligence techniques can fulfil the promise of positive business outcomes.
By Michael L. Gonzales

Powerful transaction-oriented information systems are commonplace in every major industry, effectively leveling the playing field. Getting ahead of the competition now requires analysis-oriented systems that can revolutionize an organization’s ability to use information it already owns. These analytic systems derive insight from available data and deliver information that’s conclusive, fact-based, and actionable.

Business intelligence (BI) improves corporate performance in any information-intensive industry. Most companies have the raw data and the people knowledge BI requires. Operational systems generate vast quantities of product, customer, and market data from point-of-sale, reservations, customer service, and technical support systems. But the challenge is to extract and exploit this data, transforming it into information and actionable insight.

Many companies take advantage of only a small fraction of their data for strategic analysis, let alone tactical applications. The remaining untapped data — often combined with data from external sources such as government reports, trade associations, analysts, the Internet, and purchased information — is a gold mine waiting to be explored, refined, and shaped into information. This knowledge can be applied in a number of ways, ranging from charting overall corporate strategy to communicating personally with vendors, suppliers, and customers through call centers, kiosks, billing statements, the Internet, and other touch points that facilitate genuine one-to-one marketing on an unprecedented scale.
Today’s business climate requires the BI environment to evolve beyond the implementation of traditional data warehousing tools and techniques. A fusion of traditional and advanced technologies is necessary to support a broad analytical landscape and serve up a rich blend of historical trending, real-time reporting, and predictive analytics. Finally, the overall environment must improve the knowledge and performance of the enterprise as a whole, ensuring that actions taken as a result of analysis are fed back into the environment.

Business Value

Every BI environment, to achieve real and significant impact, must be designed and built in the context of the business value that it provides. The value connection is expressed in terms of business capabilities that will be provided or improved through intelligence. Figure 1 shows the BI value chain, which turns data into information. To make this transformation happen, BI architects need to fulfill these requirements:

Align information with the knowledge of the individuals or work groups to whom it’s provided. The information-to-knowledge connection is one that most IT people find difficult. Knowledge is unique to an individual, the product of personal experience, recall, instincts, and beliefs. When information consumers are at the executive level, it is important to align one-to-one information links. When providing information to larger groups, profile the knowledge of the target groups relative to the business, the information subjects, and skill level. This profiling is the basis for customized actionable information products and services.
Combine knowledge and information used to take action. The term “actionable information” is pervasive throughout BI literature. But what companies really need is not actionable information, but actionable insight. Action is a process of doing something. All too often, BI architects look only at the event and not the activities and behaviors that lead to the event. Any combination of insight, resolve, decision, and innovation may drive a person to act. Information is actionable and promotes insight when it supports the entire process of taking action. It’s the essential bridge from integrated data to positive business outcomes — the promise of BI.
Enable informed actions that lead to positive outcomes. Favorable business outcomes are generally those that reduce cost, save time, optimize resources, increase revenue, satisfy customers, or otherwise help to fulfill business missions and goals.

How actionable your information is depends on the form in which it’s provided and the kinds of information services or business information capabilities that are offered. It’s critical to match the information services with the needs, knowledge, and abilities of information consumers.

Getting business value from BI requires a view of business domains that are within the scope of the BI effort. Value-creation opportunities may arise from many business domains, including strategy and planning, financial management, research and development, marketing, sales customer support, operations, human resources, information systems, and corporate governance.

The Art of Decision-Making

When most companies think about using BI to support better business decisions, they’re usually considering it from the perspective of strategic decision-making. However, as the BI space matures in technique and technology (and user demands grow), BI continues to evolve. Today, there is significant attention and interest in supporting tactical decision-making as well. But strategic and tactical aren’t the only types of decisions made in an organization. Many argue that there’s a third type: operational decision-making. Table 1 shows definitions and examples for the three categories.

The end game for BI isn’t simply exposing actionable information and insight. It’s also ensuring that action is taken to improve business performance. The challenge is not only what information to combine with what knowledge, but also how to ensure action.

For strategic and tactical decisions, any action taken requires human intervention. Curiously, there may be considerably less effort on the BI team to service these types of decisions. The BI team may need to build a data store and install tools that allow users to perform their own research for insight (an OLAP cube implemented for a subject matter expert, for example). The BI team often focuses only on providing the cube and tools and leaves the user to find any actionable insight.

Operational decisions can be, and often are, automated. Figure 2 shows the direct relationship between the decision category and the amount of human intervention required. Because these decisions are often automated, more research and implementation work rests on the shoulders of the BI team.

Strategic decisions (such as those regarding expansion) have broad implications for the direction of the organization as a whole. Answers to these types of questions are rarely derived from a purely automated environment. Even when building simulation models, subject matter experts must still evaluate the results and formulate actions to be taken. Tactical decisions are focused on managing processes, such as evaluating and establishing the level of risk the organization is willing to assume for specific loan products.

Operational decisions, however, are the most fundamental. They address individual transactions (such as whether a loan is approved or not). And, they likely represent the highest number of decisions made on a day-to-day basis. It’s precisely for that reason that operational decision-making can and should be targeted for automation.

Valuable Insight

In order to provide BI value, business architects must understand the kinds of decisions made in organizations, including strategic, tactical, and operational.

Each category provides clues as to the type of action process that’s feasible. Strategic and tactical decisions are often best suited with some human intervention. Once a decision has been made, it’s possible that the action process is a composite of several disparate adjustments to operations. On the other hand, operational decisions can often be fully automated and the subsequent actions made part of an inline process.

Content for this article was adapted from BI Strategy: How to Create and Document , by Michael Gonzales and David Wells, HandsOn-Press, 2006.

Michael L. Gonzales has been a BI data architecture and solutions strategist for more than a decade. He teaches a series of courses internationally through HandsOn-BI LLC and has written books on data warehousing and business intelligence.

0 Comments

Vista helps Microsoft shrug off Xbox charge

22 Jul

Posted by ProCOM bloggeraz on July 22, 2007 – 12:21 pm - 313 views

(No Ratings Yet)

Loading ...

A billion-dollar payout to disgruntled Xbox owners shaved $0.08 off Microsoft’s earnings per share