Import and export of Sybase IQ

Sybase IQ has always been known for its export-import performance, and it is not easy to use it well. Recently I have studied this, summarized as follows, for reference only.
1. Preface

Sybase IQ is a powerful ad hoc query server. Use Sybase IQ to isolate Decision support systems (Dss,decision supports System,reader) and online transaction processing systems (Oltp,online Transaction processing,writer). Sybase IQ is now widely used as a data warehouse database in the SG186 Data Center project.

2. Export

Export in Sybase IQ also known as uninstall, summary Sybase IQ unloading method No more than the following several:

2.1. Uninstalling data Using bcp

SYBASEIQ supports BCP in two ways, one is through the OCS-provided BCP, the syntax is similar to the ASE, and one is through IQ_BCP, with the following syntax:



USAGE:IQ_BCP [[database_name.] Owner.] table_name {in | out} datafile
[-c] [-T Field_terminator] [-R Row_terminator]
[-u username] [-p password] [-I. Interfaces_file] [-s server]
[-V] [-A Packet size] [-j client Character Set]
USAGE:BCP [[Db_name.] Owner.] Table_name[:slice_num] [partition pname] {in | out} [filename]
[-M maxerrors] [-F FormatFile] [-E Errfile]
[-F FirstRow] [-L LastRow] [-B batchsize]
[-N] [-c] [-T Field_terminator] [-R Row_terminator]
[-u username] [-p password] [-I. Interfaces_file] [-s server]
[-A Display_charset] [-Z Language] [-V]
[-A Packet size] [-j client Character Set]
[-t text or image size] [-E] [-G Id_start_value] [-N] [-X]
[-M LabelName Labelvalue] [-labeled]
[-K Keytab_file] [-R Remote_server_principal] [-c]
[-V [security_options]] [-Z Security_mechanism] [-Q] [-y]
[-X Trusted.txt_file]
[--maxconn Maximum_connections] [--show-fi] [--HIDE-VCC]


Both of these methods need to be configured, the open client's interface file under UNIX is interfaces, compared with the database option method of data offload, characterized by: one speed slower than the latter, but support client data offload. A specific example is given below:



bcp cmcc.d_district out "D_brand.dat"-c-t "|"-t32000-udba-psql-stestiq-jcp936 or


　　

IQ_BCP cmcc.d_district out "D_brand.dat"-c-t "|"-udba-psql-stestiq-jcp936


2.2 Text Data Mode

2.2.1, Sqladv Way

Examples are as follows:

In the CMD state:

C:>sqladv-sserver-uuser-ppassword-i C:est.sql-o C:estout.txt

But there are a few questions:

(1), out of the east of the format is not very standard:

First there is the field name of the table header, do not know how to remove.

The other is that the data between the fields are separated by n spaces, not by using tab,

However, these can be solved programmatically.

(2), when the bat processing started once sqladv, after the execution is not over, you must first close the SQLADV to execute.

2.2.2, isql way

The same effect can be achieved with isql, so that you can use the batch file directly, without the SQLADV way.

C:>isql-sserver-uuser-ppassword-i C:est.sql-o C:estout.txt

Test.sql:

SELECT * FROM Dssd_time where time_year=2001

Go

2.2.3, Output mode



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]
Output-format:
ASCII | Dbaseii | DBASEIII | Excel
| FIXED | FOXPRO | HTML | LOTUS | SQL | Xml


Quote

escape CHARACTER CHARACTER specifying escape characters

• Output data at the current running end

• You can specify the data output for the delimiter method, or you can specify how long each field is to be fixed. Shown

For example the following:



SELECT * from "DBA". " V_ICP_ID ";
OUTPUT to "C:empv_icp_id.out"
Delimited by "|"
FORMAT ASCII
Quote "";


The output is at the command execution end.

> # < file name > Output execution Results

> & < file name > output to file includes error message and execution information, execution result

For example:

SELECT *

From employee

>& Empfile

Or

SELECT * from PSDSS_DM. Aa_result ># D:mpdaaa_result.txt;

2.3. Option mode

You can export binary data in this way:



Set temporary option temp_extract_name1= "/apps/performance/iac"; --Set Output path
Set temporary option temp_extract_column_delimiter= "|"; --Set delimiter
Commit
SELECT * FROM P_abis_iac--Perform a lookup
Commit
Set temporary option temp_extract_name1= ""--Reset to console
Commit


After research, and on the basis of the East soft export tool to transform the batch table data export, to fill the east soft tools can not export the bulk of the shortcomings of the table.

2.4 Comparison of export methods

(1) option is a binary way, which is the fastest and avoids the problems of separators and nulls when importing.

(2) IQ_BCP mode supports client export.

(3) Several other ways are text mode, can be flexibly customized text export format, can only be exported on the server side.

3. Import

3.1. Load

Format of the LOAD statement:



LOAD [into] TABLE [owner.] Table-name [(Column-name, ...)]
from filename
[Load-option ...]
[Statistics-limitation-options]
Load-option:
CHECK CONSTRAINTS {on | OFF}
| Computes {on | OFF}
| DEFAULTS {on | OFF}
| Delimited by string
| ESCAPE CHARACTER CHARACTER
| ESCAPES {on | OFF}
| FORMAT {ASCII | BCP}
| hexadecimal {on | OFF}
| ORDER {on | OFF}
| PCTFREE Percent-free-space
| QUOTES {on | OFF}
| SKIP integer
| STRIP {on | OFF}
| With CHECKPOINT {on | OFF}


• If the field name does not appear in the field list, the padding is null, 0, empty, or default;

Fields that exist in the input file can be used with "filler ()". Ignore

Defaults {on | OFF} is the default value for the On field. Otherwise, NULL is taken

Quotes {ON | OFF} default is on, field delimiter is ' or '

delimited by option: can be a single character, up to 255 characters, for example: tab

Number as a split symbol:

... Delimited by ' nx09 '

Skip N ignores the previous n records;

Strip on| Whether the OFF-tail space is intercepted before insertion;

with CHECKPOINT on| Off defaults to OFF, and if set to ON, the command completes

, perform the checkpoint operation.

Here is the statement from load to table f_inn_ia_daily_sum from a text file:



Set temporary option Date_order=ymd;
Load Table F_inn_ia_daily_sum
(
Org_sid "+|+",
Deal_sid "+|+",
All_time_sid "+|+",
R_count_dim_sid "+|+",
T_tax_stor_cost "+|+",
T_stor_cost "+|+",
T_stor_sum "+|+",
Created_dt "x0a"
)
From "/load_data/f_inn_ia_daily_sum.txt"
ESCAPES OFF
QUOTES OFF
NOTIFY 100000
With CHECKPOINT on;
COMMIT


Where +|+ is the delimiter for the field, x0a is the delimiter of the record, which is the carriage return (in a text file).

Issues that are noted in load:

1. The load text file should be on the same IQ machine.

2. The type conversion error often occurs when load, so be aware of the same field type and length.

3. The last field has a null value, and a character conversion error occurs, but the real reason is that the last field has a null value.

The load statement for the last field null value:

Data:

256|59| Water Resources Kamphephe exhibition x00 fee |5903| Waterworks Waterworks Water Plant insurance x00 ship x00 ship x00x00|0|2006-12-01 02:15:19.0|2006-12-02 02:15:19.0

257|59| Water Resources Kamphephe exhibition x00 fee |5904| Water plant take-out insurance x00 ship x00 ship x00x00|0|2006-12-01 02:15:19.0|2006-12-02 02:15:19.0

258|59| Water Resources Kamphephe exhibition x00 fee |5905| industrial production take surface water insurance x00 ship x00 ship x00x00|0|2006-12-01 02:15:19.0| is empty

259|59| Water Resources Kamphephe exhibition x00 fee |5906| industrial production take groundwater insurance x00 ship x00 ship x00x00|0|2006-12-01 02:15:19.0| is empty

Because there is finally a null value, it is not loaded at the time of loading, to indicate the type of the null value. The load statement is as follows:



LOAD TABLE D_tax_kind
(Tax_kind_key "|",
Tax_kind_code "|",
Tax_kind_name "|",
Tax_item_code "|",
Tax_item_name "|",
Tax_flag "|",
Effective_date "|" null (""),
End_date "x0dx0a" null ("")
)
From "C:d_tax_kind.out"
ESCAPES OFF
QUOTES OFF
Format ASCII
With CHECKPOINT on;


Issues to be aware of:

When ASCII variable length data is loaded, field split symbols and line separators are generally described.

3.2 Insert Data loading

Grammar:



INSERT [into] [owner.] Table-name [(Column-name [, ...])]
... VALUES (expression ...)
Syntax 2
INSERT [into] [owner.] Table-name [(Column-name [, ...])]
... insert-load-options
... select-statement
Syntax 3
INSERT [into] [owner.] Table-name [(Column-name [, ...])]
... insert-load-options
[Location "Servername.dbname"
[ENCRYPTED PASSWORD] [PacketSize Packet-size]]
... {Select-statement}


3.3. Special Insert

Supports partial loading, loading from other remote server data.



INSERT into LineItem
(L_shipdate, L_orderkey)
Location "Detroit.asiqdb"
PacketSize 512
{SELECT l_shipdate, L_orderkey
From LineItem}


Specify the server name and data for the open client configuration via location "Detroit.asiqdb"

The library name.

3.4. Data loading of pipeline mode

Data loading with the operating system pipeline capabilities, reducing disk I/O makes data loading more efficient.

3.4.1, Pipeline Data Load command script



#!/bin/ksh
File=$1
If [-Z "${file}"]
Then
echo ""
echo "USAGE: $ sql_file_to_load"
echo ""
Exit
Fi
If [-R Cdrpipe]
Then
RM cdrpipe
Fi
Mknod/work_tmp/test_pipe/cdrpipe P
Nohup dbisqlc-c
"Eng=iq126;uid=dba;pwd=sql;dbn=iq126;links=tcpip{host=127.0.0.1:2660}"-
Q ${file} > ${file}.out 2>&1 &
Cat/work_tmp/test_pipe/rtqd_split.aa >/work_tmp/test_pipe/cdrpipe &
echo "Input from: ${file}"
echo "Output to: ${file}.out"


4. Summary

In summary, the export method is recommended to use the option, import using load in the bulk export of many tables, you may consider using the Neusoft export and load tools to generate bulk export and import scripts. Other relevant tools to use this article does not repeat.