TYLOGIX SQL Tips & Techniques Page

Back to Tylogix Home Page  
By Thibault Dambrine

Tylogix SQL Pivot Techniques Page
Tylogix SQL Performance Techniques Page
iSeries SQL Programming:Youve Got The Power - Tylogix original article
Tylogix SQL Learning and Tutorial Page
Tylogix Downloads - home of EXCSQL and SQLCMDCHK

iSeries SQL PowerPoint Presentations (presented at COMMON) Downloads From this Site
SQL Join and Aggregate Value Techniques - a PowerPoint Presentation
SQL Implementation on iSeries - a PowerPoint Presentation



 

By Thibault Dambrine

In this page, you will find a quick reference for the different tips and techniques I have encountered regarding SQL techniques on iSeries.

iSeries SQL Tips -  Links within this page

iSeries SQL Tips - Outside Links

How to find duplicate records in a physical file using SQL?

How to find duplicate records in a physical file using SQL?
Technique to Remove Duplicate Records in a Table With SQL

How to manipulate data on a remote system?
How to execute a single SQL statement from a CL Program?
SQL Formula to find jobs that fail most frequently in IBM's Job Scheduler
SQL Join Basics

Casting Data Types with SQL

Converting JD Edwards format dates to a readable format using SQL
What is the DB2 equivalent of Oracle's DUAL?
What are the SQL data type equivalents in C, RPG and COBOL?
Defining long field Names with Short field references

Creating an Integrity Check between two Tables Using SQL
SQL Formula to find procedures in IBM's Job Scheduler
Debugging Stored Procedures: SQLCODE, Variables and DIAGNOSTICS
Example of a simple text parsing program written as a Stored Procedure
Debugging an SQL Stored Procedure
Method to find the SQL Optimizer suggestions to improve program or stored procedure SQL performance
What is the difference between SQL Procedures and Functions?
Exploring the DB2 SQL Catalog
Creating a Surrogate Key with the Current Time Stamp
How To Extract ONLY n Rows from a table
Example of a Correlated Update Technique, using SQL

Finding where (in which library) a DDL-built table with a long name may be
Converting local TIMESTAMP value to and from a GMT TIMESTAMP
Executing a CL command from SQL - This example uses an OVRDBF to a target member
Targeting one or more members in a multi-member file using SQL using ALIAS and UNION

Creating Characters by Specifying the Hexadecimal Value in SQL
Global Character or String Replace in SQL
Finding (locating) Hexadecimal Values in SQL
Correlated Delete Example Using SQL

Joining with Unreliable Numeric Data Stored in a Character Variable
DB2 Caveat! LOJ with a Where clause on the Outer File Causes Inner Join
Method to simulate a "right-justify" using SQL
Aggregating the Aggregates SQL Technique
Insert NULL data from a SELECT Technique
Self-Referenting update (updates a row with values from max previous value)
My SQL Catalog appears to be out of sync – what can I do?
Decimal Formatting with SQL
SQL Horizontal Pivoting Technique Coding Example
A simple "count distinct" Technique
A Simple SQL Join Skill Test
A Case-insensitive Search and Replace Technique
A Working Prototype of Dynamic SQL Program- written in SQL

Technique to reduce a variable amount of spaces to only a single space
SQL Technique to limit the number of records returned from a query
Technique to substitute variables using multiple CASE Statements
Technique to convert a UNIX date into a Gregorian Date using SQL
SQL Technique to compare consecutive Rows in a Journal File

Link to iSeries Target Search: SQL Server Reviews and Comparisons
Link to IBM's DB2 for iSeries SQL Programming Concepts
Link to IBM's DB2 for iSeries SQL Reference Table of Contents
Link to IBM's DB2 Documentation - Lots of good documentation and examples here

A good SQL Time and Date Processing Webpage
A good SQL Performance Hints and Tips Webpage
Download your own personal copy of DB2 at this IBM link!
DB2 XML Extender - SQL Extensions for XML

The Java API for Xml Processing (JAXP) Tutorial

W3 Schools SQL On-line learning
Conditional Sorting with SQL
V5R3 SQL Enhancements
SQL and Multi-Member Files
An “SQL  Basics” Basics Tutorial site

FluffyCat - An excellent SQL reference page with an unusual name
IT Jungle - An excellent technical site - great iSeries SQL tips

 

Other SQL Tutorial -  Outside Links

LINUX based SQL Tutorial with live practice database and Advanced LINUX based SQL Tutorial with live practice database
This unique introductory SQL tutorial not only provides a strong SQL tutorial, it also allows one to try SQL commands learned in the tutorial course using an on-line SQL interpreter. Immediate results are provided after submitting the SQL commands. You will be able to create your own unique tables as well as perform selects, inserts, updates, deletes, and drops on your tables. This SQL tutorial currently supports a subset of ANSI SQL. The basics of each SQL command will be covered in this introductory tutorial. Unless otherwise stated, the interpreter will support everything covered in this course.
DB2 UDB for iSeries SQL Programming Concepts V5R2
Best IBM iSeries manual for SQL programming
SQL School

This site has several "schools" - SQL School, HTML, XML, JavaScript. SQL school works fine but not all the schools on this website are as reliable.
A Beginner's Guide to SQL: A MySQL Tutorial
This is a very basic, single-page SQL tutorial (create/read/update/delete) provided by Toptal and Pete Sefton for blog site Udemy. There is a link to an online course, which one can take, for a fee.
"A Gentle Introduction to SQL"
This is a fully functional SQL tutorial, where you can pick any of the following SQL engines: MYSQL, SQLServer, Postgre, Access or Oracle.
SQL Wire

This site bills itself as the SQL Server News
Go Back to the Top

 

Tylogix - iSeries SQL Tips and Techniques

Two examples of how SQL can be used to search the Database Catalog:

How to find a table name, using it's object description:

SELECT SUBSTR(TABLE_NAME, 1, 20), CHAR(TABLE_TEXT) FROM SYSTABLES
WHERE LOWER(CHAR(TABLE_TEXT)) LIKE '%vendor master%'

How to find tables that use a field suffix - in this case, a portion of the field will contain "mail" and I am looking only in library "ALLCO"

SELECT DISTINCT SUBSTR( COLUMN_NAME,1, 10) COL_NAME,SUBSTR(CHAR(COLUMN_HEADING), 1, 30) COL_HEADING,
SUBSTR(COL.TABLE_NAME,1, 10) COL_TABLE, SUBSTR(CHAR(TAB.TABLE_TEXT  ), 1, 50) TAB_TEXT,  
SUBSTR(CHAR(TAB.TABLE_SCHEMA), 1, 30) TAB_SCHEMA
FROM SYSCOLUMNS COL INNER JOIN SYSTABLES TAB ON COL.TABLE_NAME = TAB.TABLE_NAME WHERE         
CHAR(COLUMN_NAME) LIKE '%MAIL%' AND CHAR(TAB.TABLE_SCHEMA) = 'ALLCO'


Go Back to the Top

On how to find duplicate records on iSeries using SQL, James from Kentucky asked :

I am trying to identify duplicate key fields within a file. What is the best way to do this?

Here is an easy to follow technique.

Assuming a table TABLE_X with column_1, column_2, and column_3, and assuming the aim is to find all columns who happen to have exact duplicate combination of data for these three columns: You would key, in the SQL command line the following command:

SELECT COLUMN_1, COLUMN_2, COLUMN_3, COUNT(*)
FROM TABLE_X
GROUP BY COLUMN_1, COLUMN_2, COLUMN_3


Go Back to the Top

Technique to Remove Duplicates from a Table

To remove duplicates from a table, assuming the table is keyed and that only the greatest key value is the one to keep, this is the technique:

DELETE FROM SAMPLETABLE
WHERE ID_NUMBER <
(
    SELECT MAX(ID_NUMBER)
    FROM SAMPLETABLE MAXVALUES
    WHERE MAXVALUES.NAME = SAMPLETABLE.NAME
)

An other method, using the Relative Record Number (RRN,  equivalent of Row ID in Oracle) is as follows:

DELETE FROM FILENAMEX H1
WHERE RRN(H1) <
(
SELECT MAX( RRN(H2) ) FROM FILENAMEX H2
WHERE H1.TRANSACTION_ID = H2.TRANSACTION_ID
)

Go Back to the Top

Technique to extract ONLY n Rows from a table

To limit the amount of results that a query may retrieve, the “FETCH FIRST N ROWS ONLY” is a useful technique:

SELECT * FROM TABLE_NAME
FETCH FIRST 10 ROWS ONLY

The last clause ensures that only 10 rows are fetched.

Go Back to the Top

On how to manipulate data on a remote system, Dan from California wrote:

I work with for a company who owns several systems, amongst which there are AS/400. I can FTP from/to these systems but no DDM is allowed. Would there be a way to manipulate data on a remote system using FTP?

Answer:
To reach a remote site with SQL, you need to first connect and ensure the remote site is in the system directory. This is explained in detail in the Tylogix Remote SQL Technique page

The best answer is perhaps not using FTP, even if a program can be remotely triggered on a remote system using the FTP instruction "QUOTE" - (more on this in the FTP Q&A Page).

Go Back to the Top

This SQL tip is the formula to find a job name in IBM's Job Scheduler, based on the contents of its command field:

This SQL statement will find any JS job which has the string '583550'
imbedded in the command line.

You can substitute '583550' with anything such as 'PGMABC' for example,
and find which JS Job runs that command.

SELECT T1.JMJOB, T1.JMGRP, T1.JMSEQ, T1.JMTEXT, T2.CMCSEQ, T2.CMCMD
FROM QUSRIJS/QAIJSMST T1
JOIN QUSRIJS/QAIJS1CM T2 ON
(T1.JMJOB = T2.CMJOB AND T1.JMGRP = T2.CMGRP
AND T1.JMSEQ = T2.CMSEQ)
WHERE T2.CMCMD like '%583550%'
ORDER BY T1.JMGRP, T1.JMSEQ, T2.CMCSEQ

Go Back to the Top

This SQL tip is the formula to find jobs that fail most frequently over time in IBM's Job Scheduler:

This query shows JS failures from the JS history file. 
This is a method to look for areas for improvement by bringing scheduled jobs that fail frequently over time.

SELECT JMGRP,JMJOB, JMSEQ, COUNT(*) FROM QUSRIJS/QAIJSHST WHERE
SUBSTR(DIGITS((1900*10000)+JMLRD),7,2) >=
SUBSTR(DIGITS(YEAR(CURDATE() )-1),9,2) AND (JMLCC = 'A' OR JMLCC =
'G' OR JMLCC = 'T' OR JMLCC = 'L') AND (JMAPPL = 'MI' OR JMAPPL =
'DW') GROUP BY JMGRP, JMJOB , JMSEQ ORDER BY 4 DESC      

Go Back to the Top

SQL Join Basics

Joining data from more than one table

Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can retrieve and join column values from two or more tables into a single row.

Several different types of joins are supported by DB2 UDB for iSeries: inner join, left outer join, right outer join, left exception join, right exception join, and cross join.

  • An Inner Join returns only the rows from each table that have matching values in the join columns. Any rows that do not have a match between the tables will not appear in the result table.
  • A Left Outer Join returns values for all of the rows from the first table (the table on the left) and the values from the second table for the rows that match. Any rows that do not have a match in the second table will return the null value for all columns from the second table. Nulls can be monitored and be defaulted with an
    IFNULL. For example: IFNULL(TABLEX.OBSERVATION_HR  ,    0)  will replace the value of TABLEX.OBSERVATION_HR with a 0 if a NULL was detected.
  • A Right Outer Join return values for all of the rows from the second table (the table on the right) and the values from the first table for the rows that match. Any rows that do not have a match in the first table will return the null value for all columns from the first table.
  • A Left Exception Join returns only the rows from the left table that do not have a match in the right table. Columns in the result table that come from the right table have the null value.
  • A Right Exception Join returns only the rows from the right table that do not have a match in the left table. Columns in the result table that come from the left table have the null value.
  • A Cross Join returns a row in the result table for each combination of rows from the tables being joined (a Cartesian Product).
  • A Simulating a Full Outer Join like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full outer join also returns non-matching rows from both tables; left and right.
  • Using Multiple Join Types in One Statement There are times when more than two tables need to be joined to produce the desired result. This is an example of how to proceed.

You can simulate a Full Outer Join using a Left Outer join and a Right Exception Join. See Simulating a Full Outer Join for details. Additionally, you can use multiple join types in a single statement. See Multiple join types in one statement for details.

Notes on joins

When you join two or more tables:

  • If there are common column names, you must qualify each common name with the name of the table (or a correlation name). Column names that are unique do not need to be qualified.
  • If you do not list the column names you want, but instead use SELECT *, SQL returns rows that consist of all the columns of the first table, followed by all the columns of the second table, and so on.

Go Back to the Top

Casting Data Types with SQL

Sometimes, it may be convenient to compare data which is stored in different data types. This is where casting comes in:

To cast alpha data as numeric, use the following formula. Note that this example also has a substring built-in:

SELECT @iNumValue = INT( SUBSTRING(CharColumnName, StartChar, Length)) from filename

To cast numeric data as alpha, use the following formula:

SELECT @icharValue = CAST( IntegerColumn, as char(5)) from filename
(5 in this example is the length of the character string to convert to)

Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can retrieve and join column values from

An other useful data manipulator I like is the SQL keyword STRIP, which takes only the non-blank characters out of a character column. This is especially useful if you are concatenating values like FIRST_NAME and LAST_NAME. If each of these columns have 50 charaters,  you end up with something that does not look very readable.
Using STRIP(FIRST_NAME) || '  ' || STRIP(LAST_NAME), you can get something relatively normal to read
Note: The TRIM function can also be used for this.

Go Back to the Top

Techniques to convert JD Edwards Julian Dates to MDY and Back using SQL

JD Edwards stores dates in a particular format:

- first digit is a 0 for dates prior to Y2K and 1 for dates after Y2K
- second and third digits are the last two numbers of a year i.e. for 2003 this value will be "03"
- third, fourth and fifth digits are the julian day (nth day of the year).

While this works well for a computer, it is not very readable by people. This can be easily changed for reading and reporting with SQL by using the following SQL formula, here is an example that can be used interactively with SQL:   SELECT date(char(1900000+sdupmj)) from f4211
-------------------------------------------------------------------------------------------
Here is an SQL function to convert a JDE Julian Date to a MDY date:

Calling example:
SELECT XJDETOMDY(105144) FROM SYSIBM/SYSDUMMY1

SQL Code:
CREATE FUNCTION XJDETOMDY (IN_JDE_DATE INT)     
RETURNS DATE                                    
LANGUAGE SQL                                    
BEGIN                                           
  DECLARE  OUT_YMD DATE ;                       
  SET OUT_YMD = DATE(CHAR(1900000+IN_JDE_DATE)) ;
RETURN (OUT_YMD) ;                              
END 

For reference and comparison, in Excel, the Julian to Gregorian conversion code is as follows:

        A       B
        ======  ===========
1   Julian  Gregorian
2   108100  04/09/2008

The contents of the cells A2 and B2 are:

        108100  =DATE(1900+INT(A2/1000),1,MOD(A2,1000))

Here is an SQL function to convert a MDY Date to a JDE Date

Calling example:
SELECT XMDYTOJDE(DATE('05/24/05')) FROM SYSIBM/SYSDUMMY1  

SQL Code:
 CREATE FUNCTION XMDYTOJDE (IN_YMD_DATE DATE)     
 RETURNS  INT                                     
 LANGUAGE SQL                                     
 BEGIN                                            
   DECLARE  OUT_JDE_DATE   INT     ;              
   DECLARE  OUT_JDE_PART1  CHAR(1) ;              
   DECLARE  OUT_JDE_PART2  CHAR(2) ;              
   DECLARE  OUT_JDE_PART3I INT     ;              
   DECLARE  OUT_JDE_PART3C CHAR(3) ;              
 CASE                                             
      WHEN IN_YMD_DATE < DATE('01/01/2000')       
        THEN SET OUT_JDE_PART1 = '0'    ;         
      ELSE                                        
        SET OUT_JDE_PART1 = '1'    ;              
 END CASE  ;                                      
 SET  OUT_JDE_PART2  =  SUBSTR(CHAR(IN_YMD_DATE), 7,2) ;                      
                                                                              
 SET  OUT_JDE_PART3I  =    DAYS(IN_YMD_DATE) -                                
       DAYS( DATE('01/01/' || OUT_JDE_PART2  ) )Ư   ;                        
 CASE                                                                         
      WHEN OUT_JDE_PART3I < 10                                                
        THEN SET OUT_JDE_PART3C = '00' || CHAR(OUT_JDE_PART3I) ;              
      WHEN OUT_JDE_PART3I < 100                                               
        THEN SET OUT_JDE_PART3C = '0'  || CHAR(OUT_JDE_PART3I) ;               
      ELSE                                                                    
             SET OUT_JDE_PART3C = CHAR(OUT_JDE_PART3I) ;                      
 END CASE  ;                                                                  
  SET OUT_JDE_DATE = INT(OUT_JDE_PART1 || OUT_JDE_PART2 || OUT_JDE_PART3C )  ;
 RETURN (OUT_JDE_DATE) ;                                                      
 END  

Technique to convert for Gregorian to Julian Dates (JDE format) using SQL

CASE WHEN SUBSTR( CHAR( YEAR( DATE('2009-03-19') ) ) , 1, 1) = '1'
THEN '0' ELSE '1' END ||
SUBSTR( CHAR( YEAR(DATE('2009-03-19')) * 1000
+ DAYOFYEAR(DATE('2009-03-19')) ) , 3, 5)    

What is the DB2 equivalent of Oracle's DUAL?

The name of the file on DB2 is sysibm/sysdummy1
A bit less handy than "DUAL", but it works the same. Here are two examples with dates.

SELECT char( DATE(timestamp( '2005-12-31-23.59.59.000000'))) FROM sysibm/sysdummy1
12/31/05

SELECT char( DATE(timestamp( '2005-12-31-23.59.59.000000') +7 days )) FROM sysibm/sysdummy1
01/07/06

Go Back to the Top

What are the SQL data type equivalents in C, RPG and COBOL?

The data type equivalence tables can be found in the following pages:

SQL and C Types
SQL and RPG Types
SQL and COBOL Types

Go Back to the Top

Defining an iSeries table with long field names and 10-character field references

Here is an example:

CREATE TABLE TESTPF
(
DOCUMENT_COMPANY FOR GLKCO CHAR(10) ,
DOCUMENT_TYPE FOR GLDCT CHAR(2) ,
DOCUMENT_NUMBER FOR GLDOC DEC(8,0)
);
--
LABEL ON TESTPF (DOCUMENT_COMPANY TEXT IS
'Document Company ');
LABEL ON TESTPF (DOCUMENT_TYPE TEXT IS
'Document Type ');
LABEL ON TESTPF (DOCUMENT_NUMBER TEXT IS
'Document Number ');
--
LABEL ON TABLE TESTPF IS 'Example Table';

Go Back to the Top

How to run an SQL Statement from a CL Program

1) First Enter your SQL Statement in a source member
2) Use the command RUNSQLSTM with the member name
3) An other solution may be to implement and use the EXCSQL command, authored by Dan Riehl, posted on this site.

Go Back to the Top

Creating an Integrity Check between two Tables Using SQL

This code shows how two files can be scanned for differences using SQL.
The equivalent can be achived with CMPPFM, but the SQL code is portable.

------------------------------------------------------------------------------ 
-- Delete ALL rows from Integrity Result Table INTRESULT                                             
------------------------------------------------------------------------------ 
                                                                               
DELETE FROM INTRESULT ;                                                       
                                                                               
------------------------------------------------------------------------------ 
-- Insert the rows that are different.                                         
------------------------------------------------------------------------------ 
                                                                               
INSERT INTO INTRESULT                                                         
  (                                                                            
  CAT_CODE_2                  ,                                                
  COIND                       ,                                                
  OBJECT_ACCOUNT              ,                                                
  RECORD_COUNT_DIFFERENCE     ,                                                
  SUM_DIFFERENCE              ,                                                
  COMMENT                                                                      
  )                                                                            
                                                                               
SELECT                                                                         
  BASETABLE.CAT_CODE_2                                            ,      
  BASETABLE.COIND                                                 ,     
  BASETABLE.OBJECT_ACCOUNT                                        ,       
  BASETABLE.SOURCE_RECORD_COUNT   -  NEWTABLE.TARGET_RECORD_COUNT  ,       
  BASETABLE.SOURCE_SUM            -  NEWTABLE.TARGET_SUM           ,         
  'Differences between Base Table and Source and New Table Totals'           
                                                                     
FROM BASETABLE                                                           
JOIN NEWTABLE                                                           
ON  BASETABLE.CAT_CODE_2             =   NEWTABLE.CAT_CODE_2                
AND BASETABLE.COIND                  =   NEWTABLE.COIND                    
AND BASETABLE.OBJECT_ACCOUNT         =   NEWTABLE.OBJECT_ACCOUNT            
where BASETABLE.SOURCE_RECORD_COUNT  <>  NEWTABLE.TARGET_RECORD_COUNT        
   or BASETABLE.SOURCE_SUM           <>  NEWTABLE.TARGET_SUM ;              
                                                                               
------------------------------------------------------------------------------ 
-- Insert the rows that are in BASETABLE but are not in NEWTABLE             
------------------------------------------------------------------------------ 
                                                                               
INSERT INTO INTRESULT                                                       
  (                                                                            
  CAT_CODE_2                  ,                                                
  COIND                       ,                                                
  OBJECT_ACCOUNT              ,                                                
  RECORD_COUNT_DIFFERENCE     ,                                                
  SUM_DIFFERENCE              ,                                                
  COMMENT                                                                      
  )                                                                            
                                                                               
SELECT                                                                         
                                                                               
  BASETABLE.CAT_CODE_2          ,                                         
  BASETABLE.COIND               ,                                         
  BASETABLE.OBJECT_ACCOUNT      ,                                        
  BASETABLE.SOURCE_RECORD_COUNT ,                                           
  BASETABLE.SOURCE_SUM          ,                                           
  'Records in BASETABLE (Source) but not in NEWTABLE(Target)'    
FROM BASETABLE                                                              
LEFT EXCEPTION JOIN NEWTABLE                                                 
ON  BASETABLE.CAT_CODE_2       =   NEWTABLE.CAT_CODE_2                     
AND BASETABLE.COIND            =   NEWTABLE.COIND                            
AND BASETABLE.OBJECT_ACCOUNT   =   NEWTABLE.OBJECT_ACCOUNT ;                 
                                                                               
------------------------------------------------------------------------------
-- Insert the rows that are in NEWTABLE but are not in BASETABLE           
------------------------------------------------------------------------------
                                                                              
INSERT INTO INTRESULT                                                      
  (                                                                           
  CAT_CODE_2                  ,                                               
  COIND                       ,                                               
  OBJECT_ACCOUNT              ,                                               
  RECORD_COUNT_DIFFERENCE     ,                                               
  SUM_DIFFERENCE              ,                                               
  COMMENT                                                                     
  )                                                                           
                                                                              
SELECT                                                                        
                                                                              
  NEWTABLE.CAT_CODE_2           ,                                             
  NEWTABLE.COIND                ,                                              
  NEWTABLE.OBJECT_ACCOUNT       ,                                             
  NEWTABLE.TARGET_RECORD_COUNT  ,                                             
  NEWTABLE.TARGET_SUM           ,                                             
  'Records in NEWTABLE (Data Warehouse summary) but not in BASETABLE (Source)'
FROM NEWTABLE                                                                 
LEFT EXCEPTION JOIN BASETABLE                                                 
ON  NEWTABLE.CAT_CODE_2       =   BASETABLE.CAT_CODE_2                       
AND NEWTABLE.COIND            =   BASETABLE.COIND                            
AND NEWTABLE.OBJECT_ACCOUNT   =   BASETABLE.OBJECT_ACCOUNT  ;                
                     
Go Back to the Top

Example of a simple text parsing Stored Procedure

This link points to the source for a simple text parser, created as an SQL Stored Procedure.
The challenge was to get item description data that was relatively well structured, but in Microsoft Excel format, into a table.
There was more than one description sentences per item and we needed to have each item description sentence split in discrete rows.
It all sounds simple, but we had to
- replace manual line feeds with a special stand-in character (we chose "~") to avoid un-wanted line breaks during FTP transfers (we had to use MS Word for that)
- FTP the data
- Parse out the data so that it could be accepted in that particular format at the other end.

The procedure uses two nested cursors. Here is the link to the Text-Parsing Stored Procedure Example.

                   
Go Back to the Top

Debugging an SQL Stored Procedure

Debugging a Stored Procedure is very much like debugging any other ILE C program:

Consider a simple SQL Stored Procedure that uses a cursor to update each row in a table with a unique time stamp (The example code is supplied below). The way to create the procedure in a way that will allow it to be debugged is as follows:     

RUNSQLSTM SRCFILE(OBJLIB/PROCEDURES) SRCMBR(UPDT_TIMEz) DBGVIEW(*SOURCE)

Note:
DBGVIEW(*SOURCE)  will step through the SQL code that you wrote. Easier to debug that way.
DBGVIEW(*LIST) will step though the ILE C program generated by the system to wrap around your SQL code. Interesting to visit but harder to debug.  

To start the debugging process, use the following expression:
STRDBG PGM(OBJLIB/UPDT_TIMEZ) UPDPROD(*YES) OPMSRC(*YES) DSPMODSRC(*YES)  

This will bring up the source of the C program created by the compiler to run the SQL procedure. Hit F10 without entering anything else.

Now, start SQL with the command STRSQL

Within the SQL command line, call the stored procedure, exactly as if you were calling it from a normal command line. The syntax is the same. Note however that if you called a stored procedure directly from the command line, it would crash. Stored procedures have to be called either from an STRSQL screen, or from a member executed with a STRSQLSTM or even from a QMQRY member.

CALL OBJLIB/SAMPLEPGM

Now, the program will start in debug mode and you can step (F10) through it, like any other C program. In addition, if you look at your joblog, you will see what choices the SQL Optimizer has made. (see the SQL Optimizer section in this page for the details)

Here is a sample procedure, using a cursor. It updates each record with a distinct timestamp.
--------------------------------------------------------------------------------
-- TEST SQL PROCEDURE                                                          
--------------------------------------------------------------------------------
                                                                               
CREATE PROCEDURE UPDT_TIMEZ                                                    
                                                                               
LANGUAGE SQL                                                                   
                                                                               
-- START PROCEDURE                                                             
BEGIN                                                                          
                                                                               
-- DECLARE CURSOR VARIABLES                                                    
DECLARE PUBLISH_TMS          TIMESTAMP ;                                       
DECLARE WORK_TIMESTAMP      TIMESTAMP ;                                       
DECLARE SQLSTATE             CHAR(5) DEFAULT '00000' ;                         
DECLARE AT_END               INT DEFAULT 0 ;                                   
DECLARE SQLCODE              INT DEFAULT 0 ;                                   
                                                                               
DECLARE CURSOR_UPD CURSOR FOR                         
SELECT PUBLISH_TMS FROM ER400SX MAIN;                 
SET AT_END = 0;                                       
                                                      
OPEN  CURSOR_UPD ;                                    
                                                      
FETCH_LOOP:                                           
LOOP                                                  
                                                      
    FETCH CURSOR_UPD INTO WORK_TIMESTAMP ;           
                                                      
    IF SQLCODE = 0  AND SQLSTATE = '00000' THEN       
       UPDATE ER400SX                                 
       SET PUBLISH_TMS = CURRENT TIMESTAMP, TIME_ELAPSED = DAY(CURRENT_TIME_STAMP – WORK_TIMESTAMP)            
       WHERE CURRENT OF CURSOR_UPD ;                  
    ELSE                                              
       SET AT_END = 1 ;                               
       LEAVE FETCH_LOOP ;                             
    END IF ;                                          
                                                      
END LOOP  ;                     
                                
CLOSE CURSOR_UPD ;              
                               
END     

  Go Back to the Top

Debugging Stored Procedures: SQLCODE, Variables and DIAGNOSTICS

The variable SQLCODE is actually a sub-field of a data structure named SQLCA.
Here are the steps to debug it:

1) Start your debugger with the following instruction:
STRDBG PGM(PGMNAME) UPDPROD(*YES)
The debugger will then show the source.
Hit F10.
The debugger will leave the source instantly.


2) Call your program:
CALL PGM(PGMNAME)
- The debugger will now start for real. Hit
- F10 to step through a function
- F22 to Step INTO a function and see each line or component going through


3)
To see the value of SQLCODE in V5R2, use the following command:
EVAL sqlca (Make sure that "sqlca" is in lower case)
This instruction will display the entire SQLCA data structure, SQLCODE is actually a sub-field of the SQLCA data structure. To interpret the code, go to the IBM SQL Website.Within that page is a search field. Search for the string "SQL messages and codes". A search result box will appear. Hit the first link, "SQL messages and codes". Within the resulting page, look for the link saying "SQL Message Finder" Hit that link. This is a good place to start investigating.

4) Getting Diagnostic information from SQL

DECLARE SQLCODE INT DEFAULT 0 ; 
DECLARE SQL_EXCEPTION INT ;
DECLARE RECORD_COUNT INT DEFAULT 0 ;    
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET SQL_EXCEPTION = 1 ;
 
GET DIAGNOSTICS RECORD_COUNT = ROW_COUNT ;                   
SET INSERT_COUNT_CM999 = INSERT_COUNT_CM999 + RECORD_COUNT ; 

To get all the possible debugging and diagnostic values available through the GET DIAGNOSTIC SQL verb, go to : http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/index.htm?info/db2/rbafzmstincludesqlca.htm

5)To see the value of a character string in a C/400 debug session, use the following formula:
EVAL *variablename :s 12     (where 12 is the length of the string you wish to see)

6)To see the value of ALL LOCAL VARIABLES in your C/400 debug session, use the following formula:
EVAL %LOCALVARS

Go Back to the Top

Deleting Duplicate Records from a Table Using SQL

A simple Correlated Delete Example Using SQL. The value of "select 1" in this situation is really there to verify that the "where exist" condition is true.

DELETE FROM MAIN_TABLE MAIN
WHERE EXISTS 
      (SELECT 1 FROM UPDATE_TABLE UPDT
              WHERE MAIN.KEY_ID=UPDT.KEY_ID) 

A good way to test the number of rows to be affected by this delete would be to replace the first line with
SELECT COUNT(1)FROM MAIN_TABLE MAIN
In most cases using 1 will produce the same result as using *. The only time it may differ is the situation where all fields in a record are NULL. A better illustration of the utility of using a 1 is to compare the results with a query that uses a specific field that sometimes contains NULL values.

A Correlated Delete Example Using other conditions with the correlation

DELETE FROM CONTACT_TABLE AD1
       WHERE AD1.ID_NUMBER <
             (
               SELECT MAX(AD2.ID_NUMBER)
               FROM CONTACT_TABLE AD2
               WHERE (
                       AD1.ADDRESS_1 = AD2.ADDRESS_1 AND
                       AD1.ADDRESS_2 = AD2.ADDRESS_2 AND
                       AD1.ADDRESS_3 = AD2.ADDRESS_3
                      )

Go Back to the Top

Joining with Unreliable Numeric Data Stored in a Character Variable

Up until now (V5R3), SQL on the iSeries has no built-in method to determine if a character column contains valid numeric data. Here is a simple way to do this, using the keyword LOCATE. LOCATE determines the position of a string within an other string. If it is not found a zero is returned. Using this technique, one can compare each individual position of the column and figure out if the data is numeric or not, and in the latter case assing a default numeric value. The result of this case can be used to do a reliable join with a numeric key.

Example 1 - strictly numeric data is acceptable:


LEFT OUTER JOIN FAMRFCUS FCUS ON
CASE
WHEN LOCATE(SUBSTR(WEBLOG_RAW_DATA , 22, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 23, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 24, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 25, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 26, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 27, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 28, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 29, 1), '0123456789') = 0
OR LOCATE(SUBSTR(WEBLOG_RAW_DATA , 30, 1), '0123456789') = 0
THEN 0
ELSE DEC( SUBSTR(WEBLOG_RAW_DATA, 22, 9) )
END
= FCUS.RFID_CUSTOMER_ID
                            

Example 2 - Allows for blanks :

SELECT * FROM FILEAA AA
LEFT OUTER JOIN FILEBB BB
ON
CASE WHEN
(
LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,1, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,2, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,3, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,4, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,5, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,6, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,7, 1),' 0123456789')<>0
AND LOCATE(SUBSTR(AA.PURCHASE_ORDER_NUMBER,8, 1),' 0123456789')<>0
)
THEN INT(AA.PURCHASE_ORDER_NUMBER)
ELSE 0
END
= BB.NUMERIC_PO_NUMBER

Go Back to the Top

DB2 SQLCaveat! Left Outer Join with a Where clause on the Outer File Causes Inner Join

This is a "DB2 FEATURE": When using Left Outer Join with a WHERE on the outer joined table, the join actually turns itself into an INNER JOIN. Here is an example of this "feature":

This is the left table:

==============================================================================================
SELECT * FROM LEFT_TABLE  
LEFT_PK        RIGHT_FK   DESC         
      1               3   left row 1   
      2               4   left row 2   
      3               0   left row 3   
**  End of data  ********              

This is the right table:
==============================================================================================
SELECT * FROM RIGHT_TABLE 
RIGHT_PK   DESC        
       1   right row 1 
       2   right row 2 
       3   right row 3 
       4   right row 4 
***  End of data  ******

This is a select from the Right table that yields 100% of the results
==============================================================================================
SELECT * FROM RIGHT_TABLE 
WHERE RIGHT_TABLE.DESC != 'HELLO'   
RIGHT_PK   DESC        
       1   right row 1 
       2   right row 2 
       3   right row 3 
       4   right row 4 
***  End of data  ******

Query that produces a proper left outer join - note the nulls on the third row result:
==============================================================================================
SELECT * FROM LEFT_TABLE LEFT OUTER JOIN RIGHT_TABLE
ON RIGHT_FK = RIGHT_PK                                          

LEFT_PK  RIGHT_FK   DESC         RIGHT_PK   DESC          
      1         3   left row 1   3          right row 3   
      2         4   left row 2   4          right row 4   
      3         0   left row 3   -          -             
***  End of data  ********                                 

Identical query but contains a condition using the right table:
==============================================================================================
SELECT * FROM LEFT_TABLE LEFT OUTER JOIN RIGHT_TABLE
ON RIGHT_FK = RIGHT_PK WHERE RIGHT_TABLE.DESC != 'HELLO'            

LEFT_PK   RIGHT_FK    DESC         RIGHT_PK   DESC          
      1          3    left row 1   3          right row 3   
      2          4    left row 2   4          right row 4   
***  End of data  ********                                 

Go Back to the Top

Method to simulate a "right-justify" for character data using SQL

There is no "right-justify" string function built into SQL, but it can still be done: Taking a character string of variable size and right-justifying within a target column
containing a maximum of 12 characters. The example below does a bit more but it illustrates well what can be done, effectively concatenating blanks (up to 12) for a length of 12 - the length of the source column.


INSERT INTO TARGET_TABLE
SITE_CODE_RIGHT_JUSTIFED
SELECT
SUBSTR(' ', 1, 12 -
LENGTH( TRIM( CHAR_COLUMN_NAME)))
|| TRIM( CHAR_COLUMN_NAME )
FROM TABLE              

FROM SOURCE_DATA

Go Back to the Top

Executing Correlated Deletes using SQL and a correlated sub-select

Using SQL to do Correlated deletes - Take note of the two WHERE clauses:

(DELETE FROM EMPLOYEE_TABLE EM
WHERE EXISTS
(SELECT * FROM UPDATE_TABLE UPDT WHERE UPDT.ID = EM.ID);

(Note: this technique - using a join as opposed to a correlated sub-select -  is available in some SQL flavors, such as MYSQL but NOT YET in DB2 for iSeries)


Go Back to the Top

Executing Correlated Updates using SQL on the iSeries – you have to use a correlated sub-select

Using SQL to do Correlated updates - Take note of the two WHERE clauses:

UPDATE EMPLOYEE_TABLE EM
SET (EM.PAY_SCALE, EM.SALARY) =
(SELECT NPAY.PAY_SCALE, NPAY.SALARY FROM NEW_PAY NPAY WHERE NPAY.ID = EM.ID )
WHERE EXISTS
(SELECT *
FROM NEW_NEWPAY NPAY WHERE NPAY.ID = EM.ID )

If there are more than one row matching the update criteria, using the “DISTINCT” clause may be a valid solution, as it will bring back only one row:

UPDATE EACPPOMATH
SET PO_IN_GR = (
                 SELECT DISTINCT
                 PO_REFERENCE_NUMBER

                 FROM EACPLGR5AR
                 WHERE LEGACY_PO_NUMBER =  PO_REFERENCE_NUMBER
                )
WHERE EXISTS
(
   SELECT *
   FROM EACPLGR5AR
   WHERE LEGACY_PO_NUMBER =  PO_REFERENCE_NUMBER
)

The following method is not yet available on DB2/400 but it is pretty neat and works on other databases:

Using SQL to do Correlated updates can be done in two ways: The first one is using an inner join, the second one can be done using a correlated sub-select.
(Note: this technique - using a join as opposed to a correlated sub-select -  is available in some SQL flavors, such as MYSQL but NOT YET in DB2 for
iSeries)

 The inner join technique effectively says something like update the set of data as it come in from two sources joined by a key:

UPDATE
EMP_TABLE1 INNER JOIN EMP_TABLE2
ON EMP_TABLE1.EMP_NBR = EMP_TABLE2.EMP_NBR
SET EMP_TABLE1.BENEFIT_NBR = EMP_TABLE2.BENEFIT_NBR
WHERE EMP_TABLE1.BENEFIT_NBR <> EMP_TABLE2.BENEFIT_NBR

--------------------------------------------------------------------------------
-- An other example:
-- Double Correlation Update, to update only the largest date for
-- a given set of rows determined by a key in a secondary table.
--------------------------------------------------------------------------------
UPDATE FBSNHR HOURS_MAIN
SET HOURS_MAIN.EFCTV_TO_DTE = DATE('4000-01-01')
WHERE EXISTS
 ( SELECT 'YES' FROM HOURSFIX FIX
    WHERE STRIP(FIX.FIXSTORE) = STRIP(HOURS_MAIN.BUSINESS_UNIT_NBR)
    AND HOURS_MAIN.EFCTV_TO_DTE =
       ( SELECT MAX(HOURS_SUB.EFCTV_TO_DTE) FROM FBSNHR HOURS_SUB
         WHERE STRIP(HOURS_SUB.BUSINESS_UNIT_NBR) = STRIP(FIX.FIXSTORE)
       )
 )

Go Back to the Top

Finding where (in which library) a DDL-built table with a long name may be

To find the library where a long-name (SQL naming convention) table may be on the system, use the following query in the Catalog Table SYSTABLES:

SELECT
TABLE_NAME, TABLE_SCHEMA FROM QSYS2/SYSTABLES
WHERE TABLE_NAME = 'MONTH_TO_DATE_SALES';

Go Back to the Top

Targeting one or more members in a multi-member file using SQL using ALIAS and UNION

To gather the data from multiple members in an iSeries table, here is a technique that uses ALIAS to target individual members and UNION to retrieve the distinct rows:

CREATE ALIAS LIBRARY1/SALES_HIST_1999
FOR LIBRARY1/SALESHIST(MBR_HST_99)

CREATE ALIAS LIBRARY1/SALES_HIST_2000
FOR LIBRARY1/SALESHIST(MBR_HST_00)

SELECT * FROM LIBRARY1/SALES_HIST_1999
UNION
SELECT * FROM LIBRARY1/SALES_HIST_2000

Go Back to the Top

Converting local TIMESTAMP value to and from a GMT TIMESTAMP

Here are some handy timestamp routines, including a Greenwitch Mean Time (GMT) conversion:

TIME Retrieval using CURTIME function

SELECT curtime() FROM sysibm/sysdummy1

DATE Retrieval using CURDATE function
SELECT curdate() FROM sysibm/sysdummy1

CURRENT TIMESTAMP Retrieval using NOW function
SELECT now() FROM sysibm/sysdummy1

GMT TIMESTAMP using NOW and TIMEZONE
select now()- current timezone from sysibm/sysdummy1

Go Back to the Top

Exploring the DB2 Catalog

All the Database Objects in DB2 are classified and organized with a link in the DB2 Catalog. The DB2 Catalog tables all start with SYS* and they all reside in library QS2. Examples of the most well known DB2 Catalog tables are:

Catalog Table Description
SYSCOLUMNS: Columns
SYSCST: Constraints
SYSFUNCS:  Functions
SYSINDEXES: Indexes
SYSKEYS: Keys
SYSPROCS: Procedures
SYSTABLES: Tables
SYSTRIGGER: Triggers
SYSVIEWS: Views

Example of the use of the DB2 Catalog: "Where is this column (field) name used?" :

SELECT * FROM QSYS2/SYSCOLUMNS
WHERE COLUMN_NAME LIKE '%MCU%'

Go Back to the Top

Global Character or String Replace in SQL

Global character or string replacement is very simple with SQL using the keyword REPLACE.

In this example this technique is used to replace individual HEX Characters in a table.
Specificaly, a carriage-return special character is replaced with a "|" character.


UPDATE DATA_TABLE SET DESCRIPTION = REPLACE( DESCRIPTION , X'25' , X'4F' )

The same technique can be used to replace strings:

UPDATE DATA_TABLE SET DESCRIPTION = REPLACE( DESCRIPTION , 'THIBAULT' , 'Thibault' )

Go Back to the Top

Creating Characters by Specifying the Hexadecimal Value in SQL

If necessary, one can enter HEX characters directly while using SQL. This may be practical when dealing with accents and foreign languages for example: HEX(43) is Ñ.

INSERT INTO SAMPLE_TABLE
SAMPLE_COLUMN
SELECT HEX(43)                  
FROM SYSIBM/SYSDUMMY1

Go Back to the Top

Finding (locating) Hexadecimal Values in SQL

Locating a special character, like a carriage return or any other un-printable character is relatively easy with SQL.
This is especially handy when sub-stringing or parsing unstructured data. Here is an example:


SELECT LOCATE(X'25', GDTXVC, 1),            -- show the position
SUBSTR(GDTXVC, 1, LOCATE(X'25', GDTXVC, 1)) -- substring from 1 to location
FROM F00165
WHERE LOCATE(X'25', GDTXVC, 1) < 50         -- only if location is < 50 positions                  
FROM SYSIBM/SYSDUMMY1

Go Back to the Top

Technique to Call Programs or Execute CL Commands from SQL

The technique is simple: Simply call QCMDEXC, add the command as a parameter and the length of the command as a subsequent parameter, with the lenght in the numeric format shown below. For example, here is an override, followed by a call:

CALL QCMDEXC('OVRDBF FROMFILE(FILEA) TOFILE(FILEB)',0000000036.00000);
or
CALL QCMDEXC('CALL TESTLIB/TESTCLPGM',0000000022.00000);

Go Back to the Top

Difference between a Stored Procedure and a Function

Stored Procedures have the following charactetistics:
- They can accept n parameters
- They compile as an object of type *PGM, which has to be called from an SQL environment to work
- They can return n parameter values
- Best use for Stored Procedures: when one needs to do record-by-record processing - which can be done using a cursor



Functions have the following charactetistics:
- They can accept n parameters
- They compile as an object of type *SRVPGM which has to be called from an SQL environment to work
- WILL RETURN 1 AND ONLY 1 VALUE
- Best use for functions: For simple, often repeated, formula-intensive calculations


Go Back to the Top

Method to find the SQL Optimizer suggestions to improve program or stored procedure SQL performance

If you find your SQL is sluggish and you don't know why, you may want to know what the Operating System - or more specifically the SQL optimizer - is thinking. This is possible by using the debugger. For a given SQL routine that is too slow and that you wish to analyze, do the following:

Step 1: If this is an individual SQL statement, cut and paste your SQL into a QMQRY member. If this is a stored procedure, go to step 2.

Step 2: Start Debug using STRDBG UPDPROD(*YES) CHGJOB LOG(4 4 *SECLVL)- this will change the job to record all activities and second level text. With *SECLVL, both the message text and the message help (cause and recovery) of the error message are written to the job log.

Step 3: If we are dealing now with a QM member, run the freshly created QM (STRQM and hit 9 on your new member), otherwise if it is a stored procedure, call the stored procedure from within an SQL environment (STRSQL and do "CALL LIBNAM/PROC_NAME" from the SQL command line or RUNSQLSTM of a member containing "CALL LIBNAM/PROC_NAME")

Step 4: Do a WRKJOB and look at the joblog. Look for
**** Starting optimizer debug message for query and Access path suggestion for file
(Note: DB2 Optimizer suggestions may or may not appear)
These messages will show you what the SQL optimizer is looking for and the suggestions it may have made for you to improve the performance of your SQL code.
Typically the Optimizer will suggest indexes, with precise key orders.

Go Back to the Top

Technique to generate a Surrogate Key using the Current Time Stamp as a base

Of all possible key formats, the fastest ones are single-column, numeric keys. When a natural key is too long, to improve performance, using a unique numeric surrogate key to represent a unique natural key is a common technique. The question is: how does one reliably generate surrogate keys on the fly when writing a new row in a table? The solution below is both simple and easy. It relies on the fact that a timestamp on iSeries is accurate to one millionth of a second. It is therefore a reliable source for a unique surrogate key. The code below shows how one can use the timetamp to get a unique number, essentially converting the time stamp to a numeric value:

SELECT
SUBSTR(CHAR(NOW()), 1, 4) || SUBSTR(CHAR(NOW()), 6, 2) ||
SUBSTR(CHAR(NOW()), 9, 2) || SUBSTR(CHAR(NOW()),12, 2) ||
SUBSTR(CHAR(NOW()),15, 2) || SUBSTR(CHAR(NOW()),18, 2) ||
SUBSTR(CHAR(NOW()),21, 6)
FROM SYSIBM/SYSDUMMY1

Go Back to the Top

Aggregating Aggregates SQL Technique

Aggregate the result of multiple count(*) records: Note how the “TOTALDUPS” variable is used to name the COUNT(*) result, and further used to total up all duplicates.
Note also the “COUNT(*) -1” notation. The result of this operation only counts the duplicates (i.e. if there were two identical records, one would be deemed valid and the second one would be deemed to be the duplicate).

SELECT SUM( TOTALDUPS )

FROM (
               SELECT POS.COIND, POS.ILITM, POS.ILMCU, COUNT(*) -1 TOTALDUPS
               FROM POS_TABLE POS
               GROUP BY POS.COIND, POS.ILITM, POS.ILMCU
               HAVING COUNT(*) >2
        ) TABLEX                    

 SUM ( TOTALDUPS )                
        249


Combine multiple techniques using UNION ALL: Notice the use of “zero and ONHAND” and then “zero and ORDERED”, which enables the UNION – as in the two sets are identical. Now we can get the sum of orders and the matching sum of on-hands (two figures from two different tables).

SELECT SKU, SUM(ORDERED) UNION_ORD  ,SUM(ONHAND) UNION_OH           
FROM 

    (                                                         
   SELECT ORDSKU SKU, ORDQTY ORDERED,  0 ONHAND
   FROM ORDERS                                             
 
UNION ALL                                                 
   SELECT INVSKU SKU, 0 ORDERED,  INVOHQ ONHAND         
   FROM INVENTORY  

    )

 TABLEX                                                  

 GROUP BY SKU                                            

 HAVING SUM(UNION_ORD) > SUM(UNION_OH)    

SKU         UNION_ORD    UNION_OH
215996AA     274.0000      0.0000
215996LB      12.0000      0.0000
51789764     720.0000    242.0000

Go Back to the Top

Technique to insert NULL FROM SELECT data statement as a source:

Technique to insert NULL data when a SELECT is used for the source of the insert: Notice the cast of the NULL, this is the feature that enables this technique. The other method is to completely omit the content of TARGET_COL2 in the SELECT, but this method is more explicit, deliberate and easier to read.

INSERT INTO TARGET_TABLE        
(
  TARGET_COL1,
  TARGET_COL2
)                    
SELECT SRCTBL.START_DATE,
CAST(NULL AS NUMERIC)     

FROM SOURCE_TABLE SRCTBL      

Go Back to the Top

Technique to enable self-referential, previous record updating current record:

This technique enables a self-referential update that is sequential – i.e. record x with a key of SITE_CODE, PRODUCT, START_TIMESTAMP.  In this process, each row in table SITE_PROD_DAT_TABLE needs to be updated with a value contained in the immediate preceding row with the same SITE_CODE and PRODUCT.  To do this, the SITE_PROD_DAT_TABLE is correlated three times in the same SQL statement:

- The MAIN correlation will be the target of the UPDATE

- The UPDTDTA  correlation will get the value which will be used to update MAIN (actually, this is a subtraction update, if the value is not found, the IFNULL will subtract itself and leave a zero value in MAIN)

- The MAXBELOWKEY will get the maximum date below the current row with a key that matches SITE_CODE and PRODUCT

Note that it is CRITICAL to have good indexes to get good performance on this type of join. For this statement, the look-back period is only 21 days, which also helps limit the amount of searching. The index for
SITE_PROD_DAT_TABLE is by order of cardinality: site_code, product, start_timestamp.

UPDATE SITE_PROD_DAT_TABLE MAIN     

SET MAIN.price_delta =
    MAIN.value – IFNULL
  (
      ( SELECT UPDTDATA.value    
        FROM SITE_PROD_DAT_TABLE UPDTDATA   
        WHERE UPDTDATA.site_code = MAIN.site_code   
        AND UPDTDATA.product = MAIN.product  
        AND UPDTDATA.start_timestamp =
           (
              SELECT max(MAXBELOWKEY.start_timestamp)  
              FROM SITE_PROD_DAT_TABLE MAXBELOWKEY  
              WHERE MAXBELOWKEY.site_code = MAIN.site_code   
              AND MAXBELOWKEY.product = MAIN.product  
              AND MAXBELOWKEY.start_timestamp >= current date - 21 days  
              AND MAXBELOWKEY.start_timestamp < MAIN.start_timestamp
           )
       )
   , MAIN.value
   )     

WHERE MAIN.start_timestamp >= current date - 21 days  

Go Back to the Top

My SQL Catalog appears to be out of sync – what can I do?

I stumbled across a situation where I had a unique key on a table but could not find any constraint for this table in the QSYS2/SYSCST.

Answer: There can be more than one cause:

1) If the unique key was created using a logical file with a unique key (built with a DDS), the constraint will be there but it will not appear in the catalog file QSYS2/SYSCST.

2) If the catalog did go out of sync, the only way to repair it is with a reclaim storage operation, as shown below:


RCLSTG SELECT(*DBXREF)
  

Go Back to the Top

Using Decimal Formatting in SQL

This is a simple trick to format decimal data in SQL. Particularly useful when creating new tables with a simple select statement.  

SELECT DEC(MONEY_TABLE.DOLLARS_COLUMN/10000 , 15 , 4) from TABLE   

Note that the division must be inside and the Decimal formatting outside. This technique is especially useful for pulling JDE data with accurate decimal information.

To round down the figure above from 4 to 2 decimals only, you can wrap it again using the following technique:
SELECT DEC( DEC(MONEY_TABLE.DOLLARS_COLUMN/10000 , 15 , 4), 15, 2)

To round down the figure above from 4 to 2 decimals only, you can wrap it again using the following technique:
SELECT DEC( CASE WHEN                                           
            STRIP(PDCRCD) = 'CAD'                     
            THEN PDAEXP/100                           
            ELSE PDFEA/100                            
            END                
            , 15,2)    EXTENDED_PRICE

Note: Be careful when formatting aggregate values:

==================================================

SUM( DEC(DOMESTIC_OPEN_AMT/100, 14,2)) - Works

DEC( SUM(DOMESTIC_OPEN_AMT) /100, 14,2) - Does not Work

Go Back to the Top

SQL Horizontal Pivoting Technique Coding Example

The following links point to a real example, with each item belonging to up to 16 business units,
arranged in up to 16 rows. The pivoting spreads the business units the item belongs to sideways.


Pivot_SQL_Code_001
Pivot_SQL_Code_002
Pivot_SQL_Code_003
Pivot_SQL_Code_004  


Go Back to the Top

A simple "Count Distinct" Technique

Count Distinct sounds obvious, but often, I have to think of the syntax again to make it work. Here is a working example:

SELECT COUNT ( DISTINCT MATERIAL_NUMBER) FROM MATERIAL_TABLE  

Go Back to the Top

A Universal or "case insensitive" Search and Replace Technique

Search for a string and find it, no matter in which case it is written in, and replace with a new string - the trick is the UCASE keyword:

REPLACE (ITEM_DESCRIPTION, UPPER('oem') , 'MANUFACTURER' )  

Yields an upper-case result only.

The code below keeps the old case.

------------------------------------------------------------------------------
-- Case Independent String Replacer
-- This code was created to replace non-standard descriptions
-- such as SN, S/N, SerNo, Serial#, Serial NO to at standard "Serial Number"
--
-- This replacer is different from most, as it
--      - finds the string in any possible case
--      - preserves the original on both side of what is found
--      - most practical for search/replace which have to leave the  
--        original string in the original case.
------------------------------------------------------------------------------
SELECT
       SUBSTR(TEMPPF , 1, 20)                        ORIGINAL,

CASE WHEN LOCATE('OEM', UPPER(TEMPPF) ) <> 0               -- Yes there!
     THEN SUBSTR(TEMPPF, 1, LOCATE('OEM', UCASE(TEMPPF)) - 1)

     ||   'MANUFACTURER'

     || CASE WHEN SUBSTR(  TEMPPF,                               -- SUBSTR strg
                  LOCATE('OEM', UPPER(TEMPPF) ) + LENGTH('OEM'), -- SUBSTR from
                      LENGTH(TEMPPF) -
                      LOCATE('OEM', UPPER(TEMPPF))+ LENGTH('OEM')-- SUBSTR to
                    )= ''   -- NOTHING MORE TO ADD.
        THEN ''
        ELSE -- THERE IS SOMETHING AFTER THE SEARCH STRING.
                SUBSTR( TEMPPF,                                  -- SUBSTR strg
                LOCATE('OEM', UPPER(TEMPPF))  + LENGTH('OEM') ,  -- SUBSTR from
                 LENGTH(TEMPPF) -
                 LOCATE('OEM', UPPER(TEMPPF))+ LENGTH('OEM') +1) -- SUBSTR to
        END
 ELSE
        TEMPPF   -- Nothing to replace, just yield the original string
 END
    REPLACEMENT_RESULT
 FROM CATDA7/TEMPPF

--------------------------------------------------------------------
ORIGINAL              REPLACEMENT_RESULT                           
--------------------  ----------------------------------------------
OEm test replacement  MANUFACTURER test replacement                
test OEM replacement  test MANUFACTURER replacement                
test replacement OEM  test replacement MANUFACTURER                
test replacement OeM  test replacement MANUFACTURER with mixed CaSe
  oem                   MANUFACTURER                               
                oem                   MANUFACTURER        

Go Back to the Top

An SQL Technique to limit the number of records returned from a query.

This technique is handy to ensure too many rows are not retrieved. It can also be used in a co-related sub-select update, to ensure only one row is used in the update operation.
In this example, I used 1, but it could be any number.

SELECT * FROM syscolumns WHERE COLUMN_NAME = 'DSTCTL'  LIMIT 1


Go Back to the Top
An SQL Technique to reduce a variable amount of spaces to only a single space.

This tip comes to me with credit from IT Jungle from Sean. It leaves only one space where multiple spaces may exist.

SELECT Replace ( Replace( Replace(name,' ','<>' ),'><','' ),'<>',' ' )   


Go Back to the Top

An SQL Technique to substitute variables depending on value using multiple CASE statements.

This tip is long over-due,  credit goes to my colleague  Prasanna. Note:
 > This is a most useful technique for a quick transformation in an ETL situation.
 > The CASE Statement will yield a NULL if no ELSE clause is specified.

SELECT ET.EMPLOYEE_NO, ET.FIRST_NAME, ET.LASTNAME,
CASE
        WHEN ET.YEARS_OF_SERVICE > 25
            THEN ‘ELIGIBLE FOR RETIREMENT’
        WHEN ET.YEARS_OF_SERVICE > 15
            THEN ’15 YEARS OR LESS TO GO!’
        ELSE ’TAKE A DEEP BREATH!’
END
FROM EMPLOYEE_TABLE ET
 

Go Back to the Top

Technique to convert a UNIX date into a Gregorian Date using SQL

SELECT DATE((( 1250703000 - (6*3600) ) / 86400 ) + 719163 ) FROM SYSIBM/SYSDUMMY1

Note:
●  1250703000 is the Julian date to convert (replaceable with a variable in your application). In this example, 1250703000 converts to August 19, 2009.
●  6 is the Greenwich offset in terms of hours (e.g. Mountain time is – 6 hours)
●  The file sysibm/sysdummy1 is the equivalent of Oracle’s “DUAL” table, which will yield only one value. It is used only for this example. You can select your Unix dates from any other file.

Conversely,

CAST (DAYS(CURRENT TIMESTAMP) - DAYS('1970-01-01') AS INTEGER) * 86400 + (MIDNIGHT_SECONDS(CURRENT TIMESTAMP - CURRENT TIMEZONE))

will return the number of seconds since 1970-01-01, the "UNIX date" - at the time of execution.

IBM has a link on this topic located at
: http://www-01.ibm.com/support/docview.wss?uid=swg21376100


Go Back to the Top

SQL Technique to compare consecutive Rows in a Journal File to find changes

This technique is useful sniff out variations within a specific field in a file journal. In this case, a margin change.

POJRNDLY01: Generate OUTFILE FILE POJRNDLY01, which is simply ensuring the data is ordered by key and
timestamp.  The Timestamp order is critical. It will ensure we can use the RRN for the next query to get the logical previous row.
This can be done with QMQRY or EXCSQL.


 SELECT *                                                                 
 FROM PO_FILE  ORDER  BY  POCMPN, BWCUSN, BWTIMSTP     

POJRNDLY02: PO Daily Margin Change Journal Query    
Generate OUTFILE FILE POJRNDLY02, which will query POJRNDLY01 and join it to itself to get the previous row
for each record read.
The idea is to kick out a new row for every single margin change
  SELECT                                                                       
        AA.BWCMPN, AA.PODIVN, AA.PODPTN, AA.POCUSN,AA.POTTNP,  
        BB.PODCTP MARGIN_BEFORE, AA.PODCTP MARGIN_AFTER,         
        AA.PODCTP - BB.PODCTP MARGINDIFFERENCE,   
        char(BB.POTIMSTP      ) BEFORE_TIMESTAMP,     
        char(AA.POTIMSTP      ) AFTER_TIMESTAMP,  
        BB.POPGMNAM,                     
        CURRENT TIMESTAMP CURRENT_TIMESTAMP
  FROM            POJRNDLY01 AA  
      INNER JOIN POJRNDLY01 BB
           ON RRN(AA)-1 = RRN(BB)  
        AND AA.POCMPN = BB.POCMPN    
        AND AA.POCUSN = BB.POCUSN  
        AND BB.PODCTP <> AA.PODCTP                                                                         
      WHERE AA.PODCTP <> 0      
      AND   BB.PODCTP IS NOT NULL     
  

Go Back to the Top