Psap at 15:14, 20 January 2010

2010-01-20T15:14:02Z

New page

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;

[[Category:IQ]]

Reverse Engineer IQ dbspaces - Revision history

Psap at 15:14, 20 January 2010