08
Tue, Oct
2 New Articles

Step Right up and Hear About DB2 and TR9!

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

What's new with DB2 for i?

 

Like a carnival barker from a bygone era, I ask for your attention please. I have something new and exciting to tell you about. I seek not your hard-earned nickels, dimes, or quarters. I'm asking for your undivided attention for a few minutes. In today's world of hyper-tasking (hyper-tasking = multi-tasking x the # of devices you use), I realize that my request isn't trivial. Trust me…it will be time well spent on your part.

 

With the announcement of IBM i 7.1 Technology Refresh (TR) 9 and IBM i 7.2 Technology Refresh (TR) 1, IBM is sharing the news of what's new and enhanced. The focus on TRs and the TR PTF Group is well worth your attention, but it's not part of my message. What I hope you understand already is that DB2 for i enhancements are released on the same cadence as TR enhancements and the software delivery is in the form of the DB2 PTF Group (SF99701 for 7.1 and SF99702 for 7.2). The takeaway is simple, as shown in Figure 1: stay current and you will be rewarded with new and enhanced capabilities.

 

100814ForstieFigure 1

Figure 1: This is the DB2 for i enhancement timeline.

 

What's new in DB2 for i? Plenty! But I'll summarize it with this list:

  • New SQL programming capabilities
  • New SQL query capabilities
  • New IBM i services

New SQL Programming Capabilities

The heritage of the IBM i platform is application solutions for business computing. We still embrace the goal of enabling our solution providers to be successful. In this spirit, we've added several SQL programming enhancements.

 

Pipelined Table Functions

A pipelined table function is a 100% pure SQL alternative to an external User-Defined Table Function (UDTF). If you're saying to yourself, "Hey, we already have SQL UDTFs," you're correct. Non-pipelined SQL UDTFs return results through the specification of a query on the RETURN statement. What do you do if you can't build a single query that provides the results you need? Prior to pipelined functions, your only recourse was to build your own external program/service program and then create an external UDTF to allow it to be invoked. While not impossible, you need to endure source code management, a build process and a deployment process, authorization management, and more. For some IBM i clients, the steps above cause this solution to be a non-starter.

 

A pipelined function relies upon the PIPE SQL statement to return a row of data from the UDTF. The SQL Query Engine (SQE) utilizes a well-traveled UDTF invocation road to repeat the request for rows to be returned. The UDTF programmer sets in place the logic that guides the piping of rows. PIPE support is robust, with values, NULLs, and expressions accepted as input. PIPE is a control statement. It returns the specified row to the query engine and, when asked for the next row, continues execution at the SQL statement following the pipe. If the UDTF runs out of executable statements or calls the RETURN SQL statement, the query engine observes an end-of-file condition.

 

Coding a pipeline function is easy (and fun). The example code below demonstrates the basic structure.

 

With very few exceptions, an SQL statement can refer to only a single database. As we see in this example, a pipelined function can be used to blur this restriction. The invoker of the function will receive results that were gathered from many databases. Use pipelined functions to gain runtime control of the results returned, handle and overcome error conditions, or bend the rules of what's possible with SQL.

 

Example 1: Pipelined table function retrieving results from different databases

CREATE OR REPLACE FUNCTION Group_check(P_PTF_GROUP_NAME VARCHAR(7) )

   RETURNS TABLE (V_PTF_GROUP_NAME CHAR(7),
                 V_PTF_GROUP_DESCRIPTION VARCHAR(100),

                 V_LEVEL_DETAIL CLOB(1K))

LANGUAGE SQL

BEGIN

   DECLARE SkipIt INTEGER;

   DECLARE TARGET_RDB VARCHAR(128);

   DECLARE v_PTF_GROUP_NAME CHAR(7);

   DECLARE v_PTF_GROUP_DESCRIPTION VARCHAR(100);

   DECLARE v_PTF_GROUP_LEVEL INTEGER;

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

       BEGIN

         PIPE (NULL, NULL, TARGET_RDB CONCAT ' is not accessible ');

         SET SkipIt = 1;

       END;

   SET (TARGET_RDB,SkipIt,v_PTF_GROUP_LEVEL) = ('lpdac710',0,NULL);

   SELECT     PTF_GROUP_NAME,   PTF_GROUP_DESCRIPTION,   PTF_GROUP_LEVEL

       INTO v_PTF_GROUP_NAME, v_PTF_GROUP_DESCRIPTION, v_PTF_GROUP_LEVEL

       FROM lpdac710.QSYS2.GROUP_PTF_INFO WHERE P_PTF_GROUP_NAME = PTF_GROUP_NAME AND PTF_GROUP_STATUS = 'INSTALLED' ORDER BY PTF_GROUP_LEVEL DESC

       FETCH FIRST 1 ROWS ONLY;

   IF (SkipIt = 0 AND v_PTF_GROUP_LEVEL IS NOT NULL) THEN

   PIPE (v_PTF_GROUP_NAME, v_PTF_GROUP_DESCRIPTION,
         TARGET_RDB CONCAT ' has level ' CONCAT
         lower( v_PTF_GROUP_LEVEL ) CONCAT ' APPLIED');

   END IF;

   SET (TARGET_RDB,SkipIt,v_PTF_GROUP_LEVEL) = ('MysteryMachine',0,NULL);

   SELECT   PTF_GROUP_NAME,   PTF_GROUP_DESCRIPTION,   PTF_GROUP_LEVEL

     INTO v_PTF_GROUP_NAME, v_PTF_GROUP_DESCRIPTION, v_PTF_GROUP_LEVEL

       FROM MysteryMachine.QSYS2.GROUP_PTF_INFO WHERE P_PTF_GROUP_NAME = PTF_GROUP_NAME AND PTF_GROUP_STATUS = 'INSTALLED' ORDER BY PTF_GROUP_LEVEL DESC

       FETCH FIRST 1 ROWS ONLY;

   IF (SkipIt = 0 AND v_PTF_GROUP_LEVEL IS NOT NULL) THEN

   PIPE (v_PTF_GROUP_NAME, v_PTF_GROUP_DESCRIPTION,
         TARGET_RDB CONCAT ' has level ' CONCAT
         lower( v_PTF_GROUP_LEVEL ) CONCAT ' APPLIED');

   END IF;

   RETURN;

END;

 

SELECT * FROM TABLE(Group_check('SF99701')) A;

 

New DB2 for i Built-in Global Variables

A built-in variable is a variable defined and managed by the database. Your SQL statements can reference it anywhere that a column name is permitted. DB2 for i has the responsibility for the value within the global variable, and users are not permitted to change the value of the variable.

 

These variables exist only in IBM i 7.2.

 

100814ForstieFigure 2

Figure 2: DB2 for i provides these built-in global variables.

 

We have other SQL programming enhancements, like improved support for debugging, understanding SQL failure messages, and more. At the end of this article, I have pointers to the full details.

 

This is a decent place to mention that we use cost, risk, and client value to determine when to provide a database enhancement to existing IBM i operating system releases. Some of the enhancements are even provided to IBM i 6.1, where no TR technology exists. Remember, the DB2 PTF Group is the carousel to ride at this carnival.

New SQL Query Capabilities

We never cease to invest in our query support. The database industry is deluged with enhancement ideas and emergent technologies. Through the tireless efforts of the DB2 family and DB2 for i Chief Architect Mark J. Anderson, we carefully choose those query enhancements that will provide the largest impact for our clients.

 

Regular Expressions (Grep Your DB2 for i Data)

Regular expression support provides a new and exciting way to find data. (Psst…finding data is a fairly important topic for any database.) The REGEXP_LIKE predicate can be added to WHERE clauses to improve the selection of rows. Four new REGEXP_xxxx functions add complementary support. Regular-expression-based queries satisfy IBM i clients' expectation of complete national language support by using the services provided by the International Components for Unicode (ICU) found in IBM i Base Option 39.

 

Figure 3 has an overview of the new support. The SQL Reference books for 7.1 and 7.2 have been updated for all the new SQL support. The documentation includes the "Regular expression control characters" table, which spans three pages. Needless to say, there's an abundance of flexibility in the search patterns you can create.

 

100814ForstieFigure 3

Figure 3: Use regular expression for powerful search capability.

 

Again, an example will illustrate the new support. In this example, we don't see any use of the REGEXP_LIKE predicate. While it may be typical to use the predicate and functions on the same query, there's no requirement to do so.

 

This example shows how the regular expression functions can be used to find and extract website references from a tweet or a text. The example includes accommodation for loosely formed website names and case insensitivity.

 

Example 2: Using regular expression functions to extract website references

CREATE OR REPLACE FUNCTION FindHits(v_search_string CLOB(1M),
                                   v_pattern varchar(32000) )
RETURNS TABLE (website_reference varchar(512))
LANGUAGE SQL
BEGIN
DECLARE V_Count INTEGER;
DECLARE LOOPVAR INTEGER DEFAULT 0;
SET V_Count =
REGEXP_COUNT(v_search_string, v_pattern,1,'i');

IF v_pattern IS NULL OR LENGTH(v_pattern) = 0 THEN
   SET v_pattern = '(\w+\.)+((org)|(com)|(gov)|(edu))';
END IF;

WHILE LOOPVAR < V_Count DO
SET LOOPVAR = LOOPVAR + 1;
PIPE(
REGEXP_SUBSTR(v_search_string,v_pattern, 1, LOOPVAR, 'i') );  
END WHILE;

RETURN;
END;

SELECT * FROM TABLE(FindHits('Are you interested in any of these colleges: isu.EDU or www.umn.Edu? We could even visit WWW.wisc.edu if we have time.')) A;

We also extended our query support to include new Built-in Functions (BIFs) for padding data. The LPAD and RPAD functions include programmer controls over length and pad character(s). All the inputs can be derived at runtime through the use of expressions. While application logic can certainly be written to manipulate data to meet business reporting requirements, we hope our users find these new DB2 BIFs advantageous.

New IBM i Services

Over the last several years, the DB2 for i team has begun to externalize IBM i-specific operating system details via database catalogs and UDTFs. If you haven't seen these services, they're worth a peek because they provide a new option for efficiently solving business requirements. Information is derived when a query is executed, and the SQL Query Engine (SQE) can be used to select, group, order, count, analyze, and transform the data into useful forms. We document these services in the database performance and query optimization book. While this may seem like an odd home, they are officially documented, and now you understand where to look.

 

New Catalogs

Database people love catalogs. Traditional catalogs are physical tables that contain the detail needed to understand the relationships between the database constructs. IBM i Services are also provided via database catalogs. As I've already mentioned, the data returned on the query is extracted and returned at the point of query execution. We have three new catalogs:

 

  • QSYS2/JOURNAL_INFOCharacteristics and state of local and remote journals
  • QSYS2/LIBRARY_LIST_INFOLibrary list detail for the job executing the query
  • QSYS2/REPLY_LIST_INFOSystemwide reply list details

 These catalogs can be used to achieve better systems management and to solve business problems more efficiently.

 

New UDTF

Database people also love UDTFs. We are providing a new IBM i service in the form of a UDTF. The JOBLOG_INFO() UDTF should be a welcome addition to many types of users. The asterisk (*) character can be passed in to indicate that the current job log should be returned, or a target job can be specified. Think about all those times you needed to see a job log, but it had been deleted. This service makes it ever so simple to capture necessary detail. There are other applications for this service, but I'll leave that for another time.

 

  • QSYS2/JOBLOG_INFO(qualified-job-name)Joblog messages are consumed and returned in the form of an SQL table.

 

Something Unexpected

In the spirit of showing how easy it is to tie together disparate DB2 for i technologies to achieve something useful, we added the SYSTOOLS/GROUP_PTF_CURRENCY view. This view utilizes a new XML feed provided by the IBM Preventative Service Planning (PSP) organization. The feed contains an up-to-date description of the PTF Groups and CUM packages provided by IBM, their latest service levels, and the date that they were last updated by IBM.

 

The GROUP_PTF_CURRENCY view uses the DB2 for i HTTP function support to access and consume the XML feed. Next, the XMLTable() function is used to transition the XML data into a relational form. Last, the relational form of IBM's data is compared to the detail on the partition by querying the QSYS2/GROUP_PTF_INFO catalog.

 

The results of a sample query are shown in Figure 4. We decided to build this view to improve systems management for IBM i clients. We also hoped that this service would spur creativity and action within the IBM i community. There must be other good ideas for services of this ilk. The SYSTOOLS schema is where DB2 for i ships tools and examples.

 

100814ForstieFigure 4

Figure 4: Use the SYSTOOLS/GROUP_PTF_CURRENCY view.

On Your Way

This article touched on only a subset of the enhancements. Visit the following landing pages to discover the complete set of enhancements and related technical materials:

·        IBM i 7.1 TR9 enhancements  http://www.ibm.com/developerworks/ibmi/techupdates/i71-TR9
·        IBM I 7.2 TR1 enhancements  http://www.ibm.com/developerworks/ibmi/techupdates/i72-TR1  

 

As the carnival barker signals that "the show is over," gather your devices and resume hyper-tasking. Thank you for your attention, and I look forward to hearing your feedback on these enhancements and anything else you'd like to see built by the DB2 for i team.

 

Scott Forstie is a Senior Technical Staff Member at IBM. He is the DB2 for i Business Architect, working on all things related to the database on IBM i.  He also is the content manager of the IBM i Technology Updates wiki (www.ibm.com/developerworks/ibmi/techupdates) where IBM i operating system enhancements are described. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it. or followed on Twitter @ @Forstie_IBMi.

 

 

 

 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: