Create CSV files from multiple physical files.

Written by Tom Snyder

In a previous article, I discussed how to "Delimit Data on the IBM i the Easy Way." In this article, we'll take it a step further and discuss alternate methods of exporting data from multiple physical files and delimited text files using comma-separated values (CSV).

SQL Cancel support has been greatly enhanced and simplified.

Written by Kent Milligan

Occasionally, a programmer or user issues an SQL-based report that runs much longer than anticipated. The long-running query may be caused by the user underestimating the complexity of the query or by the size of the data sets being searched. In these situations, it's nice to be able to cancel the SQL request midstream and free up the system resources that the long-running request is consuming. Cancelling the query also gives the submitter an immediate opportunity to review the SQL request instead of waiting for the request to complete.

One of the nicest features of DB2 on the IBM i is the integrated journaling. This article shows you how to use SQL and journaling together.

Written by Joe Pluta

Journaling has been an integral part of the DB2 database since long before it was called the DB2 database. In fact, journaling predates the IBM i and even the AS/400; journaling was introduced on the System/38 way back in the days of cave computers. Journaling provides a number of important capabilities, including commitment control and high availability, but in this article, I'm going to stick to one of the most basic features of journaling: change logging.

A word that sometimes strikes fear into the programmers, "recursion" is sometimes the only tool for the job, and this article shows you how to recurse in SQL.

Written by Joe Pluta

Today's article is very simple and straightforward. I'm going to present you with one of the more complex basic SQL statements you'll run across and then break it down piece by piece. I know that the "complex basic" seems somewhat paradoxical, but it's not. The SQL statement I'll be showing you is basic because it has no frills; pretty much everything in the statement is needed to do what we need to do. At the same time, the statement is complex because even at its minimum the statement requires a lot of moving parts. Fear not, gentle reader, I shall act as your guide through this dangerous territory, and at the end, you'll have yet one more powerful weapon in your programming arsenal.

One of the most common issues in SQL is dealing with dates. This article will introduce you to the joys of dates, both legacy and date data type.

Written by Joe Pluta

With the introduction of the date data type (and its siblings, the time and timestamp data types), the i5/OS database took a huge step forward. And although RPG's acceptance was a little more grudging, we eventually got to where we are today. However, if you're like many shops, you may have to deal with both legacy dates and date-type dates. And when you're talking about user-controlled queries, you still have to get those date values from the user via fields on the screen.

It's one thing to create a great SQL query; it's another thing entirely to let your users do it dynamically.

Written by Joe Pluta

SQL is not a difficult syntax; it was designed to be relatively easy to use even for non-programmers, and it lives up to that goal. Selecting and sorting data is pretty straightforward: the WHERE clause and the ORDER BY do the heavy lifting. The harder part comes when the user wants to select a specific part number or a date range or a list of states or a combination of those or none at all. In this article, I'll show you the brute force method and then something a little more elegant and better-performing to boot!

Learn how to use this GUI tool to improve your SQL statements.

Written by Kevin Forsythe

Writing SQL statements that function correctly and deliver the desired results is only half the battle. We also need to manage the performance of those SQL statements. This might involve things such as how we write the statements, what indexes are in use, and what SQL engine is processing the statement. The Visual Explain tool within System i Navigator helps us manage all that and more.

SQL excels at set-at-a-time processing, and what better application than archiving your data?

Written by Joe Pluta

No matter how much disk you have, you fill it. It's a corollary to Parkinson's Law, which posits that work expands to fill the time allotted. It's the same with databases; despite the ever-increasing amount of disk available (who could have imagined a 2TB disk for the home computer?), you will always run out, so at some point you will have to reduce the amount of data in your database. As it turns out, though, SQL can provide you with significant help.