FHG Archives - Page 5 of 144

Inline Table Functions In DB2 For i

June 14, 2016 Michael Sansoterra

In DB2 for i 7.2 TR4 and IBM i 7.3, IBM has made a special user-defined table function (UDTF) enhancement that should be shouted from the rooftops. This enhancement is referred to as an inline table function.

Consider the simple example of this UDTF named Get_Customer_Orders:

CREATE OR REPLACE FUNCTION Get_Customer_Orders (
@CustomerID INT,
@StartDate  DATE,
@EndDate    DATE)
RETURNS TABLE (
SalesOrderId INT,
CustomerId   INT,
OrderDate    DATE,
ShipDate     DATE,
SUBTOTAL     DEC(19,4))
LANGUAGE SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL
SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO
    RETURN
        SELECT SalesOrderId,CustomerId,
               OrderDate,ShipDate,SubTotal
          FROM SalesOrderHeader
         WHERE CustomerId=@CustomerId
           AND OrderDate BETWEEN @StartDate AND @EndDate;

What does DB2 do when

…

Watch Your Data While Stepping Out With RDi Debug

May 31, 2016 Susan Gantner

In this latest in my series of tips on using the RDi debugger, I’ll talk about one of my favorite debug features–the Monitors view–and we’ll explore not just one or two, but four different options for stepping through program code a statement at a time.

Stepping Out

Just like with the green screen debugger, you can step through code a statement at a time. Figure 1 shows the various debug tool bar icons with those related to stepping through your code highlighted. The most commonly used options are the two arrows in the middle: “Step Into (F5)” and “Step Over

…

Global Temporary Tables And Host Variables, Take 2

May 31, 2016 Ted Holt

In Global Temporary Tables and Host Variables, I complained that DECLARE GLOBAL TEMPORARY TABLE does not permit me to use host variables. I presented two ways to circumvent the problem. Today I revisit the topic to show another way that was not available six years ago.

First, create some global variables.

   create variable somelib.gv_Company   dec(3);
   create variable somelib.gv_Customer  dec(5);

Then use those global variables in your program.

exec sql
   set gv_Company  = :inCompany;
exec sql
   set gv_Customer = :inCustomer;
exec sql
   declare global temporary table Temp1 as
       (select *
          from sales
         where companyno  = gv_Company
           and customerno = gv_Customer)

…

LAG And LEAD Functions In DB2 for i 7.3

May 31, 2016 Michael Sansoterra

Database devs, you can kiss many of your cursors goodbye, because DB2 for i has two sweet new functions named LAG and LEAD that will allow you to extract a column value from a different row within the result set (relative to the current row) without programmatically looping through each row.

From the current row, LAG instructs DB2 to look backward in the result set a specified number of rows and retrieve a value. LEAD allows DB2 to look ahead to a specified row beyond the current row and extract a value. In DB2 parlance, these new functions belong to

…

DB2 for i 7.2 TR3 and 7.1 TR11 Features

May 17, 2016 Michael Sansoterra

I was writing up some exciting stuff on the new IBM i 7.3 features when I realized I never finished reporting about other recent DB2 features! Below are highlights of some newer features offered starting in DB2 for i 7.2 TR3 and 7.1 TR11.

Assign A System Name To A Global Variable

Recall that when you create a global variable, behind the scenes SQL Server implements the variable’s logic and value retrieval logic as a service program. The following variable definition with a long name (>10 characters):

CREATE OR REPLACE VARIABLE DEV.AVENGERS_TOWER VARCHAR(32)
DEFAULT (SELECT ADDRESS FROM DEV.HERO WHERE

…

Build SQL to Define Your Existing Files, Take 2

May 17, 2016 Ted Holt

Converting physical files to tables is a good thing to do, but if you work in the sorts of shops where I’ve worked, you already have more than you can do and you don’t have time to fix what isn’t broken. However, if I could make it easy for you, maybe you’d find the time to convert physical files where doing so is to most advantageous. Let me show you how easy it can be.

First, you need a place to put the SQL DDL (data definition language) source.

crtpf  mylib/sqlsrc

Second, you need an SQL client. I have used

…

RDi Debug Without SEPs

May 17, 2016 Susan Gantner

I wrote a tip way back in 2007 about how easy it is to start a debug session using Service Entry Points (SEPs). Back then, the tool was still called WDSC, and at that time I mentioned that there were some occasions when SEPs wouldn’t work. So what can you do if you’re in one of those situations?

You can’t set an SEP on a program whose member type doesn’t end in LE. For example, with RPG or CLP programs. That’s not to say that you can’t debug non-LE programs when using SEPs–you simply can’t use one of those program

…

OLAP Aggregation Specification In DB2 For i 7.3

May 10, 2016 Michael Sansoterra

If I could use one word to describe the new DB2 for i OLAP features in IBM i 7.3, it would be “Booyah”! (According to the Urban dictionary, “booyah” is “used in order to abruptly express great joy, usually brought on by victory or some other sort of accomplishment.”) Though there are many new features in i 7.3 under the umbrella of OLAP, for now I’m going to demonstrate the new OLAP aggregation specification.

It was way back in 2006 in V5R4 that DB2 for i developers were first treated to OLAP (Online Analytical Processing) functions. If you’re unfamiliar with

…

Sending Escape Messages From RPG, Take 2

May 10, 2016 Hey, Ted

In Sending Escape Messages from RPG, your program defines the message data parameter as 80 bytes, but the IBM manual (Send Program Message (QMHSNDPM)) defines the parameter as char(*), with notes saying it can be up to 32767. I would like a variable longer than 80, but instead of coding 100 today, 120 next project, and so on, I’m wondering how I could code it better to take full advantage of the API.

–Glenn

The short answer is that you don’t have to code the length. Let the compiler figure it out for you. Here’s an illustration that may

…

When You Reach Your Break(ing) Point. . . Or Not

May 10, 2016 Susan Gantner

I’m always surprised at how well-attended my RDi debugging presentations are. After all, once you know about service entry points, there’s not a huge amount that’s new and radically different about debugging, even with RDi. Then again, I’m constantly learning new things. Here’s something I learned just last week, in fact.

Let’s assume that you already have a debug session running on the troublesome program and we’ll look at setting and using breakpoints. The most obvious and common way to set one is to position your cursor on the statement where you want it. From there, you can use

…

Previous Articles Next Articles

Search for:

Content archive

The Four Hundred
Four Hundred Stuff
Four Hundred Guru

Content archive

Recent Posts

Subscribe

Pages

Search