Ispirer Chyfo is a powerful, flexible and high-performance database migration and movement tool. Chyfo can be also used as a data export tool.
This tool significantly facilitates the movement of database schemas and data between different or same database systems. Chyfo provides the fastest way to migrate databases because it uses database native tools to import/load data.
Chyfo can export data from any database accessible through the ODBC interface. It exports data to text files (CSV, TAB delimited, fixed length and SQL INSERT statements are supported formats), generates native DDL (Data Definition Language) and import/load scripts for various databases. Chyfo uses the ODBC interface for exporting data only. The ODBC interface is fast enough in comparison with native interfaces as regards the data export.
Chyfo is especially designed for Oracle, IBM DB2, Microsoft SQL Server, Sybase and MySQL databases but can be also very helpful for other databases such as Informix, Access, dBase, FoxPro, Paradox and others.
It has a lot of features useful for DBAs:
Chyfo runs on Microsoft Windows 9x/NT/2000.
Chyfo is a command prompt tool.
For using Chyfo you have to run a command prompt environment (running cmd.exe on Windows NT/2000 or command.exe on Windows 9.x) then set options for Chyfo and run Chyfo.exe (ChyfoPro.exe for Pro version).
Available command prompt options:
ChyfoPro.exe /D = DataSourceName [/U = UserName] [/P = Password] /T = TableName or /S = SelectClause or /SF = SelectClauseFile or /TF=TableListFile or/and /V=ViewName [/EXC = ExcludeColList] [/SROW = StartRow] [/CNROWS = NumRows] [/TARGET = TargetDBMS] [/DIR = OutFilesDir] [/LOBDIR = LOBFilesDir] [/OSN=OutSchemaName] [OTN = OutTableName] [/OFN = OutFilesName] [/OTF=OutTextFileName] [/EMPS] [/DDL] [/OF = OutFormat] [/CDEL=ColDel] [/DECPT=DecPoint] [/STDEL=StDel] [LOBIN] [/TABLST=ListFileName] [/R = RowsPrefetch] [/OPT = OptionsFile] [/NSTOP] [/LOG=LogFile] [/REG] [/UNREG]
Options description:
Note: The command prompt parameters are not case sensitive.
Syntax: /D=DataSourceName
This option specifies name of the database from which data are exported. This is an ODBC alias for the database.
This option is mandatory. If you work with one database all the time set the database name in the Chyfo.ini file. Then when you omit this option in the command prompt its value is taken from the initialization file. See Chyfo.ini File Options for details.
Syntax: /U=UserName
Specifies the user name trying to connect to the database.
If you use the same login information all the time you can specify it in the Chyfo.ini file. In this case you can omit assignment this information in the command prompt every time you run Chyfo. See Chyfo.ini File Options for details.
Syntax: /P=Password
Specifies the password of the user trying to connect to the database.
If you use the same login information all the time you can specify it in the Chyfo.ini file. In this case you can omit assignment this information in the command prompt every time you run Chyfo. See Chyfo.ini File Options for details.
Syntax: /T=[SchemaName.]TableName [, NextTable]
This option specifies the tables that are exported.
If the database supports schemas you can either specify schema name for the table explicitly or use default schema name for the connected user.
You can provide a comma separated list of tables or table templates (Table templates are available in Chyfo Pro only). For example, /T=*.* or /T=%.% will export all tables accessible on the database, /T=schema1.*, schema2.* or /T=schema1.%, schema2.% will export all tables of the schemas schema1 and schema2.
Note: Some databases don't support the schemas. For example, to export all tables of an Access database you have to use /T=* or /T=%.
When this option is specified all rows and columns of each table are exported. You can define a subset of the table using options /EXC to exclude columns or /SROW and /CNROW to specify the start row and count of rows. To define more complex subsets of the table or subsets based on queries or multiple tables use options for defining queries /S and /SF.
Syntax: /V=[SchemaName.]ViewName [, NextView]
This option specifies the views whose definitions are extracted.
If the database supports schemas you can either specify schema name for the view explicitly or use default schema name for the connected user.
You can provide a comma separated list of views or views templates (Views templates are available in Chyfo Pro only). For example, /V=*.* or /V=%.% will extract all views accessible on the database, /V=schema1.*, schema2.* or /V=schema1.%, schema2.% will extract all views of the schemas schema1 and schema2.
Currently, Chyfo can extract view definitions from Oracle, IBM DB2 and MS SQL Server only.
Syntax: /S=SelectClause
This option specifies a SQL SELECT statement that defines data for exporting.
You can use this option to export data based on a subset of the table, multiple tables or complex queries.
You can optionally enclose the statement in double quotes. If you only want to exclude some columns consider using options /T and /EXC. When the statement is too large consider using the option /SF that allows providing a file containing statement.
Syntax: /SF=SelectClauseFile
This option specifies the path to the file containing a SQL SELECT statement for exporting data. In all other respects this option is same to /S.
Syntax: /TF=FileName
This option allows you providing the file containing list of tables for converting. There can be only one table in each line of the file. To comment a line use a semicolon in the first not blank position.
You can create a table list file manually or using option /TABLST to generate it from a datasource by templates.
If the file name is not explicitly specified the default file is chyfo.lst. The file must be located in the directory specified in the option /DIR.
Syntax: /EXC=Column1 [,ColumnNext]
This option specifies a list of columns that must be excluded from converting. Columns must be separated by commas.
You can use the option /S to enumerate columns you want to convert. But in some cases especially when the column list is too long it is better to enumerate columns you want to exclude. In this case it is better to use options /T and /EXC instead of /S.
Syntax: /SROW=StartRow
This option specifies the number of the start row to convert. The default value is 1.If you want to use this option to split converting of a large table to the parts you should be careful because in some cases it is not reliable way. If it is possible use SQL WHERE clause in the option /S to split a table.
Syntax: /CNROW=CountOfRows
This option specifies the count of rows for exporting. The default value is all rows that match query condition.
Syntax: /TARGET=TargetDBMS
This option specifies database type for generating native DDL (CREATE TABLE, CREATE INDEX, constraints) and import-load scripts.
Available values are:If you use the same target database type all the time you can specify it in the Chyfo.ini file. See Chyfo.ini File Options for details.
If this option is not specified DDL and import-load scripts are not generated.
Syntax: /DIR=OutFilesDir
This option specifies the directory for the output files. The default value is the current directory.
In this directory texts, scripts and logs files are created.If you use the same output directory all the time you can specify it in the Chyfo.ini file. See Chyfo.ini File Options for details.
Syntax: /LOBDIR=LOBFilesDir
This option specifies the directory where files for LOB objects are created. The default value is the subdirectory \LOBS of the directory that specified in the option /DIR.
For every LOB object one file is created and its path is specified in the text file.
LOB objects are CLOB and BLOB data types in Oracle, IBM DB2; TEXT and IMAGE data types in MS SQL Server, Sybase; MEMO data types in Access, FoxPro.
Syntax: /OSN=OutSchemaName
This option specifies the schema (owner) name of the output table. This value will be used in the DDL and import-load scripts. The default value is the schema (owner) name of the source table.
If you want to change schema and table names consider using the option /OTN.
Syntax: /OTN=[OutSchemaName.]OutTableName
This option specifies name of the output table. This table name is used in the DDL and import-load scripts. The default value is the name of the source table.If you want to change only the schema (owner) name for the table consider using the option /OSN.
Syntax: /OFN=OutFilesName
This option specifies name of the output files. The output files are text, log and scripts files. The default value is the name of the output table.
The file extentions like .txt, .sql, .cmd are added to this file name depending on the output file.
Syntax: /OTF=OutTextFileName
This option specifies a output file name and possibly a file extension for the text file. By default the file name is selected from the table name or from the option /OFN and .txt file extension is added.
When this option is specified .txt file extension is added and any other file extension can be specified.
Syntax: /EMPS
This option specifies that generated DDL statements will not contain the schema (owner) names.
If you always don't want to specify the schema (owner) names in the DDL statements set this option to Yes in the Chyfo.ini file. See Chyfo.ini File Options for details.
If you want to change the schema (owner) name for the tables to another value you can use the option /OSN.
Syntax: /DDL
This option specifies that only DDL scripts (CREATE TABLE, CREATE INDEX, primary keys, referential, check and unique constraints) are generated. When this option is specified the data are not exported and import-load scripts are not generated.
Syntax: /OF=OutFormat
This option specifies output format for the text file.
Available values are:The default value depends on the /TARGET option. If the /TARGET is Oracle, DB2 or not specified the default value is CSV format. If the /TARGET is MSSQL, Sybase or MySQL the default value is TAB format (This format is more preferable to moving data into MS SQL Server, Sybase and MySQL databases).
Syntax: /CDEL=ColumnDelimiter
This option specifies a single character column delimiter for text files. This option is only used for CSV format. The default value is a comma.
To specify the hexadecimal value of a delimiter use the next syntax: 0xhh. For example, /CDEL=0x2c specifies a comma as a delimiter.
Note. To use a TAB character as a column delimiter use the TAB output format.
You can set this option in the Chyfo.ini file. See Chyfo.ini File Options for details.
Syntax: /DECPT=DecPoint
This option specifies a single character decimal point for numeric values. The specified character is used in place of a period as a decimal point character. The default value depends on Regional Settings.
To specify the hexadecimal value of a decimal point character use the next syntax: 0xhh. For example, /DECPT=0x2c specifies a comma as a deciamal point character.
You can set this option in the Chyfo.ini file. See Chyfo.ini File Options for details.
Syntax: /STDEL=StatementDelimiter
This option specifies a single character that is used in the generated SQL scripts as the statement termination character. The default value is a semicolon (;).
To specify the hexadecimal value of a character use the next syntax: 0xhh. For example, /STDEL=0x40 specifies the AT sign (@) as a termination character.
You can set this option in the Chyfo.ini file. See Chyfo.ini File Options for details.
Syntax: /LOBIN
This options specifies that the data of LOB columns (CLOB, BLOB, TEXT, IMAGE and MEMO) are written inside of the text file.
By default the LOB data are written into separate files unless /TARGET is MSSQL or Sybase. This allows the LOB data to be moved into Oracle and IBM DB2 using SQL Loader and DB2 IMPORT/LOAD utilities.
When /TARGET is MSSQL or Sybase the LOB data are written inside of the text file even if the /LOBIN option is not specified. This allows the LOB data to be moved into MS SQL Server and Sybase using the BCP utility.
Syntax: /TABLST=[FileName]
When this parameter is specified Chyfo will generate only the list of tables that match the template in the option /T into the file.
For example, the command chyfo /t=dept.* /tablst=dept.lst will generate the list of tables that belong to the schema DEPT into the file dept.lst. Then you can comment tables that you don't want to convert (using a semicolon in the first line position) and run chyfo /tf=dept.lst.
You can also use this parameter to see which tables are available on a data source.
This option is available in Chyfo Pro only. If the file name is not explicitly specified the default file is chyfo.lst. The file is saved in the directory specified in the option /DIR.
Syntax: /R=RowsPrefetch
This option specifies the number of rows that are read before they are saved in the output file. The default value is 1000 rows.
Also this value is used to specify internal buffers and commit points in generated scripts for supported import-load utilities. Specifying this option can significantly speed up data exporting and especially importing-loading.
Syntax: /OPT=OptionsFile
This option specifies the name of a file that contains commonly used command prompt parameters.
To save commonly used parameters you can use Chyfo.ini file options, but there can be cases when using of a separate option file is more preferable.
Set options in the option file in format as they are appeared in the command prompt.
Syntax: /NSTOP
This option specifies to continue processing when an error occurs. If you specify this option and an error occurs Chyfo does not stop processing and tries to process the next table.
You can set this option in the Chyfo.ini file. See Chyfo.ini File Options for details.
Syntax: /LOG=LogFileName
This option specifies the log file which will be used to store logging information about the exporting process.
The default log file is chyfo.log. The file is saved in the directory specified in the option /DIR.
Syntax: /REG
You should use this option to enter registration information. You should enter registration name and serial key. See license.txt file that supplied with install package for license agreement.
Syntax: /UNREG
You can use this option to unregister Chyfo on the computer. See license.txt file that supplied with install package for license agreement.
Chyfo.ini file is used to save frequently used options and adjust script generations. This file consists of several sections.
Each section contains options and their values.
Syntax: Option=Value
When value for the option is not specified it means no value is assigned and the default value is used.
This section is used to save frequently used options. When an option is not specified in the command prompt its value is taken from the chyfo.ini file.
This section is used to customize data converting.
This section is used to adjust DDL and SQL script generations.
This section is used to adjust script generations when Oracle is the target database and specify properties when it is the source database.
If you what to load fields that contain only blanks into CHAR NOT NULL columns and the text file has the fixed length format you have to use DECODE function because SQL Loader treats blanks as NULLs. Set this option to Yes and Chyfo will generate DECODE function to convert NULLs to blanks.
This section is used to adjust script generations when IBM DB2 is the target database and specify properties when it is the source database.
The default value is No unless the target database is Oracle. If the target database is Oracle the default value is Yes.
You have to set this option to Yes when migrating DB2 tables into Oracle, because Oracle doesn't support microseconds in the DATE data type.
This section is used to adjust script generations when Microsoft SQL Server is the target database and specify properties when it is the source database.
The default value is No unless the target database is Oracle. If the target database is Oracle the default value is Yes.
You have to set this option to Yes when migrating MS SQL Server tables into Oracle, because Oracle doesn't support milliseconds in the DATE data type.
This section is used to adjust script generations when Sybase is the target database and specify properties when it is the source database.
This section is used to adjust script generations when MySQL is the target database and specify properties when it is the source database.
This chapter contains examples of using Chyfo.
Let us assume that in the examples below the datasource name is dsn, the user name is usr, the user password is pwd and the table is tab1. The table has columns col1, col2 and col3.
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1This example creates files tab1.txt (data file) and tab1.log (log file).
d:\>chyfo /d=dsn /u=usr /p=pwd /t=*.*This example creates the text and log files for each exported table.
d:\>chyfo /d=dsn /u=usr /p=pwd /s=select col1,col2 from tab1 where col3='abc' /of=tabYou can optionally enclose parameter values in double quotation marks.
d:\>chyfo /d=dsn /u=usr /p=pwd /s="select col1,col2 from tab1 where col3='abc'" /of=tab
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /exc=col1,col3Data of columns col1 and col3 are not exported. Also for the same effect you can use:
d:\>chyfo /d=dsn /u=usr /p=pwd /s=select col2 from tab1Which method is the best depends on the count of columns you want to export or exclude.
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /srow=10
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /cnrow=100
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /dir=d:\data /ofn=data1 /r=1000
This example creates files data1.txt (data file) and data1.log (log file)
in the directory d:\data.
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /target=oracle /r=10000This example creates files tab1.txt (data file), chyfo.log (log file), tab1_ddl.sql file that contains CREATE TABLE script like this:
CREATE TABLE tab1
(
COL1 INTEGER NOT NULL,
COL2 CHAR(3),
COL3 VARCHAR2(20)
);
CREATE INDEX, primary key, referential, check and unique constraints scripts;
tab1.cmd file (SQL Loader script) with tab1.ctl (SQL Loader control file)
that contains:
LOAD DATA
INFILE 'tab1.txt'
INTO TABLE tab1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(COL1,COL2,COL3)
LOAD DATA
INFILE 'tab1.txt'
INTO TABLE tab1
FIELDS TERMINATED BY X'09'
(COL1,COL2,COL3)
LOAD DATA
INFILE 'tab1.txt'
INTO TABLE tab1
(COL1 POSITION (1:11),
COL2 POSITION (13:15),
COL3 POSITION (17:36))
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /target=db2 /r=10000
This sample creates files tab1.txt (data file), chyfo.log (log file),
tab1_ddl.sql file that contains CREATE TABLE script like this:
CREATE TABLE tab1
(
COL1 INTEGER NOT NULL,
COL2 CHAR(3),
COL3 VARCHAR(20)
);
CREATE INDEX, primary key, referential, check and unique constraints scripts;
tab1.cmd file (IBM DB2 script) that contains:
Using DB2 IMPORT command
db2 import from tab1.txt of del commitcount 10000 insert into tab1
Using DB2 LOAD command
db2 load from tab1.txt of del savecount 10000 insert into tab1
Using DB2 IMPORT command
db2 import from tab1.txt of del modified by coldel0x09 chardel0x01
commitcount 10000 insert into tab1
Using DB2 LOAD command
db2 load from tab1.txt of del modified by coldel0x09 chardel0x01
savecount 10000 insert into tab1
Using DB2 IMPORT command
db2 import from tab1.txt of asc method L (1 11,13 15,17 36)
commitcount 10000 insert into tab1
Using DB2 LOAD command
db2 load from tab1.txt of asc method L (1 11,13 15,17 36)
savecount 10000 insert into tab1
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /target=mssql /r=10000
Note. TAB delimited output format for the text files is more
preferable to move data into a MS SQL Server database.
This sample creates files tab1.txt (data file), chyfo.log (log file), tab1.fmt (BCP Format File) and tab1_ddl.sql file that contains CREATE TABLE script like this:
CREATE TABLE tab1
(
COL1 INT NOT NULL,
COL2 CHAR(3),
COL3 VARCHAR(20)
);
CREATE INDEX, primary key, referential, check and unique constraints scripts;
tab1.cmd file (BCP utility script) that contains:
bcp tab1 in tab1.txt -f tab1.fmt -t, -r\n -Usa -Ppwd -b10000
BCP Format file:
6.0
3
1 SQLCHAR 0 11 "," 1 COL1
2 SQLCHAR 0 3 "," 2 COL2
2 SQLCHAR 0 20 "\r\n" 3 COL2
bcp tab1 in tab1.txt -f tab1.fmt -t\t -r\n -Usa -Ppwd -b10000
BCP Format file:
6.0
3
1 SQLCHAR 0 11 "\t" 1 COL1
2 SQLCHAR 0 3 "\t" 2 COL2
2 SQLCHAR 0 20 "\r\n" 3 COL2
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /target=sybase /r=10000
Note. TAB delimited output format for the text files is more
preferable to move data into a Sybase database.
This sample creates files tab1.txt (data file), chyfo.log (log file), tab1_ddl.sql file that contains CREATE TABLE script like this:
CREATE TABLE tab1
(
COL1 INT NOT NULL,
COL2 CHAR(3),
COL3 VARCHAR(20)
);
CREATE INDEX, primary key, referential, check and unique constraints scripts;
tab1.cmd file (BCP utility script) that contains:
bcp tab1 in tab1.txt -c -t, -Usa -Ppwd -b10000
bcp tab1 in tab1.txt -c -t\t -Usa -Ppwd -b10000
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /target=mysql /r=10000
Note. TAB delimited output format for the text files is more
preferable to move data into a MySQL database.
This sample creates files tab1.txt (data file), tab1.log (log file), tab1.ldi (MySQL LOAD DATA INFILE script) and tab1_ddl.sql file that contains CREATE TABLE script like this:
CREATE TABLE tab1
(
COL1 INT NOT NULL,
COL2 CHAR(3),
COL3 VARCHAR(20)
);
CREATE INDEX, primary key, referential, check and unique constraints scripts;
tab1.cmd file (MySQL script) that contains:
mysql.exe test < tab1.ldi
MySQL LOAD DATA INFILE script:
LOAD DATA INFILE 'tab1.txt'
INTO TABLE tab1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
mysql.exe test < tab1.ldi
MySQL LOAD DATA INFILE script:
LOAD DATA INFILE 'tab1.txt'
INTO TABLE tab1
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
d:\>chyfo /d=dsn /u=usr /p=pwd /t=tab1 /target=oracle /ddl
Only CREATE TABLE, CREATE INDEXES, primary keys, referential, check and
unique constraints scripts (*_ddl.sql or *_tab.sql, *_idx.sql and *.cns.sql
files) are created.
Sample content of Chyfo.ini file:
[common]
dsn=orcl
user=scott
pwd=tiger
target=db2
dir=d:\base
lobdir=
outformat=csv
prefetchrows=100
In this case you can run Chyfo as:
d:\>chyfo /t=tab1The table tab1 is exported. All omitted parameters are taken from Chyfo.ini file.
When converting was successful Chyfo's return code is 0 otherwise return code is -1. You can use this feature in batch files.
Chyfo can also return 1. This is possible when the option /NSTOP is used and means that the last operation was successful but there was an intermediate error (for example, processing one of the previous tables failed).
For example:
@echo off cls chyfo /d=datasource /u=user /p=pwd /t=table if errorlevel 0 goto ok echo. echo Converting error goto ex :ok echo. echo No errors :ex
Microsoft Windows interprets the characters such as %,| as the OS special characters. So if you want to use them in the command prompt or batch files you have to duplicate them.
Use:
chyfo /d=dsn /s=select * from t1 where field1 like '%%123%%'
instead of
chyfo /d=dsn /s=select * from t1 where field1 like '%123%'
Note: You don't need to duplicate these characters in the option file specified by the option /OPT.
The character > is treated by Windows as a redirection character. If you want to use this character in the SELECT statements you have to enclose them by double quotes:
chyfo /d=dsn /s="select * from t1 where field2 > 123"
The problem is your table has LOB columns that have less column numbers that the last not LOB column. This is not allowed by ODBC. LOB columns must be placed at the end of the select list.
To get around this problem you can use the option PLACE_LOBS_TO_END in the Chyfo.ini file or use the option /S instead of /T and manually enumerate LOB columns at the end of the select list. To get table columns you can use option /DDL.
There are differences between Chyfo and Chyfo Pro versions.
Only Chyfo Pro version generates DDL statements for primary keys, referential, check and unique constraints.
Using the table and view templates (like *, schema1.* in the options /T and /V) and generation of the list of tables by template (option /TABLST) are only available in Chyfo Pro version.
Using these features facilitates the movement or migration of large numbers of tables between databases.
For example:
Support is available via e-mail at support@ispirer.com from Monday through Friday. We also provide online support. If we receive a request during our online support time you can get an answer within 1-3 hours. In any case you will get a reply within 24 hours.
Online Support Time:
The Personal License allows you one registered copy of Chyfo either be used by a single person who uses the software personally on one or more computers, or installed on a single workstation used nonsimultaneously by multiple people, but not both.
For corporate environment you can get the Site License. If you have got the Site License you have rights of unlimited using of the product at your site.
You can choose an option to purchase Online, via Phone, Fax or Postal Mail.
Payments are available in US Dollars or Euro.
Please, visit our site www.ispirer.com/order/ for registration instructions and fees.
Please, visit Chyfo Home page for the latest news and information about Chyfo.