SQL rename local default database to system name

rename_local_database.sql

-- When indexes are created in one languate - they will not be usable in other languages.
--
-- This procedure simply replaces the current *LOCAL 
-- database name with the name of the system name 
--
-- Simply paste this gist into ACS SQL and step through the code.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- It is a cool example how far you can go with SQL: Have fun 😀 
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
create or replace procedure qusrsys.rename_local_database () 
modifies sql data
external action
begin 
    declare old_name varchar(128);
    declare new_name varchar(128);
    declare continue handler for sqlstate '38501' begin end; 

    -- Removing the *LOCAL will shout !!
    call qcmdexc ('ADDRPYLE SEQNBR(5656) MSGID(CPA3E01) RPY(''G'')');           

    -- Now remove the local 
    call qcmdexc ('dltf qtemp/ZZZRDB');
    call qcmdexc ('DSPRDBDIRE OUTPUT(*OUTFILE) OUTFILE(qtemp/ZZZRDB)');
    
    select rwrdb 
    into   old_name
    from   qtemp/ZZZRDB
    where  RWRLOC =  '*LOCAL'
    limit  1;
    
    if old_name is not null then 
        call qcmdexc ('RMVRDBDIRE RDB(' concat old_name concat ')');
    end if;

    -- Add the new with system name 
    select host_name
    into new_name  
    from SYSTEM_STATUS_INFO 
    limit 1;
    
    call qcmdexc ('ADDRDBDIRE RDB(' concat new_name concat ') RMTLOCNAME(*LOCAL *IP)');

    -- And remove de default reply
    call qcmdexc ('RMVRPYLE SEQNBR(5656)');           

end;

-- Use case. On a new partition:
call qusrsys.rename_local_database();