IBM Lotus Domino 7 DB2 integration feature on iSeries

A customer was asking me last night about the new NSFDB2 function on Domino (but, for the iSeries). I started to do some research and found this document about the DB2beta for iSeries.

So, the first I said was "wait a little more.... but.. if you want this.. plan to upgrade to V5R4".

In our conversation I also talked about an interesting thing you could do if you have Domino 7.x and DB2 as your data store, maybe It will not be possible, or IBM will not allow to do it, or I am just thinking too much...! but here we go...

What's the Plan Cache ?

The Plan Cache is a repository that contains the access plans for queries that were optimized by SQE. It contains a wealth of information about the SQE - SQL Query Engine - queries being run through the database. This Plan Cache interface provides a window into the database query operations on the system. Link to the IBM InfoCenter.

What is the SQE (SQL Query Engine) ?

Link to the InfoCenter


What is the Visual Explain ?
It is a tool which comes with the iSeries™ Navigator that graphically displays the implementation of an SQL statement. Link to the Infocenter

More Info about related topics...

---

Now, let's go with my point... Example: If you have a Notes View fetching DB2 data , and you think it's slow and/or could be faster, just go to the Operations Navigator and look for the option "SQL Plan Cache Snapshots".

Right-click the Plan Cache icon and select "Show Statements". This screen provides a direct view of the current plan cache on the system. Here you can use the filtering options to find the query you need to optimize.

After this point, you can use the Visual Explain options (Index Advisor for example) to see what you can do to improve your time response. I've seen complex queries from 20 minutes to 20 seconds (of course, it was not a Notes view !!).

Key points to take home..

• Plan Cache is cleared after IPL.
• Maybe you could see what Domino is doing when it's connecting/inserting/editing/etc... with DB2.
• Visual Explain is a great tool. Try it !
• Plan Cache was implemented in V5R2, but, you can really look inside after V5r4.
• More and more SQL keywords are being added to the SQE.
• Plan Cache is an actively changing cache. Don't wait a month to check a SQL Statement.
  
  
  
  
  
  
  

Travel to Rochester coming soon !

Just a few weeks !!!! Starting July 17th, I will be at IBM Rochester (the Big Blue building) writing the "Implementing Domino 7 on the System i" redbook. This is the first time I will go to Rochester and will not need lot of jackets, gloves, etc (my previuos travels were on October to December)...

Rochester is a very nice small city located south of Minneapolis, and it is the home of the IBM iSeries family, and the Mayo Clinic. If you need to go to this city check this site http://www.visitrochestermn.com . I also will stop in Chicago for one day to visit my brother in law Gabriel, he will be so happy to eat Venezuelan bread (in fact San Cristobal's bread !!!), Samba, Cocosettes.

Notes Domino 7.0.2 Preview

Innovation – enhancing business value

IBM/Lotus showed some of the new resources you will find in the next release of Domino. They want to be clear that this is not just a maintenance release, it really includes new stuff... I saw "Lotus Nomad - USB" at Lotusphere '06 and I can't wait to see it in a production environment.

I also recommend this interview Bonus for Lotus Notes/Domino users: Upcoming maintenance release goes beyond fixes with great new features with Penny Scharfman and Mike Mottola.

Domino on iSeries useful commands

When I started to work with Domino on the iSeries platform, the first thing I needed was the command to see the Domino Console using the "Green Screen". If you are new with Domino administration on iSeries, don't panic, but, you will need to know the iSeries specific commands for Domino. Here is a list with the commands you should use at least once:

• CFGDOMSVR - Configure or Remove a Domino server
• CHGDOMSVR - Change a Domino server
• ENDDOMSVR - End a Domino Server
• DSPDOMCSL - Display the Domino Console
• RUNDOMCMD - Run a Domino Command
• SBMDOMCMD - Submit a Domino Command
• SETDOMENV - Set Domino Environment
• STRDOMSVR - Start a Domino server
• UPDDOMSVR - Update a Domino server
• WRKDOMCSL - Work with the Domino Console
• WRKDOMSVR - Work with all the Domino servers

There are other commands (iSeries platform) you can find very helpful:

• WRKACTJOB - Work with Active Jobs. Very useful to see the performance!
• WRKSYSSTS - Work with the system. Very useful to see the performance!
• CFGTCP - Configure TCP. Very useful.
• WRKDSKSTS - Work with Disks. Very useful.
• WRKSBS - Work with Subsystem
• DLTLICPGM - Delete Licensed Program
• DSPSFWRSC - Display Software Resources
• LODRUN - Load and Run Media Program
• RSTLICPGM - Restore Licensed Program

If you don't remember a specific command, write the first letter with a "*" and press Enter, you will see an entire list with the commands starting with that letter.

Domino tips for the iSeries

Even Domino runs great in all the platform, from my personal perspective, iSeries is one of the top platforms to have Domino. However there are few (I will mention a few of them) considerations to check in order to have the best result. If you are planning or you already have Domino 7 on the iSeries, I would recommend upgrade to 7.0.1. due I found a few problems with the Http task and DWA in 7.0.

Memory Managment

Using the command sh st database you will be able to see the database statistics needed to know if your Domino servers have been properly configured. Check the Domino 6 for iSeries Best Practices Guide Redbook the see the recommended values. Please note that you will need a few days to really tune your server due most of the statistics values need to be running at leats for3-6 hours. Also, remember to check these values in you peak hours if you really want to know the real workload of the server.

• Database.Database.BufferPool.Maximum.Megabytes
• Database.Database.BufferPool.Peak.Megabytes
• Database.Database.BufferPool.PercentReadsInBuffer
• Database.DbCache.CurrentEntries
• Database.DbCache.HighWaterMark
• Database.DbCache.MaxEntries
• Database.DbCache.OvercrowdingRejections

DWA

If you are using DWA, note the Inbox (most cases) will be the first view showed when a user enters to the mail database, in this case, if your users have lot of documents in the inbox, the machine will use more CPU. Remember to explain to your users that a clean Inbox will improve their user experience (and response).

DWA + SametimeIf you have any problem to have DWA + Sametime working, please check:

• Always use FQDN (fully qualified domain name).
• Test the SSO configuration
• Check your proxy settings

Because in Notes Domino 7 most of the ini variables needed to use the IM and DWA were moved to the Configuration Document, check you notes.ini file and clean it.

Complete steps to configure Sametime Integration with DWA 7.x

After you upgrade to Domino 7, the field "Set an Instant Messaging server hostname for all DWA users" will be set to - 1 - (at least in the upgrades I have done), go and change it to your Sametime server fqdn, if not, DWA will be really slow, because it will try te connect to the server named "1".

Check the following IBM Redbooks for more info:

• Domino 6 for iSeries Best Practices Guide
• IBM Lotus Domino 6 for iSeries Implementation
• Domino 6 for iSeries - antivirus solutions

First entry !!!!!

This is the first time I have my own blog, so, I think the best way to start is writing a little bit about myself:

My names is Luis and I've been involved in the Lotus/IBM area since college in 1999, I am currently located in San Cristobal, Venezuela but I will move to the US in the next months because of a new job (I am really excited about this new chalenge and I will give more details later). I am a Dual R5, R6 and ND7 IBM Certified Advanced Application Developer and System Administrator for Lotus Notes and Domino. I also have a certification in Collaborative Solutions (Lotus Workflow) and as member of the IBM Start Now program for eBusiness (Does it still exist ?).

I co-authored (with other great team members) the following IBM Redbooks:

- Deploying IBM Workplace Collaboration Services on the IBM System i5 Platform
- Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
- SQL Performance Diagnosis on DB2 UDB for i5/OS (being edited)
- Automating Business Processes using Collaborative Workflow on Linux/Windows (this is not a redbook and I am not sure if I can provide access to it without IBM authorization)

There is a new book I will co-author next July/August in Rochester, MN - Implementing Lotus Domino 7 on the System i - I will try to provide more information later.

Finally I can tell that I just got married with a beatiful/smart/sweet women named Maria Eugenia (Mariu).