MC Press Online

Tuesday, Jan 17th

Last updateTue, 17 Jan 2017 5pm

You are here: Home ARTICLES Programming SQL

Programming / SQL

SQL 101: Tools of the Trade - i Navigator’s Run SQL Scripts

A more “modern” alternative to STRSQL, discussed in the last two articles, is the i Navigator’s Run SQL Scripts tool. Let’s explore it together, shall we?

rafael victoria preiraWritten by Rafael Victória-Pereira

While STRSQL is a green-screen tool, Run SQL Scripts is part of the i Navigator package. You can access it by choosing the Run SQL Scripts option, either from the bottom-right pane of the i Navigator window after you’ve chosen the Databases tree node from the right panel, as shown in Figure 1, or by right-clicking the database name and choosing the respective option.

Read more ...

SQL 101: Tools of the Trade – Old, Reliable STRSQL, Part 2

Ready for more tips on STRSQL? Even if you’re familiar with this old and venerable tool, this article might show you something new.

rafael victoria preiraWritten by Rafael Victória-Pereira

The previous article started to discuss STRSQL and stopped short of explaining a very important set of features. So I’ll just pick up where I left off: I’m going to explain what Option 1 of the Interactive SQL Session Services screen, reachable by pressing F13 on the main screen, can be used for. This option brings up the Session Attributes screen, depicted in Figure 1.

Read more ...

SQL 101: Tools of the Trade - Old, Reliable STRSQL, Part 1

This article kicks off a new subseries in which I’ll discuss a few tools at your disposal to write, validate, and run SQL statements. The first of the list is the Interactive SQL, better known as STRSQL.

Written by Rafael Victória-Pereira

Perhaps the most used (and certainly the oldest) tool to execute SQL statements is the green-screen Start Interactive SQL Session command, STRSQL. This command is somewhat similar to the QCMD command in the sense that it provides similar function keys.

Read more ...

SQL 101: Date-Related Functions, Part 4 - Adding and Subtracting Months and Other Neat Date Tricks

After last time’s functions for extracting information from a date, I’ll look into some real date math, with functions that perform some neat tricks.

rafael victoria preiraWritten by Rafael Victória-Pereira

It’s now time to let the system do the so-called “date math” for you. This is an easy task in SQL because there are several intuitive functions to help. Let’s start with ADD_MONTHS. This function has two parameters: a valid date representation and the number of months to add. Here’s an example:

Read more ...

SQL 101: Date-Related Functions, Part 3 - Extracting Information from Dates

This article continues the date-related functions discussion, introducing a few more simple but extremely useful SQL functions: DAYOFWEEK, WEEK, QUARTER, DAYOFYEAR, and MIDNIGHT_SECONDS. Do you have time for some date fun?

rafael victoria preiraWritten by Rafael Victória-Pereira

Let me start with a quick flashback: an RPG Academy TechTip published in October 2015, explaining how to create an RPG function to calculate the day of the week of a given date stirred things up quite a bit. Some readers complained this kind of function was totally unnecessary, because SQL is better equipped to do this type of thing and so on. My reply was that I’d get to a point in the SQL 101 series in which I’d cover the “SQL version” of that particular function, named Clc_DayOfWeek.

Read more ...

SQL 101: Date-Related Functions, Part 2—Time-Saving Functions

The functions discussed in this article won’t save time in the literal sense; mankind has yet to invent a time machine. However, these functions will surely cut down on the time it takes an RPG programmer to perform complicated “date math.”

rafael victoria preiraWritten by Rafael Victória-Pereira

As I mentioned in the previous article, there’s no BIF to return the day of the week, for instance. SQL provides several simple-to-use scalar functions that do that and more, as you’ll see later in this article.

 

Before getting to that, let me take a moment to address some readers’ remarks regarding the functions I presented in the previous article.

Read more ...

SQL 101: Date-Related Functions, Part 1—Converting Almost Anything to a Date, Time, or Timestamp

This subseries will cover the main date- and time-related functions SQL has to offer, providing examples of possible applications of this interesting and useful set of scalar functions.

rafael victoria preiraWritten by Rafael Victória-Pereira

I’ll start by showing how to convert other data types to Date, Time, and Timestamp data. Handling dates and times in RPG has always been a pain. It’s true that the new-ish BIFs help a lot, but apparently simple tasks such as determining the day of the week of a given date or finding out when will the next Monday occurs require loads of RPG code to implement. SQL offers a very interesting set of scalar functions that can really help when it comes to handling date-related data.

Read more ...

TechTip: DB2 for i Optimization Strategies, Part 2

Avoid common SQL programming mistakes that can change your query speed from a cheetah to a tortoise.

Written by Michael Sansoterra

This tip continues the series started in Part 1 that discusses some common SQL coding styles that can be improved. While some examples may seem trivial, under a heavy load, saving CPU cycles and reducing data and journaling I/O can make a considerable impact on overall system performance.

Read more ...