Michael Sansoterra
-
DB2 For i XMLTABLE, Part 1: Convert XML to Tabular Data
June 13, 2012 Michael Sansoterra
… Read moreDB2 for i 7.1 brought many new features including the ability to compose and decompose XML data. However, decomposing an XML document in DB2 for i 7.1 requires quite a few steps and creates, sometimes unwanted, permanent objects. Further, for all of the XML processing features, one lacking feature is the ability to easily and dynamically shred XML. Fortunately, that missing feature is now present with the technology refresh 4 update, which adds the XMLTABLE table function to the SQL developer’s arsenal. In this article, part 1 of my series on DB2 for i, we will look at how
-
New in DB2 for i 7.1: Use Global Variables to Track Environment Settings
January 11, 2012 Michael Sansoterra
… Read moreThe concept of having a locally maintained application “scratch pad” to keep track of session specific information has been around for quite awhile. RPG applications have long used the LDA (Local Data Area) to track common information meant to be shared between programs within a job. (This saves developers the trouble of passing around all of this common information as parameters.)
Likewise, many operating systems have the concept of an environment variable, which is similarly used to maintain and publish many OS-defined and user-defined values. Under IBM i 7.1, SQL offers something called a global variable, which can be
-
Automate E-Mail Operations with Outlook and VBA
October 6, 2010 Michael Sansoterra
… Read moreNote: The code referenced in this article is available for download here.
Microsoft Office is a wonderful productivity suite. Besides being packed with features, its claim to fame for me (and the major reason I haven’t switched to other competing Office suites) is that it allows a developer to customize and enhance the product’s functionality using Visual Basic for Applications (VBA) or .NET code. For businesses, this means that Office applications (Word, Excel, Access, PowerPoint, Outlook, and others) can be used in the software development process to solve certain problems that would otherwise be difficult to write from scratch,
-
Merge Into the Synchronization Fast Lane with DB2 for i 7.1
September 22, 2010 Michael Sansoterra
… Read moreOne of the tedious chores that many developers face is keeping many different data sources synchronized. It is common for mid- to large-sized companies to have multiple software packages with requirements to keep synchronized copies of the item master, customer master, employee master, and other master files.
Other common synchronization tasks involve keeping aggregate tables of detailed data or even synchronizing DB2 data with a relational or non-relational remote data source, including remote databases, Web services, flat files, etc. Fortunately, to help with all of this, IBM has updated DB2 for i 7.1 to include support for the SQL MERGE
-
DB2 for i: Process Stored Procedure Result Sets as Cursors
August 25, 2010 Michael Sansoterra
… Read moreIt eventually happens to every developer. You write a stored procedure to return one or more sophisticated result sets to a Java or .NET client. Everyone is (temporarily) happy. Eventually, a need arises for an RPG or COBOL program to execute this stored procedure and process the result set(s). But, alas, high level language programs have no way to open and process a result set (except when using the cumbersome CLI API).
Phooey, that means we’ll need to duplicate the stored procedure code in an RPG friendly manner! But wait, here comes DB2 for i 7.1 to the long-anticipated rescue!
-
Remove Trailing Blanks from Legacy Columns with the IBM OLE DB Providers
August 11, 2010 Michael Sansoterra
… Read moreIt’s no secret that many legacy tables in DB2 for i contain fixed-width character columns. If you present this legacy data to users using common tools such as Excel, Access, or even an application data grid control, they’re likely to be annoyed. Why? Because everyone hates fiddling with trailing spaces that are part and parcel of fixed-width columns. If you have a 50-character address column with only 20 characters filled, that leaves 30 annoying trailing spaces to remove.
Here is a picture of this annoying phenomenon shown using the classic QIWS/QCUSTCDT table displayed within an editable Windows Presentation Foundation (WPF)
-
Generic Database Access with .NET 2.0
June 30, 2010 Michael Sansoterra
… Read moreA difficult task for developers is to foresee how an application may evolve in the future. As we all know, sometimes the unrealistic becomes reality. Say you have an application that will start on SQL Server and possibly later be moved to DB2 for i or vice versa. How do you create a data-centric application in .NET that isn’t tied to a particular database server? The answer lies in writing “database-agnostic” code.
Let’s back up for a moment to when .NET was in its infancy. When .NET was first released, each ADO.NET database provider had its own set of classes.
-
Client/Server Performance, Part 1: Blocking
June 16, 2010 Michael Sansoterra
… Read moreIt is no secret that read performance can generally be improved by the age-old “blocking” mechanism whereby many rows are fetched at once in preparation for a program to process them. So how is blocking done in the client/server world? Assuming that the good old IBM i will be the server, the following code samples (C#, Java, VBA, and DRDA) show an example of how to increase the amount of data that can be transferred from the server to the client.
C# Using ADO.NET and the IBM DB2 UDB for iSeries .NET Provider
For applications that need to buffer large
-
Include C Utilities in Your Developer Library: Evaluating a Mathematical String Expression
March 24, 2010 Michael Sansoterra
… Read moreMost i programmers have a library of RPG or COBOL utilities they’ve written or filched from another source. Having a library of samples and pre-built code is a great productivity booster. However, have you ever considered adding C and C++ code to your library? You don’t know C? Big deal. If you have the C compiler loaded on your i, there’s an abundance of C and C++ code out there on the internet just waiting for you to discover. Plus, if you’re well versed in ILE concepts, you may be further along the C path than you think.
Consider this
-
A Few Excel Export to CSV Tips
January 27, 2010 Michael Sansoterra
… Read moreComma separated variable (CSV) files are frequently used when exporting DB2 data for use with Microsoft Excel. However, because CSV files contain text without any additional formatting instructions, it can be somewhat time-consuming for users to format their worksheets whenever they get a new export. This tip will address a few of the shortcomings of using CSV files with Excel and present a few possible workarounds to make the lives of your Excel user community easier.
Many of these ideas are only applicable to CSV exports intended for Excel usage. If the export file is dual purpose (one for users