Skip to content

Instantly share code, notes, and snippets.

@NielsLiisberg
Last active March 31, 2023 11:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save NielsLiisberg/7fee408e2934d26c4fc285f2c0e4397d to your computer and use it in GitHub Desktop.
Save NielsLiisberg/7fee408e2934d26c4fc285f2c0e4397d to your computer and use it in GitHub Desktop.
SQL Run bash scripts and return the data to SQL in ASCII
-- Run a bash command or script and returns the stdout as a table.
-- Each text line will be a row in the result set - the magic is done by the cool "pipe" statement.
-- Data is returned in binay (ASCII) so you need the ASC_TOO_EBCDIC to look at at the rows from with in ACS
-- So if you just heed to run bash text then use my gist bash_table
--
-- It assumes bash is installed by YUM so
-- it will be in the default location /QOpenSys/pkgs/bin/bash
-- You can use this aproach to other shells like sh, qsh setc.
-- You need to have the "ifs_write" procedure found on my gist
-- You also need library QSYSINC installed:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun -
-- (C) Niels Liisberg 2021-2023
--
-- This gist is distributed on an "as is" basis, without warranties
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
call qusrsys.ifs_write('/tmp/openfile.c' , '
#include <sys/stat.h>
#include <fcntl.h>
#include <string.h>
#include <QP2SHELL.h>
{
char cmd [32000];
memcpy ( cmd , BASH_TABLE_BIN.COMMAND.DAT , BASH_TABLE_BIN.COMMAND.LEN);
strcpy ( cmd + BASH_TABLE_BIN.COMMAND.LEN , ">");
strcpy ( cmd + BASH_TABLE_BIN.COMMAND.LEN+1 , MAIN.TEMPNAME);
QP2SHELL ("/QOpenSys/pkgs/bin/bash" , "-c" , cmd);
*strchr(MAIN.TEMPNAME,'' '') = 0x00;
MAIN.STMF = open (MAIN.TEMPNAME , O_RDONLY );
}
');
call qusrsys.ifs_write('/tmp/readfile.c' , '
MAIN.BUF.LEN = read (MAIN.STMF, MAIN.BUF.DAT, sizeof(MAIN.BUF.DAT));
');
call qusrsys.ifs_write('/tmp/closefile.c' , '
close (MAIN.STMF);
unlink (MAIN.TEMPNAME);
');
create or replace function qusrsys.bash_table_bin (
command varchar(32000) ccsid 65535
)
returns table ( stdout varchar(32000) ccsid 65535 )
set option output=*print, commit=*none, dbgview = *source --list
main:
begin
declare stmf int ;
declare endpos int;
declare NEWLINE char(1) for bit data;
declare buf varchar(4096) for bit data default '';
declare rest varchar(4096) for bit data default'';
declare tempname char(64);
set tempname = '/tmp/f' || now() || '.txt' ;
include '/tmp/openfile.c';
include '/tmp/readfile.c';
set NEWLINE = x'0A';
while length(buf) > 0 do
repeat
set endpos = position(NEWLINE , buf);
if endpos > 0 then
pipe (rest concat substring(buf, 1 , endpos));
set buf = substring(buf, endpos + 1);
set rest = '';
else
set rest = rest concat buf;
set buf = '';
end if;
until endpos = 0
end repeat;
include '/tmp/readfile.c';
end while;
if length(rest) > 0 then
pipe (rest);
end if;
include '/tmp/closefile.c';
return;
end;
stop;
-- Use cases
------------
-- List home directories
select ascii_to_ebcdic(stdout) from table (qusrsys.bash_table_bin ('cd /home;ls')) a;
-- List all files for all users in their home directory
select
authorization_name,
home_directory ,
bash.*
from qsys2.user_info,
lateral (
Select ascii_to_ebcdic(stdout) as "Files in home dir"
from table (
bash_table_bin ('cd ' || cast(home_directory as varchar(256)) || ';ls')
)
) bash;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment