Copying data from one IQ server to another on the same platform

I’m re-posting this for future reference.  It’s not my original posting but from More details is available at:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.iq_12.6.iqref/html/iqref/X315746.htm.  Please refer to this for details.

Scenario:
a new IQ server has been created with DBSpaces different from the original and the size of the tables are manageable (can be handled by the network)

Read on and see if it meets your needs.  Thanks for this we were able to move data (not all) onto a new server but not without a lot of testing times.  Good luck.  Jun

[start of re-post]
http://search.sybase.com/kbx/solvedcases?id_number=11469338

Case Description

How to copy the data from one IQ server to another IQ Server on the same platform? The number and sizes of dbspaces are different.

Tip or Workaround

Resolution

Any of the following methods can be used to copy data from one IQ server to another IQ server:

1. The most efficient way is bulk loading of tables from flat files using LOAD TABLE.

1) Extract the data to a file (or files). There are three types of data extraction (binary, ASCII, binary/swap) and there are 27 options that control the behavior of extract. You can use the defaults or customize them if needed.  More details about data extraction options is available at: Sybase IQ System Adminstration Guide, Chapter:Moving Data In and Out of Databases, Section:Data extraction options http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.iq_12.6.iqapg/html/iqapg/BABIBIGH.htm

The following example extracts the data to the single disk file:

SET TEMPORARY OPTION Temp_Extract_Name1 = ‘daily_report.txt’;

SET TEMPORARY OPTION Temp_Extract_Name2 = ”;

SELECT ….;

SET TEMPORARY OPTION Temp_Extract_Name1 = ”;
Note:

The last SET is required to disable extraction. If extraction is not disabled, then the next SELECT statement executed on the same connection will overwrites the file daily_report.txt.

2) Use the LOAD TABLE statement to load the data from a flat file:

LOAD TABLE [ owner ].table-name [ ( load-specification, … ) ] FROM ‘filename-string’, … [ FORMAT { ‘ascii’ | ‘binary’ } ] … [ DELIMITED BY string ] … [ STRIP { ON | OFF } ] … [ QUOTES { ON | OFF } ] … [ ESCAPES { ON | OFF } ] … [ ESCAPE CHARACTER character ] … [ WITH CHECKPOINT ON|OFF ] … [ load-options ]

If you have to load the data in the IQ server running on a different box, you need to ftp the file there first:

You can specify a wide range of load options. These options tell the IQ server how to interpret and process the input file, and what to do when errors occur. The load options have to be in sync with the data extract options used when the file was created.

More details is available at: Sybase IQ System Adminstration Guide, Chapter:Moving Data In and Out of Databases, Section:Bulk loading data using the LOAD TABLE statement http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.iq_12.6.iqapg/html/iqapg/X100956.htm

2. You can use the OUTPUT statement to copy the information retrieved by the current query to a file:

OUTPUT TO filename [ APPEND ] [ VERBOSE ] [ FORMAT output-format ] [ ESCAPE CHARACTER character ] [ DELIMITED BY string ] [ QUOTE string [ ALL ] ] [ COLUMN WIDTHS (integer, à) ] [ HEXADECIMAL { ON | OFF | ASIS } ] [ ENCODING encoding ]

The output file can be in a different format. The data can be loaded using LOAD TABLE from this flat file to a table in the IQ server. The output file could also have some additional messages if you choose to.

More details is available at:SYBASE IQ Reference Manual, Chapter: Sql Statements, Section: OUTPUT statement[DBISQL] http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.iq_12.6.iqref/html/iqref/Output.htm

3. You can use the INSERT… LOCATION statement to add new rows to a database table. INSERT… LOCATION is using CIS to insert data rows to a table in a different IQ server, so for a large volume of data this option is slower then bulk copying. INSERT [ INTO ] [ owner.]table-name [ ( column-name [, …] ) ] … insert-load-options [ LOCATION ‘servername.dbname ‘ [ ENCRYPTED PASSWORD ][ PACKETSIZE packet-size ] ] … {select-statement}

[end of re-post]