Category: SQL

Write to joblog using SQL

Using INCUDE statement, we can include source to the stored procedure. In this example, we are going to insert C source code which contains the API Qp0zLprintf. Create stored procedure MYPRINTF like below : cl:addlible qsysinc; create or replace procedure mylib.myprintf(printString varchar(500)) set option bindopt = ‘bndsrvpgm(qsys/qp0zcpa)’ begin if printString is not null then include […]

Extract Numbers from a string using SQL

Using regexp_replace function, we can extract numbers from a string. Example : select regexp_replace(‘Nanda has more than 15 years experience in Ibm i’, ‘[^0123456789]’, ‘  ‘, 1, 0, ‘i’) from sysibm.sysdummy1. Result : 15 First parm : Source_String Second Parm : Pattern-expression. [^0123456789] tells the system not to match the numbers. Third Parm :  Replacement […]

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