Hi Justin,
Reasonable chance you should add this to your function's source code and
recompile it to fix your problem:
set option datfmt = *iso
I'd try that first to see if it fixes your problem.
You can debug an SQL function as follows:
- From the ACS Run SQL Scripts feature, ensure you're connected to the
system you want to debug on.
- From the ACS Run SQL Scripts feature "Run" menu pull-down, choose
option "System Debugger".
- From the System Debugger window "Debug" menu pull-down, choose "Start
Debug".
- From the Start Debug window that appears, in the "Program to debug"
section, choose Type "Service Program". Reason: SQL functions compile as
service programs.
- In the "Program to debug" section, enter the short specific name of
the SQL function to debug. There must be a *SRVPGM object present matching
this name.
- In the "Program to debug" section, enter the library name containing
that SQL function to debug. This will be the library name containing the
*SRVPGM object.
- The "Existing job to debug" job parameters on this panel should be set
to the same job that "Run SQL Scripts" is connected to (you want it to be
set to whatever job will be running the SQL function).
- Click the OK button on the "Start Debug" panel.
- The service program you're debugging should now be listed in the upper
left panel. Expand the plus sign next to it, and double click on the
function's short / specific name. The SQL source code should now show in
the upper right pane.
- Click to the left of a source code line number to set a breakpoint.
Click again to remove a breakpoint.
- Go back to the Run SQL Scripts window, and run the SQL that will call
your SQL function.
- Go back to the System Debugger and click the green Resume icon on the
tool bar (looks like a typical debugger continue icon).
- You should hit your breakpoint in the debugger, assuming your code
path is as expected.
- SQL functions get converted to C language code, and then compiled.
The Locals window pane tab will therefore contain a lot of content you're
not expecting, that is not necessarily in your SQL source, resulting from
the generated C source code.
- In particular, look for a Locals variable with a name that matches
your SQL function's long name. That will be a structure that contains your
functions input parameters.
- Your function's work variables will be in a structure named SQLP_L2.
- Because C language is used, there will be a lot more pointer variables
in use than you're likely used to in the Locals pane. You have to pay
attention to the fact that for many variable types, you have a pointer and
a separate data chunk of memory.
- Other than that, the debugger functions are fairly typical for a
debugger.
I did a lot of C language programming years ago, so that stuff doesn't
bother me, but it will likely be a major irritant for many people using the
debugger.
HTH,
Mike
date: Mon, 15 Jan 2018 23:04:29 +0000
from: Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
subject: Debug SQL function
I have a SQL function that converts a numeric(6) date into a proper date
data type. It works fine for 21st century dates, but not for 20th
century. I know there has to be some way to debug a SQL function, I just
haven't ever done it.
How do I debug a SQL function?
TIA
midrange.com
