Skip to content

Instantly share code, notes, and snippets.

@forstie

forstie/parse_statement udtf.sql

Last active March 4, 2022 15:52
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save forstie/25a0d0f5e9c36831dbd3bbff1fad170f to your computer and use it in GitHub Desktop.
Save forstie/25a0d0f5e9c36831dbd3bbff1fad170f to your computer and use it in GitHub Desktop.
Download ZIP
The parse_statement() UDTF has gone largely unnoticed. I'm adding it to my Hidden Gems of Db2 for i presentation. This gist is another fully functional example, which shows how to establist an exit program for ZDA traffic (ODBC users). Parse_statement provides the realiable technique for getting the job done.
Raw
parse_statement udtf.sql
-- =============================================================
-- Author: Scott Forstie
-- Date : March 1, 2020
-- Description: ODBC user activity comes in over QZDASxINIT jobs.
-- You can easily utilize a ZDA exit program to
-- identify specific SQL activity, when you
-- code your exit program to use QSYS2.PARSE_STATEMENT().
--
-- Here's the thing:
-- With the QSYS2.PARSE_STATEMENT() UDTF, you do not need to invent your own parser!
-- This UDTF will parse, but not execute an SQL statement.
-- The rows and columns returned will give you answers to question upon question.
--
-- Documentation: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfparsestatement.htm
-- Excellent IT Jungle article: https://www.itjungle.com/2006/11/08/fhg110806-story01/
--
-- Note: This example shows how ODBC users could be prevented from updating database tables.
-- =========================================================================================
--
-- category: Coolstuff
-- description: ZDA exit program and PARSE_STATEMENT()
-- minvrm: v7r2m0
stop;
cl: crtlib coolstuff;
create or replace procedure coolstuff.parse_zda (in sql_stmt_text clob(2097152) for sbcs data,
out allow_this_sql char(1) for sbcs data)
not deterministic modifies sql data
called on null input system_time sensitive no
set option commit = *none, dynusrprf = *user, usrprf = *user
begin
declare v_name_type varchar(8) for sbcs data;
declare v_name varchar(128) for sbcs data;
set allow_this_sql = '1'; /* SQL allowed by default */
-- Disallow UPDATE attempts, via ODBC, to tables in TOYSTORE
select name_type, name
into v_name_type, v_name
from table (
qsys2.parse_statement(sql_stmt_text)
)
where sql_statement_type = 'UPDATE'
and schema = 'TOYSTORE'
order by name_start_position limit 1;
if (v_name is not null) then
call systools.lprintf('parse_ZDA: No updates allowed for the TOYSTORE/'
concat v_name concat ' ' concat v_name_type);
set allow_this_sql = '0'; /* Disallow */
end if;
end;
stop;
cl:addlible qsysinc;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(ZDAEXIT2);
insert into qtemp.qcsrc values
(1,010101,'#include <string.h> '),
(2,010101,'#include <stdio.h> '),
(3,010101,'#include <ctype.h> '),
(4,010101,'#include <stdlib.h> '),
(5,010101,'EXEC SQL INCLUDE SQLCA; '),
(6,010101,'main(int argc, char *argv[]) '),
(7,010101,'{ '),
(8,010101,'long i; '),
(9,010101,'_Packed struct zdaq0200 { '),
(10,010101,' char name[10]; '),
(11,010101,' char servid[10]; '),
(12,010101,' char fmtid[8]; '),
(13,010101,' long funcid; '),
(14,010101,' char stmtname[18]; '),
(15,010101,' char cursname[18]; '),
(16,010101,' char prepopt[2]; '),
(17,010101,' char opnattr[2]; '),
(18,010101,' char pkgname[10]; '),
(19,010101,' char pkglib[10]; '),
(20,010101,' short drdaind; '),
(21,010101,' char commitf; '),
(22,010101,' char defaultCollection[10]; '),
(23,010101,' char namingMode[1]; '),
(24,010101,' char reserved1[2]; '),
(25,010101,' long offsetExtCursorName; '),
(26,010101,' long lengthExtCursorName; '),
(27,010101,' long offsetExtDefaultSchema; '),
(28,010101,' long lengthExtDefaultSchema; '),
(29,010101,' char reserved2[110]; '),
(30,010101,' long stmttextLength; '),
(31,010101,' char stmttxt[2097152]; '),
(32,010101,' } *sptr, stx; '),
(33,010101,' typedef _Packed struct clobhv_t '),
(34,010101,' { '),
(35,010101,' unsigned long length; '),
(36,010101,' char data[2097152]; '),
(37,010101,' } *CLOBHV_P; '),
(38,010101,' CLOBHV_P clobhvp; '),
(39,010101,' EXEC SQL BEGIN DECLARE SECTION; '),
(40,010101,' SQL TYPE IS CLOB(2097152) tclob1; '),
(42,010101,' char allowit[1]; '),
(43,010101,' EXEC SQL END DECLARE SECTION; '),
(44,010101,' /* initialize return variable to indicate this action is permitted */ '),
(45,010101,' strncpy(argv[1],"1",1); '),
(46,010101,' sptr = (_Packed struct zdaq0200 *) argv[2]; '),
(47,010101,' if (sptr->stmttextLength > 0) { '),
(48,010101,' clobhvp = (CLOBHV_P)&tclob1; '),
(49,010101,' memcpy(&clobhvp->data[0], sptr->stmttxt, sptr->stmttextLength); '),
(50,010101,' clobhvp->data[sptr->stmttextLength] = ''\x00''; '),
(51,010101,' clobhvp->length = sptr->stmttextLength; '),
(52,010101,' EXEC SQL call coolstuff.parse_ZDA(:tclob1,:allowit); '),
(53,010101,' /* return the decision of whether this action is blocked */ '),
(54,010101,' *argv[1] = allowit[0]; '),
(55,010101,' }} ');
cl: CRTSQLCI OBJ(COOLSTUFF/zdaexit2) SRCFILE(qtemp/qcsrc) COMMIT(*NONE) OBJTYPE(*MODULE) OUTPUT(*PRINT) DBGVIEW(*SOURCE);
cl: CRTPGM PGM(COOLSTUFF/zdaexit2) MODULE(COOLSTUFF/zdaexit2) ACTGRP(*CALLER) ALWRINZ(*YES);
cl: ADDEXITPGM EXITPNT(QIBM_QZDA_SQL2) FORMAT(ZDAQ0200) PGMNBR(1) PGM(COOLSTUFF/ZDAEXIT2) TEXT('Parse_Statement');
-- cl: ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED);
-- cl: strPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT);
-- To un-install:
-- cl: RMVEXITPGM EXITPNT(QIBM_QZDA_SQL2) FORMAT(ZDAQ0200) PGMNBR(1);
-- cl: ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED);
-- cl: strPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT);
stop;
-- Example user activity...
select * from qsys2.library_list_info where type = 'USER';
select * from qsys2.library_list_info where type = 'SYSTEM';
update toystore.sales set sales = sales * 2;
-- Failure:
-- ========
-- SQL State: HY001 Vendor Code: -101
-- Message:
-- [PWS9801] Function rejected by user exit program ZDAEXIT2 in COOLSTUFF.
-- Cause . . . . . : User exit program ZDAEXIT2 in library COOLSTUFF called for the function and returned an indication that the function should not be done. Recovery . . . : Determine why the user exit program rejected the function, correct the problem, and run the function again.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment