Microsecond DLYJOB via SQL

Microsecond DLYJOB

-- Purpose: Delay job for fractions of a second
-- Author : Scott Forstie
-- Contact: [email protected]
-- Date   : June 20, 2019

cl: addlible QSYSINC;
cl: crtsrcpf qtemp/qcsrc;
cl: addpfm file(qtemp/qcsrc) mbr(usleep);
--
-- The usleep() function suspends a thread for the number of microseconds specified by the of useconds parameter.
--                                                           ============
-- 1 sec == 1,000 milisec == 1,000,000 microsec == 1,000,000,000 nanosec
--
insert into qtemp.qcsrc values
 (1,010101,'{'),                                                  
 (2,010101,'#include "unistd.h"'),      
 (3,010101,'while(USLEEP.SLEEPUS>1000000)'),
 (4,010101,'  { usleep(1000000); USLEEP.SLEEPUS-=1000000; }'),  
 (5,010101,'usleep(USLEEP.SLEEPUS);'),                                     
 (6,010101,'}');

CREATE OR REPLACE PROCEDURE systools.usleep(sleepus int)
program type sub
-- Use this SET OPTION if you see a compile failure [SQL7032]
-- SET OPTION dbgview = *source, output=*PRINT
   BEGIN
      INCLUDE qtemp / qcsrc(usleep);
   END;

-- Sleep for 1 microsecond
CALL systools.usleep(1);

that is pretty neat! Have never used the INCLUDE statement in SQL PROCEDURE before.

Where is the code that creates the procedure run from? You are running CL, then SQL ?

Hi Scott, any tips on how to debug SQL procedure code? For one thing, I am not able to STRDBG in an RPG program and then step into an SQL procedure. The whole process of debugging SQL procedures has been difficult for me. Maybe I am using the wrong tooling? I work from the IBM i command line - QCMD.

Hi,
Please download and install the latest ACS.
ibm.biz/IBMi_ACS

Then, launch Run SQL Scripts and Run --> System Debugger. Use the graphical debugger.
Create your SQL code as debuggable with SET OPTION DBGVIEW=*SOURCE.

Please look here for details regarding the INCLUDE SQL and INCLUDE C support: http://ibm.biz/DB2fori_INCLUDE
Regards, Scott

Hi,
Please download and install the latest ACS.
ibm.biz/IBMi_ACS

Then, launch Run SQL Scripts and Run --> System Debugger. Use the graphical debugger.
Create your SQL code as debuggable with SET OPTION DBGVIEW=*SOURCE.

excellent. thank you.

How does the SQL debugger work in ACS? Is it open source? Would love to try to make improvements, customize it. Is it just the system debugger APIs?

Gist comments aren't the right forum for these questions. The information you want is on developerwoks. And a big community gathers on the midrange listserv. Or you can use Reddit, etc.

As for ACS, it's closed source, but IBM takes feedback through RFEa. Thanks. -Ken (just a watcher)

Gist comments aren't the right forum for these questions. The information you want is on developerwoks. And a big community gathers on the midrange listserv. Or you can use Reddit, etc.

all I see that is IBM i related on developerworks is RPG and RDI. is there more? Looking to know more on how to debug SQL procedures running on IBM i. Posting here in case Scott can do some Gists on that topic.

My use case is I run javascript code in the browser. Use the fetch or $.ajax api to call into PHP code on the IBM i. Which then runs db2_execute to run an SQL procedure. PHP gets result set back from SQL, converts to JSON and returns response to browser. Multiple steps. Lot of points of failure.

What I currently do to get some debug info on a failed SQL procedure is use db2_execute in PHP to run a 2nd SQL procedure after the actual procedure fails. This 2nd SQL procedure runs DSPJOBLOG *PRINT, captures the spooled joblog, and returns it as a result set. Then the PHP converts that to JSON and returns it to the browser. The browser then runs code that downloads the joblog to a text file that can be viewed in notepad. Pretty helpful, actually. Will post it to a github repo if anyone interested.

Would be neat to run the SQL procedure in debug mode, get it to break at a statement, then ask for values of variables and return those values as JSON to the browser.

-Steve

My posting on showing lprintfs to the joblog provides a solution of sorts.
Scott