• Guru: Table Value Constructors Build Tables On The Fly

  June 25, 2018 Ted Holt

  Because I wish to be as valuable and productive as possible to the people who pay me to program their computers, I continually search the Web for new ideas and techniques. Doing so often leads me to sites that cater to other computing platforms. Today I want to share with you some SQL techniques that I learned from Microsoft SQL Server professionals.

  These techniques are based on the Table Value Constructor (TVC), which is a group of data values, usually literals, organized into rows and columns. In its simplest form, a table value constructor is literal tabular data that is …

  Read more

• IBM Patches ‘ROBOT’ Flaw in IBM i Crypto Library

  February 21, 2018 Alex Woodie

  IBM has issued patches to fix a serious security problem in the IBM Global Security Kit, or GSKit, a relatively obscure crypto package that implements SSL/TLS encryption algorithms across a variety of IBM products, including IBM i. An old flaw in the underlying RSA crypto algorithm that could let hackers decrypt data in a “side channel” attack has resurfaced under a new moniker: “ROBOT.”

  GSKit is an IBM toolkit that implements various encryption-related functions, including symmetric and asymmetric ciphers, random number generation, hashing algorithms, and encryption key management capabilities, for products that need over-the-wire encryption, including IBM i, Linux, and …

  Read more

• Should Spark In-Memory Run Natively On IBM i?

  November 6, 2017 Alex Woodie

  There’s a revolution happening in the field of data analytics, and an open source computing framework called Apache Spark is right smack in the middle of it. Spark is such a powerful tool that IBM elected to create a distribution of it that runs natively on its System z mainframe. Will it do the same for its baby mainframe, the IBM i?

  So, what is Apache Spark, and why should you care? Great questions! Let’s introduce you to Spark.

  Spark came out of UC Berkeley’s AMPLab about five years ago to provide a faster and easier-to-use alternative to MapReduce, which …

  Read more

• Guru: Logging SQL Errors And Warnings

  October 30, 2017 Birgitta Hauser

  If something goes wrong with an embedded SQL statement or an SQL routine (a trigger, stored procedure or user-defined function), the system does not crash. Instead, DB2 returns a negative SQL code and an SQL state that starts with something other than 00, 01, or 02. If the routine does not handle the error, the program continues to run.

  It is good practice to log errors to a table, especially unexpected ones. Before you can log any errors, you need to know how an SQL error can be trapped and handled. You need to know how to write a condition …

  Read more

• Visual Data Exploration Comes To Db2 Web Query

  October 18, 2017 Alex Woodie

  The IBM i server isn’t known as an analytics powerhouse — transaction processing is more its thing. But with the latest release of Db2 WebQuery for i, customers are getting visualization capabilities that will let them explore their data in powerful new ways.

  IBM announced Db2 Web Query for i version 2.2.1 on October 3 as part of its Technology Refreshes (TRs) for IBM i 7.2 and 7.3. While the release number would seem to indicate a minor update, the improvements are actually quite substantial, says Robert Bestgen, IBM’s product development manager for Db2 Web Query.

  “We kind of moved …

  Read more

• IBM Adds Support For Publishing JSON In DB2

  October 4, 2017 Alex Woodie

  At long last, IBM i shops will be able to publish JavaScript Object Notation (JSON) documents from their Db2 for i database. That much-desired capability will finally arrive as part of the latest Technology Refreshes (TRs) for IBM i versions 7.2 and 7.3, which were announced yesterday and ship this month. New SQL commands and security log enhancements round out the release.

  The capability to publish JSON documents has been a long time coming for IBM i shops, who until now have had to content themselves with the capability to consume and store JSON within their database, but who lacked …

  Read more

• Guru: Consuming A REST Web Service Using SQL And POST

  October 2, 2017 Mike Larsen

  In my prior article, I showed how to consume a REST web service using the GET verb. This time, we’re going to continue to build our knowledge of web services by working with the POST verb. In addition to working with a different verb, I’m also going to demonstrate how to pass a header and body to the service.

  The goal is to post information to an Amazon Web Service (AWS). I created a simple REST Amazon Web Service that accepts information about a pet which will be inserted into a pet store database. I pass a JSON structure …

  Read more

• Does IBM i Need More Databases?

  August 14, 2017 Alex Woodie

  There are many things that differentiate IBM i from other platforms, including its storage, security, and programming models. But one of the most unique aspects of the IBM i platform is its integrated DB2 database, which is used exclusively by practically all IBM i customers. You just don’t find this on other platforms. But perhaps it’s time for IBM i to diversify its database support?

  In many respects, DB2 for i is the platform’s greatest strength. IBM i is renowned for its transaction processing prowess that drives ERP and other line of business applications, and DB2 for i (formerly DB2/400, …

  Read more

• Guru: Error Handling in SQL PL, Part 1

  June 12, 2017 Ted Holt

  I once fancied myself a logical thinker. I changed my mind when I started programming computers. I quickly realized that I was incapable of writing an error-free program. Chalk up another valuable lesson to experience. More experience taught me to program for both expected and unexpected conditions, and now I apply that concept to all languages that I use, including SQL PL.

  SQL PL has excellent exception-handling methods, and they’re not hard to use. In this article and Part 2 to follow, we look at how DB2 informs you that your SQL request worked correctly or not. Next, we’ll take …

  Read more

• Guru: The XML Composition Conundrum of XMLNAMESPACES or XMLATTRIBUTES

  April 3, 2017 Michael Sansoterra

  Hey, Mike:

  After reading the IT Jungle Guru tips on DB2 XML Composition (see Related Stories below), I’ve been able to build XML from relational data. I have everything working but now I need to enclose all data in a “Document” tag after the XML declaration like the example here:

  <?xml version="1.0" encoding="utf-8"?>
  <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns="http://MyCompany.com" 
  xsi:schemaLocation="http://MyCompany.com validate.xsd">
  

  I tried using XMLNAMESPACES but can’t get it to produce the “xsi:schemaLocation” tag. How can I construct the document tag I need?

  –N.C.

   

  The problem here is that the xsi:schemaLocation in your example is not a namespace, but …

  Read more

Previous Articles