External user-defined functions
In this appendix, we provide the details about two external user-defined functions (UDF) that are used in our scenarios, which are described in Chapter 4, “DB2 infrastructure setup” on page 99.
The UDF routines are:
G.1 UDF GRACFGRP
The external UDF GRACFGRP is an assembler program that extracts the RACF groups that the current UDF caller is connected to from the RACF ACEE control block, which was created by DB2 because of the SECURITY USER UDF attribute. GRACFGRP then returns an XML document that contains the RACF group names as a VARCHAR scalar value. We used this UDF in the scenario that is described in 4.1.9, “WebSphere Application Server and DB2 security” on page 126. Listings of the DDL and the ASM are provided in Example G-1 and Example G-2.
Example G-1 DDL for UDF GRACFGRP
CREATE FUNCTION JOSEF.GRACFGRP
RETURNS VARCHAR(32000)
EXTERNAL NAME 'GRACFGRP'
LANGUAGE ASSEMBLE
NOT DETERMINISTIC
PARAMETER CCSID EBCDIC
PARAMETER STYLE DB2SQL
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION
NO PACKAGE PATH
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
DBINFO
NO COLLID
WLM ENVIRONMENT WLMENV1
ASUTIME NO LIMIT
STAY RESIDENT YES
PROGRAM TYPE SUB
SECURITY USER
STOP AFTER SYSTEM DEFAULT FAILURES
INHERIT SPECIAL REGISTERS
RUN OPTIONS 'NOTEST(ALL,INSPIN,,*)'
Example G-2 Assembler listing of GRAFGRP
TITLE 'GRACFGRP (Get RACF groups a user is connected to )' 00010000
***********************************************************************
* Author.....: [email protected]
* Date.......: 5th May 2012
* Function...:
* Implements an SQL UDF external scalar function to return the RACF
* groups the current RACF user is connected to. GRACFGRP obtains the
* list of RACF groups through the UDF's ACEE control block. For the
* ACEE to be available to the GRACFGRP program the UDF <MUST> be
* defined with "SECURITY USER".
*
* The scalar value is returned as VARCHAR to contain an XML document
* like the sample shown below:
* <GROUPS>
* <USER>JOSEF </USER>
* <GROUP>RACFGRP1</GROUP>
* <GROUP>RACFGRP2</GROUP>
* <GROUP>RACFGRP3</GROUP>
* </GROUPS>
*
* SQL DDL....:
* CREATE FUNCTION GRACFGRP ()
* RETURNS VARCHAR(32000)
* CCSID EBCDIC FOR SBCS DATA
* SPECIFIC GRACFGRP
* EXTERNAL NAME GRACFGRP
* LANGUAGE ASSEMBLE
* PARAMETER STYLE DB2SQL
* SECURITY USER
* FENCED
* CALLED ON NULL INPUT
* NO SQL
* ALLOW PARALLEL
* DBINFO
* WLM ENVIRONMENT WLMENV1
* ASUTIME NO LIMIT;
*
* Interface..:
* select gracfgrp() from sysibm.sysdummy1;
* --> returns the XML document shown above
*
* pureXML query:
* ==============
* SELECT T.* FROM XMLTABLE
* ('$d/GROUPS/GROUP'
* PASSING XMLPARSE (DOCUMENT gracfgrp()) AS "d"
* COLUMNS
* "RACF User" VARCHAR(08) PATH '../USER/text()',
* "RACF Group" VARCHAR(08) PATH './text()'
* ) AS T
* ;
*
* pureXML query result:
* =====================
* RACF User RACF Group
* --------- ----------
* JOSEF RACFGRP1
* JOSEF RACFGRP2
* JOSEF RACFGRP3
*
***********************************************************************
YREGS 01740000
GRACFGRP CEEENTRY AUTO=WORKSIZE,BASE=R11,MAIN=NO,PLIST=OS 01750000
USING WORKAREA,R13 01760000
L R9,0(R1) get pointer TO return parm
USING RACFGRP,R9 01760000
L R7,4(R1) get pointer to indicator variable
MVC 0(2,R7),=AL2(0) indicate return value
MVC XML01#,XML01
MVC XML11#(XML11L),XML11
MVC RACFLEN,XML01L
A0010 DS 0H 02940000
L R5,CVTPTR ADDRESS MVS CVT
L R7,CVTRAC-CVT(,R5) RACF CVT ADDRESS
LTR R7,R7 IF RACF CVT ADDRESS ZERO,
BZ A0080 RACF IS NOT EVEN INSTALLED
USING RCVT,R7 SET BASE FOR RACF CVT
A0011 DS 0H 02940000
L R8,PSAAOLD-PSA GET CURRENT ASCB ADDRESS AND
USING ASCB,R8 SET MAPPING ADDRESSABILITY
L R6,PSATOLD-PSA CURRENT TCB ADDRESS 04990000
L R6,TCBSENV-TCB(,R6) GET TASK LEVEL ACEE 05000000
LTR R6,R6 TASK LEVEL ACEE AVAILABLE 05010000
USING ACEE,R6 ESTABLISH BASE FOR ACEE
BZ A0015 NO,GO TRY ADDRESS SPACE 05020000
CLC ACEEACEE,=C'ACEE' DOSE IT LOOK LIKE AN ACEE? 05030000
BE A0017 YES,THEN USE IT 05040000
A0015 DS 0H 05050000
L R6,ASCBASXB GET ADDRESS SPACE EXTENSION BLOCK 05060000
L R6,ASXBSENV-ASXB(,R6) GET ACEE ADDRESS 05070000
LTR R6,R6 DOES AN ACEE EXIST? IF NOT, 05080000
BZ A0080 SKIP AROUND CONNECTED GROUP NAME 05090000
CLC ACEEACEE,=C'ACEE' DOES IT LOOK LIKE AN ACEE? 05100000
BNE A0080 NO, THEN CAN'T DO GROUPS 05110000
DROP R8 DROP ASCB BASE REG 05120000
A0017 DS 0H CHECK LIST OF GROUPS OPTION 00010001
MVC XML11U#,ACEEUSRI
TM RCVTOPTX,RCVTLGRP IS LIST OF GROUPS CHECKING ACTIVE 05150000
BZ A0080 NO, THEN CAN'T DO GROUPS 05160000
DROP R7 DROP RCVT BASE REG 05170000
A0020 DS 0H CHECK LIST OF GROUPS OPTION 00010001
* INITIALIZE 05270000
L R5,ACEEFCGP CONNECT GROUP BLOCK 05280000
LTR R5,R5 ENSURE THE BLOCK IS THERE 05290000
BZ A0080 THE COUNT IS ZERO, SKIP IT 05300000
USING CGRP,R5 SET BASE FOR CONNECT GROUP 05310000
CLC CGRPID,=C'CGRP' DOES IT LOOK LIKE A CGRP BLOCK? 05320000
BNE A0080 NO, GROUP NAMES NOT AVAILABLE 05330000
SLR R3,R3 CLEAR THE COUNTER REGISTER 05340000
ICM R3,B'0011',CGRPNUM GET THE NUMBER OF CONNECT GROUPS 05350000
ST R3,SECCOUNT SAVE COUNT OF GROUPS 05360000
BZ A0080 BR IF NO GROUP NAMES AVAILABLE 05370000
LA R2,CGRPENT POINT TO CONNECT GROUP ENTRIES 05380000
USING CGRPENTD,R2 SET BASE FOR CONNECT GROUP ENTRIES 05390000
LA R4,GRPS ADDRESS OF SECONDARY IDS 05410000
USING SGRP,R4 SET BASE FOR SECONDARY GROUPS 05420000
DROP R5 DROP CGRP BASE REG @TU25003 05460000
LH R7,RACFLEN
* COPY GROUP NAMES 05490000
A0026 DS 0H COUNTER SET FOR MOVING 05500000
TM CGRPNAME,X'BF' SEE IF THE GROUP IS VALID 05510000
BNM A0027 BR IF NULL, BLANK, OR FF 05520000
MVC SGRPXML1,XML12 05530000
MVC SGRPNAME,CGRPNAME MOVE THE GROUP NAME 05530000
MVC SGRPXML2,XML122 05530000
LA R4,SGRPNEXT POINT TO NEXT SECONDARY AUTHID 05540000
A0027 DS 0H BYPASS UPDATING SECONDARY LIST 05570000
LA R2,L'CGRPENT(,R2) POINT TO NEXT CONNECT GROUP 05590000
AH R7,=AL2(SGRPLEN)
BCT R3,A0026 BR UNTIL ALL GROUP NAMES EXAMINED 05600000
B A0060 MOVING IS COMPLETED 05610000
DROP R2 DROP CGRPENTD BASE REG 05620000
A0060 DS 0H Moving groups is complete 02940000
AH R7,=AL2(L'XML02)
STH R7,RACFLEN
MVC 0(L'XML02,R4),XML02
B A0099
A0080 DS 0H Can't do groups 02940000
*---------------------------------------------------------------------* 02910000
* TERMINATION * 02920000
*---------------------------------------------------------------------* 02930000
A0099 DS 0H Terminate 02940000
MVC 0(2,R1),=H'0' RC=0 03020000
CEETERM RC=0 03030000
SECLEN DC Y(SGRPNEXT-SGRP) LENGTH OF A SECONDARY AUTHID ENTRY
PPA CEEPPA
LTORG 19981000
XML01 DC C'<GROUPS>'
XML02 DC C'</GROUPS>'
XML11 DC C'<USER>'
XMLUSER DC CL8' '
DC C'</USER>'
XML11L EQU *-XML11
XML01L DC AL2(*-XML01-L'XML02)
XML12 DC C'<GROUP>'
DC CL8' '
XML122 DC C'</GROUP>'
XML12L EQU *-XML12
*---------------------------------------------------------------------* 18200000
* VARIABLES * 18210000
*---------------------------------------------------------------------* 18220000
WORKAREA DSECT 18290000
ORG *+CEEDSASZ Space for dynamic save area 18300000
SAVEREGS DS 16F Copy of caller's registers
* 18310000
SECCOUNT DS F COUNT OF SECONDARY IDS
DS 0D On doubleword boundary 19800000
WORKSIZE EQU *-WORKAREA 19810000
* 19820000
*---------------------------------------------------------------------* 19830000
* DSECTs * 19840000
*---------------------------------------------------------------------* 19850000
RACFGRP DSECT
RACFLEN DS H
RACFGRPS DS CL1024 LIST OF RACF GROUPS
ORG RACFGRPS
XML01# DC C'<GROUPS>'
XML11# DC C'<USER>'
XML11U# DC CL8' '
DC C'</USER>'
GRPS DS CL(*-RACFGRPS+L'RACFGRPS)
*
SGRP DSECT
SGRPXML1 DS CL(L'XML12)
SGRPNAME DS CL8 MOVE SECONDARY GROUPS HERE
SGRPXML2 DS CL(L'XML122)
SGRPLEN EQU *-SGRP
SGRPNEXT EQU * NEXT SECONDARY NAME STARTS HERE
CEEDSA 19950000
CEECAA 19970000
CVT DSECT=YES 19971000
ICHPRCVT 19972000
IHAACEE 19973000
ICHPCGRP 19974000
IHAPSA 19975000
IKJTCB 19976000
IKJPSCB 19977000
IKJUPT 19978000
IEFAJCTB DSECT 19979000
IEFAJCTB 19979100
IEZJSCB 19979200
IHAASCB 19979300
IHAASXB 19979400
END GRACFGRP 19990000
G.2 UDF BIGINT
The external UDF BIGINT is a COBOL program that can be used to convert a CHAR string to BIGINT. We used it in the scenario that is described in 4.3.23, “SYSPROC.ADMIN_DS_LIST stored procedure” on page 197. Listings of the DDL and the COBOL program are provided in Example G-3 and Example G-4 on page 569.
Example G-3 DDL for UDF BIGINT
-- DROP FUNCTION BIGINT ;
CREATE FUNCTION BIGINT
(NAME VARCHAR(00008))
RETURNS BIGINT
EXTERNAL NAME 'UDFDOUBL'
LANGUAGE COBOL
DETERMINISTIC
PARAMETER STYLE DB2SQL
FENCED
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
DBINFO
NO COLLID
WLM ENVIRONMENT WLMENV3
ASUTIME NO LIMIT
STAY RESIDENT YES
PROGRAM TYPE SUB
SECURITY DB2
STOP AFTER SYSTEM DEFAULT FAILURES
INHERIT SPECIAL REGISTERS
;
Example G-4 COBOL listing for UDF BIGINT
000100 CBL APOST,MAP,XREF,RENT,TRUNC(BIN),TEST 00010000
000200 Identification Division. 00020000
000300 Program-ID. 'UDFDOUBL'. 00030001
000400***************************************************** 00040000
000500* UDF interface to convert a CHAR string to BIGINT * 00050001
000600* Interface: .* 00060000
000700* select BIGINT('<VARCHAR>' ) from .* 00070001
000800* sysibm.sysdummy1; .* 00080000
000805* .* 00610000
0008§0* [email protected] .* 00610000
001100* .* 00110000
001200* Create Function DDL: .* 00120000
001300* ------------------- .* 00130000
001400* CREATE FUNCTION BIGINT .* 00140001
001500* (NAME VARCHAR(00008)) .* 00150001
001600* RETURNS DOUBLE .* 00160001
001700* EXTERNAL NAME 'UDFDOUBL' .* 00170001
001800* LANGUAGE COBOL .* 00180000
001900* DETERMINISTIC .* 00190000
002000* PARAMETER STYLE DB2SQL .* 00200000
002100* FENCED .* 00210000
002200* RETURNS NULL ON NULL INPUT .* 00220000
002300* NO EXTERNAL ACTION .* 00230000
002400* NO SCRATCHPAD .* 00240000
002500* NO FINAL CALL .* 00250000
002600* ALLOW PARALLEL .* 00260000
002700* DBINFO .* 00270000
002800* NO COLLID .* 00280000
002900* WLM ENVIRONMENT WLMENV3 .* 00290000
003000* ASUTIME NO LIMIT .* 00300000
003100* STAY RESIDENT YES .* 00310000
003200* PROGRAM TYPE SUB .* 00320000
003300* SECURITY DB2 .* 00330000
003400* STOP AFTER SYSTEM DEFAULT FAILURES .* 00340000
003500* INHERIT SPECIAL REGISTERS .* 00350000
003600* ; .* 00360000
006200***************************************************** 00620000
006300 Data Division. 00630000
006400 Working-Storage Section. 00640000
006500* EXEC SQL INCLUDE SQLCA END-EXEC. 00650000
013600*==============================================================* 01360000
013700 LINKAGE SECTION. 01370000
013800 01 UDFPARM1. 01380004
015500 49 UDFPARM1-LEN PIC 9(4) USAGE BINARY. 01390004
015600 49 UDFPARM1-TEXT PIC X(8). 01400004
014400 01 UDFPARM2 PIC S9(18) USAGE COMP. 01440008
01 UDFPARM2-X REDEFINES UDFPARM2 PIC X(8). 01441003
014500 01 UDF-RIND1 PIC S9(4) USAGE COMP. 01450000
014600 88 UDF-RIND1-OK VALUE ZERO. 01460000
014700 88 UDF-RIND1-NODATA VALUE -1. 01470000
014800 01 UDF-RIND2 PIC S9(4) USAGE COMP. 01480000
014900 88 UDF-RIND2-OK VALUE ZERO. 01490000
015000 88 UDF-RIND2-NODATA VALUE -1. 01500000
015100 01 UDF-SQLSTATE PIC X(5). 01510000
015200 88 UDF-SQLSTATE-OK VALUE '00000'. 01520000
015300 88 UDF-SQLSTATE-FAIL VALUE '38999'. 01530000
015400 01 UDF-FUNC. 01540000
015500 49 UDF-FUNC-LEN PIC 9(4) USAGE BINARY. 01550000
015600 49 UDF-FUNC-TEXT PIC X(137). 01560000
015700 01 UDF-SPEC. 01570000
015800 49 UDF-SPEC-LEN PIC 9(4) USAGE BINARY. 01580000
015900 49 UDF-SPEC-TEXT PIC X(128). 01590000
016000 88 UDF-SPEC-TEXT-CHAR VALUE 'MD5CHAR'. 01600000
016100 88 UDF-SPEC-TEXT-CLOB VALUE 'MD5CLOB'. 01610000
016200 01 UDF-DIAG. 01620000
016300 49 UDF-DIAG-LEN PIC 9(4) USAGE BINARY. 01630000
016400 88 UDF-DIAG-LEN-INIT VALUE 70. 01640000
016500 49 UDF-DIAG-TEXT PIC X(70). 01650000
016600 88 UDF-DIAG-TEXT-INIT VALUE SPACE. 01660000
016700 01 UDF-DBINFO. 01670000
016800* Location length and name 01680000
016900 02 UDF-DBINFO-LOCATION. 01690000
017000 49 UDF-DBINFO-LLEN PIC 9(4) USAGE BINARY. 01700000
017100 49 UDF-DBINFO-LOC PIC X(128). 01710000
017200* Authorization ID length and name 01720000
017300 02 UDF-DBINFO-AUTHORIZATION. 01730000
017400 49 UDF-DBINFO-ALEN PIC 9(4) USAGE BINARY. 01740000
017500 49 UDF-DBINFO-AUTH PIC X(128). 01750000
017600* CCSIDs for DB2 for OS/390 01760000
017700 02 UDF-DBINFO-CCSID PIC X(48). 01770000
017800 02 UDF-DBINFO-CDPG REDEFINES UDF-DBINFO-CCSID. 01780000
017900 03 DB2-CCSIDS OCCURS 3 TIMES. 01790000
018000 04 DB2-SBCS PIC 9(9) USAGE BINARY. 01800000
018100 04 DB2-DBCS PIC 9(9) USAGE BINARY. 01810000
018200 04 DB2-MIXED PIC 9(9) USAGE BINARY. 01820000
018300 03 DB2-ENCODING-SCHEME PIC 9(9) USAGE BINARY. 01830000
018400 03 DB2-CCSID-RESERVED PIC X(8). 01840000
018500* Schema length and name 01850000
018600 02 UDF-DBINFO-SCHEMA0. 01860000
018700 49 UDF-DBINFO-SLEN PIC 9(4) USAGE BINARY. 01870000
018800 49 UDF-DBINFO-SCHEMA PIC X(128). 01880000
018900* Table length and name 01890000
019000 02 UDF-DBINFO-TABLE0. 01900000
019100 49 UDF-DBINFO-TLEN PIC 9(4) USAGE BINARY. 01910000
019200 49 UDF-DBINFO-TABLE PIC X(128). 01920000
019300* Column length and name 01930000
019400 02 UDF-DBINFO-COLUMN0. 01940000
019500 49 UDF-DBINFO-CLEN PIC 9(4) USAGE BINARY. 01950000
019600 49 UDF-DBINFO-COLUMN PIC X(128). 01960000
019700* DB2 release level 01970000
019800 02 UDF-DBINFO-VERREL PIC X(8). 01980000
019900* unused 01990000
020000 02 FILLER PIC X(2). 02000000
020100* Database Platform 02010000
020200 02 UDF-DBINFO-PLATFORM PIC 9(9) USAGE BINARY. 02020000
020300* # of entries in Table Function column list 02030000
020400 02 UDF-DBINFO-NUMTFCOL PIC 9(4) USAGE BINARY. 02040000
020500* reserved 02050000
020600 02 UDF-DBINFO-RESERV1 PIC X(24). 02060000
020700* Unused 02070000
020800 02 FILLER PIC X(2). 02080000
020900* Pointer to Table Function column list 02090000
021000 02 UDF-DBINFO-TFCOLUMN POINTER. 02100000
021100* Pointer to Application ID 02110000
021200 02 UDF-DBINFO-APPLID POINTER. 02120000
021300* reserved 02130000
021400 02 UDF-DBINFO-RESERV2 PIC X(20). 02140000
021500* 02150000
021600 01 APPLICATION-ID PIC X(32). 02160000
021700 Procedure Division using UDFPARM1, 02170000
021800 UDFPARM2, 02180000
021900 UDF-RIND1, 02190000
022000 UDF-RIND2, 02200000
022100 UDF-SQLSTATE, 02210000
022200 UDF-FUNC, 02220000
022300 UDF-SPEC, 02230000
022400 UDF-DIAG, 02240000
022500 UDF-DBINFO. 02250000
023500 A00-CONTROL SECTION. 02350000
023600 A0010. 02360000
025100 SET UDF-SQLSTATE-FAIL TO TRUE 02361001
023700 IF UDF-RIND1 >= 0 02370000
025100 SET UDF-SQLSTATE-OK TO TRUE 02510001
INITIALIZE UDFPARM2 02520001
025500 MOVE UDFPARM1-TEXT(1:UDFPARM1-LEN) 02550004
TO UDFPARM2-X(9 - UDFPARM1-LEN:UDFPARM1-LEN) 02560005
030500 END-IF. 03050000
030600 A0099. 03060000
030700 goback. 03070000
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.