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.
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.
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.
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_INFO—Characteristics and state of local and remote journals
- QSYS2/LIBRARY_LIST_INFO—Library list detail for the job executing the query
- QSYS2/REPLY_LIST_INFO—Systemwide 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.
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.
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online