Skip to content

Instantly share code, notes, and snippets.

@forstie
Created June 20, 2019 07:32
Show Gist options
  • Save forstie/db96774f4f236b7284196e726b4ed1f2 to your computer and use it in GitHub Desktop.
Save forstie/db96774f4f236b7284196e726b4ed1f2 to your computer and use it in GitHub Desktop.
Microsecond DLYJOB via SQL
-- Purpose: Delay job for fractions of a second
-- Author : Scott Forstie
-- Contact: forstie@us.ibm.com
-- 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);
@lae0901
Copy link

lae0901 commented Jun 21, 2019

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 ?

@lae0901
Copy link

lae0901 commented Jun 21, 2019

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.

@forstie
Copy link
Author

forstie commented Jun 21, 2019

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.

@forstie
Copy link
Author

forstie commented Jun 21, 2019

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

@lae0901
Copy link

lae0901 commented Jun 24, 2019

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?

@kskuhlman
Copy link

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)

@lae0901
Copy link

lae0901 commented Jun 25, 2019

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

@forstie
Copy link
Author

forstie commented Jun 25, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment