FHG Volume: 16 Archives - Page 8 of 8

SBMJOB, CALL, And Decimal Parameters

February 23, 2016 Ted Holt

In spite of my great admiration for the IBM i operating system, I have to admit it has its idiosyncrasies. One of them, which has been there since Day 1, is the way the Submit Job (SBMJOB) command reformats decimal parameters of the CALL command. Fortunately, a relatively recent addition to CL provides an easy way to deal with this quirk.

Assume a CL program calls an RPG program, passing along two decimal variables and a character variable.

dcl  &Option       *char    1
dcl  &Account      *dec     5
dcl  &ThruDate     *dec     7

call AR100R (&Account &ThruDate &Option)

One day you are told

…

Parsing Delimited Text Data Natively In SQL, Part 2: Java To The Rescue

February 9, 2016 Michael Sansoterra

Note: The code accompanying this article is available for download here.

In Part 1 of this series, I introduced ParseFileContent, a 100 percent SQL user-defined table function (UDTF) that can parse delimited text (as a CLOB or IFS file) and format the results as rows and columns. As an advantage over tools like CPYFRMIMPF, this UDTF can streamline a text based import process by allowing CASTs, JOINs, and data integrity checks to be done while the import is running. As useful as ParseFileContent is, it does suffer a few drawbacks.

ParseFileContent is not particularly fast and it can’t handle

…

Use SQL To Change A Substring

February 9, 2016 Ted Holt

Occasionally I need to change part of a string value in a database table. SQL has three mechanisms that let me accomplish such a thing. Do you know what those three mechanisms are and how to use them?

First, we need a table for illustrative purposes.

create table Parts
  ( ID            char(6)       primary key,
    Description   varchar(40),
    Cost          dec (5,2),
    Price         dec (5,2));

insert into parts values                         
('XX-101', '20-inch Doodad',            0.12,    0.25),
('XX-105', '2-foot Doodad left-hand',   0.48,    0.75),
('XX-106', '2-foot Doodad right-hand',  0.48,    0.75),
('XX-118', '12-foot Doodad',            1.25,    3.00),
('BA-101', '5CM  Widget',               0.30,    0.45),
('BA-104', '15Cm Widget',               1.00,    1.35),
('BA-111', '150cm

…

IBM i and .Net Connectivity With XMLSERVICE

February 9, 2016 Richard Schoen

Many businesses are looking for ways to modernize their IBM i applications while maximizing their existing code investments in RPG or COBOL applications. Some contend that using Microsoft development technologies is a bad way to go. They think it promotes replacement of existing IBM i applications or of IBM i itself.

My position has always been that PCs and Microsoft technology are a great way to enhance existing IBM i applications. XMLSERVICE and Microsoft Visual Studio are open source technologies your company can use to modernize, enhance, and augment your existing IBM i code base.

Using Microsoft .Net with XMLSERVICE

…

Parsing Delimited Text Data Natively in SQL, Part 1

January 26, 2016 Michael Sansoterra

Note: The code accompanying this article is available for download here.

A common task for database developers is to accept a delimited text file, parse it, and dump it into a database table. This tip demonstrates a user-defined table function (UDTF) that can accomplish this task based on delimited text data stored in a CLOB or in an IFS file.

The UDTF is called ParseFileContent and it accepts three parameters: CLOB data to parse, row delimiter character(s), and column delimiter character(s). The UDTF returns a ROWID column that represents the line number of the text data and 250 columns

…

Upsert One Row From A Data Structure

January 26, 2016 Hey, Ted

I’m attempting to use the SQL MERGE statement to write an “upsert” that stores the data to be inserted or updated in an externally described data structure. Is that possible? Any insight into this would be helpful.

–Lewis

Even though Lewis was only dealing with one row in the database table, what he was doing qualifies as an upsert in my book. He’s updating a row if it’s there and adding it if it isn’t. However, I don’t think MERGE is the right tool for this job.

MERGE is designed to update a lot of rows in one table from

…

Run VisualAge RPG Applications On 64-bit Windows 7, 8, And 10 Over The LAN

January 26, 2016 Dan Devoe

With assurance of continued support from IBM, we used VisualAge RPG (VARPG) to develop modern business applications. Then IBM pulled the plug on VARPG. Nevertheless, we’ve kept our applications working, and have even managed to find a way to run them under 64-bit Windows from our LAN. Here’s how we did it.

A Little Background

Around a decade ago, we were looking for a way to put a modern interface on our 5250-based applications. We really liked what VisualAge RPG (VARPG) had to offer. We found many reasons to lean toward this solution, but the most enticing ones were

…

Throw Away Old Summary Query Techniques

January 12, 2016 Ted Holt

It’s a new year! Out with the old! In with the new! That exhortation applies not only to everyday life, but also to the ways we write SQL queries. Let me show you two ways we used to have to write summary queries and two new ways that are better.

First, we need a database to query. Here’s a table of customers.

 ID   Name
===   =====================
101   J. Cheever Loophole  
102   Otis B. Driftwood    
103   Quincy Adams Wagstaff
104   Rufus T. Firefly

And here’s a table of shipments.

ID   CUSTOMER   ITEM    QUANTITY   PRICE
===  ========   ======  ========   =====
 1       102

…

SQL Paging With Limit And Offset In DB2 For i

January 12, 2016 Michael Sansoterra

LIMIT and OFFSET are two new query options that will please every developer who builds user interfaces (UI) involving potentially large result sets. Handling UI queries that return a large number of rows can be annoying as they may take too long to process, create heavy network traffic, and require a web or desktop client to cache the result set. Further, it’s unlikely a user will review all the rows! But what if DB2 returns only what is needed by giving back one slice of the entire result set on demand?

This is where the new LIMIT and OFFSET features

…

Updated RDi Keyboard Shortcuts

January 12, 2016 Susan Gantner

I’ve written tips about RSE/RDi keyboard shortcuts several times before. Readers of those tips will know that I created a downloadable list of my favorite keyboard shortcuts. I’ve updated the shortcut card fairly regularly and the most recent edition has recently become available. So if you have previously downloaded my shortcut card and found it useful, you may want to get the latest one here.

This edition of the card includes a few new shortcuts, including a couple that are new for V9.5 of RDi.

The first one is Ctrl+Alt+Q, a new V9.5 shortcut to bring up a dialog

…

Search for:

Content archive

The Four Hundred
Four Hundred Stuff
Four Hundred Guru

Content archive

Recent Posts

Subscribe

Pages

Search