1. Prompted SQL DDL Creation

Not familiar with SQL CREATE TABLE syntax? No problem! There's a really easy way to create SQL DDL (Data Definition Language) statements. Written by Sam Lennon IBM i developers are becoming more comfortable exploiting the power of SQL to retrieve a

2. Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL

Create useful date-centric service programs with embedded SQL. Written by Tom Snyder Welcome to 2012! All of our year-end processes are complete, and it's the beginning of a new year with a clean slate. 2011 was a very clean year date-wise because

3. What Are the Differences Between DB2 for i and SQL Server SQL Syntax?

Avoid headaches and wasted time by learning the major differences between DB2 for i and T-SQL dialects. Written by Michael Sansoterra It's apparent by emails I've received that some SQL Server developers are having an opportunity to delve into the

4. Practical SQL: UDFs and Service Programs, Part II

With a little creativity, SQL allows you to add a form of overloading to RPG that can really extend your toolkit. Written by Joe Pluta In the previous article in this series, I created a simple user-defined function (UDF) that acted as an SQL inter

5. Practical SQL: UDFs and Service Programs, Part I

In this installment of our continuing series on using SQL in real business applications, we introduce the concept of invoking service programs from within SQL. Written by Joe Pluta We continue to provide you with examples of integrating SQL with tr

6. Practical SQL: Three Ways to JOIN

JOINing tables is one of the fundamental tasks in SQL, and this article explains three basic syntactical approaches. Written by Joe Pluta SQL is the tool of choice for querying relational data, and the whole idea behind relational data is that the

7. Practical SQL: Calling RPG from SQL, Part II

This second article on combining the strengths of RPG and SQL shows how to turn a stored procedure into a user-defined function (UDF). Written by Joe Pluta In my previous article, I showed you how to create a stored procedure that will invoke an RP

8. Practical SQL: Calling RPG from SQL, Part I

This first article on combining the strengths of RPG and SQL focuses on defining and testing stored procedures. Written by Joe Pluta SQL and RPG are powerful and complementary tools. SQL does things well that are difficult or nearly impossible in R

9. Use SQuirreL SQL During RPG Development with Embedded SQL

Any OS, any programming language, any database—SQuirreL is a perfect fit for almost any situation involving SQL! Written by Tom Snyder In a previous article, I showed you how to install the SQuirreL SQL client to run interactive SQL on your IBM i

10. An Open-Source DB2 SQL Graphical Tool That You Can Use for All Your Databases

Install SQuirreL SQL client to run interactive SQL on your IBM i. Written by Tom Snyder As you start using embedded SQL more frequently within your RPG code, you'll soon find that your SQL statements are becoming larger and larger and the interacti

11. Practical SQL: SQLCOD and SQLSTT

This article compares SQLCOD and SQLSTT and shows you how to avoid one of the primary gotchas you face when using SQLSTT. Written by Joe Pluta Embedded SQL programming is one of the most productive additions ever made to the IBM i development envir

12. Why Use Embedded SQL Within RPG?

Let's evaluate the reasons you might want to take this approach. Written by Tom Snyder You've heard about embedded SQL and maybe you've tried embedded SQL, but you may be asking yourself, "Why would I use embedded SQL?" That's the course that I hav

13. Create, Format, and Export SQL Dates and Times Using DB2

After reading this article, you'll never worry about date formats again. Written by Tom Snyder My recent articles on exporting data have generated a number of questions on formatting dates with SQL. So let's explore SQL dates by looking at how to c

14. Now Introducing XML in SQL on DB2 for IBM i!

With 7.1, you can create XML columns, insert XML values, publish XML values, and so much more. Written by Jon Triebenbach XML has become the standard for document and data interchange on the Internet and is a key technology in the support for service

15. Using DB2 with Sed, Tail, Pipes, and Redirection from Within Qshell

Export physical files directly to the IFS with headers. Written by Tom Snyder In my previous articles about SQL on the IBM i, I have used common commands that are available directly from the command line. In this article, I will use the db2 command i

16. Use SQL to Export and Delimit Files on the IBM i

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 fro

17. Halt Those Long-Running SQL Queries

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 underestimati

18. Practical SQL: Monitor Changes by Accessing Journals with SQL

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 calle

19. Practical SQL: Don't Be Afraid of Recursion

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 prese

20. Practical SQL: More Dynamic Selection Criteria

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 timesta

21. Practical SQL: Dynamic Selection Criteria

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

22. Optimize Your SQL Statements with Visual Explain

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 state

23. Practical SQL: Archiving Data with SQL

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 all

24. Run SQL from a Source Member Using RUNSQLSTM

You don't need to write a program to run common SQL statements. Written by Tom Snyder Do you have common SQL statements that you run on a regular basis but haven't found the time to put into a program? Do you try to remember the SQL statement that yo

25. Streamline Searches with Dynamic SQL

Whether your applications are completely RPG/SQL-based or include processes written in languages such as Java or ASP.NET, dynamic searches are likely a key component. By Kevin Forsythe Are you using SQL within your applications? Whether you're

26. Practical SQL: Embedding SQL in Free-Format RPG

SQL is a great tool when used properly, and this article gives you a simple, practical example to get you started. By Joe Pluta Welcome to the wonderful world of practical SQL for RPG programmers. Over the coming months, I hope to bring you a wh

27. Designing SQL Procedures and Functions

Procedures and functions, whether written in SQL or in traditional application development languages, provide powerful tools for accessing your database. By Kevin Forsythe One of the most difficult tasks when you're designing an application

28. Getting the Message Using an SQL UDTF

Use an SQL user-defined table function (UDTF) to access messages in a message file. By Mike Faust If you've ever needed to retrieve information from a message file, you're probably already aware of the Retrieve Message (QMHRTVM) API, whi

29. Making the Best Use of V6 SQL OLAP Functions

Getting the most benefit out DB2 for i requires keeping the concept of "set at time" in mind and making use of all the latest features and functions. By Mike Cain In Dan Cruikshank's August 2008 article "Working with Data Sets

30. Using SQL in the Real World

Why are the examples for SQL always so lame? It's time to see what you can do with SQL on a real-world database. By Joe Pluta Seriously, how often do you change the price in an entire price file by 15 percent? I don't know of a single si

31. Exploit the Power of Joins in SQL SELECT Statements

Inner joins, outer joins, exception joins, cross joins... Do you know how to take full advantage of all the types of joins? By Kevin Forsythe Editor's Note: In this excerpt from SQL for eServer i5 and iSeries, author Kevin Forsythe explains

32. Managing SQL Is Easy with Change Management Tools

There's no reason not to forge ahead with new techniques such as SQL as long as you have the right tools in place. By Ray Bernardi Structured Query Language (SQL) is a keyword-oriented language, which makes it easy to learn and use, and it&#

33. Working with Data Sets

Let's look back at the various ways we processed data sets using RPG and compare that to the new V6R1 SQL grouping sets capability. By Daniel Cruikshank Like many midrangers, I began my career as a computer operator, loading card decks into

34. Accessing i Data from MS SQL Server

It's one thing to access SQL Server data from the System i, but it's another thing to access System i data from SQL Server. By Joe Pluta Heterogeneous data is one of the hallmarks of today's IT. Whether due to disparities in disk price or processing

35. Working with Derived Columns

Format and name derived columns when you use SQL to do ad hoc analysis of information in an SQL table. By Skip Marchesani I frequently use Interactive SQL or Run SQL Scripts in Navigator to do ad hoc analysis of information in an SQL table, whic

36. Using Host Variables in Embedded SQL

Learn how to use host variables to get the most from your embedded SQL statements. by Adam Glauser In its simplest form, embedded SQL in RPGLE is not much different from the type of SQL you can run using the RUNSQLSTM command. You can use DDL to

37. Procedures, and Functions, and Catalogs! Oh My!

Take the hassle out of managing procedures and functions with some easy-to-use tools. By Scott Forstie Procedures and functions work as desired only when they're kept in sync with the QSYS2/SYSROUTINE and QSYS2/SYSPARMS catalogs. For some us

38. Putting Run SQL Scripts to Work for You

Execute a script that contains a single SQL statement, multiple SQL statements, or even CL commands! By Skip Marchesani Several years ago, I began using what is now called the Run SQL Scripts function (also known as the SQL Script Center or Scri

39. SQL Subselect: Soup to Nuts

Are you aware that a single SQL statement can process two or more tables without the tables being joined together? By Skip Marchesani Subselect is the capability to embed up to 256 SQL SELECT statements inside a SELECT, UPDATE, INSERT, DELETE, o

40. SQL Triggers and Other Trigger Enhancements in V5

Are you aware of everything you can do with triggers in i5/OS or OS/400? One of IBM's better-kept secrets in V5R1 is the significant new trigger function provided in the OS/400 release. This ranges from SQL triggers, which provide a high degree of fl

41. Feel Constrained!

Do you know how to handle RPG's key constraints, foreign key constraints, and check constraints?

42. I Do Declare!

I've never been a fan of little black boxes in programming, but more appear every day, and I really don't like them.

43. DB2 Integration with SQL Server 2005, Part 2: CLR Integration

The introduction of SQL Server 2005 has brought some exciting new features for developers. For instance, the ability to integrate heterogeneous systems with SQL Server in real time keeps getting easier. Part I of this series covered the power and

44. Access Your iSeries Data Quickly and Simply

You can speed up your queries and SQL statements by understanding the workings of the iSeries Query Optimizer.

45. V5R4 SQL Enhancements

New functions and functionality show IBM's continued commitment to the evolution of SQL on the iSeries.

46. Query Remote Database Tables from the iSeries Using SQL and Java

Many iSeries shops host additional database servers, including Oracle, SQL Server, and MySQL. A common solution to integrating heterogeneous data consists of nightly data transfers and batch processing routines. However, it's often necessary to have

47. Using Dynamic SQL in CL: Part 2--Retrieving a Data Value

This utility allows you to retrieve unrelated bits of data from multiple files.

48. Using Dynamic SQL in CL: Part 1--Running Action Queries

Execute dynamic SQL statements anywhere.

49. Creating SQL Functions with RPG IV Subprocedures

You may know how to embed SQL within RPG, but can you embed RPG within SQL?

50. Patch Those Leaky Interfaces

Use distributed transactions to ensure data integrity between the iSeries and SQL Server.