Skip to Content
Author's profile photo Jun Macalalad

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:  Please refer to this for details.

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]

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


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

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 = ”;


SET TEMPORARY OPTION Temp_Extract_Name1 = ”;

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

  1. 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]


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]

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Eisen Wang
      Eisen Wang

      If need to copy database, why not "backup/restore database"? That would be much better.

      Author's profile photo Jun Macalalad
      Jun Macalalad
      Blog Post Author

      if its a simple copy of an IQ database to another, that will suffice.  But if you vary the size of the IQstore, or the number if IQ devices, then that is not possible -- but do correct me if I'm wrong (pre IQ ver 16)


      Author's profile photo Eisen Wang
      Eisen Wang

      Ah... I see...


      But I think it's a 2 steps work -- I would suggest

      1.  use "backup/restore database" and "rename to" to copy the source IQ database to another server.
      2.  use "move to" to move the objects to the other new IQ dbspaces on the new server which meet the size request and then drop the old IQ devices..


      How do you think about it?


      Author's profile photo Jun Macalalad
      Jun Macalalad
      Blog Post Author

      Hi Eisen,

      the restore will re-size the target IQ stores and you will notice the rest of the space (if its bigger) will not be usable by IQ and then moving it to the other IQ dbspaces (and drop them) might do the trick.

      Try it and let us know how you go.