FHG Volume: 11 Archives - Page 10 of 12

Duplicating CPYF Function Using SQL

March 16, 2011 Skip Marchesani

There are a few questions that I am frequently asked re: SQL that are interrelated. First, can SQL be used to duplicate the function found in the native CPFY (copy file) command? Next, can SQL be used to duplicate a table similar to the function found in the native CRTDUPOBJ (create duplicate object) command? And lastly, can a field reference file be used as the source for column definitions with the CREATE TABLE SQL statement? The answer is a qualified YES to all three questions, and it is relatively easy to do.

From a SQL perspective, I break the CPYF

…

Circumventing Integer Division

March 16, 2011 Hey, Ted

My computer seems to have forgotten how to divide. When I divide one value by another, using SQL, the answer is always exactly one or zero. What gives?

–RPG Professional

This took a little while, but we finally found his problem. First, the background.

Assume a database file with three fields: customer ID, the number of orders from that customer, and the number of shipments to the customer.

select CustID, Orders, Shipments
  from Summary  
          
CUSTID  ORDERS       SHIPMENTS
     1      75              25
     2      10              16
     3      20               0
     4       8               8

Now let’s add a ratio to the query to gauge order

…

Admin Alert: Corralling i/OS Storage Hogs, Part 2

March 16, 2011 Joe Hertvik

Last week, I published part 1 of my series detailing the seven biggest culprits of unnecessary i/OS storage consumption. This week, let’s continue with the remainder of my list and see what you can do to keep your storage problems under control.

Again, the Seven Sinister Storage Hogs

While not inclusive of all the ways processes can consume disk on your machine, I usually focus my attention on the big seven culprits of unnecessary storage consumption when an issue appears on one of my iSeries, System i, or Power i systems.

Excessive spooled files and other system objects
Walking Dead

…

Running Totals in an SQL Query

March 2, 2011 Skip Marchesani

There are situations when running an SQL query for data analysis where I like to generate a running total based on a column in the table that is the target of the query, to assist with the data analysis.

“What’s a running total?” you ask. Wikipedia and I define it as the summation of a sequence of numbers that is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

To explain how to generate a running total, I use an example based on a department

…

Odds and Ends: The Reader is the Guru

March 2, 2011 Dear Professional

The emails I get from you provide a sterling illustration that the readers of this august publication are the true gurus in the world of IBM i. Here are a few contributions that I hope may prove useful to some of you.

-Ted

Hey, Ted:

I’d like to add an important point when renaming the local database. IBM’s own jobs rely on this local database name entry existing. They don’t care if you have deleted it. They will recreate it pretty quickly if it does not have a name entered, so the commands to delete the old and add

…

Admin Alert: Corralling i/OS Storage Hogs, Part 1

March 2, 2011 Joe Hertvik

Last year, I wrote an article on finding and expelling i/OS storage hogs. Since then, I’ve pinpointed some other items that illustrate how Power i system storage fills up unnecessarily, leaving you contemplating buying additional disk drives when all you really need is a good housecleaning. This week and next, I’ll expand on my storage hog list to help you keep your storage problems under control.

The Big Seven Storage Hogs

When looking for ways to reclaim disk storage, I usually focus my attention on the big seven culprits of unnecessary storage consumption.

Excessive spooled files and other system

…

Secure DB2 for i Database Server Access by IP Address

February 23, 2011 Hey, Mike

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

I am trying to secure external access to our iSeries DB (ODBC, JDBC, et al). From a security point of view, I want to allow database access if it comes from specific IPs or servers. But I cannot find a way to identify the source (i.e., remote IP address) from the QIBM_QZDA_SQL2 exit point. I only find the user ID. Is there a way to obtain the source of the remote client attempting to connect to DB2?

–Mike, Four Hundred Guru Reader

Hi, Mike:

You are correct. The

…

Avoid Division by Zero in Query/400

February 23, 2011 Hey, Ted

I want to build a temporary database file to send to a user, but records with a zero divisor do not get written to the output file. Can I prevent Query for i from dividing by zero?

–Bill

Yes, Bill, and it’s not difficult. Here’s how it’s done. But first, some test data.

create table mylib/divbyzero
(Extended dec(5,2), Qty dec(3,0))

insert into mylib/divbyzero values
(500, 2), (0, 12), (6, 0), (0, 0), (100, 3)

Now I have five rows of data to play with, two of which have zero in the Qty field. Here’s my first attempt to create an

…

Image Catalogs: Another Timesaving Method for Upgrade or Installs

February 23, 2011 Hey, Joe

Great article on six tips for preventing Power i upgrades from slowing down. One tip that you missed is using Image Catalogs for your upgrades. They really help with installing software and PTFs. Also PTFs should be downloaded via FTP using IBM procedures from their Web site. CDs and DVDs are nice, but image catalogs are significantly better!

–Rich

Rich wasn’t the only reader who pointed out the advantages of using Image Catalogs in lieu of CDs or DVDs for i/OS PTF application and upgrades. While I neglected to mention them in my recent article, image catalogs can be

…

Commands: Underused and Unappreciated

February 16, 2011 Ted Holt

As programming languages go, CL is not glamorous. Nevertheless, it is practical and effective, and it has some nice features. One of my favorite features is the ability to extend the CL language by creating my own commands. I have used this feature many times to great advantage over the years, and I am amazed that so many programmers have never written a command. In this article I discuss advantages of creating commands and give an example of how to do so.

I have noticed that many shops use the CALL command exclusively to start execution of a program. CALL

…

Previous Articles Next Articles

Search for: