Michael Sansoterra
-
Emulate sp_Help In DB2 For i
April 30, 2014 Michael Sansoterra
… Read moreNote: The code accompanying this article is available for download here.
Every once in a while I get an email from an SQL developer asking something like “What tool do you use to develop SQL routines on IBM i?” Astonished is the perfect verb to describe the reaction when I reply that I normally use STRSQL or Run SQL Scripts (in System i Navigator).
Equally apt descriptions are dumbfounded, perplexed, baffled, and flummoxed. If you’ve ever used SQL developer tools on other platforms, such as Microsoft’s SQL Server Management Studio, then the astonishment is quite
-
DB2 For i Table Function Performance Considerations
March 19, 2014 Michael Sansoterra
… Read moreIn my prior tip, I covered some guidelines on when to use scalar user defined functions (UDF) and the potential performance hit they can impose on a query.
In this tip, I’m going to follow up with a few additional guidelines about user defined table functions (UDTFs) in DB2 for i and show how they may impact query performance.
Once again, I’m using data from the Microsoft AdventureWorks sample database that has been exported to DB2 for i. Shown below is a sample UDTF named “Transactions” that combines the data from the transaction history and transaction history archive tables:
-
DB2 For i Scalar Function Performance Considerations
March 5, 2014 Michael Sansoterra
… Read moreI love using user-defined functions (UDFs) in DB2 for i. They encapsulate business logic, provide for code modularity and improve code reusability. But they can also flush performance down the drain. So in this tip, I’d like to use a sample UDF to analyze how it can affect the performance of an SQL statement.
For this illustration, I’ve exported the Microsoft SQL Server Adventure Works sample data to DB2 for i. There are three main tables to consider in this query; SalesOrderHeader (31465 rows), SalesOrderDetail (121317 rows) and ProductCostHistory (22680 rows; I dumped in some extra randomized data into this
-
The ADO Client Side Of Default Parameters And Named Arguments In DB2 For i
February 26, 2014 Michael Sansoterra
… Read moreIn Stored Procedure Parameter Defaults And Named Arguments In DB2 For i, I covered two new related features in DB2 for i 7.1 that were implemented in Technology Refresh 5 (DB2 for i Group PTF Level 18 or higher). In this tip, I’m going to cover a surprising limitation of this feature when coding in an ActiveX Data Objects (ADO) environment, whether using an IBM DB2 OLE DB provider or the iSeries Access ODBC provider.
Recall that stored procedure parameters now support a DEFAULT keyword that can be a literal, subquery, or expression (with limitations):
CREATE PROCEDURE DEV.CREATE_ORDER (
-
DB2 For i Odds And Ends
February 12, 2014 Michael Sansoterra
… Read moreIBM i 7.1 and its various incremental releases have introduced many great new features in DB2 for i. Many of the features have been covered in Four Hundred Guru tips. This tip will cover some of the less exciting, yet important features that may have gone unnoticed. Best of all, many of them are even available in IBM i 6.1.
Specify System PROGRAM NAME Keyword When Creating An SQL Trigger To Prevent Naming Conflicts For System Names
Consider the following CREATE TRIGGER statement that features a long SQL name:
CREATE OR REPLACE TRIGGER ADVWORKS08.UpdateSalesOrderHeaderModifedDate AFTER UPDATE ON ADVWORKS08.SalesOrderDetail REFERENCING NEW_TABLE
-
DB2 For i, Java, And Regular Expressions
January 29, 2014 Michael Sansoterra
… Read moreIn today’s world, IBM i developers often find themselves working with more than the relational data stored inside their trusty DB2 database. Coders can find themselves tasked with parsing many kinds of text data including: SMTP email, file contents of an IFS Folder, Java Properties files, free form comments imported from other systems, OCR data for invoices, resumes, XML, HTML, code fragments (RPG, COBOL, C, Java, SQL, VBA), and application logs.
While there are many tools available to examine plain text, a regular expression engine provides one of the best generic text processing engines available.
Yipes, it’s been over 10
-
Dynamic Compound Statements In DB2 For i
January 15, 2014 Michael Sansoterra
… Read moreDynamic compound statements are sure to be one of the most oft used new SQL features in IBM i 7.1. In general, this feature allows developers to execute a complex set of SQL statements that include variable use, error handling, and conditional logic on the fly without creating a permanent SQL routine such as a function or stored procedure.
Dynamic compound statements makes DB2 similar to other database platforms like SQL Server where SQL scripts can be written and executed on the fly. This feature comes in DB2 for i group PTF level 26, so make sure your database PTFs
-
Make Your DB2 For i Apps Environment Aware
October 23, 2013 Michael Sansoterra
… Read moreTime and time again, applications need to know something about the environment they’re running on. For example, if an application uses dynamic SQL and it needs to know what features are available, then knowing what version of IBM i it is using can help it decide how to build a query. Likewise, when auditing the source of database changes, it’s useful to know the TCP/IP address of the host requesting the change.
Consider the case where an application may need to know if a certain required PTF has been installed. While there have always been APIs to retrieve this information,
-
Retrieve The Call Stack In DB2 For i
September 11, 2013 Michael Sansoterra
… Read moreNote: The document accompanying this article is available for download here.
Troubleshooting problems is often a costly component of application maintenance. Consider the case when the user reports an application crash. Exactly what program crashed? Was it the UI logic or business logic embedded in a service program? If it was a service program, what procedure was running? What was the chain of events that led to the problem? Knowing what the call stack looks like can help answer many of these questions, thereby reducing costly investigation time. Since writing “persistent stored modules” (a.k.a., SQL routines) is increasingly popular
-
Away With The SQL Slash Separator
June 19, 2013 Michael Sansoterra
… Read moreOne potentially confusing aspect of programming SQL in DB2 for i for non-IBMers is the use of the forward slash character to qualify an SQL object with a schema. IBM i devs don’t have a problem with the slash because we recognize it as the character we use when qualifying objects while tooling around the i/OS green screen. Others database devs may wonder what sophisticated function the slash serves. However, in IBM i 7.1, IBM introduced a change in DB2 Group PTF SF99701 Level 15 that is worth noting.
When using the system naming convention, developers can now