LOAD table

[Former Member]

Is there an easy way to extract the columns list for all the tables to pass it to the load table table_name(col1,col2...). Need to extract for 600 tables to LOAD for the migration to a bigger page size from IQ 12.7 to IQ 15.4.

[markmumy · Accepted solution]

You could try some code like this.  It is a bit dated and hasn't been cleaned up in a while.

set temporary option quoted_identifier='on';

go

create or replace procedure gen_load_ASCII

  @pathdir char(255) default '/tmp'

  , @inowner varchar(255) default '%'

  , @intblnm varchar(255) default '%'

  , @fileext varchar(255) default 'txt'

as

--set nocount on

set temporary option quoted_identifier='on'

declare @tblnm char(128)

declare @tblowner char(128)

declare @colnm char(128)

declare @tblid int

declare @state int

declare @first int

declare @sqlstate int

declare get_tbl

        cursor for

        select

  table_name,  table_id , user_name ( sysuserperm.user_id )

        from

  systable, sysuserperm

        where

  table_type='BASE' -- only grab permanent tables

        and systable.server_type='IQ' -- only grab IQ tables

        and sysuserperm.user_id = systable.creator

        --and sysuserperm.user_id = user_id( 'dbo' ) -- only grab those for a specific user

        and sysuserperm.user_name like @inowner

  and -- lets eliminate a lot of system tables....

  (

  table_name not like 'sys%'

  and table_name not like 'rs_%'

  and table_name not like 'jdbc_%'

  and table_name not like 'EXCLUDE%'

  and table_name not like 'RowGen%'

  and table_name not like 'DUMMY%'

  and table_name not like 'java_%'

  and lower( table_name ) like @intblnm

  )

        order by user_name, table_name

        for read only

declare get_col cursor for

select column_name=sc.column_name

from syscolumn sc, sysdomain sd

where table_id=@tblid

and sc.domain_id = sd.domain_id

order by column_id

if ( @inowner is null )

  set @intblnm='%'

if ( @intblnm is null )

  set @intblnm='%'

set @first=1

open get_tbl with hold

fetch next get_tbl into @tblnm,@tblid, @tblowner

set @sqlstate=@@sqlstatus

if ( @sqlstate=0 and @tblnm is not null and @first=1 )

begin

  message 'set temporary option quoted_identifier=''on'';' to client

  message 'set temporary option escape_character=''on'';' to client

  message 'set temporary option disable_ri_check=''on'';' to client

  message 'set temporary option string_rtruncation=''on'';' to client

  message 'go' to client

end

else

begin

  message char(10)+'***** No matching tables found to generate the SQL'+char(10)+' ' to client

end

while @sqlstate = 0

begin

        if sqlstate != '00000'

                break

        message 'begin' to client

        message 'declare @strttm datetime;' to client

        message 'declare @stoptm datetime;' to client

        message 'declare @rc int;' to client

        message 'set @strttm = getdate();' to client

        message 'truncate table ' + @tblowner + '.' + @tblnm+';' to client

        message 'set @rc = @@rowcount;' to client

        message 'set @stoptm = getdate();' to client

        message 'message ''TRUNC STATS: ' +@tblowner+'.'+@tblnm+' truncation time: '' || datediff( ms, @strttm, @stoptm ) ||  '', rows: '' ||  @rc to client;' to client

        message 'set @strttm = getdate();' to client

        message 'load table ' + @tblowner + '.' + @tblnm to client

        select @state=1

        open get_col with hold

        fetch next get_col into @colnm

        while @@sqlstatus = 0 --while (1=1)

        begin

                if sqlstate != '00000'

                        break

                if @state=1

                begin

                        message '(' to client

                        select @state=2

        message  ' "' + @colnm + '"' to client

                end

                else

                begin

  message  ' ,"' + @colnm + '"' to client

                end

        fetch next get_col into @colnm

        end

        message ')' to client

        close get_col

        message 'from ''' + @pathdir + '/' + @tblnm + '.' + @fileext + '''' to client

        message 'on file error continue' to client

        message 'quotes off' to client

        message 'escapes off' to client

        message 'preview off' to client

        message 'FORMAT ASCII' to client

        message 'delimited by ''\x09''' to client

        message 'row delimited by ''\n''' to client

        message ';' to client

        message 'set @rc = @@rowcount;' to client

        message 'set @stoptm = getdate();' to client

        message 'message ''LOAD STATS: ' +@tblowner+'.'+@tblnm+' load time: '' || datediff( ms, @strttm, @stoptm ) ||  '', rows: '' ||  @rc to client;' to client

        message 'end' to client

        message 'go' to client

        message 'commit' to client

        message 'go' to client

        message ' ' to client

  set @first=0

  fetch next get_tbl into @tblnm,@tblid, @tblowner

  set @sqlstate=@@sqlstatus

end

close get_tbl

go

-- exec gen_load_BCP [path_to_files], [tbl_owner], [tbl_name], [file_extension]

--exec gen_load_BCP @intblnm='markt%', @fileext='del'

--exec gen_load_BCP @intblnm='markt%', @tblowner='FIG_ADS', @fileext='del'

--exec gen_load_BCP @pathdir='/sybase/dbdump/FIG_FILES', @tblowner='FIG_ADS', @intblnm='my_tables%', @fileext='del'

exec gen_load_ASCII '/sybase/data/my_files', '%', 'mark%'

go

[Former Member · Accepted solution]

Mark

Many thanks. It helped for the regular tables. Having issues with the tables that has identity columns,

even when I

set temporary option identity_insert='dbo.tableA'

go

set temporary option identity_insert='dbo.tableB'

go

Error 'db_sqlins.cxx:3501' : Cannot insert or update Column seq: set option 'identity_insert' to the specific table name containing the identity column to be modified.

Another set fails with cannot allow null with integer, float,timestamp errors

much appreciated for the script, have moved forward with the load...

[Gisung]

Hi,

Running iqunload in schema unload mode generates a reload SQL file that contains all the SQL statements necessary to re-create the legacy database schema.

You can use schema unload mode to create an empty version of a database. When you execute iqunload in schema unload only mode, using the command line argument -n, a file that contains all the SQL necessary to create the database schema is created.

You can find how to use as follows.

SyBooks Online

==

Gi-Sung Jang

[Former Member]

hello xin xun

xie xie. I did the iqunload -n extracted the schema and then reloaded on to a empty database. having issues with the load table with iidentity columns, few tables with bad data and one huge 1.2 TB table.

working on it, have posted issues on the forum, hoping to get some answers soon.

[c_baker]

You can use PowerDesigner to reverse-engineer the existing database.

I have an extension that can then be added to the IQ .pdm that will generate your LOAD TABLE statements for you.

There is a lot in it (It can create indexes, etc), but if you are familiar with PowerDesigner, you will also get extra tabs in a table's 'SQL Preview' property sheet.

You can use the 'Tools/Extended Generation...' dialog and the 'Options' tab to adjust the parameters.  You can also use this dialog to create the LOAD TABLE scripts for all the tables, instead of copying and pasting from the new 'SQL Preview' tabs.

(Still working on it to generate the 'TEMP EXTRACT...' statements though)

Rename the attached .xml file to .xem to use it.

Chris

[c_baker]

Another piece of the extension just posted also will generate the INSERT...SELECT statement for you if you want to also just connect to IQ 12.7 as a remote server from the IQ 15.4 to pull the data in.

You can still use PD to generate the DDL for the IQ 15.4.

Chris

[Former Member]

Chris

Thank you. Have been using the script that Mark had provided, have issues with the identity columns and few I think I have bad data in it. will be using different delimiters to pass through it.

Also, one of the table has 1.2 TB data in it, have set the extract_name1='a.txt' , extract_name2=' ' and set the extract size as extract_size1=1073741824 and extract_size2=' ' (assuming it will take default of 512 GB) since mine is RHEL 6.4 env.

it throws an error, incorrect syntax at line 1. where did I go wrong or any suggestions on how to unload 1.2 TB of data,