SQL ASCII to EBCDIC conversion

ascii_to_ebcdic.sql

-- Convert Q&D ascii to EBCDIC 
-- This also showcase how to integrate the C code directly into your UDTF
-- You need my IFS_WRITE UDTF found elsewhere at my gist
--
-- 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
--
-- 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/convert.c' , '
{
    static unsigned char X1252TO277 [] = {                                           
        0x00,0x01,0x02,0x03,0x04,0x05,0x06,0x07,0x08,0x09,0x25,0x0B,0x0C,0x0D,0x0E,0x0F, 
        0x10,0x11,0x12,0x13,0x14,0x15,0x16,0x17,0x18,0x19,0x1A,0x1B,0x1C,0x1D,0x1E,0x1F, 
        0x40,0x4F,0x7F,0x4A,0x67,0x6C,0x50,0x7D,0x4D,0x5D,0x5C,0x4E,0x6B,0x60,0x4B,0x61, 
        0xF0,0xF1,0xF2,0xF3,0xF4,0xF5,0xF6,0xF7,0xF8,0xF9,0x7A,0x5E,0x4C,0x7E,0x6E,0x6F, 
        0x80,0xC1,0xC2,0xC3,0xC4,0xC5,0xC6,0xC7,0xC8,0xC9,0xD1,0xD2,0xD3,0xD4,0xD5,0xD6, 
        0xD7,0xD8,0xD9,0xE2,0xE3,0xE4,0xE5,0xE6,0xE7,0xE8,0xE9,0x9E,0xE0,0x9F,0x0A,0x6D, 
        0x79,0x81,0x82,0x83,0x84,0x85,0x86,0x87,0x88,0x89,0x91,0x92,0x93,0x94,0x95,0x96, 
        0x97,0x98,0x99,0xA2,0xA3,0xA4,0xA5,0xA6,0xA7,0xA8,0xA9,0x9C,0xBB,0x47,0x20,0x3F, 
        0x5A,0x21,0x22,0x23,0x24,0x28,0x26,0x27,0x5F,0x29,0x2A,0x2B,0x2C,0x2D,0x2E,0x2F, 
        0x30,0x31,0xBE,0x33,0xBD,0xB3,0x36,0x37,0xDC,0x39,0x3A,0x3B,0x3C,0x3D,0x3E,0xFF, 
        0x41,0xAA,0xB0,0xB1,0x32,0xB2,0x70,0xB5,0x34,0xB4,0x9A,0x8A,0xBA,0xCA,0xAF,0xBC, 
        0x90,0x8F,0xEA,0xFA,0x35,0xA0,0xB6,0x38,0x9D,0xDA,0x9B,0x8B,0xB7,0xB8,0xB9,0xAB, 
        0x64,0x65,0x62,0x66,0x63,0x5B,0x7B,0x68,0x74,0x71,0x72,0x73,0x78,0x75,0x76,0x77, 
        0xAC,0x69,0xED,0xEE,0xEB,0xEF,0xEC,0xBF,0x7C,0xFD,0xFE,0xFB,0xFC,0xAD,0xAE,0x59, 
        0x44,0x45,0x42,0x46,0x43,0xD0,0xC0,0x48,0x54,0x51,0x52,0x53,0x58,0x55,0x56,0x57, 
        0x8C,0x49,0xCD,0xCE,0xCB,0xCF,0xCC,0xE1,0x6A,0xDD,0xDE,0xDB,0xA1,0x8D,0x8E,0xDF, 
    };     
                                                                          
    static unsigned char X277TO1252 [] = {                                          
        0x00,0x01,0x02,0x03,0x04,0x05,0x06,0x07,0x08,0x09,0x5e,0x0B,0x0C,0x0D,0x0E,0x0F,
        0x10,0x11,0x12,0x13,0x14,0x15,0x16,0x17,0x18,0x19,0x1a,0x1B,0x1C,0x1D,0x1E,0x1F,
        0x7e,0x81,0x82,0x83,0x84,0x0a,0x86,0x87,0x85,0x89,0x8a,0x8B,0x8C,0x8D,0x8E,0x8F,
        0x90,0x91,0xa4,0x93,0xa8,0xb4,0x96,0x97,0xb7,0x99,0x9a,0x9B,0x9C,0x9D,0x9E,0x7f,
        0x20,0xa0,0xe2,0xe4,0xe0,0xe1,0xe3,0x7d,0xe7,0xf1,0x23,0x2e,0x3c,0x28,0x2b,0x21,
        0x26,0xe9,0xea,0xeb,0xe8,0xed,0xee,0xef,0xec,0xdf,0x80,0xc5,0x2a,0x29,0x3b,0x88,
        0x2D,0x2F,0xc2,0xc4,0xc0,0xc1,0xc3,0x24,0xc7,0xd1,0xf8,0x2c,0x25,0x5f,0x3e,0x3f,
        0xa6,0xc9,0xca,0xcb,0xc8,0xcd,0xce,0xcf,0xcc,0x60,0x3A,0xc6,0xd8,0x27,0x3d,0x22,
        0x40,0x61,0x62,0x63,0x64,0x65,0x66,0x67,0x68,0x69,0xAb,0xbb,0xf0,0xfd,0xfe,0xb1,
        0xb0,0x6A,0x6B,0x6C,0x6D,0x6E,0x6F,0x70,0x71,0x72,0xaa,0xba,0x7b,0xb8,0x5b,0x5d,
        0xb5,0xfc,0x73,0x74,0x75,0x76,0x77,0x78,0x79,0x7A,0xa1,0xbf,0xd0,0xdd,0xde,0xae,
        0xA2,0xa3,0xa5,0x95,0xa9,0xa7,0xb6,0xbc,0xbd,0xbe,0xac,0x7c,0xaf,0x94,0x92,0xd7,
        0xe6,0x41,0x42,0x43,0x44,0x45,0x46,0x47,0x48,0x49,0xad,0xf4,0xf6,0xf2,0xf3,0xf5,
        0xe5,0x4A,0x4B,0x4C,0x4D,0x4E,0x4F,0x50,0x51,0x52,0xb9,0xfb,0x98,0xf9,0xfa,0xff,
        0x5C,0xF7,0x53,0x54,0x55,0x56,0x57,0x58,0x59,0x5A,0xb2,0xd4,0xd6,0xd2,0xd3,0xd5,
        0x30,0x31,0x32,0x33,0x34,0x35,0x36,0x37,0x38,0x39,0xb3,0xdb,0xdc,0xd9,0xda,0x9f 
    };
    int i;
    MAIN.OUTPUT_STRING.LEN = ASCII_TO_EBCDIC.INPUT_STRING.LEN;
    for (i= 0; i < ASCII_TO_EBCDIC.INPUT_STRING.LEN; i++) {
        MAIN.OUTPUT_STRING.DAT[i] = X1252TO277[ASCII_TO_EBCDIC.INPUT_STRING.DAT[i]];
    }  
} 
');

create or replace function qusrsys.ASCII_TO_EBCDIC (
    input_string varchar(32000) ccsid 65535
) 
returns varchar(32000) ccsid 277
set option output=*print, commit=*none, dbgview = *source --list
main:begin
    declare output_string varchar(32000) ccsid 277 default '';
    include '/tmp/convert.c';
    return output_string;
end;

-- Simple conversion
values ( ascii_to_ebcdic(x'48656c6c6f20776f726c6420c6d8c5')); 

-- List all files for all users in their home directory
-- Note: This uses bash_table_bin found elsewhere on my gist 
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;