Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link
Owner Author

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

This comment has been minimized.

Copy link
Owner Author

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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

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.

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

@lae0901

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link
Owner Author

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
You can’t perform that action at this time.