Michael Sansoterra
-
Search SQL Source Using System Catalogs
May 15, 2013 Michael Sansoterra
… Read moreThe green screen PDM and the GUI Rational Developer for i (RDi) tools provide developers an easy way to search through source code to find a specified text literal. But what if these tools don’t have access to all your code, in particular the SQL code? Many developers may not be keeping an up-to-date source copy of their SQL persisted stored modules (functions, procedures and triggers) and further, may not be keeping a copy of the source at all. If this is the case in your shop, never fear, for DB2 for i maintains a record of the source statements
-
Bringing Home The Data
April 3, 2013 Michael Sansoterra
… Read moreIn IBM i 7.1 Technology Refresh 4 (TR4), IBM gave SQL developers the ability to insert data into a local database table from a remote DB2 for i database table within the comfort of a single SQL statement. (See my article Copy Data From A Remote DB2 Database Using DB2 For i 7.1 for more info).
With Technology Refresh 5, specifically DB2 for i Group PTF Level 18 or later, you can dynamically create a local table and insert data into it directly from a remote DB2 for i database query using the CREATE TABLE … AS statement.
Recall that
-
Stored Procedure Parameter Defaults And Named Arguments In DB2 For i
February 20, 2013 Michael Sansoterra
… Read moreStarting with IBM i 7.1 Technology Refresh 5, DB2 for i stored procedure parameters support a default value, and the CALL statement supports named arguments. (DB2 for i Group PTF Level 18 or higher provides this new functionality). These simple and related enhancements can have surprising benefits for code comprehension and code maintenance.
Let’s look at creating a procedure with one or more parameters having a default value:
CREATE PROCEDURE DEV.CREATE_ORDER ( IN @CUSTOMER_ID INT, IN @ORDER_DATE DATE DEFAULT CURRENT_DATE, IN @ORDER_ID INTEGER DEFAULT (NEXT VALUE for DATALIB.ORDER_ID_SEQUENCE) , IN @ORDER_SOURCE VARCHAR(10) DEFAULT 'WEB') LANGUAGE SQL BEGIN … END
-
What’s New With CPYFRMIMPF And CPYTOIMPF?
January 9, 2013 Michael Sansoterra
… Read moreAs my years as a developer whiz by, I often find myself ignorant of the newest features available. For example, I’ve used the Copy From Import File (CPYFRMIMPF) and Copy To Import File (CPYTOIMPF) commands so often that I just became (as my old boss used to say) “fat, dumb, and happy” when using them, not realizing IBM has added new features since the time I first studied these commands. I’ll briefly share a few of the relatively recent enhancements to these commands.
First of all, in i7.1 Technology Refresh 5 (TR5), CPYTOIMPF has been enhanced with a new parameter
-
Running IBM i Access 7.1 and Windows 8
October 31, 2012 Michael Sansoterra
… Read moreWith the newly released Windows Server 2012 and Windows 8, I wanted to test drive Windows 8 and use it on my primary computer if possible. However, I knew a potential stumbling block would be getting IBM i Access for Windows to function. So I set out to load IBM i Access 7.1 on Windows 8.
While doing my research, I didn’t find anything on the IBM i Access for Windows website indicating general compatibility with Windows 8. In the past, the newest version of Windows has generally been a “no-go” until a Windows specific service pack is released. But,
-
Composing An XML Document From Relational Data: Part 2
August 22, 2012 Michael Sansoterra
… Read moreIn Part 1 of this series, I covered how to transform the results of a DB2 for i7.1 query into either an element- or attribute-based XML document. This time, I’m going to delve into a more complicated XML composition that requires the nesting of sales order XML elements as children of retail store elements. As with the last tip, I’ll start with a base SQL statement that extracts the required data and then gradually transform that statement until it produces the desired XML document using the new DB2 for i XML functionality.
To start, here is the base query that
-
Accessing Multiple DB2 Relational Databases In A Single Query
August 8, 2012 Michael Sansoterra
… Read moreIn my prior tip, I covered a new feature provided in the i7.1 technology refresh 4 update that allows a INSERT/SELECT statement to insert data into a local DB2 for i table from a query against a remote DB2 database. In this tip, I will cover a method that can be used to query multiple DB2 relational databases in a single statement.
Author’s Note: Incidentally, the remote DB2 database can be another member of the DB2 family besides DB2 for i. Check out my article on DB2 for Windows for more information.
As I mentioned in my last tip
-
Copy Data From A Remote DB2 Database Using DB2 For i 7.1
July 25, 2012 Michael Sansoterra
… Read moreIBM DB2 for i 7.1 introduced the ability to query a remote DB2 for i database directly without having to invoke an explicit CONNECT statement. Querying a remote table with one SQL statement is great, but lacks one important feature: requests for remote data are usually combined with requests for local data.
However, DB2 for i does not allow references to tables from multiple relational databases in a single statement, until now that is. The i7.1 technology refresh 4 update allows an INSERT/SELECT statement to reference tables from different DB2 relational databases. (Even if you’re not at i7.1 you can
-
DB2 For i XMLTABLE, Part 2: Using Namespaces And IFS XML Files
July 11, 2012 Michael Sansoterra
… Read moreIn Part 1 of this series, I introduced the DB2 for i developer’s newest friend, the XML “slicer and dicer” table function: XMLTABLE. XMLTABLE is powerful because it can read an XML document and convert its content to a tabular format that can participate in a query with relational data.
The developer has to specify the mapping between the XML data and the intended tabular format by using XPath expressions that instruct XMLTABLE how to map the data from XML into rows and columns. In this article, I’d like to cover two additional topics: parsing XML documents containing namespaces, and
-
Protect Your Intellectual Property: Obfuscate DB2 For i Source Code
June 27, 2012 Michael Sansoterra
… Read moreEvery “old timer” IBM i developer knows about the compiler option to include or exclude source statements within an OPM or ILE program. The CL compilers even have an option to allow CL source to be retrieved from the Retrieve CL Source (RTVCLSRC) command.
While high-level language programmers can decide whether or not to include source statements with their objects, DB2 SQL developers had no such option because anyone could use a tool like iNavigator to retrieve the SQL source. Thanks to the IBM i 7.1 technology refresh 4 update, SQL developers now have the option to obfuscate their