• Guru: Change XML Elements in SQL

  February 18, 2019 Paul Tuohy

  Over the last few years, it has become more common to store XML or JSON in a column in a table. Whereas SQL provides all the necessary functions to construct/deconstruct XML or JSON from/to relational data, it does not provide an easy means to change the contents of an element. In this article, I am going to demonstrate a technique for changing the contents of an XML element using an SQL stored procedure.

  Just to provide some background, I was recently working on a project where DB2 XML Extender functionality was being replaced with the standard XML functions. The project …

  Read more

• IBM i Has Been Getting With The Program For Years

  February 4, 2019 Timothy Prickett Morgan

  There are many things that one could constructively criticize IBM about when it comes to the Power Systems platform running the IBM i operating system. But, in recent years at least, one of those things would not be – and could not be – that the company has not done enough to embrace the most important elements of the modern programming toolbox.

  In fact, the company has done and increasingly good job of embracing and extending the compilers, interpreters, frameworks, and models of the programming languages that have gone mainstream since Java first took the stage at the beginning of …

  Read more

• Guru: Create A UDTF Wrapper For A Stored Procedure Result Set

  February 4, 2019 Michael Sansoterra

  In the tip, Arranging Query Logic in DB2 for i Routines, I addressed a reader’s question about how to create a user-defined table function (UDTF) in DB2 for i that would return the same result set as an existing stored procedure. The purpose of having the UDTF would be to do additional processing on a result set, such as joining the result set with another query or dumping the result set to a temporary table for analysis. To accomplish this, I suggested moving the stored procedure query logic into a UDTF and then replace the query within the stored …

  Read more

• Guru: Easy Date Difference

  January 14, 2019 Ted Holt

  Hey, Ted:

  The dates in our database are stored as seven-digit packed-decimal values in the common CYYMMDD format. In 2018 I wrote an SQL query that reported the number of days between two dates, but it quit calculating properly as soon as it started using 2019 dates. Can you tell me the proper way to find the difference between two dates in days?

  –Becki

  I don’t know if “the” proper way exists or not, Becki, but I can show you how to do the required calculation. SQL has some handy built-in functions that address your problem.

  The DAYS function returns …

  Read more

• Guru: Arranging Query Logic in DB2 for i Routines

  January 7, 2019 Michael Sansoterra

  Hey, Mike:

  We use a stored procedure to return the result set to Java and display the results to a screen. I would like to make this stored procedure put the result set into a temporary table and then do some processing on the temporary table. Is this possible and if so how can I achieve this? Thanks.

  Your dilemma is understandable: stored procedure result sets are great when data needs to be returned to a client. However, once generated, the result set cannot be joined, sorted, or stored in a temporary table. Thankfully, a user-defined table function (UDTF) allows …

  Read more

• IBM Delivers New Goodies with ACS Update

  December 5, 2018 Alex Woodie

  IBM has issued a new release of Access Client Solutions (ACS), the indispensable Java-based client that delivers a range of functionality to administrators, developers, and users alike. In the new release of ACS, IBM delivers enhancements across a range of functions, including SQL queries, database schemas, data transfer, and printing and emulation.

  IBM first unveiled ACS way back in August 2012 as the strategic replacement for older IBM i client interfaces, such as Access for Windows, Access for Web, and Access for Linux. With a full 5250 emulator, support for printing, the capability to view IFS, spool file, and message …

  Read more

• MB Software Gets A Handle On Remote SQL Jobs

  November 28, 2018 Alex Woodie

  System administrators who get frustrated with the inability to see which SQL jobs coming in through ODBC are causing problems on their IBM i server may be interested in a product called Work with Active SQL (WRKACTSQL) from MB Software and Consulting, which recently announced the tool will be free through the end of the year.

  SQL is just about as close to a universal language for business as you can get. While there are other (i.e. “native”) methods of fetching data from Db2 for i, nearly three-quarters of IBM i developers report using SQL, making it the number two …

  Read more

• Is Nagios The Future Of Monitoring For IBM i?

  November 12, 2018 Alex Woodie

  For decades, technology professionals turned to established frameworks from IBM Tivoli, CA, Hewlett Packard, and BMC (“The Big Four”) to monitor their hardware and software stacks. But the open source world has caught up with those closed monitoring environments, and a project dubbed Nagios is poised to be the go-to platform for IT monitoring, including on IBM i.

  Nagios, if you are not familiar, is a free and open source software product that provides monitoring and alerting for servers, network gear, applications, and the array of services that organizations increasingly rely on. The software –backed by the obligatory recursive acronym …

  Read more

• Modernizing IBM i Apps with Microservices

  November 7, 2018 Alex Woodie

  Application modernization means different things to different people. For some, it could be turning a 5250 screen into a Web or mobile interface, or replacing database access with SQL. But for others in the IBM i community, including the vendor OpenLegacy, modernization refers to exposing existing business logic as APIs through a microservices architecture.

  Microservices refers to a software development technique whereby applications are broken down into multiple self-contained components and served via APIs in a loosely coupled but coordinated manner. The main advantage of this approach is each microservice is built independently of others, which can boost productivity and …

  Read more

• Guru: More About Merge

  November 5, 2018 Ted Holt

  I often read back through articles that have appeared in this august publication to look for errors and omissions. Such an expedition recently made me aware that I have not told you as much as I would like to about the SQL MERGE statement. Today I am pleased to provide more information.

  First I want to be sure that everybody understands is that you can add conditions to the WHEN MATCHED and WHEN NOT MATCHED expressions. That means that you do not have to treat all matched or unmatched rows in the same way. Look at this example:

  merge . 

  …

  Read more

Previous Articles Next Articles