Michael Sansoterra
-
The System i and Office 2007
December 13, 2006 Michael Sansoterra
… Read moreIt seems like only yesterday that I recall people touting the benefits of upgrading to Office 97. Well here we are 10 years later at the release of Office 2007. From a System i5 developer’s perspective, I’ll briefly cover a few of the benefits of upgrading to Office 2007 by discussing some enhancements to this latest version of the Microsoft productivity suite.
First of all, the Office 2007 user interface has been redone using the new “ribbon” user interface. The main difference with this new interface is that drop down menus are now replaced with toolbar-like icons. Instead of each
-
Get Creative Using the SQL Database Exit Point
November 8, 2006 Michael Sansoterra
… Read moreThe System i platform has a powerful operating system, i5/OS. One example of that power is the fact that developers can tap into many features of the OS within homegrown applications by using the provided Application Programming Interfaces (APIs). Further, developers are allowed, via exit points, to create exit programs that interact with the system by responding to certain events that occur, such as when an FTP, SQL or Telnet request is made by a client application. This kind of versatility allows a developer to have fine-tuned control over a wide array of events on the system.
In this article,
-
Using Parameter Markers with SQL Functions
October 25, 2006 Michael Sansoterra
… Read moreSQL is a superb data access tool in terms of ease of use and versatility. Data extraction routines that used to take hours to write in RPG or COBOL often takes minutes to write in SQL. The one draw back to dynamic SQL (as compared to data access routines written in a high level language), is that the DB2 query optimizer often takes precious time to figure out how to best execute an SQL request. To take the edge off this performance hit, dynamic SQL statements can be made “reusable” by using parameter markers so that the query optimizer can
-
Declare the BINARY Data Type Using DDS
October 18, 2006 Michael Sansoterra
… Read moreWe’ve all heard by now that defining tables using DDS is becoming outdated and that developers need to use SQL in order to access the relatively new data types, including DATALINK and Large Objects (BLOB, CLOB, and DBCLOB).
I recently ran into a situation where I wanted to change a field in an existing table to use the new (as of V5R3) BINARY data type for encrypting credit card data. (Recall that the BINARY data type is similar to character data tagged with a CCSID of 65535. However, data in a BINARY column will never be translated whereas the character
-
Developers Don’t Despair: Virtualize Your PC
September 13, 2006 Michael Sansoterra
… Read moreToday’s developers often wear many hats: programmer, operator, help desk, and database administrator. Furthermore, they often support software on many operating systems (various versions of Windows, Linux, Unix, and so forth) with various versions of application software (Microsoft Office 97 or 2003, ERP clients, and so on) using a variety of developer tools (Visual Studio, Visual Studio .NET, Websphere Development Studio Client, Delphi, and others).
It can be quite overwhelming for companies with small budgets to create testing and development environments for such a wide array of software. Fortunately, there is a way to deal with this type of
-
New in V5R4: OLAP Ranking Specifications
June 14, 2006 Michael Sansoterra
… Read moreThis tip continues Four Hundred Guru‘s series on the fantastic SQL enhancements that iSeries, i5 and AS/400 users received in the latest release of OS/400, which is called i5/OS V5R4. In V5R4, IBM gave developers an impressive set of Online Analytical Processing (OLAP) tools to aid with common ranking and numbering issues. The significance of these OLAP tools lies in the amount of work they do and the time it will save database programmers.
OLAP queries are generally concerned with providing answers to high level questions about large amounts of underlying data. (OLAP is also often associated with multidimensional
-
Recursive Queries on the iSeries and System i
June 12, 2006 Michael Sansoterra
… Read moreWho would’ve guessed, but we’re almost done with the long list of SQL enhancements introduced in release V5R4 of i5/OS. Sorrow not, though, because this tip will bring abundant joy as a major limitation of SQL has just been lifted–the ability to run a query recursively.
Recursive queries are queries that need to do multiple self joins an unknown number of times. Take, for example, an employee master table that contains an employee’s employee number and the manager’s employee number. Say we need a list of all employees along with a listing of all manager’s above the employee. To do
-
Present Timestamps in the Local Time Zone
May 24, 2006 Michael Sansoterra
… Read moreThe code for this article is available for download.
For companies with locations in multiple time zones that connect to a central iSeries, time stamp presentation can be a problem. For instance, consider a legacy shipping application that records the date and time of a shipment. If the legacy application isn’t time zone aware, the shipments at the various locations will all be stamped with the date and time of the system’s time zone. A desirable solution for this problem would be a routine that can adjust a timestamp from the main time zone to a local time zone.
-
SQL Goodies in DB2 for i5/OS V5R4, Part 2
May 3, 2006 Michael Sansoterra
… Read moreA few weeks ago, I walked you through some of the enhancements that IBM has made in the SQL programming language and its DB2/400 database with the i5/OS V5R4 release, which came out in January. As I explained in the first part of this story, I have been watching SQL enhancements like a hawk since OS/400 V4R2 was announced many years ago. I am always eager to put the improvements IBM has given us into practice. The SQL in V5R4 has lots of new stuff.
But before I get into that, I want to remind you that IBM has a
-
V5R4 Improvements to the i5/OS OLE DB Providers
May 3, 2006 Michael Sansoterra
… Read moreThe code for this article is available for download.
I’ve used the feature rich and reliable AS/400 ODBC driver for quite a while in my applications. When creating new applications, I would often switch to the alternative OLE DB providers to see if performance and features were comparable. However, I would always run into a limitation of some sort with the OLE DB provider (e.g., not being able to use the system naming convention) that would put me back to using ODBC. I’m happy to report as of V5R4 it looks like many of these limitations are gone.
In