convtohyb Command--Migrate Ingresdate and Character Data to X100 Types
Permission required: System administrator.
The convtohyb (convert to hybrid) utility converts data in a database to forms that can be more efficiently processed. The utility can perform several types of data migration. It can convert columns that contain date, time, or interval data stored in ingresdate or character data to the appropriate ANSI date or time data types. It can also migrate columns of character data into UTF-8 suitable for working with X100 table structures.
This utility creates the following files:
• An SQL script (ctohout.sql) that contains appropriate ALTER TABLE statements to be executed
• An execution script (ctohexec.ing on Linux and ctohexec.bat on Windows) for executing the SQL commands on the database
After running the convtohyb utility, examine the SQL script and then execute the execution script. When you run convtohyb in automodify mode, the execution script is executed immediately.
The operations to be performed must be specified by using the flags ‑to_utf8, ‑to_ansi, ‑to_unicode, or ‑ch_to_ansi. By default, all columns of all tables that have char, varchar, c, text, or ingresdate columns are altered and modified. If you specify a table list, only the listed tables are altered (if they have char, varchar, c, text, or ingresdate columns).
The convtohyb command has the following format:
convtohyb dbname [-param_file=filename] | [[-uuser] [-P] [-Ggroupid] [-dest=dir]
[‑to_utf8] [‑to_unicode] [‑to_ansi] [‑ch_to_ansi] [‑tz]
[-sqlfile=filename] [-automodify] [-col=column [-col=column]...] [{tables ...}]]
dbname
Specifies the name of the database to be exported.
-param_file=filename
Specifies the command file that contains the options to the convtohyb command. If used, do not specify other options on the convtohyb command. The file must contain all options and each parameter (dbname, table, user, and so on) must be on a separate line.
-uuser
Specifies the effective user for the session.
-P
Specifies the password if the session requires one.
-Ggroupid
Specifies the group ID of the user.
-dest=dir
Specifies the directory the output SQL file will be written to.
‑to_utf8
Looks for char, varchar, c, or text columns that contain data that is not in UTF-8 form. The data type of the column will be unchanged but the size might be increased if multi-byte characters are needed.
‑to_unicode
Performs the function of the convtouni utility, converting char and varchar columns to nchar and nvarchar respectively.
‑to_ansi
Converts ingresdate column content to the apparent ANSI date or time type if the column contains only dates, only times, only timestamps, only year to month intervals, or only day to second intervals. If –tz is also specified, timestamp or time columns will be made into the WITH TIME ZONE variant.
‑ch_to_ansi
Converts char, varchar, c, or text columns that contain consistent data to the apparent ANSI date or time type. If –tz is also specified, timestamp or time columns will be made into the WITH TIME ZONE variant.
Note: Make sure the character strings used in your database to represent textual dates are unambiguous. Convtohyb does not support date forms that result in an ambiguous date when converted to ANSI, such as dmy or mdy (for example, 09/06/2007) because the data may be-come inaccurate or lost. When you encounter dates that did not convert to ANSIDATE, you can identify such rows by running a query like: SELECT charcol FROM tbl WHERE charcol IS NOT ANSIDATE;
‑tz
Causes time and timestamp columns be written with the WITH TIME ZONE variant. This flag can be used with –to_ansi or –ch_to_ansi.
-sqlfile=filename
Specifies the name of the output SQL file.
-automodify
Modifies the tables immediately by running the execution script.
-col=column
Specifies the column to convert, in tablename.columnname format. Each column must be preceded by –col=. Only the specified columns are changed; the rest of the char and varchar columns remain unchanged.
Note: No more than 100 objects can be specified. This limit can be raised by modifying the utexe.def file. For more information, see the Database Administrator Guide.
tables
Specifies a list of tables to convert. If no list is specified, but the ‑col= option is, then all specified columns of all tables owned by the user are altered. However, for the tables specified on the –col= option, only the specified columns are converted and then only according to the option selected.
Note: No more than 100 objects can be specified. This limit can be raised by modifying the utexe.def file. For more information, see the Database Administrator Guide.