• No Truncate Table? No Problem!

  March 23, 2011 Hey, Ted

  I am working on a project using DB2 for i, but my experience is with other database management systems. I can’t find the SQL TRUNCATE TABLE statement. Does DB2 cover this functionality some other way? –Brad Even in 7.1, the latest release, IBM does not implement the TRUNCATE TABLE statement. However, since this statement is included in other DB2 products, as well as in Informix, I expect we’ll see it eventually as part of our world. For the benefit of readers who are not familiar with TRUNCATE TABLE, this statement removes all rows of a table (records of a physical

  …

  Read more

• Automatically Deleting Spooled Files through Expiration Dates

  March 23, 2011 Hey, Joe

  Last year, I stumbled on a cool method for automatically deleting spooled files. When you create a spooled file, you can set the number of days that you want to keep that file on your system. Then once a night, you can submit a job that runs the Delete Expired Spooled Files (DLTEXPSPLF) command that will automatically delete expired spooled files. You should try it. –Peter After working with the Power i and its antecedents for almost 30 years, I always find it interesting when somebody shows me something valuable that I haven’t used before. DLTEXPSPLF is a simple command

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

• 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

  …

  Read more

Previous Articles Next Articles