DB2 for i – FAQ & Howtos (EN)

Introduction

An Italian version of this post here.

This post doesn’t want to be a DB2 manual but simply a collection of questions and answers, curiosities, tricks and so on for SQL and DB2 for i: I’m sure everyone could find something new or something forgotten. New tricks and FAQ will be added day by day so please bookmark this page and don’t be shy, please add your tricks using the comments section of the page… thanks.

Sample tables

All of the examples shown here are based on a sample database available in any IBM i partition, “embedded” in the Database… and recoverable in a classic library (which from a SQL point of view is called SCHEMA!) by invoking a “Stored Procedure” from the SQL-Script environment (SQL Script of Access Client Solution ACS, that of Rational Rdi or even just doing STRSQL). 

(STRSQL or SQL Script window)

-- Let's create a SAMPLEDB library (schema) by invoking this Stored Procedure

! Call QSYS. CREATE_SQL_SAMPLE('SAMPLED

B')-- Let's do a small test

SELECT - FROM sampledb.employee;

iDB2-FAQ-001: How can I use LIKE SQL Operator when I looking for percent or underscore?

We all know the use of LIKE for selecting records in a table, but perhaps not everyone knows its nuances. Let’s take a few examples on the SAMPLEDB table. Employee

• Standard use of LIKE: all employees starting with “JOHN”
  • Select * from sampledb.employee where lastname like ‘JOHN%’;
• All employees who end up with “SON”:
  • Select * from sampledb.employee where lastname like ‘%SON’;
• All employees named ROBERTO or ROBERTA
  • Select * from sampledb.employee where upper(trim(firstnme)) LIKE ‘ROBERT_’;
• The characters %(percent) and _ (underscore) have a very precise meaning for predicate Like … if we wanted to search all records with a percentage symbol or underscore in a column we then have to resort to an escape character : we see in this case a search of all records with a uderscore inside the last name: we then define an ecape character in the additional parameters of the LIKE… function so on a case-by-case basis we use and define the escape character:
  • Select * from sampledb.employee where lastname like ‘%!_%’ escape ‘!’;

iDB2-FAQ-002 There’s something like %SCANRPL (RPG) using SQL only? (REPLACE)

SQL’s REPLACE function is a bit like the BIF %SCANRPL in RPG: it allows you to replace part of a string by making sure you enlarge or tighten spaces if necessary.

For those who still have IBM i partitions with older operating system versions, before the 7.1 for example (but I note that at least 15 years have passed … what are you waiting for?), it might also be useful in SQL embedded in RPG by missing precisely the BIF %SCANRPL in those versions:

Example 1: Replace the string part equivalent to :old with :new inside the string :text

dcl-s text varchar(50) inz('My original string');
dcl-s old varchar(30) inz('original');
dcl-s new varchar(30) inz('new');
exec sql
   set :text : replace(:text, :old, :new);

dsply text;
  text--> 'My new string'

Here’s another example: I needed to change the URL form HTTP:// to HTTPS:// for one of my carriers:

update riespe0f set rslinkwb- replace(rslinkwb, 'http://', 'https://')
 where rsanndoc-2019 and rslinkwb like 'http://as777.brt.it%'

If you want to dig deeper into REPLACE and %SCANRPL there’s a great post by Simon Hutchinson on RPPGM.COM: https://www.rpgpgm.com/2014/12/replacing-text-in-string.html

iDB2-FAQ-003: String manipulation with SQL (POSSTR, LOCATE, LOCATE_IN_STRING)

Remaining in the theme of string manipulation with SQL we wrote a special article for the functions POSSTR (position inside a string), LOCATE and LOCATE_IN_STRING, always for positioning inside the strings but with a slightly Different:

see article : https://blog.faq400.com/database-db2-for-i/db2-for-i-sql-manipolazione-stringhe/

iDB2-FAQ-004: I need to retrieve Relative Record Number (RRN)

You can also get the relative number of records with an SQL statement: SEL
ECT RRN(filename) F
ROM filename
WHERE field : ‘value’ 

iDB2-FAQ-005: Playing with DATE and TIME in SQL

Current date and Current Timestamp

select current timestamp as timestamp_current,
        current date as Current_Date,
        year(current date) as Year,
        month(current date) as Month,
        day(current date) as Day,
        hour(current timestamp) as Time,
        minute(current timestamp) as Minutes,
        second(current timestamp) as Seconds,
        microsecond (current timestamp) as Microseconds
 from sysibm.sysdummy1;

Difference in days between dates

select days(current date) - days(date('2019-01-01') ) as Days_from_start_year
 from sysibm.sysdummy1;

Extract information from a date:

select dayname(current date) as DayName,
        dayofweek(current date) as DayOfWeek,
        dayofweek_iso(current date) as DayOfWeekIso,
        dayofyear(current date) as DayOfYear,
        monthname(current date) as MonthName,
       midnight_seconds(current timestamp) as Midnight_seconds
 from sysibm.sysdummy1;

Details about managing dates and times with SQL for DB2 for i are available at the following links:

• IBM Developer: Fun with date – https://developer.ibm.com/articles/fun-with-dates-and-times/

iDB2-FAQ-006: I’ve got some problem with Divisions and Decimal Precision in SQL

When we are dealing with the operation “divided” “/” in SQL we have to pay attention to decimal precision in order not to find inaccurate results … especially in case we use in the constant number function.

In the case we see below, taken from the forum it.com.as400 Italian, we complained that in case 1 the result came back without decimals … to solve, you only had to indicate a constant with an appropriate decimal number:

create or replace table qtemp.b13
  (b13cod char(10),
   b13id1 decimal(17, 3),
   b13id2 decimal(17, 3),
   b13id3 decimal(17, 3),
   b13id4 decimal(17, 3),
   b13nd1 decimal(21, 3),
   b13nd2 decimal(21, 3),
   b13nd3 decimal(21, 3),
   b13nd4 decimal(21, 3));
 insert into qtemp.b13
   values('A001', 10.123 , 20,345, 30,678, 40,900, 50,123, 60.456, 70,789, 80,900),
   ('A002', 10, 20, 30, 40, 50, 60, 70, 80);

-- CASE 1 ... indicating the constant 3650 as whole ... the result is an integer
  SELECT B13COD, SUM(B13ID1 - B13ID2 - B13ID3 - B13ID4) : 36500 / SUM(B13ND1 - B13ND2) as B13TIM FROM B13 group by B13COD 

-- CASE 2 .... if I want 3 decimal places
 
 SELECT B13COD, SUM(B13ID1 - B13ID2 - B13ID3 - B13ID4) : 36500,000 / SUM(B13ND1 - B13ND2) as B13TIM FROM B13 group by B13COD 

-- CASE 3 ... if I want 5 decimal places

 SELECT B13COD, SUM(B13ID1 - B13ID2 - B13ID3 ) : 36500,00000 / SUM(B13ND1 - B13ND2) as B13TIM FROM B13 group by B13COD

The rule of rounding decimals in divisions is not simple … for those who want to study it, and then explain it to us too, the links is as follows: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_decimaldivision.html

iDB2-FAQ-007: Generated Always Column, let me know something more

The “Generated Always” columns are very useful for all those audit functions on the tables … for example, to record the timestamp of the last change, user, etc.

We have dedicated a specific article on our Blog (Generated Always Columns – HowTo) on this topic and I invite you to read the insights there.

iDB2-FAQ-008: How can I deal with long-named tables (or long-named columns)

Finally, using SQL DDL to define our SQL objects, you can create tables with long and meaningful names and long named’s columns too. You have to be a little careful because in some parts of the operating system only objects up to 10 chars are displayed.

Our advice is to always use long names but also to define a short name with a certain sense … let’s see in the following example where we create a long-named table and its short name (defined by “for system name”) … and some long-named column with their short version (defined by “for column”):

Create table faq400. MyTableWithALongName for system name mytabshort (
 id Integer Generated always 
                     as Identity primary key,
 MyLongColumnName for column MyShortCol char(100) not NULL default '',
 LastUpd Timestamp Generated always for each row on update
                    as row change Timestamp
                    not NULL) ;

In SQL there are no problems: we can refer to the table and columns independently with the long name or the short one:

insert into faq400. MyTableWithALongName 
 (MyLongColumnName)
 values('Example01');

 insert into faq400. MyTabShort, Or 
 (MyShortCol)
 values('Example02');

 select is from faq400. MyTableWithALongName;

In RPG, however, we must use the short names of both column and table, or indicate ALIAS and LIKEREC to use the long table and column names in our code:

  //***********************************************************
  F4LONGNAME: How to deal with long tables name and
  long columns name:
  //
  //**********************************************************
   ctl-opt option('srcstmt:'nodebugio);

   DCL-F MyTableWithALongName usage(?update)
         ExtDesc('MYTABSHORT') ExtFile(-ExtDesc)
                       Keyed alias;
   id-1;

   Chain (id) MyTableWithALongName;

   MyLongColumnName:'Modified by RPG';

   update MyTabShort; Record Name ... max 10 char!

   'inlr'on;

References:

• A very good post by Simon Hutchinson at RPGPGM.COM “Create SQL tables, views, and indexes with long names“

iDB2-FAQ-009: I’m looking for tables with a particular column.

All of our Database Objects like tables, columns, indexes, views, functions, procedures, aliases and so on are stored in the System Catalog and in QSYS2 Lib we have some interesting views on the System Catalog

Here some examples:

1 – I’m looking for all tables in my schema (library) with one or more columns with the word “id” in the column_name or in the column_heading

 SELECT TABLE_NAME, SYSTEM_TABLE_NAME, COLUMN_NAME, SYSTEM_COLUMN_NAME, COLUMN_HEADING
       FROM QSYS2.SYSCOLUMNS
       WHERE 
       (upper(COLUMN_HEADING) like '%BIRTH%' 
 or     upper(COLUMN_NAME) like '%BIRTH%' )      
         and    TABLE_SCHEMA = 'YOURLIB'; 

iDB2-FAQ-010: Hot to get date part from a column formatted hhmmssmmddccyy

When I have an alphabetical or numerical column with a date and I need the SQL date I choose from those two different ways:

• 1 – Using the UDF iDate, one of the utilities Think400.dk with a SQL like this:

select lbtime,  Date(Timestamp_Format(Digits(lbtime    ),
'HH24MISSMMDDYYYY'))
from ptilb00

• 2 – Using Timestamp_format function SQL:

select lbtime, Date(Timestamp_Format(Digits(lbtime), 'HH24MISSMMDDYYYY'))
 from ptilb00