FHG Archives - Page 75 of 144

Load a Spreadsheet from a DB2/400 Database: Part 3

January 28, 2009 Michael Sansoterra

Note: The code accompanying this article is available for download here.

Part 1 and Part 2 of this little series on generating an Excel spreadsheet demonstrated how to use Visual Basic for Applications (VBA) code to retrieve data from DB2 on an AS/400 and place it in a spreadsheet. Additional refinements were offered on doing some fancier things to the spreadsheet, such as auto-sizing all the columns based on the loaded data, formatting date columns properly, etc. All of this was done in the name of programmatically creating a nice spreadsheet with current “DB2 for i” data. This tip

…

Admin Alert: Four Things to Beware of During a System Upgrade

January 28, 2009 Joe Hertvik

As I reported a few weeks ago, i5/OS V5R3 loses IBM support as of April 30th, which may cause many iSeries and System i shops to upgrade their operating systems in the next few months. If you’re currently working on a V5R3 upgrade, here are four things that you should watch out for as you plan your upgrade.

Be Aware of the Difference Between Compatible and Supported Software

For third-party software, it’s important to understand the difference between a compatible and a supported piece of software. I recently ran into this issue on an i5/OS V5R4 upgrade where the vendor

…

Redundant Join Criteria: Good or Bad Idea?

January 21, 2009 Hey, Ted

Suppose I have four tables that I commonly join. Is there any benefit to adding redundant criteria to the join? Or to the “where” clause? That is, will redundant criteria or selection expressions improve performance?

–Philip

Philip’s four tables are keyed as follows:

SITE     ITEM      PROCESS   STRUCTURE
------   ------    --------  ---------
SiteID   SiteID    SiteID    SiteID
         ItemID    ItemID    ItemID
         Revision  Revision  Revision
                             StructID

Here’s a join without redundant join criteria:

select whatever
from site as s
join item as i 
  on s.siteid = i.siteid
join process as p
  on i.siteid = p.siteid
 and i.itemid = p.itemid
 and i.rev    = p.rev
join

…

Do Your File Specifications Lie?

January 21, 2009 Ted Holt

An old joke says, “How do you know a salesman (or politician) is lying to you?” The answer: “His lips are moving.” All joking aside, your RPG IV F specs can lie to you, and it’s not obvious when they do. This is not a serious problem, but I’ve run across it on occasion and want to be sure others are aware of it.

Let’s say the following program is running:

H dftactgrp(*no) actgrp('QILE')
Fcustcdt   uf   e             disk
Ferd01p    o    e             printer
 /free
     *inlr = *on;
     dow '1';
        read cusrec;
        if %eof();  
            leave;
        endif;
        write erd01p1;
     enddo;
     return;

This is

…

Trouble-Shooting WebSM to HMC Connectivity Problems

January 21, 2009 Hey, Joe

I’m having problems connecting to different Hardware Management Consoles (HMCs) via a single Web Based System Manager (WebSM). I can connect to our production HMC, but I am having problems accessing our backup HMC in New York. I enter an IP address for the backup HMC, but it’s not accepting my user ID and password. Is it possible to access two HMCs with the same WebSM?

–Ray

Hi Ray,

Yes, it is possible to access two different HMCs through the same WebSM configuration. I do it in my shop where I use one WebSM to access three different HMCs on

…

Bypass Locked Records in SQL Queries

January 14, 2009 Dear Intelligent Colleague

This tip is a follow-up to both of the tips I published last week. Like Bypassing a Locked Record, Take Two, it has to do with locked records. Like Be Specific When Updating With SQL Cursors, it deals with SQL updates.

You may remember that there are two ways to update in SQL. The positioned update uses a cursor to update one row at a time, as we talked about last week. Updating through a cursor is similar to updating with native I/O.

The searched update, the easier and more powerful of the two, does not work through

…

Data Queues vs. MQSeries

January 14, 2009 Timothy Prickett Morgan

Does anyone have any knowledge or references related to the difference in performance between doing a put to a journaled data queue vs. doing an open and a put (no connect) to an MQSeries queue?

–James

In MQSeries, opens are expensive, so if you keep the queue open and do multiple puts, you will get very good performance. Using a data queue, you will get better performance as long as there are not too many entries in the data queue. If the data queue overflows (no telling where this might occur, but I have seen it around 100 messages), then

…

Admin Alert: Looking for i5/OS Trouble, Part II

January 14, 2009 Joe Hertvik

Last week, I discussed the best ways to automatically monitor iSeries, System i, and Power i systems for hidden signs of trouble, and I focused on monitoring the QSYSMSG and QSYSOPR message queues for developing problems. This week, I’m changing direction to discuss several specific i5/OS monitoring situations that can also help you detect system problems as they occur.

Trouble-Finding Tools

As I mentioned last week, the following are the best places to look for trouble in your OS/400, i5/OS, and i operating systems:

The QSYSMSG message queue
The System Operator (QSYSOPR) message queue
Disk drive statistics
Active job

…

Bypassing a Locked Record, Take Two

January 7, 2009 Hey, Ted

I have a slightly different solution to the locked record problem your friends tackled in Bypassing a Locked Record. Like them, I have RPG programs that sequentially read a file and update some of the records. When a program tries to read a record that another job has locked, I sometimes bypass the locked record. Here’s how.

Notice the READ operation in the second calculation of this example:

FSomeFile  UF   E           K Disk    Prefix(SR_)
                                                 
D Forever         S               N   inz(*On)   
D Open            C                   Const(' ') 
D Closed          C                   Const('Z') 
                                                 
 /Free                                           
     DoW Forever;                                
         Read(E) SomeRec;                        
         If %Eof(SomeFile);                      
             Leave;                              
         EndIf;                                  
         If %Error();

…

Be Specific When Updating With SQL Cursors

January 7, 2009 Ted Holt

To update a table (physical file) through an SQL cursor, use the FOR UPDATE OF clause in your UPDATE command. (I have written about FOR UPDATE OF before.) When you update through an SQL cursor, avoid two mistakes that can cause a performance problem.

First, don’t forget to include FOR UPDATE OF. The SQL preprocessor does not require you to include this clause, and will not generate a message to warn you of its omission. If you do not specify FOR UPDATE OF, SQL assumes that you want to update all columns (fields).

Second, list only the columns that

…

Previous Articles Next Articles

Search for:

Content archive

The Four Hundred
Four Hundred Stuff
Four Hundred Guru

Content archive

Recent Posts

Subscribe

Pages

Search