Which scripting utility works with data in columns

On Windows, connect to the server using a named pipe. The directory in which to look for plugins. Specify this option if the --default-auth option is used to specify an authentication plugin but mysqldump does not find it. The transport protocol to use for connecting to the server. It is useful when the other connection parameters normally result in use of a protocol other than the one you want.

For details on the permissible values, see Section 4. The path name to a file in PEM format containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. This option is ignored for accounts that do not authenticate with one of those plugins. Do not drop the mysql schema when the dump file is restored.

By default, the schema is dropped. For connections to localhost , the Unix socket file to use, or, on Windows, the name of the named pipe to use. Options that begin with --ssl specify whether to connect to the server using encryption and indicate where to find SSL keys and certificates. See Command Options for Encrypted Connections. Controls whether to enable FIPS mode on the client side.

The --ssl-fips-mode option differs from other --ssl- xxx options in that it is not used to establish encrypted connections, but rather to affect which cryptographic operations to permit. These --ssl-fips-mode values are permitted:. The permissible ciphersuites for encrypted connections that use TLSv1.

The value is a list of one or more colon-separated ciphersuite names. For details, see Section 6. The permissible TLS protocols for encrypted connections. The value is a list of one or more comma-separated protocol names. The compression level to use for connections to the server that use the zstd compression algorithm. The permitted levels are from 1 to 22, with larger values indicating increasing levels of compression.

The default zstd compression level is 3. The compression level setting has no effect on connections that do not use zstd compression. Read this option file after the global option file but on Unix before the user option file.

If the file does not exist or is otherwise inaccessible, an error occurs. Use only the given option file. Exception: Even with --defaults-file , client programs read. Read not only the usual option groups, but also groups with the usual names and a suffix of str. For example, mysqldump normally reads the [client] and [mysqldump] groups. Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.

The exception is that the. This permits passwords to be specified in a safer way than on the command line even when --no-defaults is used. To create. Usage scenarios for mysqldump include setting up an entire new MySQL instance including database tables , and replacing data inside an existing instance with existing databases and tables. The following options let you specify which things to tear down and set up when restoring a dump, by encoding various DDL statements within the dump file.

In MySQL 8. Instead, to use --add-drop-database , use --databases with a list of schemas to be dumped, where the list does not include mysql. This information is not otherwise included in the output from mysqldump. This option is currently relevant only to NDB Cluster tables. This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

The following options print debugging information, encode debugging information in the dump file, or let the dump operation proceed regardless of potential problems. Permit creation of column names that are keywords. This works by prefixing each column name with the table name. Write additional information in the dump file such as program version, server version, and host. This option is enabled by default. To suppress this additional information, use --skip-comments.

Write a debugging log. MySQL release binaries provided by Oracle are not built using this option. Print debugging information and memory and CPU usage statistics when the program exits. If the --comments option is given, mysqldump produces a comment at the end of the dump of the following form:. However, the date causes dump files taken at different times to appear to be different, even if the data are otherwise identical.

The default is --dump-date include the date in the comment. Ignore all errors; continue even if an SQL error occurs during a table dump. One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped.

Without --force , mysqldump exits with an error message. With --force , mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing. If the --ignore-error option is also given to ignore specific errors, --force takes precedence.

Log warnings and errors by appending them to the named file. The default is to do no logging. See the description for the --comments option. The following options display information about the mysqldump command itself. The following options change how the mysqldump command represents character data with national language settings. The directory where character sets are installed. If no character set is specified, mysqldump uses utf8.

Turns off the --set-charset setting, the same as specifying --skip-set-charset. The mysqldump command is frequently used to create an empty instance, or an instance including data, on a replica server in a replication configuration. The following options apply to dumping and restoring data on replication source servers and replicas.

From MySQL 8. Both options have the same effect. Use this option before MySQL 8. The options automatically enable --source-data or --master-data. The options are similar to --source-data , except that they are used to dump a replica server to produce a dump file that can be used to set up another server as a replica that has the same source as the dumped server.

These are the replication source server coordinates from which the replica starts replicating. Inconsistencies in the sequence of transactions from the relay log which have been executed can cause the wrong position to be used. In addition, specifying this option causes the --source-data or --master-data option to be overridden, if used, and effectively ignored. The option value is handled the same way as for --source-data.

Setting 2 causes the statement to be written but encased in SQL comments. It has the same effect as --source-data in terms of enabling or disabling other options and in how locking is handled. The options are used to dump a replication source server to produce a dump file that can be used to set up another server as a replica of the source.

These are the replication source server coordinates from which the replica should start replicating after you load the dump file into the replica. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1. They also turn on --lock-all-tables , unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump see the description for --single-transaction.

In all cases, any action on logs happens at the exact moment of the dump. It is also possible to set up a replica by dumping an existing replica of the source, using the --dump-replica or --dump-slave option, which overrides --source-data and --master-data and causes them to be ignored. This statement prevents new GTIDs from being generated and assigned to the transactions in the dump file as they are executed, so that the original GTIDs for the transactions are used.

In MySQL 5. For MySQL 5. If you do not replay any further dump files on the target server, the extraneous GTIDs do not cause any problems with the future operation of the server, but they make it harder to compare or reconcile GTID sets on different servers in the replication topology.

In this case, either remove the statement manually before replaying the dump file, or output the dump file without the statement. You can also include the statement but manually edit it in the dump file to achieve the desired result. The possible values for the --set-gtid-purged option are as follows:. The default value. If GTIDs are not enabled on the server, the statements are not added to the output. An error occurs if you set this option but GTIDs are not enabled on the server.

Available from MySQL 8. For example, you might prefer to do this if you are migrating data to another server that already has different active databases. The following options specify how to represent the entire dump file or certain kinds of data in the dump file. They also control whether certain optional information is written to the dump file.

Batch mode can be accessed in two different ways. The most common way is to specify the -f option on the ttIsql command line followed by the name of file to run. The other way to use batch mode is to enter the run command directly from the interactive command prompt. The run command is followed by the name of the file containing ttIsql built-in commands and SQL statements to execute:.

The ttIsql utility can be customized to automatically execute a set of command line options every time a ttIsql session is started from the command prompt. A summary of ttIsql command line options is shown below.

When ttIsql starts up it reads the value of the TTISQL environment variable and applies all options specified by the variable to the current ttIsql session. If a particular command line option is specified in both the TTISQL environment variable and the command line, then the command line version always takes precedence.

The procedure for setting the value of an environment variable differs based on the platform and shell that ttIsql is started from. You can customize the ttIsql command prompt by using the set command with the prompt attribute:. The ttIsql utility has an online version of command syntax definitions and descriptions for all built-in ttIsql commands.

To access this online help from within ttIsql use the help command. To view a detailed description of any built-in ttIsql commands type the help command followed by one or more ttIsql commands to display help for.

The example below displays the online description for the connect and disconnect commands. To view a short description of all ttIsql built-in commands type the help command without an argument. To view a detailed description of all built-in ttIsql commands type the help command followed by the all argument.

On UNIX systems, you can use the 'editline' library to set up emacs default or vi bindings that enable you to scroll through previous ttIsql commands, as well as edit and resubmit them. This feature is not available or needed on Windows. To disable the 'editline' feature in ttIsql , use the ttIsql command set editline off. The editline lib prints the current bindings. When you execute ttIsql , the editline lib prints the current bindings.

The ttIsql utility stores a list of the last commands executed within the current ttIsql session. The commands in this list can be viewed or executed again without having to type the entire command over. Both SQL statements and built-in ttIsql commands are stored in the history list. Use the history command " h " to view the list of previously executed commands. For example:. The history command displays the last 10 SQL statements or ttIsql built-in commands executed.

To display more than that last 10 commands specify the maximum number to display as an argument to the history command. Each entry in the history list is identified by a unique number. To execute the last command that begins with a given string type the! You can save the l ist of commands that ttIsql stores by using the savehistory command:.

If the output file already exists, use the -a option to append the new command history to the file or the -f option to overwrite the file. The next example shows how to append new command history to an existing file. You can clear the list of commands that ttIsql stores by using the clearhistory command:. You can use the ttIsql edit command to edit a file or edit ttIsql commands in a text editor. The ttIsql edit command starts a text editor such as emacs , gedit , or vi.

For more information on changing the default text editor, see "Changing the default text editor for the ttIsql edit command".

You can only use one parameter at a time. If you do not specify a! If you do not specify a parameter or specify!! When you specify a file parameter, the editor edits the specified file. If TimesTen does not find an exact file match for the specified file parameter in the current working directory, it searches for file. If neither file exists, the editor creates the specified file in the current working directory. You can specify a path in the file parameter. The following example edits the new.

If you execute the ttIsql edit command with a file parameter, ttIsql does not execute the contents of the file after you exit the editor. You can edit a SQL statement that is stored in the history list of the current ttIsql session. When calling the ttIsql edit command specify the! The editor opens the ttIsql command in a temporary file that you can save in a preferred location. For more information on using the ttIsql history command, see "Using the ttIsql command history".

The following example searches for and edits the last ttIsql command that contains the search string create :. The prior example is equivalent to using the ttIsql edit command with the!! The contents of the file are executed as a single ttIsql command. If you do not want to execute the contents of the file, delete the contents of the file and save the file before you exit the editor.

The following example sets the default editor to vi :. The ability of ttIsql to display characters depends on the native operating system locale settings of the terminal on which you are using ttIsql. To override the locale-based output format, use the ncharencoding option or the -N option.

There are several ttIsql commands that display information on database structures. The most useful commands are summarized below:. Use the describe command to display information on individual database objects. Displays parameters for prepared SQL statements and built-in procedures. The cachegroups command is used to provide detailed information on cache groups defined in the current database. The attributes of the root and child tables defined in the cache group are displayed in addition to the WHERE clauses associated with the cache group.

The argument to the cachegroups command is the name of the cache group that you want to display information for. The dssize command is used to report the current memory status of the permanent and temporary memory regions as well as the maximum, allocated and in-use sizes for the database. The following example uses the k option to print the database size information in KB:. The tablesize command displays the detailed analysis of the amount of space used by a table. Once you call the ttComputeTabSizes built-in procedure, which analyzes the table size of the indicated tables, the tablesize command displays the total size data for all analyzed tables.

Executing the tablesize command with no arguments displays available sizing information for all tables that have had the ttComputeTabSizes computation run. When you provide a table as an argument, tablesize displays available sizing only for the indicated table. The following example invokes the ttComputeTabSizes built-in procedure to calculate the table size of the employees table.

Then, the tablesize command displays the sizing information gathered for the employees table. Space occupied by free rows cannot be used by the system for storing other system objects or structures. If you want a more recent computation, re-execute ttComputeTabSizes and display the new output. You can find a description for each calculated value in the "SYS. The monitor command displays all of the information provided by the dssize command plus additional statistics on the number of connections, checkpoints, lock timeouts, commits, rollbacks and other information collected since the last time the database was loaded into memory.

Commands prefixed by all display all of this type of object. The following example demonstrates the procedures and allprocedures commands. Now connect to the same DSN as Pat and create a procedure called q. You can view and set connection attributes with the ttIsql show and set commands. The ttIsql utility has several built-in commands for managing transactions.

These commands are summarized below:. This can also be set as an attribute of the set command. When starting ttIsql , the autocommit feature is turned on by default, even within a SQL script. In this mode, every SQL operation against the database is committed automatically.

To turn the autocommit feature off, execute the ttIsql autocommit command with an argument of 0. When autocommit is turned off, transactions must be committed or rolled back manually by executing the ttIsql commit , commitdurable or rollback commands.

The commitdurable command ensures that the transaction's effect is preserved in case of database failure. If autocommit is off when ttIsql exits, any uncommitted statements are rolled back and reported by ttIsql. The ttIsql isolation command can be used to change the current connection's transaction isolation properties. The isolation can be changed only at the beginning of a transaction.

If the isolation command is modified without an argument then the current isolation level is reported. The ttIsql sqlquerytimeout command sets the timeout period for SQL statements. If the execution time of a SQL statement exceeds the number of seconds set by the sqlquerytimeout command, the SQL statement is not executed and an error is generated. The following example demonstrates the common use of the ttIsql built-in transaction management commands.

Preparing a SQL statement just once and then executing it multiple times is much more efficient for TimesTen applications than re-preparing the statement each time it is to be executed.

Corresponds to one or more SQLFetch calls. Corresponds to exactly one SQLFetch call. The ttIsql utility prepared statement commands also handle SQL statement parameter markers. When parameter markers are included in a prepared SQL statement, ttIsql automatically prompts for the value of each parameter in the statement at execution time. The statement is prepared and then executed twice with different values for each of the statement's two parameters. The ttIsql utility timing command is used to display the elapsed time required to executed the primary ODBC function call associated with each command.

In the example above, the prepare command is immediately followed by the SQL statement to prepare. The ttIsql utility uses this ID to keep track of multiple prepared statements. A maximum of prepared statements can exist in a ttIsql session simultaneously. Generating from wild cards works the same as the user interface generation except that regular expressions can be used. Compare pairs are processed in the order specified in the configuration.

The compare pairs generated by a single compare pair element are generated in alphabetical order of the source table name. When compare pairs are generated by more than one compare pair element, the first one will be used.

The configuration is defined by the top level configuration element and several nested elements. Most of these elements have attributes that define their characteristics, such as the operation attribute for the configuration element or the port attribute for the connection element. Table configuration Elements. Table configuration Attributes. This is the default value.

This can be used to prevent unintended modification to existing repository items. The following example adds compare pairs that can be validated and ignores those that cannot; uses regular expressions for wild carding; and uses the "create" default to adds all items as new items, adding nothing if any item already exists.

The column element defines a set of columns to be included or excluded from the compare pair. The column element has no nested elements or text data. A regular expression that defines a set of source column names. This value is required. A regular expression that defines a set of target column names. It can include references to groups captured by the source-name expression. Indicates whether or not the matched columns should be excluded from the compare pair.

The options are:. This is the default. Specifies a format to override the comparison format that would normally be used. The values can be any of the data types supported by Oracle GoldenGate Veridata. The compare-pair element specifies a set of compare pair items. Table compare-pair Attributes.

An expression defining the name of the compare pair. A regular expression that defines the table or tables to be compared. See "Regular Expression Grouping" later in this section for more detail. The default is to match all tables. A regular expression that defines the target tables for the comparison. This may contain references to groups captured by the source table expression.

The name of the default schema for the source tables referenced for the compare pair. The default is the value specified for the group.

This is not used with Enscribe files. The name of the default schema for the target tables referenced for the compare pair. The default catalog for the source tables referenced in this compare pair.

TThe default catalog for the source tables referenced in this compare pair. Indicates whether or not the compare pair should be included in the group element.

This can be used to remove a compare pair generated by an earlier compare pair element. The name of the unique index to use as the source portion of the user-specified primary key.

The default is no user-specified index name. The name of the unique index to use as the target portion of the user-specified primary key. The default is the value of the source-pkey. If the compare pair has no column elements and no specified source-pkey, Oracle GoldenGate Veridata will select the most appropriate primary key or unique index to use. Indicates that the compare pair contains column elements with the type attribute set to key , so the generated compare pair will have user-defined columns for the key.

Regular expression grouping can be used to capture the parts of the source table names to be used for matching the target table name. The following example describes the key-only compare-pair. Its source tables are defined in the test schema and target tables in the other schema. It will exclude pairs where the source column name begins with S and the target column name begins with T and otherwuse matches the source column name.

The connection element defines a connection to a source or target comparison database through an Oracle GoldenGate Veridata agent. The amount of time Oracle GoldenGate Veridata will wait before timing out when sending requests to the agent.

Either "true" or "false" to indicate whether or not spaces will be removed from the end of character columns. The default is "true" to truncate spaces.

The description element is free-form text that can be used to attach a description to the containing element. It has no associated attributes. The following example provides a description for the connection named source.

The enscribe-info element provides additional information used to compare NonStop Enscribe records at the field level. Table enscribe-info Elements. Table enscribe-info Attributes.

The enscribe-key element defines the key that is to be used for Enscribe files. The enscribe-key element has no nested elements or text data. Table enscribe-key Attributes. The key that is to be used to begin reading the Enscribe file. The key of the last Enscribe record that should be read.