MC Press Online

Friday, Mar 24th

Last updateFri, 17 Mar 2017 12pm

You are here: Home ARTICLES Programming SQL

Programming / SQL

SQL 101: Tools of the Trade - The ACS Run SQL Scripts, Part 2

Rafael Victória-Pereira

As promised, here are a few more tricks and tips about ACS’ Run SQL Scripts, including the availability of a new version with some nice features!

Written by Rafael Victória-Pereira

couple of TechTips ago, I explained how to install ACS and remarked that the process was not very user-friendly. Among other things, you need to figure out which JavaScript file to use in order to start the right installation process and, when the said process ends, you have to create your own desktop shortcuts.

Read more ...

SQL 101: Tools of the Trade - The ACS Run SQL Scripts, Part 1

Rafael Victoria-Pereira

It’s time to get to know the newest version of the Run SQL Scripts tool, part of the Access Client Solutions package. Even if you already use it, you might learn something new.

Written by Rafael Victória-Pereira

Assuming that you read the previous TechTip or already had IBM i Access Client Solutions (ACS) installed, you should be ready to learn more about the new Run SQL Scripts tool. It seems that IBM listened to its customers, took a few hints from the competition, and introduced some really neat features into this new tool. Let’s explore it together.

Read more ...

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 ...