Michael Sansoterra
-
SQL Goodies in DB2 for i5/OS V5R4, Part 1
April 12, 2006 Michael Sansoterra
… Read moreSince V4R2, I’ve watched SQL enhancements like a hawk, eagerly waiting to use the improvements IBM has given us. V5R4 SQL contains several new items that will be outlined here. Here are a few thoughts on this significant release, which I gleaned from the DB2 Summary sheet on IBM’s Web site.
- The SQL syntax in V5R4 meets the Core requirements for the 2003 SQL standard. A review of the 2003 SQL standard can be found by clicking here.
- Routines written in the SQL language will now have better execution times thanks to enhancements to the code generation engine,
-
Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers
March 8, 2006 Michael Sansoterra
… Read moreSometimes using SQL to perform simultaneous inserts, updates and deletes against related tables can be a chore. It would be convenient if programmers could simply treat related tables as a single table for the purpose of modifying data.
For simplicity, consider the following common scenario: An iSeries has multiple item tables holding data related in a one-to-one relationship. Often, this scenario is due to multiple application packages having item master files that are both populated with common item data. The illustration in Figure 1 below shows an item master table from Application A along with a supplemental custom item extension
-
Execute SQL Statements on DB2 UDB for Windows from the iSeries
June 22, 2005 Michael Sansoterra
With the necessity of tight integration between platforms pervading many IT shops, I wondered what interoperability exists between the DB2 Universal Database (UDB) on the iSeries and DB2 UDB for Windows. After a little investigation, I was pleased to find that DB2 SQL on the iSeries can be used to execute SQL statements on the Windows DB2 platform as easy as it can on a different iSeries using IBM’s Distributed Relational Database Architecture (DRDA).
For a simple intro to DRDA using two iSeries systems or partitions, see Using DRDA to Execute SQL Statements on Another iSeries. For in-depth info
-
Query Active Directory from the iSeries
June 15, 2005 Michael Sansoterra
The code for this article is available for download.
With respect to iSeries programs, real time integration with other platforms and databases has been one of my favorite pursuits. Unfortunately, as a consultant, I perceive that many have left the iSeries because of the perception that certain things could not be done programmatically. Of course, with the addition of Java and the APIs available to the ILE programmer, we know that the iSeries can do about anything–sometimes you just have to dig a little to find out how. In this article, I’m going to discuss a technique to query
-
Increase the System Inquiry Reply Line Size in WebFacing
April 20, 2005 Michael Sansoterra
WebFacing is a good tool for making green screen applications available for use in the ubiquitous browser. The technology has sufficiently matured to the point where 5250 applications can be tailored to be fairly consistent with true Web applications–with some work, of course. However, even though it is free, there are a few features unavailable with standard WebFacing.
The most grievous of these omissions is WebFacing’s inability to render system screens such as those produced by the WRKSPLF or RUNQRY commands. (Actually, system screens can be WebFaced, this just isn’t a “free” feature–one has to purchase the Advanced Edition of
-
Rounding Over a Set of Rows
March 16, 2005 Michael Sansoterra
The code for this article is available for download.
Sometimes, a seemingly simple thing like rounding can present thorny issues. This is particularly true when the rounding has to take into account an arbitrary number of values. To illustrate, consider the example of a retail point-of-sale application that requires the rounded state sales tax and local sales tax to be recorded at the sales line level instead of at the header level.
This can get tricky because the rounded tax amount calculated at the header may not equal the sum of the tax amounts calculated against the line as
-
Advanced Pattern Matching in RPG
January 12, 2005 Michael Sansoterra
The code for this article is available for download.
SQL has many great features, including the LIKE predicate. LIKE’s power is manifest in its ability to have multiple wild cards in a search pattern. Unfortunately, RPG’s closest analog to LIKE is the %SCAN built-in function, which is limited to searching a string for a single substring.
Even though %SCAN’s power is limited, a little extra code can be used to simulate LIKE. The code in program LikeR.RPGLE contains subprocedure Like. The parameters for the Like subprocedure are:
Like(search string:pattern[:wildcard])
An indicator variable is returned by Like to indicate
-
Executing Dynamic Calculations with Embedded SQL
November 10, 2004 Michael Sansoterra
The code for this article is available for download.
Every so often I encounter an application where an advanced user wants the ability to maintain a formula. Further, the formula may change every so often, so that building the formulas into the program is undesirable. This is a difficult situation in the RPG world, since the RPG language has no ability to evaluate a string expression at runtime.
Here’s an example:
Formula='Qty * Price * (1 + Case When Qty > 100 Then .10 Else 0 End)';
Fortunately, SQL can handle this dynamic calculation.
This calculation can be done
-
Use System Naming Convention and Library List with .NET Managed Provider
October 13, 2004 Michael Sansoterra
The iSeries .NET managed provider, first released in iSeries Access V5R3, lacked the ability to specify a library list and the SQL naming convention in connection strings. We were stuck with the defaults. However, with the introduction of service pack SI15176, in early September, IBM has enhanced the provider to set the library list and naming convention in the connection string.
For those unfamiliar with the concept of a managed data provider, it is .NET’s plug-in equivalent to an ODBC driver in the Windows world or a JDBC driver in the Java world. Each database manufacturer creates a .NET provider
-
Using DRDA to Execute SQL Statements on Another iSeries
September 1, 2004 Michael Sansoterra
For iSeries shops using SQL with multiple machines or logical partitioning, IBM’s Distributed Relational Database Architecture (DRDA) may help with SQL administration tasks. For example, it is common to develop an SQL routine on a test box and then duplicate the routine on a production box, which requires a cut and paste of SQL text or some other mechanism to propagate the routine. Or, sometimes it may be handy to know what’s in a file on the live system while working on the test system, without having to go through the pain of starting a new 5250 session or using