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’

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.

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  

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

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, […]