Reverse Engineer IQ dbspaces

For Sybase IQ it is sometimes handy to reverse engineer the statements that were used to create the various IQ dbspaces. You can do this with Power Designer but not everybody has access to the tool.

Below you will find a script that does a basic attempt to reverse engineer the statements for the "create database" and "create dbspace" commands. The script is not complete with regard to all the possible options for these statements. Feel free to make adjustments or mail the author when you miss something.

NB: Written specifically for IQ 15 !

begin
declare @db_file        long varchar;
declare @iq_file        long varchar;
declare @msg_file       long varchar;
declare @temp_file      long varchar;
declare @block_size     unsigned int;
declare @chunk_size     unsigned int;
declare @reserve_size   rowid;
declare @user_dbspace   long varchar;
declare @user_dbfile_name long varchar;
declare @user_file_name long varchar;
declare @counter        int;

declare local temporary table #statement(
        seq             int             not null default autoincrement,
        statement_txt   long varchar    null);

declare end_of_cursor exception for SQLSTATE '02000';

declare user_dbspaces cursor for
        select  dbspace_name
                from sysdbspace
                where   store_type      = 2
                and     dbspace_name    not in ('IQ_SYSTEM_MAIN', 'IQ_SYSTEM_TEMP', 'IQ_SYSTEM_MSG');

declare user_dbfiles cursor for
        select  f.dbfile_name,
                f.file_name
                from    sysdbfile f,
                        sysdbspace d
                where   d.store_type    = 2
                and     d.dbspace_name  = @user_dbspace
                and     d.dbspace_id    = f.dbspace_id
                order   by dbfile_id;

select  file_name into @db_file
        from    sysdbfile
        where   dbspace_id      = 0;

select  file_name into @iq_file
        from    sysdbfile
        where   dbfile_name     = 'IQ_SYSTEM_MAIN';

select  file_name into @msg_file
        from    sysdbfile
        where   dbfile_name     = 'IQ_SYSTEM_MSG';

select  file_name into @temp_file
        from    sysdbfile
        where   dbfile_name     = 'IQ_SYSTEM_TEMP';

select  block_size, chunk_size into @block_size, @chunk_size
        from    sysiqinfo;

select  reserve_size into @reserve_size
        from    sysiqdbfile
        where   dbfile_id       = 16384;

insert  into #statement(statement_txt)
        select  'create database  + @db_file + ' +
        ' IQ PATH  + @iq_file + ' +
        ' IQ PAGE SIZE ' + convert(varchar,@block_size * @chunk_size) +
        ' IQ RESERVE ' + convert(varchar,convert(int,(@reserve_size * 1024)/ @block_size)) +
        ' BLOCK SIZE ' + convert(varchar,@block_size) +
        ' MESSAGE PATH  + @msg_file + ' +
        ' TEMPORARY PATH  + @temp_file + ;' as "statement";

insert  into #statement(statement_txt)
        select  'alter dbspace ' + d1.dbfile_name +
                ' add file ' + d2.dbfile_name + '  + d2.file_name + ;' as "statement"
                from    sysdbfile d1,
                        sysdbfile d2
                where   d1.dbfile_name  in ('IQ_SYSTEM_MAIN', 'IQ_SYSTEM_TEMP')
                and     d1.dbfile_id    = d1.dbspace_id
                and     d2.dbspace_id   = d1.dbspace_id
                and     d2.dbfile_id    != d2.dbspace_id;

open    user_dbspaces;

user_dbspace_loop:
LOOP
        fetch   next user_dbspaces into @user_dbspace;
        if      SQLSTATE = end_of_cursor then
                leave user_dbspace_loop;
        end if;

        insert  into #statement(statement_txt)
                select  'create dbspace ' + @user_dbspace + ' using ';

        set     @counter = 0;

        open    user_dbfiles;

        user_dbfiles_loop:
        LOOP
                fetch   next user_dbfiles into @user_dbfile_name, @user_file_name;
                if      SQLSTATE = end_of_cursor then
                leave   user_dbfiles_loop;
        end if;

        if      @counter = 0 then
                insert  into #statement(statement_txt)
                        select  'file ' + @user_dbfile_name + ' + @user_file_name + ' as statement;

                set     @counter = @counter + 1;
        else
                insert  into #statement(statement_txt)
                        select  ',file ' + @user_dbfile_name + '  + @user_file_name + ' as statement;
        end if;

        end loop user_dbfiles_loop;
        close user_dbfiles;

        insert  into #statement(statement_txt)
                select 'iq store;' as "statement";
end loop user_dbspace_loop;

close   user_dbspaces;

insert  into #statement(statement_txt)
        select  'alter dbspace ' + d.dbspace_name +
                ' striping ' + case sb.striping_on when 'T' then 'on' else 'off' end +
                ' stripesizeKb ' + convert(varchar,stripe_size_kb) + ';' as "statement"
                from    sysiqdbspace sb,
                        sysdbspace d
                where   d.dbspace_id    = sb.dbspace_id
                and     d.store_type    = 2
                and     d.dbspace_name  != 'IQ_SYSTEM_MSG';

select  statement_txt
        from    #statement
        order   by seq;
end;

Sample output:

create database '/appl/sybase/dba/ITA_AM_SUM/ITA_AM_SUM.db' IQ PATH 'dbdev/devd1/main1.iq' IQ PAGE SIZE 131072 IQ RESERVE 69025
BLOCK  SIZE 32768 MESSAGE PATH 'ITA_AM_SUM.iqmsg' TEMPORARY PATH 'dbdev/devt1/temp1.iqtmp';

alter dbspace IQ_SYSTEM_MAIN add file main2 'dbdev/devd1/main2.iq';

alter dbspace IQ_SYSTEM_TEMP add file temp2 'dbdev/devt1/temp2.iqtmp';

alter dbspace IQ_SYSTEM_TEMP add file temp3 'dbdev/devt1/temp3.iqtmp';

create dbspace user_main using
file user_main_1'dbdev/devd1/user_main_1.iq'
,file user_main_2 'dbdev/devd1/user_main_2.iq'
,file user_main_3 'dbdev/devd1/user_main_3.iq'
,file user_main_4 'dbdev/devd1/user_main_4.iq'
,file user_main_5 'dbdev/devd1/user_main_5.iq'
iq store;

alter dbspace IQ_SYSTEM_MAIN striping on stripesizeKb 32;

alter dbspace IQ_SYSTEM_TEMP striping on stripesizeKb 64;

alter dbspace user_main striping on stripesizeKb 128;