1. Introduction

Recently I made a few tests at a client's site on SQL performance with large volumes of data. I found quite interesting results that I summarise below.

2. Application Description

The client is a financial company which stores large volumes of data for a Basel application which provides a month by month situation of the contracts. The data are used for some standard reporting and also for non standard OLAP inquiries requested especially by Credit control users

The application updates 7 different physical files which are joined together in a join LF called BASUNICO1L which has a total record length of 1789 characters. The main keys of this file are the processing period (YYYYMM) and the contract number

3. Test Environment

The tests were made in a test environment where the files contained the data of about 6 months. The total number of records for the join LF were approximately 11 million and those of the period used for the test were about 2 millions.

The first set of tests were made to test the times needed to copy all records of one period from the join logical file to a physical file with the same record layout. The record were written by using three different approaches as follows:

A simple copyfile (CPYF) which includes a selection such as INCREL(*IF PERIOD *EQ 200806)
A traditional (file oriented) Cobol program (TSTFIL1) which used a START to position the file pointer to the first record of the period and then entered into a loop of READ and WRITE operations to write to the output file all records of the requested period.
A Cobol program with embedded SQL (TSTSQL1) which wrote the output records with a simple SQL Insert of the selected records.

A Cobol program with embedded SQL (TSTSQL3) which created the output file as an SQL MQT table. A second set of test was used instead to test the times needed to read some fields of the files by using different approaches as follows:

Traditional file oriented Cobol program (TSTFIL5) which performed a START to position the cursor and then used sequential READ operations of all records of the selected period. Cobol program with embedded SQL (TSTSQL5) which included an SQL cursor to read all lines of the selected period. I decided to perform the tests in the following different conditions:

- In an environment without additional indexes (i.e only the access paths of the files)
- In an environment that could use also additional SQL indexes
- In an environment that used an SQL logical view instead of the original join logical file
- The results are described in the following points

4. Tests based only on the join logical file

The test made in the original environment to copy about 2 million records without SQL indexes were the following:

- The CPYF took 19 minutes and 35 seconds
- The traditional file oriented Cobol program took 9 minutes and 20 seconds
- The Cobol program with embedded SQL took 28 minutes

5. Tests done after the creation of SQL indexes

I checked the index advisor file QSYS2/SYSIXADV after the initial tests and I noticed that there was a record suggesting to create an index on the period field. I created both a vector index and a radix index and then started the new set of tests. The results were the following:

- The CPYF took 7 minutes and 52 seconds
- The traditional file oriented Cobol program took 7 minutes and 7 seconds
-The Cobol program with embedded SQL took 13 minutes and 20 secons
- There has been a clear benefit especially for the Cobol program with embedded SQL. By loooking at the log, I noticed that the optimiser had chosen to use the vector index during that execution.

It is interesting that if you use the commands WRKOBJ or DSPFD or DSPDBR, the indexes appear as logical files with a special SQL type attribute which has the value INDEX.

It is also interesting that the space used for the indexes is much less than what is required for a logical file. In my test environment one of the main the PF used about 500 Mega bytes, a LF used about 257 Mega bytes, the radix index used occupied about 175 Mega bytes and the radix index only 22 Mega bytes.

6. Test of the file creation with as an MQT (Materialised query table)
I tried to create the target file by using a Cobol program with embedded SQL that produced the output table as an MQT instead of the previous INSERT and I found that the time were very good

The program took just 57 seconds to produce the results.

7. Reading Test

I compared the time spent by two programs to read from all records of a selected period to sum up a total The first program was a Cobol traditional file oriented program, whereas the second one was an SQL Cobol which included an SQL cursor to read all selected records.

The first program took 13 minutes and 17 seconds to complete, whereas the second one took on the first execution just 7 minutes and 14 seconds to complete and much less in following ones.

8, Using SQL Views instead of Logical files

I tried to use an SQL view equivalent to the logical file and repeat the tests above by using the SQL view. The results were not significantly different than those based on the logical file, however I found that a view required much less space than an equivalent LF.

The SQL views appear again to the system as special logical files with the SQL type attribute containing the value VIEW. The space occupied by the view logical files is much less than that required by a logical file. In my test environment a view equivalent to a join logical file of about 405 Mega bytes. required less than 1 Mega byte.

9. Conclusions

The tests described above seem to demonstrate what follows:

The fastest option to extract the data was the Cobol program with an embedded SQL to create a MQT table.

The traditional file oriented Cobol program was faster than the corresponding Cobol program which included an embedded SQL INSERT statement. The execution time of the Cobol SQL program was significantly affected by the creation of the SQL vector index.

The traditional file oriented Cobol program that read all records of a selected period by using START and READ NEXT operations was slower than the Cobol program which read the records with an SQL cursor.

The results agree with some points of an IBM Redbook Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone ( redbooks.ibm.com/abstracts/sg246393.html?Open ). where it is written that:

The SQL insert operations are slower than Cobol write statement because SQL operations include more validations than write oprrations into a DDS PF SQL does faster reads than HLL operations. The main reason is that a cursor reading an SQL table does not have the extra data cleansing code like a DDS PF reading. Using SQL views instead of logical files should allow a significant reduction of the space occupation. The final conclusion is that a wise use of SQL can bring significant improvements in applications performance.

Scenario: Multiple IBM i5/OS logical partitions using the HMC modem and the modem on an i5/OS logical partition

This scenario demonstrates how to configure multiple i5/OS® logical partitions to connect to service and support through the modem on the i5/OS logical partition and to enable the HMC to connect to service and support through its own modem. In the event the HMC modem is busy or unavailable, the HMC can use the modem on the i5/OS logical partition to connect to service and support. Or, in the event the i5/OS modem is busy or unavailable, it can use the modem on the HMC to connect to service and support.

Situation

If you are responsible for maintaining servers at your company, one of your roles might be to establish the connections within your network and to the service and support organization so that your servers can access service and support resources. For this scenario, you are using an HMC to manage your server, and your server is divided into multiple logical partitions running multiple operating systems. You want to enable the server to use the modem on the HMC or the modem on the server to connect to service and support. In the event the HMC modem is busy or unavailable, the HMC can alternatively use the modem on the i5/OS logical partition to connect to service and support.

Objectives

In this scenario, you want to ensure that your company's server can receive support from the service organization when requested by your company's network administrator. The objectives of this scenario are as follows:

To set up the HMC to connect to service and support
To configure the i5/OS logical partition (non-service partition) to use the modem on the i5/OS service partition to report i5/OS problems to service and support, and as a backup, to use the HMC modem
To configure the i5/OS logical partition that has the modem for dial-up connection to service and support, and as a backup, to use the HMC modem
To enable the logical partitions running Linux®, AIX®, and i5/OS (non-service partition) to use the modem on the HMC to report service information, problems related to server hardware or firmware (Licensed Internal Code), and problems related to Linux and AIX to service and support
Details

The following figure illustrates the flow of service information and problems through the service connection to service and support.

Figure 1. This diagram shows the flow of information and problems from four logical partitions and the HMC to service and support. The information and problems pass through the modem on the HMC or through the modem on the i5/OS logical partition.



The figure illustrates the following points relevant to this scenario:

The HMC setup is complete.
The server has four logical partitions with the following operating systems installed:
Linux
AIX
i5/OS
i5/OS (this is the service partition)
The PPP connection is configured on the service partition and connects to service and support.
The logical partitions use either the modem on the HMC or the modem on the service partition to connect to service and support.
Service information and problems flow from each logical partition to service and support using a modem connection, as follows:
Linux logical partition > HMC > Service and support
AIX logical partition > HMC > Service and support
i5/OS logical partition > HMC (for service information) > Service and support
i5/OS logical partition > i5/OS service partition (for i5/OS problems) > Service and support
i5/OS logical partition > HMC > Service and support (backup)
i5/OS service partition > Service and support
i5/OS service partition > HMC > Service and support (backup)
Note: If the HMC modem is busy or unavailable, the HMC and logical partitions can alternatively use the modem on the service partition to send all service information and problems to service and support.

Prerequisites and assumptions

Successful implementation of this scenario requires that all necessary hardware planning and setup tasks are complete. Additional prerequisites and assumptions are noted in the appropriate places within the configuration tasks.

Configuration steps

You must complete the following tasks:

Ensure that all prerequisites are met for your connection method. Refer to Task 3. Prerequisites.
Ensure that your physical networking is set up correctly. See Task 4. Ensure that your physical networking is set up correctly.
Obtain or verify your IBM® ID. See Task 5. Obtain or verify an IBM ID.
Set up the HMC to connect to service and support. You can use either of the following methods to set up the HMC.
Guided Setup wizard (recommended method): The Guided Setup wizard is a tool on the HMC designed to guide you through the steps of setting up the HMC, including connectivity from the HMC to service and support. Although you typically use the Guided Setup wizard when you first set up your server, you can also use it to verify that your connections from the HMC to service and support are set up correctly. For details, see Task 6. Verify the HMC service settings.
Manual setup: If you prefer to use the manual method to create your service connections from the HMC to service and support, see Manually set up the HMC to connect to service and support.
Set up and configure the logical partitions. For details, refer to the Partitioning the server topic.
Install i5/OS on your system or logical partitions. For details, refer to the Installing operating systems topic.
Configure your TCP/IP network. For instructions, refer to the operating system documentation.
Activate TCP/IP on your logical partitions. TCP/IP starts automatically, as long as the network adapter is recognized and can communicate with the network when the i5/OS operating system is started.
Note: If TCP/IP does not activate, type STRTCP at an i5/OS command line to start TCP/IP. This command initializes and activates TCP/IP processing, starts the TCP/IP interfaces, and starts the server jobs. Only TCP/IP interfaces and servers with AUTOSTART *YES are started with the STRTCP command.

Configure PPP connection from the i5/OS service partition to report i5/OS problems to service and support. See Task 11. Configure Electronic Service Agent for i5/OS.
Configure PPP connections from the i5/OS logical partitions to use the modem on the service partition to report i5/OS problems to service and support. See Task 11. Configure Electronic Service Agent for i5/OS.
Make sure that the HMC modem is set up to allow use by the logical partitions.
Register the IBM ID for i5/OS. See Task 12. Register the IBM ID for i5/OS.
Verify that the connection to service and support is set up correctly and that information is being transmitted correctly. See Task 13. Test the connection to service and support.
View information that was shared with IBM. See Task 14. View the server information that was reported to IBM.
Parent topic: Scenarios: IBM i5/OS

Document Title
IBM® iSeries™ Access for Windows PC5250 Configuration for HMC Remote Console Connections

Document Description
This document provides detailed instructions for configuring the 5250 emulator included with IBM® iSeries™ Access for Windows for a HMC remote console connection. This configuration is for a non-ssl connection to the HMC. The configuration does not apply to other emulators such as the IBM Personal Communications product. For further information, refer to the IBM® eServer™ Information Center topic Connecting to a 5250 console remotely at the following Web site:

http://ping.fm/UlDGl

Step 1: Verify iSeries Access for Windows Code Level

The iSeries Access for Windows emulator being used must be at Version 5 Release 3 Service level SI13587 or later. To verify the service pack level, select Start > Programs > IBM iSeries Access for Windows > iSeries Access for Windows Properties.




Step 2: Configure the PC5250 Remote Console Session

1 Select Start > Programs > IBM iSeries Access for Windows > Emulator > Start or Configure Session.
2 From the IBM Personal Communications - Session Manager dialog that appears, press the New Session... button.
3 In the Configure PC5250 dialog:
a Update the System Name to the HMC host name or TCP/IP address.
b Set the port number to 2300.
c Then press the Properties button.


4 The properties button will launch the Connection dialog shown below.
a Set the User ID sign on information to Use default User ID, prompt as needed.
b Set the User ID to Q#HMC.
c Set the Security to Not secured.
d Click OK, OK.


5 Save the profile.

To save the workstation profile configuration for future use, click the Menu option, File then Save. Enter a profile name and click OK. The workstation save creates two files. Both file names are the same as the profile name with extensions of .ws and .cae.

Note: Do not move or copy only the workstation profile file (extension .ws). Moving only this file will result in the loss of the connection information, which causes a CWBCO1048 connection error. When possible, create a shortcut to the profile rather than a copy. If the profile must be moved or copied, copy both files to the new location.
Step 3: Verify the HMC Firewall Configuration

The firewall must be enabled for remote console, regardless of the type of emulator used. This information is not specific to the iSeries Access for Windows product.

1 In the navigation area, expand the HMC you want to work with. HMCs are listed by host name or TCP/IP address.
2 Expand HMC Management.
3 Click HMC Configuration.
4 In the contents pane, click Customize network settings.
5 Click the LAN Adapters tab.
6 Select the LAN adapter you want to work with and click Details.
7 Click the Firewall tab.
8 Select the 5250 application in the top table. Click Allow Incoming to allow all TCP/IP addresses or click Allow Incoming by IP Address to allow only specific addresses. Click OK. 5250 should now display in the Allowed Hosts table.

If you really believe that your backups are sound, would you be comforable erasing everything on your hard drive right now, and restoring it from backups?

If not, why not?

No matter how sophisticated or comprehensive your backup system is, you will never know if it works unless you actually test it. Without testing, you can have no confidence at all. Here are just some things that can go wrong with backups through no direct fault of your own:

Failed backup software: The backup software simply fails and when you restore the data from the backup media, many files are missing or corrupted. It's not good enough to rely on the backup software to tell you that it has securely written a tape. In a real and recent case, involving one of the most popular backup products on the market, a tape could not be properly reloaded, even though it was written with the "verify" option turned on.

Incomplete data: You discover that someone in a hurry to do a backup one day configured the backup software to backup just their directory, and never got around to resetting the configuration. So your twenty-seven backup tapes all consist of a copy of just one directory.

The magnet: You store your backups in a safe-deposit box in a bank. However, someone has stored their very precious collection of rare magnets in the box next to yours.

Encryption: Back in the dark ages when the backup software was installed and configured, someone set it up to write encrypted backup tapes. Now nobody knows what the password is.

Old version: You find you can't reload an old backup tape because it can only be read by an earlier version of your backup software which you recently upgraded.

Poor quality media: You discover that the media you're using for your backups is of poor quality and you can't get the data off it after six months.

Compression: The new compression feature in the latest version of your backup software turns out to compress better than anyone thought...

Upgrade: Your backups appear to be working. You even tested one once and it worked. But you upgrade your operating system and a bug in the backup software manifests itself, causing the backup to fail.

Tape breakage: Your backup tape gets broken during the backup and you send it offsite without noticing.

Backup crash: You set a backup going one night. The next morning you come in and find the tape on your desk. A friendly work collegue has taken it out of the drive and left it for you. What they didn't tell you was that they found the machine rebooted. The machine crashed during the backup and the tape drive automatically rewound the tape.

These are just some of the things that can go wrong. The important thing is to realize that there is a huge number of things that can go wrong with a backup operation. Some of those things will be intermittent. Others will be systemic, meaning that all your backups will be useless.

The only way to tell if your backups are working is to actually load a backup tape and see if the data is correctly restored. This may be difficult or impossible for many users who have filled their hard disk to capacity. However, testing your backups is such an important activity that it really is worth either reducing your disk usage to less than half of your hard disk, or finding another hard disk, so that you can perform a test restore.

If you feel that such testing is unnecessary, ask yourself if you are ready to face the backup challenge: would you feel comfortable erasing your hard disk right now, and restoring it from your backups? If not, then think again.

Programming modernisation RDi

Wanting to know just how it feels to use the new modern i5 rational developer tools, but not sure a) how to set it up b) Where to start c) What it can really do for you?

Are you confused by the flood of information and powerpoint presentations provided by IBM on this?

Why not save yourself the trouble of installing and trying it by using our on-line service, using real tools, properly installed connected to an i5 and be free to try any code you wish?

Its easy with our RDi sampler service, you can look at our demo source code an try a proper windows environment with these tools, right now

We can provide access for a day to a few months, we can also provide training and trouble shooting to ease you through the process


Why choose Mid-Blue?

We have proven and extensive knowledge on all aspects of IBM mid-range hardware and software, we are the company people to turn to for no nonsense advice and help when they need it most. 01322 407000

 

do you think your backup tape works then?

Cool Tape Tester should be working very soon, i have high hope for this www.tapetester.com