14
Mon, Oct
6 New Articles

V5R4 SQL Enhancements

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

With V5R4, IBM continues its expansion of Structured Query Language (SQL) support inside of DB2 Universal Database (DB2 UDB) for iSeries. These enhancements include additional scalar and aggregate functions, new data types, support for online analytical processing (OLAP), and support for embedded SQL in free-format ILE RPG. In this article, we'll explore these changes and see how they'll make your life easier.

Special Registers and Scalar and Aggregate Functions

In V5R4, a new set of functions has been added to increase SQL's compatibility with other DB2 implementations.

Special Registers

SQL's new functionality includes four new special registers. Special registers are areas for storing and retrieving defined values for use within other SQL statements.

The new SESSION_USER and SYSTEM_USER special registers are both used to store and retrieve authorization information. The SESSION_USER special register is equivalent to the preexisting USER special register and has been added for increased compatibility with other SQL implementations. Initially, the value of SESSION_USER will be equal to the SYSTEM_USER special register, which contains the user ID used to connect to the current server. The SESSION_USER special register, however, can be changed at runtime using the SET SESSION AUTHORIZATION statement.

The CURRENT DEBUG MODE special register controls the level of debugging supported when procedures are created. Possible values for this special register are DISALLOW, ALLOW, and DISABLE. A value of DISALLOW indicates that debugging within the Unified Debugger should not be allowed. This value can be overridden by the SET OPTION DEBUG MODE statement on the CREATE PROCEDURE statement. A value of ALLOW indicates that the procedure should be allowed to be debugged within the Unified Debugger. Again, this value can be overridden using the SET OPTION DEBUG MODE statement. The value of DISABLE indicates that the procedure should be created to not allow debugging from within the Unified Debugger and that the SET OPTION DEBUG MODE statement will not override this.

The CURRENT DEGREE special register defines the level of I/O or SMP parallel processing to be used. This value is set using the SET CURRENT DEGREE x statement, where x is either a value from 1 to 32767 or one of several special values. A value of ANY indicates that the system should determine the level of parallel processing. NONE indicates that no parallel processing should be allowed. A value of MAX also allows the system to determine the level of parallel processing, but it assumes that all active memory in the pool can be used. Finally, a value of IO indicates that no SMP parallel processing should be allowed but that any number of IO parallel tasks can occur.

Scalar Functions

V5R4 also contains several new scalar date functions. ADD_MONTHS can be used to obtain a date that is the result of a given date plus a specified number of months. The first parameter used with this function represents the date to which the months should be added. The value must be a date field, a timestamp field, or a character field that evaluates to a date. The second parameter defines the number of months to be added to the date supplied on the first parameter, as shown below:

SELECT ADD_MONTHS('01/29/2006', 1), ADD_MONTHS('01/01/2006', -1)
FROM SYSIBM.SYSDUMMY1

The values returned by this statement would be a date value representing the date 2/28/2006 and a date value representing the date 12/1/2005. Note that a negative value can be used to effectively subtract months rather than add them. Also note that if the day portion of the date provided is greater than the last day of the resulting month, the last day of that month is returned.

The LAST_DAY function accepts a single parameter containing a date value, a timestamp value, or a character string that evaluates to a date. This function returns the last day of the month for the provided date, as shown here:

SELECT LAST_DAY('01/15/2006')
FROM SYSIBM.SYSDUMMY1

When executed, this statement returns a date value representation of the date 01/31/2006.

The NEXT_DAY function returns the next date associated with a specified day of the week following a provided date. The first parameter identifies the starting date. The second parameter identifies the name of day of the week to be returned. Valid values are the full day name ('MONDAY', 'TUESDAY', etc.) or the proper three-letter abbreviation ('MON', 'TUE', etc.). The example below illustrates this function's use:

SELECT NEXT_DAY('02/08/2006', 'WEDNESDAY')
FROM SYSIBM.SYSDUMMY1

When executed, this example returns a date of 2/15/2006, which is the next Wednesday after 2/8/2006.

The VARCHAR_FORMAT function converts a supplied timestamp value or a string representation of a timestamp value into a VARCHAR representation of the proper timestamp format. The first parameter supplies the input timestamp value, while the second parameter identifies the format of the resulting timestamp in VARCHAR format. This value must be specified as 'YYYY-MM-DD HH24:MI:SS'. In this string, YYYY represents the four-digit year, MM represents the two-digit month, DD represents the two-digit day, HH24 represents the two-digit hour in 24-hour format, MI represents the minute portion of the timestamp, and SS represents the seconds portion of the timestamp. Below is a sample of this function's use:

SELECT VARCHAR_FORMAT('2006-1-1 9:00:00', ''YYYY-MM-DD HH24:MI:SS')
FROM SYSIBM.SYSDUMMY1

When executed, this statement returns the value 2006-01-01 09:00:00. Note that the leading 0's are inserted in each portion of the timestamp.

The ENCRYPT_TDES function gives the user the ability to encrypt field data within an SQL statement. Like its predecessor, the ENCRYPT_RC2 function, ENCRYPT_TDES accepts a parameter containing the string data to be encrypted in BINARY, VARBINARY, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, VARCHAR FOR BIT DATA, BLOB, CLOB, or DBCLOB formats. The function also accepts two optional parameters, which are used to supply an encryption password and a password hint. If the password value is not provided, the data is encrypted using the default encryption password, which is set using the SET ENCRYPTION PASSWORD statement. Unlike the existing ENCRYPT_RC2 function, ENCRYPT_TDES uses the Triple DES encryption algorithm. To populate a table field with the result of the ENCRYPT_TDES function, the field must be defined as BINARY, BLOB, CHAR FOR BIT DATA, VARBINARY, or VARCHAR FOR BIT DATA. The length of the field definition must also take into consideration additional bytes required to store the password data. This is accomplished by taking the length of the data to be encrypted, adding 16 bytes for the password, rounding the result up to the next multiple of 8, and finally adding the number of bytes required for the password hint.

The GENERATE_UNIQUE function generates a unique 13-byte bit character string. This unique value is generated using a combination of the Universal Time Coordinated (UTC) and the iSeries serial number. The value returned will be unique each time the function is executed. A column within a table that contains a GENERATE_UNIQUE value must be defined as CHAR(13) FOR BIT DATA. The resulting value can also be used along with the TIMESTAMP function to determine the time when the data was updated.

The RAISE_ERROR function allows a user to return a specified error. The first parameter contains a five-character string that evaluates to the SQL State associated with the error. The second parameter contains a string with a maximum length of 70 bytes, which contains a diagnostic string related to the error. This statement is generally used within SQL procedural language. It cannot be used in circumstances where parameter markers are not allowed, such as from within interactive SQL, without using a CAST specification to define a data type for the function's results. This is because the RAISE_ERROR's data type is undefined.

Aggregate Functions

In addition to these scalar functions, aggregate functions have been added. The STDDEV_SAMP function returns the sample standard deviation of a set of numbers. This value is basically the result of this formula:

SQRT(VAR_SAMP(value))

That is the square root of the sample variance for the set of numbers. The STDDEV_SAMP function accepts a single parameter containing the numeric value on which the sample standard deviation is to be calculated.

The new VAR_SAMP function, shown above, calculates the sample variance of a set of numbers. As shown earlier, the single parameter identifies the numeric field on which the VAR_SAMP results are to be based. Below is the formula used to calculate the sample variance:

(SUM(field ^ 2) - ((SUM(field) ^ 2) / (COUNT(*)))) / (COUNT(*) - 1)

Online Analytical Processing (OLAP)

OLAP functionality has been added to SQL in V5R4. This ability extends the existing aggregate functionality within SQL by adding ranking and row numbering capabilities. This functionality allows for greater ability to analyze data using only SQL. The code below shows the syntax used to accomplish this:

SELECT 
RANK() OVER(PARTITION BY part defn ORDER BY sort order [ASC, DESC]) AS RANK_VAL,
DENSE_RANK() OVER(PARTITION BY part defn ORDER BY sort order [ASC, DESC]) AS DNS_RNK_VAL,
ROW_NUMBER() OVER(PARTITION BY part defn ORDER BY sort order [ASC, DESC]) AS ROW_VAL
FROM table name

The RANK and DENSE_RANK specifications allow you to retrieve a ranking within a defined group of fields for the current record. The field definition is based on the list of fields defined on the ORDER BY clause. The PARTITION BY clause can also be included to define partitioning of the ranking data. The statement below gives an example of this:

SELECT DIVISION, CUSTOMER,  SALES$, 
RANK() OVER(PARTITION BY DIVISION ORDER BY SALES$ DESC) AS SALES_RANK
FROM SALES_DATA
ORDER BY DIVISION, RANK() OVER(PARTITION BY DIVISION ORDER BY SALES$ DESC)

In this example, the data will be ranked by sales dollars within each division. The numbering returned will represent the ranking within the PARTITION BY value specified. Note that we've used the RANK() specification again within the ORDER BY clause for our SELECT statement to indicate that the result set should be sorted by the sales dollars. The way that ranking is determined when the ORDER BY values are identical depends on whether you use RANK or DENSE_RANK. While RANK will assign identical values the same rank, it also increments the "rank counter," meaning that if the top five records all have the same ORDER BY value, they will all appear as the number 1 ranking, but the sixth record will have a ranking of 6. If DENSE_RANK is used, the first five records will still have a ranking of 1, but the next record will have a ranking of 2.

The ROW_NUMBER specification is similar to the RANK specification. It returns the sequential row number within the defined partition, based on the ORDER BY clause. ROW_NUMBER, however, does not group like values as the RANK and DENSE_RANK specifications do. Below is a sample of using ROW_NUMBER:

SELECT ROW_NUMBER() OVER (ORDER BY DEPT, LNAME ) AS ROW_N, LNAME, SALRY$ 
FROM PAYROLL 
ORDER BY DEPT, LNAME

This example displays a row number within a DEPT group based on the value of the LNAME field. The ASC and DESC modifiers can be added to the ORDER BY clause to identify ascending or descending order. The optional NULLS FIRST and NULLS LAST values can be specified to force any null values to the beginning or end of the result set. The ORDER OF clause can be used in place of the ORDER BY clause to identify that the data set should be ordered using the sort order of the table specified.

SELECT ROW_NUMBER() OVER (ORDER OF PAYROLL) AS ROW_N, LNAME, SALRY$ 
FROM PAYROLL 
ORDER BY DEPT, LNAME

In this example, the value PAYROLL identifies that the order of our data should be the same sort order defined on the PAYROLL table.

Free-Form ILE RPG Embedded SQL

If you're an "embedded SQL junkie" like I am, this is the language enhancement you've been waiting for. Prior to V5R4, in order to execute an embedded SQL statement from a free-form program, you had to exit from free-form using the /END FREE compiler directive and then enter embedded SQL using the /EXEC SQL compiler directive. In V5R4, IBM has simplified the process and in turn made embedding SQL into an RPG program even more seamless. Below is a snippet of code showing how a free-form ILE RPG program with embedded SQL would have to be coded prior to V5R4:

     C/Free                        
        SQL_Str = 'SELECT NAME FROM CUSTOMERS , ' + 
        ' WHERE STATE= ? AND ACTBAL>0 ';
      /END-FREE            
     C/EXEC SQL                                
     C+ PREPARE SQL_STMT FROM :SQL_Str     
     C/END-EXEC                       
     C/EXEC SQL                      
     C+ DECLARE CUST_Crsr CURSOR FOR SQL_STMT       
     C/END-EXEC                            
     C/EXEC SQL                                                                      
     C+ OPEN CUST_Crsr USING :STCD
     C/END-EXEC 
     C/EXEC SQL                       
     C+ FETCH CUST_Crsr INTO :CUSTNAME
     C/END-EXEC                      
     C/Free                                
       DoW SQLCOD <> 100 and SQLCOD >= 0;        
         Write PRINTP1;
       EndDo;
      /End Free                                                                                               

You'll notice that each instance of switching from free-form to SQL requires at least two additional lines of code, not to mention the fact that we need to use the /EXEC SQL and /END-EXEC directives for each SQL statement we want to execute. This means that a single line of SQL takes up three lines of code. In V5R4, IBM has rectified this problem. The code below shows how the previous example can be coded in V5R4:

     C/Free                           
        SQL_Str = 'SELECT NAME FROM CUSTOMERS , ' +       
        ' WHERE STATE= ? AND ACTBAL>0 ';
        EXEC SQL PREPARE SQL_STMT FROM :SQL_Str;     
        EXEC SQL DECLARE CUST_Crsr CURSOR FOR SQL_STMT;     
        EXEC SQL OPEN CUST_Crsr USING :STCD;
        EXEC SQL FETCH CUST_Crsr INTO :CUSTNAME;
        DoW SQLCOD <> 100 and SQLCOD >= 0;          
          Write PRINTP1;
        EndDo;
      /End Free                                                                                           

As you can see, this much simpler method of coding embedded SQL not only reduces the number of code lines required, but actually makes the code much easier to read. Once again, this indicates IBM's commitment to the further use of SQL as well as the continued development of the RPG language.

DB2 UDB and SQL

With the continued evolution of SQL on the iSeries, IBM has shown its eagerness to keep DB2 UDB for the iSeries in line not only with versions of DB2 for other platforms, but also with other implementations of SQL. One can only wonder what goodies they'll have for us in the next release.

Mike Faust is an Application Programmer for Fidelity Integrated Financial Solutions in Maitland, Florida. Mike is also the author of the books The iSeries and AS/400 Programmer's Guide to Cool Things and Active Server Pages Primer and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..


Mike Faust

Mike Faust is a senior consultant/analyst for Retail Technologies Corporation in Orlando, Florida. Mike is also the author of the books Active Server Pages Primer, The iSeries and AS/400 Programmer's Guide to Cool Things, JavaScript for the Business Developer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..


MC Press books written by Mike Faust available now on the MC Press Bookstore.

Active Server Pages Primer Active Server Pages Primer
Learn how to make the most of ASP while creating a fully functional ASP "shopping cart" application.
List Price $79.00

Now On Sale

JavaScript for the Business Developer JavaScript for the Business Developer
Learn how JavaScript can help you create dynamic business applications with Web browser interfaces.
List Price $44.95

Now On Sale

SQL Built-in Functions and Stored Procedures SQL Built-in Functions and Stored Procedures
Unleash the full power of SQL with these highly useful tools.
List Price $49.95

Now On Sale

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: