Category: SQL Tips

After the database has been upgraded to DB2 12, you can use the below SQLs to identify the current level:

select catalog_level from sysibm.sysdummy1;                   
---------+---------+---------+---------+---------+---------+--
CATALOG_LEVEL                                                 
---------+---------+---------+---------+---------+---------+--
V12R1M500                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                        
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100   
---------+---------+---------+---------+---------+---------+--

Another option:

SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1    
---------+---------+---------+---------+---------+---------+--
                                                              
---------+---------+---------+---------+---------+---------+--
DSN12010                                                      
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                        
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100   

One of our users created a somewhat complex update statement that was intended to update about 1600 rows in a table. The update statement was coded to eliminate the possibility of duplicate rows, however we continued to encounter -803 responses.

SQLCODE = -803, ERROR:  AN INSERTED OR UPDATED VALUE IS INVALID       
BECAUSE INDEX IN INDEX SPACE UNQNDX1 CONSTRAINS COLUMNS OF THE TABLE 
SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.         
RID OF EXISTING ROW IS X'00000FC90F'.

We were somewhat at a loss to identify which row was causing this error. The below SQL was able to identify the row that was causing the issue. This also identified the logic error in the update SQL.

SELECT  RID(owner.table_name), owner.table_name.*  
  FROM  owner.table_name                            
WHERE   RID(owner.table_name) = 1034511             
;

The hex value reported above was X’00000FC90F’. Use the below calculator to convert this to decimal:

http://www.rapidtables.com/convert/number/hex-to-decimal.htm

The following SQL will return a formatted DB2 version and a literal U.S. Timezone value for the current date.  The CURRENT TIMEZONE value changes between Daylight Saving time and Standard time so the SQL takes that into consideration based on the current DST start on Second Sunday of March and end on First Sunday in November.

SELECT STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),4,2),L,'0')  
       ||'.'||                                                 
       STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),6,2),L,'0')  
       AS DB2VER                                               
      ,CASE CURRENT TIMEZONE                                   
            WHEN -40000 THEN 'US/Eastern'                      
            WHEN -50000 THEN 'US/Central'                      
            WHEN -60000 THEN 'US/Mountain'                     
            WHEN -70000 THEN 'US/Pacific'                      
        END DB2TZ                                              
  FROM SYSIBM.SYSDUMMY1                                        
 WHERE CURRENT DATE BETWEEN                                    
          DATE(YEAR(CURRENT DATE)||'-03-08') +                 
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-03-08'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS                   
       AND                                                     
          DATE(DATE(YEAR(CURRENT DATE)||'-11-01') +            
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-11-01'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS - 1 DAY)          
UNION ALL                                                      
SELECT STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),4,2),L,'0')  
       ||'.'||                                                 
       STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),6,2),L,'0')  
       AS DB2VER                                               
      ,CASE CURRENT TIMEZONE                                   
            WHEN -50000 THEN 'US/Eastern'                      
            WHEN -60000 THEN 'US/Central'                      
            WHEN -70000 THEN 'US/Mountain'                     
            WHEN -80000 THEN 'US/Pacific'                      
        END DB2TZ                                              
  FROM SYSIBM.SYSDUMMY1                                        
 WHERE CURRENT DATE NOT BETWEEN                                
          DATE(YEAR(CURRENT DATE)||'-03-08') +                 
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-03-08'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS                   
       AND                                                     
          DATE(DATE(YEAR(CURRENT DATE)||'-11-01') +            
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-11-01'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS - 1 DAY)