Call Procedure in SPUFI or DSNTEP2.
Use UDF to do something useful with an IBM supplied stored procedure
(by Brian Laube)
Stored Procedures are a powerful Db2 object that are being used by more and more of our applications.
Famously, we all know that traditional “tools” like SPUFI or batch DSNTEP2 cannot call stored procedures. So inside TSO, it is hard to actually use and CALL stored procedures. Technically, we could build COBOL or REXX programs to call them. But that seems like work.
- Writing application code is for developers and I am a DBA. My first tactic for any task is to try to do it via SQL alone! I really want to be able to “easily” call a procedure in a batch job…. Using DSNTEP2 and SQL.
One can create a SQL scalar user-defined-function (UDF) that can use SQLPL and CALL a procedure and process the result and then return a single variable.
- A UDF can be easily invoked in a SELECT statement. So then we can do a simple SELECT UDF_XYZ() FROM SYSIBM.SYSDUMMY1 to invoke function UDF_XYZ which calls the procedure, process the procedure result and then returns a value to the invoker of the UDF. This is very DSNTEP2 and SPUFI friendly!
Of course, if the procedure returns lots of interesting data then this solution is not great because this UDF returns one value. But if the UDF returns one (or few) key pieces of information then using a UDF can be a simple way to CALL the procedure and return the information to the SELECT!
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createfunctionsqlscalar.html
Independent of the applications in my shop, there is some meta-Db2 information that is interesting to me (the application DBA) but not easily available via special registers or global variables. There are special registers and global variables that tell you lots of interesting things about your Db2. But I want to know my Db2 database DDF server name. And it is hard to find the server name via SQL alone!
This server name information can be found via Db2 Command -DISPLAY DDF DETAIL.
There exists an IBM/DB2 supplied stored procedure that can invoke DB2 commands. The procedure is called SYSPROC.ADMIN_COMMAND_DB2
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sp_admincommanddb2.html
I decided to combine my quest to find my Db2 server name via SELECT (that could run in DSNTEP2) with my curiosity to confirm how to use UDF to call a procedure. So I made this useful (to me) UDF!
As a bonus:: I recently had an experience where a subtle security change (ACF2) added a new resource rule for the WLM environment where most of my Db2 supplied stored procedures ran. When this change occurred, it was a problem! It was not immediately noticed by anyone that the started task (as controlled by WLM) would not start and the IBM provided procedure was not working! It was annoying and caused an application outage until we traced back to the subtle root cause!
- By having a UDF that calls a procedure where the procedure actually runs in an external (to DB2) WLM environment, I can now use the UDF in SELECT via DSNTEP2/SPUFI and ensure the WLM environment controlled started tasks actually start and work! If not, my UDF will fail (likely with reason 00E7900C).
- I have now put a SELECT to my new UDF in my “Db2 validation JCL” where I have a DSNTEP2 step that uses SELECT to do a simple query to make sure DB2 is up (and DSNTEP2 works). The JOB runs after all tech currency changes (and after IPL).
- Now my validation JCL and this job STEP are essentially calling this PROCEDURE and ensuring it works which also ensures the WLM environment is working too. I won’t be burned by that particular security issue again!
SELECT CURRENT TIMESTAMP AS CUR_TS
, DBCDBD1.UDF_DDF_HOSTNAME() AS UDF_DDF_HOSTNAME
, DBCDBD1.UDF_DB2_DETAILS() AS UDF_DB2_DETAILS
FROM SYSIBM.SYSDUMMY1;
PAGE 1
***INPUT STATEMENT:
SELECT CURRENT TIMESTAMP AS CUR_TS
, DBCDBD1.UDF_DDF_HOSTNAME() AS UDF_DDF_HOSTNAME
, DBCDBD1.UDF_DB2_DETAILS() AS UDF_DB2_DETAILS
FROM SYSIBM.SYSDUMMY1;
+-----------------------------
| CUR_TS |
+-----------------------------
1_| 2020-10-06-16.49.37.252767 |
+-----------------------------
PAGE 2
--------------------------------------------------------------------------------------------
| UDF_DDF_HOSTNAME
--------------------------------------------------------------------------------------------
1_| mainframex.manulife.com(10.221.31.21) LOCATION=HCDDB2GW DDF-STATUS=STARTD
--------------------------------------------------------------------------------------------
PAGE 3
--------------------------------------------------------------------------------------------
| UDF_DB2_DETAILS
--------------------------------------------------------------------------------------------
1_| SYSJ-DDBC (STATUS=ACTIVE) DB2_LVL=121506 DB2_CAT_LVL=V12R1M503 DB2_FUNCTION_LVL=V12R1M504
--------------------------------------------------------------------------------------------
SUCCESSFUL RETRIEVAL OF 1 ROW(S)
Creating UDF that use SQLPL provides powerful functionality for your SQL programming. First, SQLPL is a procedural language. Second, using a UDF allows you to easily stick it inside SQL SELECT. As I stated earlier, SQLPL can CALL a procedure and process the returned result set as returned by Db2 (the whole point of this mini article). Dealing with returned result sets is a bit tricky in SQLPL… but you can see how it is done in the two UDF examples below.
- Granted, the provided UDF below with SQLPL may not be well documented… but (famous last words) the code looks obvious to me (today)
- Also, the provided UDF and SQL does not do lots of error checking or handling… but it is a simple UDF used by me (mostly). I can deal with minimal error handling. If this was a real application UDF then complete error handling would be included (high hopes indeed).
- My two UDF examples each call a specific procedure with specific inputs. As a result, they are simple to read UDF. Is it possible to make a more a generic UDF and pass in the procedure name and input parameter values and generic instructions on how to process the result? I thought about this… but the answer is no… that is too hard and not worth the effort.
Db2 provides many stored procedures that do something useful. These are documented in the knowledge center
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_suppliedstoredprocedures.html
Another set of Db2 procedures are used by the IBM Data Server driver for JDBC and SQLJ
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/java/src/tpc/imjccjccenablespsandtables.html
Many/most/all of these Db2 supplied stored procedures are external procedures that run inside a started task that is controlled by WLM.
Do you want to know more about my “fun” security problem mentioned earlier?
Apparently, many tools call the Db2 supplied procedures behind the scenes.
We have a dot-net application using application built Db2 native SQL stored procedure. Excellent. A fun and modern application using Db2 stored procedures. These native SQL (SQLPL) run inside DBM1, not inside a WLM environment.
One day there was a subtle security change to the resource rules. This one dot-net application began to fail calling just one specific application procedure. Apparently, the application procedure had an input parameter of type timestamp. But normally, dot-net used SYSPROCEDURECOLS and knew the input parm was type date. The dot-net application was using type timestamp. When SYSPROCEDURECOLS was working then the dot-net somehow changed the input parm from timestamp to date and the application procedure worked. When the SYSPROCEDURECOLS stopped working then dot-net just passed in the timestamp value and then the application procedure failed! Most subtle and confusing. I wish dot-net told us it was not able to call SYSPROCEDURECOLS
You don’t always know when some of these IBM provided procedures are being called by your client tools. For example, when calling your own application procedure from the DB2 Command Line Prompt on your workstation, behind the scenes the CLP is calling SYSIBM.SQLPROCEDURECOLS first to learn about the parameters of your procedures! And if this call to SQLPROCEDURECOLS fails, then CLP won’t call your application procedure.
- Interesting, calling the same application procedure via Data Studio apparently does not use that same IBM provided procedure behind the scenes (at least in my experience). I guess Data Studio knows more about my application procedures compared to CLP. I could call this application procedure in Data Studio (not via CLP).
Anyways, it was a real “fun” problem!
--#SET TERMINATOR #
SET CURRENT SQLID='DBCDBD1'#
DROP FUNCTION DBCDBD1.UDF_DDF_HOSTNAME# COMMIT#
CREATE FUNCTION UDF_DDF_HOSTNAME()
RETURNS VARCHAR(128)
VERSION R20200925A
DETERMINISTIC NO
EXTERNAL ACTION
MODIFIES SQL DATA
ASUTIME LIMIT 80000
DISABLE DEBUG MODE
/* THIS IS A DBA CREATED UDF DESIGNED
TO ISSUE DB2 COMMAND >DISPLAY DDF DETAIL<
VIA IBM/DB2 SUPPLIED STORED PROCEDURE.
THEN THIS UDF PARSES THE PROCEDURE OUTPUT AND RETURNS
THE MOST INTERESTING INFO TO CALLER
> WHAT IS INTERESTING IS SUBJECTIVE.
> I WANT TO KNOW THE HOSTNAME.
ORIGINAL : SEPT 2020 - BRIAN LAUBE
*/
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE AT_END INTEGER DEFAULT 0;
DECLARE OUT_FINAL VARCHAR(128) DEFAULT '*EMPTY*';
DECLARE OUT_HOST VARCHAR(128) DEFAULT '*UNKNOWN HOST*';
DECLARE OUT_IPADDR VARCHAR(128) DEFAULT '*UNKNOWN IPADDR*';
DECLARE OUT_DDF_STATUS CHAR(8) DEFAULT '*UNKOWN DDF STS*';
DECLARE OUT_LOCATION CHAR(16) DEFAULT '*UNKOWN LOCATION*';
DECLARE ACD_IN_DB2_CMD VARCHAR(40) DEFAULT '-DISPLAY DDF DETAIL';
DECLARE ACD_IN_CMD_LEN INTEGER DEFAULT 40;
DECLARE ACD_IN_PROC_TYPE VARCHAR(3) DEFAULT ' '; -- YES, USE BLANK
DECLARE ACD_IN_DB2 VARCHAR(8) DEFAULT ' ';
DECLARE ACD_OUT_CMD_EXEC INTEGER;
DECLARE ACD_OUT_IFI_RC INTEGER;
DECLARE ACD_OUT_IFI_REASON INTEGER;
DECLARE ACD_OUT_EXCESS_BYTES INTEGER;
DECLARE ACD_OUT_GROUP_IFI_RC INTEGER;
DECLARE ACD_OUT_GROUP_EXCESS_BYTES INTEGER;
DECLARE ACD_OUT_RC INTEGER;
DECLARE ACD_OUT_MSG VARCHAR(1331);
DECLARE RSLV1 RESULT_SET_LOCATOR VARYING;
DECLARE C1_ROWNUM INTEGER;
DECLARE C1_TEXT CHAR(81);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 99;
CALL SYSPROC.ADMIN_COMMAND_DB2
(ACD_IN_DB2_CMD
,ACD_IN_CMD_LEN
,ACD_IN_PROC_TYPE
,ACD_IN_DB2
,ACD_OUT_CMD_EXEC
,ACD_OUT_IFI_RC
,ACD_OUT_IFI_REASON
,ACD_OUT_EXCESS_BYTES
,ACD_OUT_GROUP_IFI_RC
,ACD_OUT_GROUP_EXCESS_BYTES
,ACD_OUT_RC
,ACD_OUT_MSG )
;
IF (ACD_OUT_RC = 0) THEN
SET OUT_HOST='*SP_ACD_SUCCESSUL*';
ASSOCIATE RESULT SET LOCATORS (RSLV1)
WITH PROCEDURE SYSPROC.ADMIN_COMMAND_DB2;
ALLOCATE RSCUR1 CURSOR FOR RSLV1;
WHILE (AT_END = 0) DO
FETCH RSCUR1 INTO C1_ROWNUM, C1_TEXT;
IF (LOCATE('DSNL081I',C1_TEXT)>0) THEN
SET OUT_DDF_STATUS=SUBSTR(C1_TEXT,LOCATE('=',C1_TEXT)+1);
END IF;
IF (LOCATE('DSNL083I',C1_TEXT)>0) THEN
SET OUT_LOCATION=SUBSTR(C1_TEXT,LOCATE('DSNL083I',C1_TEXT)+9,16);
END IF;
IF (LOCATE('DSNL085I',C1_TEXT)>0) THEN
SET OUT_IPADDR=SUBSTR(C1_TEXT,LOCATE('=::',C1_TEXT)+3);
END IF;
IF (LOCATE('DSNL086I',C1_TEXT)>0) THEN
SET OUT_HOST=SUBSTR(C1_TEXT,LOCATE('=',C1_TEXT)+1);
END IF;
END WHILE;
END IF;
SET OUT_FINAL = STRIP(OUT_HOST)
||'('||STRIP(OUT_IPADDR)
||') LOCATION='||STRIP(OUT_LOCATION)
||' DDF-STATUS='||STRIP(OUT_DDF_STATUS)
;
RETURN OUT_FINAL;
END P1#
GRANT EXECUTE ON FUNCTION DBCDBD1.UDF_DDF_HOSTNAME TO PUBLIC#
SELECT DBCDBD1.UDF_DDF_HOSTNAME() FROM SYSIBM.SYSDUMMY1#
--#SET TERMINATOR #
SET CURRENT SQLID='DBCDBD1'#
DROP FUNCTION DBCDBD1.UDF_DB2_DETAILS# COMMIT#
CREATE FUNCTION UDF_DB2_DETAILS()
RETURNS VARCHAR(128)
VERSION R20200925A
DETERMINISTIC NO
EXTERNAL ACTION
MODIFIES SQL DATA
ASUTIME LIMIT 80000
DISABLE DEBUG MODE
/* THIS IS A DBA CREATED UDF DESIGNED
TO ISSUE DB2 COMMAND >DISPLAY GROUP<
VIA IBM/DB2 SUPPLIED STORED PROCEDURE.
THEN THIS UDF PARSES THE PROCEDURE OUTPUT AND RETURNS
THE MOST INTERESTING INFO TO CALLER
> WHAT IS INTERESTING IS SUBJECTIVE.
> I WANT TO KNOW THE CATALOG LEVEL, FUNCTION LEVEL
DB2_LVL AND DB2 SSID, AND STATUS (AND LPAR)
ORIGINAL : SEPT 2020 - BRIAN LAUBE
*/
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE AT_END INTEGER DEFAULT 0;
DECLARE OUT_FINAL VARCHAR(128) DEFAULT '*EMPTY*';
DECLARE OUT_CAT_LVL CHAR(9) DEFAULT '*CAT LVL*';
DECLARE OUT_FUNCT_LVL CHAR(9) DEFAULT '*FUNC LVL*';
DECLARE OUT_SSID CHAR(8) DEFAULT '*SSID*';
DECLARE OUT_STATUS CHAR(8) DEFAULT '*DB2 STATUS*';
DECLARE OUT_DB2_LVL CHAR(8) DEFAULT '*DB2 LVL*';
DECLARE OUT_LPAR CHAR(8) DEFAULT '*LPAR*';
DECLARE ACD_IN_DB2_CMD VARCHAR(40) DEFAULT '-DISPLAY GROUP';
DECLARE ACD_IN_CMD_LEN INTEGER DEFAULT 40;
DECLARE ACD_IN_PROC_TYPE VARCHAR(3) DEFAULT 'GRP'; -- YES, USE GRP
DECLARE ACD_IN_DB2 VARCHAR(8) DEFAULT ' ';
DECLARE ACD_OUT_CMD_EXEC INTEGER;
DECLARE ACD_OUT_IFI_RC INTEGER;
DECLARE ACD_OUT_IFI_REASON INTEGER;
DECLARE ACD_OUT_EXCESS_BYTES INTEGER;
DECLARE ACD_OUT_GROUP_IFI_RC INTEGER;
DECLARE ACD_OUT_GROUP_EXCESS_BYTES INTEGER;
DECLARE ACD_OUT_RC INTEGER;
DECLARE ACD_OUT_MSG VARCHAR(1331);
DECLARE RSLV1 RESULT_SET_LOCATOR VARYING;
DECLARE C1_ROWNUM INTEGER;
DECLARE C1_TEXT CHAR(81);
DECLARE RSLV2 RESULT_SET_LOCATOR VARYING;
DECLARE C2_ROWNUM INTEGER;
DECLARE C2_DB2_MEMBER CHAR(81);
DECLARE C2_ID INTEGER;
DECLARE C2_SUBSYS CHAR(4);
DECLARE C2_CMDPREF CHAR(8);
DECLARE C2_STATUS CHAR(8);
DECLARE C2_DB2_LVL CHAR(6);
DECLARE C2_SYSTEM_NAME CHAR(8);
DECLARE C2_IRLM_SUBSYS CHAR(4);
DECLARE C2_IRLM_PROC CHAR(8);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 99;
CALL SYSPROC.ADMIN_COMMAND_DB2
(ACD_IN_DB2_CMD
,ACD_IN_CMD_LEN
,ACD_IN_PROC_TYPE
,ACD_IN_DB2
,ACD_OUT_CMD_EXEC
,ACD_OUT_IFI_RC
,ACD_OUT_IFI_REASON
,ACD_OUT_EXCESS_BYTES
,ACD_OUT_GROUP_IFI_RC
,ACD_OUT_GROUP_EXCESS_BYTES
,ACD_OUT_RC
,ACD_OUT_MSG )
;
IF (ACD_OUT_RC = 0) THEN
SET OUT_FINAL='*SP_ACD_SUCCESSUL*';
ASSOCIATE RESULT SET LOCATORS (RSLV1,RSLV2)
WITH PROCEDURE SYSPROC.ADMIN_COMMAND_DB2;
ALLOCATE RSCUR1 CURSOR FOR RSLV1;
WHILE (AT_END = 0) DO
FETCH RSCUR1 INTO C1_ROWNUM, C1_TEXT;
IF (LOCATE('CATALOG LEVEL(',C1_TEXT)>0) THEN
SET OUT_CAT_LVL=SUBSTR(C1_TEXT,LOCATE('LEVEL(',C1_TEXT)+6);
END IF;
IF (LOCATE('FUNCTION LEVEL',C1_TEXT)>0) THEN
SET OUT_FUNCT_LVL=SUBSTR(C1_TEXT,LOCATE('LEVEL(',C1_TEXT)+6);
END IF;
END WHILE;
ALLOCATE RSCUR2 CURSOR FOR RSLV2;
FETCH RSCUR2 INTO C2_ROWNUM, C2_DB2_MEMBER, C2_ID, C2_SUBSYS
,C2_CMDPREF, C2_STATUS, C2_DB2_LVL, C2_SYSTEM_NAME
,C2_IRLM_SUBSYS, C2_IRLM_PROC
;
SET OUT_LPAR = STRIP(C2_SYSTEM_NAME);
SET OUT_SSID = STRIP(C2_SUBSYS);
SET OUT_STATUS = STRIP(C2_STATUS);
SET OUT_DB2_LVL = STRIP(C2_DB2_LVL);
END IF;
SET OUT_FINAL = STRIP(OUT_LPAR)
||'-'||STRIP(OUT_SSID)
||' (STATUS='||STRIP(OUT_STATUS)||')'
||' DB2_LVL='||STRIP(OUT_DB2_LVL)
||' DB2_CAT_LVL='||STRIP(OUT_CAT_LVL)
||' DB2_FUNCTION_LVL='||STRIP(OUT_FUNCT_LVL)
;
RETURN OUT_FINAL;
END P1#
GRANT EXECUTE ON FUNCTION DBCDBD1.UDF_DB2_DETAILS TO PUBLIC#
SELECT DBCDBD1.UDF_DB2_DETAILS() FROM SYSIBM.SYSDUMMY1#
In my 2 UDF code examples you may notice that I am calling ADMIN_COMMAND_DB2 to execute the DB2 commands of -DISPLAY DDF DETAIL in one and -DISPLAY GROUP in the other. Why did I use the ADMIN_COMMAND_DB2 process type input parameter of ‘blank’ in one ‘GRP’ in the other? This input parameter determines if the procedure returns a second result set (or not) where the procedure attempts to be helpful and parse the actual DB2 command output and put it into multiple variables and into a wide second result set. This is fine. I used ‘blank’ for -DISPLAY DDF DETAIL because I did not want the wide second result set. Too many variables for me. This is a minor pain to process programmatically. I decided to just use ‘blank’ in this case and process the first result set to find my interesting data. The -DISPLAY GROUP with ‘GRP’ had relatively few variables in the second result set and it was relatively easy for me to parse the second result to find my interesting data.