Category: SQL

DB2 Regexp_Substr

Regexp_Substr is a scalar function which returns one occurrence of a substring that matches the regular expression pattern. Syntax : Regexp_Substr(source_string, pattern_expression, start, occurence, flags, group)   Example : Return the string which matches any character preceding a ‘a’. select regexp_substr(‘Today is mother’s day’, ‘.a’,1,1) from sysibm.sysdummy1 Result : ‘da’

Db2 – Get the largest files on the system

Using the Syspartitionstat view, we can get the largest files on the system.   Sql to get the first 100 largest files on the system. Select table_schema, table_name, table_partition, number_rows, number_deleted_rows, data_size, variable_length_size, ‘ number_distinct_indexes From qsys2.syspartitionstat Order By data_size desc Fetch 100 rows only.

Db2 Sql – Left Function

Left function returns a substring consisting of the leftmost ‘n’ characters from a string. syntax : left  (source string, position) example : Values left(‘EASTER’,4); Result : EAST  

Db2 Sql to get the first and last day of the month

LAST_DAY function returns the last day of a month. Example : To get the last day of the current month, Values last_day(current date) Result : 04/30/2019 To get the first day of the current month, Values last_day(current date) + 1 day – 1 month Result : 04/01/2019

Db2 Upsert using Merge into

Using Merge into statement, you can either insert or update a row. Merge into Syntax Merge into upsert_table as Temp using (values (1, 2, 3), (4, 5, 6), (7, 8, 9) —- more rows ) As Merge (C1, C2, C3) On Temp.Key_to_Match = Merge.Key_to_Match When Matched Then Update set temp.c1 = merge.c1, temp.c2 = merge.c2, […]