Author: nanda1980

Generate JSON data from SQL

We can generate Json data on ibm i using JSON_OBJECT sql function. Example: If we have a Empmst table with the following record. Emp_ID is numeric, First_Name and Last_Name fields are CHAR. Emp_ID  First_Name    Last_Name 1                 Nanda              Jay   Select Json_Object […]

DB2 for i – Object lock info

This DB2 for i service returns info similar to command Wrkobjlck. The locked object The lock info The lock holder Returns one row for every lock held for every object on the partition Member locks included for files View over all objects on the system . Example : Select * From Qsys2.Object_Lock_Info where Object_Name = […]

DB2 Sql – Get scheduled job info

The Scheduled_Job_Info view returns information similar to the Work with Job Schedule Entries (Wrkjobscde) command interface. Each row reflects the details related to each scheduled job. Example: Review the job scheduled entries which are no longer in effect, either because they were explicitly held or because they were scheduled to run a single time and […]

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.